1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2009, week 3 2 35 71_AUTO: Chandan K Vishwakarma is out of the office (returning 27/11/2009)21_Chandan K Vishwakarma19_cvishwak@IN.IBM.COM31_Sun, 15 Nov 2009 08:02:05 +0530399_US-ASCII I am out of the office until 27/11/2009.

For any Technical escalations, please contact Roona Shree (Roona Shree/India/IBM).

Thanks & Regards, Chandan



Note: This is an automated response to your message DB2-L Digest - 14 Nov 2009 to 15 Nov 2009 (#2009-327) sent on 15/11/09 6:30:00. This is the only notification you will receive while this person is away. [...] 38 70 65_AUTO: John Iczkovits is out of the office. (returning 11/30/2009)14_John Iczkovits19_iczkovit@US.IBM.COM31_Sat, 14 Nov 2009 22:03:19 -0700545_US-ASCII



I am out of the office until 11/30/2009.







Note: This is an automated response to your message "?DB2-L Digest - 14 Nov 2009 to 15 Nov 2009 (#2009-327)" sent on 11/14/09 18:00:00.

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 * _____________________________________________________________________ [...] 109 56 33_any db2 tools for data migrations10_Zhang Ming16_db2sub@YAHOO.COM31_Sun, 15 Nov 2009 01:01:22 -0800445_us-ascii Hi,Rick, Tks for your quick reply. what I need is a tool for z/os db2 data migrations from one existing application system to another newly developed application systems with the same business function, but the data strcture may have some difference,both in the number of tables and the column contents. I know it is application's resonsibility to do that, but i want to use some tools to easy our job. tks for help. best regards. [...] 166 151 37_Re: any db2 tools for data migrations16_Bednarz, Michael24_michael.bednarz@CITI.COM31_Mon, 16 Nov 2009 07:12:00 +0000607_us-ascii Hi Zhang Ming,

you can contact CA and ask for RC-Migrator



Regards

Michael Bednarz CTI, Data Center, EMEA Mainframe DB2 DBA +49 2132 74 259 For Group contact details and processes visit our website: https://globalconsumer.collaborationtools.consumer.citigroup.net/sites/CTI_EMEA/Platforms_Management/Teams/Mainframe.aspx





________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Zhang Ming Sent: Sonntag, 15. November 2009 10:01 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] any db2 tools for data migrations [...] 318 59 20_DBA authority survey0_24_hhuang@DCCSH.ICBC.COM.CN31_Mon, 16 Nov 2009 16:20:09 +0800377_US-ASCII Hi list,

I just want to know, what kind of DB2 authority and privilege are granted to your system DBA at your site? We use SYSADM, but security guys think there is an exposure that DBA could change application data anytime. Then how do you avoid that? It is heard that in DB2 Vnext, the privilege of data access would be separated from SYSADM, is it true? [...] 378 161 65_How to determine table columns with format VARCHAR FOR BIT DATA ?12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 16 Nov 2009 10:02:15 +0100453_us-ascii Environment: DB2 for z/OS Version 8 CM

Collegues,

we know that we have table columns with the format VARCHAR and also columns with the format VARCHAR FOR BIT DATA.

For a specific reason we have the need to find out which columns are VARCHAR and which columns are VARCHAR FOR BIT DATA.

SYSIBM.SYSCOLUMNS gives no information on this - so where we can find the information that a column is VARCHAR FOR BIT DATA ? [...] 540 297 69_Re: How to determine table columns with format VARCHAR FOR BIT DATA ?13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 16 Nov 2009 10:13:43 +0000593_ISO-8859-1 SYSIBM.SYSCOLUMNS.FOREIGNKEY='B'

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







"Peter, Georg" To DB2-L@WWW.IDUGDB2-L.ORG cc

bcc

Subject [DB2-L] How to determine table columns with format VARCHAR FOR BIT DATA ?









"Peter, Georg" Please respond to : IDUG DB2-L Sent by: IDUG DB2-L 16-11-09 10:02



Environment: DB2 for z/OS Version 8 CM [...] 838 178 69_Re: How to determine table columns with format VARCHAR FOR BIT DATA ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 16 Nov 2009 11:16:10 +0100515_UTF-8 Its here in syscolums

EVALUATE TRUE WHEN FOREIGNKEY = 'B' MOVE 'FOR BIT DATA ' TO SQLDDL-COL-FOR-DATA WHEN FOREIGNKEY = 'M' MOVE 'FOR MIXED DATA' TO SQLDDL-COL-FOR-DATA WHEN FOREIGNKEY = 'S' MOVE 'FOR SBCS DATA ' TO SQLDDL-COL-FOR-DATA END-EVALUATE











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 [...] 1017 420 77_AW: [DB2-L] How to determine table columns with format VARCHAR FOR BIT DATA ?12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 16 Nov 2009 11:47:15 +0100620_iso-8859-1 Thank you, Dr. Ebert.

This was exactly what we are looking for........



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 ---------------------------------------------------------------------- P Think before you print. [...] 1438 80 24_Re: DBA authority survey14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Mon, 16 Nov 2009 13:17:20 +0000467_utf-8 I do not think the data change authority originates with SYSADM. If it did the authority could be restricted today with SYSCTL authority. This issue lies with change control. In tightly controlled shops the authority to create and drop databases is often centralized. The creator of a database automatically gets DBADM on the database and has full data access at that point. DBADM cannot be revoked from the database creator and have the database survive. [...] 1519 59 32_Re: New features in DB2 9 (z/OS)11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Mon, 16 Nov 2009 14:31:48 +0000656_UTF-8 Not necessary in order of appearance : more zIIP offload, larger index pages, RTS improvements ( index usage!), plan stability, elimination of BUILD2 phase, online INDEX REBUILD .....

Regards,Nenad



On Sat, 14 Nov 2009 11:21:09 +0000, Steven Lamb wrote:

>What do people think are the new features in DB2 9 that will really make a >difference to them? >I haven't had any experience of this version yet myself but I've seen >mentions of virtual indexes and eliminating the BUILD2 phase for Reorgs. The >(main) reason I'm asking is that I've got a technical interview on Monday >with a company [...] 1579 24 32_Re: New features in DB2 9 (z/OS)11_Steven Lamb23_stevenjlamb@HOTMAIL.COM31_Mon, 16 Nov 2009 14:50:10 +0000639_UTF-8 Thanks for that Willie - it was very useful. I've just had the interview and I don't think it went all that well, but never mind.

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...] 1604 34 47_Re: DB2 Control Center - very long connect time6_Bill M18_bmilam59@YAHOO.COM31_Mon, 16 Nov 2009 15:24:25 +0000484_UTF-8 Thanks for the replies. You pointed me in the right direction. The traces I had showed an E79002 reason code trying to run stored procedure SYSIBM.SQLPROCEDURECOLS, which is a timeout. Our timeout (STORTIME was set to 1200 seconds). The reason for the timeout was that the WLM application environment to be used was in a QUIESCED state. This was done just prior to an OS upgrade on that LPAR (I don't know why). Once the WLM was resumed, the connect is now immediate. bill [...] 1639 29 33_ddl from sysibm.sysviews db2 v7.114_Sherry Simmerl20_ssimmerl@CSIHOME.COM31_Mon, 16 Nov 2009 14:31:52 +0000324_utf-8 I found this 3 part article on listserver and would like to know if anyone has used this recently? Are there any new revisions?

Gen DDL from DB2 Catlg V2 - Part 1 From: Michael Hannan Reply-To: DB2 Data Base Discussion List Date: Wed, 12 Jan 2000 01:38:04 +1000 [...] 1669 71 37_Re: ddl from sysibm.sysviews db2 v7.114_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Mon, 16 Nov 2009 17:21:32 +0000465_utf-8 Did not re-read the 8 year old article. There are DDL generators on the IDUG CODEPAGE. I have used them for older DB2 reoleases and in they work with some minor changes to the REXX.

IBM has a service aid that generates DDL, Its intention is to provide documention for RUNSTATS caused performance problems so it prints STATS and generates the DDL for the related database/tablespace/table. I assume one could obtain access via a defect or Q&A PMR. [...] 1741 473 59_Re: Fw: Row Number function as replacement for Max Subquery12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 16 Nov 2009 10:09:45 -0800624_iso-8859-1 Thanks. Interesting is that we adjusted a process removing the max subquery with this method and it took more CPU and longer wall clock to use this way versus the old MAX subquery. From one of our programmers: "The new way is less efficient than the current way we are doing the query with the MAX. The test bed was 8386 records. Old queries START 2009320.1842 STOP 2009320.1843 CPU 0 MIN 18.65 SEC SRB 0MIN 00.39 SEC New queries START 2009320.1829 STOP 2009320.1833 CPU 0 MIN 19.92 SEC SRB 0MIN 00.40 SEC" Wondery why? Myron ________________________________ From: James Campbell [...] 2215 46 21_[JOB] DBA Opportunity14_Benjamin Kniaz26_benjamin.x.kniaz@CHASE.COM31_Mon, 16 Nov 2009 18:44:48 +0000613_utf-8 Hi – we are looking for a z/OS DB2 DBA in Wilmington Delaware. This is a full time, non contract position.

Requisite HR blurb to follow:

The ideal candidate should be an experienced DBA using DB2 in a Z/OS mainframe environment. Candidate should be capable of investigating and resolving performance issues within the DB2 subsystem. Candidate should be well versed in database backup and recovery, disaster recovery, DB2 performance and problem determination in a 24 x 7 environment. Experience with other relation or hierarchical databases a plus. Minimum 3 years experience with DB2. [...] 2262 64 41_Re: Easy way to Connect from z/os to LUW?12_Susan Gudell28_susan.gudell@CPA.STATE.TX.US31_Mon, 16 Nov 2009 21:24:25 +0000460_utf-8 Have you considered setting trust_allclnts in the DBM cfg. to "DRDA only" and trust_clntauth to "server"? From the Info Center: Setting this parameter to "DRDAONLY" protects against all clients except clients from DB2® for OS/390® and z/OS®, DB2 for VM and VSE, and DB2 for OS/400®. Only these clients can be trusted to perform client-side authentication. All other clients must provide a user ID and password to be authenticated by the server. [...] 2327 38 22_DB2 V9 CM in z/OS 1.100_24_carol.sutfin@REGIONS.COM31_Mon, 16 Nov 2009 18:34:37 -0600427_US-ASCII We just converted to V9 CM and I am getting messages

DSNT715E -DB2T ERROR 00C90004 READING 572 SYSIBM.DSNRLMT01.

We do use RLF but have it turned off right now. DSNRLMT01 has been built

If I do start it then the subsystem abends with a S04F.

Has anyone else encountered this problem.

TIA Carol Sutfin Corporate DBA Regions Financial Corp. (205)261-5214 carol.sutfin@regions.com [...] 2366 40 26_Re: DB2 V9 CM in z/OS 1.109_Jeff Kram24_Jeff.Kram@WELLSFARGO.COM31_Mon, 16 Nov 2009 21:16:25 -0600501_utf-8 What level of maint are you on? DB2 DBS Office: 612-316-0634 Cell: 612-708-7860

This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. [...] 2407 490 16_IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Tue, 17 Nov 2009 10:30:51 +0100486_iso-8859-1 Hello everyone!!





I have two queries that return the same result.





I would like to use the one with the SUBQUERY statement but I'm not able to obtain the same performance

as I get with the first one.





Any ideas to speed up the one with SUBQUERY?





1)

SELECT COUNT(*)

FROM MDOR.HADT099

WHERE

T099_SCTCB IN ('B1', 'B3', 'H2', 'SE', 'SF', 'SG', '01') [...] 2898 548 20_AW: IN and SUBSELECT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 17 Nov 2009 11:47:32 +0100332_iso-8859-1 Hi

In the first query you have a list of constants, so I guess, you will see a matching index access using INLIST-processing. In the second query there is a subquery, so the access path can be very different, depending on the DB2 version. What version are you running. And can you provide the explain output? [...] 3447 1239 24_Re: AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Tue, 17 Nov 2009 12:31:34 +0100342_iso-8859-1 Hello Walter!





This is the first query explain:





Query Q Collect. Progname Pl M Ac M I T Table

S Number Bl (COLLID) (Packg) No T Ty Co O No Owner Table Name

* * * * * * * * * * * *

-- ----------- -- -------- -------- -- - -- -- - -- -------- ------------------ [...] 4687 1109 32_AW: [DB2-L] AW: IN and SUBSELECT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 17 Nov 2009 15:43:19 +0100395_iso-8859-1 Hello Jose

Well, the explain shows the difference. For the first query you have a 1 matching index access (Type N), but for the second you have a tablespace scan for the big table Have the columns T099_SCTCB exactly the same definition? Or, but it seems to be too easy, does the column T099_SCTCB exist in table SISTCAM.HADT099,because you omitted the QBLOCK_TYPE column. [...] 5797 804 36_Re: AW: [DB2-L] AW: IN and SUBSELECT14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Tue, 17 Nov 2009 16:24:53 +0100493_iso-8859-1 Hi,





I think that both accespath are starting with a tablespacescan ( AcTy = R )

The second accespath has also an additional tablespacescan and a sort of the data.





greetings,





Patrick Steurs





From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Walter Janien Sent: dinsdag 17 november 2009 15:43 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] AW: [DB2-L] AW: IN and SUBSELECT [...] 6602 100 24_Re: AW: IN and SUBSELECT14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Tue, 17 Nov 2009 16:58:48 +0100383_ISO-8859-1 Hello Jose,

try INNER JOIN or WHERE EXISTS with correlated subquery.

If there is no Unique Index on the column used in the SELECT list of the subquery, DB2 will eliminate the duplicates and perform the subquery this way. Only if there is a Unique Index, DB2 will be able to convert this Subquery to a Join, using Matching Index Scan on the Outer Table. [...] 6703 108 26_Re: DB2 V9 CM in z/OS 1.100_24_carol.sutfin@REGIONS.COM31_Tue, 17 Nov 2009 10:07:08 -0600577_US-ASCII Details, details

RSU0811. That may be the problem there.

