1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2009, week 2 2 1258 78_[RUG] It's not too late! Baltimore/Washington DB2 Users Group Meets *Tomorrow*7_Kim May28_kim.may@THEFILLMOREGROUP.COM30_Tue, 8 Sep 2009 08:24:09 -0400393_us-ascii September 9, 2009 Meeting Agenda

Two Tracks! DB2 for z/OS *and* LUW











Please join us for the September meeting at the Sheraton in Columbia featuring eight sessions - four for z/OS and four for LUW. Attend the sessions you prefer - from either track - throughout the day. Session abstracts and speaker bio's are included below. [...] 1261 38 37_Activate Bufferpool in DB2V8 for Z/OS24_SUBSCRIBE DB2-L Karthick16_karthickn@HCL.IN30_Tue, 8 Sep 2009 13:08:49 +0000399_utf-8 Hi,

I have added buffers to Bufferpool by using Alter Bufferpool command with VPSIZE specifying number of buffers.While displaying the specific bufferpool,it says that specified number of buffers gets added but its still in inactive status.

How buffers are activated and changed to Active status.

Is Buffers become active once those buffers are used by some objects. [...] 1300 25 66_Re: DB2 Optimization Service Center and Java Heap on Windows Vista13_Matthew Scott18_mscott@ALFAINS.COM30_Tue, 8 Sep 2009 13:47:59 +0000637_utf-8 Is there anyone that has any idea how to resolve this? Better yet, is there anyone out there using OSC on Windows Vista? Please help!!!!!

Matthew Scott mscott@alfains.com

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "The clear and high level technical information of some of the sessions makes the conference a value for the money." _____________________________________________________________________ 1326 34 12_db2cmd on NT16_Sreedhar Bobbadi29_sreedhar.bobbadi@WACHOVIA.COM30_Tue, 8 Sep 2009 13:50:45 +0000586_utf-8 I have two DB2 instances running on a single server. Programmatically we are trying to determine what DBs are under each of these instances. We use PSEXEC to execute remote code. The command we are using is db2cmd - w -i set DB2INSTANCE=ABC1 && db2 list db directory....this works great but get the DBs under ABC1 always even if I change the DB2INSTANCE to ABC2. I think I need a way to make DB2CMD execute two commands. Its executing set DB2INSTANCE=ABC1 and then tries to execute db2 list database directory in another command windows. Any help is greatly appreciated. [...] 1361 196 41_Re: Activate Bufferpool in DB2V8 for Z/OS23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 8 Sep 2009 10:24:20 -0400508_UTF-8 Karthick,

If you are looking at the field Current Buffers Active in a display, that is really the number of buffers NOT AVAILABLE this should always be a relatively low number compared to the buffers allocated for the pool.

Joel



Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com [...] 1558 43 50_v8 z/OS - WLM Appl Env switching to STOPPED status16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV30_Tue, 8 Sep 2009 14:30:33 +0000450_utf-8 DB2 for z/OS - v8 nfm

I have a WLM application environment which is used for a group of stored procedures accessed via a ColdFusion application.

The task starts correctly on the first call of a procedure but without warning or error messages it will go into a STOPPED state…I issue the VARY command with RESUME and it continues to operate.... But will repeatedly go into the STOPPED state again after each VARY command.... [...] 1602 28 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status11_Mukesh Jain21_jain.mukesh@AOINS.COM30_Tue, 8 Sep 2009 14:52:46 +0000446_utf-8 Can you display the status of the stored procedure. And find out the status of the rpocedures that was using this WLM. Because sometime if the procedure fails it terminates the WLM and it goes into stop status.

Thanks Mukesh Jain Senior DB2 DBA & System programmer (517) 9801064

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 1631 61 34_DB2 V9 - DSNUTILS stored procedure14_Manana Vinokur20_manana.v@SAPIENS.COM30_Tue, 8 Sep 2009 14:30:43 +0000 1693 22 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV30_Tue, 8 Sep 2009 15:27:55 +0000549_utf-8 no warnings or error messages are issued and all procedures are in the STARTED status....

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "The clear and high level technical information of some of the sessions makes the conference a value for the money." _____________________________________________________________________ 1716 149 38_Re: DB2 V9 - DSNUTILS stored procedure14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Tue, 8 Sep 2009 17:33:19 +0200818_UTF-8 check stored procedure





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Manana Vinokur Sent: dinsdag 8 september 2009 16:31 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V9 - DSNUTILS stored procedure





I am installing DB2 V9 and running verification jobs as a part of the installation process. Job DSNTEJ6U is a DSNUTILS caller that uses PL/1. It gets the following error: DSNE949E COMMIT PROCESSING ABNORMAL TERMINATION, REASON CODE X'00E70115' DSNX906I -DSN9 DSNX9CAC PROCEDURE OR FUNCTION 174 SYSPROC.DSNUTILS TERMINATED ABNORMALLY. THE PROCEDURE OR FUNCTION HAS BEEN STOPPED. ASID= 0063 WLM_ENV= WLMENV1 WLM sample JCL : //DSN9ENV1 PROC RGN=0M,APPLENV=WLMENV1,DB2SSN=DSN9 //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, [...] 1866 315 38_Re: DB2 V9 - DSNUTILS stored procedure14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 8 Sep 2009 12:01:02 -0400396_us-ascii I think what Patrick is saying is that you need to -DIS PROC(DSNUTILS) and maybe to -STA PROC(DSNUTILS) in that subsystem

________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steurs Patrick Sent: Tuesday, September 08, 2009 11:33 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V9 - DSNUTILS stored procedure [...] 2182 100 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status14_Wayne Driscoll18_wdrisco@US.IBM.COM30_Tue, 8 Sep 2009 11:21:05 -0500622_UTF-8 If you issue a "D A,stcname" for the Started Task associated with the Appl Env, is there one running? One cause for this that I have seen is a JCL error in the started task JCL.

=============================================== Wayne Driscoll OMEGAMON DB2 L3 Support/Development wdrisco(AT)us.ibm.com ===============================================





From: Natalie Faulkner To: DB2-L@WWW.IDUGDB2-L.ORG Date: 09/08/2009 09:31 AM Subject: [DB2-L] v8 z/OS - WLM Appl Env switching to STOPPED status Sent by: DB2 Data Base Discussion List [...] 2283 20 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV30_Tue, 8 Sep 2009 17:07:16 +0000518_utf-8 yes... the task starts on the initial call to the proc....

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "The clear and high level technical information of some of the sessions makes the conference a value for the money." _____________________________________________________________________ 2304 91 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Tue, 8 Sep 2009 11:04:11 -0700481_us-ascii Does the definition of the application environment in WLM use any variables for JOBNAME in the Start Parameters? Just wondering if there could be instances of the stored procedure that you didn't know to look at because the jobname was different.

Are you data sharing? Could the applenv JCL have been invoked on an LPAR that is using a different proclib, and that JCL might have an error in it? Depending on the JCL error, you might not get any output for it. [...] 2396 65 16_implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Tue, 8 Sep 2009 12:42:14 -0600645_us-ascii Probably a beginner's question, but then I am a beginner.

Is there some special secret to CAF implicit connect? I can do an explicit connect to SSID "DSN" and it works fine. But when I try an implicit connect I get this:

DSNT408I SQLCODE = -991, ERROR: CALL ATTACH WAS UNABLE TO ESTABLISH AN IMPLICIT CONNECT OR OPEN TO DB2. RC1=000C RC2=00F30006 DSNT418I SQLSTATE = 57015 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNACA00 SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC [...] 2462 124 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV30_Tue, 8 Sep 2009 18:49:57 +0000309_utf-8 Thanks to all for making me look deeper into the catalog....

found that the DBA had defined a "test" WLM environment in a "prod" stored proc... it still puzzles me that there are no messages issued in syslog or within the MSTR or stored proc WLM task to indicate that a STOP was issued..... [...] 2587 90 20_Re: implicit connect13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US30_Tue, 8 Sep 2009 12:20:49 -0700417_us-ascii Frank, Before you can execute any SQL, you must perform a call to CAF (DSNALI) to CONNECT specifying the SSID and then call to OPEN specifying the SSID and PLAN.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Frank Swarbrick Sent: Tuesday, September 08, 2009 11:42 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] implicit connect [...] 2678 91 20_Re: implicit connect14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 8 Sep 2009 15:21:23 -0400427_us-ascii Frank,

Are you running this code in a batch job? Does the JCL that specifies the CAF also specify a SSID? The RC2=00F30006 suggests "unable to find the subsystem with that name" as a possible cause, which suggests that you haven't given the CAF a correctly-named subsystem. Alternately, if your mainframe has multiple z/OS LPARs, you may be running in a different one than your target DB2 Subsystem is in. [...] 2770 69 20_Re: implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Tue, 8 Sep 2009 13:23:51 -0600361_us-ascii Thanks to cuneyt goksu for clueing me in to DSNHDECP. Looks like if I use the default one in SDSNLOAD it works, but if I use the one in SDSNEXIT it does not. Apparently the one in SDSNEXIT is pointing to DSN1 instead of DSN. I'll have to get my sysprog to fix that (or perhaps better have the SSID set to DSN1 (or something else) instead of DSN). [...] 2840 54 42_migrating from db2 7.1 z/os to db2 9.5 luw15_Yadigar Dirican18_ydirican@YAHOO.COM30_Tue, 8 Sep 2009 13:36:13 -0700332_iso-8859-1 Hi listers,

I want to do a search about migrating from udb2 7.1 for z/os to udb2 9.5 luw, that is; how can it be done easiest, efficient and fast way, also what is riscs e.t.c.

is there any documentation or informatin which I can access?

