1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l February 2010, week 2 2 300 50_Re: Abend S0C4 in DB2 z/OS V9 with OR and NOT LIKE11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 8 Feb 2010 06:54:54 +0100708_ISO-8859-1 managed to get this extra data

DUMP TITLE=S91A,ABND=0C4-00000038,U=HENN ,M=(N),C=910.RDS -R SCTCACH,M=DSNTFRCV,LOC=DSNXGRDS.DSNXODFB+3B1A











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







Todd Burch Gesendet von: IDUG DB2-L 29.01.2010 14:59 Bitte antworten an IDUG DB2-L [...] 303 212 36_Re: DB2 for z/OS V9 Catalog poster ?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 8 Feb 2010 07:05:08 +0100540_ISO-8859-1 and the winner is ...........................................................................



QUEST Software





I just got my posters from them and they look great even if I did spend 20 minutes looking for SYSCAT.SYSDATABASE .... oh well different worlds I guess!!!





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 [...] 516 880 38_Re: IMMEDWRITE YES Performance problem12_Jose Antonio17_jamorcillo@CAM.ES30_Mon, 8 Feb 2010 09:03:03 +0100703_iso-8859-1 Hi!





Thanks a lot for all your suggestions!! We are going to think about setting it only for online plan...but before we are to test it.





Regards,





__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Joel Goldstein - Responsive Systems Enviado el: viernes, 05 de febrero de 2010 15:33 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] IMMEDWRITE YES Performance problem [...] 1397 561 79_Re: Friday survey: How many members are you using in your largest DB2 DS group?12_Jose Antonio17_jamorcillo@CAM.ES30_Mon, 8 Feb 2010 09:05:29 +0100668_iso-8859-1 Hi!





We are a bank saving running a 2-way data sharing as to keep 24x7 availabitiy.





Regards





__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Taddei, Cathy Enviado el: viernes, 05 de febrero de 2010 18:28 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] Friday survey: How many members are you using in your largest DB2 DS group? [...] 1959 406 79_Re: Friday survey: How many members are you using in your largest DB2 DS group?20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Mon, 8 Feb 2010 10:38:29 +0200519_iso-8859-9 I have customers running from 2 way to 4 way DS Groups.





Regards, Cuneyt





From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of hhuang@DCCSH.ICBC.COM.CN Sent: Thursday, February 04, 2010 7:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Friday survey: How many members are you using in your largest DB2 DS group?







It's Friday. :) Here is the survey, How many members are you using in your largest DB2 Date Sharing group? [...] 2366 157 18_DB2mag Winter 200910_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 8 Feb 2010 05:23:07 -0500444_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, February 8th 2010





Winter 2009 issue of DB2mag is available on line:





- DB2mag Winter 2009 (digital version: interactive PDF) - DB2mag Fall 2009 (digital version: interactive PDF) - DB2mag Summer 2009 (digital version: interactive PDF) [...] 2524 1658 27_Re: Group Buffer Pools Size12_Jose Antonio17_jamorcillo@CAM.ES30_Mon, 8 Feb 2010 12:41:10 +0100757_iso-8859-1 Hi Robert!





Thank you very much for such a clear explanation! We are looking through all this values to decide whether to increase GBP size or

leave with 150Mb





Thanks again and congratulations for your blog!









__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Robert Catterall Enviado el: jueves, 04 de febrero de 2010 21:16 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] Group Buffer Pools Size [...] 4183 93 57_Re: Revocation of PUBLIC - how to predetermine the damage13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Mon, 8 Feb 2010 11:49:07 -0000518_us-ascii BUT

What happens if someone tries to access an object between the start of step 2 and the end of step 4?

Because the REVOKE is not yet committed nor rolled back, DB2 will make them wait (and wait)

Phil Grainger Grainger Database Solutions Ltd

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: 07 February 2010 08:27 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage [...] 4277 514 36_Re: DB2 for z/OS V9 Catalog poster ?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Mon, 8 Feb 2010 10:25:59 -0500654_iso-8859-1 Roy, Would you have found it faster if you were looking for SYSIBM.SYSDATABASE?? --Phil

________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Monday, February 08, 2010 1:05 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ?



and the winner is ...........................................................................



QUEST Software





I just got my posters from them and they look great even if I did spend 20 minutes looking for SYSCAT.SYSDATABASE .... oh well different worlds I guess!!! [...] 4792 499 79_Re: Friday survey: How many members are you using in your largest DB2 DS group?35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Mon, 8 Feb 2010 10:35:50 -0500738_iso-8859-9 MessageClient with 14-way DS.

Regards, 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 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

tel. (732) 972-1261 fax.(732) 972-9416 ----- Original Message ----- From: Cuneyt Goksu (Gmail) Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, February 08, 2010 3:38 AM Subject: Re: [DB2-L] Friday survey: How many members are you using in your largest DB2 DS group? [...] 5292 598 50_Re: Abend S0C4 in DB2 z/OS V9 with OR and NOT LIKE17_Mortensen, Danial29_danial.mortensen@NEBRASKA.GOV30_Mon, 8 Feb 2010 10:03:56 -0600625_iso-8859-1 You may want to see if this is on your system

APAR Identifier ...... PK65805 Last Changed ........ 08/09/02 ABEND0C4 RC38 IN DSNXODFB+3B1A MAY OCCUR ON BIND OF A QUERY USING A LIKE PREDICATE AND INDEX ON EXPRESSION

Dan Mortensen Sr. Systems DBA



From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Sunday, February 07, 2010 11:55 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Abend S0C4 in DB2 z/OS V9 with OR and NOT LIKE [...] 5891 146 26_IDUG Mentor reward program10_Mark Labby21_mlabby.idug@GMAIL.COM30_Mon, 8 Feb 2010 12:06:51 -0500578_ISO-8859-1 IDUG is very pleased to announce it¹s new IDUG Mentor reward program.

Mentoring is catching on across all industries, but especially in the large IT shops. Over the next 5 to 10 years there will be a huge turnover in personnel as the people who pioneered relational database use start retiring. So, whether because of succession planning, or just the ever changing technology, mentoring is an increasing part of our professional lives. While it takes work, helping someone understand and grow can be a truly rewarding experience for both people involved. [...] 6038 103 47_Re: Which is better? "BETWEEN" vs "<=" and >"="13_Terry Purcell18_tpurcel@US.IBM.COM30_Mon, 8 Feb 2010 17:57:45 +0000332_utf-8 It is true that there are still some differences with how DB2 handles BETWEEN vs ">= AND <=". And it is still on the to-do list to bring these into line - it just hasn't bubbled up high enough.

Rather than focusing on Filter Factor estimation differences, I would first target what gets you matching index access. [...] 6142 474 62_Re: Question about a error from db2 9.5 and PeopleSoft 8.9 LUW16_Robert Catterall21_rfcatterall@GMAIL.COM30_Mon, 8 Feb 2010 15:09:49 -0500283_windows-1252 Duh. Thanks for setting me straight, Andy. I saw the "import... /dev/null" but failed to pick up on that as the LUW equivalent of a mainframe DB2 "LOAD REPLACE with DD DUMMY" process. A case of my reading a DB2 for LUW question with my DB2 for z/OS hat on. My bad. [...] 6617 206 40_DB2 V8, ZOS 1.10: Unable to drop trigger16_Proctor, William25_William.Proctor@TGSLC.ORG30_Mon, 8 Feb 2010 15:50:54 -0600482_us-ascii Thanks for all replies in advance. We have a trigger that is getting -805's and when we look there is no package in the catalog but we do have a definition in SYSTRIGGERS. We cannot rebind (or drop) to create the package because the system say's it doesn't exist. We cannot recreate the trigger because the system says that it already exist. I'm sure that there is a simple fix to this but it has eluded us all afternoon. Can someone tell me what we are missing here? [...] 6824 315 44_Re: DB2 V8, ZOS 1.10: Unable to drop trigger14_Leblanc, Fritz27_fritz.leblanc@SALLIEMAE.COM30_Mon, 8 Feb 2010 17:07:53 -0500439_us-ascii When you issue the FREE command you need to specifically identify the package as belonging to a trigger.

Try FREE TRIGGER PACKAGE name (Not sure about this... it's been a long time.) Or REBIND TRIGGER PACKAGE

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Monday, February 08, 2010 4:51 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8, ZOS 1.10: Unable to drop trigger [...] 7140 414 44_Re: DB2 V8, ZOS 1.10: Unable to drop trigger11_Mann, Darsh24_Darsh.Mann@SALLIEMAE.COM30_Mon, 8 Feb 2010 17:16:03 -0500371_us-ascii Take a look at the following problem tickets at IBM site to see if they apply to your situation:

Record 41918,122,000 Record 40862,122,000

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Leblanc, Fritz Sent: Monday, February 08, 2010 5:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8, ZOS 1.10: Unable to drop trigger [...] 7555 355 43_DB2 connection pooling via Spring/Hibernate14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Mon, 8 Feb 2010 16:27:46 -0600496_us-ascii Esteemed group,





I have a Java application that uses Spring w/hibernate connection pooling. The java task starts running, spring uses hibernate to open a thread to DB2, then proceeds to launch a bunch of non-DB2 work. The amount of work is so great that it exceeds the idle thread timeout value. DB2 Kills the thread and when spring returns, it abends because the thread is gone. I'd rather not change the idle thread timeout value (as it's already one hour). [...] 7911 576 36_Re: Control Center Keyboard Shortcut14_Chris Zampogna34_Chris.Zampogna@STERLINGSAVINGS.COM30_Mon, 8 Feb 2010 14:48:16 -0800520_us-ascii Thanks! Good to hear from you, too, Russel! Please send me a note offline so I can keep track of your email address.



Best Regards,

Chris Zampogna, PMP(r) Program Manager, Enterprise Data Warehouse

Sterling Savings Bank 111 North Wall Street Spokane, WA 99201 Office (509) 625-3402 - VOF(6435) Mobile (509) 368-0686 Fax (509) 458-2391 chris.zampogna@sterlingsavings.com www.sterlingsavingsbank.com [...] 8488 401 36_Re: DB2 for z/OS V9 Catalog poster ?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 9 Feb 2010 09:54:01 +0100505_ISO-8859-1 no - I just had to turn it around!!! The QUEST poster is LUW on one side (SYSCAT.xxxxx) and z/OS on the other side (SYSIBM.xxxx) very tasty indeed!





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 [...] 8890 116 84_Re: *****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 9 Feb 2010 22:26:05 +1100293_US-ASCII Yes. But exactly what will be locked - causing a wait (and wait)? Or do you think I use RR on the SQL?

BTW, I forgot to include synonym and MQT names as something to capture and compare. Which I noticed because, as it happens, tomorrow I have to run my little set of SQL. [...] 9007 49 35_DB2 zOS - Baffled by an Update Lock12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 9 Feb 2010 11:42:37 +0000335_utf-8 Thanks in advance - especially as I'm sure that I'm missing something obvious.

I have a package that performs an Index Only update of a table. The access path is via both columns of the 2 column non-unique clustering index. (I'm not sure why the index is non-unique as there is only ever one row for each index key.) [...] 9057 159 84_Re: *****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Tue, 9 Feb 2010 11:46:15 -0000501_US-ASCII Well, I imagine you will be locking pages in the xxxAUTH tables of the catalog, potentially preventing DB2 doing other authorisation checks until you do your ROLLBACK

Phil Grainger Grainger Database Solutions Ltd

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: 09 February 2010 11:26 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] *****SPAM***** Re: [DB2-L] Revocation of PUBLIC - how to predetermine the damage [...] 9217 44 11_Auto Rebind14_Gwyn Pritchard27_Gwyn.Pritchard@BARCLAYS.COM30_Tue, 9 Feb 2010 12:35:24 +0000560_utf-8 Our Application DBAs have asked me "Under what circumstances will Plans Packages be marked as needing a REBIND". They are particularly interested what effect an ADD COL, Create Index etc , have on their Application.

We are are a zOS shop running DB2 V8 (mainly NF).

SYSPLAN / SYSPACKAGE has a column VALID which will be set to A for Tables which have been altered - I assumed this would always invoke an Auto Rebind at the next execution - but they claim not always and have asked for a definitive list of when it will when it won't.. [...] 9262 342 44_Re: DB2 V8, ZOS 1.10: Unable to drop trigger10_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 9 Feb 2010 07:46:27 -0500382_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...] 9605 42 23_AW: [DB2-L] Auto Rebind12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Feb 2010 13:52:03 +0100638_us-ascii Gwyn,

