1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2010, week 2 2 59 29_Upgrading db2 from 9.5 to 9.716_Harold Pritchett14_harold@UGA.EDU30_Thu, 7 Jan 2010 19:39:47 -0500290_ISO-8859-1 I am running db2exc. Currently at 9.5.0, I am looking at putting up a test server at the 9.7.0.1 level. The existing server is 32 bit db2 running under Red Hat Enterprise Linux 5.4 32 bit. The new test server is 64 bit db2 running under Red Hat Enterprise Linux 5.4 64 bit. [...] 62 42 33_Re: Upgrading db2 from 9.5 to 9.711_Jeff Garmon15_jgarmon@UGA.EDU30_Fri, 8 Jan 2010 01:34:30 +0000640_utf-8 Based on the 9.5 admin guide, I believe what you're proposing will work:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp? topic=/com.ibm.db2.luw.admin.ha.doc/doc/c0005960.html

To quote the manual:

"The target system must have the same (or later) version of the DB2 database product as the source system. You cannot restore a backup created on one version of the database product to a system running an earlier version of the database product. For example, you can restore a DB2 UDB V8 backup on a DB2 V9 database system, but you cannot restore a DB2 V9 backup on a DB2 UDB V8 database system. [...] 105 175 27_z/OS V8, V9 History madness11_Roy Boxwell16_R.Boxwell@SEG.DE30_Fri, 8 Jan 2010 09:56:47 +0100496_ISO-8859-1 Hi list!

I know its friday but go and have a look at the HIST table docu in the IBM Manual in my V8manualon P. 913

History statistics Several catalog tables provide historical statistics for other catalog tables. These catalog history tables include: SYSIBM.SYSCOLDIST_HIST SYSIBM.SYSCOLUMNS_HIST SYSIBM.SYSINDEXES_HIST SYSIBM.SYSINDEXPART_HIST SYSIBM.SYSINDEXSTATS_HIST SYSIBM.SYSLOBSTATS_HIST SYSIBM.SYSTABLEPART_HIST SYSIBM.SYSTABLES_HIST SYSIBM.SYSTABSTATS_HIST [...] 281 38 39_Re: What is DB2 Doing Index Compression14_Peter Vanroose17_pvanroose@ABIS.BE30_Fri, 8 Jan 2010 13:09:52 +0000331_UTF-8 If someone is going to test this: be aware that the data in the bufferpool is never compressed, so as long as the index is in memory things could be fine. So make sure to flush the buffer pool (e.g. by stopping the index space) after your INSERT and before verifying whether that INSERT ended up correctly in the index. [...] 320 124 57_Re: Changing limitkey on a Partition (encore w/ problems)11_Nick Cianci19_ncianci@AU1.IBM.COM30_Fri, 8 Jan 2010 17:25:42 +1100580_UTF-8 Hi, Just thought I'd update the list with some issues we experienced recently to do with adding in partitions, that we had not seen previously or encountered in any of our test environments.

Our tale of woe starts with some 480M records across 26 Partitions for a total of 56 compressed Gb of Table. The partitioning (clustering) index accounts for 8Gb and is also the logical insert sequence so has high natural clustering. There are also 6 x NPI's (DPSIs are not really an option here) that occupy a total of ~170Gb, and their cluster ratio is "bad". With the [...] 445 76 39_Re: What is DB2 Doing Index Compression12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 8 Jan 2010 16:18:18 +0000350_utf-8 As noted, you could try to hurt yourself. One other possibility is that you are getting access to the data and using 8K pages that have only 4K of data, so that your bufferpool space would need to double in size or you could have lots of IO for an under sized buffer pool. Do you you have time to see what problems you cause for yourself? [...] 522 43 25_Indexspacestats Good News10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 Jan 2010 16:31:20 +0000368_utf-8 While DB2 9 has been a bit of a challenge we are now able to start applying some of the nice features. Using the LASTUSED column of SYSINDEXSPACESTATS we have found a lot of indexes that haven't been used in at least a year

These are our index stats

265GB unused versus 790GB used in our data sharing environment (mostly from the OLAP things) [...] 566 128 8_Runstats0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 8 Jan 2010 09:09:27 -0800431_US-ASCII Can someone please tell me if the statistics we are collecting during an on-line conditional REORG are the same as the ones we are collecting when we execute the RUNSTATS utility prior to running the conditional REORG? Listed below are the parameters we are using for each utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL [...] 695 42 46_free seminars in 7 cities over next two months12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 8 Jan 2010 16:29:53 +0000415_utf-8 upcoming DB2 for z/OS technical update seminars in US are part of a System z - Technology Summit

Jan 28 - Washington Feb 2 - Atlanta Feb 9 - St Louis Feb 11 - NYC Feb 18 - Toronto March 4 - Chicago March 11- Dallas

Attend our free technical education seminars on DB2 9 & X for z/OS and learn how an upgrade can help address demands for more database capacity and cost-cutting capabilities. [...] 738 115 39_Re: What is DB2 Doing Index Compression10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 Jan 2010 16:26:32 +0000366_utf-8 I have colleagues that like to probe a little deeper, then I get caught up in understanding what exactly is happening. When we use this in production the indexes will be rebuilt. We probably won't even try to change a compressed index back to the 4K bufferpool and then try to access it without doing a rebuild ;-P, I'd think that should definitely fail. [...] 854 92 39_Re: What is DB2 Doing Index Compression10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 Jan 2010 16:23:13 +00002649_utf-8 No, this is not somethign we are doing as standard operating procedure it just came out of some curiosity. Apparently the software compression is fairly smart :-)



On Wed, 6 Jan 2010 14:13:50 -0700, Blaicher, Chris wrote:

>SSBoYXZlIG5vdCB0cmllZCBzb21ldGhpbmcgbGlrZSB0aGF0LCBidXQgb XkgaW1wcmVzc2lvbiBp >cyB0aGF0IGl0IGlzIGxpa2UgbG9hZGluZyBhIGRvdWJsZS1iYXJyZWxlZCBz aG90Z3VuLCBhaW1p >bmcgaXQgYXQgeW91ciBmb290IGFuZCBwdWxsaW5nIHRoZSB0cmlnZ2 VyLg0KDQpBdCBiZXN0LCB5 >b3Ugbm93IGhhdmUgYSBjYXRhbG9nIHRoYXQgc2F5cyBpdCBpcyBjb21w cmVzc2VkIGFuZCBhbiBp >bmRleCB0aGF0IGlzIG5vdC4gIEhvdyBkaWZmZXJlbnQgcGFydHMgb2YgR EIyIGFuZCBvdGhlciB1 >dGlsaXR5IHZlbmRvcnMgcHJvZHVjdHMgcGxheSB3aXRoIHRoYXQgaXMg dXAgZm9yIGdyYWJzLg0K >DQpDaHJpcyBCbGFpY2hlcg0KUGhvbmU6IDUxMi0zNDAtNjE1NA0KTW9i aWxlOiA1MTItNjI3LTM4 >MDMNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBJRFVHI ERCMi1MIFttYWlsdG86 >REIyLUxASURVR0RCMi1MLk9SR10gT24gQmVoYWxmIE9mIEpvcmcgTH Vla2UNClNlbnQ6IFdlZG5l >c2RheSwgSmFudWFyeSAwNiwgMjAxMCAxOjIwIFBNDQpUbzogREIyLUx AV1dXLklEVUdEQjItTC5P >UkcNClN1YmplY3Q6IFtEQjItTF0gV2hhdCBpcyBEQjIgRG9pbmcgSW5kZX ggQ29tcHJlc3Npb24N >Cg0KVGhpcyBpcyBmb3IgREIyIDkgb24gei9PUy4NCg0KV2UgY2FuIGFsd GVyIHRoZSBpbmRleCBi >dWZmZXJwb29sIHRvIEJQOEsyIHRoZW4gQ29tcHJlc3MgWS4gIFRoaXMg cHV0cyB0aGUgDQppbmRl >eCBpbiByZWJ1aWxkIHBlbmRpbmcgc3RhdHVzLiAgQnV0IGlmIG9uZSByZ W1vdmVzIHRoYXQgZmxh >ZyB0aGUgaW5kZXggaXMgDQphY2Nlc3NpYmxlIHZpYSBCUDhLMiB3aXR oIG5vIG9idmlvdXMgYWR2 >ZXJzZSBhZmZlY3RzLiAgT2J2aW91c2x5IHRoZSBpbmRleCANCmRhdGFz ZXQgaXMgbm90IGNvbXBy >ZXNzZWQgYW5kIHByZXN1bWFibGUgdGhlIHNvZnR3YXJlIGRlY29tcHJlc3 Npb24gaXMgDQpydW5u >aW5nLiAgQnV0IHdvdWxkIHRoZXJlIGJlIGFueSBhZHZlcnNlIGFmZmVjdHM gaW4gcmVtb3Zpbmcg >dGhlIHJlYnVpbGQgDQppZG5leCBmbGFnPyAgSSBzdXBwb3NlIHdlIHNob 3VsZCBzZWUgd2hhdCBo >YXBwZW5zIG9uIGFuIEluc2VydC4uLg0KDQpfX19fX19fX19fX19fX19fX19f X19fX19fX19fX19f >X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18NCg0KKiBJR FVHIE5vcnRoIEFt >ZXJpY2EgKiBUYW1wYSwgRmxvcmlkYSwgKiBNYXkgMTAtMTQgMjAxMC AqICBodHRwOi8vSURVRy5P >UkcvTkEgKg0KX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX 19fX19fX19fX19f >X19fX19fX19fX19fX19fX19fX19fDQoNCmh0dHA6Ly93d3cuaWR1Zy5vcm cvZGIyLXZpZGVvcy5o >dG1sIGhhcyBodW5kcmVkcyBvZiB2aWRlbyBwcmVzZW50YXRpb25zIQ0K RGlkIHlvdSBtaXNzIG91 >dCBvbiBhdHRlbmRpbmcgYW4gSURVRyBjb25mZXJlbmNlPw0KTWFueS BvZiB0aGUgcHJlc2VudGF0 >aW9ucyB3ZXJlIHJlY29yZGVkIGFuZCBhcmUgYXZhaWxhYmxlIG9uIG91ciB 3ZWJzaXRlIQ0KX19f >X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX1 9fX19fX19fX19f >X19fX19fX19fDQoNCklmIHlvdSBuZWVkIHRvIGNoYW5nZSBzZXR0aW5nc ywgaHR0cDovL3d3dy5p >ZHVnLm9yZy9jZ2ktYmluL3dhP0EwPURCMi1MIGlzIHRoZSBob21lIG9mIElE VUcncyBEQjItTA0K [...] 947 122 31_Re: z/OS V8, V9 History madness10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 Jan 2010 17:22:52 +0000673_utf-8 We're just now looking at adding the History Stats collection to our jobs, though mostly for space trending. What would you be looking for in SYSTABLESPACE_HIST that you can't get from the other history tables?

