1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2009, week 5 2 26 25_Re: Database Cleanup List25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM31_Wed, 29 Jul 2009 00:31:54 +0000644_utf-8 Thanks everyone for the great information and detail on this subject. Bottom up sounds like the way to go.

If I delete the underlying VSAM datasets before dropping the objects, won't DB2 complain when the objects are dropped?

_____________________________________________________________________

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

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "This was definitely the best IDUG conference I have ever attended." _____________________________________________________________________ 29 70 58_AUTO: Arun Rao is out of the office (returning 07/29/2009)8_Arun Rao16_arunr@US.IBM.COM31_Tue, 28 Jul 2009 21:51:34 -0600332_US-ASCII



I am out of the office until 07/29/2009.

I will be out of the office on Tue 07/28/2009. Please contact my manager for any issues that can't wait.



Note: This is an automated response to your message "DB2-L Digest - 28 Jul 2009 to 29 Jul 2009 (#2009-220)" sent on 7/28/09 19:00:00. [...] 100 172 28_Re: db2 v9 redbooks and DPSI8_duam lee20_duam_lee@HOTMAIL.COM31_Wed, 29 Jul 2009 04:22:37 +0000382_ks_c_5601-1987

Thank you Robert,





I went through this, understood though the limitation is enahanced from V8 that the all partitions are not probed. But only a limited parts are probed.

In v8 earlier the local predicate was needed from partitioning key and now in V9 the manual says the limited probing due to limited page-range screening. [...] 273 137 21_Re: Code page dilemma11_Vasko Tomas23_tomas.vasko@GENERALI.AT31_Wed, 29 Jul 2009 08:16:09 +00001084_utf-8 Hi,

here the full report Regards Tomas



ISF031I CONSOLE $$IUE4P ACTIVATED -D UNI,ALL CUN3000I 09.57.55 UNI DISPLAY 708 ENVIRONMENT: CREATED 07/15/2009 AT 21.12.21 MODIFIED 07/16/2009 AT 09.49.35 IMAGE CREATED --/--/---- AT --.--.-- SERVICE: CHARACTER CASE NORMALIZATION COLLATION STRINGPREP BIDI STORAGE: ACTIVE 287 PAGES FIXED 0 PAGES LIMIT 500 PAGES CASECONV: NORMAL NORMALIZE: DISABLED NORM VER: NONE COLLATE: DISABLED COLL RULES: NONE STRPROFILES: NONE CONVERSION: 00273-01208-R 00850-01200(13488)-R 00437-01208-R 01200(13488)-00037-E 01200(13488)-00273-E 01200(13488)-00367-E 01047-01200(13488)-R 01200(13488)-00500-E 01200(13488)-00819-E 01200(13488)-00850-E 01208-00037-E 01200(13488)-01047-E 00273-01200(13488)-R 01208-00273-E 01208-00367-ER 01200(13488)-01252-E 01208-00437-E 01208-00500-E 01208-00819-E 01208-00850-E 01208-01047-E 01208-01200-ER 01208-01252-E 00037-00367-E 00037-00500-E 00037-00500-R 00037-00850-E 00037-00850-R 00037-01047-R 00037-01208-R 01252-01208-R 00367-00037-E 00500-00037-E 00500-00037-R 00367-00273-E 00367-00500-E [...] 411 196 42_Antwort: Re: [DB2-L] Database Cleanup List11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 29 Jul 2009 16:38:43 +0200464_ISO-8859-1 no. That's one of the joys of DB2 it just assumes "Oh its gone already - okey dokey - next dataset please"





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 [...] 608 108 46_Re: Antwort: Re: [DB2-L] Database Cleanup List9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 29 Jul 2009 10:00:47 -0500326_iso-8859-1 Depends on how long you have worked with DB2. once upon a time, DB2 would trip over with C90101 or something if the dataset was missing. But it has worked this way for at least 10 years and probably a lot more. I think it was the first release to support DEFER on create index but that is fairly ancient also. [...] 717 67 43_[JOB] DBA opportunity in Northwest Arkansas10_Fred Edgar18_fredgarx@YAHOO.COM31_Wed, 29 Jul 2009 08:46:26 -0700534_us-ascii We're looking to back fill a full time DBA position. Requires good DBA skills and knowledge in at least one of these:

DB2 z/OS DB2 LUW Oracle SQL Server

You'd be part of a team sharing responsibility for providing