may be this link can help you:

http://www.idug.org/solutions-journal-content/escaping-the-rebind-blues- in-db2-9-for-zos.html



With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 9648 37 35_Plan Management -DB2 z/os- PLANMGMT12_Bob Jeandron21_bob.jeandron@USDA.GOV30_Tue, 9 Feb 2010 14:05:48 +0000388_utf-8 Any body using this? Looks good to protect yourself during migration. Run into space issues with SPT01?

Would it be possible to: -Turn on PLANMGMT with EXTENDED. -Rebind application, to save original V8 packages -Turn off PLANMGMT, and still be able to SWITCH back to the saved package?

Will test but would like to know if anyone has already been down that road. [...] 9686 70 31_Announcing DB2 10 for z/OS Beta20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Tue, 9 Feb 2010 16:06:17 +0200649_us-ascii For Details:

http://www-01.ibm.com/software/data/db2/zos/db2-10/

Regards, Cuneyt

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * ** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________ http://www.IDUG.org/mentor Mentoring should be a rewarding experience for everyone... IDUG is offering up to $1600 off when you both come to the conference! _____________________________________________________________________ [...] 9757 518 44_Re: DB2 V8, ZOS 1.10: Unable to drop trigger13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Tue, 9 Feb 2010 14:08:18 -0000425_us-ascii Just a thought, and I am not totally sure of the links in the catalog between a trigger and it's package, BUT have you tried binding a package (ANY package) with the same name as the trigger and THEN try dropping thetrigger?





Phil Grainger

Grainger Database Solutions Ltd





phil@graingerdatabasesolutions.com

+44 (0)1298 872148

+44 (0)7505 266768 [...] 10276 100 23_AW: [DB2-L] Auto Rebind35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Tue, 9 Feb 2010 15:22:32 +0100578_iso-8859-1 Hi

My findings are:

. if an object, the package is depenant of is dropped . changing column format . adding a column with datatye DATE, TIME or TIMESTAMP with the clause NOT NULL WITH DEFAULT . adding a partition, alter limitkey or rotate a partition . adding a column to an index . altering an index to PADDED . adding an RI-relatioship with CASCADE or SET NULL (packages will be invalidated, which access the parent tables) . altering a table to a MQT and vice versa . altering a column, which is referenced by User-Defined Function . altering a [...] 10377 80 47_AW: [DB2-L] Plan Management -DB2 z/os- PLANMGMT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Tue, 9 Feb 2010 15:26:36 +0100466_iso-8859-1 Hi

With the ZPARM you can turn it on system-wide. But if you don't use the zparm, you can nevertheless bind packages with PLANMGMT. So if there are copies of certain packages, you can switch back and forth as long as the package copy is valid.

Mit freundlichen Grüßen Walter Janissen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...] 10458 107 39_Re: Plan Management -DB2 z/os- PLANMGMT12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Tue, 9 Feb 2010 08:46:05 -0600442_utf-8 Bob:

There are two ways of implementing Plan Management. One is by turning on the ZPARM and the other is by using the parameter on the rebind control cards. You should apply the fix to compress SPT01, as use of this function will seriously increase the size of SPT01 without that fix. For a migration, we strongly suggest using EXTENDED. This will allow fall back to the original access pathing in your worst case scenario. [...] 10566 87 39_Re: Plan Management -DB2 z/os- PLANMGMT13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Tue, 9 Feb 2010 14:46:03 -0000386_utf-8 To answer your questions randomly...

You can't "turn on and turn off" plan management, it is always ON

What you CAN do, is change the global default in zparm from "OFF" to "EXTENDED" - this means that ANY rebind that doesn't otherwise explicitly specify a plan management option, will get extended (someone could still do a rebind PLANMGMT(OFF) if they wanted) [...] 10654 89 47_AW: [DB2-L] DB2 zOS - Baffled by an Update Lock35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Tue, 9 Feb 2010 15:52:55 +0100431_iso-8859-1 Hi Adam

My understanding always was, that DB2 first acquires an U-lock to read the data and an X-lock, when it tries to update that data. So there seems to be other packages, which hold at least U-locks on that table.

Mit freundlichen Grüßen Walter

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...] 10744 31 34_IBM announces DB2 10 for z/OS Beta13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Tue, 9 Feb 2010 09:01:46 -0600198_ISO-8859-1 I've published a blog entry that summarizes today's DB2 10 for z/OS Beta announcement. Check it out at http://it.toolbox.com/blogs/db2zos/db2-10-for-zos-beta-announced-today-36790

10776 32 19_memu / memusage ddl11_Jeff Agosta20_agost003@MC.DUKE.EDU30_Tue, 9 Feb 2010 15:27:09 +0000296_utf-8 We run memu2 and have the comma delimited output going to a flat file and then port that to xls. We are looking to load the file in db2 for historical purposes and were wondering if someone might have ddl to create a single table for this.

Thanks for your time and help, Jeff A [...] 10809 28 39_Re: Plan Management -DB2 z/os- PLANMGMT12_Bob Jeandron21_bob.jeandron@USDA.GOV30_Tue, 9 Feb 2010 15:16:16 +0000424_utf-8 Thanks for the reply, Phil. Have a ? about the rebind under V8... Was under the impression we could do the rebind under V9 and that would save the V8 package as the "original".

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * ** If you are going to attend only one conference this year, this is it! [...] 10838 118 39_Re: Plan Management -DB2 z/os- PLANMGMT13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Tue, 9 Feb 2010 15:45:41 -0000376_utf-8 Bob very politely pointed out privately that I wasn't concentrating

What I MEANT to say was:

What you CAN do, is change the global default in zparm from "OFF" to "EXTENDED" - this means that ANY rebind that doesn't otherwise explicitly specify a plan management option, will get extended (someone could still do a rebind PLANMGMT(OFF) if they wanted) [...] 10957 171 39_Re: Plan Management -DB2 z/os- PLANMGMT13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 9 Feb 2010 15:55:10 +0000433_utf-8 Additional points.

Remember, the default is OFF. As Gerry and Phil have stated - it can be very beneficial to enable either EXTENDED (preferred choice) or BASIC.

With regard to REBIND with OFF after prior REBIND with BASIC or EXTENDED - OFF will NOT render the previous/original copy as unusable. Therefore, to clarify (and repeat), you CAN REBIND with BASIC, followed by OFF, and followed by REBIND SWITCH. [...] 11129 35 51_Re: AW: [DB2-L] DB2 zOS - Baffled by an Update Lock12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 9 Feb 2010 15:39:23 +0000439_utf-8 Hi Walter. In the case of an index only update, the data page to be updated has already been fully qualified / identified from the index. The only touch on the data page should, therefore, be the actual update with an X lock.

A U lock on a page that may or may not be updated buys concurrency with readers. A U lock on a page that is definitely going to be updated doesn't seems to gain anything. That's why I'm puzzled. [...] 11165 31 27_Re: AW: [DB2-L] Auto Rebind11_Dave Harvey17_db2dave@GMAIL.COM30_Tue, 9 Feb 2010 15:38:50 +0000473_UTF-8 Walter,

A very useful list of possible 'impacts' but for the examples given by Gwyn ("Add col"; "Create index") the simple answer is NO IMPACT.

Mit freundlichen Grüßen und genießen V10 (Bekanntgabe 2 Stunden)

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * ** If you are going to attend only one conference this year, this is it! [...] 11197 204 39_Re: Plan Management -DB2 z/os- PLANMGMT13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Tue, 9 Feb 2010 16:17:30 -0000525_utf-8 Cool, thanks Terry

So it's only a BIND that will automatically eradicate prior versions - that’s neat

Phil Grainger Grainger Database Solutions Ltd

phil@graingerdatabasesolutions.com +44 (0)1298 872148 +44 (0)7505 266768

www.graingerdatabasesolutions.com



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Terry Purcell Sent: 09 February 2010 15:55 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Plan Management -DB2 z/os- PLANMGMT [...] 11402 64 39_Re: Plan Management -DB2 z/os- PLANMGMT12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Tue, 9 Feb 2010 10:29:34 -0600355_utf-8 Bob:

If there is an auto rebind then you need to have the ZPARM set for BASIC or EXTENDED. Other than that the rebind to gain V9 functionality can wait until the migration is done. If you rebind in V8 then you will escape the auto rebind situation.

We strongly suggest running DSNTIJP9 to find any surprises before they find you. [...] 11467 71 67_mystery - disappearing license DB2 ESE v9.5 on SuSe 10.1 under z/VM14_Bruce Lightsey25_Bruce.Lightsey@ITS.MS.GOV30_Tue, 9 Feb 2010 10:47:47 -0600427_US-ASCII had a mystery glitch last night :

the license file on one of my servers disappeared (i.e db2ese_c.lic was nowhere to be found ) and db2licm -l showed that both the ESE and connect licenses were expired.

No work could be processed ( bit of an irritation there )

After putting the license files back on the box and using db2licm -a on each, I now see that the licenses are permanent ( again ). [...] 11539 108 41_Page forward query - non-matching IX scan17_Darren Kilpatrick31_darren.kilpatrick@WELLPOINT.COM30_Tue, 9 Feb 2010 17:48:12 +0000640_utf-8 This query will either have a non-matching index scan or multi-index scan depending on how the OR statements are coded. We are on DB2 9 NFM for z/OS. Any help would be appreciated: SELECT BEN_CD , NTWRK_ID , BEN_NTWRK_EFF_DT , BEN_NTWRK_END_DT , MDR_REIMB_PCT , ACTY_TM_STMP_TM , JOB_TRANS_NM,LST_ACTY_DESC_TXT, SYS_PGM_NM , USE_ID_NO , TRML_ID_NO , OON_PROF_REIMB_FEE_SCHED_CD, OON_PROF_REIMB_FEE_SCHED_TXT FROM IMSX05.BEV40101_BEN_NTWRK WHERE (( BEN_CD = ? AND NTWRK_ID = ? AND BEN_NTWRK_EFF_DT > ? ) OR ( BEN_CD = ? AND NTWRK_ID > ? ) OR ( BEN_CD > ? ) ) ORDER BY BEN_CD , NTWRK_ID , BEN_NTWRK_EFF_DT FETCH FIRST 51 ROWS ONLY [...] 11648 50 51_Antwort: [DB2-L] IBM announces DB2 10 for z/OS Beta12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Feb 2010 19:14:48 +0100453_us-ascii Bon soir, Willie.

Boah, a lot of companies here in Germany are changing there database systems. Truely a lot went over to Oracle 11g. They are leaving the big iron and they are going to another platform - it is LINUX on a 64-bit-machine.

And what I (an old mainframe dino (starting with DB2 V1.3) & IDUG veteran) have seen in the announcement - there is in V10 of DB for z/OS - no real good argument to stay on the big iron. [...] 11699 361 45_Re: Page forward query - non-matching IX scan14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Tue, 9 Feb 2010 12:58:05 -0600447_us-ascii Try the "AND NOT" style predicates which are equivalent but perform better.

WHERE BEN_CD >= ? AND NOT ( BEN_CD = ? AND NTWRK_ID < ? ) AND NOT ( BEN_CD = ? AND NTWRK_ID = ? AND BEN_NTWRK_EFF_DT <= ? )



HTH Khalid







Darren Kilpatrick Sent by: IDUG DB2-L 02/09/2010 11:53 AM Please respond to IDUG DB2-L [...] 12061 215 45_Re: Page forward query - non-matching IX scan13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 9 Feb 2010 19:11:26 +0000578_utf-8 I would strongly suggest against using the "AND NOT" logic. Fortunately I have the benefit of knowing what's coming in the newer release(s) of DB2, and there is an enhancement after DB2 9 that will benefit the original query format, but will not help the "AND NOT" style.

In the meantime, I would suggest adding the following predicate outside of the parentheses: AND BEN_CD >= ? You should then get matching index access (MATCHCOLS=1) on the 03 index with the ORDER BY sort avoided. If you still get multi-index access, then you could add OPTIMIZE FOR 1 ROW. [...] 12277 178 45_Re: Page forward query - non-matching IX scan14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Tue, 9 Feb 2010 13:28:41 -0600419_us-ascii Terry Can tell us a little more about the upcoming enhancement or is it still classified ? Is there a chance of this enhancement being back ported to DB2 9 ? TIA Khalid