On Fri, 8 Jan 2010 09:56:47 +0100, Roy Boxwell wrote:

>Hi list! > > I know its friday but go and have a look at the HIST table docu in the >IBM Manual in my V8manualon P. 913 > >History statistics >Several catalog tables provide historical statistics for other catalog >tables. These catalog history tables include: > SYSIBM.SYSCOLDIST_HIST > SYSIBM.SYSCOLUMNS_HIST > SYSIBM.SYSINDEXES_HIST > SYSIBM.SYSINDEXPART_HIST [...] 1070 41 12_Re: Runstats14_Peter Vanroose17_pvanroose@ABIS.BE30_Fri, 8 Jan 2010 17:52:54 +0000505_UTF-8 On Fri, 8 Jan 2010 09:09:27 -0800, barbara.j.nigh@US.HSBC.COM wrote: >Can someone please tell me if the statistics we are collecting during an >on-line conditional REORG are the same as the ones we are collecting when >we execute the RUNSTATS utility ***prior to*** running the >conditional REORG? Listed below are the parameters we are using for each >utility: > >RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL >REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL [...] 1112 25 39_Re: What is DB2 Doing Index Compression10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 8 Jan 2010 21:12:56 +0000752_utf-8 It looks like http://www-01.ibm.com/support/docview.wss?uid=swg1PK79312 will reduce the amount of indexes in rebuild pending

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 1138 31 36_Looking for a downlevel install file16_Harold Pritchett14_harold@UGA.EDU31_Sun, 10 Jan 2010 22:12:53 -0500286_ISO-8859-1 I need to do some testing of my upgrade path from db2exc 9.5.0 32 bit to db2exc 9.7.1 64 bit.

To to this, I need to find a copy of the install file for db2exc 9.5.0 64 bit. I can't find it on the IBM download site since they only seem to have the 9.7.1 versions. [...] 1170 27 44_[DB2 for z/OS V8] REORG Utility BUILD2 Phase15_Jorge Martelanz27_jorge_martelanz@HOTMAIL.COM31_Mon, 11 Jan 2010 06:04:57 +0000689_utf-8 Hi Does somebody have more information of what "serviceability enhancement" APAR PK78611 (http://www- 01.ibm.com/support/docview.wss?uid=swg1PK78611) provides? Thanks Jorge

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...] 1198 203 31_Re: z/OS V8, V9 History madness11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 Jan 2010 07:36:03 +0100614_UTF-8 NACTIVEF ??







Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert







Jorg Lueke Gesendet von: IDUG DB2-L 08.01.2010 18:22 Bitte antworten an IDUG DB2-L



An DB2-L@WWW.IDUGDB2-L.ORG Kopie [...] 1402 89 40_Re: Looking for a downlevel install file12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Mon, 11 Jan 2010 01:41:31 -0500355_us-ascii You should still have 9.5 available via your Passport Advantage account, I have both 9.5 and 9.7 still available. That isn't the support site, but your organizations Passport Advantage account. Usually, one or two people in an organization have access to this online. You should be able to download what you need from there. Here is a link: [...] 1492 203 48_Re: [DB2 for z/OS V8] REORG Utility BUILD2 Phase11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 Jan 2010 08:04:29 +0100608_ISO-8859-1 IBM??





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strae 5 40470 Dsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Dsseldorf, HRB 37894 Geschftsfhrung: Siegfried Frst, Gerhard Schubert







Jorge Martelanz Gesendet von: IDUG DB2-L 11.01.2010 07:04 Bitte antworten an IDUG DB2-L



An DB2-L@WWW.IDUGDB2-L.ORG Kopie [...] 1696 31 48_Re: [DB2 for z/OS V8] REORG Utility BUILD2 Phase15_Jorge Martelanz27_jorge_martelanz@HOTMAIL.COM31_Mon, 11 Jan 2010 08:20:46 +0000367_utf-8 Well... as many IBM Lab guys are watching this list, that's why I posted my question here. If you take a look at the "official" IBM description of the APAR, you will understand why I am asking for some clarification. I can (and probabbly will) open an informational PMR, but sometimes this list provides faster responses. Thanks for your suggestion. Jorge [...] 1728 38 77_AD: Exciting new collaboration between Cogito and Grainger Database SOlutions13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Mon, 11 Jan 2010 08:39:25 -0000485_US-ASCII Hi everyone, and greetings from a surprisingly snowy UK!

I'm happy to announce the start of a brand new collaboration between Grainger Database Solutions and Cogito to provide DB2 consultancy and product design & development services

For more details of this exciting development, please read the release at http://www.graingerdatabasesolutions.com/html/news.html

If anyone has any questions, you can contact me at phil@graingerdatabasesolutions.com [...] 1767 185 70_Redbook DB2 9 for z/OS: Resource Serialization and Concurrency Control10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 11 Jan 2010 04:35:57 -0500749_iso-8859-1

Hi DB2 user,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com

Last update on Monday, January 11th 2010



IBM just published a new Redbook, "DB2 9 for z/OS: Resource Serialization and Concurrency Control, SG24-4725-01":





Abstract Locking is the generic term used to refer to the database management system function that is required for managing interprocess concurrency and maintaining data integrity. However, locking is just one of the serialization mechanisms available in IBM DB2 for z/OS. DB2 uses different mechanisms for serialization to achieve its goal of maximizing concurrency without losing integrity with a minimum cost in [...] 1953 224 48_Re: [DB2 for z/OS V8] REORG Utility BUILD2 Phase11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 11 Jan 2010 10:52:21 +0100417_ISO-8859-1 I was being a bit facetious in my answer as I have also seen lots of APARs like that and it bugs me to! Quite why IBM are so secretive about these things is a mystery to me. I think the DB2 world would positively *like* the idea of help/improvement of tracing or dumps or what have you but instead you get a mysterious apar with useless text that you have to blindly install for apparently no reason. [...] 2178 265 20_AW: [DB2-L] Runstats35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Mon, 11 Jan 2010 12:36:21 +0100711_iso-8859-1 Barbara

There are some restrictions to inline stats:

- you cannot collect statistics for NPIs, when you reorg on partition level - you cannot collect histogramm-stats

may be, there are further restrictions, I am not aware of.

Mit freundlichen Gren Walter Janien

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Dsseldorf mailto:walter.janissen@itergo.com

Vorsitzender des Aufsichtsrates: Jrgen Vetter Geschftsfhrung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schn. Sitz: Dsseldorf, Handelsregister: Amtsgericht Dsseldorf, HRB 37996 [...] 2444 392 48_Re: [DB2 for z/OS V8] REORG Utility BUILD2 Phase16_Christian Michel18_cmichel@DE.IBM.COM31_Mon, 11 Jan 2010 13:45:47 +0100634_ISO-8859-1 Jorge & Roy,

First of all: Happy New Year!

When we mark APARs as "Serviceability enhancements", they are exactly that. Those APARs would not contain any new end user functionality or performance changes but provide additional diagnostic information that can be activated and used by L2 or L3 for specific problems that are very difficult to diagnose otherwise. Such diagnostic functions are not any committed functionality, they can be added, changed or removed by development at will, if we would start to document them, then we would have to support that kind of functions as well and could not easily [...] 2837 413 29_Re: SQL query with variables?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Mon, 11 Jan 2010 08:18:07 -0500556_windows-1252 Bala & Dale - Thanks for your potential solutions. Apologies, it took long for my response.



-Josh





On Wed, Dec 30, 2009 at 5:24 PM, Hardy, Dale wrote:

> If the tables arent too big, how about defining after insert trigger(s) > on Table B. > > > > 1. Sort the input file to Table B by the key columns and LOAD_DATE. > > 2. Load TABLE B with SHRLEVEL CHANGE. > > The trigger could check table b for a match on col1, col2, col3, col4 and > valid_from is null > > If cols 1-3 and [...] 3251 453 65_Re: DB2 SQL error: SQLCODE: -930, SQLSTATE: 57011, SQLERRMC: null16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 11 Jan 2010 09:32:09 -0500389_ISO-8859-1 As Phil pointed out, in a DB2 command line processor session you can get information about an error message by entering a question mark followed by the error message of interest (and the same can be done with a SQLSTATE value). You can also find this information via the DB2 for LUW Information Center on IBM's Web site. Here's the link for the DB2 9.7 Information Center: [...] 3705 43 40_Re: Looking for a downlevel install file16_Harold Pritchett14_harold@UGA.EDU31_Mon, 11 Jan 2010 10:10:18 -0500450_ISO-8859-1 Phil Gunning wrote: > You should still have 9.5 available via your Passport Advantage account, I > have both 9.5 and 9.7 still available. That isn't the support site, but your > organizations Passport Advantage account. Usually, one or two people in an > organization have access to this online. You should be able to download what > you need from there. Here is a link: > > http://www-01.ibm.com/software/howtobuy/passportadvantage/ [...] 3749 534 57_Re: Changing limitkey on a Partition (encore w/ problems)16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 11 Jan 2010 10:59:41 -0500377_windows-1252 Nick, a couple of things:

1) You mentioned looking at FAROFFPOS numbers in the SYSINDEXPART catalog table for some of your NPIs. FAROFFPOS (actually, FAROFFPOSF) is an indicator of tablespace organization, not index organization -- and it's a valid indicator of tablespace organization only if the FAROFFPOSF value is for the table's clustering index. [...] 4284 66 17_VCAT HLQ question12_Roy Reynolds17_royr@BERKELEY.EDU31_Mon, 11 Jan 2010 16:20:25 +0000584_UTF-8 I inherited several subsystems that exclusively use VCATs. The applications were old VSAM apps back in the late 80s. When they were moved to DB2 the application group person acting as DBA insisted that all the HLQs remain unchanged. So the Dev environment still has HLQs like PR1, PR2, PR3, PR4, etc. Likewise, the QA and Prod environments use the same HLQs for their DB2 VSAM Linear datasets. The differences between the enviroments are in the DB name level. They use DB names like PR1XYZD for Dev, PR1XYZQ for QA, and PR1XYZA or PR1XYZB or PR1XYZC for Prod. The leftmost [...] 4351 150 30_DB2 V8 and Z/os - Forcing bind13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Mon, 11 Jan 2010 10:27:44 -0600336_us-ascii Periodically, we need to get an emergency CICS/DB2 program compiled and bound to our production environment. Many times there are cached threads that prevent the bind from occurring. Is there a way (short of terminating all active threads) to force this bind though? Surely 24/7 shops have a need to force a bind through. [...] 4502 44 15_DSN1COPY V9 NFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 11 Jan 2010 08:32:04 -0800486_us-ascii We use DSN1COPY a lot to copy files from one version/system to another. I recently found that with V9 NFM, DSN1COPY does a delete/define of the target dataset prior to actually doing the copy. When we use PRIQTY -1 and SECQTY -1 for the tablespace allocations, this delete/define process has more than once given us problems as it uses allocation sizes of 2,1 cylinders for the allocation. And we've run out of extents several times if extent consolidation didn't happen. [...] 4547 313 21_Re: VCAT HLQ question13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 11 Jan 2010 16:40:35 +0000318_ISO-8859-1 An application can't even know the HLQ unless they query the Catalog, and I can't imagine any scenario in which the HLQ would even make a difference for anything an app might want to do with a database. Ask your manager to ask the other manager for a valid business case why they should "own" the HLQ. [...] 4861 311 46_AD: Webinars from Cogito and Helios & Matheson13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Mon, 11 Jan 2010 16:56:20 -0000567_US-ASCII Just a quick plug for some upcoming webinars on the EZ-DB2 products from Cogito