- Backup and recovery - Administration (DDL and such) - Database and SQL tuning - Oncall support (about 1 week out of 6) - General application support - Installation and maintenance of some DB and 3rd-party software - Whatever else someone needs us to do. We do what needs to be done. [...] 785 43 16_Multi-row insert16_Richard Sandford24_rsandford@HEALTHPLAN.COM31_Wed, 29 Jul 2009 16:05:48 +0000428_UTF-8 We have a developer that is trying to do the following kind of INSERT from a web application and is getting an error: INSERT INTO DB2TEST.CASE_TABLE (N_NUM,QUOTE_ID, EFF_DATE) VALUES ('SAAQ', 563, '2009-08-01'), ('SSAW', 564, '2009-08-01')

The error he is getting is: ERROR - 37000 [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]ILLEGAL SYMBOL ,; VALID SYMBOLS ARE . [...] 829 72 20_Re: Multi-row insert11_Mike Turner19_mike.turner@GMX.COM31_Wed, 29 Jul 2009 17:26:13 +0100587_utf-8 Hi Richard

Unfortunately that syntax (array of literals) is not supported by DB2 for z/OS.

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: "Richard Sandford" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, July 29, 2009 5:05 PM Subject: [DB2-L] Multi-row insert [...] 902 447 24_Re: CA DB2 Utilities r1217_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 29 Jul 2009 22:01:57 +0100343_us-ascii Mark,





I understand that you have already been contacted offline about this - in any case we expect R12 to be GA within the second half of 2009 - our existing customers can logon to support.ca.com and look for the complete roadmap for our DB2 Tools and Utilities there J





Thanks, HTH. [...] 1350 25 16_UDB apply Fixpak3_MSH16_mvsmsh@YAHOO.COM31_Wed, 29 Jul 2009 15:19:37 -0700600_utf-8 Are there anyway to apply one apar instead of whole Fixpa Hi listers , Are there anyway to apply one apar instead of whole Fixpak to UDB in AIX ,like SMP apply PTF xxx ? Regards UDB Newbie

_____________________________________________________________________

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

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "This was definitely the best IDUG conference I have ever attended." _____________________________________________________________________ 1376 82 20_Re: UDB apply Fixpak12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 29 Jul 2009 19:33:19 -0400830_UTF-8 In a nutshell, no. IBM sometime builds special builds or fixes. Hth pg





Phil Gunning IBM Information Champion www.gunningts.com Twitter:@db2luw See us at IDUG Europe, 5-9 Oct, 2009 Rome, Italy Database Industry Analyst DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support http://it.toolbox.com/blogs/db2-for-luw DB2 9.7 Early Access Program -- http://www-01.ibm.com/software/data/db2/technology-sandbox/ Direct +1.610.451.5801 Toll Free 888.241.1070 pgunning@gunningts.com IBM Business Partner IBM Authorized Reseller This message (including attachments, header and footer details) contains confidential information intended for a specific individual or individuals and purpose(s), and is protected by law. If you are not the intended recipient, you should delete this message and [...] 1459 377 63_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Database Cleanup List11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 30 Jul 2009 07:27:22 +0200315_ISO-8859-1 then it must have been 1.1 or 1.2 - I am old enough to have worked from 1.3 up.....Mind you I cannot remember if I idcam deleted datasets in those days as we used punch cards to store the data and we just burnt the cards to delete the data for ever...of course DB2 was a bit slower reading cards... [...] 1837 278 25_Re: Database Cleanup List12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 30 Jul 2009 10:06:29 +0300703_UTF-8 Hi,

Last time I've checked DB2 did not clear the syslgrnx (but did the syscopy).

I may need to check again J





Isaac Yassin

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of jtonchick@AOL.COM Sent: Wednesday, July 29, 2009 1:24 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Database Cleanup List





Max, running MODIFY prior to the DROP is not necessary. The DROP also removes entries in both SYSCOPY & SYSLGRNX. But in following the intent of my original reply, reducing locking overhead, running the MODIFY before the DROP will remove the need to perform the SYSCOPY/SYSLGRNX clean up and thus [...] 2116 46 55_AW: [DB2-L] [JOB] DBA opportunity in Northwest Arkansas12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 30 Jul 2009 09:20:12 +0200286_us-ascii >>>> DBA's are not famous for winning Ms. or Mr. Congeniality awards, but we do have to be civil to other employees. <<<<<<

This is the sentence of the year - isn't it ?