Terry Purcell Sent by: IDUG DB2-L 02/09/2010 01:13 PM Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc [...] 12456 38 41_additional backup before DB2/zOS V9 ENFM?11_Gary Martin29_Gary.Martin@LIBERTYMUTUAL.COM30_Tue, 9 Feb 2010 19:30:55 +0000404_utf-8 I'm wondering if anyone is still running an additional backup before migrating from DB2 V9 CM to ENFM. During our DB2 V8 upgrade project, we had taken this precaution (STOP DB2, followed by DFDSS DUMP of catalog tablespaces and indexspaces & various other "system" objects) before migrating to ENFM. Now that we have the CM* mode in V9, I'm questioning if the DFDSS backup is still warranted. [...] 12495 132 45_Re: Page forward query - non-matching IX scan13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 9 Feb 2010 19:45:06 +0000296_utf-8 Khalid,

Unfortunately it is too early to share what is in DB2 10. All I can say is what I mentioned below - that there is an enhancement planned after DB2 9 for the syntax used in the original post. But I cannot commit to which release that will be, nor can I share any detail. [...] 12628 116 42_IBM Announces DB2 10 for z/OS Feb 9th 201014_Surekha Parekh20_surekha21@UK.IBM.COM30_Tue, 9 Feb 2010 22:28:12 +0000713_UTF-8 IBM Announces DB2 10 for z/OS Beta TODAY! The undisputed leader in total system availability, scalability, security and reliability

In today's business and economic environment, the challenge for IT is clear: improve operational efficiencies, reduce costs, and adapt quickly to support business growth -- all without sacrificing the resiliency required for today's demanding business requirements. DB2 for z/OS is the undisputed leader in total system availability, scalability, security, and reliability at the lowest cost per transaction. DB2 10 builds on the formidable capabilities of DB2 9 for z/OS continues to set the standard, delivering key innovations and resource savings, including: [...] 12745 49 37_Access to transaction data in trigger13_Arthur Sannen32_arthur.sannen@SYDNEYWATER.COM.AU30_Tue, 9 Feb 2010 22:28:48 +0000348_utf-8 Hi

env: DB2 V8.1 on z/OS

Is it somehow possible for a trigger to have access to data created in the program that modified the table on which the trigger is defined?

I would like to set a transaction ID in my main program and have all the triggers that get fired during an update to pick up that same transaction ID. [...] 12795 2913 47_IBM Announces DB2 10 for z/OS 9th February 201014_Surekha Parekh20_surekha21@UK.IBM.COM30_Tue, 9 Feb 2010 22:52:03 +00006805_UTF-8 --part-K50-lrpMydH3Ctifn3jvdaopI3fBgv420W2gtF1-HAN2E Content-Transfer-Encoding: base64 Content-Type: APPLICATION/PDF; name="DB2 10 for zOS.pdf"

JVBERi0xLjQNCiXk9tzfDQoyIDAgb2JqDQo8PCAvTGVuZ3RoIDMgMCBSDQogICAvRmlsdGVy IC9GbGF0ZURlY29kZQ0KPj4NCnN0cmVhbQ0KeJydXVuLbUdufj/g/7CfAzmue9WCwbC7d3cy IQNxxpCHkIcQJsOQ2IkNIX8/JdXu29H3qWsF49P2aa26SCrdSlL9egmX8DVe6lG/5sso8etx +e1Pl3/6q8sv3335/m/j7373/R8ef3+7hB9+eLg9fvl1AqcYe5s/c0+1zJ+1jZYuv/35uy8P P333JcqAlzlg+zouvY2v6fLTz3Oo53iJ4/LTv1/++fK7EEMKOZRQQ/vh8i+Xn/7u7bsYvQ8t eOpfGwXvYYQWjnAND+bDUZ15ygv409zSj/PHHx6/fP/3f/nlP17wEV/xoaN9u/s4DmdZjyF9 u5wUywRlH9wMeCre6sNTeDaf1ODMUGKwHxyTGShqY5wfpZhjifXbT3NyZrLAHiVSuMYW+zf0 +PWCMP1vP8uo/3v/zT/+zXdfwuWv4/z1z5fY85xE/+c/L398o+kbh6ezHJ5b/lq+XXeZZ+mV Ct9y0ZmxHf6JA3HnH//7X3952Us+vZcRZC+9zllf55vALzO2eKA537BXTs94yPCcHa+T7g+T x0acnBYf59/c3nP1aYyWw+Hm+IR29w8vm6vvD3sgoq4MIb2OnKZYAqIudCOEQhYyk0+HBfdm mmLOSsdIwc2hjUlww8AfQjYf5IOC262W5oxepkzM4QY+03PLPrNLWnxMwJ+QEnAWZcGHM7qo GvNJCsmhsNFKaVKYz/A8qXCbmCrhMB9miiUg2UtxFnWAZdXqLMtQITWK1BgNcE8OhS1CO1/5 LVq9utQD+8CAHx6Xjon7W8zy0yoxj0/NSc6pc6aIIVolOS2zE5yaJ4U5wQ6gsStfvcFSbh7/ PIIPeuHYiYZ/8vBpYNF5JE90WXC6esG9Rec8cN6BMeDR46Ic69QGT3MnD/ZollrPnIZSuQi+ WWu5TGnKF2aEb3GlqViZGVGjBj6HYY0aBketnDXzQTycRZmDWZMngD/Q4L3uf/7Ln//ntz+9 GABtGQA/Xn4VZd8v0xyaf5YoSjNMer16TL/OvwzTztC556/6Mg4Efkzz9Mv3v/95fnT7r8uP 35gY/W0GxyFzLZB6HPcd5o/2x7coaW2OiaHNUeziNGDYu00zcYgsD7VsNicRu4at6GFy2HXy cprcdgXzFLZxO0uNE6MYuM/xrW/ZttEUpxJjY0+rdWIqhRrD3If1yyZvsE+tZlVuJvNY4E7Z YhrNQPuVzJYymXnuYe7gae6kWJGdRtuntyhltrAy5a/ZyTSg6QdWe4RBefZ5jt4nI4j8TbGB mWrZp0aunW069klvc/Jyj/tHL/dKlxKj1YFDJNPu2EcgoDd7xvIhZu/myCVESqpnwbtVlpVy jtVIKTL+tKCUZ6LV2NP3qdtbzJyK5kyVXBnygPZXwxwDX+UIzlNrEdgT5Xe79s6xXafT/TRn 6VO031Rk9TdD8c3hdjTQyK9a1pVMTVeMoaMhemscViTq/NfMMLpgnawnWSl8KP0JvEQerOaJ fAtWP0Q9y2R8AD44OieFrCM7Rf1Bx29Thz6Dj2o8saQpGDmGDD5jFYuZgVeAzh4cipVwBUgd 0WE6AO7RAFoA4r1ztFq9pt47n2FaS/OIWZc2J5Hc27Pk5pwesZfmmYiPU9YaHZG6c/DATFNY 8IUVG0JKozoIAHbAcNB7DY9T6F1Fj+Kg8Ymd5Cnf+U4+sTElGHuCPlmdLEafpmL8JnaOVZ2e 0LKiXD0NNs9TBJr2OIOzkjy2VCsKKLsT42dH5k4M3ZC7S/ETxR15BJ+4PAz0o4cjwxplAjo4 snKxHIXTeLIG8I8jF6Sv+m/asqcUdRNO2zS+u0zNvrAOY+fjx3XAblM03dSReAYx7qT02p0u 5iBCnc+3BKLObB28ZY5vz9WbMCyZCxr88UhnkBcPlzqew52mUUoXR5UIAZ+O0XT1VCV+ZK63 OOmJXaXDI+lnwjePMxjMJfG5PvLDizM7TQt7TKOeus9mZbfDw78dnseMjz5tjpisN5PHmWNR itxNnmJVCSXuj98yZ9Rpz5RUk5VIaP9yZytxMnNrmy4/X1ST6n9/uLR9jZod/sVcGY4wApua fHoCBzUE58QBP7pGR1aB8SeW+HI+iOotmV/jRLMd6PF+ysXUkqsl6yasUB75vGk8+0mt2ycg ADWAyT7Oeu6Br9EFUeQjEYMaxUEmfnR2ep2S8yo7nCc/29hCSmq34Y+n420/yKrLyWw2upY7 R8XcjR2/NGf8BzBDre4MwKJKrToYAw5E5LwwdXEGimkkZxc2HDrPbKXgHeEpB2dRCE85Br7p hacwwLVUUj+A8vJVQ72PYsWtAZBv0BzOXgoXBOsan1c1mSy5y05fFo80QuPcrSGfjM0/CS9x 7OplVgQBmHLwY28l4xQxnOiGaScPOGSQaPsV5d44sjE3gNkrCFxpaBlCG9G3rgQQLLCaq+wf whaNyKW5pelOvETmrMUSl/3nT0ez2cIn6Wy5sMGxuSISnCAKSO0WhMPg6D19m+z0LvXrdVWu EaEGpGNExOhbEXEdgM29dBXBEHhZnmLDWyEZ6v4cKQzGs8IbVq9FsaA3x06Fci0UDykf+5Se dqHoAnh+oFGaaqMHLgAF2A66VZB+lUaga39AdxXiyeyj8hgUlc8aCXwvsmGMOde8j9xcG0Pu RK3Vmusa+P8pMJIvMCRkRgbHAkMyOD4VX2/AlR2AZK92wjYGp2XJKEZ8Js1F2B19xdPgqj/z mN7w6ftLQlJP1OVPHKZCtSHYTw0nNl8zFXDW+66MMas67PaSrXc6eppSF1gnjNlsiO849nEi hgwTECBePlmKsQTyB7lqEdPznMWTAg7qx5XWA64phqpB8p0RL9OM7xQ66VXI/FNdBnANH7Mq Cvy5jTvHXJ213dCtaVzcS2YABo3k59MZUIwsag43+yRHwJZxhZPJJ+PuL6974Ldb2qt4s8t1 ABq0V4fcVsMNvVQg4FeJsUdwfZoDX7bNsYgaFGWIEUsXZafkXBx8AgLkos4W24vy3g1dBkua BsUZyJ3slc7yyCKoUy05/Aez7d/EeNlISC+HUHINXXDtjTp9Ek8u4VCn6cHmz8UhVgUb7AYr TYJkNJFPQDayKlEGHgFLZ7n7Y0t6CAdIT5oahX5grcjsbNlut0kGI1s/qI5JhzM+YGMNb7Dl VESBaU5Ma2qbAutkUQqABNrC+UuSqwbYxcgO0W7it9sjIsqUz2PTZ1Ly5rA2cMnNmcFsu5Tg HCwTLCtFYvYMvM1/nuNAd2Bl0M/Apg/ntNs0sRooY1gK1CBmJ93w29HEYmqnbqb0CAVnnF6C scWGRNzZF+bQDw77/GJJLvMQpKOoAbI9VczeXoDlF4tYrPvjV1GZDPym6ZxPyGiLw/sQBNCb g2GjkuMRKDC4C5Wo/vZSUpAAHx09PEVQFZnPbFbyY+lmp8P3pDbW47q1tlLfozhIpRGv7ASD pCEXJ/vjj+Yg95NUmnxmJ1nLxBj4WBaijU3EM7TPtTq0f4p9ueNyd2v1jLc8EEpI3sEFoXgK bLNhNKq9vRRJ/6EUB3I/dkdCgczZ5DA7cpFzPUOykg+HZIQtSkvOR+DWuTu0/SSRQHTZCcao MXMBbalRXdFT3+ds7rjnpeJbX5xJS6BhJi2FdTNp2XqAODmkEIDNAko/xAFiwwPwzEcnVRwp chSBGabp3Bm4xC6tJo8cQeBmpHQ+Pi5aoat/fzvyFnXIp8ZfR4qAo8hMl6g2o28FNoLW2LIP UGTmaM4MCV+e1P0ty/XJQcdvqu47ytLNZ/hIEqzooiR8EysKTKaW+OJQpW1zTucNXAZ1B7nI bDmc8ybBQpDnko5+hh7Tv+UnelHDXndmvafan0PbhLBjZP3bXB0hDDzPad9QWks4yxpELuFs yKxHB6XW2e589aDa1hsbmKb5OCVR8+FIVClrsYZQOaMCy3JS6Um7aWKwjet7BxTbN3QWkrPX TnBoaZ7Ui1qYA9KP3eNvwb3TrKFkq6HFbjrBTWI38W1ftQrynClU8l7m0DKFCDRggK62J4Ev 6JQfGogjXxi+1KwdAo2IGZcaI8NLXksHTrXWQfCPupY8TrMOatpWHATLxzAHms6XHtMNJTWr I8ERHbIYMgm484dDobzy/eOTXG7PP/Xmav58CDWBAtLk7TQ9pOccgOjKyVn6/Z5CC0mvKNcr 8o/vDqzkXIG78Ors23p2ITh7a7COImZnhnvtn5WAhZMe1YJ0j3oktkpGt6KsHA5lcHqziEoy /iNY/wrrsxk04RVcFgeXZaGHUqN4jfyjx2id2eQcw3W9c0rG5oB8F3Qo+4GhQfhVI48Q9sTJ lchn2p0yVhU1cDfziFthFtjYSBpEZ0cfZIERZHHQD0FUMrJFkRypdR22OboWXWHgVeL/dM84 bPe0pG/qgN8kqtpDe7PmSKgSb7IjLVa8nyhg6kvUYnOedlD6P2sLg1UKWzQnQDNlbZxMit03 5yuadImBb7i8m4wN7WDNOSAnSE1CO8NgfGbN5tEoU8LIqtQ7bGNmZfQSzFgzpjIGQQYmXfdH M3xL8Gmnnq3AimZgEXiQ0Js5ND7GY/D12PGP5Kym6cWFZBf2AJKYJVsm7k8VNcOTgeOYmzbG 2J+h6TEi4FdQJNj5gkBleXNGL+gYicl6Zv3HagfEMNS084uUBVzvwrWo9QculdRI2Z039eyA 20SF7rEYyI51eBhElo4TPLUSaSn/SgkFcliydlvaniWvWC6ny6dKTrsP7s/Xu4Mxt73Gm0bx DjZQQI4Uk4syq4IaP8wAvAY6OuhlVKrHYdaIb/wYr/JgmBVdjnwCQ0d3OI3Y4/XMKVwNybZP YdW+NJRJgN5dF3FMfp1Sd0EuATdkZtNIB4EG10pyzDA0t4rXzSv56nk65S0MveeXr60KyHw7 eXUPmG4FUDUaZCEf1rsvgmoSDw3/kw81WwWIuTg4ZnR/oI67OqgHtxJS8cDA7fVCKXwXqAV0 5uufFAL3EcnBUp5Wrpj5ZVlAIIwemrN5Le2KQfIY9JJXrVQkhYPDjtoIYPXDQR8XrdFlFCNh orsUJstGseeil6q7VCvVOSoTLQ/AjWnd4XMt2b0HlXu8Z6gCyZbUAmC8eLYCOWu7FxA6UpVo +04Iq5JvQDRXThqDLsp6xmQ5qjNDBU2NgsTo2QeadXCP6gHnLxYPB2B1scphoMvDDqBkkBe+ RAsu4QAGvpojZGWXHlEbiNQdDIJascTxp3jr01KuWpKuR1sCRHP2oCXbgtmxaow1yfQ27UTN ecPsk49IVyY1B9YaCdljuPj4lmb/Uq8z/0b6CBbUOpVzI8g4L4dEiCgi39+ZvJkdw1kuSMyo qTus8S7wgpM520YypzQGfhEV77NQjym1rcPrfGHdC00rYeNbB1SvEBm4zcbUhhRsLTfR7pP1 ruEh2ncktEcFm+lxWiL2k2M4s7XJ3OCgRQdZNoNdw8NsUU82xraqONn4tmd3lWwnBm5qCCSh no8O8u+7Sw2xAEClQo7R2TRwDoMDLsJozIP9IHHpZA2V4SAY9acuHF/oAtzZCcqTb87oUjU+ Js5EdD585K4tpV3Ka0rb+1UcEmszRzo7X5h1D1XZGBpcvg5nbPhUAh/c6trorByAJ6lYZ4t5 iKD+YaWJbeMmau0SA89aOXCLEuJ7nspBqar3vKCMImktPhuq4v7Xb3K/78j9VGBaRPRTMjVs Rr6FuX8M+KWxGKJToR+BFzAku5AtCORGrioB9gHQ87FIWJx9YLm4HA44rBKgxECZWhJ4TSfG b8XZLni7Q3I790fvkjnCiCVVGhY/R3AIBn14jp9nlPKRlmVAFnX3RK027Q5jA9MslX6GzvKU Ex8f1PqndoIOyaUy6CDUHQyhjhH94HRGlQ2J44YnUmZtkrE9S470HNw7L19RbE6kqnPiLDgH vgeFzSetnzlEEvzgyEVtIIbHSyBZUEICTN6D9GjJTqHsAXLmgqNPcLiGE856ZZEzNsoZ0STT /cUk5yR82+LhfWznBH2LZjhQEYzC2dLAiZIMdUBtjk7ALaKOU2pk9UBleIJR0ar9GLdlkrTO 41wEwiY1HWfYrmouOKX0Kfs6hq+o38gnkdJDnX/yLTCd1PjE4MjSKnxlKHFRBToBdyrPouZ8 sQ+tNVf4HlD/hdqcZQHjSW0tAk4KmKLmTu/PMeRCkoG/1rUZFRg4lqw+Xm7wLvEm4s5gSYr1 +YbtYhIf25Y8psNZOUhlkoYk7Ayg2gpNl+PYt7jRZ8/2F1QHXxDopbjsLIYdxG7SnYPPAEyn 7Jyve72cNZycU4aaechNPmU3YDIlaWu6vQcpN6Xjg9zZnBweetTuKXbLNTuUQJ1FWnU4CaSl DWfLMDU7j+pQGqQMR4fSsIEWVx8wMk63qw2DgOkUz4gVsbT4dledEbxEXjly+/NoYhLbiTWd WnZ2AVKGjzNscTfMGBXuTy7Z9ITgnWqQiceXBHTNdBHOEK4mTyKp1jxjDcn7i/g6A7fiZPCo [...] 15709 321 47_Re: DB2 connection pooling via Spring/Hibernate12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Tue, 9 Feb 2010 23:53:22 +0100461_Windows-1252