thank you in advance. Best regards

Y.Ayse Dirican [...] 2895 378 46_Re: migrating from db2 7.1 z/os to db2 9.5 luw14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 8 Sep 2009 17:37:19 -0400498_us-ascii Yadigar,

You've got four issues here (at a minimum). I'm not a multiplatform specialist, so take this as hints only and not a "solution".

First, that code to connect to a DB2 for z/OS subsystem (instance) is significantly different than code to connect to an LUW instance (although once you have connected, the SQL executes almost identically). Are you going to be rewriting mainframe code to run on an LUW OS? Will you be converting from COBOL, for example, to Java/C? [...] 3274 64 17_Correlation names15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Tue, 8 Sep 2009 17:00:15 -0600500_us-ascii In DB2 for LUW you can specify a keyword 'AS' between an object name an it's correlation name. For example:

UPDATE ACCTASGN.AUTO_NUMBERS AS AUTO_NUMBERS SET AUTO_NUMBERS.STATUS_CODE = :AUTO-NBR-STATUS-CODE WHERE CURRENT OF ACCTUP-CSR

In z/OS it appears the AS keyword is not allow; at least not how I have it set up. So I have to change it to this: UPDATE ACCTASGN.AUTO_NUMBERS AUTO_NUMBERS SET AUTO_NUMBERS.STATUS_CODE = :AUTO-NBR-STATUS-CODE WHERE CURRENT OF ACCTUP-CSR [...] 3339 521 46_Re: migrating from db2 7.1 z/os to db2 9.5 luw13_David S Waugh16_dsw-dba@JUNO.COM28_Wed, 9 Sep 2009 01:59:50 GMT579_windows-1252 I'll add a couple of cents here:

Yes, you CAN get extended support for DB2 for z/OS V7 with a fairly expensive contract. The customer I'm working with now has such a support contract while we're migrating them to DB2 for z/OS V8.

Don't forget that the collating sequence for ASCII on a LUW platform is different than the collating sequence for EBCDIC on a z/OS platform. ORDER BY rows can come back in a different order on LUW than it currently does on your mainframe. That tends to trip up a lot of multi-platform applications, as well as people [...] 3861 77 21_Re: Correlation names14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE30_Wed, 9 Sep 2009 10:29:13 +0200486_ISO-8859-1 I never felt the need to specify a correlation name in such a statement; I would simply write

UPDATE ACCTASGN.AUTO_NUMBERS SET STATUS_CODE = :AUTO-NBR-STATUS-CODE WHERE CURRENT OF ACCTUP-CSR



I also question the need of the qualifier ACCTASGN in static SQL, this should be specified by the BIND option QUALIFIER.

There is only one case, where a correlation name on UPDATE makes sense to me: when a correlated subquery is involved, such as in [...] 3939 140 50_Re: Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Wed, 9 Sep 2009 11:31:10 +0300623_us-ascii Regarding to this problem we applied UK34803, UK35347, UK27279, UK27499, UK28296 in addition to PK49057 / UK29639 and PK60789 / UK34180.

Problem fixed. Thank you.

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner > Sent: Friday, August 28, 2009 12:13 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete > > > Hi Cuneyt > > Maybe PK49057 was not applied because it went PE. Fixed by > APAR PK60789 > (UK34180) in March 2008. The original problem was -510 from > Delete of [...] 4080 68 41_Re: Activate Bufferpool in DB2V8 for Z/OS24_SUBSCRIBE DB2-L Karthick16_karthickn@HCL.IN30_Wed, 9 Sep 2009 11:49:12 +0000625_utf-8 Hi Joel,

Thank you for your information.

After adding buffers to Bufferpool BP4,when I displayed that bufferpool using Display Bufferpool(BP4),I received following information.

DSNB401I ¢ BUFFERPOOL NAME BP4, BUFFERPOOL ID 4, USE COUNT 0 DSNB402I ¢ BUFFER POOL SIZE = 2000 BUFFERS ALLOCATED = 0 TO BE DELETED = 0 IN-USE/UPDATED = 0 BUFFERS ACTIVE = 0 DSNB406I ¢ PGFIX ATTRIBUTE - CURRENT = NO PENDING = NO PAGE STEALING METHOD = LRU DSNB404I ¢ THRESHOLDS - VP SEQUENTIAL = 80 DEFERRED WRITE = 50 VERTICAL DEFERRED WRT = 10, 0 PARALLEL SEQUENTIAL =50 ASSISTING PARALLEL SEQT= 0 DSN9022I ¢ [...] 4149 683 38_Re: DB2 V9 - DSNUTILS stored procedure14_Manana Vinokur20_manana.v@SAPIENS.COM30_Wed, 9 Sep 2009 15:28:09 +0300881_UTF-8 Hi,

I have defined a separate WLM environment WLMENV1 for these two stored procedure: DSNUTILS and DSNUTILU. The name if this environment matches that of the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement.

CREATE PROCEDURE SYSPROC.DSNUTILS ...... EXTERNAL NAME DSNUTILS LANGUAGE ASSEMBLE WLM ENVIRONMENT WLMENV1 COLLID DSNUTILS ...

The WLM address space JCL: //DSN9ENV1 PROC RGN=0M,APPLENV=WLMENV1,DB2SSN=DSN9 //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, // PARM='&DB2SSN,1,&APPLENV' //STEPLIB DD DISP=SHR,DSN=DSN910.RUNLIB.LOAD // DD DISP=SHR,DSN=SYS1.DSN910.SDSNEXIT // DD DISP=SHR,DSN=DSN910.SDSNLOAD //* DD DISP=SHR,DSN=CEE.SCEERUN //* //UTPRINT DD SYSOUT=* //RNPRIN01 DD SYSOUT=* //* //DSSPRINT DD SYSOUT=* //SYSIN DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //SYSPRINT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //* [...] 4833 88 41_Re: Activate Bufferpool in DB2V8 for Z/OS12_McLaren Phil32_Phil.McLaren@AXAWINTERTHUR.CO.UK30_Wed, 9 Sep 2009 13:28:24 +0100538_iso-8859-1 Hi Karthick,

Have you altered any tablespaces or indexes to move them into this bufferpool, and then accessed the data using SQL or a utility? I assume this is part of a tuning exercise, which is why you have created BP4 in the first place?

Regards Phil

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SUBSCRIBE DB2-L Karthick Sent: 09 September 2009 12:49 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Activate Bufferpool in DB2V8 for Z/OS [...] 4922 46 16_Re: db2cmd on NT14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 9 Sep 2009 22:35:13 +1000285_US-ASCII I suspect your problem is that DB2INSTANCE is set after db2cmd has started, and hence after the attachment to ABC1. Try set DB2INSTANCE=ABC2 & db2cmd -i db2 list db directory or db2cmd -i set db2 attach to abc2 && db2 list db directory (assuming that the node is set up) [...] 4969 72 21_Re: Correlation names14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 9 Sep 2009 22:35:13 +1000348_US-ASCII Have you actually tried using AS?

There are other places in the SQL Reference that do not show AS, but AS works. Also the 2003 SQL Standard (SQL/Foundation) says AS is allowed in this context .

Might even be able to raise a documentation APAR!

James Campbell

On 8 Sep 2009 at 17:00, Frank Swarbrick wrote: [...] 5042 73 41_Re: Activate Bufferpool in DB2V8 for Z/OS13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM30_Wed, 9 Sep 2009 08:43:10 -0400409_iso-8859-1 Once you alter a table space or index to this buffer pool and it is accessed it will show up in the used counter.

Bob

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SUBSCRIBE DB2-L Karthick Sent: Wednesday, September 09, 2009 7:49 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Activate Bufferpool in DB2V8 for Z/OS [...] 5116 77 49_SV: [DB2-L] Activate Bufferpool in DB2V8 for Z/OS13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Wed, 9 Sep 2009 15:19:10 +0200501_iso-8859-1 Hi, The bufferpool will be allocate as soon as DB2 open a page set associated with the bufferpool BP4. The bufferpool remain allocated until the last object associated with BP4 is physically closed.

Regards, Olle



-----Ursprungligt meddelande----- Från: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] För SUBSCRIBE DB2-L Karthick Skickat: den 9 september 2009 13:49 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: Re: [DB2-L] Activate Bufferpool in DB2V8 for Z/OS [...] 5194 879 38_Re: DB2 V9 - DSNUTILS stored procedure12_David Booher22_david.booher@QUEST.COM30_Wed, 9 Sep 2009 06:57:04 -0700617_us-ascii I would follow-up with IBM on a S0C4 in the DSNUTILS module.

Dave Booher Quest Software



________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Manana Vinokur Sent: Wednesday, September 09, 2009 7:28 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V9 - DSNUTILS stored procedure

Hi,

I have defined a separate WLM environment WLMENV1 for these two stored procedure: DSNUTILS and DSNUTILU. The name if this environment matches that of the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement. [...] 6074 1095 38_Re: DB2 V9 - DSNUTILS stored procedure14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 9 Sep 2009 09:59:27 -0400475_us-ascii You should probably set your FAIL parameter to something larger than 1, to allow multiple tests for a single START. Other than that, I think it's time to put in an ETR (problem) ticket with IBM.

--Phil

________________________________ From: Manana Vinokur [mailto:manana.v@sapiens.com] Sent: Wednesday, September 09, 2009 8:28 AM To: SrinivasG; Sevetson, Phil; DB2 Data Base Discussion List Subject: RE: [DB2-L] DB2 V9 - DSNUTILS stored procedure [...] 7170 733 38_Re: DB2 V9 - DSNUTILS stored procedure9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK30_Wed, 9 Sep 2009 14:45:21 +0000666_iso-8859-1