SCNR ;-))

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r [...] 2163 105 35_Question about REORG with Rebalance15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL31_Thu, 30 Jul 2009 10:15:42 +0200532_us-ascii L.s.

We did a REORG with opton (REBALANCE) on a 50 partition (TCP) tablespace with one cl. index on the primary key. It is a "static" tablespace so there is "no add partition or rotate" done until now.

The rebalance was done on part (31:40). The utility ended normal and the data is available. The only strange thing is that the logical sequence in the values is disturbed for part 31:40 after the reorg. You can see that by the DDLGEN that is created after the REORG by the DB2-Admin tool. (See below.) [...] 2269 75 25_select rid(T1) from table13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Thu, 30 Jul 2009 11:11:31 +0200277_us-ascii I have a question about select a row based on the RID in DB2 z/os V9 nfm. I do not code much SQL, so the solution may be simple for others. I get a -171

The scenario: I have a little test table that I just have created an I can get the RID value like this [...] 2345 145 39_Re: Question about REORG with Rebalance11_Mike Turner19_mike.turner@GMX.COM31_Thu, 30 Jul 2009 10:19:35 +0100492_iso-8859-1 Hi Harry

If you mean that the clustering sequence within the partitions is disturbed by the REORG, then this is a known issue. When the clustering sequence is different from the partitioning sequence, then REORG should be run twice, once with REBALANCE to move rows into their new partitions, and once without to enforce the clustering. The first REORG will place the tablespace in Advisory Reorg Pending (AREO*) status as a warning that a second REORG is recommended. [...] 2491 159 54_Betr.: Re: [DB2-L] Question about REORG with Rebalance15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL31_Thu, 30 Jul 2009 11:39:28 +0200378_utf-8



Hallo Mike,

The situation you describe with (Advisory Reorg Pending (AREO*) is not the problem. This extra reorg is already done after we got this status in the first place.



Our "problem" is that the limit key ( changed by DB2 Reorg with Reblance) is no longer in ascending sequence according to the DDLGEN after this DB2-reorg. [...] 2651 278 58_Re: Betr.: Re: [DB2-L] Question about REORG with Rebalance11_Mike Turner19_mike.turner@GMX.COM31_Thu, 30 Jul 2009 11:41:46 +0100581_UTF-8 Hi Harry

Ah, now I see. Parts 34 and 39 have limit keys out of sequence. I suppose this might just be a problem with the DDLGEN. I would check SYSTABLEPART LIMITKEY column for the Reorged partitions and also that PARTITION and LOGICAL_PART match for each partition (just to confirm that no Rotate was done). If SYSTABLEPART is wrong, then you might want to check APARs in this area. I have found a few related to Reorg Rebalance. Two especially seem like they might relate to your problem. APAR PK75705 (V8 PTF UK47947) refers to limitkey problems where there is [...] 2930 266 73_Betr.: Re: [DB2-L] Betr.: Re: [DB2-L] Question about REORG with Rebalance15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL31_Thu, 30 Jul 2009 13:49:27 +0200495_utf-8



Hallo Mike,



We did this query on the DB2 catalog. So this seems oké. We will look at the apars.







SELECT SUBSTR(TSNAME, 1, 8) AS TABSNAME, PARTITION, LOGICAL_PART, SUBSTR(LIMITKEY, 1, 15) AS LIMITKEYS FROM SYSIBM.SYSTABLEPART WHERE DBNAME = 'DSSDB01' AND TSNAME = 'DSSTSZW' AND PARTITION BETWEEN 29 AND 50

+-------------------------------------------------------+ | TABSNAME | PARTITION | LOGICAL_PART | LIMITKEYS | [...] 3197 87 49_SQL1326N The file or directory cannot be accessed25_SUBSCRIBE DB2-L Anonymous27_ubalabhaskar@REDIFFMAIL.COM31_Thu, 30 Jul 2009 11:46:51 +0000512_utf-8 I am using DB2 V9.1 on AIX

huxp0101:/db2home/pmsinst1# db2level DB21085I Instance "pmsinst1" uses "64" bits and DB2 code release "SQL09013" with level identifier "01040107". Informational tokens are "DB2 v9.1.0.3", "s070719", "U811590", and Fix Pack "3". Product is installed at "/opt/IBM/db2/V9.1".



Following the output from the .profile of 'usr1'

huxp0101:/home/usr1# more .profile PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.

export PATH [...] 3285 160 36_Can Db2 z/os access Sql Server 2005?13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Thu, 30 Jul 2009 08:02:07 -0400413_US-ASCII I don't even know if I can do this, but I was playing around with it anyway. I'm trying to get DB2 z/os v8 to talk to a sql server 2005 instance. I've added everything to the DB2 z/os communication databases (LOCATIONS, IPNAMES, USERNAMES) and bound the plan I'm using to include the new location. When I do a simple batch select from DB2/zos to sql server, the error I get reported back to db2 is: [...] 3446 266 40_Re: Can Db2 z/os access Sql Server 2005?20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 30 Jul 2009 15:14:12 +0300767_us-ascii Hi

You need IBM Federation Server which was Data Joiner or similar product.



Please check the link below

http://www-01.ibm.com/software/data/infosphere/federation-server/

Regards, Cuneyt



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hodgin, Scott Sent: Thursday, July 30, 2009 3:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Can Db2 z/os access Sql Server 2005?





I don't even know if I can do this, but I was playing around with it anyway. I'm trying to get DB2 z/os v8 to talk to a sql server 2005 instance. I've added everything to the DB2 z/os communication databases (LOCATIONS, IPNAMES, USERNAMES) and bound the [...] 3713 32 16_Package Contoken4_Anil21_alisha_kale@YAHOO.COM31_Thu, 30 Jul 2009 14:36:47 +0000339_utf-8 Hi All,

Env: DB2 Version 9 for z/OS

Is there a REXX/CLIST/JCL that can use a LOADLIB and extract all load module names with their CONTOKEN ? Alternatively, you could suggest a way to compare and list all Packages having a matching CONTOKEN load module. The ultimate goal is to free up package versions not in use. [...] 3746 33 16_Package Versions4_Anil21_alisha_kale@YAHOO.COM31_Thu, 30 Jul 2009 14:37:32 +0000410_utf-8 Hi All,

This question is in the context of Db2 for z/OS version 9 where you have enabled Package Versioning. Over a period of time, you end up with either many redundant packages or many obsolete packages. So, What kind of procedures do you follow to maintain the different versions of PACKAGES ? How do you clean up the obsolete packages or packages that have no corresponding load module ? [...] 3780 37 28_AW: [DB2-L] Package Versions12_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM31_Thu, 30 Jul 2009 17:06:17 +0200330_utf-8 Hi dear Anil ! We use a SW-product called 'Bind Manager' ... with a lot of DB2-feature's (e.g.: avoid-not-needed-bind's etc.) ... one of those feature's is a pkg-cleanup-process of "old package's". PS: We got it long time ago from an ISV (HLS: ghodge@hlstechnologies.com) ... now IBM is the SW-maintainer. Kinds/Othmar [...] 3818 75 20_Re: Package Contoken12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 30 Jul 2009 10:11:02 -0500603_utf-8 Anil:

There are several problems in identifying the connect token from a load library and matching it to a DB2 Subsystem. DB2 is designed to be called from a program, but does not call the program directly. This makes locating the connect difficult as each programming language encodes the connect token differently. To do this process correctly, you have to provide all of the possible load modules and scan each for all of the possible hits. The scan has to be conscious of how each language, which is identified in the package information in the catalog, encodes the connect token. [...] 3894 46 20_Re: Package Versions13_Palko, George16_gpalko@OPERS.ORG31_Thu, 30 Jul 2009 16:24:50 +0100440_utf-8 Hi,

IBM just released a new RedBook SG24-7688- DB2 9 for z/OS: Packages Revisited. This book may help. It help us set up a process to purge the OSC extended tables.

Regards, George





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil Sent: Thursday, July 30, 2009 10:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Package Versions [...] 3941 45 18_SYSPACKAGES Sizing4_Anil21_alisha_kale@YAHOO.COM31_Thu, 30 Jul 2009 15:25:42 +0000575_utf-8 Hi All !

Is there any documentation on sizing considerations for the catalog table - SYSPACKAGE when using packages with the version option, there is always the potential to have too many redundant/obsolete packages. Your DBA instincts would tell you that we need to define how to manage the versions and when do you delete old versions. But what if the decision to implement package versioning was before your time. What do you do to fix after you are in the driver's seat ? As a band-aid solution, I want to ask our systems DBA to allocate more space for [...] 3987 29 20_Re: Package Contoken4_Anil21_alisha_kale@YAHOO.COM31_Thu, 30 Jul 2009 15:34:44 +0000430_utf-8 Thank you Gerald for responding to my question.