Carol Sutfin Corporate DBA Regions Financial Corp. (205)261-5214 carol.sutfin@regions.com





Jeff Kram To Sent by: IDUG DB2-L@WWW.IDUGDB2-L.ORG DB2-L cc Subject Re: [DB2-L] DB2 V9 CM in z/OS 1.10

11/16/2009 09:16 PM



Please respond to IDUG DB2-L











What level of maint are you on? DB2 DBS Office: 612-316-0634 Cell: 612-708-7860 [...] 6812 165 26_Re: DB2 V9 CM in z/OS 1.109_Jeff Kram24_Jeff.Kram@WELLSFARGO.COM31_Tue, 17 Nov 2009 10:24:43 -0600661_us-ascii Check out PK75926 / UK44440, we had a similar issue with DSNRLMT01 until this fix was applied. Closed on 3/14/09.



DB2 DBS Office: 612-316-0634 Cell: 612-708-7860 http://ds.wellsfargo.com/db2zos/ http://ds.wellsfargo.com/

>This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. [...] 6978 388 106_Re: New England DB2 User's Group Meeting November 19 Don't Forget, Seats Available, Expect the Unexpected,7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 17 Nov 2009 11:22:08 -0800580_utf-8 One last reminder. Our next meeting is scheduled for Thursday November 19 in Sturbridge Mass. We are delighted to have the following lineup of terrific speakers, Sheryl Larsen, Curt Cotner, and Phil Czacharowski. See below for details Seats are limited so sign up now by replying to the address below. There will be sticky buns, fabulous prizes and no turkey! See you soon. November 19, 2009 - Meeting Notice The next meeting of the New England DB2 User's group will occur on Thursday November 19, 2009 in the Tap Room at the Publick House in Sturbridge Mass. As [...] 7367 36 36_DB2 V8 on z/OS Message code DSNR035I14_Leblanc, Fritz27_fritz.leblanc@SALLIEMAE.COM31_Tue, 17 Nov 2009 16:26:50 -0500431_us-ascii Hi.

We occasionally get a message when an application program runs long without any checkpoints. A sample message looks like this:

DSNR035I # DSNRPBCW WARNING - UNCOMMITTED UR AFTER 30 CHECKPOINTS

There is a ZPARM (URCHKTH) which controls this message, and I believe the default value is 5. I'm wondering what other sites use for this value, and what actions they take when this message appears. [...] 7404 61 41_Unable to restart DB2 - 00C90101 DSNIARPL12_Brian Church15_bchurch@QVC.COM31_Tue, 17 Nov 2009 21:38:34 +0000379_UTF-8 We are on the phone with IBM.

Had an error on DB2 that caused DB2 to stop at 10:30 this morning. Had to cancel/force xxxxMSTR/DBM1/DIST, IRLM shut down by itself.

On restart 28 txns in flight. Getting following error for many objects that were being used by the transactions. IBM identified a PTF that matched the error but it did not resolve the issue. [...] 7466 1341 24_Re: AW: IN and SUBSELECT12_Tony Andrews22_tandrews@THEMISINC.COM31_Tue, 17 Nov 2009 17:13:58 -0500315_iso-8859-1 You need to get away from the tablescan on MDOR.HADT099. Does this table have an index

on column = T099_SCTCB? If there is an index on this column, I would try a 2 table inner join, where

DB2 should choose the SISTCAM.HADT099 table as the starting table and execute a nested loop join [...] 8808 149 45_Re: Unable to restart DB2 - 00C90101 DSNIARPL15_Jewell, Scott A26_scott.jewell@FREDMEYER.COM31_Wed, 18 Nov 2009 00:38:52 -0500364_us-ascii Hi Brian,

You didn't mention if you were in CM or not, but if you are you might try starting DB2 with the V7 libraries.

I seem to remember running into something like this with my CM "sandbox" that was a member of a group that had V7 members. Don't remember the details, but seem to remember starting it V7 and the problem "went away". [...] 8958 227 26_Re: DB2 V9 CM in z/OS 1.1011_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Wed, 18 Nov 2009 07:30:06 +0000856_UTF-8 Can also have a look at PM00162 : http://www-01.ibm.com/support/search.wss?q=DSNRLMT01&ibm-search.x=0&ibm-search.y=0&ibm-search=Search&apar=include&lang=en+en&spc=&stc=&rs=0&loc=en_US&from=tss&cs=utf-8&cc=us

Regards, Nenad

On Tue, 17 Nov 2009 10:24:43 -0600, Jeff Kram wrote:

>Check out PK75926 / UK44440, we had a similar issue with DSNRLMT01 until this fix was applied. Closed on 3/14/09. > > >DB2 DBS >Office: 612-316-0634 >Cell: 612-708-7860 >http://ds.wellsfargo.com/db2zos/ >http://ds.wellsfargo.com/ > >>This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in [...] 9186 2059 36_Re: AW: [DB2-L] AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Wed, 18 Nov 2009 08:51:26 +0100639_iso-8859-1 Hi





First of all thanks for your help!





I also think that both are table space scans (Access Type = 'R')





__________________________ Jos A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Produccin Informtica



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Steurs Patrick Enviado el: martes, 17 de noviembre de 2009 16:25 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] AW: [DB2-L] AW: IN and SUBSELECT [...] 11246 1806 44_AW: [DB2-L] AW: [DB2-L] AW: IN and SUBSELECT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Wed, 18 Nov 2009 09:26:35 +0100583_iso-8859-1 Hi

Yes, in the second query, both are tablespace scans, but the problem is, you get a tablespace scan for MDOR.HDT099, but in the first query you get a matching index access for that table. That means, there is an index on T099_SCT099 (otherwise you wouldn't get a matching index access). The question is, why doesn't the optimizer pick up this index for the second query. One reason could be, that column T099_SCT099 does not exist in table HDT099.STSTCAM. A second reason could be, that both columns are not defined in the same way and last but not least, a [...] 13053 84 36_Re: AW: [DB2-L] AW: IN and SUBSELECT11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 18 Nov 2009 09:28:28 +0100413_ISO-8859-1 do a RUNSTATS on the tablespacxe that holds the SISTCAM HADT099 table. Could it be that this space is SIMPLE ?? If so, drop it and create a SEGMENTED version and try again...





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strae 5 40470 Dsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...] 13138 365 60_Deadlock : the information in the master of db2 is not good.14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 18 Nov 2009 10:35:57 +0100527_iso-8859-1 Hi,





We are on db2 v8.1 rsu0806 and z/Os v1.8

I dectected a strange deadlock in the master of db2. Did anyone already had the same problem ? Does anyone has a solution ?





Info from db2-master :

20.49.10 STC33058 DSNT375I
888 CORRELATION-ID=CCO15893

888 CONNECTION-ID=BATCH

888 LUW-ID=BEBNBB01.NA15DB2.C5192935B227=35511

888 THREAD-INFO=CCO15:*:*:*

888 IS DEADLOCKED WITH PLAN=" 0 " 0 WITH [...] 13504 94 43_IBM 2009 Technotes (FAQ) about DB2 for z/OS10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Wed, 18 Nov 2009 05:20:47 -0500585_utf-8