Is RUNLIB.LOAD authorised? If not, remove it as it isn't needed if you are just using this proc to run DSNUTILS/U. DSNUTILS has to run from an authorised STEPLIB.





Chris





Date: Wed, 9 Sep 2009 15:28:09 +0300 From: manana.v@SAPIENS.COM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V9 - DSNUTILS stored procedure











Hi,

I have defined a separate WLM environment WLMENV1 for these two stored procedure: DSNUTILS and DSNUTILU. The name if this environment matches that of the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement. [...] 7904 97 20_Re: implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 09:07:29 -0600614_us-ascii FYI, if anyone cares, my sysprog changed the DB2 subsystem name from DSN to DSN1 and now my implicit connect works.

Frank

On 9/8/2009 at 12:42 PM, in message <4AA65125.6F0F.0085.0@efirstbank.com>, Frank Swarbrick wrote: > Probably a beginner's question, but then I am a beginner. > > Is there some special secret to CAF implicit connect? I can do an explicit > connect to SSID "DSN" and it works fine. But when I try an implicit connect > I get this: > > DSNT408I SQLCODE = -991, ERROR: CALL ATTACH WAS UNABLE TO ESTABLISH AN > IMPLICIT CONNECT OR OPEN [...] 8002 90 20_Re: implicit connect14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Wed, 9 Sep 2009 10:34:21 -0500736_us-ascii Frank What are your reasons for using implicit connect ? I consider the flexibility of specifying subsystem and plan at run time an advantage. Khalid









FYI, if anyone cares, my sysprog changed the DB2 subsystem name from DSN to DSN1 and now my implicit connect works.

Frank





HCSC Company Disclaimer

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at [...] 8093 81 21_Re: Correlation names15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 09:51:35 -0600616_us-ascii >>> On 9/9/2009 at 2:29 AM, in message <4AA76759.3040606@t-online.de>, Bernd Oppolzer wrote: > I never felt the need to specify a correlation name in such a > statement; I would simply write > > UPDATE ACCTASGN.AUTO_NUMBERS > SET STATUS_CODE = :AUTO-NBR-STATUS-CODE > WHERE CURRENT OF ACCTUP-CSR

I didn't write the code. It just happened to be in the first program I tried to compile on z/OS. I think the programmer always like to qualify the columns with a correlation name just in case there is a join of two tables and the tables have columns with the same name. [...] 8175 109 20_Re: implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 10:24:46 -0600388_us-ascii Advantage: simplicity.

In the end we may not use it. I am researching the possibilities during our migration to z/OS from VSE.

I am curious as to why it is advantageous to be required to specify the plan name. Wouldn't I always want to use the same plan for any particular program? Seems to me it would be a pain to be required to explicitly specify the plan. [...] 8285 56 21_Re: Correlation names14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE30_Wed, 9 Sep 2009 18:29:53 +0200576_ISO-8859-1 >> I never felt the need to specify a correlation name in such a >> statement; I would simply write >> >> UPDATE ACCTASGN.AUTO_NUMBERS >> SET STATUS_CODE = :AUTO-NBR-STATUS-CODE >> WHERE CURRENT OF ACCTUP-CSR >> > > I didn't write the code. It just happened to be in the first program I tried to compile on z/OS. I think the programmer always like to qualify the columns with a correlation name just in case there is a join of two tables and the tables have columns with the same name. I suppose he could have written > SET ACCTASGN.AUTO_NUMBERS.STATUS_CODE = [...] 8342 42 38_Re: DB2 V9 - DSNUTILS stored procedure11_Mukesh Jain21_jain.mukesh@AOINS.COM30_Wed, 9 Sep 2009 16:48:47 +0000363_utf-8 What is the NUMTCB value specified for tjis WLM. You haven't put any value in the PROC so it should be getting from the WLM environment defination. Generally it is good idea to have NUMTCB value in the WLM PROC it will over write the value specified in the WLM definition and it is easy to maintain. You should have NUMTCB=1 for DSNUTILS and DSNUTILU. [...] 8385 210 21_Re: Correlation names15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 10:55:14 -0600454_us-ascii Certainly I have tried. That is how I discovered it does not work:

DSNH083I I DSNHSM3 LINE 526 COL 45 IBM SQL RESERVED WORD "AS" SPECIFIED AS AN IDENTIFIER NAME

The precompiler appears to think that I am trying to assign a correlation name of AS to the table.

Upon further investigation it appears that the AS keyword only(?) does not work for UPDATE. It does work for SELECT, which I'm guessing is what you say works. [...] 8596 28 38_Re: DB2 V9 - DSNUTILS stored procedure11_Mukesh Jain21_jain.mukesh@AOINS.COM30_Wed, 9 Sep 2009 17:08:19 +0000432_utf-8 Another thing was your DSNTIJSG job was successful Can you check the bind for this proc and can you compare the contoken from the catalog tables to the load module. Whether you bound the correct ?

Thanks Mukesh jain Senior DB2 DBA & System programmer (517) 9801064

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 8625 417 50_IDUG Europe 2009: Make today your IDUG Europe Day!17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM30_Wed, 9 Sep 2009 18:11:37 +0100309_us-ascii Hi list, At the Conference Planning Committee for IDUG Europe we are getting very excited about this year's conference: despite the global crisis we have a great enrolment rate and this year's conference is looking as good as it has ever been!

And here's a little message from all of us: [...] 9043 109 21_Re: Correlation names15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 11:31:10 -0600599_us-ascii >>> On 9/9/2009 at 10:29 AM, in message <4AA7D801.8020004@t-online.de>, Bernd Oppolzer wrote: >> > I never felt the need to specify a correlation name in such a >>> statement; I would simply write >>> >>> UPDATE ACCTASGN.AUTO_NUMBERS >>> SET STATUS_CODE = :AUTO-NBR-STATUS-CODE >>> WHERE CURRENT OF ACCTUP-CSR >>> >> >> I didn't write the code. It just happened to be in the first program I > tried to compile on z/OS. I think the programmer always like to qualify the > columns with a correlation name just in case there is a join of two tables > and the [...] 9153 180 20_Re: implicit connect14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Wed, 9 Sep 2009 12:34:20 -0500573_us-ascii Implicit connection does have the benefit of simplicity of coding but it comes with some loss of control and administrative overhead. Here is what the manual say about default plan name and one of its limitations:

"Plan name The member name of the database request module (DBRM) that DB2 produced when you precompiled the source program that contains the first SQL call. If your program can make its first SQL call from different modules with different DBRMs, you cannot use a default plan name; you must use an explicit call using the OPEN function." [...] 9334 167 20_Re: implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 14:31:13 -0600399_us-ascii Am I undertstanding this correctly?