I don't recall seeing this feature in any of IBM's DB2 Tools. Clearly, I am missing something. Would you please elaborate more on which IBM Tool allows it ? I am curious to see what it is.

Thanks

Anil

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 4017 75 20_Re: Package Contoken12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 30 Jul 2009 10:53:17 -0500583_utf-8 Anil:

The programs are BNDA036 and BNDA046. 36 is for V8 and above, while 46 is for V7 and below. The issue is the Unicode catalog.

They are both in the Bind Manager Tool. The most common problem we encounter is attempting to run everything in one pass. This can take hours and usually upsets the performance people. If you target the packages by collection ID the program usually works fine. Once the obsolete packages are removed, the program can be run as part of weekend maintenance. Our worst case was one place with over 400,000 packages and less than [...] 4093 75 22_Re: SYSPACKAGES Sizing9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 30 Jul 2009 11:00:59 -0500555_us-ascii Not much, because the problem always shows up in the directory table SPT01. one row in SYSPACKAGE is equal to many rows in SPT01 with a total space used larger than the PKSIZE value in SYSPACKAGE. The PKSIZE is the size of the package in EDM pool. The problem got much worse with V8 because as part of making the package 64 bit enabled, and the new lengths for names, package size increased dramatically. V9 added the capability of keeping 3 copies of each package in SPT01 with plan stability so now you have 3 times the problem. There are [...] 4169 31 25_Re: Database Cleanup List13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Thu, 30 Jul 2009 16:31:42 +0000448_utf-8 Nope... the DROP TABLESPACE will succeed if the VSAM cluster has been deleted.....

give it a try ....create a single tablespace in a test enviornemt.....locate the DSN of the VSAM cluster for the tablespace.....delete it and the issue DROP TABLESPACE...

Regards

Jack

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...] 4201 96 29_Re: select rid(T1) from table14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 30 Jul 2009 12:40:54 -0400577_iso-8859-1 Hi Hanne, Would you try putting the qualified table name in the ( ) please: RID(TESTGEN.T0333) ... your own table needed no qualification. Hope this helps. Larry Kintisch, Pres ABLE Information Services www.DBIndexdesign.com

At 05:11 AM 7/30/2009, you wrote: >I have a question about select a row based on >the RID in DB2 z/os V9 nfm. I do not code much >SQL, so the solution may be simple for others. >I get a -171 > >The scenario: >I have a little test table that I just have >created an I can get the RID value like this > >SELECT FELT1,FELT2,RID(T1) [...] 4298 20 7_goodbye17_Harrison,Thomas A15_tah4@UAKRON.EDU31_Thu, 30 Jul 2009 13:39:26 -0400521_us-ascii Review db2-l

_____________________________________________________________________

* 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!" _____________________________________________________________________ 4319 68 32_DBA looking for next opportunity0_22_sally.mir@WACHOVIA.COM31_Thu, 30 Jul 2009 17:28:22 -0400351_US-ASCII

As a result of merger activity, I have been 'displaced' from my DBA position. If anyone has or knows of any opportunities, I would appreciate being contacted off-list.

A few things about me: 1) I can tell a package from a plan. 2) I actually was 'Miss Congeniality' once. 3) My favorite DB2 manual is the Diagnosis Guide. [...] 4388 79 22_Re: SYSPACKAGES Sizing11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Fri, 31 Jul 2009 06:59:23 +0000373_utf-8 Hi Anil,

what's the size of your SPT01 TS ? Aren't you more worried about its size ?

Besides on DASDs, larger SYSPKAGE would also take more space in the bufferpool ( BP0) so you may want to check its hit ratio... I wold also try to clean up garbage and adopt the package management procedure because sooner of later you'll have to deal with it ... [...] 4468 95 29_DB2 z/OS Universal Tablespace12_Marc vd Valk34_mw.van.der.valk@BELASTINGDIENST.NL31_Fri, 31 Jul 2009 10:59:31 +0200347_utf-8



Hello list,

in the DB2 version 9.1 for z/OS SQL Reference manual at the CREATE TABLESPACE statement, it is stated that you should not use LOCKSIZE TABLE on a Universal Tablespace, but no reason is given.

Does anyone know why?

And what if I execute a LOCK TABLE on a table in a Universal Tablespace? [...] 4564 125 33_Re: DB2 z/OS Universal Tablespace13_Mertens, Bart18_bart.mertens@CZ.NL31_Fri, 31 Jul 2009 11:51:08 +0200646_utf-8 Marc,