Hi DB2 user,

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

Last update on Monday, November 16th 2009



Here are several new Technotes (FAQ), available on IBM DB2 for z/OS website:





- What is DB2 Accounting Class 2 Not Accounted Time?​ - Keywords for searching APAR text - BIND ERROR SQLCODE109 TOKENS=INTO in Version 9 CM - Converting an external SQL procedure to a native SQL procedure​ - Preallocate EDITUTL1 and EDITUL2 datasets before you run DB2 V9 DSNTINST CLIST [...] 13599 1298 24_Re: AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Wed, 18 Nov 2009 11:56:17 +0100466_iso-8859-1 Hello!





I have defined a UNIQUE index on SISTCAM.HADT099(T099_SCTCB), this is the new explain:





SELECT COUNT(*)

FROM MDOR.HADT099

WHERE T099_SCTCB IN (

SELECT T099_SCTCB FROM SISTCAM.HADT099

)

WITH UR;





Query Q Collect. Progname Pl M Ac M I T Table

S Number Bl (COLLID) (Packg) No T Ty Co O No Owner Table Name

* * * * * * * * * * * * [...] 14898 415 36_Re: AW: [DB2-L] AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Wed, 18 Nov 2009 11:57:07 +0100571_iso-8859-1 By the way I recreated SISTCAM.HADT099 segmented but the problem still remains...





__________________________ Jos A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Produccin Informtica



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Roy Boxwell Enviado el: mircoles, 18 de noviembre de 2009 9:28 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] AW: [DB2-L] AW: IN and SUBSELECT [...] 15314 50 32_Regarding NL Join and Merge join10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Wed, 18 Nov 2009 17:37:27 +0530432_ISO-8859-1 Hello List, We are on DB2 v8.

I have a SQL with inner join on two tables like,

select from TAB1, TAB2 where tab1.col1 = tab2.col2 and tab1.col2 = tab2.col2 and (tab1.col3 = 'x' or tab2.col3 ='Y') order by blah1,blah2,blah3

Basically, there is no matching index column predicate and the query is absolutely static. There are clustering indexes on both the tables on the join columns. [...] 15365 78 36_Re: Regarding NL Join and Merge join11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 18 Nov 2009 14:09:42 +0100649_ISO-8859-1 "I tried putting cardinality and frequency stats into catalog"

How?

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

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 15444 128 36_Re: Regarding NL Join and Merge join10_Dave Nance16_dav1mo@YAHOO.COM31_Wed, 18 Nov 2009 05:50:30 -0800509_iso-8859-1 If you still have a copy of the old explain you can use an opthint and rebind this package. Barring that you could make the cluster ratios look as bad as they were before, bind the pkg, then change the ratios back to true info. One other item, this and/or condition on the flags, are these heavily skewed columns? Meaning do the x or y filter out most of the data from the two tables? The reason I ask this is we were just discussing on the DB Forum the other day the use of an index on a y/n [...] 15573 242 36_Re: Regarding NL Join and Merge join10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Wed, 18 Nov 2009 20:11:55 +0530272_ISO-8859-1 Hi Dave,

I do have the old copy in a backup table that I could insert back. I would use opthint the last resort. First, trying to see if I could bring back the Merge Join back without changing the SQL or using OPTHINT (too big an ask ? IT seems so) [...] 15816 108 36_Re: Regarding NL Join and Merge join10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Wed, 18 Nov 2009 20:14:17 +0530613_ISO-8859-1 Roy,

I put the cardinality and frequency stats (for COUNT 1) using Runstats.







On Wed, Nov 18, 2009 at 5:37 PM, DB2sysprog wrote:

> Hello List, We are on DB2 v8. > > I have a SQL with inner join on two tables like, > > select > from TAB1, TAB2 > where tab1.col1 = tab2.col2 > and tab1.col2 = tab2.col2 > and (tab1.col3 = 'x' or tab2.col3 ='Y') > order by blah1,blah2,blah3 > > Basically, there is no matching index column predicate and the query > is absolutely static. There are clustering indexes on both the tables [...] 15925 70 45_Re: Unable to restart DB2 - 00C90101 DSNIARPL15_Hagedorn, Linda24_Linda_Hagedorn@MEDCO.COM31_Wed, 18 Nov 2009 10:06:44 -0500417_us-ascii 003.RCRSC 02 is the checkpoint/restart serial controller.

What was the original error that caused DB2 to stop? Do you mean hang? Do you have XRC? What happened that the decision was made to bring DB2 down in this manner? Were MVS commands used against DB2: FORCE or modiFy (abbrev. F)? Is this a test or production system? Can you publish the sequence of events and the current restart log here? [...] 15996 30 36_Re: Regarding NL Join and Merge join35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_walter.janissen@ITERGO.COM31_Wed, 18 Nov 2009 15:22:52 +0000335_utf-8 Do yiu have VE available. My guess is, that the predicate (tab1.col3 = 'x' or tab2.col3 ='Y') is for whatever reason stage2 and therefore evaluated after the join. Are the other columns part of an index, so that optimizer believes index screening would eliminate most of the rows, so that only a few rows run into the join? [...] 16027 196 24_Re: AW: IN and SUBSELECT14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Wed, 18 Nov 2009 16:40:52 +0100535_iso-8859-1 Hello Jose,

defining the UNIQUE INDEX, you eliminated the subquery processing. Good.

But you have still the problem, that the wrong table is used as the outer table (the large one) and that it is read sequentially. IMO this is due to the fact that the small table has no valid statistics. If DB2 knew that the small table had only 7 rows, it would sure use the small table as the outer table and use a matching index scan on the large table. Try it. Do RUNSTATS on the small table and then EXPLAIN again. [...] 16224 47 24_Re: AW: IN and SUBSELECT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_walter.janissen@ITERGO.COM31_Wed, 18 Nov 2009 15:13:42 +0000396_utf-8 Hello Jose

Can put in a GROUP BY in your first query and see what happens:

SELECT COUNT(*)

FROM MDOR.HADT099

WHERE T099_SCTCB IN (

SELECT T099_SCTCB FROM SISTCAM.HADT099 GROUP BY T099_SCTCB )

WITH UR;

I guess, you are on DB2 V9 and global optimization comes up with your access path. With group by you will disable global optimization. [...] 16272 22 18_test please ignore16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV31_Wed, 18 Nov 2009 16:21:18 +0000554_utf-8 test - please ignore

_____________________________________________________________________

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

http://www.IDUG.org membership is now open and free for all. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...] 16295 83 40_Re: DB2 V8 on z/OS Message code DSNR035I13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 18 Nov 2009 10:53:13 -0800463_us-ascii Hey Fritz! How goes it?

The default for URCHKTH in DB2 9 is 0, which disables the option. Ours is set to 20, and we never see the message. I plan to lower the value when we upgrade to DB2 X and I have more control over checkpointing. Right now we checkpoint using number of log records, which fluctuates throughout the day. I don't want to lower the value of URCHKTH now and get a bunch of messages just because there is extra-heavy logging. [...] 16379 37 47_Does DB2 9 for z/OS work with CA TCP/IP Stack ?13_Bart Verboven23_bart.verboven@DOMMEL.BE31_Wed, 18 Nov 2009 20:54:29 +0000499_UTF-8 We are just looking for reassurance that DB2 9 will work with CA TCPaccess Communication Server for z/OS from any colleagues out there which have successful incoming and outgoing distributed connections in such a configuration.