Are you saying if I have a subroutine (let's call it SUBR) that is called by PROGA and PROGB, then I cannot do an implicit connect within SUBR to use a plan called SUBR?

This sounds like bad news. We currently have at least one subroutine that works this way. On VSE we've had no issues with this. Is z/OS going to cause me a problem? [...] 9502 185 20_Re: implicit connect9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 9 Sep 2009 16:01:52 -0500472_US-ASCII The first SQL call sets the plan name. It doesn't have to be anything special. The driver program can execute a EXEC SQL SET :HV TO CURRENT TIMESTAMP END-EXEC. Once the plan name is established, all the other subroutines have to be in the packlist for that plan.

Say that PROGA doesn't have any SQL but SUBR does. The first SQL in SUBR would set the plan name to SUBR and it would stay that even if there were multiple other subroutines that has SQL. [...] 9688 92 6_STDSQL15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 15:11:48 -0600347_us-ascii I have a question(?) about STDSQL. In both DB2 for VSE and DB2 for LUW we do the following:

EXEC SQL INCLUDE SQLCA END-EXEC EXEC SQL BEGIN DECLARE SECTION END-EXEC [...host variables go here...] EXEC SQL END DECLARE SECTION END-EXEC

In DB2 for z/OS this code will not work no matter if I use STDSQL(YES) OR STDSQL(NO). [...] 9781 155 20_Re: implicit connect14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 10 Sep 2009 08:04:05 +1000386_US-ASCII Or alternatively, since Frank implied that he wanted one plan, both PROGA and PROGB call another program - PLANX - which issues a dummy SET, and hence makes PLANX the plan name. PLANX then has a PKLIST of COLLIDX.* and every program has its package in COLLIDX. Then any program can call any subroutine (after the setup call to PLANX) and they all live happily ever after. [...] 9937 314 20_Re: implicit connect15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 9 Sep 2009 17:56:43 -0600383_us-ascii To be honest this is all giving me a bit of a headache. None of these issues exist, as far as I know, on VSE, so all of this PLAN versus PACKAGE, implicit versus explicit, etc. is making my head spin! I don't even know what I don't know! (Which is slightly better than thinking I know something when I really don't, which was how I was before this discussion started!) [...] 10252 862 38_Re: DB2 V9 - DSNUTILS stored procedure13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 10 Sep 2009 00:38:11 GMT388_windows-1252 Manana:

Both DSNUTILS and DSNUTILU *must* run in an APF-authorized environment (see DB2 V9.1 Installation Guide, Chapter 7. "Working with additional capabilities for DB2", page 513).

I'd be willing to bet that at least one of your libraries in STEPLIB is not APF-authorized (like RUNLIB.LOAD, and possibly DSNEXIT), and that's what's causing your problem. [...] 11115 306 20_Re: implicit connect9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 9 Sep 2009 20:08:34 -0500524_us-ascii you can but don't - A DB2 plan can link t a program two ways - 1. list of DBRM's - the old way - major headaches operationally - have to rebind the entire plan just to change one program. All the old books and even the current manuals cover this method first. IBM has made noises about this going away eventually. 2. package list (the PKLIST parm in bind) - package list can be a list of DBRM's to be included or the more normal Collection.*. Which means any subroutine that exists now or in the future can be [...] 11422 50 18_Ministry of Health14_Johanna Wilson26_johanna_wilson@MOH.GOVT.NZ31_Thu, 10 Sep 2009 04:10:32 +0000435_utf-8 The Ministry of Health has posted a tender opportunity on the Government Electronic Tender Service (GETS) for Database Administration Services for specialist IBM DB2 Technologies.

The objective of this RFP is to identfy and engage via an Agreement for Services an individual(s) or organisation(s) with demonstrated knowledge and experience in providing Database Administration services specified in the RFP document. [...] 11473 30 41_Re: Activate Bufferpool in DB2V8 for Z/OS24_SUBSCRIBE DB2-L Karthick16_karthickn@HCL.IN31_Thu, 10 Sep 2009 07:15:56 +0000735_utf-8 Hi,

After Altering the Tablespace to access the specific Bufferpool and executed queries and utilities,the Bufferpool is used and it becomes Active.

Thank you all for your Kind Information

Regards, Karthick.N

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- “The main reason for my attendance at IDUG - Europe was general training and rekindling enthusiasm. I took away more knowledge and enthusiasm – IDUG did the job!" _____________________________________________________________________ 11504 205 20_Re: implicit connect14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 10 Sep 2009 11:00:05 +0200596_ISO-8859-1 I would like to add that we are very happy with only one single plan per subsystem and environment. That is, one plan for production batch, one plan for production IMS, one plan for test IMS stage one, one for test IMS stage two and so on.

Each plan has an appropriate list of collections, and the packages, which relate one by one to the modules (main programs or sub programs) are bound in the relevant collections; this is all done automatically by a home grown change and configuration management system, which determines automatically, which package needs to be bound [...] 11710 47 20_Re: implicit connect14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 10 Sep 2009 11:10:04 +0200484_ISO-8859-1 Don't go the old way with DBRMs bound into plans. Instead, go the new way with packages from the start. (Solution Nr 2 in Mike Bell's post). It's much easier.

A plan (one single plan, if you wish) points to a list of collections, much like a steplib concatenation.

The collections contain many many packages, and the first package with the appropriate name and timestamp (matching the name and timestamp of the load module or subprogram) will be chosen. [...] 11758 273 28_AW: [DB2-L] implicit connect35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 10 Sep 2009 11:44:52 +0200468_iso-8859-1 Hi

You wrote:

I would like to add that we are very happy with only one single plan per subsystem and environment. That is, one plan for production batch, one plan for production IMS, one plan for test IMS stage one, one for test IMS stage two and so on.

Wow! And how do you do accounting, performance analysis and so on, if you have just one plan for production IMS. That means, every transaction uses the same plan. Unbelievable. [...] 12032 45 32_Re: AW: [DB2-L] implicit connect14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 10 Sep 2009 13:10:51 +0200576_ISO-8859-1 I don't know how exactly it's done, but it's done, and it's done on a per package or per module base (or even per SQL statement). And we are constantly improving our environment, due to cost pressure from our management.

Regards

Bernd





Walter Janißen schrieb: > Hi > > You wrote: > > I would like to add that we are very happy with only one single plan per subsystem and environment. That is, one plan for production batch, one plan for production IMS, one plan for test IMS stage one, one for test IMS stage two and so on. [...] 12078 60 32_Re: db2 catalog admin tcpip node11_Dave Mullin28_David.Mullin@JOHNLEWIS.CO.UK31_Thu, 10 Sep 2009 11:27:18 +0000699_utf-8 Thanks Jack and Bernd for your replies.

I am aware that the DAS (database administration server) is used for the following:

Enabling remote administration of DB2 servers. Providing the facility for job management, including the ability to schedule the running of both DB2 and operating system command scripts. These command scripts are user-defined. Defining the scheduling of jobs, viewing the results of completed jobs, and performing other administrative tasks against jobs located either remotely or locally to the DAS using the Task Center. Providing a means for discovering information about the configuration of DB2 instances, databases, and other DB2 administration [...] 12139 487 38_Re: DB2 V9 - DSNUTILS stored procedure14_Manana Vinokur20_manana.v@SAPIENS.COM31_Thu, 10 Sep 2009 14:41:57 +0300337_Windows-1252 David,

Thank you very much for your reply.

I have amended the WLM JCL, but there is no trace info after the S0C4 abend in any of mentioned DD cards. I have added SYSABEND and produced the system dump.



All libraries are APF authorized. I have added RUNLIB to the list as well two weeks ago. [...] 12627 92 38_Re: DB2 V9 - DSNUTILS stored procedure14_Manana Vinokur20_manana.v@SAPIENS.COM31_Thu, 10 Sep 2009 14:50:22 +0300319_windows-1255 Mike Yes, exactly as you say. The RUNLIB is APF authorized. The job produces the same results even when RUNLIB is removed from the WLM JCL. NUMTCB is 1 (also for REXX WLM).

I have dropped/created these procedures + rebind. The same results.

I would like to thank everyone for your help. [...] 12720 31 22_DB2 Cross Loader Query22_Devyani R Sahasrabudhe22_devyani.sah@IN.IBM.COM31_Thu, 10 Sep 2009 11:13:15 +0000318_utf-8 Hi Folks,

DB2 Cross loader uses Load Incursor option to the load the table from the table residing on remote server. Therefore it should fetch data by reading the cursor definition. So when the utility is running, is the data marerialized in any temporary memory space before loading into the table. [...] 12752 75 10_Re: STDSQL14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 10 Sep 2009 22:53:36 +1000376_US-ASCII The earliest set of standards I have access to is a draft set of 2003 ones. SQLCA is not mentioned - nor is SQLCODE. For reasons best left alone, different vendors use different SQLCODE/meaning pairs. For example, a well known DBMS uses SQLCODE= -1 for a duplicate index and -104 for a deadlock. SQLSTATE (which is described) is the vendor neutral set of codes. [...] 12828 83 20_Re: implicit connect14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Thu, 10 Sep 2009 09:23:59 -0500560_us-ascii I'm not aware of any issues regarding a single plan with programs bound as packages in batch environment. Like others have already said don't bind DBRMs directly in a plan. Having a single collection for all your packages also works fine. BTW explicit connect with is not such a big deal. I once wrote a cobol program which read subsystem name and plan name from a file and connected to DB2. Any program that needed to use CAF would simply call this program before any SQL was executed. The file being read would simply be an inline data card in [...] 12912 34 26_Re: DB2 Cross Loader Query14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 10 Sep 2009 10:43:46 -0400556_utf-8 Devyani,

The cursor should be using standard DB2 z/OS cursor rules, which means it will only be materialized if certain operations occur without supporting indexes (e.g. DISTINCT, ORDER BY, GROUP BY). I believe those are published somewhere: Anyone else know the where on this?

--Phil

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Devyani R Sahasrabudhe Sent: Thursday, September 10, 2009 7:13 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Cross Loader Query [...] 12947 85 20_Re: implicit connect9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 10 Sep 2009 14:11:46 -0500357_US-ASCII I suppose to make this complete I should explain a really big problem with plans with DBRM's. The EDM pool has to keep DBD's and skeleton plans and packages. So if you have one plan with a whole bunch of DBRM's in it, it gets loaded into the EDM pool as one entity. Every active program using that plan will have another copy in the EDM pool. [...] 13033 28 38_Re: DB2 V9 - DSNUTILS stored procedure9_Jeff DeBo18_jd6306@HOTMAIL.COM31_Thu, 10 Sep 2009 19:27:22 +0000721_utf-8 Manana, Verify that the SCEERUN library is APF authorized. If the IEASYSxx parmlib member specifies LNKAUTH=APFTAB, then the library must be included in the PROGxx member used for APF authorization.

HTH, Jeff

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- “The main reason for my attendance at IDUG - Europe was general training and rekindling enthusiasm. I took away more knowledge and enthusiasm – IDUG did the job!" _____________________________________________________________________ 13062 711 38_Re: DB2 V9 - DSNUTILS stored procedure13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 10 Sep 2009 19:28:16 GMT402_windows-1252 Manana:

We don't have a PL/I compiler, so I can't try to reproduce your problem. Have you tried running DSNTEJ6V? It also invokes DSNUTILS. If it fails with the same 0C4, that would indicate to me that your WLM environment or DSNUTILS stored procedure isn't set up quite right. If it works, that would indicate that there's something wrong just with the DSNTEJ6U PL/I program. [...] 13774 126 32_Re: Cobol host variable question9_Don Leahy19_don.leahy@LEACOM.CA31_Thu, 10 Sep 2009 15:18:56 -0400605_windows-1252 I think that IPADDR is a VARCHAR column, so the host variable needs to have a 49 level in it.

On Fri, Sep 4, 2009 at 5:27 PM, Frank Swarbrick wrote: > I'm thinking I just must be blind, but can anyone see why I'm getting this error? > > IGYPS2221-E Host variable "IPN-USERNAMES" was not declared within a "SQL DECLARE" > section. The host variable definition was accepted. > > > identification division. > program-id. skel. > data division. > working-storage section. > exec sql > begin declare section > end-exec > > 01 sqlcode pic s9(9) comp-5. [...] 13901 33 32_Re: Cobol host variable question14_Peter Vanroose17_pvanroose@ABIS.BE31_Thu, 10 Sep 2009 19:57:46 +0000400_UTF-8 On Thu, 10 Sep 2009 15:18:56 -0400, Don Leahy wrote:

>I think that IPADDR is a VARCHAR column, so the host variable needs to >have a 49 level in it.

Even if the DB2 column is VARCHAR, the host variable need not be. The current declaration (X(100) at level 5) is fine. DB2 will pad the returned value with blanks to fill the 100-character host variable. [...] 13935 48 49_Ministry of Health - New Tender / Job Opportunity14_Johanna Wilson26_johanna_wilson@MOH.GOVT.NZ31_Thu, 10 Sep 2009 20:19:56 +0000496_utf-8 The subject line in my previous posting was not that clear, and may not have caught your attention.

The Ministry of Health has posted a tender opportunity on the Government Electronic Tender Service. I have attached the RFP document.

The objective of this RFP is to identfy and engage via an Agreement for Services an individual(s) or organisation(s) with demonstrated knowledge and experience in providing Database Administration services specified in the RFP document. [...] 13984 260 38_Re: DB2 V9 - DSNUTILS stored procedure13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 10 Sep 2009 21:10:58 GMT652_windows-1252 Manana:

Something else I just thought of -- I notice you put specific volsers in your APF ADD statements in PROG00:

APF ADD DSNAME(SYS1.DSN910.SDSNEXIT) VOLUME(SP4212) APF ADD DSNAME(SYS1.DSN910.SDSNLINK) VOLUME(SP4212) APF ADD DSNAME(DSN910.SDXRRESL) VOLUME(SP4212) APF ADD DSNAME(DSN910.SDSNLOAD) VOLUME(SP4212) APF ADD DSNAME(DSN910.RUNLIB.LOAD) VOLUME(SP4211)



You should make absolutely SURE that these datasets really DO reside on the volumes you've specified. If not, I'm thinking they won't get the APF-authorization you're expecting (I seem to remember that happening to me once a long time ago). [...] 14245 113 55_Hey Houston, there's a no-charge seminar coming you way13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 10 Sep 2009 17:22:23 -0500292_ISO-8859-1 This is just a reminder to sign up for the DB2 seminar scheduled for Sept 15 here in Houston. We have some terrific speakers for the event, including Jay Yothers. You'll also have to put up with me for a topic but that's the price you have to pay for a free lunch...(smile..) [...] 14359 87 38_Re: DB2 V9 - DSNUTILS stored procedure14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 10 Sep 2009 22:48:48 +0000864_utf-8 Do you have PK74109/UK41538 installed?

James Campbell

On Tue, 8 Sep 2009 14:30:43 +0000, Manana Vinokur wrote:

>I am installing DB2 V9 and running verification jobs as a part of the >installation process. Job DSNTEJ6U is a DSNUTILS caller that uses PL/1. >It gets the following error: >DSNE949E COMMIT PROCESSING ABNORMAL TERMINATION, >REASON CODE X'00E70115' > >DSNX906I -DSN9 DSNX9CAC PROCEDURE OR FUNCTION >174 >SYSPROC.DSNUTILS TERMINATED ABNORMALLY. THE PROCEDURE >OR FUNCTION HAS >BEEN STOPPED. ASID= 0063 WLM_ENV= WLMENV1 > >WLM sample JCL : >//DSN9ENV1 PROC RGN=0M,APPLENV=WLMENV1,DB2SSN=DSN9 >//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, >// PARM='&DB2SSN,1,&APPLENV' >//STEPLIB DD DISP=SHR,DSN=DSN910.RUNLIB.LOAD >// DD DISP=SHR,DSN=SYS1.DSN910.SDSNEXIT >// DD DISP=SHR,DSN=DSN910.SDSNLOAD [...] 14447 55 60_DB2 8.2 with DPF on AIX: Moving 7 TB DB to Different Servers13_Brian Stewart23_briankstewart@GMAIL.COM31_Thu, 10 Sep 2009 21:20:04 -0600448_ISO-8859-1 Hello - I am considering moving a 7 TB DB2 8.2 database on AIX (partitioned on four systems with one partition each) to new servers at a different data center. Several methods come to mind, but I was wondering if any of you have executed such a move and what approach you used. I'm not really needing a lot of detail, but rather the general approach and whatever issues you encountered. Any and all feedback is greatly appreciated. [...] 14503 107 64_Re: DB2 8.2 with DPF on AIX: Moving 7 TB DB to Different Servers13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 10 Sep 2009 21:51:31 -0700382_ISO-8859-1 I have done this multiple times, using everything from shipping media (tapes or even hard drives) with backups to block-level replication on the SAN. There are many considerations, but the two most important ones are probably:

1. Can you take an outage for the move? If so, how long? 2. What kind of network connectivity (bandwidth) exists between the sites? [...] 14611 230 28_AW: [DB2-L] implicit connect35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 11 Sep 2009 09:26:02 +0200557_iso-8859-1 Khalid

Well, this discussion is somehow off topic, because it has nothing to do with the original question. The issue with one plan is that, that you cannot do any performance analysis or at least I don't know how. E.g. we use query monitor to look, which IMS transaction uses which statements. Most of our transaction have a unique PSB and a unique plan. So you can assign which statements were executed by certain plans. Or if you want to know, why a batch job or an IMS transaction is running bad, what do you do, if there is only [...] 14842 28 89_Performance Issues in DB2 V9.5(linux, 64 bit) post migration from DB2 v8.1(linux, 32 bit)7_Praveen22_praveen_kj@INFOSYS.COM31_Fri, 11 Sep 2009 08:36:04 +0000441_utf-8 We have recently migrated one application database from DB2 v8.1(Linux, 32 bit) to DB2 v9.5( (Linux, 64 bit), We are facing severe performance issue with entire application and prima facie it appears database is trouble creator.

Can you pls pass some pointers for the same.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 14871 124 56_Performance analysis of DB2 activity in IMS transactions14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Fri, 11 Sep 2009 10:49:01 +0200497_ISO-8859-1 We are using OMEGAMON, as far as I know, and we are getting performance numbers depending on packages. Performance numbers depending on IMS transactions wouldn't help us much, because many many load modules (and corresponding DB2 packages) are running under just one IMS transaction, with a many to many relation between them. That is, IMS transaction A uses modules M1, M2, M3, M4, transaction B uses M2, M4, M6, M8, for example. And each module is tuned and measured separately. [...] 14996 30 93_Re: Performance Issues in DB2 V9.5(linux, 64 bit) post migration from DB2 v8.1(linux, 32 bit)12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Fri, 11 Sep 2009 10:09:25 +0000434_- Seems to be an isolated problem. Have done many of these migrations with no performance problems, performance improved. Check top sql. Pg ------Original Message------ From: Praveen Sender: DB2 Data Base Discussion List To: DB2-L@WWW.IDUGDB2-L.ORG ReplyTo: DB2 Database Discussion list at IDUG Subject: [DB2-L] Performance Issues in DB2 V9.5(linux, 64 bit) post migration from DB2 v8.1(linux, 32 bit) Sent: Sep 11, 2009 4:36 AM [...] 15027 296 20_Re: implicit connect13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 11 Sep 2009 08:17:05 -0500579_iso-8859-1 Just a little different perspective -- I try to limit the number of plans being used and generally focus on the correlation-id for monitoring/performance analysis. This might depend on what kind of monitoring tools and process you have, but with the tools I use we can monitor on corrid just as easily as planname. Since I don't have an IMS background I can't say how that translates when PSB's, etc are involved, but it works well for a typical batch process. One other factor to consider when looking at the number of plans you might need would be security. If [...] 15324 21 32_Truls Pedersen is out of office.14_Truls Pedersen16_tpeders6@CSC.COM31_Fri, 11 Sep 2009 14:42:43 +0100497_US-ASCII I will be out of the office starting 11.09.2009 and will not return until 21.09.2009.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________ 15346 34 28_Getting started as a DB2 DBA18_McBride, Catherine18_CMcbride@KABLE.COM31_Fri, 11 Sep 2009 08:47:03 -0500584_iso-8859-1 If you have a spare moment and feel like giving some free advice:

A dear colleague of mine is making a sincere effort to transition from being an ADABAS DBA to a DB2 DBA. She is taking classes and studying very hard to receive certifications in DB2 skills. This lady has been a non-relational DBA for about 10 years and definitely has the dedication, work ethic, and personality a DBA needs to have. Based on your own experiences and what you've observed during your careers, do you have any advice that might help her make the transition and successfully land [...] 15381 121 49_Performance Monitoring in Single Plan Environment14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Fri, 11 Sep 2009 09:07:53 -0500435_iso-8859-1 Walter A plan is not the only distinguishing attribute in thread accounting information, correlation id or even job name / start time can be used distinguish one from another. I use package level data to narrow a problem down to a program. Moreover my comment was about use of single plan in batch environment not in IMS (which I have no knowledge of) or CICS. Then again DDF works with one plan anyway. Regards Khalid [...] 15503 49 53_Re: Performance Monitoring in Single Plan Environment11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 11 Sep 2009 14:46:18 +0000503_utf-8 Walter, Khalid is right, you can get all the accounting information from the single plan. I have most of the places either they have been using one plan per environment or per application. there are other ways to identify the accounting report. You can use correlation ID, Job name, Auth ID etc to identify the details. Are you using any tool to generate the report or just getting information from SMF data. I have been generating all kind of reports either from omegamon or SMF or APPTUNE. [...] 15553 21 54_Re: v8 z/OS - WLM Appl Env switching to STOPPED status14_Nancy Stafford24_nancy.stafford@AFBIC.COM31_Fri, 11 Sep 2009 15:50:36 +0000541_utf-8 Sometimes the messages will log to the test WLM started task if it is defined in the production db2 subsystem to go to the test wlm.

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________ 15575 192 64_Re: DB2 8.2 with DPF on AIX: Moving 7 TB DB to Different Servers13_Brian Stewart23_briankstewart@GMAIL.COM31_Fri, 11 Sep 2009 09:53:13 -0600539_ISO-8859-1 There can be a negotiated outage time, but I don't know at this point for how long. Naturally, I'd like to keep it to a minimum as this is a production database. The bandwidth I will pessimistically assume is no better than what you can expect over the internet. What first comes to mind is to do a full backup to tape at the source site, ship the tapes to the destination and then do a redirected restore. I haven't done a redirected restore with DPF but I am assuming it works as it would in a non-DPF environment. After [...] 15768 56 32_Re: Getting started as a DB2 DBA14_Barbara Koenen33_barbara.koenen.bgit@STATEFARM.COM31_Fri, 11 Sep 2009 10:42:21 -0700434_us-ascii Z or LUW?

Honestly, if I had to make a change now, I'd study LUW, Oracle, SQLServer or MySQL.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of McBride, Catherine Sent: Friday, September 11, 2009 8:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Getting started as a DB2 DBA

If you have a spare moment and feel like giving some free advice: [...] 15825 62 32_Re: Getting started as a DB2 DBA13_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM31_Fri, 11 Sep 2009 13:55:55 -0400492_us-ascii That is an easy answer. DON'T!! Go study Oracle, SQLSERVER, MySQL. There is little to no future with DB2 LUW all the jobs are in India and the few that are here there is always the threat they will go to India.

Nick



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Barbara Koenen Sent: Friday, September 11, 2009 12:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Getting started as a DB2 DBA [...] 15888 76 32_Re: Getting started as a DB2 DBA18_McBride, Catherine18_CMcbride@KABLE.COM31_Fri, 11 Sep 2009 13:09:36 -0500401_iso-8859-1 DB2 UDB, non-mainframe

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Barbara Koenen Sent: Friday, September 11, 2009 12:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Getting started as a DB2 DBA



Z or LUW?

Honestly, if I had to make a change now, I'd study LUW, Oracle, SQLServer or MySQL. [...] 15965 49 66_Re: DB2 Optimization Service Center and Java Heap on Windows Vista10_Todd Burch17_toddburch@MAC.COM31_Fri, 11 Sep 2009 14:52:30 -0500450_US-ASCII Hi Matthew.

I am sending an email to the service coordinator for OSC with a link to your post from last week. When I hear anything back, if Patrick doesn't respond directly, I'll post here.

Todd DB2 z/OS Level 2.



On Sep 8, 2009, at 8:47 AM, Matthew Scott wrote:

Is there anyone that has any idea how to resolve this? Better yet, is there anyone out there using OSC on Windows Vista? Please help!!!!! [...] 16015 114 43_uuaaa: [DB2-L] Getting started as a DB2 DBA14_Michael Kaplan25_micaelkp@NETVISION.NET.IL31_Fri, 11 Sep 2009 22:52:58 +0300407_US-ASCII Honestly, I would prefer BI or .Net programmer rather than MySQL DBA. Just curious, what skills are needed ( if any) to operate MySQL ?

Michael Kaplan DBA DB2 Leumi Card

-----????? ??????----- ???: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] ??? Crissy, Frank ????: ? 11 ?????? 2009 20:56 ??: DB2-L@WWW.IDUGDB2-L.ORG ????: Re: [DB2-L] Getting started as a DB2 DBA [...] 16130 47 66_Re: DB2 Optimization Service Center and Java Heap on Windows Vista10_Todd Burch17_toddburch@MAC.COM31_Fri, 11 Sep 2009 16:13:35 -0500379_US-ASCII Hi Matthew. I got a reply from Patrick. Please go ahead an open a PMR.