Faz,

basically depends on the connection pool implementation in use. If "c3p0" (comes with hibernate) is used as a connection pool, these are the options you should look at (in file hibernate.cfg.xml):

hibernate.c3p0.timeout (= maximum idle time in seconds for a connection) hibernate.c3p0.idle_test_period (= idle time in seconds before a pooled connection is validated) hibernate.c3p0.validate exists but is deprecated [...] 16031 175 29_ADMIN recent questions/update16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Tue, 9 Feb 2010 19:18:20 -0500552_utf-8 We have been receiving recently some posts from subscribers questioning whether or not the db2-l list is up.

There are a number of reasons that you may not be receiving any posts that are not related with the listserv itself

1) Your ISP/company has started to think that post from the listserv are spam, and start to block it. Easy way to check if the list is up, is to go to www.idug.org and login the list using the web interface. In this way you can see the latest posts etc, and if need be provide that information to your [...] 16207 582 47_Re: DB2 connection pooling via Spring/Hibernate14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Tue, 9 Feb 2010 18:24:09 -0600598_us-ascii Fantastic! I'll pass on to the designer.





Thanks,

faz





________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Suhner Sent: Tuesday, February 09, 2010 4:53 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 connection pooling via Spring/Hibernate





Faz,

basically depends on the connection pool implementation in use. If "c3p0" (comes with hibernate) is used as a connection pool, these are the options you should look at (in file hibernate.cfg.xml): [...] 16790 291 45_Re: Page forward query - non-matching IX scan10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 10 Feb 2010 01:17:43 +0000310_UTF-8 The AND & NOT method, or Terry's suggestion of adding AND BEN_CD >= ? as a predicate will both perform well unless there are many rows with the same value for the high-order column of the key (BEN_CD). They both have matchcols=1 and will start at the first key with the particular value for BEN_CD. [...] 17082 420 13_ADMIN UOGRADE16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Wed, 10 Feb 2010 02:35:25 -0500373_utf-8 This is a note to let everyone know we are scheduling an upgrade of the software on Saturday Feb 13th from 10PM to Midnight EST (03:00 GMT to 05:00 GMT). We have been remiss in keeping up with versions (wondering how many people here can relate to that).









We will update you with further information as it becomes available [...] 17503 154 55_Re: Antwort: [DB2-L] IBM announces DB2 10 for z/OS Beta10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 10 Feb 2010 10:56:18 +0100609_US-ASCII Hi Peter Georg

The same here.