From the Administration guide: "Another clause of the CREATE TABLESPACE statement is LOCKSIZE TABLE. This clause is valid only for tables that are in segmented table spaces. DB2, therefore, can acquire locks that lock a single table, rather than the entire table space."

From the Application Programming & SQL Guide (Chapter The LOCK TABLE statement) "You can use LOCK TABLE on any table, including auxiliary tables of LOB and XML table spaces. For more information about locking auxiliary tables, see“The LOCK TABLE statement for LOBs” on page 487 and “The LOCK TABLE statement for XML data” on page 491." [...] 4690 117 27_R: [DB2-L] Package Versions15_Moschelli Mauro34_mauro.moschelli@INTESASANPAOLO.COM31_Fri, 31 Jul 2009 11:59:52 +0200403_iso-8859-1 If you want to keep only the last package (the more recently compiled) you can look on the SYSPACKAGE table WHERE PCTIMESTAMP <> ( SELECT MAX(Q.PCTIMESTAMP) FROM SYSIBM.SYSPACKAGE Q WHERE Q.LOCATION=A.LOCATION AND Q.COLLID=A.COLLID AND Q.NAME = A.NAME )

HTH

Mauro Moschelli Intesa Sanpaolo Group Services

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS [...] 4808 166 33_Re: DB2 z/OS Universal Tablespace11_Mike Turner19_mike.turner@GMX.COM31_Fri, 31 Jul 2009 11:38:22 +0100717_utf-8 Hi Marc

A UTS can hold a maximum of one table. LOCKSIZE TABLE is only of use for tablespaces containing more than one table.

The LOCK TABLE statement on a table in a UTS will take a tablespace-level lock, just like for a simple or segmented tablespace.

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: "Marc vd Valk" Newsgroups: bit.listserv.db2-l To: Sent: Friday, July 31, 2009 [...] 4975 200 33_Re: DB2 z/OS Universal Tablespace11_Mike Turner19_mike.turner@GMX.COM31_Fri, 31 Jul 2009 12:50:56 +0100722_utf-8 Correction.

> The LOCK TABLE statement on a table in a UTS will take a tablespace-level > lock, just like for a simple or segmented tablespace.<

That should read 'just like for a simple or partitioned tablespace'.

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: "Mike Turner" Newsgroups: bit.listserv.db2-l To: Sent: Friday, July 31, 2009 11:38 AM Subject: Re: [DB2-L] DB2 z/OS Universal Tablespace [...] 5176 46 11_Silly doubt14_John Mitchelle29_john.mitchelle@GOOGLEMAIL.COM31_Fri, 31 Jul 2009 12:51:54 +0100644_ISO-8859-1 "if data in database is huge then more CPU is required when users perform standard database activities such as updating data"

I came across this in an article related to database archiving. Can anyone explain me in detail ?

_____________________________________________________________________

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

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldnt fault anything. Very impressed." _____________________________________________________________________ 5223 192 28_Antwort: [DB2-L] Silly doubt11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 31 Jul 2009 14:08:26 +0200320_ISO-8859-1 simply put

Insert into empty DB - fast and easy Insert into huge DB - DB2 must "find" a slot - could be long and hard! Update and Delete are similar

TS scans take much more CPU and IO if the TS is big IX scans take much more CPU and IO if the index is big (Think also index splits etc etc) [...] 5416 72 23_CA-Detector help needed11_Sameer Rana21_sameerdrana@GMAIL.COM31_Fri, 31 Jul 2009 14:46:33 +0000647_utf-8 Folks ,

I have been working for quite sometime but am at loggerheads so thought someone who has worked on CA/Detector may ready with the solution!

