1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2009, week 3
2 153 52_max RBA log messages - have u been there, done that?13_Case, Missy J24_Missy.Case@FIRSTDATA.COM31_Mon, 17 Aug 2009 10:53:36 -0500323_us-ascii All,
We're getting ready to pop and drop a DB2 member into a new RBA phase, we only have a couple weeks until we run out of RBA's for it. It's currently non-data sharing, but we've recently migrated it to a sysplex where we are planning on defining it to data sharing to help us get around this issue. [...]
156 333 43_Re: Performance issue with a delete process10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 17 Aug 2009 05:13:34 +0000584_utf-8 Thanks, Terry.
I didn't realise the impact of the pseudo-deleted entries. I know they occupy space, but I didn't really think they were accessed as part of the query. But on thinking about it, I can understand some level of impact of trawling thru the various pseudo-deleted entries. But I still didn't expect this level of a decrease in performance. The first few deletes (of 100 rows each) went thru quickly. The next one just hung...it didn't gradually degrade. This could be dependent on the way the data is stored in the table and indexes, I guess. I will have [...]
490 349 43_Re: Performance issue with a delete process10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 17 Aug 2009 05:21:13 +0000542_utf-8 Thanks, Terry.
I didn't realise the impact of the psuedo-deleted entries. I knew they occupy storage, but I didn't think they would actually be accessed to retrieve the next value. On thinking about it, I can understand why it would need to trawl thru heaps of deleted rows to get to the next valid value, but I didn't really expect them to impact the performance to this extent. The first few deletes go thru quickly (say 7 of them, 100 rows each), then the next one just hangs. I was expecting a more gradual degradation. [...]
840 33 23_DB2 LUW backups and TSM12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Mon, 17 Aug 2009 14:21:40 +0000416_utf-8 We are familiar with DB2 UDB Z/OS but are being thrust into the LUW world. Trying to get a handle on the backup mechanisms and processes used. We have TSM installed and wondering what is typical as to the setup and config. Are archives sent to disk then copied to TSM or sent to TSM directly? Are backups sent directly to TSM? Where is the best place to find up to date info on using TSM for DB2 backups? [...]
874 338 43_Re: Performance issue with a delete process10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 18 Aug 2009 00:42:16 +0000358_utf-8 Thanks, Terry.
I didn't realise the impact of the psuedo-deleted entries. I knew they occupied storage, but I didn't think they would actually be accessed to retrieve the next value (is it because it is only at the leafpage level that the row is marked as deleted, and hence has to go to each index key to actually see if it is deleted ?). [...]
1213 287 43_Re: Performance issue with a delete process10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 17 Aug 2009 05:38:53 +0000547_utf-8 Thanks, Larry. I am not sure why the mail bounced back. I will check again.
The cluster ratios on the delete key index are approximately 50% (for all the tables).
Average row lengths are about 800, 1300, 500. Sizes are about 17 million, 500000 and 500000
All 3 indexes use the same BP (but the deletes for the 3 tables do not happen concurrently - they run one after the other, so no contention of BP). BP size is about 3000 4K pages. (It is not big, but, we are just starting off and so I wanted to leave changing [...]
1501 125 42_IDUG India Sept 24-26 - Spread the word...11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 17 Aug 2009 10:29:57 -0500546_iso-8859-1
IDUG India will take place September 24-26 2009 at the Chancery Pavilion.
Keynote by Sal Vella, sessions by top-notch speakers like Roger Miller and free Certification are just some of the highlights.
If you are based in India - please plan to attend (a justification kit is included).
If you are US-based but have offshoring operations in India, please help to spread the word. You know the quality education IDUG provides. Help us share this. [...]
1627 19 18_Test please ignore22_Leslie Pendlebury-Bowe21_db2dba@BTINTERNET.COM31_Mon, 17 Aug 2009 21:27:30 +0100404_us-ascii _____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________
1647 154 29_decrypt_char function problem12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM31_Mon, 17 Aug 2009 13:59:42 -0400607_US-ASCII Hi all,
we are getting a -171 from a bind package. the program it is trying to bind is cobol. The -171 is coming from one statement :
SELECT CONSOLIDATION_NUM , ROUTING_NUM , DECRYPT_CHAR(ACCOUNT_ID,'xxxxxxx') INTO :HV-AIX-PYA-CONSOLIDATION-NUM , :HV-AIX-PYA-ROUTING-NUM , :HV-AIX-PYA-ACCOUNT-ID FROM xxxx.xxxxxxxxxx
We are doing a connect to a udb datasource before we execute this statement. It used to compile and bind successfully before in db2 z/os V7. Now we are v8 nfm and the decrypt_char function is valid under v8 and we think bind package recognizes it as such [...]
1802 54 20_Test - Please ignore10_Mark Labby21_mlabby.idug@GMAIL.COM31_Mon, 17 Aug 2009 21:51:09 -0400543_US-ASCII Sorry, I know test messages are discouraged but the mail server for idug.org is being worked on and we need to see if it is working
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________
1858 58 22_Test 4 - Please ignore10_Mark Labby21_mlabby.idug@GMAIL.COM31_Mon, 17 Aug 2009 22:46:18 -0400559_US-ASCII Sorry, I know test messages are discouraged but the mail server for idug.org is being worked on and we need to see if it is working
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________
1917 60 22_Test 5 - Please ignore10_Mark Labby21_mlabby.idug@GMAIL.COM31_Mon, 17 Aug 2009 23:05:14 -0400591_US-ASCII Sorry, I know test messages are discouraged but the mail server for idug.org is being worked on and we need to see if it is working
Things now seem to be working :)
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG Europe Attendee Testimonial- "Conference was superb! Couldn’t fault anything. Very impressed." _____________________________________________________________________
1978 31 26_Re: Test 3 - Please ignore11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 18 Aug 2009 05:09:23 +0000349_- I'm seeing duplicates. ------Original Message------ From: Mark Labby Sender: DB2 Data Base Discussion List To: DB2 List ReplyTo: DB2 List Sent: Aug 17, 2009 21:59 Subject: [DB2-L] Test 3 - Please ignore
Sorry, I know test messages are discouraged but the mail server for idug.org is being worked on and we need to see if it is working [...]
2010 104 27_Re: DB2 LUW backups and TSM12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Tue, 18 Aug 2009 08:35:39 +0000704_utf-8 Interfacing DB2 LUW and TSM is quite simple, fast and reliable. Some details which are at least true for Unix environments:
- TSM client is required, minimum version depends on the DB2 version. The more recent, the better (as usual). - "dsm.sys" should contain a separate SErver definition for DB2 - Your "dsm.opt" file should point to the SErver defined in "dsm.sys" - The following environment variables must be set for DB2: DSMI_CONFIG=[your_dsm.opt_file] DSMI_LOG=[your_tsm_logfile_target] DSMI_DIR=[your_tsm_client_api_bin_path] - Instance restart is required after setting these variables. It's best to set these variables through "userprofile" (which is called by "db2profile"). [...]
2115 172 34_z/Journal (June - July 2009 issue)10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Tue, 18 Aug 2009 06:02:12 -0400474_iso-8859-1
Hi DB2 user,
Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com
Last update on Monday, August 17th 2009
Articles from June - July 2009 issue of z/Journal are available online:
- Database Reorganization Strategies for DB2 z/OS by Rick Weaver
- Approaches to DB2 Tuning: What's the Best Use of Your Time? by Lockwood Lyon [...]
2288 133 33_Re: decrypt_char function problem10_Dave Churn19_davechurn@GMAIL.COM31_Tue, 18 Aug 2009 09:20:30 -0500378_ISO-8859-1 Jeff,
What is the definition of the column on the table? What is the definition of the COBOL field?
The friendly manual says:
-171 THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT nn OF function-name IS INVALID
Explanation:
Either the data type, the length or the value of argument nn of scalar function function-name is incorrect. [...]
2422 124 56_Re: max RBA log messages - have u been there, done that?9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 18 Aug 2009 09:51:26 -0500333_us-ascii First answer - no, I have NOT had a subsystem wrap the logrba. We are a software company so we do lots of other weird things though. In a previous life, a friend was running a large performance test and trying to use DFDSS to backup and restore the base tables. He didn't know about REPAIR LEVELID so it always failed. [...]
2547 117 21_Please unsubscribe me19_Vanitha_Subramaniam30_Vanitha_Subramaniam@SATYAM.COM31_Tue, 18 Aug 2009 21:27:04 +0530673_us-ascii Dear all,
Please let me know how to unsubscribe mails .
Thanks, Vanitha ________________________________________ From: DB2 Data Base Discussion List [DB2-L@IDUGDB2-L.ORG] On Behalf Of Larry Kintisch [LKint@VERIZON.NET] Sent: Tuesday, August 11, 2009 11:13 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] SQL to remove dupes. [ WITH UR ]
Hi Bill,
When you have specified "small" locks [ ROW or PAGE ] and you are doing a "read" operation [SELECT or searching for rows to change] then ISOLATION LEVEL comes into effect. This is normally specified at the BIND PACKAGE process [and the ISOLATION LEVEL for a package can be seen in [...]
2665 358 43_Re: Performance issue with a delete process12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 18 Aug 2009 19:26:09 +0300422_utf-8 Hi,
And don't forget that those pseudo-deleted are cleaned later on (index page reorg) during your work, making it slower while it goes ...
Isaac Yassin -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Teldb2kals Sent: Tuesday, August 18, 2009 3:42 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Performance issue with a delete process [...]
3024 34 27_Re: DB2 LUW backups and TSM18_Peter J. Krawetzky21_krawetzkypj@AETNA.COM31_Tue, 18 Aug 2009 17:40:49 +0000358_utf-8 We send our backups and logs directly to TSM without going to disk. This reduces the amount of disk space required for a particular database.
There are several DB config parms which you must set if you want to send logs directly to tape when they are ready for archiving. The reference guides are pretty good in respect to setting this up. [...]
3059 25 56_Re: max RBA log messages - have u been there, done that?11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 18 Aug 2009 17:48:00 +0000297_- >I remember when I was in a meeting with IBM - long time ago and they we so proud of lobrba because it was going to be more durable than the TOD clock value that IMS used.
Actually, in a DB2PLEX, the TOD value is used for logs. LOGRBA only makes sense with a single instance of DB2. [...]
3085 44 55_tool to create "mapping" of XML to DB2 table/row/column12_Davis, Kriss17_kpdavis@ILSTU.EDU31_Tue, 18 Aug 2009 13:03:49 -0500276_us-ascii Is there a commercial product or a tool someone has created that will allow a user to "visually" map an example set of XML statements to a table and column in Z/os DB2? For eventual use by the DB2 loader process to parse and extract XML to DB2 table structures? [...]
3130 133 20_Re: DB2 intro course20_Bruzdzinski, Michael27_Michael.Bruzdzinski@SSA.GOV31_Tue, 18 Aug 2009 14:16:31 -0400539_us-ascii
Michael Bruzdzinski Social Security Administration DCS/OESAE/DDBS/DBSB 410-966-7185
"The last temptation is the greatest treason: To do the right deed for the wrong reason." * Thomas Becket in Murder in the Cathedral By In T.S Eliot
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Aurora Emanuela Dellanno Sent: Thursday, November 20, 2008 5:10 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 intro course [...]
3264 135 20_Re: DB2 intro course20_Bruzdzinski, Michael27_Michael.Bruzdzinski@SSA.GOV31_Tue, 18 Aug 2009 14:19:50 -0400382_us-ascii Does anyone know if this course ever got posted to the IDUG listserv ?
I am interested in getting a copy.
Michael Bruzdzinski Social Security Administration DCS/OESAE/DDBS/DBSB 410-966-7185
"The last temptation is the greatest treason: To do the right deed for the wrong reason." * Thomas Becket in Murder in the Cathedral By In T.S Eliot [...]
3400 89 27_Re: DB2 LUW backups and TSM12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Tue, 18 Aug 2009 11:35:50 -0700538_iso-8859-1 Generally, archives to TSM disk. Backups directly to TSM unless very large then maybe to disk and migrate off to TSM. Latest TSM manual has what you need. Believe you can find it online also. You or TSM person will need to setup .opt and .sys TSM file then put the TSM entries in the DB2 profile. After that, the TSM person needs to set the password one time per instance and you should be good to go. Consider using failarchpath just in case TSM down for some reason. I'll try and find the doc later, getting on a plane. [...]
3490 387 43_Re: Performance issue with a delete process13_Terry Purcell18_tpurcel@US.IBM.COM31_Tue, 18 Aug 2009 18:25:09 +0000406_utf-8 Hi Kals,
The pseudo-delete indicator is in the prefix for the RID. Thus DB2 needs to read the RID prefix to determine if the row is deleted or not. So it's not at the leaf page or key level, it's at a lower level of granularity than that.
I believe the REORG DISCARD is a good approach, since it deletes the rows, cleans up pseudo-deleted entries, reorganizes etc etc, all in one. [...]
3878 159 24_Count(*) with UNIAON all8_duam lee20_duam_lee@HOTMAIL.COM31_Tue, 18 Aug 2009 21:21:39 +0000521_ks_c_5601-1987
Greetings ,
Here is good problem I came across and want all your experinces. Please share your experiences.
A program which is having sql which does a count of two parts and union all like below.
SELECT COUNT (*) INTO :WS-DETA FROM (
FROM A, B
WHERE ...
AND .....
.....
UNION ALL
SELECT C.ID AS ID FROM C ,D,E [...]
4038 212 28_Re: Count(*) with UNIAON all9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Tue, 18 Aug 2009 16:45:13 -0500516_us-ascii If you're really only interested in COUNT then why not sum the counts from each section of the union
SELECT SUM(ROW_COUNTS) FROM ( SELECT COUNT(*) as ROW_COUNTS FROM a,b
UNION ALL
SELECT COUNT(*) as ROW_COUNTS FROM c,d,e ) as x
________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of duam lee Sent: Tuesday, August 18, 2009 4:22 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Count(*) with UNIAON all Importance: High [...]
4251 90 17_TCPKPALV - DB2 V87_Mabel B17_BLANCO@AR.IBM.COM31_Wed, 19 Aug 2009 04:23:05 +0000611_utf-8 Hi All
we have the following errors in DB2 V8:
DSNL511I -DSNI DSNLIENO TCP/IP CONVERSATION FAILED 285 TO LOCATION 129.39.165.165 IPADDR=129.39.165.165 PORT=2719 SOCKET=RECV RETURN CODE=1121 REASON CODE=00000000
DSNL511I -DSNI DSNLIENO TCP/IP CONVERSATION FAILED 459 TO LOCATION 129.39.165.164 IPADDR=129.39.165.164 PORT=1232 SOCKET=SENDMSG RETURN CODE=140 REASON CODE=76697242
and we have the following parameters in zparm:
DDF=AUTO, CMTSTAT=ACTIVE, IDTHTOIN=1800, RESYNC=2, RLFERRD=NOLIMIT, TCPALVER=NO, MAXTYPE1=500, TCPKPALV=ENABLE, POOLINAC=120 [...]
4342 41 28_Re: Count(*) with UNIAON all14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 19 Aug 2009 05:53:01 +0000379_UTF-8 On Tue, 18 Aug 2009 16:45:13 -0500, Sam Baugh wrote:
SELECT SUM(ROW_COUNTS) FROM ( SELECT COUNT(*) as ROW_COUNTS FROM a,b UNION ALL SELECT COUNT(*) as ROW_COUNTS FROM c,d,e ) as x
Or maybe even as follows: SELECT (SELECT COUNT(*) FROM a,b WHERE ...) + (SELECT COUNT(*) FROM c,d,e WHERE ...) INTO :WS-DETA FROM sysibm.sysdummy1 [...]
4384 26 27_Re: DB2 LUW backups and TSM12_James Titmas22_jtitmas@AESSUCCESS.ORG31_Wed, 19 Aug 2009 11:42:46 +0000398_utf-8 I have tried to send logs to tsm but have had problems when they are being written while a backup is also going to TSM. The backup hangs. I have to send archived logs to disk which are then written to TSM while the backup is not running.
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
4411 22 20_DSNDB07 32K datasets17_Darren Kilpatrick31_darren.kilpatrick@WELLPOINT.COM31_Wed, 19 Aug 2009 12:57:09 +0000631_utf-8 We are migrating to DB2 9 CM and with the dynamic prefetch, I am wondering if we need to increase the DSNDB07 32K work datasets to match the 4K datasets?
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG DB2-L FAQ and e-mail settings are located on the IDUG.org Listserv page. If you are not already an IDUG.org member, please register at http://www.idug.org/register _____________________________________________________________________
4434 57 31_Help need in deleting Mutlirows14_Karanath, Daly21_daly.karanath@FMR.COM31_Wed, 19 Aug 2009 18:22:09 +0530370_us-ascii
Hi All,
I am developer. I need to run a Cobol code job which deletes the table A. The table A contains a date column ,say dat. The delete condition is dat< 10 years (in words, delete all the data which is less than 10 years).
The table A contains more than 15 years of data. So the DB team has suggested the commit logic to the code. [...]
4492 118 20_Re: DB2 intro course17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 19 Aug 2009 14:05:07 +0100342_utf-8 Ouch.
I think I may have totally forgotten all about it, since I was up to my eyes with my DB2 V7 SMP/E issues, and then out of a job a few months after that :-S
Apologies to all the people who had asked me... please contact me directly again, offline, and I give you my girl guide's honour that I'll send it to you! [...]
4611 50 41_extending syslgrng dataset on v8 z/0s 1.70_21_BudGreenman@ONGOV.NET31_Wed, 19 Aug 2009 09:08:51 -0400646_US-ASCII I know this has been discussed before, We are having problems on our production system due to the syslgrng dataset maxed out. What is the fastest safe way to extend it?
Thanks
Bud Greenman Applications Programming Manager
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG DB2-L FAQ and e-mail settings are located on the IDUG.org Listserv page. If you are not already an IDUG.org member, please register at http://www.idug.org/register
4662 41 86_How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Wed, 19 Aug 2009 08:43:48 -0500503_us-ascii Does anyone have a good way to measure the "residency time" of a bufferpool? (on Mainframe DB2).
I figure that a "low tech" method of doing this would be to "seed" an object into the bufferpool and then use the "DISPLAY BUFFERPOOL" command to detect when the page is washed out. Another method that might work would be to schedule a "-DIS BUFFERPOOL(*) DETAIL(INTERVAL)" every n minutes and calculate the "flow rate" by looking at the size of the bufferpool and the number of I/Os. [...]
4704 97 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.22_Leslie Pendlebury-Bowe21_db2dba@BTINTERNET.COM31_Wed, 19 Aug 2009 14:54:52 +0100305_us-ascii Hi The way I caculate it is :
Number of pages in the pool / (changed pages / number of seconds in this interval)
So for a pool of 1000 pages with 300 pages updated in a 60 second interval of the display bp command :
1000 / (300/60) = 200 seconds average residency time. [...]
4802 86 45_Re: extending syslgrng dataset on v8 z/0s 1.79_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 19 Aug 2009 09:26:29 -0500570_US-ASCII You don't say whether you are maxed on extents or your allocation is big enough to allocate the second dataset. Your options are all the normal ones for moving a DB2 dataset except it is vcat defined and directory.
If you are maxed on extents, you need to get a new dataset that has better allocations. This requires stop database/start database. You can do this while production is running because only work that is trying to open a logrange or close a logrange is affected. These are the work that get the -904 on the lgrng dataset. 1. DFDSS copy [...]
4889 40 24_Re: DSNDB07 32K datasets13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 19 Aug 2009 16:37:45 +0200586_utf-8 Darren,
With DB2 9's preference to sort using the 32K work file datasets rather than the 4K ones you should definitely increase the size/number of them. I don't remember the threshold when 32k is used rather than 4K but it's surprisingly low - a low multiple of 100K or so. There's also a PTF (again, apologies for not remembering the number) to make DB2 use workfiles that allow 2ndary extents for (D)GTTs and to use workfiles that don't allow 2ndary extents for normal sorting. Think there was a discussion here about large (D)GTTs blowing up because a lot of sites [...]
4930 96 35_Re: Help need in deleting Mutlirows13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 19 Aug 2009 10:39:20 -0400606_utf-8 Hello Daly,
Your program can define a cursor and have the where criteria in the cursor and loop though with FETCH and DELETE WHERE CURRENT OF CURSOR and in your loop logic control the number or time range for doing your commits.
Does this make sense?
Dave
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and [...]
5027 58 24_Re: DSNDB07 32K datasets13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 19 Aug 2009 16:45:54 +0200625_utf-8 It was APAR PK70060 and PTF UK46839:
PROBLEM CONCLUSION: Code has been optimized (1) to provide significant performance improvement with DGTT mass-deletes, (2) to reuse the space occupied by mass-deleted rows at commit for DGTTs with ON COMMIT PRESERVE ROWS option, and (3) to minimize contention for space between work files and DGTTs, by implementing a preference in the selection of table spaces that cannot grow beyond the primary space allocation (due to SECQTY = 0) for work files use, and a preference in the selection of table spaces that can grow beyond the primary space allocation (with SECQTY > [...]
5086 111 35_Re: Help need in deleting Mutlirows13_Mertens, Bart18_bart.mertens@CZ.NL31_Wed, 19 Aug 2009 16:53:25 +0200379_us-ascii Hi Daly,
You could use a cursor to fetch the rows and then use "DELETE WHERE CURRENT OF" to delete the fetched row. Adding a commit after 100 rows will do the trick.
This method is described in the Application Programming and SQL Guide, Chapter 7. "Using a cursor to retrieve a set of rows". There's an example of "DELETE WHERE CURRENT OF" in Step 4. [...]
5198 91 24_Re: DSNDB07 32K datasets22_Leslie Pendlebury-Bowe21_db2dba@BTINTERNET.COM31_Wed, 19 Aug 2009 15:56:01 +0100342_us-ascii Ray The threshold is row length less then 100bytes go to 4K and above go to 32K ... Well it was when I read up a week or so back. :-) but you know how things go, it will change now in the next ten minutes :-))
And in my experience with 70 odd migs to db2 9 now, is to seriously increase the number and size of your 32Ks. [...]
5290 113 35_Re: Help need in deleting Mutlirows10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 19 Aug 2009 11:13:46 -0400550_US-ASCII You don't mention the volume of data in the table or how much qualifies for deletion (1/3?), nor whether the tablespace is partitioned or not. I am presuming that you actually want to delete the data that is over 10 years old and to retain the more recent data.
It seems to me that a Reorg Discard would be a more effective option for the initial run. Subsequent monthly deletes would be deleting less that 1% of the data (1 month of 120), so that could be done by a program. Again, depending on the volume, you could delete the [...]
5404 82 35_Re: Help need in deleting Mutlirows13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 19 Aug 2009 17:46:59 +0200557_utf-8 Hi Daly,
The 'delete where current of cursor' approach will work, but there are problems with it. Do you really want to make DB2 return a row to your program just so you can then tell DB2 to delete it? It would, in my opinion, be better if you could (in addition to DAT) find another column in the table you could restrict the number of rows deleted in any given Delete statement. Assuming even data spread (ha ha) you could say 'delete from Cust_Table where Last_Purchase_Date < current date - 10 years and Surname like 'A%' and run that [...]
5487 386 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 19 Aug 2009 12:01:47 -0400510_iso-8859-1 Normally I don't focus on changed pages only, since most pages (as I see across most installations) are only referenced unless you're in a specific heavy update . Focusing on changed pages, I use for other analyses.
Are you trying to look at individual objects, or the overall pool?
As a swag for page residency time I use - multiply the system hit ratio * the measured interval. system hit ratio = ((getpages -sum of all pages read into the pool by all access methods)/getpages) [...]
5874 50 24_Re: DSNDB07 32K datasets13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 19 Aug 2009 18:03:57 +0200397_us-ascii Cheers L-BP. Was thinking along those lines but didn't want to commit to 'ink'. So 'a low multiple' in this case is, er, 1!
RayMOND ;o)
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Leslie Pendlebury-Bowe Sent: 19 August 2009 15:56 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DSNDB07 32K datasets [...]
5925 43 48_A No-charge IBM seminar is being held in Houston13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 19 Aug 2009 11:13:26 -0500472_ISO-8859-1 Hello to everyone in the Houston area, anyone within driving distance of Houston, or anyone just looking for a reason to spend a day in 100° F temperatures with 95% humidity for a day.
IBM, along with the Houston Area DB2 User Group, is sponsoring a DB2 Seminar in Houston on September 15, 2009. It will be held at the IBM offices at Two Riverway (just off Woodway and the 610 Loop for the locals). I have a blog entry with some additional details. [...]
5969 445 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.22_Leslie Pendlebury-Bowe21_db2dba@BTINTERNET.COM31_Wed, 19 Aug 2009 17:34:22 +0100587_us-ascii hi Joel, I just knew I'd learn something here :-) Leslie
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel Responsive Systems Sent: 19 August 2009 17:02 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.
Normally I don't focus on changed pages only, since most pages (as I see across most installations) are only referenced unless you're in a specific heavy update . Focusing on changed pages, I use for other analyses. [...]
6415 107 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 19 Aug 2009 12:54:57 -0400694_us-ascii Hi Larry,
I assume you have not taken the IBM course "CV960 Application Performance and Tuning" [was CF961]. Perhaps someone else at M of A has the notes.
In the chapter on "Massive batch" is a relatively simple technique that lets you determine MUPA [Maximum Unreferenced Page Age].
Depending upon how fine-tuned you want to be, the suggested 10-30-100-300-1000 [equal logarithmic breakdown] should do. More fine-tuned might be 10-18-30-56-100-180-300-560-1000-etc. [also an equal logarithmic breakdown]. Now create a 1-row, 1-column table for each of those increments, each table in its OWN tablespace assigned to the bufferpool you'd like to measure: T10, [...]
6523 43 17_IBM Take It Again17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 19 Aug 2009 18:28:52 +0100563_iso-8859-1 IBM are running a program for free vouchers to retake a Certification exam one has failed.
https://www.ibm.com/developerworks/mydeveloperworks/blogs/SusanVisser/entry/certification_try_it_again7
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
please don't print this e-mail unless you really need to!
IDUG Europe 2009 - Rome, Italy - October 5-9, 2009 http://IDUG.ORG/EU [...]
6567 366 33_[z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Wed, 19 Aug 2009 12:51:22 -0500363_US-ASCII I reorg'ed and runstats a z/OS v8 NFM catalog this morning.
RTS shows the correct UPDATESTATSTIME, but REORGLASTTIME did not change and none of the REORG* counting columns got reset.
I reorged some user spaces and everything gets externalized / reset just fine for them.
I wanted to use RTS to help with making Is this normal ? [...]
6934 153 35_DB2 Cobol precompiler and ORACLENET16_Wolters, Jeff B.24_Jeff.Wolters@PGNMAIL.COM31_Wed, 19 Aug 2009 14:11:07 -0400358_us-ascii We are running DB2 UDB Z/OS V8 NFM.
We have recently received and are preparing to install ORACLENET 10g R2, in order to access Oracle tables from a mainframe program.
Our developers are asking if anyone has successfully compiled a COBOL program using both pre-compilers, so that the SQL calls don't get confused with each other. [...]
7088 72 59_Re: tool to create "mapping" of XML to DB2 table/row/column13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 19 Aug 2009 14:16:47 -0400558_us-ascii Response from my co-worker Gregg Lippa:
Looks like Altova (makers of XMLSpy) has this space sewed up with their MapForce product. There are several editions starting at $249: http://www.altova.com/products.html
Gregg Lippa Senior Technical Advisor Themis Education
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Davis, Kriss Sent: Tuesday, August 18, 2009 1:04 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] tool to create "mapping" of XML to DB2 table/row/column [...]
7161 176 39_Re: DB2 Cobol precompiler and ORACLENET16_David Sanfilippo31_david_sanfilippo@MASTERCARD.COM31_Wed, 19 Aug 2009 14:01:46 -0500536_ISO-8859-1 Not sure if it's still true, since it's been 10+ years ago, and at a different site, but we were able to do this from a single COBOL program.
The problem then was that both Oracle and DB2 SQL statements are started with EXEC SQL and the END-EXEC. What we did to get around this was each SQL call had to be coded to identify which DBMS to use. The programmers had to do this by coding EXEC DB2 (or ORACLE) and END-DB2 (or ORACLE) . We wrote a couple pre-precompiler programs to change the EXEC DB2 to valid syntax [...]
7338 65 45_Re: extending syslgrng dataset on v8 z/0s 1.711_Mukesh Jain21_jain.mukesh@AOINS.COM31_Wed, 19 Aug 2009 19:41:08 +0000557_utf-8 There are various methods you can extend the DB2 catalog datasests. Depends on for what it is failing.
1) If there is no space on the volume it resides and there is no candidate volume. Since DB2 catalog and directory datasets are user managed not DB2 managed. You need to add the volume to the datasets. But you need to stop the tablespace or indexspace for that. Than run IDCAMS ADDVOL and start the tablespace or index space. Sometimes i take the short cut if i don't want any outage. If you can identify some datasets which are not so [...]
7404 32 24_Re: DSNDB07 32K datasets11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Wed, 19 Aug 2009 19:46:09 +0000356_utf-8 So far my experience with 30 DB2 subsystems upgrade to v9 says that you need to define the 32k datasets atleast equal to whatever you had 4K datasets in v8. if you have space constrain (which should not be as space is very chap now) you can reduce some 4k space and give it to 32K. I have seen that most of the work is done now in 32K datasets. [...]
7437 51 35_Re: Help need in deleting Mutlirows11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Wed, 19 Aug 2009 20:05:37 +0000637_utf-8 There are many ways you can do either through Where current of cursor or if you are using the stored procedure you can use the stored procedure:
CREATE PROCEDURE DELETE_MANY_ROWS (tabschema VARCHAR(128), tabname VARCHAR(128), predicate VARCHAR(1000), commitcount INTEGER) BEGIN DECLARE SQLCODE INTEGER; DECLARE txt VARCHAR(10000); DECLARE stmt STATEMENT; SET txt = 'DELETE FROM (SELECT 1 FROM "' || tabschema || '"."' || tabname || '" WHERE ' || predicate || ' FETCH FIRST ' || RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';> PREPARE stmt FROM txt; l: LOOP EXECUTE stmt; IF SQLCODE = 100 THEN LEAVE l; END IF; COMMIT; [...]
7489 158 46_IDUG India Sept 24-26 - Call for presentations11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 19 Aug 2009 15:06:27 -0500608_Windows-1252
Posting this on behalf of Anil Mahadev - the Conference Chair for IDUG India, which will take place in Bangalore, India Sept 24-26.
Check out the conference web page for details.
Thx
Suresh
Speak @ IDUG India Forum 2009! Be a DB2 RockStar!
The Conference Planning Committee strives to achieve a balanced conference program of business solutions relevant to today’s IT environment and usage of DB2 on one or more platforms (e.g., Windows, Linux, UNIX, z/OS). Presentation Categories include: [...]
7648 134 35_Re: Help need in deleting Mutlirows9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 19 Aug 2009 15:17:47 -0500326_US-ASCII Remember that you don't have to retrieve all the data for the row, only one column is required to establish a cursor. And DB2 has to look at each data page anyway so there isn't any extra cost there.
The CPU cost to retrieve a row has decreased with every release of DB2 and Z/os but it still isn't free. [...]
7783 103 35_Re: Help need in deleting Mutlirows14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Wed, 19 Aug 2009 15:32:09 -0500336_us-ascii And you can save some CPU by using mltirow fetch and rowset delete. Khalid
Remember that you don't have to retrieve all the data for the row, only one column is required to establish a cursor. And DB2 has to look at each data page anyway so there isn't any extra cost there. [...]
7887 591 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 19 Aug 2009 17:09:17 -0400496_iso-8859-1 Hi Leslie,
How are you doing? Hope all's well, and you're having some fun.
Regards, Joel
Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance 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 [...]
8479 68 39_Re: DB2 Cobol precompiler and ORACLENET15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM31_Wed, 19 Aug 2009 18:47:49 -0600587_us-ascii >>> On 8/19/2009 at 12:11 PM, in message <06C9D19053DFB24DB66AB7BDA9D1881F06B7942E@NT000835.oak.zone1.progress-energy.com , "Wolters, Jeff B." wrote: > We are running DB2 UDB Z/OS V8 NFM. > > We have recently received and are preparing to install ORACLENET 10g R2, > in order to access Oracle tables from a mainframe program. > > Our developers are asking if anyone has successfully compiled a COBOL > program using both pre-compilers, so that the SQL calls don't get > confused with each other. > > Anyone out there have any background on this? [...]
8548 144 35_Re: Help need in deleting Mutlirows14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 19 Aug 2009 21:31:22 -0400390_us-ascii Hi Raymond and Daly,
Just a reminder that Raymond's idea is only efficient if there is already and index that starts with (the_date_col, the_name_col,...) so that the suggested search is Matching Columns = 2.
Next best is an index that has 1 or more other columns between the 2, (the_date_col,..., the_name_col,...) whic h is MC = 1 and screening for name LIKE. [...]
8693 112 28_Re: Count(*) with UNIAON all8_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 20 Aug 2009 01:31:05 +0000677_ks_c_5601-1987
Hi Peter
I had tested the first option and it did take the same time and queryblock UNION ALL was consuming the almost 90 percent of the elapsed time. I would try the second option and check it up for sure.
With Thanks Duam.
> Date: Wed, 19 Aug 2009 05:53:01 +0000 > From: pvanroose@ABIS.BE > Subject: Re: [DB2-L] Count(*) with UNIAON all > To: DB2-L@WWW.IDUGDB2-L.ORG > > On Tue, 18 Aug 2009 16:45:13 -0500, Sam Baugh wrote: > > SELECT SUM(ROW_COUNTS) FROM ( > SELECT COUNT(*) as ROW_COUNTS FROM a,b > UNION ALL > SELECT COUNT(*) as ROW_COUNTS [...]
8806 30 62_AUTO: Renee Wilson is out of the office (returning 31.08.2009)12_Renee Wilson17_TM2110@CH.IBM.COM31_Thu, 20 Aug 2009 04:04:06 +0200365_US-ASCII I am out of the office until 31.08.2009.
I am currently on leave enjoying sunny, sparkling, splendid Spain from Thursday 20th August 2009. I will be back in the office on Monday 31st August 2009.
Note: This is an automated response to your message "DB2-L Digest - 19 Aug 2009 to 20 Aug 2009 (#2009-239)" sent on 20/8/09 3:00:03. [...]
8837 70 58_AUTO: Arun Rao is out of the office (returning 08/20/2009)8_Arun Rao16_arunr@US.IBM.COM31_Wed, 19 Aug 2009 20:58:38 -0600332_US-ASCII
I am out of the office until 08/20/2009.
I will be out of the office on Wed 08/18/2009. Please contact my manager for any issues that can't wait.
Note: This is an automated response to your message "DB2-L Digest - 19 Aug 2009 to 20 Aug 2009 (#2009-239)" sent on 8/19/09 19:00:03. [...]
8908 310 43_Re: Performance issue with a delete process14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 19 Aug 2009 23:22:39 -0400600_us-ascii Hi Kals,
I think the REORG DISCARD is your best option.
I can't do a full analysis, but with Terry's input of the re-reading of the deleted index rows [rids], I think I know why "it hangs".
You said that for each table the three indexes share the same buffer pool [BP] and that the BP is 3000 pages [quite small in today's world]. The Timestamp [TS] index will be probed [root->middle->leaf] sequentially, about 237 entries per page, or 5 pages per 1000 entries [assume 0% Freespace]. The other indexes will get deletions RANDOMLY [although there will be maybe 50% [...]
9219 57 32_SV: [DB2-L] DSNDB07 32K datasets13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Thu, 20 Aug 2009 07:38:32 +0200459_iso-8859-1 You should definitly increase the size of your 32K work datasets. We have been running on 9 for a year now and our workfile configuration is now 70% on 32K and 30% of 4K which seems to be a quite good balance. In prod we have 8 32K datasets and 4 4K datasets for each DB2 member the 32K spaces are 2GB each and the 4K ones are about 1 GB each DB2 9 often prioritize to use 32K and will the spill over to 4K when the 32K storage are exhausted. [...]
9277 515 50_Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 07:50:29 +0200539_ISO-8859-1 didnt believe it so I tested it here...strange...Copy and Runstats works fine...Reorg updates nothing...very very odd...my bet is an APAR has "broken something" since March 2009....
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
9793 27 37_Re: [z/OS v8 NFM] RTS and the Catalog5_Steve21_malutjuta@HOTMAIL.COM31_Thu, 20 Aug 2009 05:56:03 +0000735_utf-8 hi Mark,
There's quite a few APAR fixes for RTS in V8.1, our recent Db2 maintenance release had only half of them included, best to checkout IBMLINK
regards, Steve M
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *
_____________________________________________________________________
IDUG Europe Attendee Testimonial- "I had my first experience with certifications (and passed)! I learned a lot about DB2 version 9 (LUW) that hopefully will enable me to convince management a timely migration will be of benefit for the business." _____________________________________________________________________
9821 81 50_Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 08:00:50 +0200460_ISO-8859-1 just checked all of our V8 DSNDB06's and the last successfull update of REORG data was in Jan 2009. We are always early with maintenance here so my bet is something that came out in Nov, Dec last year killed the update...
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 [...]
9903 79 32_RTS and the Catalog V8 and V9 !!11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 08:06:34 +0200488_ISO-8859-1 same is true for V9 in NFM by the way :( looks like someone did a bad job somewhere! INDEXes are also not updated/reset by the way
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
9983 95 50_Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 08:11:21 +0200552_ISO-8859-1 just found this
PK56504: REORGLASTTIME NOT UPDATED FOR CATALOG/DIRECTORY OBJECTS
R810 PSY UK32960 R910 PSY UK32961
looks old but I will check if we have it installed
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
10079 414 26_Re: IDUG Europe 2009, Rome17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Thu, 20 Aug 2009 10:01:11 +0100638_iso-8859-1 Roy,
How about you start advertising IDUG Europe 2009? ;-)
It's sooner, it's closer, and it's going to be great education and networking...
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
P please don't print this e-mail unless you really need to!
IDUG Europe 2009 - Rome, Italy - October 5-9, 2009 [...]
10494 30 44_Deb Van Epps/MutualOMA is out of the office.0_30_Deb.Van.Epps@MUTUALOFOMAHA.COM31_Thu, 20 Aug 2009 05:01:01 -0500656_us-ascii I will be out of the office starting 08/19/2009 and will not return until 08/24/2009.
If you need immediate assistance please contact my manager Kevin Stettler x3185.
This e-mail and any files transmitted with it are confidential and are solely for the use of the addressee. It may contain material that is legally privileged, proprietary or subject to copyright belonging to Mutual of Omaha Insurance Company and its affiliates, and it may be subject to protection under federal or state law. If you are not the intended recipient, you are notified that any use of this material is strictly prohibited. [...]
10525 34 33_Static SQL & Dynamic access check15_Itschak Mugzach18_imugzach@GMAIL.COM31_Thu, 20 Aug 2009 11:21:36 +0000382_utf-8 I am trying to control the way DB2 makes authorization check when DB2 resources are accessed. usually, DB2 perform authorization check at bind time (and complete the check if validate=run at run time). I want Static SQL to be checked at run time for all SQL statements using the userid of the person/task that runs the plan and not using the plan/package owner identity. [...]
10560 205 54_Re: Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 07:12:02 -0500555_ISO-8859-1 Thanks Roy.
Roy Boxwell Sent by: DB2 Data Base Discussion List 08/20/2009 02:58 AM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog
just found this
PK56504: REORGLASTTIME NOT UPDATED FOR CATALOG/DIRECTORY OBJECTS
R810 PSY UK32960 R910 PSY UK32961 [...]
10766 2821 43_Please remove me from the distribution list0_20_Jeff.Wible@CHASE.COM31_Thu, 20 Aug 2009 07:14:19 -0500652_iso-8859-1
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2-L automatic digest system Sent: Wednesday, August 19, 2009 9:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2-L Digest - 19 Aug 2009 to 20 Aug 2009 (#2009-239)
There are 35 messages totalling 4262 lines in this issue.
Topics of the day:
1. TCPKPALV - DB2 V8 2. Count(*) with UNIAON all 3. DB2 LUW backups and TSM 4. DSNDB07 32K datasets (6) 5. Help need in deleting Mutlirows (8) 6. DB2 intro course 7. extending syslgrng dataset on v8 z/0s 1.7 (2) 8. How to measure "residency time" or MUPA [...]
13588 207 54_Re: Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 07:19:34 -0500562_ISO-8859-1 Well I have UK32960 APPLIED, so I will keep looking... Mark Vickers
Roy Boxwell Sent by: DB2 Data Base Discussion List 08/20/2009 02:58 AM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog
just found this
PK56504: REORGLASTTIME NOT UPDATED FOR CATALOG/DIRECTORY OBJECTS [...]
13796 280 54_Re: Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 07:55:18 -0500598_ISO-8859-1 FYI: I found
PK52434: EXTENT FIELD IN RTS TABLES NOT UPDATED BY REORG SHRLEVEL CHANGE
R810 PSY UK29779 UP07/10/17 P F710 R910 PSY UK29780 UP07/10/17 P F710
UK29779 is SUP by UK33692 which I have and still have no soution.......
Mark.
Roy Boxwell Sent by: DB2 Data Base Discussion List 08/20/2009 02:58 AM Please respond to DB2 Database Discussion list at IDUG [...]
14077 337 71_Antwort: Re: [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 15:02:30 +0200456_ISO-8859-1 needless to say we did not have it installed in V8 or 9....so much for "current on maintenance" :)
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
14415 342 71_Antwort: Re: [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 15:49:08 +0200271_ISO-8859-1 hmm - My system guys say they are both not applied - I have asked that they get applied asap - then I will do some more catalog reorgs - luckily I do not rely on the RTS data for DB2 Catalog / Directory reorg decisions or I would be looping since March!! [...]
14758 262 37_Re: [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 08:54:32 -0500700_ISO-8859-1 Oh boy - it helps to RTFM........
RTS is only updated by the runstats UPDATE ALL keyword !
I added it to my reorg control cards and wallah :
DBNAME NAME PARTITION SPACE TOTALROWS EXTENTS REORGLASTTIME REORGINSERTS REORGDELETES REORGUPDATES TOTAL_CHANGES REORGDISORGLOB -------- -------- --------- ------------ ------------ ------- -------------------------- ------------ ------------ ------------ ------------- -------------- --------------- DSNDB06 SYSALTER 0 672 85 1 2008-03-03 11:17:46.440562 217 132 0 349 0 DSNDB06 SYSCOPY 0 7200 8368 1 2008-03-03 11:24:06.651526 14371 8292 81 22744 0 DSNDB06 SYSDBASE 0 352080 44392 1 2009-08-20 08:44:35.472534 0 0 0 0 0 [...]
15021 360 53_Antwort: Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 15:56:32 +0200635_ISO-8859-1 duh....pizza for the world then?
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert
MarkVickers@GROCERYBIZ.COM 20.08.2009 15:54
An DB2 Database Discussion list at IDUG Kopie R.Boxwell@SEG.DE, malutjuta@HOTMAIL.COM Thema Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog [...]
15382 127 71_Antwort: Re: [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 20 Aug 2009 16:01:03 +0200709_ISO-8859-1 Weirder...I just did the inline runstats and now I get
-+---------+---------+----- LOADRLASTTIME -+---------+---------+----- 2009-08-20-15.54.23.416624 2009-08-20-15.54.24.043819 2009-08-20-15.54.28.235691 2009-08-20-15.54.28.953796 2009-08-20-15.54.29.710650
which is the "other" older bug and the TS is not updated at all ------+---------+---------+---------+---------+---------+---------+--- LOADRLASTTIME REORGLASTTIME REORGINSERTS ------+---------+---------+---------+---------+---------+---------+--- 0001-01-01-00.00.00.000000 0001-01-01-00.00.00.000000 535
I am using SYSVIEWS for test...perhaps thats why....I wait until our sysprog has done the apply... [...]
15510 316 43_Re: Performance issue with a delete process14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 20 Aug 2009 10:04:06 -0400439_us-ascii AND...I think if you use the DECLARED GLOBAL TEMP TABLE option [below] you'll want to change the Timestamp index to be (Timestamp, key_cols) to speed the INSERT ..SELECT of those columns with index only access. LK
Hi Kals,
I think the REORG DISCARD is your best option.
I can't do a full analysis, but with Terry's input of the re-reading of the deleted index rows [rids], I think I know why "it hangs". [...]
15827 29 27_Re: DB2 LUW backups and TSM12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 20 Aug 2009 14:12:46 +0000359_utf-8 I've heard of this problem but I never encountered it myself. Presumably depends on the DB2 version.
According to IBM, it should only happen if you include logfiles into the backup sets and log archiving occurs in parallel during backups. IBM recommendation is to use different TSM server definitions for backups and log files in this case. [...]
15857 451 26_Re: IDUG Europe 2009, Rome0_23_j.p.slot@RN.RABOBANK.NL31_Thu, 20 Aug 2009 16:12:35 +0200543_iso-8859-1 Aurora, Put it on the footer of the listserver? Regards, Jaap
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dell'Anno, Aurora Sent: Thursday, August 20, 2009 11:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] IDUG Europe 2009, Rome
Roy,
How about you start advertising IDUG Europe 2009? ;-)
It's sooner, it's closer, and it's going to be great education and networking... [...]
16309 72 35_Re: Help need in deleting Mutlirows13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 20 Aug 2009 16:31:00 +0200586_us-ascii Hey Larry,
Quite agree. A searched delete of any kind ideally needs a useful index to make it, well, useful. So as a former colleague of mine used to say, 'Good point, well made.'
Reorg with Discards is a good choice for the initial purge, but it might also be a good option for the regular monthly purge. Among other benefits, it keeps things nice 'n' clustered, assuming there are sequential processes out there that would benefit. Might not be the fastest option though, as each reorg (if it Discards the oldest month's data for the previous 10 years) is [...]
16382 377 49_Redesigned Q Replication Introduction and Webcast7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Thu, 20 Aug 2009 10:37:17 -0400710_US-ASCII The Fillmore Group and leading experts from IBM will deliver a 45-minute webcast to introduce the Q Replication Dashboard and improvements included in the new, redesigned Dashboard scheduled for release this month.
The Q Replication Dashboard is the free monitoring tool available from IBM that replaces the Java Q Replication Dashboard and the Data Studio Administrative Console (DSAC) with a thin-client, browser-based interface. The Dashboard provides real-time Q Replication monitoring. In the webcast presenter and DB2 Gold Consultant Frank C. Fillmore, Jr., will introduce and explain how the new redesigned Dashboard's expanded features and better ability to customize will enhance [...]
16760 34 33_Null indicator variable in DCLGEN11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Thu, 20 Aug 2009 14:31:19 +0000350_utf-8 Hi, When we create a copybook using the DB2I> DCLGEN utility, it creates an array for all the null indicators. I have to then amend the copybook manually to enter individual null indicators. Is this the way that DCLGEN creates the copybook or is there any other way wherein the null indicators are listed individually instead of an array. [...]
16795 117 38_PTF UK40685 on V8 or PTF UK40686 in V913_Palko, George16_gpalko@OPERS.ORG31_Thu, 20 Aug 2009 18:00:37 +0100628_us-ascii Hi List,
I was wondering how many companies came across issues after applying PTF UK40685 on V8 or PTF UK40686 in V9?
Basically, after applying UK40685 or UK40686 , SQL statements which have numeric predicates that are of varying lengths are no longer index-able. In V7, for a predicate to be index-able the lengths had to match. V8 removed this restriction and allowed varying length numeric predicates to be index-able. V9 same as V8, until PUT0810. Needless to say the application of these two PTF's requires code change. Specifically the addition of the CAST function on the larger predicate. My [...]
16913 65 56_Create index on Declared Temporary Table fails with -55112_Carlos Olson23_Carlos.Olson@INOVIS.COM31_Thu, 20 Aug 2009 10:06:16 -0700600_us-ascii I searched the ListServ archives, Google and the SQL Reference manual but cannot figure this out for the life of me. Why can I declare the global temporary table but I cannot create the index. The SQL Reference guide states that all privileges are implicitly granted to public for declaring global temporary tables but I can't find info on privileges required to create the index. I granted use of bufferpools, use of TEMP tablespace, I even granted DBADM on database TEMP to QRSIMAG. What privilege does QRSIMAG below require short of SYSADM? (I have no problem executing this with my [...]
16979 65 35_Re: Help need in deleting Mutlirows13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Thu, 20 Aug 2009 17:05:47 +0000474_utf-8 Depending on how complex the logic is for the delete process it may not be possible to code a REORG DISCARD.
If you do indeed need a program to perform the deletes - it may be worth looking into the following:
1) How is the data clustered (row sequence) / partitioned - it may be possible to run more than one delete-process in parallel to reduce over all run-time, but be aware of possible contention if the deletes overlap onto the same data page [...]
17045 483 57_Re: Antwort: Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 20 Aug 2009 13:55:37 -0400382_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...]
17529 65 39_DB2 z/OS Manuals on Line - Book Manager0_22_DB2information@AOL.COM29_Thu, 20 Aug 2009 14:35:52 EDT421_US-ASCII Hello List, Does anyone have a link to manuals for DB2 z/OS. DB2 Application and SQL Guide. I am seeking all the SELECT options to retrieve data. I have some links but none that will allow me to print pages from only the subject that I am seeking.
Ed
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
17595 110 60_Re: Create index on Declared Temporary Table fails with -55110_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 20 Aug 2009 14:41:20 -0400480_US-ASCII Have you explicitely granted "all privileges" on the GTT to QRSIMAG? I recall being frustrated by a similar problem a few years ago.
Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Carlos Olson Sent: Thursday, August 20, 2009 1:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Create index on Declared Temporary Table fails with -551 [...]
17706 537 37_Re: [z/OS v8 NFM] RTS and the Catalog13_Hampton, Kirk26_kirk.hampton@CAPGEMINI.COM31_Thu, 20 Aug 2009 15:04:02 -0400491_iso-8859-1 If you are reorg'ing your DB2 Catalog / Directory based on RTS recommendations, how often are you doing so ? And are we talking about a Production DB2 subsystem, or one used for Application Development ? We have never been able to detect much performance degradation or improvement before/after a Catalog reorg, regardless of whether it was done twice a year or once every 8 years. We are not using RTS yet, but I am understanding that it is near to becoming a requirement ? [...]
18244 188 43_Re: DB2 z/OS Manuals on Line - Book Manager10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 20 Aug 2009 15:06:05 -0400382_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...]
18433 254 43_Re: DB2 z/OS Manuals on Line - Book Manager14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 20 Aug 2009 15:42:14 -0400627_us-ascii Do you need a link to the PDF? http://publib.boulder.ibm.com/epubs/pdf/dsnapk14.pdf
________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2information@AOL.COM Sent: Thursday, August 20, 2009 2:36 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/OS Manuals on Line - Book Manager
Hello List, Does anyone have a link to manuals for DB2 z/OS. DB2 Application and SQL Guide. I am seeking all the SELECT options to retrieve data. I have some links but none that will allow me to print pages from only the subject that I am seeking. [...]
18688 138 43_Re: DB2 z/OS Manuals on Line - Book Manager18_Hynes, Carol - DWD29_Carol.Hynes@DWD.WISCONSIN.GOV31_Thu, 20 Aug 2009 14:33:40 -0500507_us-ascii http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsqj16/CCONTENTS
If you click on the print option on the page (not the print in the browser toolbar) you can select: Current Topic Only Selected Topics All topics ________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2information@AOL.COM Sent: Thursday, August 20, 2009 1:36 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/OS Manuals on Line - Book Manager [...]
18827 258 75_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 14:56:42 -0500501_ISO-8859-1 Well it's interesting that you get the warning: DSNU629I -DSNT DSNURFIT - INLINE STATISTICS IS NOT SUPPORTED FOR SYSPLAN and a bunch other, not all though, but.... If you don't do STATISTICS UPDATE ALL, you will not get RTS updates !!! go figure ! Mark Vickers
Roy Boxwell Sent by: DB2 Data Base Discussion List 08/20/2009 11:16 AM Please respond to DB2 Database Discussion list at IDUG [...]
19086 112 60_Re: Create index on Declared Temporary Table fails with -55113_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 20 Aug 2009 15:12:43 -0500446_us-ascii I'm able to execute that sequence just fine without SYSADM (on DB2 9 NFM). However, just a guess -- try qualifying your GTT ("SESSION.XYZ") on the DECLARE.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Carlos Olson Sent: Thursday, August 20, 2009 12:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Create index on Declared Temporary Table fails with -551 [...]
19199 616 37_Re: [z/OS v8 NFM] RTS and the Catalog12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 20 Aug 2009 15:20:45 -0500362_ISO-8859-1 Kirk, I have not yet implemented the RTS queries (which I am adding to our queries that drive the reorgs from the older statistics) so I cannot answer the first question. Still making sure I don't get repeat selections after reorging the first round, and that was when the same DSNDB06 objects kept popping back up and I started digging into it. [...]
19816 367 41_[v9 z/OS COMPAT] The New TEMP tablespace?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 20 Aug 2009 17:14:02 -0400818_us-ascii I'm using (trying) DB2 Admin Tool to reverse-engineer DDL, trying to create a new database for PLAN_TABLEs in DB2V9. What I get is this:
----------------------------------------------------------------------- ADB2GEN - Create DDL from catalog info -----------------------------------------------------------------------
ADB1649E ADB2RET: Unexpected sqlcode in : Dcl adbgen gtt
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E7009A, TYPE OF RESOURCE 200, RESOURCE NAME TABLESPACE IN WORKFILE DATABASE DSNDB07 DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXIDCL SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 15 0 0 -1 20 2916 SQL DIAGNOSTIC INFORMATIO DSNT416I SQLERRD = X'0000000F' X'00000000' X'00000000' [...]
20184 103 33_Re: decrypt_char function problem14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 20 Aug 2009 17:27:51 -0400319_us-ascii Hi Jeff,
As Dave said, Chapter three has all of the functions and their arguments.
For DECRYPT_CHAR( ), ACCOUNT_ID should be defined as CHAR( ) FOR BIT DATA .
There does not seem to be a DB2 function to cast a CHAR to a CHAR ..FOR BIT DATA. BINARY data doesn't seem to be compatible! [...]
20288 289 45_Re: [v9 z/OS COMPAT] The New TEMP tablespace?7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 20 Aug 2009 14:55:40 -0700515_utf-8 At the risk of being wrongly obvious, do you have a 32k table space defined? Or maybe several huge ones? Could they be blowing out of space? Is the target DDL result set huge ( many thousands of objects)? If you do have 32k tablespaces defined then I would: 1: Search IBMLINK for a bug in the admin tool; its a target rich environment. 2: Run a SQL trace and try to get the detail on the GTT? Exactly how big is the effective row length in bytes, Johnny? 3: Did you run a dis db restrict for DSNDB07? 4: [...]
20578 195 34_DB2 OSC and Plan_Table Alias setup23_Lazowski, James S (Jim)25_Jim.Lazowski@NAVISTAR.COM31_Thu, 20 Aug 2009 17:26:53 -0500567_us-ascii Hello all, I'm playing around with DB2 OSC and it seems to be working well if you're sysadm when pointing at explain tables via an alias', however when a non-sysadm person is set up with alias' and they try to connect to the subsystem and OSC goes through its checks, it indicates that "EXPLAIN is Disabled". The workaround for this is to have the user re-enable the him/herself for explain and selecting "Use Existing Explain tables" then clicking on Check Tables. This O.K. but they have to do it every time they exit the product and reconnect again. [...]
20774 151 60_Re: Create index on Declared Temporary Table fails with -55112_Carlos Olson23_Carlos.Olson@INOVIS.COM31_Thu, 20 Aug 2009 16:19:22 -0700323_us-ascii Thank you Roger Hecq and Mike Vaughn for suggestions but it turns out that this was a good example of bad naming conventions or less than clear error messages. In the message below:
DSNT408I SQLCODE = -551, ERROR: QRSIMAG DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT QRSIMAG [...]
20926 391 16_Re: [z/OS] Perl?16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 20 Aug 2009 23:28:35 -0400595_windows-1252 Phil,
Sorry about the late addition to this thread that you started, but I'm interested in getting your (and other people's) take on something: how important is it for the Perl code (or PHP or Python or Ruby or whatever) to actually run on a mainframe server, versus having Perl apps running on LUW application servers and accessing DB2 for z/OS by (for example) calling stored procedures via DB2 Connect (and I'm thinking that to an increasing extent the stored procedures will be SQL procedures)? I often think of the future of DB2 for z/OS and the mainframe as being [...]
21318 68 37_Re: Null indicator variable in DCLGEN17_Tunen, Marcel van31_Marcel.van-Tunen@CORUSGROUP.COM31_Fri, 21 Aug 2009 09:22:34 +0200464_US-ASCII That's just the way a dclgen is created. We've written a cobol program to convert. It scans the declaration part to find lines without not null and subsequently ads a -NULL line to the cobol declaration at the appropriate spot.
Met vriendelijke groet, Marcel van Tunen
-----Original Message----- From: Mukesh Jain [mailto:jain.mukesh@AOINS.COM] Sent: Thursday, August 20, 2009 4:31 PM Subject: Null indicator variable in DCLGEN [...]
21387 236 18_Migrate tablespace41_=?iso-8859-2?Q?B=F6hm_Jan_=28ITA-302=29?=24_Jan.Bohm1@IT-AUSTRIA.COM31_Fri, 21 Aug 2009 09:59:56 +0200675_iso-8859-2
> Hi List, > > Is there any way to migrate tablespace created in version 8 DB2 z/OS > to universal tablespace established in version 9 z/OS? > > Thanks > > Jan Böhm > > Informations-Technologie Austria CZ, s.r.o. > Antala Sta¹ka 32/1292, 140 00 Praha 4 > mailto: Jan.Bohm1@it-austria.com > http://www.it-austria.com > Company Identification No: 27654443 > registered in the Commercial Register by the City Court in Prague, Section C, File No.: 1218661 > > Tato zpráva a v¹echny pøipojené soubory jsou dùvìrné a urèené výluènì adresátovi(-ùm). Jestli¾e nejste oprávnìným adresátem, je zakázáno jakékoliv zveøejòování, zprostøedkování nebo jiné [...]
21624 496 22_Re: Migrate tablespace13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Fri, 21 Aug 2009 10:55:15 +0200347_iso-8859-2 Sure is; unload, drop, recreate, reload. If the original object is a 'normal' index-controlled partitioned object it has to be converted to table-based partitioning first - or at the same time.
Of course, there are tools out there to ease the process, but there's no magic 'flick the switch and you're a UTS' method. Sorry. [...]
22121 602 54_Antwort: Re: [DB2-L] [z/OS v8 NFM] RTS and the Catalog11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 21 Aug 2009 12:29:11 +0200537_ISO-8859-1 we're an IVP so not production at all! I reorg very often of course! Most places I know try to reorg the catalog about once a year and sometimes for special reasons some other TS's (most notably in my case SYSCOPY) on a more regular basis, some times even weekly. As IBM say it does not help DB2 a bit but it could help your own or even third party software tools run faster. Think along the lines of DDL gen, SYSCOPY checking, etc. And finally space management. DSNDB01 can get quite big these days and so more and more [...]
22724 233 38_Re: DB2 OSC and Plan_Table Alias setup15_Stephen Vagnier17_sjvagnier@AEP.COM31_Fri, 21 Aug 2009 08:20:46 -0400460_ISO-8859-1 Hi Jim,
I got around the problem by allowing the users to create their own explain tables in DSNDB04 using a table naming convention that would make the explain table names unique.
Regards,
**************************************** Steve Vagnier Staff IT System Administrator American Electric Power One Riverside Plaza - 7th Floor Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677 [...]
22958 398 22_SV: Migrate tablespace13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Fri, 21 Aug 2009 14:58:05 +0200361_utf-8 There is no need to convert to go to V9 . You can start to use them on all new TS, and convert the ones that you think will benefit most.
Best regards hanne Fra: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] PÃ¥ vegne av Bell, Raymond Sendt: 21. august 2009 10:55 Til: DB2-L@WWW.IDUGDB2-L.ORG Emne: Re: [DB2-L] Migrate tablespace [...]
23357 804 57_Re: Antwort: Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog15_Patrick Hignett32_Patrick.Hignett@SSSWORLDWIDE.COM31_Fri, 21 Aug 2009 14:26:28 +0100460_iso-8859-1 Roger, I know that people in the States have some strange habits but in polite society it is normallyl frowned on to eat children or at least to admit to it on a public forum.
Regards
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq Sent: 20 August 2009 18:56 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Antwort: Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog [...]
24162 941 57_Re: Antwort: Re:[DB2-L] [z/OS v8 NFM] RTS and the Catalog10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 21 Aug 2009 10:02:02 -0400382_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...]
25104 574 49_[z/OS v8] NFM Catalogs with space filled VARCHARS12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 21 Aug 2009 09:06:36 -0500476_US-ASCII In all of my migrated subsystems SYSINDEXPART's varchar(128) columns seem to be space filled. And on ONE subsystem SYSFOREIGNKEYS has the same problem.
I only discovered this when running extracting a Baseline DDL and the tool abended.
If anyone has any ideas please let me know.
~~~ sidebar ~~~
Would some of you please run this query against a couple of your NFM subsystems and tell me if the NBR_BLANKS column has anything > ZERO. [...]
25679 626 45_Re: [v9 z/OS COMPAT] The New TEMP tablespace?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 21 Aug 2009 10:27:57 -0400416_us-ascii The error does not repeat, today. Yesterday, it turns out, we had some extremely poorly written queries running in that subsystem at approximately the time I had my problems. It may be that the DB07 tablespaces were being filled with intermediate return sets (whatever you correctly call them?) from these queries, and had no space for my stuff. In any case... "Nothing to see here. Move on. Move on." [...]
26306 292 38_Re: DB2 OSC and Plan_Table Alias setup13_Palko, George16_gpalko@OPERS.ORG31_Fri, 21 Aug 2009 15:33:00 +0100389_us-ascii Hi Steve,
We opened a ticket with IBM and had numerous discussions with the developers concerning this issue. Currently the only way around the problem is to bind the DSNAEXPL package with an OWNER which has INSTALL SYSADM authority.
I'm not going to get into the specifics of why that needs to be done. But, I'd be happy to discuss off-line. Good luck. George [...]
26599 226 37_Re: Null indicator variable in DCLGEN11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 21 Aug 2009 09:59:37 -0500771_iso-8859-1
Mukesh,
We have taken the same concept one step further by adding the 88-levels to ease coding.
Here is a COBOL example:
05 CONFIRM-DATE PIC X(10). 05 CONFIRM-DATE-NULL-CDE PIC S9(4) COMP. 88 CONFIRM-DATE-NULL-YES VALUE -1 -2. 88 CONFIRM-DATE-NULL-NO VALUE +0 -0.
Wish standard iBM DCLGENs would do this!
Thx
Suresh
> Date: Fri, 21 Aug 2009 09:22:34 +0200 > From: Marcel.van-Tunen@CORUSGROUP.COM > Subject: Re: [DB2-L] Null indicator variable in DCLGEN > To: DB2-L@WWW.IDUGDB2-L.ORG > > That's just the way a dclgen is created. We've written a cobol program > to convert. > It scans the declaration part to find lines [...]
26826 30 38_Re: DB2 OSC and Plan_Table Alias setup11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 21 Aug 2009 14:52:52 +0000290_utf-8 We are using a secondary auth id for each application group. So you have 1 set of tables for each application group that way you can controlle the number of tables created instead of creating for each user. Users of each application group have access to there secondary auth id. [...]
26857 602 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS13_arlen stovall22_arlenstovall@GMAIL.COM31_Fri, 21 Aug 2009 12:13:24 -0400516_ISO-8859-1 Mark, the results of your query on our V8 NFM system is
SSID TBNAME NBR_ROWS NBR_BLANKS ---------+---------+---------+---------+---------+--- DSNR SYSCOLAUTH 1614 0 DSNR SYSCOLUMNS 69065 0 DSNR SYSFIELDS 0 0 DSNR SYSFOREIGNKEYS 0 0 DSNR SYSINDEXES 2910 0 DSNR SYSINDEXPART 2910 2910 DSNR SYSKEYS 10702 0 DSNR SYSRELS 338 0 DSNR SYSSYNONYMS 1495 0 DSNR SYSTABAUTH 116678 0 DSNR SYSTABLEPART 2075 0 DSNR SYSTABLES 4353 0 DSNR SYSTABLESPACE 2075 0 DSNE610I NUMBER OF ROWS DISPLAYED IS 13 [...]
27460 185 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Fri, 21 Aug 2009 14:44:03 +0000552_utf-8 to all:
Thanks for the responses on this topic. It has been very useful as I have reviewed our processes.
I may already be using a (possibly) improved approach for measureing the MUPA (as compared to using several tables). Instead of using the multiple table approach, I simply use one (possibly one for each bufferpool if you wish to monitor the MUPA of multiple bufferpools). I perform the "SELECT" call on this "dummy" table to get a page into the bufferpool. Then, I execute the following command (about every 10 seconds): [...]
27646 59 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 21 Aug 2009 16:43:39 +0000572_utf-8 Mark, The result of your query on DB2 v9 NFM is: SSID TBNAME NBR_ROWS NBR_BLANKS ---------+---------+---------+---------+---------+---------+---------+ DSNR SYSCOLAUTH 210 5 DSNR SYSCOLUMNS 28418 1475 DSNR SYSFIELDS 0 0 DSNR SYSFOREIGNKEYS 4330 630 DSNR SYSINDEXES 3782 11 DSNR SYSINDEXPART 3789 95 DSNR SYSKEYS 6858 718 DSNR SYSRELS 3727 5 DSNR SYSSYNONYMS 86 0 DSNR SYSTABAUTH 24706 15 DSNR SYSTABLEPART 2955 0 DSNR SYSTABLES 3502 6 DSNR SYSTABLESPACE 2948 0 DSNE610I NUMBER OF ROWS DISPLAYED IS 13 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 [...]
27706 37 22_Re: Migrate tablespace13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Fri, 21 Aug 2009 17:16:07 +0000384_utf-8 One other option, which allows you to retain the original format tablespace until you know there are no issues with the migration to universal TS
1) Create a NEW ts as universal 2) Create table _NEW 3) Use cross-loader to populate the table_NEW 4) RENAME table to table_OLD 5) RENAME table to table_NEW (*check manual for restrictions on tables that can be renamed) [...]
27744 98 38_Re: DB2 OSC and Plan_Table Alias setup5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Fri, 21 Aug 2009 12:50:35 -0500492_utf-8 James,
I am not sure what is different between our two shops. We strictly use ALIAS names here (for explaining) and we are not encountering the problem that you describe. In fact, we basically have two copies of PLAN_TABLE and his cousins. We have one with a high level qualifier of DB2OSC (which was created by OSC when we tried to first use the product) and a second copy of this set of tables with many, many ALIASs on them so our developers can use the product. [...]
27843 567 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 21 Aug 2009 14:04:30 -0400421_UTF-8 Larry,
There are a few other things that might be considered.
I'm not sure why you might care about the UPA part aside from scientific curiosity. I've always been concerned only with the residency time, because it affects IO, and thus user response time.
If you have (busy) one object in a pool, and it's very large and very random, there will be a high IO rate, and a low residency time. [...]
28411 71 49_[Ad] IBM Training - Internet or NYC in Sept - Oct14_Larry Kintisch17_LKint@VERIZON.NET31_Fri, 21 Aug 2009 14:39:30 -0400411_us-ascii Hi Listers, [repeating in case you missed this]
I'll be teaching DB2 classes as a contract instructor for IBM, as I have for 18 years. If you are "new" to DB2 or are a manager are looking for background, or are recently thrust into the DB2 Database Administrator role these courses are great! All 3 are in midtown New York. For the CV721 class, it may be taken live, on-line from anywhere. [...]
28483 90 80_DB2 V8 z/OS: PK85889 & PK85856: DB2 utilities DFSORT usage to zIIP consequences?15_Chris Hoelscher21_choelscher@HUMANA.COM31_Fri, 21 Aug 2009 14:41:22 -0400519_US-ASCII good afternoon, we are interested in the effects of applying this fix earlier rather than later:
we are aware of the postential cost savings - we we are wondering if a piece of utility work is I/O intensive (or any other work that precludes z/IIP execution) - has the increased cycles needed to continually re-dispatch work in that process between GP and z/IIP processors increased the elapsed time of the process to the point that it has become noticable to those who notice those kind of things? [...]
28574 134 56_[z/OS V9] Optimization Service Center: First Cut Results14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 21 Aug 2009 15:59:36 -0400618_us-ascii Two quick things: 1) Installing Optimization Service Center can be a challenge. Using it in production requires setting your SQLID equal to the production schema name (or synonym owner, or whatever you use in prod to qualify your tables). 2) Our first test of the product looks like it's going to be a blazing success. A COGNOS generated query went from a couple of minutes under DB2V8 to doesn't-finish under DB2V9 COMPAT. Bad. However, we've been able to test the query, after running some complex runstats (one COLUMN and two one-column COLGROUPs), the query in test switched from one-column matching [...]
28709 276 60_Re: [z/OS V9] Optimization Service Center: First Cut Results13_David Simpson22_dsimpson@THEMISINC.COM31_Fri, 21 Aug 2009 16:20:23 -0400488_us-ascii To address #1 below you can now use the CURRENT SCHEMA which is in the "Query Context" in OSC. This allows you to set a default table qualifier that is different than your CURRENT SQLID which is set in the same place.
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Friday, August 21, 2009 3:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [z/OS V9] Optimization Service Center: First Cut Results [...]
28986 726 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 21 Aug 2009 15:40:39 -0500334_US-ASCII Arlen, Thank you ! This proves that there is a problem somewhere. If you query the VARCHAR(128) columns with LENGTH(col), col, you will see they are reported as length = 128, but they are not !
I have a ticket open with IBM and will keep this line open... Just done a DSN1SDMP for them, so we will resume Monday. [...]
29713 398 60_Re: [z/OS V9] Optimization Service Center: First Cut Results14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 21 Aug 2009 16:59:47 -0400492_us-ascii David,
I don't know where the "Query Context" is. Preferences seems to universal, and the various subsystem configs don't appear to show this choice (Radio button? Tab? Menu item?)
--Phil
________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson Sent: Friday, August 21, 2009 4:20 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS V9] Optimization Service Center: First Cut Results [...]
30112 481 60_Re: [z/OS V9] Optimization Service Center: First Cut Results13_David Simpson22_dsimpson@THEMISINC.COM31_Fri, 21 Aug 2009 17:31:10 -0400394_us-ascii The Query Context is at the "Project" level, not the subsystem. It's one of the tabs you get in a Query Tuning Project.
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Friday, August 21, 2009 4:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS V9] Optimization Service Center: First Cut Results [...]