1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2009, week 4
2 67 37_Re: Static SQL & Dynamic access check14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 22 Aug 2009 12:46:38 +1000565_US-ASCII Roll-your-own checking.
A program can: - use GETVARIABLE for SYSIBM.PACKAGE_SCHEMA, SYSIBM.PACKAGE_NAME and SYSIBM.PACKAGE_VERSION to identify its own package. - query SYSIBM.SYSTABAUTH to see the privileges needed for the static SQL in the package. - issue an READA on IFCIDS 0234 to find the secondary auth-ids that the current user has. (If you have QMF, it supplies a table function that returns this information.) - re-query SYSIBM.SYSTABAUTH to see if the end user or its secondary auth-ids have the required privileges to issue the SQL. [...]
70 357 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 21 Aug 2009 21:51:56 -0500448_us-ascii Remember to be carefull about index queries vs tablespace queries - especially if you didn't apply the ZPARM to compact varchar index entries.
Mike HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Vickers Sent: Friday, August 21, 2009 3:41 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS v8] NFM Catalogs with space filled VARCHARS [...]
428 56 73_AUTO: Andrew Butcher/HAV/SSE is out of the office. (returning 02/09/2009)0_38_Andrew.Butcher@SCOTTISH-SOUTHERN.CO.UK31_Sat, 22 Aug 2009 04:00:12 +0100387_us-ascii I am out of the office until 02/09/2009.
I will respond to your message when I return. In the meantime, if it is important then please forward your Email to my colleague Steve Spurgin or contact him on Ext. 44988.
Note: This is an automated response to your message "DB2-L Digest - 21 Aug 2009 to 22 Aug 2009 (#2009-242)" sent on 22/08/2009 02:00:03. [...]
485 391 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS13_David Simpson22_dsimpson@THEMISINC.COM31_Sat, 22 Aug 2009 11:19:46 -0400482_us-ascii Great point! Specifically, if zParm RETVLCFK is set to "YES" and index only access is used, the length will always be returned as the max since DB2 would have needed to go to the data to determine the length.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell Sent: Friday, August 21, 2009 9:52 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS v8] NFM Catalogs with space filled VARCHARS [...]
877 41 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Sat, 22 Aug 2009 11:13:57 -0500362_us-ascii If there is an interest, our HLS Describe will tell you what ZPARMS are set by reading and displaying the setting through Db2's Stored Procedure for that purpose. It is available for download from our website, www.hlstechnologies.com, for no charge. Just sign it and go to the download page.
Gerald Hodge HLS Technologies, Inc. 888-494-9019 [...]
919 75 84_Re: DB2 V8 z/OS: PK85889 & PK85856: DB2 utilities DFSORT usage to zIIP consequences?12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 23 Aug 2009 19:38:51 +0000519_utf-8 This function was announced last week, with z/OS V1.11, and the PTFs are only a few days old. As always with new function that is brand new, be prepared to test if you are putting them on very early. I have not seen performance measurements yet. Much of the IO is already asynchronous, using SRBs for prefetch, so this does not change dispatching considerations. With the current speed of disks, around 200 MB per second, the LOAD, REORG, and RUNSTATS utilities are not the usual ones mentioned as IO bound. [...]
995 147 66_Antwort: [DB2-L] [z/OS v8] NFM Catalogs with space filled VARCHARS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 24 Aug 2009 07:18:19 +0200514_ISO-8859-1 here's mine
---------+---------+---------+---------+------- SSID TBNAME NBR_ROWS NBR_BLANKS ---------+---------+---------+---------+------- DSNR SYSCOLAUTH 48 0 DSNR SYSCOLUMNS 108277 641 DSNR SYSFIELDS 0 0 DSNR SYSFOREIGNKEYS 324 0 DSNR SYSINDEXES 3276 31 DSNR SYSINDEXPART 4672 1108 DSNR SYSKEYS 10427 60 DSNR SYSRELS 190 0 DSNR SYSSYNONYMS 29 0 DSNR SYSTABAUTH 14328 32 DSNR SYSTABLEPART 10065 1049 DSNR SYSTABLES 5973 28 DSNR SYSTABLESPACE 2485 2 DSNE610I NUMBER OF ROWS DISPLAYED IS 13 [...]
1143 159 31_Akira Shibamiya's presentations10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 24 Aug 2009 04:10:13 -0400501_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 24th 2009
Here are several DB2 z/OS Performance presentations of Akira Shibamiya, available on IDUG website:
- DB2 9 for z/OS Select, Insert, Update, Delete SQL Application Performance by Akira Shibamiya
- DB2 9 for z/OS Performance Overview by Akira Shibamiya [...]
1303 53 39_Re: DB2 Cobol precompiler and ORACLENET12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Mon, 24 Aug 2009 08:55:26 +0000426_utf-8 Jeff,
I agree with Frank's comment. The future of Oracle mainframe products is quite uncertain, although I suppose they will continue to provide mainframe programming APIs / client functionality for a few more years than their server products.
Using DB2 (newer) coprocessors instead of pre-compilers might also limit your possibilities of adapting the source code with self-written pre- precompilers. [...]
1357 35 55_RC/Merge to Clone Objects from One Subsystem to Another13_Roy Proudlove24_royproudlove@HBOSPLC.COM31_Mon, 24 Aug 2009 10:53:00 +0000565_utf-8 Hello, I'm looking for a very fast way to clone objects from one Subsystem to another. Catch is that source is V7 and target is V8. In total it will be approx 1Tb of data and service downtime must be kept to a minimum, or better still non at all. We are primarily a BMC shop and have considerable knowledge of their tools. I'm looking at CA RC/Merge as a possibility. Exact object replication (e.g. internal object identifiers) and flash copy to replicate data looks promising. Anyone out there with real world use of this product in a similar scenario? [...]
1393 57 59_Re: RC/Merge to Clone Objects from One Subsystem to Another15_Lizette Koehler23_starsoul@MINDSPRING.COM31_Mon, 24 Aug 2009 07:46:42 -0400316_utf-8 Could you provide info on your hardware environment? That also needs to be taken into account here.
IBM DS8600? EMC DMX4500? What vendor software - IBM Flashcopy EMC Timfinder - and so on. To me cloning is almost like DR so you need to bring into play the hardware and software available to do it. [...]
1451 886 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Mon, 24 Aug 2009 08:00:04 -0500361_US-ASCII Mike,You hit the proverbial nail right on the noggin !
I have RETVLCFK = YES, changed it to NO and the query returned 0 rows that have blank filled varchars.
HOWEVER.....
This does not seem right that when you tell DB2 to retrieve VARCHAR data from the index, the length of the column is always the maximum (varchar) length ? [...]
2338 27 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Mon, 24 Aug 2009 13:13:35 +0000610_utf-8 Joel, Could you help me out a bit by defining the difference between MUPA and "residency time". I can tell that you consider them as two different things but I have been confusing the two.
Larry
_____________________________________________________________________
* 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." _____________________________________________________________________
2366 1013 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Mon, 24 Aug 2009 09:02:23 -0500416_US-ASCII OK, problem resolved by IBM - now I am going to send my vendor a really nasty thank you for not documenting the migration correctly.
There was a vendor-user-defined index on the catalog which if not specified, as it was not, defaulted to PADDED on the varchars. The query was doing an index scan and everything was padded - all makes sense now. Changed them to NOT PADDED and no-more-problems ! [...]
3380 454 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS13_David Simpson22_dsimpson@THEMISINC.COM31_Mon, 24 Aug 2009 10:15:11 -0400397_iso-8859-1 When you set RETVLCFK to YES, you are saying that the performance benefit of index-only access is more important to you than always getting the right answer on the length of a VARCHAR column. Probably should be set to NO unless everyone understands the implications.
--------------------
Mike,You hit the proverbial nail right on the noggin ! [...]
3835 102 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 24 Aug 2009 10:29:21 -0400513_UTF-8 Hi Larry,
It's sort of a glass 1/2 full vs. 1/2 empty approach.
The MUPA is the amount of time it takes for a page that is seldom (to never) re-referenced to fall off an LRU queue and disappear from the pool.
I'm looking at the other side, how long pages that are re-referenced in relatively short timeframes will still be in the pool when they are re-referenced. There are some pages, like an index root page, or a spacemap page, that may live in a pool forever in a busy system. [...]
3938 33 59_Re: RC/Merge to Clone Objects from One Subsystem to Another13_Roy Proudlove24_royproudlove@HBOSPLC.COM31_Mon, 24 Aug 2009 14:03:06 +0000539_utf-8 Hi Lizette. Source is Z9 z/OS 1.9 DB2 V7 - non data sharing. DASD is IBM DS8000. Target will be Z10 z/OS 1.11 DB2 V8 NFM - Data sharing. DASD is IBM DS8000. Obvious methods are Unload / Load, Log Master, DASD flash Copy etc. We have licences for BMC and CA products. We don't have any experience of using the CA RC/Merge product and I'm a little wary that it manipulates some of the DB2 internals (e.g. OBIDs). We don't have scope to use anything beyond our current product set. The CA product looks like a viable solution. I'm [...]
3972 446 53_Re: [z/OS v8] NFM Catalogs with space filled VARCHARS9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 24 Aug 2009 09:46:58 -0500355_US-ASCII More ZPARM's - there is a entry that controls whether DB2 will return varchar data from an index that is padded. The normal is to only use the index for the where clause and force data access to get the correct length. This result is working as coded and zparm specified. It isn't a hidden parm so it should be listed in the install manual. [...]
4419 65 59_Re: RC/Merge to Clone Objects from One Subsystem to Another12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Mon, 24 Aug 2009 10:28:59 -0600374_us-ascii Roy, if you have BMC products, you could use CHANGE MANAGER with UNLOAD PLUS/LOADPLUS specifying FORMAT BMCLOAD/FORMAT BMCUNLOAD - that will make the process faster. Log Master will pull off SQL UPDATES/INSERTS/DELETES from the source system Log and apply them to the target via high speed apply processing - no outage for either the log extract or the apply. [...]
4485 258 59_Re: RC/Merge to Clone Objects from One Subsystem to Another11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Mon, 24 Aug 2009 17:33:43 +0100568_US-ASCII Hi Roy,
We used RC/Merger a few years ago to move from a V7 non-data sharing subsystem to a V7 data sharing subsystem.
I would recommend this method of moving data (we are about to start another large migration like this) although depends on your exact scenario of course. I recall quite a lot of our implementation window was taken by the target object creation rather than the dataset manipulation so this is something to bear in mind if you have a large number of objects to move (there is an option to do this as a separate phase which [...]
4744 55 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Mon, 24 Aug 2009 17:27:37 +0000538_utf-8 Thanks Joel, That helps me to know the different terminology.
Here is my reason for being interested in the MUPA. It appears that I can measure the MUPA without requiring many system resources. Also, I can measure the MUPA at least once per hour without much of a problem. In a world where my traffic to DB2 would not ever vary, it would be easier to keep the bufferpools tuned. As I monitor the MUPA, I can determine if there are times when a bufferpool has a very low MUPA. I certainly do not want the MUPA to be less [...]
4800 35 27_Re: -952 DB2 Connect V9.5 ?12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Mon, 24 Aug 2009 17:40:08 +0000355_utf-8 So far, the IBM fix for -952s has worked for us...... "Problem Resolution: .NEt does not support QUERYTMEOUTINTERVAL CLI setting, he needs to use CommandTimeout=0 of the DB2Command object."
I believe this is a statement level command. I.e. specify before each SQL. We set ours to 180 (3 mins.). Queries will be reviewed for performance. [...]
4836 42 52_Agenda for upcoming seminar on Houston User web site13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 24 Aug 2009 12:53:45 -0500675_ISO-8859-1 The agenda for the IBM no-charge seminar coming to Houston on September 15 is now posted on the Houston Area DB2 User Group's web site at http://db2now.com/hadug/ .
Other information is available in my blog at http://it.toolbox.com/blogs/db2zos/cool-news-ibm-is-offering-a-nocharge-customer-seminar-in-houston-33427
Registration details and links are on BOTH pages.
You must register for this seminar.
This is a great opportunity to listen to a DB2 Program Manager from Silicon Valley Lab, plus others, speak about DB2 for z/OS. Please try to attend. I would like to show the Lab that we need more DB2 events scheduled in Houston. [...]
4879 37 78_IBM Webcast - Simplify DB2 Administration and Development with IBM Data Studio19_McBride, Michael C.31_michael.mcbride@DOT.STATE.FL.US31_Mon, 24 Aug 2009 18:01:38 +0000354_utf-8 Simplify DB2 Administration and Development with IBM Data Studio Thursday 27 Aug 2009, 11:30 AM Eastern
Attend this webinar and find out how database developers and DBAs can get their jobs done more efficiently and with less risk using the no-charge capabilities in Data Studio 2.2, and the value-added capabilities in Optim solutions. [...]
4917 146 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.14_Larry Kintisch17_LKint@VERIZON.NET31_Mon, 24 Aug 2009 14:57:15 -0400489_us-ascii Hi Joel and Larry and others,
When the issue of MUPA [max unreferenced pool age ] is raised in the IBM course CV960 [was CF961] "Application Performance & Tuning", it is in the context of "massive batch jobs".
For example, while printing ten thousand invoices, it asks "if I re-reference a certain SKU row for one invoice, and later I re-reference that same SKU for another, will that same SKU row of the 800,000 still be in the B.P. since its last reference?" [...]
5064 316 43_Re: Performance issue with a delete process14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 20 Aug 2009 10:04:54 -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". [...]
5381 71 49_[Ad] IBM Training - Internet or NYC in Sept - Oct14_Larry Kintisch17_LKint@VERIZON.NET31_Fri, 21 Aug 2009 14:40:53 -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. [...]
5453 220 67_Central Ohio DB2 Users Group (CODUG) September Meeting Announcement12_Theresa Ryan27_Theresa.Ryan@HUNTINGTON.COM31_Mon, 24 Aug 2009 16:48:14 -0400345_UTF-8 Central Ohio DB2 Users Group resuming after a 2 year hiatus
The next meeting of CODUG will be held: Wednesday, September 16, 2009 1:00 – 4:30PM
at: Huntington Business Center (near Easton) 7 Easton Oval, Columbus OH 43219 2nd floor - Training Rooms 4 & 5
Attendance and Parking are free! (see directions below) [...]
5674 191 20_Re: DB2 intro course17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Mon, 24 Aug 2009 23:50:07 +0100410_utf-8 Righty ho ladies and gentlemen, I have now sent this to the people who requested it – please note: it was written in Y2K AND it’s DB2 for Dummies, so don’t shoot the piano player...
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 [...]
5866 192 33_DB2 9 for z/OS migration workshop8_Lo, Mary17_Mary.Lo@TGSLC.ORG31_Mon, 24 Aug 2009 17:53:33 -0500369_us-ascii Heart of Texas DB2 Users Group (HOTDUG) is presenting a free DB2 9 for z/OS migration workshop by IBM. It is for application developers, DBAs, system programmers, and managers to learn more about DB2 9. The morning has information for all parties; the afternoon will focus on migration thus a better fit for DBAs, system programmers and project managers. [...]
6059 270 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 24 Aug 2009 19:45:36 -0400581_UTF-8 Larry,
It appears that you are using the reduction of MUPA to show the pool stress has increased, and thus the IO rate/second has increased...??
If so, why not just track the IO rate/second? It's readily available from systems statistics records without going through your sampling to determine MUPA.
It would be interesting (if you have time) to see a mapping of MUPA to IO rate/second and getpages/second over some period of time during one of your busy periods. Maybe this can be another useful analysis technique to improve overall performance. [...]
6330 30 59_Re: RC/Merge to Clone Objects from One Subsystem to Another13_Roy Proudlove24_royproudlove@HBOSPLC.COM31_Tue, 25 Aug 2009 09:32:24 +0000460_utf-8 Thanks for the info Jim. In this case it's a copy rather than move operation. I guess I share the same 'gut' concerns that you have going from V7 to V8 using a tool that we have no real experince of. Maybe nothing to worry about, but... If this route progresses then if you don't mind then I'll certainly keep in touch. BTW we're also considering a BMC solution using Image Copy data and Log Master.to apply subsequent updates on the target system. [...]
6361 60 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.11_Mike Bracey22_mike_bracey@UK.IBM.COM31_Tue, 25 Aug 2009 10:26:43 +0000535_UTF-8 Hello Larry I have had an interest in this topic for some time and have developed a simple method for measuring residency time which I take to mean the average time that a page stays in a buffer pool. Of course we all know that the man with his head in the oven and feet in the fridge is "on average" quite comfortable so I treat my measure more as an indicator of stress rather than a definitive measure. The equations I use are: System residency time (seconds) = buffer pool size / total pages read per second Random page [...]
6422 100 59_Re: RC/Merge to Clone Objects from One Subsystem to Another11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Tue, 25 Aug 2009 12:13:09 +0100684_US-ASCII No problem Roy.
One further thought - are your CCSIDs consistent between your V7 and V8 subsystems ?
Jim.
Jim Addison ISOS Enterprise Middleware Standard Life Employee Services Limited http://www.standardlife.com
Tel: +44 (0)131 245 3206
This e-mail is confidential and, if you are not the intended recipient, please return it to us and do not retain or disclose it. We filter and monitor e-mails in order to protect our system and the integrity, confidentiality and availability of e-mails. We cannot guarantee that e-mails are risk free and are not responsible for any related damage or unauthorised alteration of e-mails by [...]
6523 50 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Tue, 25 Aug 2009 12:19:21 +0000373_utf-8 thanks Joel and everyone who responded.
I will do some further research with the I/O numbers. At the current time, we run a "-DIS BUFFERPOOL(*) DETAIL(INTERVAL)" every 5 minutes and collect the various output fields and then place them in a DB2 table for further analysis. This then can easily be used for looking at I/O numbers in every 5 minute period. [...]
6574 46 59_Re: RC/Merge to Clone Objects from One Subsystem to Another16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 25 Aug 2009 08:51:03 -0400510_UTF-8 Greetings - we've had several requests from DB2 sites asking going from V7 to V8 and V8 to DB2 9 (meaning using RC/Merger to COPY or MOVE the pagesets including all the internals in the pagesets when ID's don't match). This is possible - because you're not required to do anything to your objects when upgrading DB2 from V7 to V8 - everything will continue to run. DB2 doesn't turn the tablespaces into RRF instead of BRF, the objects are not automatically converted from ICP to TCP, so . . . . . . [...]
6621 107 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 25 Aug 2009 09:29:15 -0400473_UTF-8 Hi Larry,
What's your setting for cutting DB2 statistics records? Current recommendation is 1 minute (1440 records per day). Easily combined into the 15 min intervals I like to start with, while allowing 1 minute problem analysis if the need occurs.
Your comment about RLST in your system is quite interesting, since I haven't seen this level of usage in any systems. I don't know if any of the sites that send me data use the predictive governor. [...]
6729 51 14_VOLATILE table16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG31_Tue, 25 Aug 2009 09:52:58 -0400
6781 34 38_Re: DB2 OSC and Plan_Table Alias setup12_Jim Lazowski25_jim.lazowski@NAVISTAR.COM31_Tue, 25 Aug 2009 13:59:37 +0000338_utf-8 Thank you for all your input.
So far the only thing I haven't tried is the Seondary authid's, but I'm not too hopeful because I can't add SYSADM to everyone that wants to use OSC.
The set current sqlid='SYSADM' is executed on connection to the DB. I don't understand why, but that's what I'm seeing in the trace. [...]
6816 267 26_AW: [DB2-L] VOLATILE table35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 25 Aug 2009 17:00:49 +0200510_iso-8859-1 Hi
I have played around with volatile tables some time ago. What I have experienced is, that although an index would make sense, the optimizer didn't pick it up, because list prefetch is disabled for volatile tables. Therefore for updates or deletes you can get a tablespace space scan although you specify part of the key. So, because list prefetch is not possible, multi-index access is also no option. May be, there are other restrictions. I think Terry or Pat can tell you the best. [...]
7084 74 38_Re: DB2 OSC and Plan_Table Alias setup11_Mike Turner19_mike.turner@GMX.COM31_Tue, 25 Aug 2009 16:28:05 +0100348_utf-8 Hi Jim
I think the set current sqlid='SYSADM' is a red herring. If I remember correctly, it is just used to test if SYSADM authority is available. If you grant select, insert, update, and delete privileges on the plan tables to which the aliases point to either a primary or secondary ID of the user, I think things will work OK. [...]
7159 245 18_Re: VOLATILE table14_Amsden, John W19_John.Amsden@LFG.COM31_Tue, 25 Aug 2009 11:50:49 -0400330_us-ascii We've used the "volatile" designation on several tables that are of the "work" type. I.e. loaded or inserted into, then used, then deleted or reloaded. We do not execute the RUNSTATS utility on these tables, or, if we do, we don't worry about the number of rows in them. We've had very good luck with this strategy. [...]
7405 142 42_db2 v8 z/os jdbc driver schema definitions12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Tue, 25 Aug 2009 09:06:14 -0700341_us-ascii Hi All- we have a client that is using a tool called razorsql to connect to db2 z/os v8. They are unable to define the specific schema's they want to look at so every time they connect the tool pulls all schemas from db2. Is anyone familiar with this tool? Here's some additional information from our middleware support group: [...]
7548 34 18_Re: VOLATILE table13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Tue, 25 Aug 2009 16:44:47 +0000361_utf-8 I have a few "transactional" tables which are set-up as VOLATILE, the rows can vay from 0 to many thousands in a 24 hr period. So far I have see no performance issues.
As others have mentioned, list prefetch may be disabled. So I tend to compensate, by ensuring good indexes (and good SQL), to avoid any potential index scan / TS scan issues. [...]
7583 63 38_Re: DB2 OSC and Plan_Table Alias setup13_Palko, George16_gpalko@OPERS.ORG31_Tue, 25 Aug 2009 18:57:30 +0100517_utf-8 Jim, Did you bind the DSNAEXPL package with OWNER as a <> not <> id. The Primary id also has to have INSTALL SYSADM authority. We had the exact same problem that you describe. IBM specifically told us to do this to resolve the problem. Since making this change we are real pleased with the product. Each subsystem has a single set of OSC tables that ALL the developers share. We also created triggers to automatically prune the tables which in a development region can grow very large. [...]
7647 55 46_Re: db2 v8 z/os jdbc driver schema definitions12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Tue, 25 Aug 2009 20:22:29 +0000552_UTF-8 Shery,
you could try and have a "DB2JccConfiguration.properties" file on your classpath which allows you to define a variety of basic settings. Like e.g.
db2.jcc.override.currentSchema=[your_schema_name_here_in_uppercase]
More details on this under http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/cjvcfgpr.htm (doc is DB2 9, but you can easily use this more recent driver for DB2 v8 z/OS server).
BUT(!): This setting will only influence unqualified queries. [...]
7703 56 4_Test10_Mark Labby21_mlabby.idug@GMAIL.COM31_Tue, 25 Aug 2009 22:44:14 -0400688_US-ASCII The IDUG.org e-mail server is being configured with new SPAM blocking software, so this is to see if posts get through...
_____________________________________________________________________
* 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." _____________________________________________________________________
7760 98 37_JDBC Driver DB2 V9 for z/OS + JBOSS 47_Xpto Br16_lppa66@GMAIL.COM31_Wed, 26 Aug 2009 09:52:23 +0000564_utf-8 Hi there,
We are using AIX 5.3 + JBOSS 4 + JBOSS Portal 2.6.5. Our client application is JAVA in AIX 5.3 and access a DB2 for z/OS database by JDBC Driver IBM Type 4 (DB2 V9).
We have the following problem:
ATSSDPRF.COM_NUM=0000001 AND TRM_ROLCOD=00048) OPTIMIZE FOR 1 ROW [I]-2009.07.30 15:12:42,183 :Connection:Closed:true [E]-2009.07.30 15:12:42,187 org.jboss.util.NestedSQLException: Error; - nested throwable: (java.lang.StringIndexOutOfBoundsException)-:-Error; - nested throwable: (java.lang.StringIndexOutOfBoundsException) [...]
7859 288 18_COBOL SQL question15_Patrick Hignett32_Patrick.Hignett@SSSWORLDWIDE.COM31_Wed, 26 Aug 2009 11:18:21 +0100315_us-ascii
I've a batch cobol program which uses DB2 to get some data.
It opens a cursor, then fetches rows. Each row has values used to key into other tables / do other processing so that totals can be accumulated. On break of certain values in the original cursor, these totals are written out. [...]
8148 392 30_AW: [DB2-L] COBOL SQL question35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Wed, 26 Aug 2009 12:53:25 +0200461_iso-8859-1 Hi Patrick
This is normal behaviour and coding. We use that all the time. A routine calls another one, which fetches some rows and returns back to the caller. Then the routine is called again and continues to fetch. The cursor will not be closed. Cursor position is maintained by DB2 outside the program in a controlblock called CUB (cursor user block). You loose your position, when you commit and the cursor was not declared WITH HOLD. [...]
8541 343 76_Immediate Volunteer Opportunity at IDUG 2009 - Europe: Moderate a Session!!!17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 26 Aug 2009 12:26:12 +0100476_us-ascii Go further...
with your IDUG 2009 Europe Conference experience! Moderate a Session!
Interested in volunteering to moderate a session at this year's conference?
Session moderators are essential to the success of each and every IDUG conference and you can help. To become a moderator, after registering to attend the conference, simply go to the Personal Itinerary Builder on the Conference Scheduler page and login at the bottom of the page. [...]
8885 70 22_Re: COBOL SQL question11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Wed, 26 Aug 2009 13:32:23 +0000376_utf-8 Hi Patrick, There are multiple ways you can achieve this depends on how are you passing data or the best way is through store procedure.
Option 1 =========== This is the best option which i would do if i can use stored procedure. Put all the logic in stored procedure and delare cursor as WITH RETURN TO CALLER. Fow more information on this refer below URL: [...]
8956 63 46_Re: db2 v8 z/os jdbc driver schema definitions12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 26 Aug 2009 10:18:11 -0700565_utf-8 HI Peter! Thanks for your response. We've done some more investigation and have found that we have views created on the db2 system catalog tables that are used by this driver. Upon further investigation all of the views are defined with a schema of SYSCAT and some of them are joins coded with columns that don't have indexes to support the joins. No wonder the response time is awful! I'm looking at cleaning them up- but can't find any reference to where the ddl originates. My systems programmer doesn't have it as it doesn't appear to be part of any [...]
9020 34 46_Re: db2 v8 z/os jdbc driver schema definitions12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 26 Aug 2009 10:52:01 -0700609_utf-8 Trying this again- don't know if the first one went through
-----Original Message----- From: Hepp Shery C Sent: Wednesday, August 26, 2009 10:18 AM To: 'DB2 Database Discussion list at IDUG' Subject: RE: [DB2-L] db2 v8 z/os jdbc driver schema definitions
HI Peter! Thanks for your response. We've done some more investigation and have found that we have views created on the db2 system catalog tables that are used by this driver. Upon further investigation all of the views are defined with a schema of SYSCAT and some of them are joins coded with columns that don't have indexes to [...]
9055 39 46_Re: db2 v8 z/os jdbc driver schema definitions11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Wed, 26 Aug 2009 18:14:27 +0000553_utf-8 Sherry, SYSCAT views are used by LUW not z/OS. You mentioned that you have some views with SYSCAT created in 2001. How many view you have because in DB2 v4 there were some views crteated with SYSCAT for SYSIBM.SYSROUTINES. I don't remember all but there were some views created.
Another reason it might be that if someone installed some product that is JDBC driver based and there was wrong coniguration settings that time that you are connecting to DB2 z/OS or DB2 LUW. And that might have created some view which needed for DB2 LUW. [...]
9095 285 38_Re: DB2 OSC and Plan_Table Alias setup12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Wed, 26 Aug 2009 14:21:09 -0500497_US-ASCII George, Could you expand on the triggers and maybe include an example. If you think this is off topic, please send off-line.
Appreciatively,
"Palko, George" Sent by: DB2 Data Base Discussion List 08/25/2009 01:12 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: [DB2-L] DB2 OSC and Plan_Table Alias setup [...]
9381 54 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Wed, 26 Aug 2009 19:42:31 +0000573_utf-8 Joel (and all), We do use PAGEFIX. Also, on the FIFO bufferpools, they are big enough to store the entire object.
I actually have two FIFO bufferpools. One that I use for the RLST only (so I can records stats and give additional information to IBM) and one for several of our other objects that are small and have large GP counts. At the current time, the RLST bufferpool gets about 25 million getpages per day and the other (more general use FIFO bufferpool) gets about 250 million per day. Since the bufferpools are large enough to completely store the [...]
9436 364 46_Re: COBOL SQL question [maybe off topic a bit]12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Wed, 26 Aug 2009 14:43:56 -0500285_ISO-8859-1 Walter, ATROSS (At the risk of sounding stupid) I have been out of programming for some time now and I had to confer with one of our senior DB2 programmers. We cannot figure out how to achieve this, so could you explain this a little more please. Thanks, Mark Vickers. [...]
9801 42 46_Re: db2 v8 z/os jdbc driver schema definitions12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Wed, 26 Aug 2009 21:52:52 +0000502_UTF-8 Shery,
I fully agree with Mukesh. SYSCAT is a schema used in LUW for views on the catalog tables.
No idea how they got created, but they are unusual on z/OS systems. Maybe the former DBA was an LUW guy and felt more comfortable having his standard view onto the catalog?
I'd consider them obsolete on DB2 z/OS. Can you generate the DDL from the Admin Tool or revoke access from these views to see if any SQLCODE -551 occur which would tell you somebody still uses them? [...]
9844 57 41_Re: JDBC Driver DB2 V9 for z/OS + JBOSS 412_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Wed, 26 Aug 2009 22:05:42 +0000346_UTF-8 Hi Br,
I don't know how to trace JBOSS, but it's easy to trace the JDBC driver (which actually is compatible with JBOSS).
To activate trace on the JDBC driver, simply add the respective parameters to your connect string (which usually resides in a properties file). The connect string will then look something like this: [...]
9902 47 46_Re: db2 v8 z/os jdbc driver schema definitions12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 26 Aug 2009 15:23:59 -0700535_utf-8 Hi Mukesh/Peter- there are 56 views created with the SYSCAT schema. What we have done is renamed the views in one test system and tried to use the tools that are accessing the type 4 driver. What we found is that they work fine without the SYSCAT views. (yeah) Given that we plan to drop them from our DB2 production systems. Interesting enough it looks like the driver does want to access a view called syscat.schemata, but if it encounters a -204 then it uses the db2 catalog directly. Which is what we want in this case. [...]
9950 111 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, 26 Aug 2009 20:48:19 -0400389_UTF-8 Hi Larry,
If you have tons of memory it isn't hurting anything to pagefix the FIFO pools. But, if they have little to no IO as you say, it does not buy you anything. It only helps to pagefix pools with high IO rates/second.
We've seen numbers from IBM, that pagefix saves about 8% CPU. I've seen this at a couple of clients, and a bit more at one specific client. [...]
10062 34 46_Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 27 Aug 2009 11:50:52 +0300327_US-ASCII Cobol pgm does Multi Row Fetch from T1 and does Multirow Insert to T2 w/o any problem.
The question, Is it possible to use Positioned Delete in this FETCH / INSERT loop references to ROWSET Positioned Cursor ? I tried to define the cursor with FOR UPDATE OF and DELETE from where current of receives -510. [...]
10097 73 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 Aug 2009 10:59:53 +0100361_iso-8859-1 Hi Cuneyt
You should be able to DELETE ... WHERE CURRENT OF, but it will delete ALL rows in the current rowset (i.e. all rows returned by the last FETCH). The -510 must be because the cursor is read-only, maybe it includes a join or an ORDER BY?
To delete only one of the rows in the rowset, add FOR ROW n OF ROWSET to the delete. [...]
10171 98 50_Re: Multi Row Fetch / Insert and Positioned Delete14_Peter Backlund21_BacklundDB2@TELIA.COM31_Thu, 27 Aug 2009 12:05:59 +0200
10270 45 60_(Fwd) Re: [DB2-L] db2 v8 z/os jdbc driver schema definitions14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 27 Aug 2009 20:36:52 +1000329_US-ASCII IIRC early versions of ODBC drivers used these views. After DB2 for z/OS got stored procedures, the IBM drivers switched to using SPs. You might still find 3rd party ODBC drivers that use them.
I also recall discussion on DB2-L back in the american.edu days. But all messages from then got lost in the move. [...]
10316 544 46_Re: COBOL SQL question [maybe off topic a bit]11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Thu, 27 Aug 2009 12:16:10 +0100435_ISO-8859-1 Hi Mark,
You need to test whether you have come into your program for the first time or not - probably use of some working storage flag is best but you could fetch from your cursor and use a -501 to indicate first time (then open), there are other techniques. Then you can go back to the caller and come back in and fetch the next row (so long as you have not closed the cursor, commited without with hold etc). [...]
10861 146 50_Re: Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 27 Aug 2009 15:24:29 +0300749_US-ASCII Thank you Peter and Mike.
The SQL does not contain any sort (DISTINCT, ORDER BY, GROUP BY). does not access to view. No join package is bound with CURRENTDATA(YES), the cursor is not ambiguous.
Still have -510.
EXEC SQL DECLARE C1 CURSOR WITH ROWSET POSITIONING WITH HOLD FOR SELECT ARF_IDFR, ISBT_WS_ID_ISB, ISBT_HOST_TS_ISB, ISBT_OPERATION_DATE, ISBT_OP_BRANCH, ISBT_SEQUENCE_NUM, ISBT_TRCODE, ISBT_FNCTN_CODE, ISBT_TYPE, ISBT_USERID_ISB, ISBT_EV_IDFR, ISBT_RSVTYPE, ISBT_REVERSAL_IND, ISBT_RIFS_IDFR_RPTD_TEXT, ISBT_TOKENS, CRUD_VALUE, ISBT_LOCAL_TS, ISBT_HOST_TS, ISBT_USER_ID, ISBT_WS_ID, RULESYSTEMID, LDBID FROM CRBFID0.ISB_TRANS WHERE ISBT_OPERATION_DATE < CURRENT DATE AND LDBID = 9310 FOR UPDATE [...]
11008 169 50_Re: Multi Row Fetch / Insert and Positioned Delete9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 27 Aug 2009 08:25:24 -0500497_us-ascii Curious - might be time to open a PMR. The manual mentions both remote tables and parallel access (degree any) as potential problems. The degree any would require partitioned table and degree any bind.
Mike
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (Gmail) Sent: Thursday, August 27, 2009 7:24 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete [...]
11178 178 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 Aug 2009 14:29:53 +0100625_iso-8859-1 Hi Cuneyt
Any chance that the access path is using parallelism? That can cause a -510.
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: "Cuneyt Goksu (Gmail)" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 27, 2009 1:24 PM Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete [...]
11357 1168 38_Re: DB2 OSC and Plan_Table Alias setup13_Palko, George16_gpalko@OPERS.ORG31_Thu, 27 Aug 2009 14:34:34 +0100346_us-ascii Hi Jim,
The triggers are needed to remove the obsolete rows from the OSC tables because there is not a way to manage the accumulation of data via the product itself. If the obsolete rows are not removed then in a development regions, where a large number of binds occur, will grow very large, especially the DSN_QUERY_TABLE. [...]
12526 188 58_AW: [DB2-L] Multi Row Fetch / Insert and Positioned Delete35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 27 Aug 2009 16:12:01 +0200502_iso-8859-1 Hi
If that all is true, then according to the message and codes manual:
DB2 has employed parallelism to execute the SELECT statement associated with the cursor named in a DELETE WHERE CURRENT OF cursor statement
Did You use parellelism? Did you bind with DEGREE(ANY)
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf mailto:walter.janissen@itergo.com [...]
12715 220 50_Re: Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 27 Aug 2009 17:49:29 +0300643_us-ascii Mike,
The package that keeps this SQL is bound with DEGREE(1)
Regards, Cuneyt
> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner > Sent: Thursday, August 27, 2009 4:30 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete > > > Hi Cuneyt > > Any chance that the access path is using parallelism? That > can cause a -510. > > 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 [...]
12936 204 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 Aug 2009 15:54:46 +0100637_iso-8859-1 Hi Mike
Sorry to quibble, but I believe that parallelism has been possible on non-partitioned tables since V6.
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 Bell" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 27, 2009 2:25 PM Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete [...]
13141 198 50_Re: Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Thu, 27 Aug 2009 18:27:00 +0300629_us-ascii I requested to open a PMR and will inform the list later when I receive something from the support.
Thanks.
> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell > Sent: Thursday, August 27, 2009 4:25 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete > > > Curious - might be time to open a PMR. The manual mentions > both remote tables and parallel access (degree any) as > potential problems. The degree any would require partitioned > table and degree any bind. [...]
13340 253 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 Aug 2009 16:34:22 +0100632_iso-8859-1 Hi Cuneyt
Then, unless it is a system-maintained Materialized Query table, I am out of ideas.
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: "Cuneyt Goksu (Gmail)" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 27, 2009 3:49 PM Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete [...]
13594 44 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Thu, 27 Aug 2009 16:23:47 +0000527_utf-8 Joel,
I have a bit of a disconnect with what you are saying regarding FIFO vs LRU. It looks like your advice is to use FIFO for bufferpools with a high I/O rate. My understanding is that the FIFO chain is updated each time you do an I/O while the LRU chain is updated each time you do a getpage. My application of this understanding would be to use FIFO when you have a high getpage and a low I/O within a bufferpool (of course, you would also be concerned that the change from LRU to FIFO would not increase [...]
13639 156 50_Re: Multi Row Fetch / Insert and Positioned Delete12_Cuneyt Goksu22_cuneyt.goksu@GMAIL.COM31_Thu, 27 Aug 2009 16:26:22 +0000486_Windows-1252 It's a standart normal base table.
Let's wait and see what the support team will say.
AVEA BlackBerry® Servisi ile gönderildi.
-----Original Message----- From: Mike Turner
Date: Thu, 27 Aug 2009 16:34:22 To: Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete
Hi Cuneyt
Then, unless it is a system-maintained Materialized Query table, I am out of ideas. [...]
13796 35 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 Aug 2009 17:57:32 +0100491_iso-8859-1 Hi Cuneyt
I did an APAR search for SQLCODE510 and got some hits. the two most recent that are related to multi-row fetch are PK49057 (PTF UK29639) from October 2007, and PK41062 (UK23921) from April 2007.
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. [...]
13832 51 28_Question on XML and XMLQuery12_David Booher22_david.booher@QUEST.COM31_Thu, 27 Aug 2009 10:40:42 -0700637_us-ascii I've been messing around with XML and the XMLQUERY statement. Given the sample XML:
Kathy Smith 5 Rosewood Torontoe Ontario M6W 1E6 416-555-1358
The following query returns everything in customerinfo (from the to :
SELECT XMLQUERY ('declare default element namespace "http://posample.org";$INFO/customerinfo[@Cid]' ) FROM AIX95QA3.CUSTOMER; [...]
13884 95 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 27 Aug 2009 13:47:46 -0400352_UTF-8 Hi Larry,
Maybe I wasn't clear or had a brain blip. I'm not sure where I'm confusing the issue.
My FIFO recommendation is two ways - one the way you have it, for objects that are resident, low/no IO - another would be for objects that are very large, very random, that will always do an IO and rarely ever re-reference a page. [...]
13980 147 57_Re: DB2-L Digest - 14 May 2009 to 15 May 2009 (#2009-143)0_24_Philip_Jacobs@BCBSIL.COM31_Thu, 27 Aug 2009 13:00:24 -0500784_iso-8859-1 An exert from DB2-L. I know it's too late to change your strategy. Thought you might find this interesting. I'm not endorsing the suggestion, just passing it along.
------------------------------
Date: Thu, 14 May 2009 13:53:54 -0500 From: "Degtyarev, Lucy" Subject: Re: revoke sysadm impact
--_000_DFB1B75D1983AF4E9E351B68092761F602053DB560crexmail01usa_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Renzo, I actually was able to REVOKE SYSADM authority without suffering the cascad= ing effect. The method is as follows: 1.Reassemble the ZPARM, for the DB2 subsystem , to allow SYSADM ID , you wi= sh to revoke , as one of the INSTALL SYSADM ID's ( keep in [...]
14128 36 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Thu, 27 Aug 2009 18:26:46 +0000499_utf-8 OK. I think that I am tracking but still have a question.
From what you are saying, when I have the PAGEFIX(NO) attribute on a "high getpage but low I/O" bufferpool, the O/S LRU tracking would not be busy (on behalf of this). If I change this to be PAGEFIX(YES) , again the O/S LRU tracking would not be busy with this. I would think that the actual paging rate by the O/S for the pages in this pool would be almost 0 since the pages in the pool would be referenced so frequently. [...]
14165 1159 38_Re: DB2 OSC and Plan_Table Alias setup23_Lazowski, James S (Jim)25_Jim.Lazowski@NAVISTAR.COM31_Thu, 27 Aug 2009 13:58:16 -0500602_us-ascii The triggers are very helpful! Thank you!
I found the answer to my original problem it has to do with DSN_VIRTUAL_INDEXES
It is created via the install job DSNTIJOS, but when trying to disable/enable OSC from the tool with a drop and create of the OSC tables and then re-enabling a user for explain by creating alias' the user is said to be enabled for Explain after the alias' are created, but the alias for DSN_VIRTUAL_INDEXES doesn't really get created. With the alias for DSN_VIRTUAL_INDEXES gone when a user tries to reconnect they are flagged as not being enabled for [...]
15325 88 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Thu, 27 Aug 2009 14:25:27 -0500553_us-ascii Hi Larry Your comment - "My understanding is that the FIFO chain is updated each time you do an I/O while the LRU chain is updated each time you do a getpage." is not entirely correct. While a FIFO chain is not affected by a getpage but an LRU chain does get updated due to an I/O. LRU processing is just manipulating the chain to favor the recently used pages. When this manipulation does not increase the chances of avoiding a future I/O the cost is unwarranted and FIFO should be used. Some examples are a fully resident object (chance [...]
15414 200 39_Disallow filtering in 'where' clause...8_Bob Kota23_bob.kota@EXELONCORP.COM31_Thu, 27 Aug 2009 14:36:06 -0500369_us-ascii Here's the scenario:
We are creating a few tables with a column that will allow free form text input. We have a requirement to not allow searches on this column. In other words, the column can be viewed but not searched on. Can this be done via views? Recommendations welcome.
Table Primary key is col1, col2 [...]
15615 94 90_Re: How to measure "residency time" or MUPA ( Maximum unreferenced pool age) time on a BP.23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 27 Aug 2009 17:49:47 -0400465_UTF-8 Larry,
Right.
But the CPU reduction from Pagefix is the cost of fixing then releasing a page in memory whenever an IO has to be performed into, or from the pool. So if the IO rate is low, there will be little benefit from pagefix.
Doesn't have any direct relationship to system paging, except of course, that the memory won';t be paged if it's fixed. A highly active pool will only encounter paging when/if the system memory is low. [...]
15710 21 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, 28 Aug 2009 00:48:59 +0000426_utf-8 Thanks to all!
_____________________________________________________________________
* 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." _____________________________________________________________________
15732 273 32_Loading Literals with IBM Load ?14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Fri, 28 Aug 2009 11:37:40 +1000266_us-ascii Hi Listers, I have a task to setup an IBM Shrlevel-Change Load job with literals for certain columns which do not have a value in the input file, but I am not having luck with the syntax!, and the util guide is not clear whether or not it can be done. [...]
16006 641 36_Re: Loading Literals with IBM Load ?13_Bright, Randy20_Randy_Bright@BMC.COM31_Thu, 27 Aug 2009 19:55:51 -0600731_us-ascii This may be a stupid question, but if it works with BMC LoadPlus, why not use BMC LoadPlus?
Being one of the Vendors referred to here, I'm naturally curious.
________________________________
Randy Bright Solutions Architect DB2 Utilities BMC Software, Inc.
phone: 512-340-6014 cell: 512-656-0240
10431 Morado Circle Austin, TX 78759
[http://www.bmc.com/USA/Corporate/graphics/logo_with_tag1.gif]
________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Alexander John Sent: Thursday, August 27, 2009 8:38 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Loading Literals with IBM Load ? [...]
16648 29 36_Re: Loading Literals with IBM Load ?14_Alexander John28_jalexander@WOOLWORTHS.COM.AU31_Fri, 28 Aug 2009 04:19:46 +0000404_utf-8 Hi Randy, In this case we are looking for concurrency by running IBM Load Resume Yes with Shrlevel Change, which converts the Load to Inserts & in the process the table will be available for other online activities.
Regards John
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
16678 24 34_DB2 z V9 - Who dropped the table ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Fri, 28 Aug 2009 06:34:01 +0000572_utf-8 Hello, which is the easiest way to find out who and when dropped a DB2 table ( no DB2 tools available) ? I know the OBID of the dropped table ... Regards, Nenad
_____________________________________________________________________
* 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." _____________________________________________________________________
16703 68 50_Re: Multi Row Fetch / Insert and Positioned Delete20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Fri, 28 Aug 2009 09:52:26 +0300611_us-ascii Hi Mike,
PK49057 is not applied PK41062 is applied.
I'll add this info to the PMR.
Thanks, Cuneyt
> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner > Sent: Thursday, August 27, 2009 7:58 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Multi Row Fetch / Insert and Positioned Delete > > > Hi Cuneyt > > I did an APAR search for SQLCODE510 and got some hits. the > two most recent > that are related to multi-row fetch are PK49057 (PTF UK29639) > from October > 2007, and PK41062 (UK23921) [...]
16772 35 38_Re: DB2 z V9 - Who dropped the table ?14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Fri, 28 Aug 2009 09:05:17 +0200539_utf-8 If you have RTS, then you know the last interval when your table has been used.
greetings,
Patrick Steurs
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Nenad Vidak Sent: vrijdag 28 augustus 2009 8:34 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z V9 - Who dropped the table ?
Hello, which is the easiest way to find out who and when dropped a DB2 table ( no DB2 tools available) ? I know the OBID of the dropped table ... Regards, Nenad [...]
16808 27 38_Re: DB2 z V9 - Who dropped the table ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Fri, 28 Aug 2009 08:24:50 +0000421_utf-8 Patric, thanks for the god tip, but this still doesn't answer who acctually dropped a table. This information must be obtained from the DB2 log somehow but I'm not an expert neither in log reading/formatting utilites nor in log format itself.... Regards, Nenad
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
16836 102 50_Re: Multi Row Fetch / Insert and Positioned Delete11_Mike Turner19_mike.turner@GMX.COM31_Fri, 28 Aug 2009 10:12:55 +0100728_iso-8859-1 Hi Cuneyt
Maybe PK49057 was not applied because it went PE. Fixed by APAR PK60789 (UK34180) in March 2008. The original problem was -510 from Delete of a multi-row set after the Fetch returned less than the requested number of rows.
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: "Cuneyt Goksu (Gmail)" Newsgroups: bit.listserv.db2-l To: Sent: Friday, August 28, 2009 7:52 AM Subject: Re: [DB2-L] Multi [...]
16939 530 47_uuaaa: [DB2-L] Loading Literals with IBM Load ?14_Michael Kaplan25_micaelkp@NETVISION.NET.IL31_Fri, 28 Aug 2009 12:22:46 +0300323_us-ascii Hi Join,
I have no BMC Load Plus, so I used to handle this kind of problems in several ways:
1. If your input file is not big, then , define like-table with all "constant-value" columns defined as Default, load and unload the data - in such a way your input file [...]
17470 77 51_Antwort: [DB2-L] DB2 z V9 - Who dropped the table ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 28 Aug 2009 12:05:48 +0200593_ISO-8859-1 trawl through the log - a nightmare without tools and/or very good assembler skills!!!
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
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
17548 396 36_Re: Loading Literals with IBM Load ?19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Fri, 28 Aug 2009 06:24:01 -0400328_US-ASCII The column descriptions in your load statement are intended to represent how the input data looks (not the table). So if your input data does not have data for certain columns of your table, simply delete those column descriptions from your load statement. The new rows will have default values for those columns. [...]
17945 69 36_Re: Loading Literals with IBM Load ?13_Bright, Randy20_Randy_Bright@BMC.COM31_Fri, 28 Aug 2009 07:40:42 -0500295_us-ascii LoadPlus has the same capability. In fact it was available in LoadPlus before it was available in IBM load. It has been available in LoadPlus since V6.1 released in July, 2001 so I am sure you must be on a release that supports it.
Use the same parameters as with IBM load: [...]
18015 54 38_Re: DB2 z V9 - Who dropped the table ?13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Fri, 28 Aug 2009 07:27:13 -0600373_us-ascii Nenad,
I have a couple of presentations I could send you... One actually has DSN1LOGP examples of finding catalog rows based upon an object name. You should be able to modify that example to look for an OBID. The second is an old and moldy presentation on log record format. Ping me directly if you would like me to send the power point presentations. [...]
18070 257 43_FW: Disallow filtering in 'where' clause...8_Bob Kota23_bob.kota@EXELONCORP.COM31_Fri, 28 Aug 2009 08:27:57 -0500728_us-ascii I'm resending the email just in case.
Thanks.
Bob Kota Information Technology - Database Services CHQ 46/NE/010/01 Office: (312)-394-7523 Pager: (877)-396-9345
________________________________
From: Kota, Bob:(BSC) Sent: Thursday, August 27, 2009 2:36 PM To: 'DB2 Database Discussion list at IDUG' Subject: Disallow filtering in 'where' clause...
Here's the scenario:
We are creating a few tables with a column that will allow free form text input. We have a requirement to not allow searches on this column. In other words, the column can be viewed but not searched on. Can this be done via views? Recommendations welcome. [...]
18328 139 38_Re: DB2 z V9 - Who dropped the table ?14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Fri, 28 Aug 2009 08:59:05 -0500429_US-ASCII If you are a competent assembler program, and you have access to Ken McDonald of BMC's excellent presentations on the DB2 log, you could run through your archive logs looking for a delete from SYSIBM.SYSTABLES where the OBID and DBID matches the table you are looking for. You could possibly do the same thing by eyeballing DSN1LOGP output, but if you don't have a timerange to review, you will have a lot to scan. [...]
18468 412 43_Re: Disallow filtering in 'where' clause...11_Mike Turner19_mike.turner@GMX.COM31_Fri, 28 Aug 2009 15:08:45 +0100648_iso-8859-1 Hi Bob
I don't know any way of preventing a search on a column. It is a basic part of the relational theory that any column can be specified in the WHERE clause.
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: Bob Kota Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thursday, August 27, 2009 8:36 PM Subject: [DB2-L] Disallow filtering in 'where' clause... [...]
18881 76 39_Identity columns uniqueness Db2 z/os v810_Art McEwen21_art.mcewen@ONTARIO.CA31_Fri, 28 Aug 2009 16:06:52 +0000365_utf-8 Hi Listers,
Does DB2 guarantee the uniqueness of identity column content within a table or do I have to worry about -803’s after the fact if I were do this on an existing table the following example (updates are typically IMS MPP):
ALTER TABLE XYZ.TEST ADD IDENT1 DEC(10) GENERATED BY DEFAULT AS IDENTITY; [...]
18958 57 38_Re: DB2 z V9 - Who dropped the table ?11_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 28 Aug 2009 16:07:34 +0000419_utf-8 Hi Nenad, First you can run the DSN1LOGP to find out when was the table deleted from the SYSIBM.SYSTABLES and after that once you have the time line you can run the DSN1LOGP for the OBID and the RBA around that time frame. That way you can limit your DSN1LOGP report for that particular object and time frame. Now you can review that DSN1LOGP output to find out what is the Authid who has dropped the table. [...]
19016 27 43_Re: Disallow filtering in 'where' clause...13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Fri, 28 Aug 2009 16:34:50 +0000400_utf-8 One option might be to create a Stored Proc to access the table / view.
The stored Proc would accept parameters groups (column name / value) and build dynamic SQL to retrieve the data. You could add an error routine that rejects the
_____________________________________________________________________
* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU * [...]
19044 35 43_Re: Disallow filtering in 'where' clause...13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Fri, 28 Aug 2009 16:36:41 +0000321_utf-8 One option might be to create a Stored Proc to access the table / view.
The stored Proc would accept parameters groups (column name / value) and build dynamic SQL to retrieve the data. You could add an error routine that rejects the unwanted column if the routine is called with this column/value pair. [...]
19080 82 43_Re: Identity columns uniqueness Db2 z/os v814_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Fri, 28 Aug 2009 12:55:12 -0400342_us-ascii Art,
DB2 cannot guarantee uniqueness of Identity Columns defined as Generated By Default. After all, this allows you to execute an INSERT statement using any value you with for the column.
Luckily, you have already created a UNIQUE Index on the column; DB2 will use *this* to enforce unique values for the column. [...]
19163 27 38_Re: DB2 z V9 - Who dropped the table ?14_Peter Vanroose17_pvanroose@ABIS.BE31_Fri, 28 Aug 2009 16:43:04 +0000649_UTF-8 Just a quick question related to this which came to mind:
Did someone ever try to set up a delete trigger on SYSIBM.SYSTABLES to catch (and log) this kind of table drop action?
-- Peter Vanroose, ABIS Training & Consulting.
_____________________________________________________________________
* 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." _____________________________________________________________________
19191 76 43_Re: Identity columns uniqueness Db2 z/os v811_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 28 Aug 2009 16:47:35 +0000607_utf-8 Hi Art, Is this table is existing table or new table. Another thing is that do you want application to provide value for Identity column or you want DB2 to generate the value.
An identity column defined as GENERATED ALWAYS is given values that are always generated by DB2. Applications are not allowed to provide an explicit value. An identity column defined as GENERATED BY DEFAULT gives applications a way to explicitly provide a value for the identity column. If the application does not provide a value, then DB2 will generate one. Since the application controls the value, DB2 cannot [...]
19268 47 43_Re: Identity columns uniqueness Db2 z/os v813_Jack Campbell23_jackrcampbell@YAHOO.COM31_Fri, 28 Aug 2009 16:48:57 +0000394_utf-8 Art
an identity column is just another kind of "sequence object" - so yes the values are unique, in that every value returned is +1 higher than the previous request for a sequence (number).
I have used identity columns extensively without issues, and found them to be "threadsafe" (unique values returned, even when running the same process multiple time in parallel). [...]
19316 42 43_Re: Identity columns uniqueness Db2 z/os v811_Mukesh Jain21_jain.mukesh@AOINS.COM31_Fri, 28 Aug 2009 18:01:46 +0000476_utf-8 Jack, You are right Identity column is another kind of Sequence object but there is only one concern with that. The application never knows what value is put into the primary key column after running the INSERT statement. If the application has to continue to insert rows into the child tables, it has to run a SELECT statement on the parent table to get the primary key value. But if this is not a concern in your system, using the IDENTITY option is a good idea. [...]
19359 69 43_Re: Identity columns uniqueness Db2 z/os v810_Art McEwen21_art.mcewen@ONTARIO.CA31_Fri, 28 Aug 2009 18:04:52 +0000396_utf-8 On Fri, 28 Aug 2009 16:47:35 +0000, Mukesh Jain wrote:
Hi Mukesh,
>Hi Art, > Is this table is existing table or new table.
The table is existing, my understanding is that the alter to add the column will put it re-org pending and the reorg will populate existing rows. But that sort of goes against your BY DEFAULT and ALWAYS explaination. [...]
19429 97 43_Re: Identity columns uniqueness Db2 z/os v814_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Fri, 28 Aug 2009 14:29:26 -0500374_us-ascii That's not true any more. Application can use SELECT from INSERT to get the generated value. Khalid
The application never knows what value is put into the primary key column after running the INSERT statement. If the application has to continue to insert rows into the child tables, it has to run a SELECT statement on the [...]
19527 182 43_Re: Identity columns uniqueness Db2 z/os v811_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 28 Aug 2009 15:37:39 -0500458_iso-8859-1
Mukesh -
The first restriction (having to insert and then select) is no longer an issue since you can use SELECT FROM INSERT.
I agree each may be useful in different circumstances. One fundamental difference I see is that Identity columns are tied to a table - when using the GENERATED ALWAYS option, no programmer can mess up (a DBA would be needed to alter and cause duplicate issues). [...]