There are three which are repeated throughout the year, so you are sure to find ones at times that suit you:





SQL Access Path Impact Analysis w/ EZ-DB2

See how EZ-Impact Analyzer predicts and determines the impact of application and system changes on ALL SQL access paths. Only EZ-Impact Analyzer weights access path impact costs by Workload SQL Distribution for a true understanding of critical and non-critical impact. [...] 5173 209 30_Creating a homemade DATATYPE ?14_Jan Vanbrabant23_vanbrabantjan@GMAIL.COM31_Mon, 11 Jan 2010 17:52:54 +0100578_windows-1252 Hi,



This is about PL1 support with DB2 SQL.







Migrating Datacom to DB2 for tables defined in Datacom with fields defined as zoned decimal (PL1 PIC'9999'); in the (Datacom) application programs we are using host variables defined as PIC'9999' too.









Loading data though from a flat file (created by Datacom) into DB2 table, created with create table fieldname decimal(4), is working fine but data is stored as fixed dec and using DCLGEN data field type is generated as fixed dec. [...] 5383 338 32_New webinar series - Help wanted13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Mon, 11 Jan 2010 17:20:56 -0000353_US-ASCII Hi all





I'd like to kick off another monthly webinar series (much like the DB2 9 series I did in 2008), but I am not 100% what topics would be of interest





People think I am joking when I say that writing presentations is easy, it's coming up with interesting topics that's hard - but it's TRUE [...] 5722 760 21_Re: VCAT HLQ question12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM31_Mon, 11 Jan 2010 11:48:32 -0600355_iso-8859-1 IMHO,

The application group lost "ownership" of the dataset names when they converted from VSAM to DB2. The datasets can't really be used outside of DB2's control. Except for possibly the issue of DASD space billing, which may key off of the HLQ, there is no reason the applications team needs to be aware of the DB2 dataset names. [...] 6483 90 21_Re: VCAT HLQ question10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 11 Jan 2010 19:04:14 +0100423_UTF-8 I knew some dept. used first qualifier for some accounting (for instance SAL1 for salary app or ORD1 for orders etc in SAS programs) so if someone say HLQ or first 'n' qualifiers belong to an 'application' it

could be possible they're used for space accounting or something similar. I agree qualifiers in your post aren't so 'smart' but in a previous company I saw this kind of accounting (using MICS and [...] 6574 187 21_Re: VCAT HLQ question0_24_carol.sutfin@REGIONS.COM31_Mon, 11 Jan 2010 13:03:48 -0600368_UTF-8 I agree with Kirk on this.

We have the HLQ out of the application owner control.

Granted, we do not currently have a charge back system for DASD usage, we do keep track of usage by dataset name. 99% of the DB2 data uses a single HLQ and System Support is responsible for backup of the ICF catalogs for this along with the DB2 catalog backups. [...] 6762 275 21_Re: VCAT HLQ question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 11 Jan 2010 14:59:15 -0500569_iso-8859-1 Carol, If those databases aren't backed up, who catches the flak from management? DBA, or the application? If it's you, then you need to own the backups.

I've never heard of a line of business being responsible for backups. It's usually the case that the backups are integrated into the application business batch flows, but owned by DBA. There's also (usually) a corporate recoverability SLA that the database will be recoverable to within 24 hours of time-of-failure. Sometimes it's even "image copy with recoverysite versions every 24 hours." [...] 7038 30 21_Re: VCAT HLQ question11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Mon, 11 Jan 2010 20:11:20 +0000319_- >Also that 99% of the data is controlled by SMS routines. >The only non-SMS DB2 is the DB2 catalog. >Just call me old fashioned there. >I still like being able to allocate those myself.

With SMS-control, you still can. Ask your storage admins about "guaranteed space". - Too busy driving to stop for gas! [...] 7069 27 21_Re: VCAT HLQ question11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Mon, 11 Jan 2010 20:14:03 +0000344_- >I've never heard of a line of business being responsible for backups.

Pre-SMS, a lot of shops didn't have centralised storage administration, so lots of applications did backups of their own data with inconsistent standards. Some shops still work that way, even after implementing SMS.

- Too busy driving to stop for gas! [...] 7097 870 21_Re: VCAT HLQ question0_24_carol.sutfin@REGIONS.COM31_Mon, 11 Jan 2010 14:43:47 -0600416_ISO-8859-1 Phil,

It gets interesting Applications here are responsible for the Image Copy/Backup of their databases because we have multiple applications within a single DB2 subsystem.

Yes, I am the "B***H" that pokes them to make sure that they can recover their databases. I have been known to to my own copy of DB's with my own HLQ as a CYA until they get their processes in place and working. [...] 7968 1026 21_Re: VCAT HLQ question16_Broyles, Carol L27_Carol.L.Broyles@ACS-INC.COM31_Mon, 11 Jan 2010 15:03:26 -0600489_iso-8859-1 This is an excellent argument for not using the same HLQ across DB2 systems. We RACF protect the HLQ so a different DB2 system can't open them. We use the group attach name, and it's working well across more than 80 systems.









Carol L. Broyles

Infrastructure Mgt. Consultant

Commercial Solutions

Office Phone: 937-495-4003

carol.l.broyles@acs-inc.com





Affiliated Computer Services Inc. [...] 8995 312 57_Re: Changing limitkey on a Partition (encore w/ problems)0_24_hhuang@DCCSH.ICBC.COM.CN31_Tue, 12 Jan 2010 08:57:08 +0800411_GB2312 Nick,

I'm also curious on what is DB2 doing during updating NPIs when reorg an "empty" part. I would suggest to run a DSN1LOGP agaisnt that TS/IX object and reorg utility, maybe a short time period is enough, to get some clues. What I could guess now, is some pseudo_delete_entries clearance? I have no idea about that internal update NPI process details, hope some IBMer could shed a light. [...] 9308 274 57_Re: Changing limitkey on a Partition (encore w/ problems)11_Nick Cianci19_ncianci@AU1.IBM.COM31_Tue, 12 Jan 2010 12:34:13 +1100332_UTF-8 Hi Robert, thanks for your comments. BTW I still refer to your red-leather bound (v3 I think it was) DB2 SQL performance manual from time to time (a good read)!

Sorry you are right in my rush to put this out I got NEAR/FAROFFPOSF mixed-up with LEAFNEAR/FAR, which was what I'd meant (... mia culpa ... mia culpa!) [...] 9583 42 49_AUTO: Gab Gambassi is away (returning 17/01/2010)12_Gab Gambassi20_gabriele@NZ1.IBM.COM31_Tue, 12 Jan 2010 16:01:01 +1300506_US-ASCII I am out of the office until 17/01/2010.

I am sorry, I am away from the office. For urgent matters please contact: my collegaue Art Balingit Phone 09-359-8417 Mobile 021-464-958 Mail balingit@nz1.ibm.com

or my Team Leader , Joshua Joshua Phone 09-359-8523 Mobile 021-2442673 Mail joshuaj@nz1.ibm.com

Thanks for you patience.



Note: This is an automated response to your message "DB2-L Digest - 9 Jan 2010 to 12 Jan 2010 (#2010-8)" sent on 12/1/10 14:00:00. [...] 9626 99 35_Re: MQT not recognized by optimizer10_Joe Geller21_joerg6666@HOTMAIL.COM31_Tue, 12 Jan 2010 05:15:37 +0000535_UTF-8 I believe that your problem is that your query references the columns STA_DT and END_DT, but the MQT definition does not. Since the MQT does not have those columns, there query cannot use it.

Joe



On Thu, 7 Jan 2010 16:18:29 +0000, Mark Vickers wrote:

>I created my first MQT and cannot get the optimizer to pick it up ? > >Original Query: >SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , : >H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H [...] 9726 417 21_Re: VCAT HLQ question0_17_jtonchick@AOL.COM31_Tue, 12 Jan 2010 00:58:07 -0500333_utf-8

You might want to check to see how the applications are charged for their dasd usage. I'd bet that they are billed by highlevel. This goes beyond the concept of "ownership" and adds a level of complexity to any proposed change since you'll have to get by in from management to change the IT chargeback algorithms. [...] 10144 29 48_Re: [DB2 for z/OS V8] REORG Utility BUILD2 Phase15_Jorge Martelanz27_jorge_martelanz@HOTMAIL.COM31_Tue, 12 Jan 2010 06:36:49 +0000291_utf-8 Thanks Christian and Happy New Year for you too! I was expecting that answer :-), but, you know... I come from the times when you had to use the crank to start the car (punched cards, /360, etc, etc.) In any case that will clarify the issue for the guys upstairs. Take care Jorge [...] 10174 134 21_Re: VCAT HLQ question10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 12 Jan 2010 10:51:00 +0100428_UTF-8 In the last company I worked backups were executed by application people, not by DBAs. So you had a lot of useless backups because everyone was scared to lose data even if tables were not updated daily or weekly......



Massimo Scarpa - Ufficio Sistemi Cesve Servizi Informatici Bancari v. Longhin, 1 - 35129 Padova Tel. 049.8067111 - Fax. 049.8067300 E-Mail: mscarpa@cesve.it - Web: http://www.cesve.it [...] 10309 213 19_Re: DSN1COPY V9 NFM11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 12 Jan 2010 10:55:03 +0100580_ISO-8859-1 dont use automatic space management??? and dont use XML spaces either.....sometimes I hate DSN1COPY.....try playing with multiple datasets LDSs for a while and you will see what I mean! However I must be honest and say that I have not seen your behaviour here. What you say would cause a multi-dataset DSN1COPY to die because you must allocate all target datasets (I use MODEL) before doing the DSN1COPY - of couse what you mention might be true only for single datasets entities...I am really busy with "new" stuff at the moment but if I get a free minute I will [...] 10523 398 35_Re: MQT not recognized by optimizer23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Tue, 12 Jan 2010 06:31:26 -0600664_windows-1252 Thanks David, I had not picked up on the original context that this was a z/OS question. Pardon my prejudice.