Mainframe - as I said many times - is almost disappeared in Italy. A lot of public agencies/departments moved to less expensive platforms (LINUX,UNIX) and less expensive databases (My SQL,SQL server, etc). I know at least 5 companies which migrated to LINUX/MySQL or UNIX/ORACLE and at least 2 are medium to big companies. DB2 experts (apart some exceptions) but in general MF experts are payed every year less (even because there's a lot of retired people still working as consultant, a sort of unfair competition) so making MF jobs less attractive. [...] 17658 33 54_DB2 z/os: Difference(s) between SYSL and SYSS packages12_Pete Woodman20_pete.woodman@EDS.COM31_Wed, 10 Feb 2010 12:00:40 +0000372_utf-8 I know that the package SYSSHxxx have cursors defined WITH HOLD and that the SYSSN have cursors defined WITHOUT HOLD.

But does anyone know what the differences between the SYSS and SYSL packages are?

The only difference I can find is that the base section in the SYSIBM.SYSPACKAGES is about 100k for the "L" packages and 20k for the "S" packages. [...] 17692 427 55_Re: Antwort: [DB2-L] IBM announces DB2 10 for z/OS Beta13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Wed, 10 Feb 2010 07:37:11 -0500532_us-ascii Mainframe - as I said many times - is almost disappeared in Italy. The same holds true for Pittsburgh. After the Steel Mills went south along with it was all supporting industries. I don't believe there are more than a 5-6 M/F environments left in the city. When contracting I was involved with 4 data center consolidations which eventually led to the migration to Oracle and offshore. Currently I am working with clients to support environments which are moving off the M/F with no intention of any further upgrades. [...] 18120 515 42_Re: ASN0011E: Reorg without keepdictionary32_Lainez Gutierrez, Maria Victoria21_mvlainez@IBERDROLA.ES31_Wed, 10 Feb 2010 14:42:05 +0100521_iso-8859-1 Hello Pete (Woodman),





After the problem we had with Data Propagator (Reorg wihout keepdictionary) in November, that we solved losing data, we decided to test the procedure you sent us.

-----------------------------------------------------------------------------

I believe that this might work ...





1. Image Copy the Tablespace.

2. Put the in Tablespaces UT.

3. Recover the Tablespace (or Partition) to before the Reorg ran without [...] 18636 72 45_Re: additional backup before DB2/zOS V9 ENFM?11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Wed, 10 Feb 2010 13:53:23 +0000344_UTF-8 Hello,

how long are you planning to stay in ENFM ? In CM I do the following : 1. Stop DB2 2. Taking DFSMS dumps of directory, catalog, active logs and BSDSes 3. Start DB2 in maint mode 4. Full image copy of DB2 directory and catalog 5. Running DSNTIJEN followed with DSNTIJNF to bring the DB2 system to NFM . . Regards, Nenad [...] 18709 122 45_Re: additional backup before DB2/zOS V9 ENFM?13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 10 Feb 2010 14:30:22 -0000347_utf-8 I'd perhaps ask myself the question slightly differently

"Can you spare the time to do a pre-NFM or pre-ENFM backup"? If you can, then don't even think about it just DO IT. If you can fit them in, there really is no such thing as "too many backups"

If you don't think you have the time, then it gets a little more awkward [...] 18832 51 47_Re: DB2 connection pooling via Spring/Hibernate12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Wed, 10 Feb 2010 14:46:14 +0000339_UTF-8 Faz,

just had a discussion with one of our Java guys on that topic.

Actually it looks like the connection pool only keeps track of connections within the pool. Once a connection is checked out to a thread requesting it, neither the connection pool nor hibernate itself do check whether the connection stays valid. [...] 18884 353 45_Re: Page forward query - non-matching IX scan13_Terry Purcell18_tpurcel@US.IBM.COM31_Wed, 10 Feb 2010 16:48:36 +0000327_utf-8 Joe,

While I applaud you for trying to find a more optimal solution, as you point out, this is incredibly dangerous if the access path you expect is not chosen by DB2. According to relational theory, SQL should be written to specify what you want, and allow the DBMS to determine how to satisfy that request. [...] 19238 417 45_Re: Page forward query - non-matching IX scan10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 10 Feb 2010 17:28:58 +0000365_utf-8 Terry, I agree completely. I was trying to illustrate what the ideal process would be and how the different query and predicate types are handled. Safety (if terms of an access path that doesn't change on you) can be more important that absolute speed. The three cursors is what I recommend in those cases where the other techniques are not fast enough. [...] 19656 100 41_Re: Access to transaction data in trigger10_Dave Nance16_dav1mo@YAHOO.COM31_Wed, 10 Feb 2010 10:04:16 -0800650_iso-8859-1 Arthur, Take a look at special registers, see if you can make use of any of them. If not, then you would have to add something else programatically. For instance, update a transaction id column, then using n.tran_id in your triggers. David Nance ________________________________ From: Arthur Sannen To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tue, February 9, 2010 5:28:48 PM Subject: [DB2-L] Access to transaction data in trigger Hi env: DB2 V8.1 on z/OS Is it somehow possible for a trigger to have access to data created in the program that modified the table on which the trigger is defined? I would like [...] 19757 216 42_DB2 V8, ZOS 1.10, need help with procedure16_Proctor, William25_William.Proctor@TGSLC.ORG31_Wed, 10 Feb 2010 12:23:26 -0600513_us-ascii All replies are appreciated. We have a problem with a trigger that has no package that we are unable to get rid of and it is causing several of our online applications to fail. I had an earlier thread on this and a ticket with IBM. The only solution seems to be that we change a zparm to allow updates to catalog tables so that I can delete the trigger definition. Our DBA that normally works with the ZPARMS happens to be on a cruise out in the Gulf of Mexico and I haven't done this in 4 years or [...] 19974 210 45_Re: Page forward query - non-matching IX scan9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Wed, 10 Feb 2010 12:26:53 -0600554_utf-8 Not at v9 yet, otherwise I would have already tried this. Would an index on expression: concatenated columns CHAR(BEN_CD)||CHAR(NTWRKID)||CHAR(BEN_NTWRK_EFF_DT,ISO) and use this same expression in the WHERE predicate be reasonable? WHERE CHAR(BEN_CD)||CHAR(NTWRKID)||CHAR(BEN_NTWRK_EFF_DT,ISO) > :hv?

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Terry Purcell Sent: Wednesday, February 10, 2010 10:49 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Page forward query - non-matching IX scan [...] 20185 43 45_Re: Page forward query - non-matching IX scan10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 10 Feb 2010 19:21:14 +0000344_utf-8 This might work for char, decimal and date columns, but will definitely not work for integer. The CHAR function will left justify an integer with trailing spaces. The number 2 is smaller than the number 13, but '2' collates after '13', so you won't get the rows in the order you are expecting. Decimal columns will have leading 0's. [...] 20229 404 46_Re: DB2 V8, ZOS 1.10, need help with procedure12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Wed, 10 Feb 2010 14:45:17 -0600618_us-ascii Bill:





Mike Bell responded to this offline. But for those who are interested we have free "as is" program on our website that will turn this feature on and off.





If you have any questions contact me at 888-494-9019.





Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com









From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Wednesday, February 10, 2010 12:23 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8, ZOS 1.10, need help with procedure [...] 20634 28 62_DB2 LUW - Any way to find out when a database was catalogued ?10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 10 Feb 2010 21:22:32 +0000563_utf-8 Hi,

DB2 LUW v8.2 - Is there any way to determine when a database or node entry was created in an instance ?

Thanks,

Regards, Kals.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * Your only source for independent, unbiased, and trusted DB2 information. * ** The most DB2 technical sessions of any conference ** Access IBM experts and developers _____________________________________________________________________ [...] 20663 42 66_Re: DB2 LUW - Any way to find out when a database was catalogued ?13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Wed, 10 Feb 2010 14:55:46 -0700586_ISO-8859-1 If you want to know when a database was created,

select min(create_time) from syscat.tables

That should be pretty accurate.

If you want to know when a database was cataloged (i.e. an entry was added to the local or system database directory), I'm pretty sure that is tracked anywhere.



Good luck,



Ian Bjorhovde



On Wed, Feb 10, 2010 at 2:22 PM, Teldb2kals wrote: > Hi, > > DB2 LUW v8.2 - Is there any way to determine when a database or node > entry was created in an instance ? > [...] 20706 70 66_Re: DB2 LUW - Any way to find out when a database was catalogued ?10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 10 Feb 2010 22:14:04 +0000619_utf-8 Thanks, Ian. I am interested in the later. I was also sure it was logged somewhere. But it doesn't seem to be easily accessible anywhere.

Will just wait for any other feedback from the list.

Regards, Kals. On Wed, 10 Feb 2010 14:55:46 -0700, Ian Bjorhovde wrote:

>If you want to know when a database was created, > > select min(create_time) from syscat.tables > >That should be pretty accurate. > >If you want to know when a database was cataloged (i.e. an entry was >added to the local or system database directory), I'm pretty sure that >is tracked anywhere. [...] 20777 39 66_Re: DB2 LUW - Any way to find out when a database was catalogued ?16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Thu, 11 Feb 2010 10:09:43 +0800439_utf-8

You could perhaps check the dates for files in the db and node directories - although that may only show the date it was modified.

Check in the docs for "Directory structure for your installed DB2 database product" for the details of where they hide the files e.g.

Local database directory for the instance: home/db2inst1/db2inst1/NODE0000/sqldbdir System database directory: home/db2inst1/sqllib/sqldbdir [...] 20817 298 174_Upcoming IDUG Webcast - "The DB2Night Show Episode #12 - "The Wild Wonderful World of DB2 Resources", Scott Hayes, DBI Software" - Friday 12 February 2010 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU31_Thu, 11 Feb 2010 15:40:18 +1100817_us-ascii

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________



Hello DB2 List,

Here is an upcoming webcast that you may be interested in.

The DB2Night Show Episode #12 - 12 Feb 2010 - "The Wild Wonderful World of DB2 Resources", Scott Hayes, DBI Software

The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. This live event will run approximately 60 minutes and participants will have the opportunity to pose questions to the presenter. Capacity is [...] 21116 597 59_Re: Abend S0C4 in DB2 z/OS V9 with OR and NOT LIKE - FIXED!11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 11 Feb 2010 07:55:39 +0100864_ISO-8859-1 is now working...however which one of these PTFs actually fixed it I would not like to guess:

UK31158, UK31692, UK34655, UK35971, UK35977, UK36119, UK36872, UK37110, UK37143, UK37292, UK37512, UK37528, UK37604, UK37823, UK37836, UK37972, UK38139, UK38158, UK38163, UK38239, UK38414, UK38627, UK38634, UK38635, UK38637, UK38677, UK38709, UK38711, UK38934, UK38947, UK39139, UK39140, UK39357, UK39400, UK39482, UK39483, UK39497, UK39524, UK39747, UK39783, UK39795, UK39802, UK39805, UK39835, UK39837, UK39844, UK39849, UK39892, UK39992, UK40156, UK40163, UK40265, UK40270, UK40287, UK40347, UK40356, UK40385, UK40498, UK40684, UK40687, UK40717, UK40815, UK40942, UK41007, UK41212, UK41218, UK41370, UK41681, UK41717, UK41817, UK41931, UK41932, UK41933, UK41934, UK41941, UK41942, UK42119, UK42187, UK42189, UK42199, UK42229, UK42300, UK42313, [...] 21714 79 53_AW: [DB2-L] Page forward query - non-matching IX scan35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 11 Feb 2010 09:20:48 +0100745_iso-8859-1 You can use DIGITS instead

Mit freundlichen Grüßen Walter Janißen

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

Vorsitzender des Aufsichtsrates: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht----- Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Joe Geller Gesendet: Mittwoch, 10. Februar 2010 20:21 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] Page forward query - non-matching IX scan [...] 21794 212 78_[DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 11 Feb 2010 09:47:27 +0100426_iso-8859-1 Hi all, we are running a reorg-job on a tablespace with 3.3 millions of rows (one table only) and are specifying about 990.000 when-statements in the discard option; the job, however, was cancelled after more than 24 hours runtime, and the reorg itself had still been in the unload-phase. I know our Italian friend Max had a similar question some years ago but seaarching the archives didn't reveal any clues. [...] 22007 644 82_Re: [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg13_Michael Ebert18_mebert@AMADEUS.COM31_Thu, 11 Feb 2010 09:09:46 +0000534_ISO-8859-1 Not surprising, since DB2 has to match each of 3.3M rows against nearly 1M WHEN conditions. Running a separate DELETE ahead of the REORG should give DB2 better optimisation opportunities.

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







Kurtz, Rüdiger To DB2-L@WWW.IDUGDB2-L.ORG cc

bcc

Subject [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg [...] 22652 50 66_Re: DB2 LUW - Any way to find out when a database was catalogued ?12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 11 Feb 2010 09:36:47 +0000370_UTF-8 Kals,

Information on cataloged databases, nodes and dcs directory are stored in files named sqldbdir, sqlnodir and sqlgwdir respectively.

These files come in some DB2 internal, binary format. Possible that they contain a timestamp of the "CATALOG" command. But even if it exists, I have no idea how to collect this information from these files. [...] 22703 615 82_Re: [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 11 Feb 2010 10:40:50 +0100627_ISO-8859-1 my word! Time for a little ol' DELETE before a ruinous Reorg methinks...





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







Kurtz, Rüdiger Gesendet von: IDUG DB2-L 11.02.2010 09:47 Bitte antworten an IDUG DB2-L [...] 23319 208 46_DB2 10 for z/OS Presentations and Beta Program10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Thu, 11 Feb 2010 04:47:17 -0500560_Windows-1252





Hi DB2 user,

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

Last update on Thursday, February 11th 2010



Here are several articles and presentations about DB2 10 for z/OS (as known as DB2 X) available on several websites:





- DB2 Trends and Directions (Version X) by Gareth Jones





- What's Ahead? DB2 X leads the industry to minimize complexity and maximize productivity by Terrie Jacopi and Jeff Josten [...] 23528 90 82_Re: [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 11 Feb 2010 11:20:15 +0100330_US-ASCII It was a long time ago, in far far galaxy, while fighting against The Dark Side of the Force......which won..... :-(

If I remember well it was due to SQL exceeding the size of previous SQL statement(s) length (32 Kb before V8) and there was no way to manage that problem, with batch stuck in an endless loop. [...] 23619 33 21_TS/IX Space Estimates4_Anil21_alisha_kale@YAHOO.COM31_Thu, 11 Feb 2010 10:32:41 +0000324_utf-8 Hi All !

The DB Admin Tool has the space estimator function, but has allows only one table or one index at a time. Is there a way to invoke it in batch and specify multiple tables/indexes at a time ? Or, does any one have any REXX/CLIST/EXCEL Sheet that can be used for multiple objects at the same time ? [...] 23653 252 90_AW: [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 11 Feb 2010 11:33:37 +0100595_iso-8859-1 Ciao Max,

come stai?

Whether 990.000 "when-"Statements actually make sense is a totally different matter. The cause for the whole trouble was a DELETE-Job that ran for about 30 minutes, which was too long a time-span for our application developpers. So the idea was: since a reorg was scheduled right after the delete anyway, why not combine these two steps ... apparently to no avail. But what really took us aback was the huge amount of time needed by the reorg. If I interpret the figures correctly, then only about 1/3 of the data had been unloaded by the time [...] 23906 1124 82_Re: [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 11 Feb 2010 11:10:02 -0000666_iso-8859-1 Or could you unload with a RETENTION where clause instead? I.e., only unload the rows you want to KEEP (or do you also need the rows you are discarding?)





Phil Grainger

Grainger Database Solutions Ltd





phil@graingerdatabasesolutions.com

+44 (0)1298 872148

+44 (0)7505 266768





www.graingerdatabasesolutions.com





From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michael Ebert Sent: 11 February 2010 09:10 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg [...] 25031 432 46_Re: DB2 V8, ZOS 1.10, need help with procedure16_Proctor, William25_William.Proctor@TGSLC.ORG31_Thu, 11 Feb 2010 05:42:21 -0600459_us-ascii Many thanks to Mike for responding. IBM did get back with me and they came up with a repair job that removed the trigger for me without having to resort to the more drastic steps that I thought we would have to do.





From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gerald Hodge Sent: Wednesday, February 10, 2010 2:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8, ZOS 1.10, need help with procedure [...] 25464 71 25_Re: TS/IX Space Estimates13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 11 Feb 2010 11:51:54 -0000317_utf-8 Hi Anil

I suggest you don't worry about object sizing and let DB2 take care of it - there really is no need any more for DBAs to really care about object sizes

If you insist, I possibly do have my old SuperCalc spreadsheet lying around somewhere, if anyone still has a copy of CAs SuperCalc! [...] 25536 1122 90_AW: [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 11 Feb 2010 12:52:54 +0100362_iso-8859-1 Phil,

since the "old" way of doing the whole stuff was DELETE followed by REORG I should think that the deleted rows are no longer needed, and, as I wrote as a reply to Max's remarks, since 30 minutes are too long for the application people I would rather they split the DELETEs in smaller portions so as to keep the delete-times smaller. [...] 26659 30 26_Generate DBRM from Catalog4_Anil21_alisha_kale@YAHOO.COM31_Thu, 11 Feb 2010 10:45:07 +0000630_utf-8 Hi All !

Is there a way to generate the DBRM from the catalog/directory ? If yes, where can i get some direction as to how to do it.

Thanks.

Anil

Btw, we are talking of DB2 V9 for z/OS

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * If you are going to attend only one conference this year, this is it! * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...] 26690 79 94_Re: AW: [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 11 Feb 2010 13:06:38 +0100435_US-ASCII Hi

I'm fine but under the strong influence of the Dark Side. The weather here is bad, no chance for skiing or for some other nice activity :-(

In my case it was a real loop, no data was unloaded/deleted.

It's a little bit surprising as I deleted via reorg rows from 100+ million rows (V6,V7,V8) with good elapsed times, ok with a by far more simple SQL WHERE. Why SHRLEVEL CHANGE ? Is is mandatory ? [...] 26770 34 25_Re: TS/IX Space Estimates4_Anil21_alisha_kale@YAHOO.COM31_Thu, 11 Feb 2010 12:17:34 +0000392_utf-8 Hi Phil,

I am not trying to determine the PRIQTY/SECQTY (which is what you may be suggesting). I am in the process of capacity planning for one of our projects. I need to request more DASD volumes. And I am expected to calculate the DASD requirement based on the projected usage by DB2 objects. Hence the requirement. I would appreciate if you can send me your excel sheet. [...] 26805 1578 94_Re: AW: [DB2-L] [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 11 Feb 2010 12:44:23 -0000450_iso-8859-1 I was just wondering if you could specify the rows you WANTED with fewer than 900k predicates, a partial unload work work much faster – then you could do a LOAD REPLACE to put the data back





Of course, there is an outage here





Phil Grainger

Grainger Database Solutions Ltd





phil@graingerdatabasesolutions.com

+44 (0)1298 872148

+44 (0)7505 266768 [...] 28384 72 25_Re: TS/IX Space Estimates13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 11 Feb 2010 12:49:51 -0000582_utf-8 Ah - sorry, misunderstood

Unfortunately my spreadsheet was many (many!) years before Excel (or Windows, for that matter!)

I'm sure someone else here will be able to help though

Phil Grainger Grainger Database Solutions Ltd

phil@graingerdatabasesolutions.com +44 (0)1298 872148 +44 (0)7505 266768

www.graingerdatabasesolutions.com



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil Sent: 11 February 2010 12:18 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] TS/IX Space Estimates [...] 28457 207 31_[AD] Generate DBRM from Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 11 Feb 2010 14:02:43 +0100452_ISO-8859-1 Changed it to be AD as the answer is "yes there is and its called DBRM Reconstruct" from us. I am sure other ISVs offer similar features. I have no idea if there is any freeware out there that would do the same...





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 [...] 28665 84 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 11 Feb 2010 07:46:20 -0600496_utf-8 Anil:

V9 presents some issues, but let me list the constraints first. 1.) Is the data in the catalog the result of a bind or a bind copy? If it is a straight bind then the data is there. If a bind copy is done within the same SSID then the data is there. If a bind copy is done between SSIDs then some of the data will be missing. (Ask IBM why) 2.) Is the data in the catalog all EBCIDIC or UNICODE? Some of the data after V8 is still in EBCIDIC and much of the SQL information [...] 28750 751 30_Re: Generate DBRM from Catalog17_Kaminski, Michael39_Michael.Kaminski@TRUSTMARKINSURANCE.COM31_Thu, 11 Feb 2010 08:02:14 -0600380_us-ascii Worked in DB2 V8.1.0..(CM) ... Not sure in Db2 V9.1.0 (might want to contact IBM support, this is what they gave us to use)

Mike K

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil Sent: Thursday, February 11, 2010 4:45 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Generate DBRM from Catalog

Hi All ! [...] 29502 29 56_Re: DB2 zOS - Baffled by an Update Lock - Got the answer12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 11 Feb 2010 14:24:46 +0000314_utf-8 Just in case any one is interested - I've spotted what's happening.

The row is variable length and the index only update causes the row length to increase. The updated row doesn't fit on the current page so DB2 goes page hunting with a U Lock.

So, an Index Only update can obtain U Locks. [...] 29532 97 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 11 Feb 2010 08:32:09 -0600534_us-ascii Mike:

We just found out that DSNREXX does not support LIBDEF, which was a surprise.

If you are in an environment where you cannot use JOBLIB, TSOLIB, or STEPLIB this is a constraint affect DSNREXX use.

Gerald Hodge HLS Technologies, Inc. www.hlstechnologies.com



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kaminski, Michael Sent: Thursday, February 11, 2010 8:02 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Generate DBRM from Catalog [...] 29630 43 41_Re: Access to transaction data in trigger12_Scott Hodgin19_shodgin@SCFBINS.COM31_Thu, 11 Feb 2010 14:55:42 +0000509_utf-8 Arthur, Did you try _created_ global temp tables instead of _declared_ global_ temp tables.

In other words you actually issue CREATE GLOBAL TEMP TABLE schema.TRAN_ID_TRACKER (TRAN ID CHAR(blah)). This CREATE occurs outside of an application and you only need to define it once. The definition is stored in the DB2 catalog, but no space is allocated yet. Only when you start accessing it, is it actually created and I would assume any program (trigger) in that LUW would be able to get to it [...] 29674 73 64_AW: [DB2-L] DB2 zOS - Baffled by an Update Lock - Got the answer35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 11 Feb 2010 15:56:18 +0100700_iso-8859-1 Hi Adam

Great, that you finally found the answer.

I think, I have overlooked, that you said, access was using index only. That means, there couldn't be any other predicate, which made DB2 scanning through different pages.

Mit freundlichen Grüßen Walter Janißen

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

Vorsitzender des Aufsichtsrates: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...] 29748 173 45_Re: additional backup before DB2/zOS V9 ENFM?11_Gary Martin29_Gary.Martin@LIBERTYMUTUAL.COM31_Thu, 11 Feb 2010 15:50:45 +0000316_utf-8 Nenad & Phil - Thanks for reply. Like with our V8 upgrade process, we will immediately follow ENFM with NFM. Our process for V8 was much like the one Nenad has described. I'm not interested in assuming additional risk, nor am I interested in taking actions simply because "we've always done it that way". [...] 29922 77 30_Re: Generate DBRM from Catalog9_Don Leahy19_don.leahy@LEACOM.CA31_Thu, 11 Feb 2010 11:14:39 -0500458_ISO-8859-1 Is there a reason why SDSNLOAD is not in the Linklist?

On Thu, Feb 11, 2010 at 09:32, Gerald Hodge wrote:

> Mike: > > We just found out that DSNREXX does not support LIBDEF, which was a > surprise. > > If you are in an environment where you cannot use JOBLIB, TSOLIB, or > STEPLIB > this is a constraint affect DSNREXX use. > > Gerald Hodge > HLS Technologies, Inc. > www.hlstechnologies.com > > > > [...] 30000 320 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 11 Feb 2010 10:45:38 -0600431_us-ascii Don;





Yes. I am explaining the reasons we are given and that is all. Some sites have so many SSIDs and shared groups that they prohibit many entries into Linklist. This is usually bypassed by use of LIBDEF or other dynamic allocation code. We have found interesting issues with LIBDEF and REXX and verified that DSNREXX does not presently support this. If you want to chat offline, let me know. [...] 30321 146 30_Re: Generate DBRM from Catalog17_Kaminski, Michael39_Michael.Kaminski@TRUSTMARKINSURANCE.COM31_Thu, 11 Feb 2010 11:03:06 -0600426_us-ascii Why not add SDSNLOAD as a STEPLIB in TSO logon proc. And execute the REXX program



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gerald Hodge Sent: Thursday, February 11, 2010 8:32 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Generate DBRM from Catalog

Mike:

We just found out that DSNREXX does not support LIBDEF, which was a surprise. [...] 30468 167 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 11 Feb 2010 11:27:26 -0600439_us-ascii Mike:

Because I am told that is against policy.

Gerald Hodge HLS Technologies, Inc.



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kaminski, Michael Sent: Thursday, February 11, 2010 11:03 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Generate DBRM from Catalog

Why not add SDSNLOAD as a STEPLIB in TSO logon proc. And execute the REXX program [...] 30636 101 45_REXX Problem Using DB2 SQL UPDATE with quotes0_16_DBMSUser@AOL.COM29_Thu, 11 Feb 2010 15:29:47 EST552_US-ASCII Hello List, I have a problem with a db2 update using REXX. My DTEMLIN1 has two quotes in the value - dsn('db2.db.ts')

Code below:

ADDRESS DSNREXX "EXECSQL UPDATE RKI.RKITEMPLATE", 000338 " SET DB2ID = '"||DB2ID||"',", 000339 " DTEMPLAT = '"||DTEMPLAT||"',", 000340 " DTEMLIN1 = '"'||DTEMLIN1||'"',", <======= value is dsn('db2.db.ts') 000341 " DTEMLIN2 = '"||DTEMLIN2||"',", 000342 " DTEMLIN3 = '"||DTEMLIN3||"',", 000343 " DZUSER = '"||DZUSER||"',", 000344 " DDATE = '"||DDATE||"'", 000345 " WHERE DDATE = '"||OLDDATE||"'" [...] 30738 382 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 11 Feb 2010 15:45:29 -0500523_us-ascii I think you've put the concat operators inside your target string on the fourth line. Try this instead.

ADDRESS DSNREXX "EXECSQL UPDATE RKI.RKITEMPLATE", 000338 " SET DB2ID = '"||DB2ID||"',", 000339 " DTEMPLAT = '"||DTEMPLAT||"',", 000340 " DTEMLIN1 = '" || 'DTEMLIN1' || "',", <======= value is dsn('db2.db.ts') 000341 " DTEMLIN2 = '"||DTEMLIN2||"',", 000342 " DTEMLIN3 = '"||DTEMLIN3||"',", 000343 " DZUSER = '"||DZUSER||"',", 000344 " DDATE = '"||DDATE||"'", 000345 " WHERE DDATE = '"||OLDDATE||"'" [...] 31121 112 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes10_Todd Burch17_toddburch@MAC.COM31_Thu, 11 Feb 2010 14:46:11 -0600336_US-ASCII When I have issues like this, I will set a variable to the built clause and SAY it out to see what is getting built.

Todd



On Feb 11, 2010, at 2:29 PM, DBMSUser@AOL.COM wrote:

Hello List, I have a problem with a db2 update using REXX. My DTEMLIN1 has two quotes in the value - dsn('db2.db.ts') [...] 31234 438 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 11 Feb 2010 15:50:35 -0500661_us-ascii Oh - after you've executed that statement, SAY the values for SQLCA, like this:

/*********************************************************************/ 2300_SAY_DB2_ERR_INFO: /*********************************************************************/ SAY 'SQLCODE =' SQLCODE; SAY 'SQLWARN =' SQLWARN.0 SQLWARN.1 SQLWARN.2 SQLWARN.3 SQLWARN.4, SQLWARN.5 SQLWARN.6 SQLWARN.7 SQLWARN.8 SQLWARN.9, SQLWARN.10; SAY 'SQLERRD =' SQLERRD.1 SQLERRD.2 SQLERRD.3 SQLERRD.4 SQLERRD.5, SQLERRD.6; SAY 'SQLERRMC =' SQLERRMC; SAY 'SQLSTATE =' SQLSTATE; SAY 'SQLERRP =' SQLERRP ; RETURN; /*********************************************************************/ [...] 31673 72 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Phil Smith III15_lists@AKPHS.COM31_Thu, 11 Feb 2010 16:07:38 -0500482_us-ascii "Sevetson, Phil" wrote: >I think you've put the concat operators inside your target string on the >fourth line. Try this instead.

Indeed. Couple other tips: 1) Learn to use TRACE R to see what's actually being created/generated. If that's too hard to read, assign the entire thing to a variable and SAY it (though creating variables that are only used once is generally not my idea of good coding in production, for a debugging exercise it makes lots of sense) [...] 31746 591 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Thu, 11 Feb 2010 15:06:45 -0600523_us-ascii It's been a while since I've done anything cool like this :-P





But...I think you need to strip out the quote embedded in your field and re-construct when building the SQL.





Approximate this:





select 'dsn(''db2.db.ts'')'

from sysibm.sysdummy1





So your code would be:

PARSE VAR DTEMLIN1 PT1 "'" PT2 "'" PT3

ADDRESS DSNREXX "EXECSQL UPDATE RKI.RKITEMPLATE",

000338 " SET DB2ID = '"||DB2ID||"',", [...] 32338 134 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes0_22_DB2information@AOL.COM29_Thu, 11 Feb 2010 16:40:44 EST419_US-ASCII When I used: ADDRESS DSNREXX "EXECSQL UPDATE RKI.RKITEMPLATE", " SET DB2ID = '"DB2ID"', ", "DTEMPLAT = '"DTEMPLAT"', ", "DTEMLIN1 = '"DTEMLIN1"', ", "DTEMLIN2 = '"DTEMLIN2"', ", "DTEMLIN3 = '"DTEMLIN3"', ", "DZUSER = '"DZUSER"', ", "DDATE = '"DDATE"' ", "WHERE DDATE = '"||OLDDATE||"'"

I get DTEMLIN1 = 'DSN('DB2.XXX')', and that is what I want in the db2 column but I get sqlcode = -104 as below: [...] 32473 28 61_AUTO: Truls Pedersen is out of office. (returning 15.02.2010)14_Truls Pedersen16_tpeders6@CSC.COM31_Thu, 11 Feb 2010 21:45:47 +0000708_US-ASCII I am out of the office until 15.02.2010.







Note: This is an automated response to your message "?DB2-L Digest - 11 Feb 2010 - Special issue (#2010-37)" sent on 11/2/10 20:45:41.

This is the only notification you will receive while this person is away.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * If you are going to attend only one conference this year, this is it! * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...] 32502 47 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes0_22_DB2information@AOL.COM29_Thu, 11 Feb 2010 16:51:22 EST492_US-ASCII Is there a way to use the VALUES clause with an update like the INSERT?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * If you are going to attend only one conference this year, this is it! * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...] 32550 582 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 11 Feb 2010 17:02:17 -05001130_us-ascii DB2 is probably looking for the string "DTEMLIN1='DSN(''DB2.XXX'')' instead. I tried these two statements in SPUFI:

********************************* Top of Data ********************************** ---------+---------+---------+---------+---------+---------+---------+---------+ SELECT 'DSN(''DB2.XXX'')' FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+---------+

---------+---------+---------+---------+---------+---------+---------+---------+ DSN('DB2.XXX') DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ SELECT 'DSN('DB2.XXX')' FROM SYSIBM.SYSDUMMY1 ; ---------+---------+---------+---------+---------+---------+---------+---------+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 3 0 0 -1 17 502 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = [...] 33133 400 30_Re: Generate DBRM from Catalog9_Don Leahy19_don.leahy@LEACOM.CA31_Thu, 11 Feb 2010 17:18:21 -0500634_ISO-8859-1 Bummer. This policy has effectively disabled DSNREXX, a very valuable and versatile tool that comes free with DB2.

On Thu, Feb 11, 2010 at 12:27, Gerald Hodge wrote:

> Mike: > > Because I am told that is against policy. > > Gerald Hodge > HLS Technologies, Inc. > > > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kaminski, > Michael > Sent: Thursday, February 11, 2010 11:03 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Generate DBRM from Catalog > > Why not add SDSNLOAD as a STEPLIB in TSO logon proc. And execute the > [...] 33534 138 15_Driver Problems0_17_db46@CHRYSLER.COM31_Thu, 11 Feb 2010 17:53:11 -0500355_US-ASCII We have a Java application on a remote box using the 'net' driver to make a connection.

Our production box is at version 8.2.5 and our dev box is now at version 9.5.3

What was done was to copy done many of the .jar or .zip files to this machine, setup the classpath, and use the 'net' driver to make the connection as follows: [...] 33673 731 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 11 Feb 2010 16:58:49 -0600645_us-ascii Don:





If you are not against using code from the CBT tape or have another dynamic allocation solution that will work. Since we create generalize products, I am having each occurrence of DSNREXX rewritten in something else.





I wonder if Microsoft has these issues.





Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com









From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Don Leahy Sent: Thursday, February 11, 2010 4:18 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Generate DBRM from Catalog [...] 34405 262 19_Re: Driver Problems14_Michael Kalena27_Michael.Kalena@JPMORGAN.COM31_Thu, 11 Feb 2010 18:14:53 -0500462_us-ascii Are you trying to connect to DB2 z/OS?

If so, you would want to use the host name and port for the DB2 subsystem or Data Sharing groups (DVIPA) in your second example (to connect directly to DB2 z/OS).

To connect thru a DB2 Connect Gateway to DB2 z/OS, you'd use the host name and port of the gateway but it would be the port DB2 Connect listens on, usually not 6789. If memory serves that was a special port for the Net driver only. [...] 34668 143 22_SQL PROBLEM. Need help8_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 11 Feb 2010 23:19:14 +0000571_ks_c_5601-1987

Hi Sql experts,





here is a problem I do have and explained below.





I am hoping that some of you might have this kind of sql in past and i need help in this regard.





I have two sqls and both of them are same. The second one is just same as the first one excpet it groups to do a count of number of rows in the first sql based on groups. Is there a possibility that this two can be combined? The need is to find the details value as well the number of occurence of these details [...] 34812 45 19_Re: Driver Problems0_17_db46@CHRYSLER.COM31_Thu, 11 Feb 2010 18:36:28 -0500509_US-ASCII No, these are unix(aix) based servers.

Dean J. Burchill 248-576-3957 (8) 776-3957

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * If you are going to attend only one conference this year, this is it! * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...] 34858 501 30_ADMIN Upgrade and notification16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Thu, 11 Feb 2010 18:46:12 -0500320_utf-8 In view of the planed upgrade of the software, a number of you I am sure are 'worrying' on how will they know if the listserv is up or not (admit it!!!!! It is the first step to recovery)