Basically I have set up the Collection Profile with appropriate Application resource group filter at Authid and Correlation-id level. I have used following Collection Profile options : SQL collection options: Standard Activity ==> Y (Y/N) Dynamic SQL Stats ==> Y (Y/N) (Requires profile) View By Keys ==> N (Y/N) (Requires profile) Dynamic Exceptions ==> Y (Y/N) (Requires profile) Static Exceptions ==> N (Y/N) Exception cache size ==> 1000 SQL Errors ==> Y (Y/N) (Requires [...] 5489 72 27_Re: CA-Detector help needed16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Fri, 31 Jul 2009 11:07:19 -0400522_UTF-8 Hello Sameer - first of all, why don't you call Support :-) They will be more than willing to help you set up the correct parameters.

If you want to collect each and every transaction (without aggregating the dynamic stats), you should use VIEW-BY-KEYS. This consumes less overhead compared to DYNAMIC EXCEPTIONS. If you just want to see statistics for every DISTINCT Dynamic Statement and how many times it's been executed along with the stats - you should turn off EXCEPTIONS and do DYNAMIC SQL STATS. [...] 5562 56 36_Re: DBA looking for next opportunity14_Larry Kintisch17_LKint@VERIZON.NET31_Fri, 31 Jul 2009 11:10:51 -0400579_us-ascii Good luck Sally! Tell us where you are located and where you'd be willing to go to. Larry Kintisch

At 05:28 PM 7/30/2009, you wrote:

>As a result of merger activity, I have been 'displaced' from my DBA >position. If anyone has or knows of any opportunities, I would >appreciate being contacted off-list. > >A few things about me: >1) I can tell a package from a plan. >2) I actually was 'Miss Congeniality' once. >3) My favorite DB2 manual is the Diagnosis Guide. > >For more, request my resume. :) > >Thanks, > >Sally Mir >mirsally@yahoo.com >[] > > [...] 5619 20 54_Re: Duplicates in Unique index: v8 (NFM);z/os 1.7; MVS12_Mary Parsons27_dmsdb2_worthing@YAHOO.CO.UK31_Fri, 31 Jul 2009 15:14:34 +0000435_utf-8 Thanks, James. Great idea.

_____________________________________________________________________

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

_____________________________________________________________________

IDUG Europe Attendee Testimonial- "Conference was superb! Couldnt fault anything. Very impressed." _____________________________________________________________________ 5640 34 27_Re: CA-Detector help needed11_Sameer Rana21_sameerdrana@GMAIL.COM31_Fri, 31 Jul 2009 15:47:52 +0000548_utf-8 Thanks for your response Steen ,

My question was more pertaining to the activity that is being collected. I have coded the application resource group to filter at a particular AUTHID and CORRELATION id that I am interested in ( mind you all sql is JAVA based Dynamic SQL ). However my main problem is when I start the trace I see all application activity from every plan being captured. Why is it not collecting only for the given CORRELATION id? Is this the way the product works anyways? Can it not filter activity it collects? [...] 5675 229 71_[AD] DB2 Connect Tuning Class - Last Minute Special Offer - O's Tickets12_Kimberly May28_kim.may@THEFILLMOREGROUP.COM31_Fri, 31 Jul 2009 13:34:10 -0400572_us-ascii This class IS going to run - for the first time in over two years.





As a special offer to anyone that may be trying to decide whether or not to join us, in addition to the summer seafood dinner that we will be treating students to (crabs, Chincoteague oysters, sweet corn, yum), I have a pair of tickets (great seats - 12 rows behind the third base dugout) to see the Orioles play the Oakland A's in beautiful Camden Yards on Monday night, following the first day of class. If you are interested, please contact me off-list. First come [...] 5905 414 34_DB2 z/OS - V8 - Weird bind problem13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US31_Fri, 31 Jul 2009 10:36:33 -0700703_us-ascii Hello listers,

Has anyone ever seen this? We were binding a package in our production system and got the following:

READY

DSN SYSTEM(DSNA)

DSN

BIND PACKAGE(MVD_PROD) OWNER(MPROD1) MEMBER(KCM0AO3) VALIDATE(BIND) DEGREE(1) CURRENTDATA(NO) KEEPDYNAMIC(YES) DYNAMICRULES(BIND) ISOLATION(CS) RELEASE(COMMIT) EXPLAIN(YES) ACTION(REPLACE) DSNT500I @ DSNXOD2 RESOURCE UNAVAILABLE

REASON 00D70014

TYPE 00000220

NAME DB2A.DSNDBC.MUTIL1D.SPAUL5.I0001.A001

DSNT500I @ DSNXOD2 RESOURCE UNAVAILABLE

REASON 00D70014

TYPE 00000220

NAME DB2A.DSNDBC.MUTIL1D.SPAUL5.I0001.A001