Jeffrey Benner eBenner.com Consulting http://www.ebenner.com +001 312 520 0090 http://www.linkedin.com/in/ebenner





On Thu, Jan 7, 2010 at 14:02, David Simpson wrote:

> Hi Jeff, > > > > This may be a difference between DB2 for z/OS and LUW > > > > The Performance Monitoring and Tuning guide for DB2 on z/OS says: > > > > DB2 supports automatic query rewrite only for read-only, dynamic queries. > DB2 cannot automatically rewrite statically bound queries. > > > > [...] 10922 594 36_Re: New webinar series - Help wanted13_Joehlin, Gary26_Gary.Joehlin@SALLIEMAE.COM31_Tue, 12 Jan 2010 08:57:28 -0500541_us-ascii Hello Phil!

Happy New Year!

I would like to take you up on your offer and to propose several topics. I don't know what the rest of the world wants to see, so my topics of interest are move selfish.

What I would like to see is a series of a practicum, a 'start to finish'. I would like to see topics, perhaps not fully exploited today where I see that have a future for the companies that I've contracted. Since I don't have the experience today I don't wish to start off on a bad footing and by improperly [...] 11517 562 35_Re: MQT not recognized by optimizer14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Tue, 12 Jan 2010 08:11:00 -0600349_us-ascii Thanks everyone, My bad for not saying [z/OS]

I added the STA_DT & END_DT to the MQT and still no luck.

I am going to go with Phil's comment (An MQT also behaves like a regular DB2 table if you reference it directly) and play with that and if I can see that updates are reflected in the summary, I will use that, Thanks. [...] 12080 29 44_Re: VCAT HLQ question - replies appreciated!12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 12 Jan 2010 14:18:19 +0000528_UTF-8 Many thanks for weighing in on this, all of you! The 'weight of opinion and experience' offered by this significant-DBA worldwide neighborhood is invaluable. Even this DBA who started with tape based 'experimental databases' in the late 60s is humbled by your generosity. Cheers, Roy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 12110 1014 35_Re: MQT not recognized by optimizer13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Tue, 12 Jan 2010 15:42:43 -0000388_us-ascii If you are going to go with my suggestion, I have to admit being concerned by your comment "and if I can see that updates are reflected in the summary"





The data in the MQT will only be as current as the last time you issued a REFRESH TABLE statement





An MQT is NOT automatically kept "in synch" with the table it is defined upon [...] 13125 39 35_Single or cursor - which is better?12_Sheldon Rich16_zisher@GMAIL.COM31_Tue, 12 Jan 2010 16:00:21 +0000297_utf-8 We are undergoing an overall DB2 performance review which has led to some interesting questions. What is most efficient way to "select" a single row from within an application program. Is it better to create a cursor and Open, Fetch, and Close OR is it better to do a singleton select? [...] 13165 210 21_Re: VCAT HLQ question13_David Simpson22_dsimpson@THEMISINC.COM31_Tue, 12 Jan 2010 10:00:36 -0600545_utf-8 -----Original Message----- From: jtonchick@AOL.COM Sent: Tuesday, January 12, 2010 12:43 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] VCAT HLQ question



You might want to check to see how the applications are charged for their dasd usage. I'd bet that they are billed by highlevel. This goes beyond the concept of "ownership" and adds a level of complexity to any proposed change since you'll have to get by in from management to change the IT chargeback algorithms. [...] 13376 41 21_Who can issue GRANTs?12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 12 Jan 2010 16:09:07 +0000481_UTF-8 I am working with a site to evaluate their DB2 practices. In the 80s the apps were VSAM. In the early 90s they moved to DB2 but they had no one trained as either a systems or application DBA. So there were a lot of RTFM and 'best guess' outcomes. One application manager has always had auth to issue all the necessary GRANTs for Dev, QA, and Prod. This manager can also define databases, tablespaces, tables, indexes, and views, and can modify data in all environments. [...] 13418 50 25_Re: Who can issue GRANTs?10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 12 Jan 2010 17:28:43 +0100717_US-ASCII I see this situation in the past. IMHO no application manager should have RACF permits. Move her/him to security group and the problem's solved :-)

Max Scarpa

DB2 for Commodore 64 sysprog

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ 13469 188 25_Re: Who can issue GRANTs?12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM31_Tue, 12 Jan 2010 11:16:57 -0500437_US-ASCII No,I would think Auditors would frown upon that. The other red flag would be 'can modify data in all environments'. It sounds like this person has SYSADM. Jeff





Roy Reynolds Sent by: IDUG DB2-L 01/12/2010 11:10 AM Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject [DB2-L] Who can issue GRANTs? [...] 13658 29 44_Formatting table row printing and SQL in DB25_David17_mairecj@YAHOO.COM31_Tue, 12 Jan 2010 17:01:22 +0000494_utf-8 Is there a way to turn off formatting in batch SPUFI and QMF? Would like just row output/print with fixed length columns (blank or comma separated).

Also is there an IBM tool or open source tool that can be used to format unformatted DB2 SQL?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 13688 37 48_Re: Formatting table row printing and SQL in DB213_Bell, Raymond20_Raymond_Bell@BMC.COM31_Tue, 12 Jan 2010 18:09:00 +0100364_utf-8 Yeah, there is - an open-source SQL formatter. A customer of mine sent me the link to it awhile ago but since then I've had to rebuild my laptop and have lost the link. I'll ask him again. I don't need it as the few times I do want SQL formatted there are also ISV tools that do that - and I kinda have access to a good chunk of 'em. Can't think why... [...] 13726 58 48_Re: Formatting table row printing and SQL in DB214_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Tue, 12 Jan 2010 11:27:38 -0600405_us-ascii That would be an unload (see dsntiaul) or if you are trying to use this data in a rexx, you can access the data directly with the Rexx/DB2 interface.

faz

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Sent: Tuesday, January 12, 2010 11:01 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Formatting table row printing and SQL in DB2 [...] 13785 73 48_Re: Formatting table row printing and SQL in DB214_Arnold, Mark S20_Mark.Arnold@BNSF.COM31_Tue, 12 Jan 2010 11:46:32 -0600617_us-ascii I like SSLinForm: www.sqlinform.com

Thanks, Mark

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Tuesday, January 12, 2010 11:09 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Formatting table row printing and SQL in DB2

Yeah, there is - an open-source SQL formatter. A customer of mine sent me the link to it awhile ago but since then I've had to rebuild my laptop and have lost the link. I'll ask him again. I don't need it as the few times I do want SQL formatted there are also ISV tools that do that - and I kinda [...] 13859 176 39_Re: Single or cursor - which is better?16_Robert Catterall21_rfcatterall@GMAIL.COM31_Tue, 12 Jan 2010 12:52:45 -0500575_ISO-8859-1 Are you looking for a "one size fits all" solution (i.e., a SELECT construction that you could use in all cases)? Personally, I'd prefer not to go that route. I'd rather maximize efficiency by using the technique that best suits a given situation. If I know that a SELECT statement cannot generate a multi-row result set (as when I reference a primary key or some other unique key in an equal-type predicate), I'll go with a singleton SELECT (that is, a SELECT INTO) because that minimizes calls to DB2; otherwise, I'll go the DECLARE/OPEN/FETCH/CLOSE route. [...] 14036 38 39_Re: Single or cursor - which is better?12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Tue, 12 Jan 2010 10:52:31 -0700425_UTF-8 In my humble opinion a single select for 1 row only is more efficient than open, fetch close cursor because it's one instruction instead of 3 for cursor processing.

Regards, Shery

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sheldon Rich Sent: Tuesday, January 12, 2010 9:00 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Single or cursor - which is better? [...] 14075 985 35_Re: MQT not recognized by optimizer12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 12 Jan 2010 19:53:30 +0200521_utf-8 Hi,





Or use maintained by user with triggers to keep it fully in synch.





Isaac Yassin

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Tuesday, January 12, 2010 5:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] MQT not recognized by optimizer





If you are going to go with my suggestion, I have to admit being concerned by your comment “and if I can see that updates are reflected in the summary” [...] 15061 35 24_DB2 Table Update Columns4_Anil21_alisha_kale@YAHOO.COM31_Tue, 12 Jan 2010 18:05:35 +0000373_utf-8 Hi All !

While working with DB2 table in the MVS world, there used to a best practice that said "For effective logging, specify most frequently updated columns at the end of the row (record)". I was wondering if that recommendation is still valid or has DB2 changed over the years and that now its a non-issue. Can someone throw some light on this topic. [...] 15097 40 28_Question concerning unixODBC6_Jeff G15_jgarmon@UGA.EDU31_Tue, 12 Jan 2010 18:24:11 +0000346_UTF-8 We've got an installation where a PHP application is accessing DB2 v9.7 Express C databases (everything is on the same hardware - nothing remote) using standard odbc functions and everything seems to be working. Simply update the unixODBC.ini to point to the appropriate driver, add the data source to the odbc.ini, and we're running. [...] 15138 245 24_ZOS DB2 v8 Reserve Words17_Kaminski, Michael39_Michael.Kaminski@TRUSTMARKINSURANCE.COM31_Tue, 12 Jan 2010 12:42:58 -0600360_us-ascii We are currently at DB2 v8 (CM) and have been doing some testing with the new v8 reserve words and it appears to be tolerated. We tested and know that you cannot alter a table to add a reserve work but you can create a table using one. We also did not come across any errors in any SQL statements using reserve words as well. IBM's response was: [...] 15384 68 28_Re: DB2 Table Update Columns9_Stan Hoey22_shoey@CIRCLE-GROUP.COM31_Tue, 12 Jan 2010 18:48:53 -0000313_us-ascii My knowledge in this area is a little out of date and may no longer be correct, but log records used to span from the first column updated to the last column updated. Best practice was to group frequently updated columns together. I don't think the position of the columns in the table is a factor. [...] 15453 88 39_Re: Single or cursor - which is better?9_Stan Hoey22_shoey@CIRCLE-GROUP.COM31_Tue, 12 Jan 2010 19:13:37 -0000346_us-ascii I agree that there is no "one size fits all", but the CPU costs of different SQL calls differ, sometimes dramatically. OPEN and CLOSE have a very short pathlength, and there combined cost is much less than a FETCH or SELECT.