Thanks, Todd





On Sep 11, 2009, at 2:52 PM, Todd Burch wrote:

Hi Matthew.

I am sending an email to the service coordinator for OSC with a link to your post from last week. When I hear anything back, if Patrick doesn't respond directly, I'll post here. [...] 16178 136 47_Re: uuaaa: [DB2-L] Getting started as a DB2 DBA14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 11 Sep 2009 17:56:09 -0400448_us-ascii There are a couple of books published. Go to your favorite technical book website, or Amazon if that's your poison, and look up "MySQL Database Administration". --Phil Sevetson

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michael Kaplan Sent: Friday, September 11, 2009 3:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] uuaaa: [DB2-L] Getting started as a DB2 DBA [...] 16315 17 0_4_roro16_roro@BLUEMAIL.CH31_Fri, 11 Sep 2009 23:49:28 +0200404_us-ascii _____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________ 16333 59 11_DB2 Courses13_Irwin Deutsch28_ideutsch@PRINCESSCRUISES.COM31_Fri, 11 Sep 2009 15:07:37 -0700683_US-ASCII Hi,

Anyone have any experience with Unicomp's DB2 courses (on mainframe)? There's so many venues and dates it seems to good too be true. (Maybe it is!) I'm looking for a DB2 for z/OS V9 System Performance/Tuning course.