We have once again using a 'vacation' notification to all you to query to automatically find out. [...] 35360 76 51_FW: DB2-L Status Re: ADMIN Upgrade and notification16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Thu, 11 Feb 2010 18:51:27 -0500469_utf-8 An example of the notification if any one is interested.

But now I would not be able to query again as I will not receive any more info

Just a warning



Robert Galambos CIPP/C CIPP/IT

Compuware Senior Technical Specialist IBM Certified Database Associate IBM Certified DB2 9 for z/OS Database Administration Certified Information Privacy Professional/Canada Certified Information Privacy Professional/Information Technology [...] 35437 299 19_Re: Driver Problems12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 12 Feb 2010 01:18:08 +0100444_iso-8859-1

Dean,



the jcc driver supports different connection types depending on the way you code the connect string. Therefore, if your connect string contains an error, the driver doesn't know how to handle the URL and comes up with the message "no suitable driver".

To connect without a DB2 client, you need a type 4 connection, for which the URL should look like this: jdbc:db2://:/ [...] 35737 328 15_Driver Problems0_17_db46@CHRYSLER.COM31_Thu, 11 Feb 2010 19:45:45 -0500552_US-ASCII Boy, I looked at that url so many times and missed that stupid colon.

Well, it got farther. Now I get:

com.ibm.db2.jcc.DB2Driver Using JDBC connection => jdbc:db2://dctcudbd000.tcc.chrysler.com:6789/impact impact impact com.ibm.db2.jcc.b.tn: [jcc][t4][2043][11550][3.53.70] Exception java.net.Connect Exception: Error opening socket to server dctcudbd000.tcc.chrysler.com/53.233.7.241 on port 6,789 with message: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001