I would only ever use a singleton SELECT if I knew for certain that only one row would be returned. [...] 15542 93 25_Re: Who can issue GRANTs?10_Roger Hecq18_Roger.Hecq@UBS.COM31_Tue, 12 Jan 2010 14:21:03 -0500397_US-ASCII By application manager do you mean the manager who is the business owner of the data or an application development manager? My experience has consistently been that the business owner requested / authorized the grants and that either the DBAs (DB2 security) or the RACF security group implemented the necessary rules for the business owner. This is a fairly basic audit requirement. [...] 15636 93 39_Re: Single or cursor - which is better?10_Roger Hecq18_Roger.Hecq@UBS.COM31_Tue, 12 Jan 2010 14:31:30 -0500433_US-ASCII On the whole, I would have to go with the singleton select, because it only involves one transit to DB2. From an overall performance perspective, however, the difference betweent he two is probably insignificant when compared to the importance assuring an optimal access path, only returning the desired columns, etc. Obviously, the program would have to accommodate the -811 SQL code, if that was a valid possibility. [...] 15730 73 25_Re: Who can issue GRANTs?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 12 Jan 2010 11:32:31 -0800581_utf-8 Hi Roy. We still do our security in DB2, but we grant only to secondary authids, and have things about as granular as we need them: One authid for read-only for appl A, another for update for appl A, another for read-only for appl B, etc. The DBA's actually perform the grants, but it's pretty clear what table belongs to which application and what level of authority is required, so there's not much decision making there. The real decision is who to connect to which secondary authid. That is controlled in our external security package (Top Secret) and those updates [...] 15804 165 39_Re: Single or cursor - which is better?0_26_John.L.Miller@ATCOITEK.COM31_Tue, 12 Jan 2010 13:06:03 -0700570_us-ascii Sheldon,

Keep in mind that the cost in an OPEN/FETCH/CLOSE scenario may not always occur on the FETCH. Depending on the access path the cost may occur on the OPEN. This means that while your statement that a singleton select must check for a second row is true, it's also true that an open/fetch/close scenario may retrieve ALL rows before a FETCH is even issued. This is especially true if you're doing an order by that can't avoid a sort. In this scenario a singleton select is better because it will stop executing after the second row is found. [...] 15970 35 23_Facebook account hacked13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Tue, 12 Jan 2010 14:07:44 -0600315_ISO-8859-1 Hi All,

I wanted to let everyone know that my Facebook account was hacked. If you receive anything requesting money from me (or at least my Facebook account), please ignore it.

I did a blog today explaining it all.... http://it.toolbox.com/blogs/db2zos/facebook-account-hacked-36360

16006 131 39_Re: Single or cursor - which is better?12_Martin Hubel17_Martin@MHUBEL.COM31_Tue, 12 Jan 2010 15:07:27 -0500 16138 115 28_Re: DB2 Table Update Columns13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Tue, 12 Jan 2010 14:21:00 -0600355_us-ascii My (also slightly dated) experience was fixed length tables logged from the first changed byte to the last changed byte, not necessarily full columns. And for variable length rows, DB2 tended to log from the first changed byte to the end of the row... so putting frequently updated columns towards the end of the row was better for logging. [...] 16254 372 39_Re: Single or cursor - which is better?15_Leon Katsnelson15_leon@CA.IBM.COM31_Tue, 12 Jan 2010 15:44:46 -0500661_US-ASCII

Bonnie Baker touched on this subject in her article in the latest IBM Data Management magazine http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2009_Issue3/ProgrammersOnly/index.html.

I think most people will agree that singleton select is preferable because it is faster the question is by how much and where. If your application code is not running on the z/OS i.e. you are using DB2 Connect to do SQL from Windows, Unux, Linux or Apple Mac then you may not even have a choice as most common SQL APIs (CLI, ODBC, JDBC, .NET etc.) on these platforms don't even have a concept of a singleton select and all operations are [...] 16627 71 28_Re: DB2 Table Update Columns13_George Toolan19_gtoolan@COMCAST.NET31_Tue, 12 Jan 2010 15:46:38 -0500511_UTF-8 The end of row was for variable length columns (now we have reordered row format). For logging updates, the thing to do was keep the columns that are updated together physically together (beginning/middle/end -- doesn't matter) since logging is from the first changed column to the last. If you opt to log the entire row, none of this matters. If you update the variable length columns it doesn't make much difference if the variables are at the end and the row length indicator is at the beginning. [...] 16699 104 28_Re: DB2 Table Update Columns9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 12 Jan 2010 14:50:14 -0600468_US-ASCII Yes, BUT - the order that the DDL specifies is not necessarily the order in the physical table as of V9. Look for reordered row format in the administration guide. IBM did not trust people to put the columns in the 'best' order.

The correct rule for logging is - for fixed length columns - start at the first changed column and end at the last changed column. If there are any variable length columns in that span, then log to the end of the row. [...] 16804 263 87_Fw: Red Alert: Both DB2 9 for z/OS potential data corruption and DB2 crash (2010.01.12)15_Chris Hoelscher21_choelscher@HUMANA.COM31_Tue, 12 Jan 2010 16:01:06 -0500708_ISO-8859-1 just in case anyone does not subscribe to IBM red alerts



From: IBM My Notifications



Date: 01/12/2010 03:45 PM Subject: Red Alert: Both DB2 9 for z/OS potential data corruption and DB2 crash (2010.01.12)









12 Jan 2010





Dear Subscriber (choelscher@humana.com),

The following are the latest updates to your subscription at IBM My notifications.



Note: Forward this message to colleagues who may benefit from this technical information. The "Subscribe or Unsubscribe" link at the bottom of this message will allow them to create their own subscriptions. [...] 17068 103 48_Re: Formatting table row printing and SQL in DB213_Bell, Raymond20_Raymond_Bell@BMC.COM31_Tue, 12 Jan 2010 22:37:06 +0100554_us-ascii My customer friend sent me the link again. Looks like what I vaguely remember: http://www.dpriver.com/pp/sqlformat.htm

It's web based. Paste in your SQL and it gets layed out in the bottom window. Looks nice - for free software... ;o)

Cheers,



Raymond

________________________________________ From: IDUG DB2-L [DB2-L@IDUGDB2-L.ORG] On Behalf Of Arnold, Mark S [Mark.Arnold@BNSF.COM] Sent: 12 January 2010 17:46 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Formatting table row printing and SQL in DB2 [...] 17172 44 39_Re: Single or cursor - which is better?12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Tue, 12 Jan 2010 22:08:45 +0000358_UTF-8 Hi Martin, that tool has become CA Gen now (after having been "AllFusion Gen" for a while). Did it really in the old days? Uhg!

My Gen experience is also a bit outdated meanwhile, but - at least with more recent versions - these "singletons plus -811 checks" are done selectively, influenced by physical data model and pseudo code options. [...] 17217 27 25_Re: Who can issue GRANTs?12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 12 Jan 2010 22:09:36 +0000701_UTF-8 Hi Roger, By application manager, I mean Application Development Manager, not Application Data Owner. I really appreciate the contributions all are making to this topic. Regards, Roy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 17245 54 38_DB2 v8 Identifying PTFs under each RSU9_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Wed, 13 Jan 2010 11:06:34 +0530316_ISO-8859-1 Dear list,

Is there a way out there to identify the PTFs/APARs available per each RSU (monthly) ? I looked at the IBM Support website and for z/OS such an option was available and explained. But, I just want to know if there is some link or document that could help me in working on DB2 PTFs. [...] 17300 824 47_AW: [DB2-L] Single or cursor - which is better?12_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM31_Wed, 13 Jan 2010 10:21:59 +0100501_iso-8859-1 Hi dear Martin ! Info only to CA's 'Coolgen' ... some different product-names, of the same software, in this story:

JMA (JamesMArtin) --> IEF --> Composer --> Cool:Gen --> Advantage:Gen --> Allfusion:Gen --> (now !) CA:Gen ... *g*

PS: We run, right now with no problems, in our shop's CA's "CA:Gen/7.6.1" with all it's nice "additional IT-stuff" (DB2, Cobol, Java, PL/1, Assembler, CTS/CICS, Proxy, WEB, IP-Listener, Assembler, LDAP-Server, IMS; ... etc.,etc.,etc.) *gg* [...] 18125 171 42_Re: DB2 v8 Identifying PTFs under each RSU16_Christian Michel18_cmichel@DE.IBM.COM31_Wed, 13 Jan 2010 12:39:19 +0100617_ISO-8859-1 Yes, all DB2 PTFs are also listed in the following directory for the available PUT and RSU levels: ftp://service.boulder.ibm.com/s390/assigns

Mit freundlichen Gren / Kind regards Christian Michel

Christian Michel E-mail: cmichel@de.ibm.com Phone (49)-7031-16-4293 DB2 for z/OS Utilities Development Information Management Development Boeblingen, Germany





IBM Deutschland Research & Development GmbH Vorsitzender des Aufsichtsrats: Martin Jetter Geschftsfhrung: Dirk Wittkopp Sitz der Gesellschaft: Bblingen / Registergericht: Amtsgericht Stuttgart, HRB 243294 [...] 18297 535 28_Re: ZOS DB2 v8 Reserve Words13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 13 Jan 2010 12:15:51 -0000356_us-ascii Another way to reduce the burden of eliminating reserved words (especially when none of us know what reserved words there will be in the future) is to wrap quote marks around everything that the SQL parser may misinterpret

Ie CREATE TABLE "SELECT" .. Instead of CREATE TABLE SELECT

Phil Grainger Grainger Database Solutions Ltd [...] 18833 62 27_Re: Facebook account hacked13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 13 Jan 2010 12:19:35 -0000553_us-ascii It may just be me, but when I click the link to Willies blog, I get a mostly blank screen?

Phil Grainger Grainger Database Solutions Ltd

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Willie Favero Sent: 12 January 2010 20:08 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Facebook account hacked

Hi All,

I wanted to let everyone know that my Facebook account was hacked. If you receive anything requesting money from me (or at least my Facebook account), please ignore it. [...] 18896 115 35_AW: [DB2-L] Facebook account hacked35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 13 Jan 2010 14:20:42 +0100853_iso-8859-1 Nope, me too; thought it was some strange behaviour on our end of the line, but apparently it is not.

Ruediger Kurtz



Rdiger Kurtz Abteilung Informatik Betrieb

HUK-COBURG Bahnhofsplatz 96444 Coburg Telefon 09561 96-44147 Telefax 09561 96-44104 E-Mail Ruediger.Kurtz@HUK-COBURG.de Internet www.HUK.de ============================================================= HUK-COBURG Haftpflicht-Untersttzungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021 Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg Vorsitzender des Aufsichtsrats: Werner Strohmayr. Vorstand: Dr. Wolfgang Weiler (Sprecher), Wolfgang Flahoff, Stefan Gronbach, Klaus-Jrgen Heitmann, Dr. Hans Olav Hery, Jrn Sandig. ============================================================= [...] 19012 97 27_Re: Facebook account hacked11_Mike Turner19_mike.turner@GMX.COM31_Wed, 13 Jan 2010 13:40:23 -0000649_iso-8859-1 I get through eventually, but all access to Willie's blog posts is very slow. Other blogs on Toolbox for IT seem to respond quickly.