Regards,

Irwin



_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________ 16393 203 32_Re: Cobol host variable question15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM31_Fri, 11 Sep 2009 16:47:24 -0600275_us-ascii For anyone who is interested, I opened a PMR against Cobol in regard to this issue and they have tested and verified that it is, in fact, a Cobol compiler issue. They came up with a workaround. I hope it is not some sort of violation to post text from the PMR: [...] 16597 191 15_Re: DB2 Courses23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 11 Sep 2009 19:40:24 -0400356_iso-8859-1 I'm taking a stab that it looks like they are teaching the IBM course material (which IBM allows consulting companies to do, for a hefty royalty).

If that is correct, the material is good.

The next important question is the instructor. You should find out who it is, and evaluate their credentials to teach a class like this. [...] 16789 193 32_Re: Cobol host variable question10_Todd Burch17_toddburch@MAC.COM31_Fri, 11 Sep 2009 19:23:37 -0500492_ISO-8859-1 Woo hoo!! Them COBOL people are pretty sharp.

I don't think you can get a ticket from the IBMLINK police for copy/ paste.

Todd



On Sep 11, 2009, at 5:47 PM, Frank Swarbrick wrote:

For anyone who is interested, I opened a PMR against Cobol in regard to this issue and they have tested and verified that it is, in fact, a Cobol compiler issue. They came up with a workaround. I hope it is not some sort of violation to post text from the PMR: [...] 16983 291 64_Re: DB2 8.2 with DPF on AIX: Moving 7 TB DB to Different Servers13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Fri, 11 Sep 2009 17:34:50 -0700373_ISO-8859-1 On Fri, Sep 11, 2009 at 8:53 AM, Brian Stewart wrote:

> There can be a negotiated outage time, but I don't know at this point for > how long. Naturally, I'd like to keep it to a minimum as this is a > production database. The bandwidth I will pessimistically assume is no > better than what you can expect over the internet. [...] 17275 298 26_SQL and RUNSTATS question.9_Dee Reins21_rcwingman@COMCAST.NET31_Sat, 12 Sep 2009 22:03:02 +0000528_utf-8







Is there an additional statistic I could run on the column SRVC_FACS_STAT_CD that would stop the tablespace scan on table VMR257VID_FACS.









Thanks

Dee









SELECT EXS_CHK_CD INTO :H

FROM VXX009EXS_CHK A

