1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2006, week 1
2 40 51_Re: v8 z/os-looking for reorg rebalance experiences12_Isaac Yassin20_yassini@BEZEQINT.NET30_Sat, 1 Jul 2006 10:29:24 +0200531_us-ascii Hi,
Short story: "not mature, yet" Long story: It goes by rows not by size, and even that has some surprising attributes like having nearly all in last partition, getting different results for different runs with same "input" files, etc.
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bill Brown Sent: Friday, June 30, 2006 2:40 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] v8 z/os-looking for reorg rebalance experiences [...]
43 162 29_Re: z/OS DB2 V8 Justification13_Palko, George16_gpalko@OPERS.ORG30_Sat, 1 Jul 2006 10:47:41 -0400771_iso-8859-1 Bill,
All the information that one needs to justify migrating to V8 is located at the following address.
http://www-306.ibm.com/software/data/db2/zos/roadmap.html
Good luck with your migration. George
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Proctor, William Sent: Friday, June 30, 2006 3:49 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] z/OS DB2 V8 Justification
All,
According to IBM, tentative end of support date for V7 is March, 2008. We have been asked to supply management with a justification as to why we need to go to DB2 V8 (end of support date must not be justification enough). Can anyone please supply [...]
206 78 10_WG: Update35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 3 Jul 2006 08:17:16 +0200445_iso-8859-1 Hi everyone,
the SQL-statement below was sent to me by an application developper and he asked me what was wrong with it. We did some testing and found that if we remove the correlation-name 'T' the statement works. Now according to the V7 & V8 SQL-Reference the use of the correlation-name should not be a problem. Is this an error in the docs or in DB2 ? And on a personal note, I find the error message very misleading. [...]
285 87 15_V8 for z/OS, CM35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 3 Jul 2006 08:22:06 +0200614_iso-8859-1 ooops, hit the send-button too soon. This one's for V8 for z/OS, CM.
-----Ursprüngliche Nachricht----- Von: Kurtz, Rüdiger Gesendet: Montag, 3. Juli 2006 08:17 An: 'DB2 Database Discussion list at IDUG' Betreff: WG: Update
Hi everyone,
the SQL-statement below was sent to me by an application developper and he asked me what was wrong with it. We did some testing and found that if we remove the correlation-name 'T' the statement works. Now according to the V7 & V8 SQL-Reference the use of the correlation-name should not be a problem. Is this an error in the docs or in DB2 [...]
373 69 113_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats-qestion, DB2 V7 for z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 3 Jul 2006 08:35:44 +0200571_us-ascii Hi Mike!
After finishing my coffee and erasing the memory of the football match on Saturday..........I have also run a bunch of tests and see that you are correct! The IBM docu, and I, are wrong... However, in my defense, the CPU comparisons I did were all with FREQVAL and COLGROUPS and I also used the RUNSTATS output and here there is no mention that sampling is on/off. I will change my "Are you a RUNSTATS master?" presentation to reflect the fact that SAMPLE is actually not always on (Which is good news considering how off-base it can be!) I [...]
443 61 113_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats-qestion, DB2 V7 for z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 3 Jul 2006 09:35:44 +0200402_us-ascii Hi Mike, again!
I have just run through my presentation and see that I have not got to change it (Thankfully!) The SAMPLE problem that I mention there is just as valid. To recap, if you use the SAMPLE keyword , I will open a docu update about this, then the generated COLCARDFs can vary significantly even for index columns (This is also incorrectedly stated in the RUNSTATS docu) [...]
505 15 14_Re: WG: Update33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Mon, 3 Jul 2006 03:14:39 -0500571_ISO-8859-1 Rüdiger
AFAIK, you are not allowed, to use the correlation name in the set-clause.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
521 45 22_AW: [DB2-L] WG: Update35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 3 Jul 2006 10:44:35 +0200535_iso-8859-1 Walter,
that's what we found out, too, but then I find the notes in the SQL-Reference p. 1156 misleading:
correlation-name: Can be used within [...] assignment-clause to designate the table or view.
Apparently APAR PK14145 deals with this "problem".
Ruediger
-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Walter Janißen Gesendet: Montag, 3. Juli 2006 10:15 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] WG: Update [...]
567 208 33_Runstats-qestion, DB2 V7 for z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 3 Jul 2006 12:04:13 +0200551_us-ascii Me again....
Yes I completely agree with your "improved" syntax diagram. I did some digging and I see that V6 and V7 had the SAMPLE as an extra keyword and in V8 (and V9) they have the Over and Under the line (Which is wrong, as Mikes tests proved). I have opened a docu probelm for this. I then ran my tests again and did a compare of the COLCARDFs and the actual using SELECT DISTINCTS and see that the values with no SAMPLE are inaccurate and with SAMPLE very innacurate. Example 1370 real values, 1472 without sample and 1859 [...]
776 96 25_Re: Unload/Load LOB > 32K13_Bell, Raymond22_raymond.bell@LANDG.COM30_Mon, 3 Jul 2006 11:42:31 +0100398_iso-8859-1 Um, what? Have I missed something here? Was this in reply to some correspondence on unloading LOBs > 32K? The reason I ask is that on the day of the post I was trying to copy a table with a 10MB BLOB; spooky or what? Anyway, unsurprisingly it failed.
So, what PTFs are you referring to? Is there some magic way of successfully unloading/loading a table with a BLOB? Do tell. [...]
873 342 19_Re: V8 for z/OS, CM13_Richard Fazio21_rfazio@TRANSUNION.COM30_Mon, 3 Jul 2006 07:56:16 -0500601_ISO-8859-1 I think this says it.
APAR Identifier ...... PK14145 Last Changed ........ 05/12/02 QUALIFIED COLUMN NAMES ALLOWED IN V8 NFM SYNTAX FOR INSERT AND AND UPDATE SHOULD RECEIVE -4700 WHEN USED IN V8 COMPAT MODE
Symptom ...... IN INCORROUT Status ........... CLOSED PER Severity ................... 2 Date Closed ......... 05/11/11 Component .......... 5740XYR00 Duplicate of ........ Reported Release ......... 810 Fixed Release ............ 999 Component Name 5740 IBM DATABA Special Notice Current Target Date ..06/01/04 Flags SCP ................... Platform ............ [...]
1216 476 27_AW: [DB2-L] V8 for z/OS, CM35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 3 Jul 2006 15:37:45 +0200291_iso-8859-1 Yep, it sure does, but as I said earlier on, the lines on p. 1156 in SQL reference are misleading; apparently I can't use a correlation-name in the assignment-clause - according to the manual I can, but what the heck, no dump, no problem ;-)
Thanks a lot all the same [...]
1693 38 40_sql code -30090 with Data Replication V86_larsen19_billarsen@YAHOO.COM30_Mon, 3 Jul 2006 07:16:13 -0700281_iso-8859-1 Dear all, we've just installed the product , and get SQLCODE -30090 at the first Apply test , Reason code 18 ; sqlstate 25000. It appears that we have a problem with two phase commit . Binds are done with DRDA option , and VTAM is set to support two phase commit . [...]
1732 205 29_Re: z/OS DB2 V8 Justification13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Mon, 3 Jul 2006 10:30:36 -0500515_us-ascii Seeing that I am stuck indoors today and cannot take out my bike, I thought I would take a quick stab at this.
There are lots of reasons to move to V8 depending on what stuff is going on with your V7 subsystem. We will discuss this next week.
However, two somewhat recent happenings that may make V8 a bit more interesting are the zIIP specialty engine and DB2 9. DB2 V8 is a prereq to being able to take advantage of the zIIP. zIIP is very cool way to lower your z software stack cost. [...]
1938 129 31_Re: AW: [DB2-L] V8 for z/OS, CM13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Mon, 3 Jul 2006 10:44:12 -0500316_us-ascii Forgive me if I missed something here... however...
The new function delivered in DB2 V8 NFM is the ability to qualify the column name on the SET clause with a correlation name. This was not allowed in V7.
The error message is simply telling you that you are using a NFM feature in CM... [...]
2068 82 35_[zOS v7] - SQL not picking up index14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Mon, 3 Jul 2006 12:57:43 -0500808_US-ASCII I have the following query:
SELECT ITM.ITM_NBR ,ITM.ITM_SIZ_DSC ,ITM.ITM_DSC ,WHI.FAC_WHS ,WHI.ITM_NBR ,WHI.RTL_UNT_STR_CSE ,WHI.SPK_PCK_SLT_NBR ,WHI.VEN_CSE_PER_LAY ,WHI.NBR_LAY_PER_PAL ,WHI.TOT_BAL_SHP_CSE ,WHI.RSV_BAL_SHP_CSE ,GIA.ITM_FLM_RAT ,SLT.FAC_WHS ,SLT.SLT_NBR ,FVL.FLD_VAL ,FVL.FLD_VAL_DSC FROM WW44G.WCSSTITM ITM JOIN WW44G.WCSSTWHI WHI ON WHI.ITM_NBR = ITM.ITM_NBR JOIN WW44G.WCSSTGIA GIA ON GIA.ITM_NBR = WHI.ITM_NBR AND GIA.FAC_WHS = WHI.FAC_WHS JOIN SUNSYS.SUNFVL FVL ON FVL.FLD_NME = 'FLD_VAL_DSC' AND FVL.FLD_VAL = GIA.ITM_FLM_RAT AND FVL.CPY_CD = 'G' JOIN WW44G.WCSSTSLT SLT ON SLT.ITM_FAC_WHS = WHI.FAC_WHS AND SLT.ITM_NBR = WHI.ITM_NBR WHERE SLT.FAC_WHS >= '006' AND FVL.FLD_INF = 'R' ORDER BY SLT.FAC_WHS ,GIA.ITM_FLM_RAT ,WHI.ITM_NBR WITH UR OPTIMIZE FOR 17 ROWS [...]
2151 27 43_Running analyzer for DB2 Data Propagator V710_Kathy Long21_kathy.long@KROGER.COM30_Mon, 3 Jul 2006 13:30:09 -0500572_- We are in the process of upgrading Data Propagator from V7 to V8. The migration guide includes a step to run the analyzer tool from a workstation to identify problem registrations or subscriptions. Can someone tell me how important this step is and if is very important, maybe give some assistance in running it? I have tried using the Windows command line and entering asnanalyze -db 'mydatabase' -pw 'mypassword file', but I keep getting an error. When it did work, the asnanalyze is V8 and I need to execute V7 analyze. Any thoughts on how I can get around this [...]
2179 131 39_Re: [zOS v7] - SQL not picking up index12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US30_Mon, 3 Jul 2006 14:39:05 -0400396_us-ascii Mark -
In looking at the explain, the WCSSTGIA table is the first table accessed and since it is first, the values associated to the WHI table (the ones used in the ON statement) are not known. How would DB2 be able to use an index given this info?
Mark
Mark Anzmann Tech Services Manager DHRIS Program - ACS Phone: 410-238-1213 email: manzmann@dhr.state.md.us [...]
2311 168 39_Re: [zOS v7] - SQL not picking up index14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Mon, 3 Jul 2006 14:02:07 -0500432_US-ASCII Mark, That looks like it - a missing predicate. I'll check it out with applications folks. thank you, Mark Vickers Grocers Supply Co 3131 E. Holcombe Blvd. Houston, TX 77021 Tel (713) 842-6541
Mark Anzmann Sent by: DB2 Data Base Discussion List 07/03/2006 01:39 PM Please respond to DB2 Database Discussion list at IDUG [...]
2480 404 44_[Fwd: Fw: [DB2-L] z/OS DB2 V8 Justification]13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Mon, 3 Jul 2006 14:21:52 -0500579_us-ascii Posting for Bernie O'Connor...
----- Forwarded by Bernie OConnor/COR/AXE on 07/03/2006 10:55 AM ----- Bernie OConnor/COR/AXE o DB2 Database Discussion list at IDUG cc DB2 Data Base Discussion List , DB2-L@WWW.IDUGDB2-L.ORG Subject Re: [DB2-L] z/OS DB2 V8 JustificationLink
Bill --
There are plenty of great reasons to move forward, even though there are a few bumps in the road. [...]
2885 78 61_IDUG 2006 - Europe Conference, 2-6 October in Vienna, Austria30_Marcel L=?ISO-8859-1?Q?=E9vy?=24_marcel_idug_db2@YAHOO.FR30_Mon, 3 Jul 2006 17:15:47 -0500495_ISO-8859-1 Hi DB2 User
Go Further at the leading user-driven technical event for DB2 and Informix professionals.
IDUG 2006 - Europe 2-6 October 2006 Vienna, Austria www.IDUG.org
Register today for IDUG 2006 - Europe - download a print-friendly version of our registration form! http://conferences.idug.org/Portals/2/Docs/Euro06Reg.pdf
You can also download a copy of the Advance Programme http://conferences.idug.org/Portals/2/Docs/Euro_06AP.pdf [...]
2964 138 32_DB2 LUW v8.2 - hadr and userexit14_Martin Flavell18_mflavell@I-TCS.COM30_Tue, 4 Jul 2006 11:21:40 +0100321_US-ASCII
Thanks in advance for any help you can give.
We have several HADR pairs of databases now, and have been using the DB2 supplied userexit for use with TSM for a long time.
I thought I had seen that the logs were being archived on whichever side of the HADR pair was the primary. [...]
3103 74 29_Re: [zos v7] Cost of triggers14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 4 Jul 2006 14:20:43 +0100321_US-ASCII Sorry to answer your question with another question, but....
Why are you using triggers for change logging? Is it because you want to see the changes in near-real-time, as records in other DB2 tables or because it is "easier and cheaper" than buying a 3rd party product that can do DB2 log analysis? [...]
3178 24 21_v8 Development Center13_Lee Robertson24_lee_robertson@FSMAIL.NET30_Wed, 5 Jul 2006 05:05:14 -0500468_- Hi ,
I am trying to connect to a v7.2 database using the development center and when I test my connection I am getting an error message
"Connection to failed. Unsupported platform"
can anyone shed any light on this. The CA allows me to create a client connection and tests successfully, yet I cannot do this via the development center. Both the client dev center and the server db are 32 bit versions of their respective releases. [...]
3203 224 20_DB2 ssids on an LPAR14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Wed, 5 Jul 2006 13:01:13 +0100291_- Is there a way of getting a list of all active DB2 subsystems on your LPAR?
I have a Rexx/ISPF DB2 application which can sit on any LPAR, and I want to be able to select the DB2 subsystem I want to run against from a list of active DB2 subsystems on that LPAR. [...]
3428 251 37_Antwort: [DB2-L] DB2 ssids on an LPAR11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 5 Jul 2006 14:13:12 +0200364_iso-8859-1 I would just read the control block chains (all well documented) and it must be easy in REXX...
Get the CVT -> Get the JESCT -> Get the start of SSCVT(JES) chain
Start Loop: Run through the chain until you find a SSCVT(DB2) -> ERLY control block -> if the SCOM pointer has a valid address then this db2 is up. Process the next SSCVT. [...]
3680 147 37_Antwort: [DB2-L] DB2 ssids on an LPAR11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 5 Jul 2006 14:19:51 +0200341_us-ascii Here's some REXX code from listserver 2002 (Should still work :) doesn't quite do what you want, but it gives you a very good start!)
/* Rexx */ /* PROGRAM - DB2ERLY */ /* Coded - Nanc */ /* Date - April MMII */ /* PUPOSE - Mod level of DB2 ERLY code */ /* */ /* */ Arg db2ssid Numeric digits 10 [...]
3828 246 24_Re: DB2 ssids on an LPAR13_Richard Fazio21_rfazio@TRANSUNION.COM30_Wed, 5 Jul 2006 08:19:20 -0500346_US-ASCII There is a great utility called "SHOWMVS". It displays a report with many significant operating system tidbits.
It not only lists all the SSIDs on the LPAR that you are on, it also lists all subsystems (as inactive) in the rest of the Sysplex. MQ Subsystems as well.
You can download it from CBTTAPE.ORG for free. faz [...]
4075 21 51_Re: v8 z/os-looking for reorg rebalance experiences33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Wed, 5 Jul 2006 08:20:33 -0500577_- Bill
I would not say, that I am an experienced user with reorg rebalance, but we did some little tests at our shop. The first time, we executed the utilitiy, we got a S0C7, which was fixed in the maentime (UK12715).
Up to now, we only have index controlled tablespaces (because our third party tool cannot handle v8 features). For these type of tablespaces, the limitkey will not be expanded. What does that mean? If you have an 4-column index, but the limitkey only specified for the first, then the limitkey- values will not be expanded, that a value of [...]
4097 100 29_Re: [zos v7] Cost of triggers15_Gaston, Raymond17_GastonRay@ORU.COM30_Wed, 5 Jul 2006 09:56:14 -0400327_iso-8859-1 Phil.... Just my 2C
We have a third party product at our shop that does DB2 log analysis but we still do "trigger auditing". Our logs aren't kept forever (usually recycled after several weeks). However, a request to see "what changed" 2 years ago is not far fetched.
- Ray Gaston Con Ed / DB2 DBA [...]
4198 140 29_Re: [zos v7] Cost of triggers14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 5 Jul 2006 15:13:27 +0100331_US-ASCII OK understood, and in those cases where you need an "open ended" auditing capability, then triggers may be "best" (assuming you can keep all the audited data forever just in case).
However, many people use trigger auditing even for well defined audit tasks, where log processing would be much more applicable. [...]
4339 26 7_BMC/ARC28_SUBSCRIBE DB2-L Dharmalingam32_DharmalingamMayannan@HBOSPLC.COM30_Wed, 5 Jul 2006 09:30:26 -0500500_ISO-8859-1 Dear All,
We are trying to use BMC ARC for check point restart. Our application program calls many other DB2 programs. Could you please advise how called DB2 programs should be compiled / linked. We are using PL/I DB2 programs as subprograms developed in different mainframe and we don’t have PL/I compiler in our mainframe. We just have PL/I load modules to use as subprograms. In this case, could anyone please advise if BMC/ARC is a feasible option for check point restart. [...]
4366 306 24_Re: DB2 ssids on an LPAR13_Bell, Raymond22_raymond.bell@LANDG.COM30_Wed, 5 Jul 2006 15:56:59 +0100567_iso-8859-1 Marcus mate,
You could try the SDSF (i.e. console) command D OPDATA. It'll show active subsystems, not just DB2 but it will show you what's out there. Dunno if it's exactly what you want but it might do the job.
Oh, and good luck cloning any more subsystems...
Raymond Bell Database Administrator
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Davage, Marcus Sent: 05 July 2006 13:01 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 ssids on an LPAR [...]
4673 161 25_Re: v8 Development Center15_Leon Katsnelson15_leon@CA.IBM.COM30_Wed, 5 Jul 2006 11:03:36 -0400623_US-ASCII Development Center (v8) does not support developing database objects in DB2 v7.2 for Linux, UNIX and Windows databases.
Leon Katsnelson Program Director, Technical Marketing. IBM Toronto Lab tel: (905) 413-2119 Admin Assistant: Cynthia Thomson (905) 413-3632 I blog at: http://blogs.ittoolbox.com/database/talk/ http://DB2onRails.com/ http://db2expressc.blogspot.com/
Lee Robertson Sent by: DB2 Data Base Discussion List 07/05/2006 06:05 AM Please respond to DB2 Database Discussion list at IDUG [...]
4835 21 46_Datapropagator V6.01 compatable with DB2 V7.1?12_Patrick Lyon22_ptlyon@MIDAMERICAN.COM30_Wed, 5 Jul 2006 12:53:40 -0500330_- Hello Listers - I have searched the archives and all of the manuals that I can think of but not finding a definite answer.
Is Datapropagator V 6.1 compatable with Db2 V7.1?
We would like to get out of upgrading Datapropagator with our updgrade to DB2 V7 if possible.
Has anyone done this successfully? [...]
4857 120 29_Re: [zos v7] Cost of triggers14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Wed, 5 Jul 2006 13:42:33 -0500537_US-ASCII Sorry folks, I was out on Monday, so I wanted to answer some of the comments I missed (Phil & Raymond):
We have a 3rd party DB2 Log tool. The problem is that orders can be made well in advance and these could be changed many times over a 3 week period. When they have a problem after it is picked/billed/delivered, they may want the history of a particular line item over that 3 week period. I could use Logmaster to get that, and that would be an expensive exercise, so I tried to get the development folks to tell [...]
4978 121 44_[DB2 z/OS V7] Odd Identity Column behavior ?0_25_LL581@DAIMLERCHRYSLER.COM30_Wed, 5 Jul 2006 15:18:44 -0400473_US-ASCII Dear Esteemed List:
I have a developer who reported some "odd" behavior using an IDENTITY column. This column exists in a table whose sole purpose in life is to be used to generate unique values for use in other tables.
The (claimed) behavior is that successive INSERTs to the table result (based on the IDENT_VAL_LOCAL() function) in values 1, 2, 3, etc. as expected. However, upon reaching the value of 20 the next successive value is ... 41. [...]
5100 211 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?11_James Szabo18_jim.szabo@CORE.COM30_Wed, 5 Jul 2006 16:11:47 -0400433_iso-8859-1 Lock,
The INSERT is being processed on different members of a datasharing group, each of whom, when active, have a range of values cached.
----- Original Message ----- From: LL581@DAIMLERCHRYSLER.COM Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wednesday, July 05, 2006 3:18 PM Subject: [DB2-L] [DB2 z/OS V7] Odd Identity Column behavior ?
Dear Esteemed List: [...]
5312 263 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?0_25_LL581@DAIMLERCHRYSLER.COM30_Wed, 5 Jul 2006 16:11:16 -0400494_US-ASCII James,
Well, okay, I believe you. Still, is this documented somewhere, like in a manual or IDUG presentation? I scanned through the SQL Reference and the Application Programming Guide and couldn't find anything.
Thanks!
- Lock Lyon Compuware Corp
James Szabo Sent by: DB2 Data Base Discussion List 07/05/2006 04:11 PM Please respond to James Szabo [...]
5576 226 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Wed, 5 Jul 2006 16:13:15 -0400476_us-ascii Lock,
Forgive me if I'm telling you something you already know.
The identity clause has a Cache clause which defaults to 20. Does the program insert in batches?
Dave
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of LL581@DAIMLERCHRYSLER.COM Sent: Wednesday, July 05, 2006 3:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [DB2 z/OS V7] Odd Identity Column behavior ? [...]
5803 367 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US30_Wed, 5 Jul 2006 16:16:26 -0400340_US-ASCII Yes it is documented in SQL reference under ALTER TABLE command
From manual.....
[ NO CACHE Specifies that values for the identity column are not preallocated. In a data sharing environment, use NO CACHE if you need to guarantee that the identity values are generated in the order in which they are requested. ] [...]
6171 460 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?0_25_LL581@DAIMLERCHRYSLER.COM30_Wed, 5 Jul 2006 16:20:19 -0400498_US-ASCII Aha! Thanks, all.
Wow, looks like I forgot the all-important RTFM step . . .
Lock Lyon Compuware Corp
Pradeep Gunjala Sent by: DB2 Data Base Discussion List 07/05/2006 04:16 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: [DB2-L] [DB2 z/OS V7] Odd Identity Column behavior ? [...]
6632 402 48_Re: [DB2 z/OS V7] Odd Identity Column behavior ?13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Wed, 5 Jul 2006 16:14:25 -0500622_us-ascii James is correct. Each data sharing member will have its own cached Identity values. Check out Identity's CACHE keyword on the CREATE TABLE statement.
Are you in a data sharing environment?
Willie
LL581@DAIMLERCHRYSLER.COM wrote:
> > James, > > Well, okay, I believe you. Still, is this documented somewhere, like > in a manual or IDUG presentation? I scanned through the SQL Reference > and the Application Programming Guide and couldn't find anything. > > Thanks! > > - Lock Lyon > Compuware Corp > > > > > > James Szabo > Sent by: DB2 Data Base Discussion [...]
7035 104 24_Re: DB2 ssids on an LPAR14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 6 Jul 2006 10:25:20 +1000508_ISO-8859-1 Look in the IDUG Insiders Code Place. I've put REXX/ISPF code there that does what (I think) you want.
James Campbell
On 5 Jul 2006 at 13:01, Davage, Marcus wrote:
> > Is there a way of getting a list of all active DB2 subsystems on your LPAR? > > I have a Rexx/ISPF DB2 application which can sit on any LPAR, and I want to be > able to select the DB2 subsystem I want to run against from a list of active DB2 > subsystems on that LPAR. > > What I have done is this: > > [...]
7140 108 24_Re: DB2 ssids on an LPAR14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Thu, 6 Jul 2006 10:11:52 +0100464_- Sheesh! You guys are great! Thanks to all who replied!
Marcus Davage
(Ray - taking a break from breaking subsystems!)
This e-mail is only for the above addressees. It may contain confidential or privileged information. If you are not an addressee you must not copy, distribute, disclose or use any of the information in it or any attachments. If you have received it in error please notify the sender and delete it immediately. [...]
7249 88 25_Re: v8 Development Center13_Lee Robertson24_lee_robertson@FSMAIL.NET30_Thu, 6 Jul 2006 06:03:25 -0500809_- As I suspected. Thanks for your input.
Regards
Lee
On Wed, 5 Jul 2006 11:03:36 -0400, Leon Katsnelson wrote:
>Development Center (v8) does not support developing database objects in >DB2 v7.2 for Linux, UNIX and Windows databases. > >Leon Katsnelson >Program Director, Technical Marketing. >IBM Toronto Lab >tel: (905) 413-2119 >Admin Assistant: Cynthia Thomson (905) 413-3632 >I blog at: > http://blogs.ittoolbox.com/database/talk/ > http://DB2onRails.com/ > http://db2expressc.blogspot.com/ > > > > >Lee Robertson >Sent by: DB2 Data Base Discussion List >07/05/2006 06:05 AM >Please respond to >DB2 Database Discussion list at IDUG > > >To >DB2-L@WWW.IDUGDB2-L.ORG >cc > [...]
7338 53 50_Re: Datapropagator V6.01 compatable with DB2 V7.1?13_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM30_Thu, 6 Jul 2006 08:11:54 -0400425_us-ascii I don't believe you will have a problem with V6 to V7 where you will have a challenge is when you go to V8 of DpropR. IBM really created a mess for us. Hopefully all your replication is between DB2 and DB2 and not a heterogeneous database. If you have that kind of replication you have my sincerest and deepest sympathy. We are just recovering a migration from v7 to v8 of DpropR and it was and is a nightmare. [...]
7392 77 58_FW: [DB2-L] Datapropagator V6.01 compatable with DB2 V7.1?29_Goodwin, Stanley CTR DISA CDB26_GoodwinS.ctr@MECH.DISA.MIL30_Thu, 6 Jul 2006 08:19:34 -0400583_us-ascii Frank, Could you elaborate on the DpropR situation. We are currently running DB2 V7 and Websphere Information Integrator V8.2. Does this match the scenario you mentioned below as being a nightmare...
Stan Goodwin SMC Mechanicsburg DB2 Support DSN 430-4335 Outside 717-605-4335 EMAIL: GOODWINS.CTR@MECH.DISA.MIL -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Crissy, Frank Sent: Thursday, July 06, 2006 08:12 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Datapropagator V6.01 compatable with DB2 V7.1? [...]
7470 62 47_Re: Running analyzer for DB2 Data Propagator V713_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM30_Thu, 6 Jul 2006 08:50:13 -0400344_us-ascii Keep in mind that IBM in their infinite wisdom did not make v7 and v8 tools compatible. So you need a desktop with v8 of db2 pe and another deaktop with v7. When you want to run against v8 you have to use v8 desktop. IBM did not make the tools backwards compatible which makes the migration that much more of a painful challenge. [...]
7533 35 50_Re: Datapropagator V6.01 compatable with DB2 V7.1?12_Patrick Lyon22_ptlyon@MIDAMERICAN.COM30_Thu, 6 Jul 2006 08:26:54 -0500529_- On Thu, 6 Jul 2006 08:11:54 -0400, Crissy, Frank wrote:
>I don't believe you will have a problem with V6 to V7 where you will >have a challenge is when you go to V8 of DpropR. IBM really created a >mess for us. Hopefully all your replication is between DB2 and DB2 and >not a heterogeneous database. If you have that kind of replication you >have my sincerest and deepest sympathy. We are just recovering a >migration from v7 to v8 of DpropR and it was and is a nightmare. > >Frank > [...]
7569 70 50_Re: Datapropagator V6.01 compatable with DB2 V7.1?13_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM30_Thu, 6 Jul 2006 09:44:19 -0400472_us-ascii Patrick,
DpropR migration was and is still a nightmare for us. At least you have the best tested and most used migration being DB2 to DB2 mainframe to mainframe. Believe it or not that is the easiest and least error prone of all the migration possibilities. I wouls strongly suggest just go to v8.2 and don't bother with a bus stop to v7. The migration is painful no question so you may as well feel it once. If you want more feel free to contact me. [...]
7640 30 50_Re: Datapropagator V6.01 compatable with DB2 V7.1?12_Patrick Lyon22_ptlyon@MIDAMERICAN.COM30_Thu, 6 Jul 2006 08:43:44 -0500571_- On Thu, 6 Jul 2006 09:44:19 -0400, Crissy, Frank wrote:
>Patrick, > >DpropR migration was and is still a nightmare for us. At least you have >the best tested and most used migration being DB2 to DB2 mainframe to >mainframe. Believe it or not that is the easiest and least error prone >of all the migration possibilities. I wouls strongly suggest just go to >v8.2 and don't bother with a bus stop to v7. The migration is painful >no question so you may as well feel it once. If you want more feel free >to contact me. > >Frank [...]
7671 314 24_Re: DB2 ssids on an LPAR10_IBMsysProg25_IBMsysProg@GEEK-SITES.COM30_Thu, 6 Jul 2006 10:56:38 -0400389_iso-8859-1 You might want to take a look at the MVS command "D SSI"
It will show defined subsystems (not just DB2) and there status. The command also supports some filtering such as Active or Inactive
Av Friedman ----- Original Message ----- From: James Campbell Sent: Wednesday, July 05, 2006 8:25 PM Subject: Re: DB2 ssids on an LPAR [...]
7986 386 24_Re: DB2 ssids on an LPAR13_Steve Runtsch26_steve.runtsch@ASSURANT.COM30_Thu, 6 Jul 2006 14:32:40 -0500301_ISO-8859-1 I use Mr. Campbell's code as a panel exit on our ISPF DB2 Products Menu to show all available DB2 subsystems. The user selects a subsystem from the list which is then passed on to the selected product - BMC DB2 tools, File-AID, DB2I Primary Options, etc. I have found this very handy. [...]
8373 18 28_DB2 Connect and DB2 Governor13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Thu, 6 Jul 2006 15:19:28 -0500295_- Hello,
We are beginning to use DB2 Connect and, since we want to make sure they don't take over the system, we'd like to set up the DB2 governor to cancel the queries if they exceed a certain amount of CPU time. What columns do we need to fill in for the DB2 Resource Limit Table? [...]
8392 25 45_DB2 v8 and column stats for non-index columns13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Thu, 6 Jul 2006 15:36:17 -0500587_- Hello,
We've heard that the non-key column stats provided by the IBM DSTATS utility prior to DB2 v8 and included in the DB2 v8 RUNSTATS utility are important for the DB2 v8 optimizer and not having them available could adversely impact performance.
Since we're currently on DB2 v7, we're wondering if anyone who's already upgraded to DB2 v8 has any feedback on this. Have you run in to any performance issues if these stats aren't available? If you are using these stats, are they being generated via the IBM Runstats utility or a 3rd party vendor (ie BMC, CA, CDB, [...]
8418 53 32_Re: DB2 Connect and DB2 Governor12_HEPP SHERY C17_schepp@SRPNET.COM30_Thu, 6 Jul 2006 14:11:21 -0700599_us-ascii Donna- the following link is from the doc on governing dynamic queries.
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnagh15/5.5.2.2?SHE LF=dsnshha6.bks&DT=20050721100022#HDRDPGOV1
key thing to remember is proactive only works for category A queries. Category B queries are only be governed by reactive. (you'll need entries for both) you'll also need to calculate the cpu time based on service units. Our sys prog set ours up- had to do some tweaking from what I recall. You can set up for all to default to certain settings and then add entries for exceptions. [...]
8472 50 49_Re: DB2 v8 and column stats for non-index columns13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Thu, 6 Jul 2006 16:58:23 -0500725_ISO-8859-1 I did a paragraph in my blog about this subject last week. It doesn't answer all of your questions, but does have some (what I hope at least) is good information.
http://blogs.ittoolbox.com/database/db2zos/archives/v8-cm-rebind-and-statistics-what-do-they-all-have-in-common-10221
Willie
Donna Domovic wrote:
>Hello, > >We've heard that the non-key column stats provided by the IBM DSTATS >utility prior to DB2 v8 and included in the DB2 v8 RUNSTATS utility are >important for the DB2 v8 optimizer and not having them available could >adversely impact performance. > >Since we're currently on DB2 v7, we're wondering if anyone who's already >upgraded to DB2 v8 has any feedback on [...]
8523 94 49_Re: DB2 v8 and column stats for non-index columns11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM30_Thu, 6 Jul 2006 19:22:55 -0500558_US-ASCII There are MANY issues in this discussion.
What type of SQL are we talking about? Static SQL is only affected at bind/rebind time and only if there is enough information to cause the optimizer to use the statistics. The optimizer has to have either literals or some use of REOPT if you have parameter markers or Host Variables. Most do not have literals, so you are left with the REOPT options. You could turn static SQL into dynamic by re-optimizing all the time. You could do it the first time and then you are left at the mercy of the [...]
8618 109 54_V8 for z/OS CM, High wait times from DSNAPRH/DSNXEEZ ?35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Fri, 7 Jul 2006 10:22:31 +0200491_iso-8859-1 Henrik,
have you received any answers to your question ? (see below) The archives suggest you haven't, but there might have been the odd off-list answer.
Regards
Ruediger
Esteemed List,
On DB2 V8 CM, z/OS.
We are seeing some excessive wait times originating from the modules DSNAPRH DSNXEEZ
Can anybody shed some light on what could cause DSNXEEZ / DSNAPRH to wait for a very long time ? [...]
8728 150 58_Re: V8 for z/OS CM, High wait times from DSNAPRH/DSNXEEZ ?26_Sorensen Henrik (KSFA 321)33_henrik.sorensen@CREDIT-SUISSE.COM30_Fri, 7 Jul 2006 10:32:38 +0200454_iso-8859-1 Ruediger
After we upgraded Strobe, we did get the correct accounting of the wait time. The actual problem was a long running query.
Henrik
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kurtz, Rüdiger Sent: Friday, July 07, 2006 10:23 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] V8 for z/OS CM, High wait times from DSNAPRH/DSNXEEZ ? [...]
8879 210 27_Re: INDEX SPACE CALCULATION10_Neil Price18_neil.price@TNT.COM30_Fri, 7 Jul 2006 10:56:36 +0100654_US-ASCII I've now posted my spreadsheet to The Code Place.
Regards Neil
----- Forwarded by Neil Price/CORP/TPG on 06/07/2006 12:11 -----
Sysdba AHE Sent by: Neil Price 16/05/2006 17:10
To: DB2 Database Discussion list at IDUG cc: Subject: Re: [DB2-L] INDEX SPACE CALCULATION
Raj,
How do you expect to get meaningful space estimates from just the number of rows, without the sizes of the rows and keys? You could just use a rough estimate such as (number of rows) x (length of row) x 1.5 for the tablespace and something similar for each index - if your figures aren't very [...]
9090 23 32_Re: DB2 Connect and DB2 Governor13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Fri, 7 Jul 2006 07:19:21 -0500591_- All,
Thanks for the information on setting up the resource limit table. I'm actually familiar with that for normal batch jobs, QMF queries, etc. My problem, related to DB2 Connect queries, is that I'm not sure what the plan/package names would be? When we look at Omegamon, the queries come through using the plan name DISTSERV which is actually used for the majority of our TCP/IP connections. Since we want to set specific limits for DB2 Connect, I'm trying to find out plan or collid/package names I should use. Also, I believe DB2 Connect uses Private Protocol but I'm not [...]
9114 77 32_Re: DB2 Connect and DB2 Governor13_Bell, Raymond22_raymond.bell@LANDG.COM30_Fri, 7 Jul 2006 13:31:17 +0100480_iso-8859-1 Dominic,
DISTSERV is, as you've found, the name of the plan most remote requestors (all?) use. If you're looking for non-mainframe DDF access via DB2 Connect the most likely candidate for a collection name is NULLID. I used to think this was what it was called, but it just depends on how the remote DB2 Connect packages were bound. Most people just run them out of the box, so NULLID is what you get when you don't give a collection name during the binds. [...]
9192 469 30_Austin HOTDUG meeting nex week9_Bob Brock28_brock@KRYPTON-CONSULTING.COM30_Fri, 7 Jul 2006 12:03:59 -0500401_iso-8859-1 Heart of Texas DB2 User Group RSVP to Bill Houston at houstonb@us.ibm.comThird Quarterly Meeting Tuesday, July 11, 2006 8:30 AM - 1:00 PM
Location: IBM Briefing Center 11501 Burnett Road (Building 904) Austin, TX
Breakfast and Lunch Sponsor: IBM
08:30 - 9:00 Registration and Breakfast
9:00 - 10:00 DB2 V8 customer migration experiences Kevin Harrison, IBM [...]
9662 40 31_Need IMS-L Listserv Information0_22_DB2information@AOL.COM28_Fri, 7 Jul 2006 13:49:02 EDT551_US-ASCII Please send me information on how to sign on IMS-L List.
TIA Ed.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
9703 92 35_Re: Need IMS-L Listserv Information13_Daley, Norman24_Norman.Daley@CINERGY.COM30_Fri, 7 Jul 2006 14:12:24 -0400921_iso-8859-1 https://po.missouri.edu/cgi-bin/wa?SUBED1=ims-l &A=1
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of DB2information@AOL.COM Sent: Friday, July 07, 2006 1:49 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Need IMS-L Listserv Information
Please send me information on how to sign on IMS-L List.
TIA Ed. --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm [...]
9796 76 32_Re: DB2 Connect and DB2 Governor11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM30_Fri, 7 Jul 2006 14:05:48 -0500381_us-ascii Donna
You might also consider classifying this work through workload manager. This would allow you to lower the priority as CPU is consumed. So short running queries would in effect receive no penalty, but longing running queries could have their priority reduced over time. They will still be running, but will not negatively affect other work in the system. [...]
9873 40 53_File allocation error during a recovery at a DR site.9_Dee Reins24_dee.reins@CENTURYTEL.COM30_Fri, 7 Jul 2006 18:08:57 -0500723_us-ascii This is the error message from the DB2 log.
0545 DSNT311I +DB2P MESSAGE LIMIT EXCEEDED. DISPLAY IS TERMINATED. 00545 DSN9023I +DB2P DSNTDDIS 'DISPLAY DATABASE' ABNORMAL COMPLETION 00545 DSNP009I +DB2P THE FOLLOWING ERROR MESSAGES WERE 531 RECEIVED FOR DEFINE CLUSTER ON PMMR.DSNDBC.DBTR05.TSTM016.J0001.A001 IGD17103I CATALOG ERROR WHILE DEFINING VSAM DATA SET PMMR.DSNDBC.DBTR05.TSTM016.J0001.A001 RETURN CODE IS 28 REASON CODE IS 30 IGG0CLEU IGD306I UNEXPECTED ERROR DURING IGG0CLEU PROCESSING RETURN CODE 28 REASON CODE 30 THE MODULE THAT DETECTED THE ERROR IS IGDVTSCU SMS MODULE TRACE BACK - VTSCU VTSCT VTSCH VTSCG VTSCD VTSCC VTSCR SSIRT SYMPTOM RECORD CREATED, PROBLEM ID IS IGD00004 IGD17219I [...]
9914 163 49_Re: DB2 v8 and column stats for non-index columns13_Richard Fazio21_rfazio@TRANSUNION.COM30_Fri, 7 Jul 2006 08:57:45 -0500519_US-ASCII During our upgrade to V8 we had significant issues with access path changes due to non-indexed column statistics.
In most cases, the impact was to table join order. Some tables need to be accessed AFTER significant filtering due to their "evil" design. Other differences were related to join method.
V7 we did not have the stats; in some cases, the non-indexed stats caused poor access path decisions. V8, it was mandatory; the join order was significantly degraded by NOT having the stats. [...]