Regards Mike Turner Email: mike.turner@gmx.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44. ----- Original Message ----- From: "Phil Grainger" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, January 13, 2010 12:19 PM Subject: Re: [DB2-L] Facebook account hacked [...] 19110 151 39_Re: AW: [DB2-L] Facebook account hacked13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 13 Jan 2010 09:06:12 -0500423_utf-8 Hmmmm. Maybe it's the pond.

I get Willie's blog through that link

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 19262 81 25_Re: Who can issue GRANTs?10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 13 Jan 2010 09:17:26 -0500327_US-ASCII The application development manager should not have any security admin authority on the production DB. That is a major exposure and I am amazed the the auditors have not gotten on that. Access to production data should be owned by the business owner of the data and administered by the security dept or the DBAs. [...] 19344 408 39_Re: AW: [DB2-L] Facebook account hacked11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 13 Jan 2010 15:27:33 +0100613_ISO-8859-1 looks fine in Firefox 3.5.7 also this morning it was ok,,,





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strae 5 40470 Dsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Dsseldorf, HRB 37894 Geschftsfhrung: Siegfried Frst, Gerhard Schubert







Kurtz, Rdiger Gesendet von: IDUG DB2-L 13.01.2010 14:20 Bitte antworten an IDUG DB2-L [...] 19753 131 27_Re: Facebook account hacked13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 13 Jan 2010 15:03:13 -0000691_US-ASCII Willie must be too popular

http://it.toolbox.com/blogs/it-blogs/ is fine

http://it.toolbox.com/blogs/db2zos/the-mainframe-sounds-cool-how-do-i-learn- more-36365 is slow to non existent, as is http://it.toolbox.com/blogs/db2zos/facebook-account-hacked-36360

They need to be on a bigger machine :)

Phil G

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner Sent: 13 January 2010 13:40 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Facebook account hacked

I get through eventually, but all access to Willie's blog posts is very slow. Other blogs on Toolbox for IT seem to respond quickly. [...] 19885 152 35_AW: [DB2-L] Facebook account hacked35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 13 Jan 2010 16:09:15 +0100867_iso-8859-1 After reading Mike's mail I tried it another time and after having waited for a verrrrrry long time I got thru as well; though still not ages, it certainly took too long for me to wait the previous times.

Regards

Ruediger Kurtz

Abteilung Informatik Betrieb

HUK-COBURG Bahnhofsplatz 96444 Coburg Telefon 09561 96-44147 Telefax 09561 96-44104 E-Mail Ruediger.Kurtz@HUK-COBURG.de Internet www.HUK.de ============================================================= HUK-COBURG Haftpflicht-Untersttzungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021 Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg Vorsitzender des Aufsichtsrats: Werner Strohmayr. Vorstand: Dr. Wolfgang Weiler (Sprecher), Wolfgang Flahoff, Stefan Gronbach, Klaus-Jrgen Heitmann, Dr. Hans Olav [...] 20038 491 27_Re: Facebook account hacked13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 13 Jan 2010 15:11:05 +0000573_ISO-8859-1 I don't have a problem (speed or otherwise) on any of these pages...

Dr. Michael Ebert aMaDEUS Data Processing DB2 & Oracle Database Administrator Erding / Munich, Germany







Phil Grainger To DB2-L@WWW.IDUGDB2-L.ORG cc

bcc

Subject Re: [DB2-L] Facebook account hacked









Phil Grainger Please respond to : IDUG DB2-L Sent by: IDUG DB2-L 13-01-10 16:03 [...] 20530 73 48_Re: Formatting table row printing and SQL in DB220_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Wed, 13 Jan 2010 17:24:56 +0200686_us-ascii How about this one?

http://www.dpriver.com/pp/sqlformat.htm



Cuneyt



> > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David > Sent: 12 January 2010 17:01 > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Formatting table row printing and SQL in DB2 > > Is there a way to turn off formatting in batch SPUFI and > QMF? Would like > just row output/print with fixed length columns (blank or > comma separated). > > Also is there an IBM tool or open source tool that can be > used to format > unformatted DB2 SQL? > > _____________________________________________________________________ > > * IDUG [...] 20604 151 39_Re: AW: [DB2-L] Facebook account hacked15_Nelsen, Keith P26_KPNelsen@INTEGRYSGROUP.COM31_Wed, 13 Jan 2010 09:18:48 -0600401_iso-8859-1 I had also been having problems since around October accessing Willie's blog from work while I could access it from home without issue. Found out from our Network folks that there is currently some link to LINKEDIN on the IT.TOOLBOX.COM site when accessing Willie's blog which our network folks block, making the blog inaccessible for me. Could others be running into this same issue? [...] 20756 22 31_Re: z/OS V8, V9 History madness10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 13 Jan 2010 15:56:11 +0000661_utf-8 Maybe they want you to keep history from SYSTABLESPACESTATS

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L 20779 69 32_Re: Question concerning unixODBC10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 13 Jan 2010 16:12:46 +0000639_utf-8 Hi Jeff,

Are you looking for something different than the odbcinst command?

On Tue, 12 Jan 2010 18:24:11 +0000, Jeff G wrote:

>We've got an installation where a PHP application is accessing DB2 v9.7 >Express C databases (everything is on the same hardware - nothing remote) >using standard odbc functions and everything seems to be working. Simply >update the unixODBC.ini to point to the appropriate driver, add the data >source to the odbc.ini, and we're running. > >However; I'm curious if there is a way to configure unixODBC this so I don't >have to manually enter each database name [...] 20849 358 39_Re: AW: [DB2-L] Facebook account hacked13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 13 Jan 2010 10:21:29 -0600402_ISO-8859-1 I have sent the folks at Toolbox, they own the site that host my blog, a note about all of the problems that have been popping up lately. Occasionally, I'll get an error or nothing will happen when I access the blog. When I try it again just a few minutes later, all is OK... they keep telling me they cannot find a problem. So I cleanup up a bunch of the banners to see if that helps. [...] 21208 23 35_Re: MQT not recognized by optimizer10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 13 Jan 2010 16:03:48 +0000569_utf-8 Have you tried CURRENT REFRESH AGE = 'ANY' when you check the query?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________ [...] 21232 33 11_unsubscribe11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Wed, 13 Jan 2010 11:39:59 -0500620_US-ASCII ***************************************************************************** The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this message in error, please contact the sender and delete the material from any computer. ***************************************************************************** [...] 21266 88 35_Re: MQT not recognized by optimizer23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Wed, 13 Jan 2010 10:59:22 -0600757_ISO-8859-1 I had mentioned that as well and am not sure I ever got a response.

On Wed, Jan 13, 2010 at 10:03, Jorg Lueke wrote:

> Have you tried CURRENT REFRESH AGE = 'ANY' when you check the > query? > > _____________________________________________________________________ > > * IDUG North America * Tampa, Florida, * May 10-14 2010 * > http://IDUG.ORG/NA * > _____________________________________________________________________ > > http://www.idug.org/events/index.html is your DB2 Events calendar! RUG > meetings, > Webcasts, Conferences- what is going on next? > RUG leaders- get your events on the calendar today! > _____________________________________________________________________ > > If you need to [...] 21355 67 34_Re: DB2 V8 and Z/os - Forcing bind10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 13 Jan 2010 17:12:45 +0000708_utf-8 Are you using package versioning?



On Mon, 11 Jan 2010 10:27:44 -0600, Hodgin, Scott wrote:

>Periodically, we need to get an emergency CICS/DB2 program compiled and >bound to our production environment. Many times there are cached threads >that prevent the bind from occurring. Is there a way (short of >terminating all active threads) to force this bind though? Surely 24/7 >shops have a need to force a bind through. > > > > >Scott Hodgin > >Database Administrator, MCITP >South Carolina Farm Bureau Insurance Company > >shodgin@scfbins.com > > > >__________________________________________________________ ___________ > >* IDUG North America * Tampa, [...] 21423 22 35_Re: MQT not recognized by optimizer10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 13 Jan 2010 17:21:52 +0000640_utf-8 Then it's clearly a great question :-)

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L 21446 179 19_Re: DSN1COPY V9 NFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 13 Jan 2010 09:54:11 -0800591_iso-8859-1 I can't use MODEL (totally different physical machines with zero catalog access between them). What seems to happen with DSN1COPY is that with reuse as part of the HURBA being reset, the space allocation is totally freed back to the primary Z/OS specified primary. And then as extents are taken, the Z/OS specified secondary is used. If you use DB2 automatic space management (-1 -1), then the primary quantity no matter the size of the dataset is 2 cylinders and the secondary is 1 cylinder. DB2 never updates the Z/OS vsam info about using a larger and larger secondary as [...] 21626 526 39_Re: AW: [DB2-L] Facebook account hacked35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 13 Jan 2010 13:28:30 -0500475_iso-8859-1 It seems that ISP's can never find a problem on their side...

Joel



Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1 [...] 22153 58 31_GRANTs - an enhancement request9_Ajay Sahu20_ajay_sahu@JBHUNT.COM31_Wed, 13 Jan 2010 17:40:39 +0000595_utf-8 I have a DB2 enhancement request for the IBMers reading this.

Currently we grant select/update access to users/groups/roles at a table level. This means any new table has to have grants performed on it. Typically, for us, all tables in a given subsystem/database have grants performed to the same user/group.

We need a global/system level privilege. So, to borrow the syntax from Sql Server and Oracle, we would want to say, for example, GRANT DataReader TO PUBLIC OR, GRANT SELECT ANY TABLE TO PUBLIC (Public gets read access on all tables in the subsystem/database. ) [...] 22212 917 39_Re: AW: [DB2-L] Facebook account hacked13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 13 Jan 2010 19:50:43 -0000637_iso-8859-1 Of course, it works NOW doesnt it





_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Willie Favero Sent: 13 January 2010 16:21 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: [DB2-L] Facebook account hacked





I have sent the folks at Toolbox, they own the site that host my blog, a note about all of the problems that have been popping up lately. Occasionally, I'll get an error or nothing will happen when I access the blog. When I try it again just a few minutes later, all is OK... they keep telling me they cannot find a problem. So I cleanup up [...] 23130 84 35_Re: GRANTs - an enhancement request13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 13 Jan 2010 19:52:15 -0000460_us-ascii Ajay

You can do this already with external (ie RACF, ACF2 or Top Secret) security instead of DB2s own native security mechanisms, so I doubt if it will be accepted as an enhancement by IBM

Phil G Grainger Database Solutions

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ajay Sahu Sent: 13 January 2010 17:41 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] GRANTs - an enhancement request [...] 23215 35 47_DB2 Omegamon/XE v4.1 External Security question18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV31_Wed, 13 Jan 2010 20:37:58 +0000402_utf-8 I've got a co-worker trying to set up external security for the classic (VTAM) interface for the DB2 Omegamon/XE product using ACF2. The source for the security exit is something he got out of one of the sample libraries that come with this version of Omegamon and it says it's for ACF2. Is there anyone out there doing this with this combination that could answer some questions we've got? [...] 23251 356 39_Re: AW: [DB2-L] Facebook account hacked13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 13 Jan 2010 15:16:17 -0600564_ISO-8859-1 We think we may have discovered the problem... doesn't that sound so positive...