WHERE EXISTS ( SELECT *

FROM VMR257VID_FACS B , VXX009EXS_CHK C

WHERE B . XRV_ID = :H AND B . SRVC_FACS_STAT_CD

IN ( 'U' , 'P' ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) ; [...] 17574 358 30_Re: SQL and RUNSTATS question.12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Sat, 12 Sep 2009 21:57:48 -0700350_iso-8859-1 Dee,

Add the column on XMR25703 index and ran runstats. This will give you index scan.

Also, change the subquery as follow (i.e select 1 instead of select * )

SELECT 1 FROM VMR257VID_FACS B , VXX009EXS_CHK C WHERE B . XRV_ID = :H AND B . SRVC_FACS_STAT_CD IN ( 'U' , 'P' ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) ; [...] 17933 47 30_Re: SQL and RUNSTATS question.14_Peter Vanroose17_pvanroose@ABIS.BE31_Sun, 13 Sep 2009 10:03:02 +0000374_UTF-8 Try the following, which essentially pulls apart the "EXISTS" into the two independent existence conditions, namely one on B and one on C:

SELECT A.EXS_CHK_CD INTO :H FROM VXX009EXS_CHK A INNER JOIN VXX009EXS_CHK C ON C . EXS_CHK_CD = A . EXS_CHK_CD WHERE EXISTS ( SELECT 1 FROM VMR257VID_FACS B WHERE XRV_ID = :H AND SRVC_FACS_STAT_CD IN ( 'U' , 'P' )) ; [...] 17981 358 38_Re: DB2 V9 - DSNUTILS stored procedure14_Manana Vinokur20_manana.v@SAPIENS.COM31_Sun, 13 Sep 2009 16:37:39 +0300473_Windows-1252 David, Thank you very much for the useful information and help.

I have been following all your thoughts and advises and double checked all the definitions.



1. All libraries are APF authorized and reside on the mentioned volumes. D PROG,APF,ALL command shows:

SP4212 SYS1.DSN910.SDSNEXIT SP4212 SYS1.DSN910.SDSNLINK SP4212 DSN910.SDXRRESL SP4212 DSN910.SDSNLOAD SP4211 DSN910.RUNLIB.LOAD SARES8 CEE.SCEERUN SARESC CEE.SCEERUN2 [...] 18340 29 56_New England DB2 User's Group Tools Fair Seats Going Fast7_Ed Long19_rdhm99a@PRODIGY.NET31_Sun, 13 Sep 2009 20:14:45 -0700283_us-ascii Our Free lunch is filling up fast. The event will be held at the Publick House in Sturbridge Mass. The facility is less than a mile from where I-84 meets the Massachusetts Turnpike. We will start at 0900 and complete the day by 4:30 on Thursday 9/17/09. Sign up today! [...] 18370 105 18_OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 07:01:00 +0200577_ISO-8859-1 Hi list!

I was doing some housekeeping the other day when I saw there was a DSNDB04 lob space LOBTS1 that was 44,000 tracks...this peaked my interest and so I tracked own who/what/where on it and found that it is a LOB space created and used by the OSC (DSN_QUERY_AUX related to base table DSN_QUERY_TABLE). Now I vaquely remember playing with a feature of the OSC that enabled checking of packages/collections that never seemed to return..it just sat there..I now know what it was doing - filling up my packs with LOB data! I then did a deeper check on [...] 18476 174 47_Re: uuaaa: [DB2-L] Getting started as a DB2 DBA11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Mon, 14 Sep 2009 06:27:31 +0000426_UTF-8 Hello,

horrible replies in this thread : " go learn MySQL ...", " everything went to India ..." , "no future ..." etc.

Is it really so bad? What makes you think that companies won't outsource other technologies (like MS, Oracle ...), beside IBM DB2, to India as well ? I work for a bank and can't imagine that someone form the other part of the world is messing with our sensitive production data ... [...] 18651 315 47_Re: uuaaa: [DB2-L] Getting started as a DB2 DBA10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 14 Sep 2009 10:34:54 +0200574_UTF-8 Nenad

I'm sorry but in some countries it's happening (or in the worst cases it's history): in a previous company where I worked all was migrated tor ORACLE and later to MySQL/SQL Server (and it was a big company). Others are moving mainframes away (outsourcing) and let sysprogs from abroad to access to their systems for maintenance/upgrades. If a bank has clients' permissions they can do anything with clients' data, from a legal point of view (at least here). Here in Italy DB2 on mainframe is disappearing and there's no way to see new installations. [...] 18967 465 38_AW: [DB2-L] SQL and RUNSTATS question.35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Mon, 14 Sep 2009 11:36:44 +0200327_iso-8859-1 To answer your question: You can use the COLGROUP-clause of the runstats-utility for that column together with the FREQVAL-clause. Then may be, you will get a non-matching index scan on XMR25701. But if it will be faster, depends on the size of that index (compared to the size of the table) and the filtering. [...] 19433 83 61_AW: [DB2-L] Performance Monitoring in Single Plan Environment35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Mon, 14 Sep 2009 11:40:15 +0200328_iso-8859-1 Hi

But in an IMS-DC-environment, that doesn't work very well, if I want to see, why a certain transaction is running slow. I don't want to limit on AUTHID. Correlation_id would also of no help, because my transaction is running in namy dependet regions. The same holds for jobname. Or do I miss anything? [...] 19517 122 48_Timeouts in Content Manager v7 (DB2 for z/OS v8)14_Martin Flavell24_Martin.Flavell@I-TCS.COM31_Mon, 14 Sep 2009 12:35:35 +0100346_iso-8859-1 Hi,

We are running Content Manager version 7. I have just upgraded our DB2 from version 8 CM to version 8 NFM. Suddenly I am seeing timeouts (00c9008e) in some Content Manager tasks, which were absent when using compatibility mode.

I would be grateful for any suggestions for things I can do to resolve the problem. [...] 19640 388 49_Redesigned Q Replication Introduction and Webcast7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Mon, 14 Sep 2009 07:40:18 -0400719_us-ascii *Tomorrow* There's still time to register!

The Fillmore Group and leading experts from IBM will deliver a 45-minute webcast to introduce the Q Replication Dashboard and improvements included in the new, redesigned Dashboard scheduled for release this month.

The Q Replication Dashboard is the free monitoring tool available from IBM that replaces the Java Q Replication Dashboard and the Data Studio Administrative Console (DSAC) with a thin-client, browser-based interface. The Dashboard provides real-time Q Replication monitoring. In the webcast presenter and DB2 Gold Consultant Frank C. Fillmore, Jr., will introduce and explain how the new redesigned Dashboard's expanded features and [...] 20029 27 22_Re: OSC and LOB spaces11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Mon, 14 Sep 2009 12:41:13 +0000651_utf-8 Are you doing something with Workload Control Center. I believe this table is used by Workload Control center (WCC).

Thanks Mukesh Jain Senior DB2 DBA & System programmer (517) 9801064

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "The clear and high level technical information of some of the sessions makes the conference a value for the money." _____________________________________________________________________ 20057 109 32_Re: Getting started as a DB2 DBA14_Barbara Koenen33_barbara.koenen.bgit@STATEFARM.COM31_Mon, 14 Sep 2009 05:48:28 -0700332_us-ascii I've been involved a bit in the LUW side, just because I support and debug DRDA connections to and from z. I've also done a bit of Oracle, and Informix (pre-IBM). No SQLServer. My husband does MySQL for the school and church websites for which he does volunteer work, so he's always asking me usage questions on that. [...] 20167 201 22_Re: OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 14:59:10 +0200498_ISO-8859-1 to my knowledge I have never used the WCC feature. I just used the EXPLAIN bits and then, once, the PACKAGE explain from the DB2 Catalog....





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 [...] 20369 198 22_Re: OSC and LOB spaces13_Palko, George16_gpalko@OPERS.ORG31_Mon, 14 Sep 2009 14:02:18 +0100428_iso-8859-1 Hi Roy,

Yes- the OSC DSN_QUERY_TABLE is populated each time a bind occurs. So, in a development regions this table can become very large, unless it is pruned. Our company prunes all the tables monthly by deleting obsolete rows from the PLAN_TABLE, which triggers deletes for the other OSC tables. We then run reorgs to reclaim the space. A while back I posted the pruning process along with the triggers. [...] 20568 199 22_Re: OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 15:13:15 +0200470_ISO-8859-1 I just tried the WCC and I have never installed the procedures so it cannot be that that is responsible...odd...





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 [...] 20768 59 39_DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 09:19:34 -0400517_us-ascii

Dear Listers... Forwarding this for a DBA colleague. Anyone have an idea?

Thanks - Ray Gaston ConEd - DBA

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



I had a change to do this weekend that required a drop of a view. I got a -904 with a reason code I never saw before:



SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E70081, TYPE OF RESOURCE 00000B00, AND RESOURCE NAME CQDBXDIV.ACCT2 [...] 20828 132 47_Re: uuaaa: [DB2-L] Getting started as a DB2 DBA11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Mon, 14 Sep 2009 09:24:58 -0400373_utf-8 Yeah that's what I said but our PCI (customer credit card info) data is now being managed by off-shore.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Nenad Vidak Sent: Monday, September 14, 2009 2:28 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] uuaaa: [DB2-L] Getting started as a DB2 DBA [...] 20961 215 22_Re: OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 15:38:13 +0200515_ISO-8859-1 found the axis of evil - do a "statement cache" as query source and then watch your LOB space explode...great fun!!! LOB space started at 165 tracks (default installation size) I did the DSC option with ACCUM_CPU_DESC and then waited...after 200 seconds of watching my LOB space grow I clicked CANCEL now my LOB space is at 10,140 tracks....needless to say I have dropped all the OSC tables again...just for the record I have 677 statements in the DSC which I can explain in about 1 second...weird! [...] 21177 207 22_Re: OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 15:40:34 +0200316_UTF-8 hmmm,, just posted that it seems it was the DSC. I will drop and recreate and then do lots of REBINDS. However I do not think that EXPLAIN YES or BIND interfaces directly with these tables. If that was true we would have died *ages* ago!!!

Anyone from OSC or IBM wish to wade in and correct me???? [...] 21385 110 51_AW: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 14 Sep 2009 15:47:12 +0200355_iso-8859-1 Ray,

in the DB2 LUW world there is a SQL statement "Flush package cache".

This statement lets you remove cached dynamic SQL statements by invalidating them. Those who currently use the dynamic SQL statements can continue to do so. But any new request for the same statement will be prepared and a new statement entry cached. [...] 21496 267 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 15:49:46 +0200506_ISO-8859-1 Runstats with RUNSTATS TABLESPACE xxxx.xxxx UPDATE NONE REPORT NO ? should flush the Cache for all objects dependant on it including views/mqts etc.





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 [...] 21764 93 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?11_Mike Turner19_mike.turner@GMX.COM31_Mon, 14 Sep 2009 14:50:19 +0100744_iso-8859-1 Hi Ray

Run RUNSTATS TABLESPACE x UPDATE NONE REPORT NO on the tablespace containing the table that the view points to. This will purge any dynamic statements from the cache that use the tables in that tablespace without actually gathering any statistics.

Regards Mike Turner Email: mike.turner@gmx.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44. ----- Original Message ----- From: "Gaston, Raymond" Newsgroups: bit.listserv.db2-l To: Sent: Monday, September 14, 2009 2:19 PM Subject: [DB2-L] DB2V8 Z/OS [...] 21858 80 22_Re: OSC and LOB spaces11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Sep 2009 15:52:22 +0200433_ISO-8859-1 I give up...just found that this was exploding the LOBTS2...I do not have a LOBTS1 space anymore - so it doesnt grow like Topsy...I will just drop the whole darn thing and sleep better at night!





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 [...] 21939 86 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?13_Walters, Paul28_Paul.A.Walters@SALLIEMAE.COM31_Mon, 14 Sep 2009 09:58:52 -0400331_us-ascii Open an ETR with IBM. We have a similar problem and have been unable to alter structures. IBM has told us the only way to resolve this issue is to cycle the subsystem.

It has happened twice to our site this year. IBM has supplied some specific dump requests and we are waiting for the problem to happen again. [...] 22026 549 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 14 Sep 2009 15:59:29 +0200590_iso-8859-1 And it's still this way in DB2 9. From the Utility Guide & Reference:

'To invalidate statements in the dynamic statement cache without collecting statistics by: Specify the options UPDATE NONE and REPORT NO in the RUNSTATS utility control statement'

I must have dreamt a command to do this. Vnext, anyone?

Cheers,



Raymond

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: 14 September 2009 14:50 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ? [...] 22576 141 42_R: DB2V8 Z/OS ... How To Flush the Cache ?15_Moschelli Mauro34_mauro.moschelli@INTESASANPAOLO.COM31_Mon, 14 Sep 2009 16:01:47 +0200343_iso-8859-1 RUNSTATS TABLESPACE ..... REPORT NO UPDATE NONE

With this combination of parameters is quick and does nothing but to flush statements referring the object out of the Dynamic Statement Cache.

Maybe you also need to cancel the thread that issued this statement before the runstats, but I don't know how to find it. [...] 22718 131 55_Re: AW: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 10:03:24 -0400429_iso-8859-1

Thanks G e o r g... We'll give it a try!

- Ray

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: Monday, September 14, 2009 9:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] AW: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?

Ray,

in the DB2 LUW world there is a SQL statement "Flush package cache". [...] 22850 148 55_Re: AW: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?7_Ed Long19_rdhm99a@PRODIGY.NET31_Mon, 14 Sep 2009 07:04:39 -0700612_iso-8859-1 Runstats? Edward Long



--- On Mon, 9/14/09, Peter, Georg wrote:

> From: Peter, Georg > Subject: [DB2-L] AW: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ? > To: DB2-L@WWW.IDUGDB2-L.ORG > Date: Monday, September 14, 2009, 9:47 AM > Ray, > > in the DB2 LUW world there is a SQL statement "Flush > package cache". > > This statement lets you remove cached dynamic SQL > statements by invalidating them. Those who currently use the > dynamic SQL statements can continue to do so. But any new > request for the same statement will be prepared [...] 22999 308 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 10:08:34 -0400481_iso-8859-1 Thanks Roy !

- Ray

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Monday, September 14, 2009 9:50 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?





Runstats with RUNSTATS TABLESPACE xxxx.xxxx UPDATE NONE REPORT NO ? should flush the Cache for all objects dependant on it including views/mqts etc. [...] 23308 250 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?10_Todd Burch17_toddburch@MAC.COM31_Mon, 14 Sep 2009 09:27:59 -0500494_ISO-8859-1 A RUNSTATS as mentioned will indeed flush the cache, but it won't resolve the RC00E70081.

This reason code says that a SQL statement has a dependency on the object, and running RUNSTATS (or otherwise clearing the cache) will not resolve this situation.

Please sign yourself up for PK92910 (still open). What this APAR resolves is a timing hole in the thread ABORT and CANCEL code where the use count for a statement in the cache is not being decremented properly. [...] 23559 72 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 14 Sep 2009 16:38:32 +0200524_US-ASCII In V8 there's RUNSTATS statements UPDATE NONE REPORT NONE doing nothing but cleaning cache (invalidating SQL statements).

This causes dynamic statement cache invalidation without any data access.

Max Scarpa

"It has been proven attending IDUG in Rome prolongs life beyond 100 years and increases your fascination"

A. Einstein



_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 23632 137 15_Re: DB2 Courses14_Jeff Skogsberg18_SKOGSBEJ@SLRMC.ORG31_Mon, 14 Sep 2009 08:50:03 -0600348_us-ascii My experience is they offer a lot of classes, but don't hold many. It may have been just the classes I was looking for, but I would suggest getting a confirmation the class is being held before you make travel arrangements.

Jeff Skogsberg St. Luke's Regional Medical Center (208) 381-1191 (208) 384-0210 fax skogsbej@slrmc.org [...] 23770 364 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 11:26:33 -0400466_us-ascii

Thanks Mike...

We are going to do the RUNSTATS TABLESPACE x UPDATE NONE REPORT NO. The table contains apprx 30,000,000 rows so we were hesitant at first !!!

Thanks again - Ray

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner Sent: Monday, September 14, 2009 9:50 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ? [...] 24135 108 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 12:01:54 -0400527_us-ascii Ouch!!! Let's see if the RUNSTAT strategy works for us first.

Thanks - Ray

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Walters, Paul Sent: Monday, September 14, 2009 9:59 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?

Open an ETR with IBM. We have a similar problem and have been unable to alter structures. IBM has told us the only way to resolve this issue is to cycle the subsystem. [...] 24244 113 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 14 Sep 2009 12:12:19 -0400295_us-ascii Paul,

Would you please post to the List as events develop on this? I came _that_ close to using the Cache monitor in OSC when we installed it last month, and it sounds like that would be a Very Bad Thing. We all (who use the OSC) need to know if/when this problem is fixed. [...] 24358 682 22_Re: OSC and LOB spaces14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 14 Sep 2009 12:18:21 -0400504_iso-8859-1 Roy,

DSN_QUERY_TABLE is populated when an EXPLAIN occurs for that table's owning schema.

If you say BIND PACKAGE... OWNER(XYZ) ...EXPLAIN(YES), then the XYZ.DSN_QUERY_TABLE is populated. Ref: DB2 V8 Administration Guide.

--Phil

________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Monday, September 14, 2009 9:41 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] OSC and LOB spaces [...] 25041 135 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 14 Sep 2009 12:39:19 -0400598_us-ascii Yes, of course. Will do.