We are preparing DB2 9 migration from V8 and see 64-bit pre-req PTFs for IBM's TCP/IP stack. We believe that TCPaccess does not support 64-bit mode, and therefore are concerned. So, if you have tried this already, do let us know if you were successful. [...] 16417 34 30_Need to partition- what steps?5_anand19_mahadea@LABCORP.COM31_Wed, 18 Nov 2009 22:12:19 +0000574_UTF-8 Hi, On DB2 for z/OS v8.

We have large table (500 mil rows) currently in a segmented tablespace. We want to leverage this table controlled partitioning. What are our next steps? Do we back up data, drop and recreate tablespace as partitioned, create table with appropriate partitioning criteria, reload data?

Thanks!

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 16452 64 34_Re: Need to partition- what steps?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 18 Nov 2009 17:26:49 -0500433_us-ascii Do you have enough space to have two versions of the table present at once? I'd recommend creating a new partitioned table/TS, unloading from the old, loading to the new, rename the tables and check things out. Then drop the old once you're satisfied.

If you don't have enough space to have it out there twice, take _two_ unloads, not one, and make sure it's in Readonly mode while you do and until you drop it. [...] 16517 51 34_Re: Need to partition- what steps?13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 18 Nov 2009 17:43:23 -0500473_UTF-8 My favorite way to do this lately has been:

1. Create a new partitioned tablespace and a table with a new name. 2. Unload data from the existing table and load to the new. 3. Rename the two tables so that the new table now has the name of the old. 4. Drop and recreate any triggers on the table. 5. Re-direct any referential integrity constraints to the new table and resolve check pending flags. 6. Image Copy 7. Rebind all packages accessing the table. [...] 16569 309 189_Upcoming IDUG Webcast - "The DB2Night Show Episode #6 - Free DB2 Application Development Tools - Scott Hayes, DBI Software" - Friday, November 20, 2009 at 10:00 am USA Central Standard Time13_David Chapman24_David.Chapman@IAG.COM.AU31_Thu, 19 Nov 2009 11:22:14 +1100768_us-ascii

_______________________________________________________________________________________

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





Hello DB2 List,

The DB2Night Show Episode #6 - Free DB2 Application Development Tools - 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 limited, so register early. [...] 16879 173 34_Re: Need to partition- what steps?10_Dave Nance16_dav1mo@YAHOO.COM31_Wed, 18 Nov 2009 17:08:29 -0800581_iso-8859-1 Another set of steps you may want to consider is do you want to minimize your down time? Unloading and loading (with stats) a half billion rows is not the quickest act you can accomplish on your database. Something you may want to consider is following the steps as Dave suggested, but add a couple more to include one or more triggers and shadow tables. Such as... 1. Create a new partitioned tablespace and a table with a new name. 2. Create additional tables with the same structure, one for each type of modifying instruction (insert, update, delete) 3. Create [...] 17053 71 38_Kuhr, Richard L. is out of the office.0_13_RLKUHR@UP.COM31_Wed, 18 Nov 2009 19:14:51 -0600629_US-ASCII

I will be out of the office starting 11/18/2009 and will not return until 11/20/2009.

I will respond to your message when I return. If this is an emergency please contact Computer Scheduling. **

This message and any attachments contain information from Union Pacific which may be confidential and/or privileged. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited by law. If you receive this message in error, please contact the sender immediately and delete the message and any attachments. [...] 17125 179 40_Re: DB2 V8 on z/OS Message code DSNR035I15_Chris Hoelscher21_choelscher@HUMANA.COM31_Wed, 18 Nov 2009 20:21:30 -0500553_US-ASCII looking at our DSNTUJUZ jobs, it appears we had the value set to 3 across the board, but now it is set to 6

Chris Hoelscher Senior IDMS & DB2 Database Administrator Humana Inc 502-476-2538 choelscher@humana.com

you only need to test the programs that you want to work correctly











From: "Leblanc, Fritz" To: DB2-L@WWW.IDUGDB2-L.ORG Date: 11/17/2009 17:08 Subject: [DB2-L] DB2 V8 on z/OS Message code DSNR035I Sent by: IDUG DB2-L [...] 17305 413 34_Re: Need to partition- what steps?19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Wed, 18 Nov 2009 20:53:10 -0500322_us-ascii Again, I am stunned than no one has mentioned cross-loading instead of unload/reload. It is easier, safer and faster. I have not done an unload/reload since 2001. (Of course, I have lots of DASD...) You can have a separate cursor that specifies the value range for each partition and cross-load in parallel. [...] 17719 158 34_Re: Need to partition- what steps?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 19 Nov 2009 07:20:21 +0200618_iso-8859-1 Hi,

I would like to have Runstats before the rebind :-) and rebind trigger package explain(yes)

Isaac Yassin

---- Original message ---- >Date: Wed, 18 Nov 2009 17:43:23 -0500 >From: David Simpson >Subject: Re: [DB2-L] Need to partition- what steps? >To: DB2-L@WWW.IDUGDB2-L.ORG > >My favorite way to do this lately has been: > >1. Create a new partitioned tablespace and a table with a new name. >2. Unload data from the existing table and load to the new. >3. Rename the two tables so that the new table now has the name of the old. >4. Drop and recreate [...] 17878 119 34_Re: Need to partition- what steps?10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Thu, 19 Nov 2009 10:50:38 +0530486_ISO-8859-1 May I ask you, do you have any DBA tool to assist this ? IBM's or Vendor's ? Because, without a tool, it could be a long process identifying dependant objects from catalog.

If you have decided on partitioning keys and # of partitions, you could unload the huge table in parts specifying the key range. You would have 'n' small unload files which you could load it back after you create the new table by any of the convenient methods mentioned by other gentlemen. [...] 17998 116 36_Re: Regarding NL Join and Merge join10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Thu, 19 Nov 2009 10:57:45 +0530367_ISO-8859-1 Indexes are quite strange. First key of the primary index has cardinality '1'. Unable to bring back the Merge Join for this SQL, I created an index that increased the matchcols to max possible. The SQL finished cheaper than the Merge Join. The table *mostly* has read cursors for batch So, I decided, an extra index won't bring a snag for other SQLs. [...] 18115 147 24_Re: DBA authority survey0_24_hhuang@DCCSH.ICBC.COM.CN31_Thu, 19 Nov 2009 16:24:03 +0800779_GB2312 Thanks, Avram.

So, in your shop, you don't grant SYSADM to DBAs as an all-in-one solution, but grant kinds of privileges and authorities individually. Is this too complex to manage?









Avram Friedman



ռ DB2-L@WWW.IDUGDB2-L.ORG

Re: [DB2-L] DBA authority survey











I do not think the data change authority originates with SYSADM. If it did the authority could be restricted today with SYSCTL authority. This issue lies with change control. In tightly controlled shops the authority to create and drop databases is often centralized. The creator of a database automatically gets DBADM on the database and has full data [...] 18263 304 24_Re: AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Thu, 19 Nov 2009 12:02:47 +0100348_iso-8859-1 Hi Bernd!