It looks like it might be the Amazon.com ad in the right hand margin that is causing the page load delays. It was been removed.

Now we get to sit back to see if there is any improvement.

Willie

Kurtz wrote: > After reading Mike's mail I tried it another time and after having waited for a verrrrrry long time I got thru as well; though still not ages, it certainly took too long for me to wait the previous times. > > Regards > > Ruediger [...] 23608 919 39_Re: AW: [DB2-L] Facebook account hacked17_Kaminski, Michael39_Michael.Kaminski@TRUSTMARKINSURANCE.COM31_Wed, 13 Jan 2010 15:23:55 -0600416_iso-8859-1 I think someone hit the nail on the earlier when they said that that URL might be blocked from their network.

I could not get into the URL directly and I think it might be because is has 'facebook' in it and that's blocked?

But it I just went to http://it.toolbox.com/blogs/db2zos and then to his blog it worked great. [...] 24528 190 35_Re: GRANTs - an enhancement request12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Wed, 13 Jan 2010 22:31:25 +0100366_iso-8859-1

Nice idea and actually what you can get from using external security mechanisms (we use RACF at our site). Major pros of external security: - You can use wildcards for object names (thus not only "any table", but also "all tables with naming schema IT*" and the like) - Security definitions persist even if an object is dropped and recreated [...] 24719 108 32_Re: Question concerning unixODBC4_Jeff15_jgarmon@UGA.EDU31_Thu, 14 Jan 2010 00:09:22 +0000650_utf-8 No, was just wondering if there was a way for unixODBC to find/register the db2 database names dynamically instead of having to enter them into the .ini.

Thanks Jeff

On Wed, 13 Jan 2010 16:12:46 +0000, Jorg Lueke wrote:

>Hi Jeff, > >Are you looking for something different than the odbcinst command? > >On Tue, 12 Jan 2010 18:24:11 +0000, Jeff G wrote: > >>We've got an installation where a PHP application is accessing DB2 v9.7 >>Express C databases (everything is on the same hardware - nothing >remote) >>using standard odbc functions and everything seems to be working. [...] 24828 135 48_Re: Formatting table row printing and SQL in DB210_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 13 Jan 2010 19:32:21 -0800714_iso-8859-1 You can format your SQL in IBM's Optimization Service Center for DB2 (it's now rebranded as Optim something!) Sudhir RealTime-DBA ________________________________ From: Cuneyt Goksu (Gmail) To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, January 13, 2010 10:24:56 AM Subject: Re: Formatting table row printing and SQL in DB2 How about this one? http://www.dpriver.com/pp/sqlformat.htm Cuneyt > > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David > Sent: 12 January 2010 17:01 > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Formatting table row printing and SQL in DB2 > > Is there a way to turn off formatting in batch SPUFI and > QMF? [...] 24964 198 31_Re: z/OS V8, V9 History madness11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 14 Jan 2010 06:55:40 +0100658_ISO-8859-1 nah I think they just "forgot" like they forgot that SYSCOLSTATS also needs a _HIST table nowadays........





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strae 5 40470 Dsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Dsseldorf, HRB 37894 Geschftsfhrung: Siegfried Frst, Gerhard Schubert







Jorg Lueke Gesendet von: IDUG DB2-L 13.01.2010 16:56 Bitte antworten an IDUG DB2-L [...] 25163 467 19_Re: DSN1COPY V9 NFM11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 14 Jan 2010 07:01:45 +0100550_ISO-8859-1 I use MODEL on the TARGET not from the source! Allocate a single dataset and then MODEL it for however many LDSs you need and after a DSN1COPY check backwards through the datasets and delete any with unused HRBAs as these will cause you grief at some time in the future. This clone method is used all the time here and has never caused any allocation problems I am aware of. It also works with VSAM copies but then the clusters must be STOPed of course. DSNUM is *never* accurate as it needs a RUNSTATS and can change the millisecond [...] 25631 92 19_UDF with scalar SQL11_Michel Helg24_michel.helg@AXA-TECH.COM31_Thu, 14 Jan 2010 09:10:05 +0000381_utf-8 Hi all

I have tried to implement a UDF with SQL (DB2 z/OS, V9.1 NFM). The samples that I could find in the manuals (and Listserv) are pretty simple and I wonder whether it is also possible to use an SQL which is a bit more complex and uses *SQL control statements* and *cursors*. Here my example which works with a simple SQL but doesn't work with the SQL below. [...] 25724 69 27_Re: Facebook account hacked13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 14 Jan 2010 10:55:21 +0100322_iso-8859-1 Willie, I didn't know you had an uncle in the Nigerian government that needed help transferring funds out of the country. Of course I'll help. I'll send you the 45.33 needed to clear the handful of formalities and am looking forward to receiving the promised 5% of the 400K you'll be transferring to me. [...] 25794 51 43_Which is better? "BETWEEN" vs "<=" and >"="12_Sheldon Rich16_zisher@GMAIL.COM31_Thu, 14 Jan 2010 13:41:02 +0000307_utf-8 As part of our DB2 performance review another interesting question has developed. Which is the most efficient way to retrieve rows that are between two values. Method 1: Select * from mytab where cola between :hv1 and :hv2

Method 2: Select * from mytab where cola >= :hv1 and cola <= :hv2 [...] 25846 287 19_Re: DSN1COPY V9 NFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 14 Jan 2010 06:02:15 -0800629_iso-8859-1 In order to use MODEL, the target and the source must be on shareable catalogs. That is the VSAM information for the model that the target is using must come from a VSAM VVDS. If that VSAM VVDS is unaccessable,then MODEL will not work. I take it that you don't define any tablespaces with PRIQTY and SECQTY of -1. Because that's where you run into problems using DSN1COPY. Unfortunately, I'm a user of another's computer so there are no options to buy any tools. ________________________________ From: Roy Boxwell To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, January 14, 2010 1:01:45 AM Subject: Re: [...] 26134 95 21_Re: VCAT HLQ question11_Jerry Boehm22_gboehm@DOT.STATE.NY.US31_Thu, 14 Jan 2010 08:58:51 -0500605_US-ASCII FWIW ... In our shop, DASD usage is allocated via SMS (which is HLQ-sensitive) but is charged at a volume level (regardless of the dataset names found on the volume).

Regards, Jerry Boehm NY State Dept of Transportation Albany, NY

>>> On 1/12/2010 at 12:58 AM, jtonchick@AOL.COM wrote:

You might want to check to see how the applications are charged for their dasd usage. I'd bet that they are billed by highlevel. This goes beyond the concept of "ownership" and adds a level of complexity to any proposed change since you'll have to get by in from management to change [...] 26230 37 50_Can I find whether programs had -805 from the log?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Thu, 14 Jan 2010 16:40:00 +020053_us-ascii Any kind of statistical information.

26268 107 35_Re: GRANTs - an enhancement request9_Ajay Sahu20_ajay_sahu@JBHUNT.COM31_Thu, 14 Jan 2010 14:52:14 +0000603_utf-8 Phil and Peter, Thank you for your input. We haven't tried using RACF for this. Another issue with RACF is that it would only work on z/OS, not LUW.



On Wed, 13 Jan 2010 22:31:25 +0100, Peter Suhner wrote:

> >Nice idea and actually what you can get from using external security mechanisms (we use RACF at our site). >Major pros of external security: >- You can use wildcards for object names (thus not only "any table", but also "all tables with naming schema IT*" and the like) >- Security definitions persist even if an object is dropped and [...] 26376 85 47_Re: Which is better? "BETWEEN" vs "<=" and >"="13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 14 Jan 2010 15:01:33 -0000326_us-ascii I know Terry is out there somewhere, but I'm kind of hoping that in the 21st century DB2 is smart enough to know these are equivalent and would rewrite one into the other or at least treat them the same

Now I'll wait for someone to patiently tell me circumstances when they are NOT equivalent statements! [...] 26462 108 47_Re: Which is better? "BETWEEN" vs "<=" and >"="10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 14 Jan 2010 10:04:07 -0500599_US-ASCII As usual, there is no absolute answer. I am sure that there are exceptions, but my experience is that the Between with consistently outperform 2 range predicates. This is particularly true if the predicate is a matching column predicate on the index being used to access the table. The difference is not as important if the predicate is applied in an index screen or on a non-indexed predicate. My belief is that the Between predicate has been the recommended approach since it was introduced. The best approach would be to use Explain or Visual Explain (or its successors) to compare [...] 26571 84 47_Re: Which is better? "BETWEEN" vs "<=" and >"="20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 14 Jan 2010 17:06:46 +0200610_US-ASCII Hi,

If you have an index on cola or a composite index starting with cola, both queries will be indexable, Matchcol = 1 and stage 1.

Regards, Cuneyt

> -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of > Sheldon Rich > Sent: Thursday, January 14, 2010 3:41 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"=" > > > As part of our DB2 performance review another interesting > question has > developed. Which is the most efficient way to retrieve rows > that are between > two values. > Method 1: [...] 26656 23 32_Re: Question concerning unixODBC10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 14 Jan 2010 15:26:52 +0000688_UTF-8 Kind of like the register as a system datasource on Windows?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 26680 116 47_Re: Which is better? "BETWEEN" vs "<=" and >"="14_Arnold, Mark S20_Mark.Arnold@BNSF.COM31_Thu, 14 Jan 2010 09:36:09 -0600466_us-ascii An additional question, perhaps out of scope for your purposes, is which option is clearer for the folks writing and maintaining the code. If you are developing coding standards, that's an important question...

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Thursday, January 14, 2010 9:02 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"=" [...] 26797 172 21_Re: VCAT HLQ question10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 14 Jan 2010 16:44:13 +0100416_US-ASCII ...so if there's an ACS routine error and your files go to another volume you don't pay for that space.....? I saw it in the past (files in the wrong volumes I mean).









Jerry Boehm Sent by: IDUG DB2-L 14/01/2010 16.08 Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc [...] 26970 129 55_AW: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 14 Jan 2010 17:02:29 +0100377_iso-8859-1 Hi

I think, although the result set will be the same, the predicates are not equivalent, because they have different filtor factors and I don't believe either, that DB2 will rewrite these predicates. May be, DB2 can use multi-index access for the greater/less equal predicate. From time to time, I got different access paths when I use these predicates. [...] 27100 45 45_CDB Trusted Context and Already Authenticated10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 14 Jan 2010 15:40:00 +0000571_UTF-8 If anyone has any experience with the Communications Database in going from z/os to LUW your feedback will be appreciated! We have verified that we can connect between our different systems, well not Oracle at this point. A key requirement is that users can use the link for both scheduled and adhoc work (the data serves mainly BI). We definitely don't want to start populating USERNAMES table with 300 analysts for 12 or more servers. What they would really like is to pass the userid/pwd in a parm (via somethign like a SAS command) to the other side but I [...] 27146 135 21_Re: VCAT HLQ question0_24_carol.sutfin@REGIONS.COM31_Thu, 14 Jan 2010 10:42:33 -0600482_US-ASCII Happens all the time.

Create the file uses the ACS routines and places it on volume 123456.

Use 3.4 is ISPF to RENAME the file to something else and it will not get moved. It stays on 123456 instead of being on ABCDEF.

Happens here all of the time. Test files in the Production DASD pool because someone changed the name. or worse PROD files in the Test pool that do not get "flashed" to the DR site because they are not on DASD that gets copied. [...] 27282 50 32_Re: Question concerning unixODBC13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 14 Jan 2010 09:50:10 -0700465_ISO-8859-1 Maybe this is a silly question, by why not just use the native DB2 driver for PHP (i.e., ibm_db2) instead of using unixODBC? Then you wouldn't have to set up the DSNs in odbc.ini at all.



Ian Bjorhovde

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 27333 143 21_Re: VCAT HLQ question11_Jerry Boehm22_gboehm@DOT.STATE.NY.US31_Thu, 14 Jan 2010 12:10:28 -0500357_US-ASCII Well, not quite true. What I didn't mention is that we as a database group are not specifically charged. The use of any volumes (which are on a machine hosted by a different state agency) is charged at the agency level (Dept of Transportation). So DOT still gets charged.

Regards, Jerry Boehm NY State Dept of Transportation Albany, NY [...] 27477 48 59_Re: AW: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 14 Jan 2010 18:21:31 +0100360_ISO-8859-1 I, too, think that there is a difference in the calculation of the filter factors, and, probably more important, there will be different default filter factors, if proper filter factors can not be calculated due to the use of host variables and NOREOPT(VARS).

I always recommend to use BETWEEN, if if meets the needs of the application. [...] 27526 224 23_Re: UDF with scalar SQL16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 14 Jan 2010 12:26:25 -0500569_ISO-8859-1 Michel,

You could do what you are wanting to do with a user-defined SQL function in a DB2 for LUW environment, but not with a DB2 for z/OS system. The body of a DB2 for LUW SQL UDF can contain a dynamic compound SQL statement (multiple statements, such as a FOR...DO and a RETURN, delimited by BEGIN and END and treated as one executable block). The body of a DB2 for z/OS SQL scalar UDF, on the other hand, is contained within the RETURN statement itself. The expression in that return statement can't contain a SELECT (neither a fullselect nor [...] 27751 71 21_Re: VCAT HLQ question10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 14 Jan 2010 18:38:24 +0100339_US-ASCII Or someone who has a 'power user' rename file from 'xyz' to 'abc' and then she/he executes a migrate/recall via HSM to move files to another volume. And, if guaranteed space is activated for that pool/name, files are moved to, just to say, some system dasd pack, almost filling it...... . You can easily imagine consequencs. [...] 27823 68 34_Versioning am I missing Something?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 14 Jan 2010 16:38:19 +0000466_UTF-8 In looking at the Admin Guide and SQL Reference it appears that the oldest_version column in systablespace/systablepart can be reset by running a reorg and a modify removing all the image copies in the old version. This does not appear to happen, at least not in all cases. So I took an object and ran Reorg. Then a modfy removing all image copies, followed by a fresh full copy. Still, I see the odlest_version listed at 0 instead of 2? How can this be? [...] 27892 39 26_view to convert data types3_tom16_tabbott@HESC.COM31_Thu, 14 Jan 2010 18:24:44 +0000469_utf-8 We are a DB2 Version 8 z/OS shop.

We are working with a vendor whose ‘shrink wrap client’ can handle integer and character data only. We are being asked to create a view which converts our small integer, decimal, date, timestamp, varchar, etc. columns to integer or character.

We feel this would be inefficient and prone to performance problems. It has always been our standard to have the application handle any necessary data conversion. [...] 27932 39 21_Re: VCAT HLQ question11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 14 Jan 2010 18:37:27 +0000325_- >Use 3.4 is ISPF to RENAME the file to something else and it will not get moved. >It stays on 123456 instead of being on ABCDEF.

When SMS was first introduced, just about 20 years ago, I was one of the proponents of re-driving the ACS routines after re-name. IBM said it was too resource-consumptive, but with: [...] 27972 27 21_Re: VCAT HLQ question11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 14 Jan 2010 18:39:14 +0000514_- >Or someone who has a 'power user' rename file from 'xyz' to 'abc' and then she/he executes a migrate/recall via HSM to move files to another volume.

At least, a recall re-drives the ACS routines, whereas a re-name doesn't.

- Too busy driving to stop for gas!

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 28000 173 23_Re: UDF with scalar SQL10_Dave Nance16_dav1mo@YAHOO.COM31_Thu, 14 Jan 2010 10:58:38 -0800618_iso-8859-1 Hi Michael, As Robert has already pointed out what you are attempting here is not possible as you have written it. What you are really after is a recursive SQL statement. There are many examples on the web, quite a few on DBFORUM as well. David Nance ________________________________ From: Michel Helg To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, January 14, 2010 4:10:05 AM Subject: [DB2-L] UDF with scalar SQL Hi all I have tried to implement a UDF with SQL (DB2 z/OS, V9.1 NFM). The samples that I could find in the manuals (and Listserv) are pretty simple and I wonder whether [...] 28174 77 30_Re: view to convert data types14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 14 Jan 2010 14:03:47 -0500499_us-ascii Tom,

I believe the industry standard in such a case would be to get a different vendor.

Barring that, if this is a business application software package that you're referring to (as opposed to, say, a Telnet "client" for accessing the mainframe), probably what you need to do is set up application-compatible versions of the tables they need and do regular ETL (Extract/Transformation/Load) to populate them and keep them in synchronization with the rest of the company. [...] 28252 42 30_Re: view to convert data types15_Patrick Bossman25_patrick.bossman@GMAIL.COM31_Thu, 14 Jan 2010 19:21:17 +0000558_UTF-8 Hi Tom, I haven't performed a study of what would happen in all the different scenarios. There is a cost to data conversion - but that would not be my biggest worry.

I think you're right that it's prone to performance problems - especially if you plan to join one view to another view. I would also be concerned about predicates against columns from the view that were casted in the select list of the view. Does the predicate get pushed down and applied against the base table? Or does the cast preclude that and so you can't get matching [...] 28295 130 52_Differences in Statistics Taken in Runstats VS Reorg0_26_barbara.j.nigh@US.HSBC.COM31_Thu, 14 Jan 2010 11:30:57 -0800431_US-ASCII Can someone please tell me if the statistics we are collecting during an on-line conditional REORG are the same as the ones we are collecting when we execute the RUNSTATS utility prior to running the conditional REORG? Listed below are the parameters we are using for each utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL [...] 28426 460 56_Re: Differences in Statistics Taken in Runstats VS Reorg13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 14 Jan 2010 15:06:14 -0500515_us-ascii Since you have TABLE(ALL) in the REORG you are gathering cardinality stats on every column in the table. Since you have left out the (ALL) on the RUNSTATS version, you are probably only collecting those stats on the leading columns of indexes.







________________________________________________________________________ ______ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com [...] 28887 29 53_Strange messages from the db2 fault management daemon16_Harold Pritchett14_harold@UGA.EDU31_Thu, 14 Jan 2010 15:15:22 -0500397_ISO-8859-1 I have installed a test server using CentOS 5.4 and db2 9.7.1, both 64 bit versions. I have been getting these messages from the dasusr1 user (sent to itself and re-directed to me)

db2fmd The state of instance 'dasusr1' gcf module '/opt/ibm/db2/V9.7/das/lib/libdb2dasgcf.so.1' is OPERABLE (OFFLINE).

I have seen this before, but don't remember how I made it go away. [...] 28917 328 56_Re: Differences in Statistics Taken in Runstats VS Reorg0_26_barbara.j.nigh@US.HSBC.COM31_Thu, 14 Jan 2010 12:56:03 -0800599_US-ASCII David,

Would the REORG statistics then override the RUNSTATS statistics and vice versa? The access path seemed to change when we reran the RUNSTATS utility after the REORG.

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________

Phone. 831 754 1400 2048 Fax. 831-759-7073 Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com Email. barbara.j.nigh@us.hsbc.com Internet. http://www.hsbc.com ______________________________________________________ [...] 29246 196 33_db2 9.1 cancel thread ur rollback11_Diana Nagel16_dknagel@BEPC.COM31_Thu, 14 Jan 2010 15:43:50 -0600444_us-ascii I started an update transaction on a very large table....it was running, running and not getting done so I cancelled...Well that wasn't a very good idea. Now the transaction is in rollback

KCP0243: WSR WAITING FOR WORKLOAD DEFINITIONS DSNR048I # DSNRBMON UR BACKOUT PROCESSING LOG RECORD 504

This is a test area and I can recover this tablespace from a backup so I can just stop this activity totally. I don't care. [...] 29443 872 56_Re: Differences in Statistics Taken in Runstats VS Reorg13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 14 Jan 2010 17:05:48 -0500404_us-ascii I just dug a little deeper on this and found that (ALL) seems to be the default when you specify TABLE with no other options, so these would seem to be functionally equivalent.





Keep in mind that access paths could change ANY time you rerun stats and the data has changed to some degree. Are you seeing access paths flip-flop when you do the reorg and later runstats? [...] 30316 378 37_Re: db2 9.1 cancel thread ur rollback14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 14 Jan 2010 17:18:00 -0500314_us-ascii 1) Open an ETR with IBM to get information on what "Waiting for Workload Definitions" means - it sounds like the thread is hanging, doing nothing. 2) Can you or Operations or the DB2 System Programmers tell whether the task is actually rolling back? Has it, for example, allocated archive log files? [...] 30695 276 50_New DB2 on Campus ebook -- IBM Data Studio for DB212_Phil Gunning19_pkgunning@GMAIL.COM31_Thu, 14 Jan 2010 17:56:44 -0500965_us-ascii This series is a good reference for those new to DB2 and those wanting to learn about IBM Data Studio for DB2.





https://www.ibm.com/developerworks/wikis/display/db2oncampus/FREE+ebook+-+Ge tting+started+with+IBM+Data+Studio+for+DB2













Phil Gunning

IBM Information Champion

The World Leader in DB2 LUW Consulting

Database Industry Analyst

DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support

Direct +1.610.451.5801

Toll Free 888.241.1070

pgunning@gts1software.com

IBM Business Partner

IBM Authorized Reseller

This message (including attachments, header and footer details) contains confidential information intended for a specific individual or individuals and purpose(s), and is protected by law. If you are not the intended recipient, you should delete this message and any attachments.Any [...]