- Ray

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Monday, September 14, 2009 12:12 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?

Paul,

Would you please post to the List as events develop on this? I came _that_ close to using the Cache monitor in OSC when we installed it last month, and it sounds like that would be a Very Bad Thing. We all (who use the OSC) need to know if/when this problem is fixed. [...] 25177 764 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 14 Sep 2009 12:42:44 -0400658_us-ascii Whoops! Got my DB2-L threads crossed. Sorry, all.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Monday, September 14, 2009 12:12 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?





Paul,





Would you please post to the List as events develop on this? I came _that_ close to using the Cache monitor in OSC when we installed it last month, and it sounds like that would be a Very Bad Thing. We all (who use the OSC) need to know if/when this problem is fixed. [...] 25942 136 24_Your're all wrong! DVIPA12_Scott Jewell26_scott.jewell@FREDMEYER.COM31_Mon, 14 Sep 2009 18:24:26 +0000656_utf-8 ....or maybe I am (that got your attention)

Hi, we've recently implemented DVIPA, basically by the book, and there appears to be an opportunity for a simplified configuration.

If you've ever implemented DVIPA, you've undoubtedly been told, "the resync ports must be unique across all members of a datasharing group".

Well, it would seem that this "requirement" could be considered a "misconception" held over from pre-DVIPA days when the member IP address was tied to the LPAR. A careful examination of the available doc, seems to indicate that the resync process is tied to the member specific IP address/port combination. [...] 26079 26 52_Re: Timeouts in Content Manager v7 (DB2 for z/OS v8)11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Mon, 14 Sep 2009 20:28:53 +0000585_utf-8 You are getting timeout on what resources ?

Thanks

Mukesh Jain Senior DB2 DBA & System programmer (517) 9801064

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "The clear and high level technical information of some of the sessions makes the conference a value for the money." _____________________________________________________________________ 26106 342 76_Immediate Volunteer Opportunity at IDUG 2009 - Europe: Moderate a Session!!!17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Mon, 14 Sep 2009 21:52:27 +0100476_us-ascii Go further...

with your IDUG 2009 Europe Conference experience! Moderate a Session!

Interested in volunteering to moderate a session at this year's conference?

Session moderators are essential to the success of each and every IDUG conference and you can help. To become a moderator, after registering to attend the conference, simply go to the Personal Itinerary Builder on the Conference Scheduler page and login at the bottom of the page. [...] 26449 364 43_Re: DB2V8 Z/OS ... How To Flush the Cache ?13_Sunil Chennur17_SChennur@TSYS.COM31_Mon, 14 Sep 2009 16:50:49 -0400419_US-ASCII Hi Raymond,

We had the same issue, few month's ago, These are all the events that lead to the stray SQL in the Global Statement Cache in our case. When the developer tried to run the application and decided to cancel the thread, This happened while the Prepare SQL statement has been successfully ran, but before other SQL's Execute. for some reason, SQL does not flush out of the Statement Cache. [...] 26814 362 51_Fw: [DB2-L] DB2V8 Z/OS ... How To Flush the Cache ?13_Sunil Chennur17_SChennur@TSYS.COM31_Mon, 14 Sep 2009 17:09:57 -0400419_US-ASCII Hi Raymond,

We had the same issue, few month's ago, These are all the events that lead to the stray SQL in the Global Statement Cache in our case. When the developer tried to run the application and decided to cancel the thread, This happened while the Prepare SQL statement has been successfully ran, but before other SQL's Execute. for some reason, SQL does not flush out of the Statement Cache. [...] 27177 585 59_Re: [DB2 z/OS] Using IBM automation tool to drive utilities16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 14 Sep 2009 17:49:18 -0400431_us-ascii Greetings DB2'ers - sorry about the VERY late response,





Those of you who are using CA Database Analyzer (or might want to) should have a closer look at the RTOS feature available.

There are a number of possible criteria which will select objects for further processing, whether that is Copy, Reorg, automatic space administration or user defined actions - or a combination of utilities. [...]