DSNT500I @ DSNXOD2 RESOURCE UNAVAILABLE [...] 6320 180 36_Re: DBA looking for next opportunity0_22_sally.mir@WACHOVIA.COM31_Fri, 31 Jul 2009 13:39:20 -04006_UTF-8 6501 135 38_Re: DB2 z/OS - V8 - Weird bind problem9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 31 Jul 2009 13:09:44 -0500343_us-ascii The only way I can combine a BIND package and a customer table would be some kind of trigger on SYSPACKAGE or SYSPACKSTMT. Maybe a trigger on your PLAN_TABLE or DSN_STATEMNT_TABLE since I see the EXPLAIN(YES)? That would be a little easier to believe. It has to be generating an INSERT of some kind because of the space failure. [...] 6637 186 38_Re: DB2 z/OS - V8 - Weird bind problem10_Fred Edgar18_fredgarx@YAHOO.COM31_Fri, 31 Jul 2009 11:32:16 -0700607_utf-8 My first thought, Is there a MPROD1.PLAN_TABLE in tablespace MUTIL1D.SPAUL5 ?

Fred

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] > On Behalf > Of BOND Victor A > Sent: Friday, July 31, 2009 12:37 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] DB2 z/OS - V8 - Weird bind problem > > Hello listers, > > Has anyone ever seen this? > We were binding a package in our production system and got > the following: > > READY > > DSN SYSTEM(DSNA) > > DSN > > BIND PACKAGE(MVD_PROD) > OWNER(MPROD1) MEMBER(KCM0AO3) > VALIDATE(BIND) [...] 6824 128 36_Re: DBA looking for next opportunity14_Larry Kintisch17_LKint@VERIZON.NET31_Fri, 31 Jul 2009 14:33:19 -0400308_iso-8859-1 Sally, I don't know if these firms are hiring but here are two contacts from your general area. You may want to go through any regional idug group as well ...

Bob Paynter [DB2 systems prog] at BB&T bank in the Raleigh area bpaynter@bbandt.com They are migrating many VSAM apps to DB2. [...] 6953 50 59_Re: AW: [DB2-L] [JOB] DBA opportunity in Northwest Arkansas17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Fri, 31 Jul 2009 19:33:51 +0100409_utf-8 Well DBAs are normally better with people than us sysprogs.

We are the ones with a fine collection of vintage oak sharpened baseball bats, to use on those nasty application developers AND occasionally on DBAs ;-)







Aurora Emanuela Dell'Anno CA Sr Engineering Services Architect Tel: +44 (0)1753 577 733 Mobile: +44 (0)7768 235 339 Aurora.Dellanno@ca.com [...] 7004 656 36_Re: DBA looking for next opportunity14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 31 Jul 2009 14:27:53 -0400677_us-ascii Sally, there's a job for a senior DB2 database administrator at SECU in Raleigh. That's about it for North Carolina on Dice at the moment, though. (I have a longer commute than that but I get to ride a bus.)

http://seeker.dice.com/jobsearch/servlet/JobSearch?op=302&dockey=xml/2/e/2ef00d069bfffa6ee093786da743f2cb@endecaindex&source=19&FREE_TEXT=db2+database+administrator&rating=99

--Phil

________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of sally.mir@WACHOVIA.COM Sent: Friday, July 31, 2009 1:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DBA looking for next opportunity [...] 7661 121 59_Re: AW: [DB2-L] [JOB] DBA opportunity in Northwest Arkansas14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 31 Jul 2009 14:41:18 -0400483_utf-8 Aurora,

I believe the technical term for a baseball bat with points on it is "mace." Spelled like the citizen-use tear gas.

--Phil, who _isn't_ an SCAphile but who played D&D when young.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dell'Anno, Aurora Sent: Friday, July 31, 2009 2:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: [DB2-L] [JOB] DBA opportunity in Northwest Arkansas [...] 7783 215 38_Re: DB2 z/OS - V8 - Weird bind problem13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US31_Fri, 31 Jul 2009 11:45:13 -0700467_iso-8859-1 Ok, you guys hit it right on the head. Someone used VISUAL EXPLAIN to create all the DSN% explain tables and they created them in the SPAUL5 tablespace. I just feel dumb now, not crazy. Thanks everyone, Vic

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Fred Edgar Sent: Friday, July 31, 2009 11:32 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS - V8 - Weird bind problem [...] 7999 139 38_Re: DB2 z/OS - V8 - Weird bind problem14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 31 Jul 2009 15:32:19 -0400512_utf-8 Ouch! That wouldn't have occurred to me.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of BOND Victor A Sent: Friday, July 31, 2009 2:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS - V8 - Weird bind problem

Ok, you guys hit it right on the head. Someone used VISUAL EXPLAIN to create all the DSN% explain tables and they created them in the SPAUL5 tablespace. I just feel dumb now, not crazy. Thanks everyone, Vic [...]