Dean J. Burchill 248-576-3957 (8) 776-3957 [...] 36066 42 28_DB2 10 overview presentation12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 12 Feb 2010 01:17:33 +0000687_utf-8 Here is a techdoc that contains some information and and links to DB2 10 presentations. http://www.ibm.com/support/docview.wss?uid=swg27017960

The presentations are PDF files that have notes in the one version and just the slides in the other: ftp://public.dhe.ibm.com/software/data/db2/zos/presentations/overview/db2- 10-zos-roadshow-2010-notes-slides.pdf ftp://public.dhe.ibm.com/software/data/db2/zos/presentations/overview/db2- 10-zos-roadshow-2010-slides-only.pdf

We'll be using these in events and updating the presentations as we find corrections and improvements. Keep those cards and letters coming. http://www.ibm.com/software/data/db2/zos/events.html [...] 36109 617 19_Re: Driver Problems12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 12 Feb 2010 02:28:22 +0100440_Windows-1252

Well, that looks much better already!

Actually it looks like your server didn't listen on the port you have specified. For your new dev server, please make sure you have: - TCPIP connections activated (db2set DB2CONN=TCPIP) - Specified a port (db2 update dbm cfg using svcename ) - Also ensure you have set authentication on the server (db2 update dbm cfg using srvcon_auth [SERVER | SERVER_ENCRYPT]) [...] 36727 60 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes0_22_DB2information@AOL.COM29_Thu, 11 Feb 2010 20:32:55 EST255_US-ASCII I attempted every suggestions but never got it to work. So I did a delete followed by a add. I got the results I wanted. When I got REXX to like the update then DB2 did not like it. In some cases db2 would have the column name as the value. [...] 36788 587 34_AW: [DB2-L] SQL PROBLEM. Need help35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 12 Feb 2010 10:03:31 +0100587_iso-8859-1 Hi

What's about a join like that:

SELECT X.COL1 ,Y.COL2 ,X.COL3 ,X.COL4 ,'XYZ' ,X.COL5 ,X.COL6 ,X.COL7 ,X.COL8 , Z.COL5 FROM OWNER.TABLE1 X,OWNER.TABLE2 Y inner join (SELECT X.COL1 ,Y.COL2 ,X.COL3 ,X.COL4 ,'XYZ' ,DECIMAL(COUNT(*)) AS COL5 ,X.COL6 ,COL7 ,COL8 FROM OWNER.TABLE1 X,OWNER.TABLE2 Y WHERE X.COL8 = Y.COL9 AND (COL10 = 'TONED' OR COL10 = ' ' OR COL10 = ' ' ) AND X.COL11 = 'C1' GROUP BY X.COL1, Y.COL2, X.COL3, X.COL4, X.COL6, COL7, X. COL8; ) as Z on z.col1 = x,col1 and so on WHERE X.COL8 = Y.COL9 AND (COL10 = 'TONED' OR COL10 = ' ' OR COL10 = [...] 37376 45 31_LUW: Exclusive Alter Table Lock9_Andy Hunt21_andy_hunt@YAHOO.CO.UK31_Fri, 12 Feb 2010 09:47:00 +0000598_iso-8859-1 On LUW... Can anyone point me to any documentation that describes lock compatibilty (or not) with Exclusive Alter Table Lock (n.b. not an Exlcusive Table Lock). Thanks, Andy

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * Your only source for independent, unbiased, and trusted DB2 information. * ** DB2 certification -> no additional charge ** Meet fellow DB2 users and leading DB2 consultants _____________________________________________________________________ [...] 37422 41 58_Re: DB2 z/os: Difference(s) between SYSL and SYSS packages14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Feb 2010 21:32:26 +1100543_US-ASCII L = Large; S = Small

Yes, those are the words in the doco.

James Campbell

On 10 Feb 2010 at 12:00, Pete Woodman wrote:

> I know that the package SYSSHxxx have cursors defined WITH HOLD and > that the SYSSN have cursors defined WITHOUT HOLD. > > But does anyone know what the differences between the SYSS and SYSL > packages are? > > The only difference I can find is that the base section in the > SYSIBM.SYSPACKAGES is about 100k for the "L" packages and 20k for > the "S" packages. > > Also, how do [...] 37464 66 30_Re: Generate DBRM from Catalog14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Feb 2010 21:32:27 +1100606_US-ASCII You don't have access to the TSO READY prompt at logon??

Because if you did you could use the TSOLIB command.

James Campbell

On 11 Feb 2010 at 11:27, Gerald Hodge wrote:

> Mike: > > Because I am told that is against policy. > > Gerald Hodge > HLS Technologies, Inc. > > > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kaminski, Michael > Sent: Thursday, February 11, 2010 11:03 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Generate DBRM from Catalog > > Why not add SDSNLOAD as a STEPLIB in TSO logon proc. And [...] 37531 254 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Feb 2010 21:32:27 +1100650_US-ASCII Did you try " DTEMLIN1 = :DTEMLIN1, " ?

Otherwise, as several people have noted, you have scan every host value looking for apostrophes to double.

James Campbell

On 11 Feb 2010 at 15:29, DBMSUser@AOL.COM wrote:

> > Hello List, > I have a problem with a db2 update using REXX. My DTEMLIN1 hastwo quotes in the > value - dsn('db2.db.ts') > > Code below: > > ADDRESS DSNREXX "EXECSQL UPDATE RKI.RKITEMPLATE", > 000338 " SET DB2ID = '"||DB2ID||"',", > 000339 " DTEMPLAT = '"||DTEMPLAT||"',", > 000340 " DTEMLIN1 = '"'||DTEMLIN1||'"',", <======= value is dsn('db2.db.ts') > 000341" DTEMLIN2 = '"||DTEMLIN2||"',", [...] 37786 67 26_Re: SQL PROBLEM. Need help14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Feb 2010 21:32:28 +1100331_ISO-8859-1 Post the actual SQL. You have managed to conceal the problem.

James Campbell

On 11 Feb 2010 at 23:19, duam lee wrote:

Hi Sql experts,

here is a problem I do have and explained below.

I am hoping that some of you might have this kind of sql in past and i need help in this regard. [...] 37854 536 26_Re: SQL PROBLEM. Need help17_Tunen, Marcel van31_Marcel.van-Tunen@CORUSGROUP.COM31_Fri, 12 Feb 2010 11:54:47 +0100393_US-ASCII try using CTE's

with v1 as ( SELECT X.COL1 ,Y.COL2 ,X.COL3 ,X.COL4 ,X.COL5 ,X.COL6 ,X.COL7 ,X.COL8 FROM OWNER.TABLE1 X,OWNER.TABLE2 Y WHERE X.COL8 = Y.COL9 AND (COL10 = 'TONED' OR COL10 = ' ' OR COL10 = ' ') AND X.COL11 ='C1' ) , v2 as ( SELECT COL1 ,COL2 ,COL3 ,COL4 ,DECIMAL(COUNT(*)) AS COL5 ,COL6 ,COL7 ,COL8 FROM v1 GROUP BY COL1, COL2, COL3, COL4, COL6, COL7, COL8; [...] 38391 510 49_Re: REXX Problem Using DB2 SQL UPDATE with quotes14_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Fri, 12 Feb 2010 07:31:46 -0600531_us-ascii I did not have time to play last night. I just tried this this morning, and it DID work....





Did my post not come through??





faz





DTEMLIN1 = "dsn('db2.db.ts')"

parse var DTEMLIN1 pt1 "'" pt2 "'" pt3

SQLSTMT = ""

SQLSTMT.0 = 2

SQLSTMT.1 = "update fred set "

SQLSTMT.2 = " nme = '"||pt1||"''"||pt2||"''"||pt3||"'"





say SQLSTMT.2

Do X = 1 TO SQLSTMT.0;SQLSTMT = SQLSTMT||" "||SQLSTMT.X;End [...] 38902 96 30_Re: Generate DBRM from Catalog12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Fri, 12 Feb 2010 07:59:46 -0600438_US-ASCII James:

They do not accept this as a solution, either. The answer is not to use DSNREXX as part of a product solution.

Gerald Hodge HLS Technologies, Inc. www.hlstechnologies.com



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: Friday, February 12, 2010 4:32 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Generate DBRM from Catalog [...] 38999 326 26_Re: SQL PROBLEM. Need help10_Dave Nance16_dav1mo@YAHOO.COM31_Fri, 12 Feb 2010 06:30:25 -0800566_utf-8 Duam,   Maybe I am missing something here, but doesn't the second query below give you what you are asking for? Is it maybe that you have stripped these down to show as an example?  David Nance  ________________________________ From: duam lee To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, February 11, 2010 6:19:14 PM Subject: [DB2-L] SQL PROBLEM. Need help Hi Sql experts,  here is a problem I do have and explained below.  I am hoping that some of you might have this kind of sql in past and i need help in this regard.  I have [...] 39326 263 38_Re: AW: [DB2-L] SQL PROBLEM. Need help10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 12 Feb 2010 14:57:01 +0000579_utf-8 You can also use a scalar fullselect in the select list to put the count along with the rest of the columns:

SELECT X.COL1 ,Y.COL2 ,X.COL3 ,X.COL4 ,'XYZ' ,X.COL5 ,X.COL6 ,X.COL7 ,X.COL8 ,(SELECT DECIMAL(COUNT(*)) FROM OWNER.TABLE1 X2,OWNER.TABLE2 Y2 WHERE X2.COL8 = Y2.COL9 AND (COL10 = 'TONED' ; OR COL10 = ' ' OR COL10 = ' ' ) AND X2.COL11 = 'C1' GROUP BY X2.COL1, Y2.COL2, X2.COL3, X2.COL4, X2.COL6, COL7, X2.COL8 WHERE X1.col1 = X2.col1 and so on ) AS CT FROM OWNER.TABLE1 X,OWNER.TABLE2 Y WHERE X.COL8 = Y.COL9 AND (COL10 = 'TONED' OR COL10 = ' ' OR COL10 [...] 39590 297 38_Re: AW: [DB2-L] SQL PROBLEM. Need help10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 12 Feb 2010 15:08:46 +0000582_utf-8 Of course I have a typo below. The Where clause to correlate the scalar fullselect should come before the group by, not after.

Joe

On Fri, 12 Feb 2010 14:57:01 +0000, Joe Geller wrote:

>You can also use a scalar fullselect in the select list to put the count along >with the rest of the columns: > >SELECT X.COL1 > ,Y.COL2 > ,X.COL3 > ,X.COL4 > ,'XYZ' > ,X.COL5 > ,X.COL6 > ,X.COL7 > ,X.COL8 > ,(SELECT DECIMAL(COUNT(*)) > FROM OWNER.TABLE1 X2,OWNER.TABLE2 Y2 > WHERE X2.COL8 = Y2.COL9 > AND (COL10 = 'TONED' ; > OR COL10 = ' [...] 39888 586 19_Re: Driver Problems0_17_db46@CHRYSLER.COM31_Fri, 12 Feb 2010 11:58:20 -0500540_ISO-8859-1 Thanks a lot. Yes, when I finally got the syntax right and got the right port (in etc/services), everything worked fine.

Dean J. Burchill 248-576-3957 (8) 776-3957





Peter Suhner Sent by: IDUG DB2-L 02/11/2010 10:12 PM Please respond to IDUG DB2-L



To DB2-L@www.idugdb2-l.org cc

Subject Re: [DB2-L] Driver Problems











Well, that looks much better already! [...] 40475 69 48_[JOB] DB2 z/OS DBA Position(s) in Cincinnati, OH5_Billy21_billysundar@YAHOO.COM31_Fri, 12 Feb 2010 18:35:42 +0000374_utf-8 Fifth Third Bank is currently recruiting for Lead DB2 z/OS DBA position(s) based in Cincinnati, OH.

If you are interested in applying, please contact me off-line, and I will forward your information to the hiring manager.

DB2 DBA Role Description: This is an individual contributor role reporting to the Sr. Manager of Database Administration team. [...] 40545 139 30_Re: Generate DBRM from Catalog17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Fri, 12 Feb 2010 19:49:11 -0000510_us-ascii Thanks Gerald,

Yes CA offer you a nifty product called Bind Analyzer, please feel free to contact me offline if you want any further info, or contact your local friendly CA Rep...

Thanks.



Aurora







Aurora Emanuela Dell'Anno CA Sr. Engineering Services Architect Tel: +44 (0)1753 577 733 Mobile: +44 (0)7768 235 339 Aurora.Dellanno@ca.com

http://www.ca.com/



P please don't print this e-mail unless you really need to! [...] 40685 441 62_DB2 V9 nigration: INVALID OPERAND 'COPYDDN' FOR KEYWORD 'COPY'12_Clark, Kevin22_Kevin.Clark@BCBSDE.COM31_Fri, 12 Feb 2010 19:31:21 -0500505_us-ascii Has anyone experience this error when migration from DB2 V8 from DB2 V9.



Same syntax works fine under DB2 V8,

DSNU000I 043 17:20:27.81 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = CPYFULSP.IMAGECP DSNU1044I 043 17:20:27.87 DSNUGTIS - PROCESSING SYSIN AS EBCDIC

DSNU005I 043 17:20:27.87 DSNUGPRS - TEMPLATE TMPCOPY DSN ('DB2S.IMGCPYFL.SPEC.&DB..&TS.(+1)') DSNU005I 043 17:20:27.87 DSNUGPRS - DISP (NEW,CATLG,DELETE) DSNU005I 043 17:20:27.87 DSNUGPRS - UNIT CART [...] 41127 177 16_INCORROUT and V820_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Sat, 13 Feb 2010 13:58:17 +0200352_us-ascii

Hello,

This is V8 for z/OS.

We had discovered that the Output of the following SQLs are not same 1.5 months ago. They are tested with the same data on DB2 9 for z/OS , DB2 9.5 for LUW and even Oracle The results were the same but V8 for z/OS.

We just received an APAR Fix PM05402 for this issue and it works. [...] 41305 296 35_Re: LUW: Exclusive Alter Table Lock16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Sun, 14 Feb 2010 10:21:19 +0800627_us-ascii Hi Andy,

The only list I know of is this one: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005274.htm Hope that helps.

Regards,

Greg







From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Hunt Sent: Friday, 12 February 2010 5:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] LUW: Exclusive Alter Table Lock

On LUW...

Can anyone point me to any documentation that describes lock compatibilty (or not) with Exclusive Alter Table Lock (n.b. not an Exlcusive Table Lock). Thanks, Andy [...] 41602 162 35_Re: LUW: Exclusive Alter Table Lock9_Andy Hunt21_andy_hunt@YAHOO.CO.UK31_Sun, 14 Feb 2010 21:17:50 +0000633_iso-8859-1 Thanks, yes, seen that one. It's missing the elusive Exclusive Table Alter lock in the matrix. I'm sure there must be an internal IBM manual somewhere that includes this lock in a similar matrix. My interest is in the use of INSPECT to build compression dictionaries. Information Centre for INSPECT says: "Dictionary insert requires an Exclusive Table Alter lock and an Intent on Exclusive Table Lock". I monitored the locking of INSPECT on a Range partitioned table (also across DPF partitions) and found that it was creating dictionaries serially by Range (several across each DPF partition). db2top was showing: * [...]