I did a RUNSTAT on the small table but the EXPLAIN is still again. This is the way I did:

RUNSTATS TABLESPACE MDORDBHA.MDPHA099 TABLE (ALL) SAMPLE 25 INDEX (ALL) KEYCARD FREQVAL NUMCOLS 3 COUNT 10 SHRLEVEL CHANGE REPORT NO UPDATE ALL HISTORY NONE /*

How you change outer table???

Thanks a lot! [...] 18568 134 24_Re: AW: IN and SUBSELECT14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Thu, 19 Nov 2009 13:16:05 +0100443_iso-8859-1 Hi,

I would never use "sample 25" on small tables.

greetings,

Patrick Steurs

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jose Antonio Sent: donderdag 19 november 2009 12:03 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: IN and SUBSELECT

Hi Bernd!

I did a RUNSTAT on the small table but the EXPLAIN is still again. This is the way I did: [...] 18703 404 24_Re: AW: IN and SUBSELECT12_Jose Antonio17_jamorcillo@CAM.ES31_Thu, 19 Nov 2009 13:36:06 +0100509_iso-8859-1 Sorry I really used 100%, that example was analyzing the outer table, the big one

greetings Jos

__________________________ Jos A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Produccin Informtica

__________________________





-----Mensaje original----- De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Steurs Patrick Enviado el: jueves, 19 de noviembre de 2009 13:16 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: Re: [DB2-L] AW: IN and SUBSELECT [...] 19108 132 34_Re: Need to partition- what steps?13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 19 Nov 2009 07:04:38 -0600440_iso-8859-1 Picky picky :)

Yes Runstats should be in there... Probably inline with the load.

-----Original Message----- From: Isaac Yassin Sent: Wednesday, November 18, 2009 11:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Need to partition- what steps?

Hi,

I would like to have Runstats before the rebind :-) and rebind trigger package explain(yes) [...] 19241 74 40_Re: DB2 V8 on z/OS Message code DSNR035I14_Benjamin Kniaz26_benjamin.x.kniaz@CHASE.COM31_Thu, 19 Nov 2009 07:13:32 -0600719_us-ascii Hi Fritz,

I have used this extensively. Actually in conjunction with a couple of others that Willi had blogged about: http://it.toolbox.com/blogs/db2zos/do-you-know-you-can-monitor-your-unit s-of-recovery-ur-8642

I had periodically modified the value (down). I don't remember what I started with 50? 30? Whatever, so whenever we found something running long, I would go after the team that owned the program causing the problem. When I got pushback, I would break out the following justifications for having it fixed. 1) Can cause timeouts. 2) Long running transactions can impact recovery in a dramatic failure, or Disaster recovery situation. 3) Rollbacks should something abend will take [...] 19316 325 57_Error trying to load zLinux table from zOs file using NFS37_=?iso-8859-7?B?xMfMz8rBIMrBy8vJz9DH?=24_dimokap@INTERAMERICAN.GR31_Thu, 19 Nov 2009 15:25:15 +0200387_iso-8859-7 Hello dear Listers,





We want to load a table residing on DB2 v9.5 on zLinux from a file created on zOs 1.9. We have exported an HFS from zOs which has been mounted on the zLinux image (/nfs_prod). This is the location we put the zOs file and this is where we want to load the table from (to avoid ftp). However, when we try to do the following load [...] 19642 380 32_AW: [DB2-L] AW: IN and SUBSELECT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 19 Nov 2009 14:24:09 +0100782_iso-8859-1 Jose

Did you try the GROUP BY in between the in-subquery?

Mit freundlichen Gren Walter Janien

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

Vorsitzender des Aufsichtsrates: Jrgen Vetter Geschftsfhrung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schn. Sitz: Dsseldorf, Handelsregister: Amtsgericht Dsseldorf, HRB 37996



-----Ursprngliche Nachricht----- Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Jose Antonio Gesendet: Donnerstag, 19. November 2009 13:36 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: [SPAMVERDACHT] Re: [DB2-L] AW: IN and SUBSELECT [...] 20023 80 24_Re: AW: IN and SUBSELECT14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 19 Nov 2009 15:11:47 +0100455_ISO-8859-1 Hi Jose,

as another poster has mentioned, the key to performance is that DB2 uses nested loop join or N-type processing and that the small table is accessed first. If this is not the case, this can be due to several reasons:

- duplicate result rows in the subquery (which we eliminated by defining a unique index - by the way: this is why I always prefer joins over subqueries; joins normally perform better - rule of thumb) [...] 20104 127 4_test18_Bertolotti Ermanno37_ermanno.bertolotti@INTESASANPAOLO.COM31_Thu, 19 Nov 2009 16:57:13 +0100747_us-ascii Everything works? The last mail is dated 14 November

Prima di stampare, pensa all'ambiente ** Think about the environment before printing

________________________________ Il presente messaggio, inclusi gli eventuali allegati, ha natura aziendale e potrebbe contenere informazioni confidenziali e/o riservate. Chiunque lo ricevesse per errore, ? pregato di avvisare tempestivamente il mittente e di cancellarlo. E' strettamente vietata qualsiasi forma di utilizzo, riproduzione o diffusione non autorizzata del contenuto di questo messaggio o di parte di esso. Pur essendo state assunte le dovute precauzioni per ridurre al minimo il rischio di trasmissione di virus, si suggerisce di effettuare gli opportuni controlli [...] 20232 102 26_Re: Amount of real storage13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 19 Nov 2009 09:21:31 -0800319_us-ascii Hi Willie. We have 7 gig configured on each LPAR, with 1 prod and 4 non-prod data sharing members on each. We run right on the edge, meaning that if there is a storage event such as an SVC dump, the system will page. If there is a DB2 SVC dump (which is typically very large), the system will page a lot. [...] 20335 151 32_GUPI ? -- almost Friday question18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM31_Thu, 19 Nov 2009 15:00:18 -0500653_us-ascii Can anyone explain what the notation GUPI means in pdf format of the DB2 manuals? Just very curious.

Thanks, Deborah



_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 20487 259 36_Re: GUPI ? -- almost Friday question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 19 Nov 2009 15:21:03 -0500710_us-ascii "Gotta Upgrade, Poor Information"?

________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of GRESHAM, DEBORAH B Sent: Thursday, November 19, 2009 3:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] GUPI ? -- almost Friday question

Can anyone explain what the notation GUPI means in pdf format of the DB2 manuals? Just very curious.

Thanks, Deborah



________________________________

[http://www.idug.org/images/M_images/idug%20org.jpg]

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. [...] 20747 261 36_Re: GUPI ? -- almost Friday question8_Bob Kota23_bob.kota@EXELONCORP.COM31_Thu, 19 Nov 2009 14:20:49 -0600640_us-ascii BACK_1.1.1 General-use Programming Interface and Associated Guidance Information





General-use Programming Interfaces allow the customer to write programs that obtain the services of DB2 Version 9.1 for z/OS.

General-use Programming Interface and Associated Guidance Information is identified where it occurs by the following markings:



General-use Programming Interface and Associated Guidance Information... [...] 21009 24 61_Re: Error trying to load zLinux table from zOs file using NFS7_J Anand30_anand.jayachandran@DAIMLER.COM31_Thu, 19 Nov 2009 20:02:11 +0000627_utf-8 Did you check what is reason code 22?

Are the files available in the directory? Check with ls command

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 21034 37 61_ICF Catalog how to Split into Sub-Systems per IBM Recommended9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV31_Thu, 19 Nov 2009 15:43:57 -0500591_us-ascii Our Shop is a PeopleSoft environment running DB2 v8.1 NFM, we currently have 1 ICF catalog that holds everything, My System Programmer is asking us DBA's some Questions on Braking up the ICF Catalog into Multi Parts per what IBM is recommending, how do we go about doing this, are there a specify order that we need to followed? Not sure ? there are Bootstraps, VSAM-files, Logs etc involved there are Thousands of entrees, we'll like to brake out into each subsystem we currently have 3 Non-Prod subsystem, and 1 production. How do we accomplish this in each sub-systems, any [...] 21072 68 36_Re: GUPI ? -- almost Friday question12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 19 Nov 2009 15:47:51 -0500 21141 162 36_Re: GUPI ? -- almost Friday question13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 19 Nov 2009 14:59:57 -0600616_ISO-8859-1 I hate to actually give a serious answer so close Friday.... however,

General Use Program Interface

Willie

GRESHAM, DEBORAH B wrote: > > Can anyone explain what the notation GUPI means in pdf format of the > DB2 manuals? Just very curious. > > > > Thanks, > > Deborah > > > > > ------------------------------------------------------------------------ > > IDUG - The Worldwide DB2 User Community! > > The IDUG DB2-L Listserv is only part of your membership in IDUG. If > you are not already an IDUG member, please register here. > > 21304 345 36_Re: GUPI ? -- almost Friday question13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US31_Thu, 19 Nov 2009 13:03:04 -0800715_us-ascii Take your pick:

Guinea Pig (robotic children's toy) General-Use Programming Interface (IBM) Globally Unique Persona Identifier ?

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Thursday, November 19, 2009 12:21 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] GUPI ? -- almost Friday question





"Gotta Upgrade, Poor Information"?





________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of GRESHAM, DEBORAH B Sent: Thursday, November 19, 2009 3:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] GUPI ? -- almost Friday question [...] 21650 40 65_Re: ICF Catalog how to Split into Sub-Systems per IBM Recommended9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV31_Thu, 19 Nov 2009 16:03:38 -0500577_us-ascii Hi Group, I have a Question but not sure if I'm saying this correctly, here goes, Our Shop is a PeopleSoft environment running DB2 v8.1 NFM, we currently have 1 ICF catalog that holds everything, My System Programmer is asking us DBA's some Questions on Braking up the ICF Catalog into Multi Parts per what IBM is recommending, how do we go about doing this, are there a specify order that we need to followed? Not sure ? there are Bootstraps, VSAM-files, Logs etc involved there are Thousands of entrees, we'll like to brake out into each subsystem we currently [...] 21691 367 36_Re: GUPI ? -- almost Friday question35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 19 Nov 2009 16:14:53 -0500427_iso-8859-1 If you add another P it's a fish?

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

Buffer Pool Tool for DB2 on www.LinkedIn.com Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1 [...] 22059 293 36_Re: GUPI ? -- almost Friday question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 19 Nov 2009 13:25:46 -0800522_us-ascii GUPI also appears in the bookmanager format of the manuals. Didn't want anyone to think that the pdf's had any better content than bookmanager!

Is there anyone out there who can create a bookmanager version of the doc for DB2 X? Will bribes work? Starbucks? Homemade cookies?

Thanks, Cathy

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Willie Favero Sent: Thursday, November 19, 2009 1:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] GUPI ? -- almost Friday question [...] 22353 42 24_Re: DBA authority survey14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 19 Nov 2009 21:11:39 +0000458_utf-8 Forgive me if this reply is a duplicate, it appears my original did not make it to the list.

The original question / issue concerned a listers desire to restrict highly authorized (SYSADM) DBAs from also having data access.

To a large extent, this is impossible. Even with specialized permissions such as SYSCTL the DBA still has data access. This is a result of a non revokable authority granted to the creator of a database DBADM. [...] 22396 92 65_Re: ICF Catalog how to Split into Sub-Systems per IBM Recommended12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM31_Thu, 19 Nov 2009 15:54:35 -0600471_us-ascii My first reaction is, why would the SysProg be asking you DBA's anything about ICF catalogs, he should know more than you about it.

I must assume that each of your subsystems uses a different VCAT name (dataset high-level qualifier) even thought they are in the same ICF catalog. Unless he is wanting you to change high-level qualifiers, it should be not a huge deal to move one or more of your high-level qualifiers from one ICF catalog to another. [...] 22489 103 65_Re: ICF Catalog how to Split into Sub-Systems per IBM Recommended14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 19 Nov 2009 21:26:11 +0000365_utf-8 Please post the question to IBM and the recomendation. I suspect something got lost in the translation.

In general of each DB2 subsystem uses diffrent high level qualifers splitting the ICF catalog is not difficult but may result in an extended outage. There are IDCAMS / AMS utilities that will do the job. In addition there may be ISV products. [...] 22593 28 28_My replies are not appearing14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 19 Nov 2009 21:36:39 +0000454_utf-8 Per the subject I use the web interface to reply to postings. The replies are not appearing in the archivie I am copying my self on this posting so I can document it to the SYSOPS

Regards Avram Friedman

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 22622 95 36_Re: GUPI ? -- almost Friday question12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 19 Nov 2009 17:35:44 -0500 22718 60 61_Re: Error trying to load zLinux table from zOs file using NFS37_=?iso-8859-7?B?xMfMz8rBIMrBy8vJz9DH?=24_dimokap@INTERAMERICAN.GR31_Fri, 20 Nov 2009 09:47:33 +0200562_iso-8859-7 Hello,

Reason code 22 is not documented :-))) And yes, the input file is indeed there!

If the file is copied to another location on zLinux, the table is loaded fine. Which means the error occurs only with the NFS path...

Thanx

Best Regards,

Kalliopi A. Dimoka

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of J Anand Sent: Thursday, November 19, 2009 10:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Error trying to load zLinux table from zOs file using NFS [...] 22779 61 13_Reclaim Space4_Anil21_alisha_kale@YAHOO.COM31_Fri, 20 Nov 2009 12:37:17 +0000524_utf-8 Hi All !

Before I ask my question, the env details: DB2 for z/os; DB2 V8 ; z/os 1.09; DB2 datasets on non-sms volumes

I noticed that lately we get an -904 (00D70014) error even though there was plenty of space on the volume.

I am told it has to with how db2 manages the extents. It turns out that just because you have space on a volume doesn't mean that DB2 will use it. DB2 will use a volume for extents but only if it has not used it before(doesn't have a dataset on it). Let me explain a [...] 22841 91 65_Re: ICF Catalog how to Split into Sub-Systems per IBM Recommended9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV31_Fri, 20 Nov 2009 08:23:03 -0500603_us-ascii Hi Avram, Kirk, I think my system programmer is posing the Question to the DBA's to get some sort of ideas, not sure? But on each subsystem the High level Qualifiers are different for Example in one of the subsystem there are names like DB2L.DSNDBD.dbname.**, DB2T.DSNDBD.dbname.**, and in another there are names like DB2Q.DSNDBD.dbname.**, and production likes like DB2P.DSNDBD.dbname.** I'm not sure what IBM is recommending but I can surely asked to get more Info. I'll forward this e-mail to my System Programmer with the tools that was mentions thank you for the heads up and advice [...] 22933 81 17_Re: Reclaim Space10_DB2sysprog21_bala.db2dba@GMAIL.COM31_Fri, 20 Nov 2009 18:55:15 +0530543_ISO-8859-1 Anil,

Volumes and space management is something I do often but, I had not faced this situation in particular. A reorg would make DB2 to go back and use the 80% space left in V1, for sure.





On 11/20/09, Anil wrote: > Hi All ! > > Before I ask my question, the env details: > DB2 for z/os; DB2 V8 ; z/os 1.09; DB2 datasets on non-sms volumes > > I noticed that lately we get an -904 (00D70014) error even though there was > plenty of space on the volume. > > I am told it has [...] 23015 94 17_Re: Reclaim Space11_Greuter, Al18_Al.Greuter@SSA.GOV31_Fri, 20 Nov 2009 08:27:35 -0500382_us-ascii Anil,

I am curious to know what the primary quantity on the tablespace is. If it is over 2 gig, it will try to allocate another primary quantity. If the pack involved (after cleanup) does not have the space for another primary you will get a space error.

The other question that I have is what type of DASD is in the stogroup? Is it MOD-3 MOD-9 or what? [...] 23110 86 36_Re: GUPI ? -- almost Friday question10_Todd Burch17_toddburch@MAC.COM31_Thu, 19 Nov 2009 14:25:51 -0600570_US-ASCII General Use Programming Information.

as opposed to PSPI - or Product Specific Programming Information.

Todd



On Nov 19, 2009, at 2:00 PM, GRESHAM, DEBORAH B wrote:

Can anyone explain what the notation GUPI means in pdf format of the DB2 manuals? Just very curious.

Thanks, Deborah

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 23197 81 17_Re: Reclaim Space14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Fri, 20 Nov 2009 15:50:50 +0100639_us-ascii Anil,

For the last few years, we never had space-problems. We are using SMS, combined with db2-tablespace- parameters"priqty -1 & secqty - 1" and monthly scheduled reorgs.

greetings,

Patrick Steurs

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2sysprog Sent: vrijdag 20 november 2009 14:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Reclaim Space

Anil,

Volumes and space management is something I do often but, I had not faced this situation in particular. A reorg would make DB2 to go back and use the 80% space left in V1, for sure. [...] 23279 106 36_Re: GUPI ? -- almost Friday question12_Bill Shipley28_william_shipley@VANGUARD.COM31_Fri, 20 Nov 2009 09:49:25 -0500805_UTF-8 Programming interface information This information is intended to help you to plan for and administer DB2 for z/OS. This information also documents General-use Programming Interface and Associated Guidance Information and Product-sensitive Programming Interface and Associated Guidance Information provided by DB2 for z/OS. General-use Programming Interfaces allow the customer to write programs that obtain the services of DB2 Version 9.1 for z/OS. General-use Programming Interface and Associated Guidance Information is identified where it occurs by the following markings: General-use Programming Interface and Associated Guidance Information… Product-sensitive Programming Interfaces allow the customer installation to perform tasks such as diagnosing, modifying, monitoring, repairing, [...] 23386 22 36_Re: GUPI ? -- almost Friday question12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Fri, 20 Nov 2009 15:37:24 +0000655_utf-8 GUPI? Almost Friday?? TFI Friday. Poets Day. I'm off.

_____________________________________________________________________

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

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

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L 23409 104 36_Re: GUPI ? -- almost Friday question16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG31_Fri, 20 Nov 2009 13:48:36 -0500 23514 47 114_Reserve Your Free Half-Day DB2 Workshop in December with Sheryl Larsen Through the Los Angeles Area DB2 User Group13_Mark DePledge20_mark.depledge@CA.COM31_Fri, 20 Nov 2009 21:51:39 +0000596_utf-8 The Los Angeles Area DB2 Users Group (LAADB2UG) is holding a free, half-day DB2 workshop with Sheryl Larsen! The workshop will take place at the LAADB2UG quarterly meeting on Thursday, December 3, 2009 in Costa Mesa. A repeat workshop will take place on Friday, December 4, 2009 near LAX.

The workshop will include 90-minute session starting at 10:00 a.m. The topic will be “DB2 9 & X for z/0S SQL Update”. The afternoon will also consist of a 90-minute workshop starting at 12:30 p.m. The cross platform topic will be “How and When to Change the DB2 Optimizer's Mind”. [...] 23562 516 36_Re: GUPI ? -- almost Friday question12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 21 Nov 2009 11:47:29 +0200615_utf-8 Hi,





That's Lawyer's English J





Isaac Yassin

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Labby - AES Sent: Friday, November 20, 2009 8:49 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] GUPI ? -- almost Friday question







Wow - is there associated guidance those who speak English as their only language?









From:

"Bill Shipley"



To:

DB2-L@WWW.IDUGDB2-L.ORG



Date:

11/20/2009 10:51 AM [...] 24079 38 6_[test]14_Peter Backlund21_BacklundDB2@TELIA.COM31_Sat, 21 Nov 2009 11:15:09 +0100 24118 61 10_Re: [test]14_Peter Backlund21_BacklundDB2@TELIA.COM31_Sat, 21 Nov 2009 11:17:23 +0100 24180 148 10_Re: [test]14_Peter Backlund21_backlunddb2@GMAIL.COM31_Sat, 21 Nov 2009 11:40:07 +0100700_ISO-8859-1 more testing

On Sat, Nov 21, 2009 at 11:15 AM, Peter Backlund wrote:

> Just to see if I get this message > > -- > > +-------------------------------+---------------------------------+ > | Peter G Backlund | Telephone: 08 - 38 69 08 | > | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | > | Rasundavagen 94 | Country Code (Sweden): 46 | > | S-169 57 SOLNA | Skype: BacklundDB2 | > | Sweden | E-mail: BacklundDB2@telia.com | > +-------------------------------+---------------------------------+ > | IBM Database GOLD Consultant | > +-------------------------------+---------------------------------+ > | Working with DB2 since 1981 ... and [...] 24329 106 25_Listserv message slowdown10_Mark Labby21_mlabby.idug@GMAIL.COM31_Sat, 21 Nov 2009 12:41:50 -0500440_ISO-8859-1 There was a slowdown in the number of messages and the list administrators had some people reporting that they were not getting any messages at all in the past week or two. Upon some investigation, the mail server that DB2-L uses was found listed on a blocking site. Some people were not affected at all it is totally up to your individual ISP how they filter mail and if they happened to subscribe to that blocking list. [...]