1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2009, week 1
2 396 37_Re: -553, Secondary Auth Id, and RACF14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Sun, 31 May 2009 20:50:43 -0500463_US-ASCII Ed, If you look in the SDSNSAMP library, there is member DSN3SATH which is a sample source for the DSN3@ATH identify authentication exit that will build the list of secondary authorization groups from the RACF list of groups, which is what I believe you are looking to do.
=============================================== Wayne Driscoll OMEGAMON DB2 L3 Support/Development wdrisco(AT)us.ibm.com =============================================== [...]
399 164 34_Re: CPU Impact on Getpages and i/o16_Robert Catterall21_rfcatterall@GMAIL.COM31_Sun, 31 May 2009 22:33:27 -0400569_ISO-8859-1 Douwe indicated that he was "a little behind on the posts." I am WAY behind on the posts.
Jorg, have you checked to see whether or not you are exceeding the data management threshold (DMTH) for any of the buffer pools to which objects accessed by the query in question are assigned? DMTH is reached when 95% of a pool's buffers are non-stealable (either in use or updated and not yet externalized). When DMTH is reached, DB2 will issue a GETPAGE request for each row retrieved from a page. So, if a page contains two qualifying rows and DMTH has [...]
564 37 34_DB2 z/OS - Determining index usage11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Mon, 1 Jun 2009 08:03:42 +0000515_ISO-8859-1 Hello, there are six indexes that impact SQL inserts into a table. Now I would like to determine if (or how heavy) are indexes used in SQL selects. All SQL is 100% dynamic and the table may be accessed from five different db2 members in data sharing. Currently I can see that getpage requests were issued for index page sets , but I can't differentiate if those requests were SQL inserts or selects ... Do I have any other choice except running accounting trace on all members and creating reports? [...]
602 74 38_Re: DB2 z/OS - Determining index usage13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Mon, 1 Jun 2009 10:54:26 +0200365_us-ascii Hi Nenad,
Do you have another choice? Kind of. It depends how much effort you want to go through to obtain the information. One of our products has an 'access matrix' that shows you counts of the S/I/U/D statements against a table and its indexes, which you can drill down into for more detail. I imagine other ISVs may have something similar. [...]
677 124 38_Re: DB2 z/OS - Determining index usage14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 1 Jun 2009 14:21:57 +0100325_iso-8859-1 Don't forget - if this is DB2 9, there is an RTS field that is exactly what Nenad is looking for - the last time an index was used TO ACCESS the data
Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com Skype: db2grainger [...]
802 147 38_Re: DB2 z/OS - Determining index usage13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Mon, 1 Jun 2009 15:31:22 +0200530_iso-8859-1 Wot, you mean LASTUSED? Wish I'd thought of that... ;o)
See you in just under a fortnight,
Raymond
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: 01 June 2009 14:22 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS - Determining index usage
Don't forget - if this is DB2 9, there is an RTS field that is exactly what Nenad is looking for - the last time an index was used TO ACCESS the data [...]
950 196 38_Re: DB2 z/OS - Determining index usage14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 1 Jun 2009 15:14:43 +0100504_iso-8859-1 Indeed I do (just couldn't remember the name of the column!!)
Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com Skype: db2grainger
2009 IBM Data Champion
Ditton Park Riding Court Road Datchet Slough SL3 9LL
CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179. [...]
1147 222 38_Re: DB2 z/OS - Determining index usage13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Mon, 1 Jun 2009 16:36:38 +0200467_iso-8859-1 I remembered. ... The LASTUSED column in RTS table Sysindexspacestats might also do for a quick high-level look...
Make me spell it out, why dontcha... ;o)
Laters,
Raymond
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: 01 June 2009 15:15 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS - Determining index usage [...]
1370 186 53_Re: calling DB2 / COBOL stored procedure form web.net15_Bernie O'Connor26_berniedb2loconnor@LIVE.COM30_Mon, 1 Jun 2009 11:28:58 -0400546_Windows-1252
We have procedures that are called from a .Net application thousands of times a day. The call works well, which is your main concern. It's a classic ANSI standard call. We use input parms to pass the arguments, output parms to pass the return code and diagnostic information, and we use GTTs for the results, through which the client can fetch. There are quite a few examples on this approach, and Google is your friend to find them. Also, the IDUG site (idug.org) has quite a few examples, and IDUG Membership is free. [...]
1557 72 48_AD: Describe 3.0 now available for Free Download9_Eric West25_ewest@HLSTECHNOLOGIES.COM30_Mon, 1 Jun 2009 17:30:38 +0000485_- Ladies and Gentlemen,
We have just released Describe 3.0 on our Website at www.hlstechnologies.com. The only requirement is that you become a free registered user on our site if you aren't already.
Describe gives DB2 DBAs and System Programmers the ability to identify the existing DSNZPARM settings in a DB2 subsystem. However unlike visual Explain, It also provides a capability to compare DSNZPARM settings between versions of the DSNZPARMS for each subsystem. [...]
1630 47 27_Stored Procedures - ASUTIME12_Kapil Mathur25_kapilmathur2000@YAHOO.COM30_Mon, 1 Jun 2009 19:12:33 +0000426_windows-1252 Hi, I am in a DB2 V8 on z/OS environment. There are quite a few COBOL/DB2 stored procedures in use in our shop. I have a question concerning the parameter ASUTIME on the stored procedure DDL .
Does the ASUTIME for a stored procedure include only the time used in the SQL calls executed by the stored procedure or does it also include the CPU time spent in the COBOL code of the stored procedure also? [...]
1678 58 23_DB2 Developer Workbench0_28_BILL.GALLAGHER@PHOENIXWM.COM30_Mon, 1 Jun 2009 15:57:28 -0400444_US-ASCII We're in the midst of an upgrade of our DB2 LUW environment from v8.2 to v9.5. I just discovered that what was the "Development Center" tool in the v8.2 Application Developer client has been unbundled and enhanced, and became the "Developer Workbench" in v9.1. And now it looks like it was further enhanced in v9.5 and has been rebranded yet again as the now-licensed (i.e. write IBM a check) "IBM Data Studio Developer" product. [...]
1737 85 27_Re: DB2 Developer Workbench13_David Simpson22_dsimpson@THEMISINC.COM30_Mon, 1 Jun 2009 16:08:39 -0500706_iso-8859-1 I have run both of those products on the same workstation.
-----Original Message----- From: BILL.GALLAGHER@PHOENIXWM.COM Sent: Monday, June 01, 2009 2:59 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Developer Workbench
We're in the midst of an upgrade of our DB2 LUW environment from v8.2 to v9.5. I just discovered that what was the "Development Center" tool in the v8.2 Application Developer client has been unbundled and enhanced, and became the "Developer Workbench" in v9.1. And now it looks like it was further enhanced in v9.5 and has been rebranded yet again as the now-licensed (i.e. write IBM a check) [...]
1823 199 38_Re: DB2 z/OS - Determining index usage11_Nenad Vidak21_nenad.vidak@GMAIL.COM30_Tue, 2 Jun 2009 06:26:24 +0000643_windows-1250 Yes, LASTUSED will provide the data needed. Still running in V9 CM so will have to migrate to NFM first .... Thanks!
On Mon, 1 Jun 2009 15:31:22 +0200, Bell, Raymond wrote:
>Wot, you mean LASTUSED? Wish I'd thought of that... ;o) > >See you in just under a fortnight, > > >Raymond > >-----Original Message----- >From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil >Sent: 01 June 2009 14:22 >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] DB2 z/OS - Determining index usage > >Don't forget - if this is DB2 9, there is an RTS field that [...]
2023 192 17_AW: [DB2-L] Tools0_26_Walter.Janissen@ITERGO.COM30_Tue, 2 Jun 2009 08:54:48 +0200464_iso-8859-1 Hi
We are also using Query Monitor and noticed the CPU-overhead as well. I think, this CPU-overhead is somehow the same for every SQL-statement, so if the statement is very simple, the overhead is very high, if it complex, the overhead is low. We run Query Monitor in production only for 1 hour between 2:00 pm and 3:00 pm. So we catch all the important statements running during online, but none of the batch applications running at night. [...]
2216 116 17_AW: [DB2-L] Tools0_26_Walter.Janissen@ITERGO.COM30_Tue, 2 Jun 2009 08:58:16 +0200312_iso-8859-1 Hi
I can agree with Tor and we are running QM version 2.3 on the lowest level possible and we noticed a high CPU-usage, when QM is running. This CPU is not charged to the QM-addresspace, but to the SQL-statement. In Stobe we saw it as cross memory service in the applications addresspace. [...]
2333 114 21_Re: AW: [DB2-L] Tools11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Tue, 2 Jun 2009 07:00:40 +0000632_ISO-8859-1 Which version of QM do you use? On which version DB2? How do you actually measure the overhead (joblogs? RMF ? DB2PE ?)?
Regards, Nenad
On Tue, 2 Jun 2009 08:54:48 +0200, Walter.Janissen@ITERGO.COM wrote:
>Hi > >We are also using Query Monitor and noticed the CPU-overhead as well. I think, this CPU-overhead is somehow the same for every SQL-statement, so if the statement is very simple, the overhead is very high, if it complex, the overhead is low. We run Query Monitor in production only for 1 hour between 2:00 pm and 3:00 pm. So we catch all the important [...]
2448 155 29_AW: [DB2-L] AW: [DB2-L] Tools0_26_Walter.Janissen@ITERGO.COM30_Tue, 2 Jun 2009 09:07:45 +0200586_iso-8859-1 Hi
We are running QM V2.3 on DB2 9 CM. We use Strobe to measure the CPU-consumption.
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf Tel.: +49 211 477-2928 Fax: +49 211 477-2615 mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrats: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...]
2604 107 8_00D7002413_Laila hosaini19_lshosaini@YAHOO.COM30_Tue, 2 Jun 2009 00:14:24 -0700707_iso-8859-1
Hi Dear Listers I'm trying to chang storagegroup and VACT of a tablespace;
I have done it before successfully ,but today it had a problem,
there is no ALIAS for SOURCE vsam and it cataloged in MASTER CATALOG. AFTER RENAME IT TO NEW VCAT ;TARGET vsams has ALIAS ,BUT MY vsam is catloged in master catalog yet................ when I start TABLESPACE ,EVERYTHING IS OK, but i couln't select from tables in that TABLESPACE; ERROR IS this: DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00D70024, TYPE OF RESOURCE 00000220, AND RESOURCE NAME KSHCAT.DSNDBC.NGFST.TSLOGTAB.I0001.A001 DSNT418I SQLSTATE [...]
2712 211 33_Re: AW: [DB2-L] AW: [DB2-L] Tools11_Nenad Vidak21_nenad.vidak@GMAIL.COM30_Tue, 2 Jun 2009 07:53:00 +0000394_windows-1250 We are running identical configuration.
So, your scenario is to measure CPU consuption of the same SQL query with QM active and without it? Where do you see increased CPU : in DB2 allied address spaces ( CICS, BATCH), in DB2 ASs or ... ? Any particular SQL/workload type ?
I'm not familiar with strobe ... Can you see individual LMODS, CSECTs that burn CPU ...? [...]
2924 89 12_Re: 00D7002411_Nenad Vidak21_nenad.vidak@GMAIL.COM30_Tue, 2 Jun 2009 08:10:15 +0000576_windows-1250 My colleague storage admin says that you shoud use IDCAMS REPRO MERGECAT to move the entry from the master to user catalog .... Don't ask me for detalis ... Regards, Nenad
On Tue, 2 Jun 2009 00:14:24 -0700, Laila hosaini wrote:
> > > > >Hi Dear Listers >I'm trying to chang storagegroup and VACT of a tablespace; > >I have done it before successfully ,but today it had a problem, > >there is no ALIAS for SOURCE vsam and it cataloged in MASTER CATALOG. >AFTER RENAME IT TO NEW VCAT ;TARGET vsams has ALIAS ,BUT MY vsam is [...]
3014 242 33_Re: AW: [DB2-L] AW: [DB2-L] Tools11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Tue, 2 Jun 2009 10:28:34 +0000394_ISO-8859-1 I just created two accounting reports : one with QM active on the system, and one without it. Then I was comparing cpu consumed by same CICS transactions with similar number of SQL DML issued for both periods and no significant difference in CPU can be observed.
Regards, Nenad
On Tue, 2 Jun 2009 07:53:00 +0000, Nenad Vidak wrote: [...]
3257 86 12_Re: 00D7002415_Lizette Koehler23_starsoul@MINDSPRING.COM30_Tue, 2 Jun 2009 07:28:25 -0400349_iso-8859-1 LSH Basically the message seems to indicate the HLQ you are using KSHCAT is not an alias pointing to a usercat. Instead the process is trying to put it in the Master Catalog.
See if KSHCAT is an alias, if it is not, then you need to remove ALL KSHCAT.** datasets from the master cat, create the alias, then restore the files. [...]
3344 202 37_Re: -553, Secondary Auth Id, and RACF7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 2 Jun 2009 07:44:32 -0700364_iso-8859-1 Here is where I am at the moment. 1: I've opened a PMR.I've also collected the IFCID 314's.
2: It appears from the research to date, and assuming RACF/DB2, that the concept of a secondary authorization ID, by default unless the exits are modified, is nothing more than a list of all the groups that a given Primary authid is 'connected' to. [...]
3547 179 17_DB2 V8, Z/OS 1.1016_Proctor, William25_William.Proctor@TGSLC.ORG30_Tue, 2 Jun 2009 10:25:22 -0500360_us-ascii Hello and thanks in advance for all replies. We are trying to come up with justifications in our budget to migrate to DB2 V9 on Z/OS. One of the areas of concern is performance and the information that we have heard is that V9 actually improves performance over V8. Is this true? For those that have migrated to V9 what has been your experience? [...]
3727 585 21_Re: DB2 V8, Z/OS 1.1014_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 2 Jun 2009 14:31:34 -0400372_us-ascii Bill:
V9 offers:
REORG SHRLEVEL CHANGE with no BUILD2 phase, for partitioned tablespaces with unique indexes
This is a Performance enhancement; there is a downside, all NPIs are completely REORGed when a parttion is REORGed, but this is in most cases much faster than the BUILD2 phase and eliminates need for separate REORGs of the NPIs [...]
4313 199 15_Re: Date format14_Larry Kintisch17_LKint@VERIZON.NET30_Mon, 1 Jun 2009 20:33:03 -0400385_us-ascii Hi all, I passed a query about the mapping of the DSNXNBRM macro to my friends at IBM Education support. In Germany our DB2 instructional lead, Sabine Kaschta , found this. Please post and share this bit mapping. Larry Kintisch Pres. ABLE Information Services 845-353-0885
SELECT DISTINCT 'DBRM NAME = ' CONCAT RTRIM(NAME) CONCAT 'COLLID = ' CONCAT RTRIM(COLLID), [...]
4513 50 6_dopust33_Janez =?ISO-8859-2?Q?Svolj=B9ak?=22_Janez.Svoljsak@ZZZS.SI30_Wed, 3 Jun 2009 01:00:44 +0200488_ISO-8859-2 Odsoten bom od 02.06.2009 in se ne bom vrnil do 01.07.2009.
OPOZORILO: Sporoèilo lahko vsebuje informacije zaupne narave, ki so namenjene samo naslovniku. Èe ste sporoèilo pomotoma prejeli zaradi napake v naslovu ali pri prenosu sporoèila, prosimo, da nas o tem obvestite s povratno po¹to. V tem primeru vsebine prejetega sporoèila ne smete ¹iriti, kopirati, tiskati, razkriti oziroma uporabiti na kakr¹enkoli naèin. [...]
4564 194 12_Re: 00D7002413_Laila hosaini19_lshosaini@YAHOO.COM30_Tue, 2 Jun 2009 22:18:12 -0700498_iso-8859-1 Thanks Dear Friends I Done ,REPRO MERGECAT,and every thing is OK. l.s.h
--- On Tue, 6/2/09, Nenad Vidak wrote:
From: Nenad Vidak Subject: Re: [DB2-L] 00D70024 To: DB2-L@WWW.IDUGDB2-L.ORG Date: Tuesday, June 2, 2009, 12:40 PM
My colleague storage admin says that you shoud use IDCAMS REPRO MERGECAT to move the entry from the master to user catalog .... Don't ask me for detalis ... Regards, Nenad [...]
4759 72 15_Re: Date format13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 3 Jun 2009 10:24:35 +0200406_us-ascii That SQL statement, Mr Kintisch, is a beautiful thing. Just to spell it out - for me at least - the reference to 'OFFSET 33' at the end of the SQL comment details below is where the same info can be found in the DBRM PDS member. Just like the SQL, count across 4 bytes (with 33 as byte 1) and you're at the byte that shows the date format used during the precompile. As Emil said, I like it! [...]
4832 28 21_Re: AW: [DB2-L] Tools9_Dan Chatt15_dchatt@FNNI.COM30_Wed, 3 Jun 2009 11:01:38 +0000450_windows-1252 With another vendors tool we noticed a set overhead per SQL statement so your most CPU/elapsed time impact is with a high volume of very quick SQL statements. We didn't notice the impact with large queries but we saw the elapsed time double with the high volume efficient queries.
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...]
4861 200 37_Re: -553, Secondary Auth Id, and RACF14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 3 Jun 2009 21:24:38 +1000513_ISO-8859-1 Reading the tail [sic] of woe, try using DSN3SATH.
ICHPCGRP is a RACF control block, and hence would not be defined in a DB2 manual. From memory the source code uses the mapping macro. http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ichzc490/1.8
You seem to use AUTHID to mean "RACF user-id or group-id", whereas, in a DB2 context it is a contraction of authorization-id and has a different meaning. http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ16/2.8 [...]
5062 32 52_CN=Thomas Frieske/OU=IL/O=HCSC is out of the office.11_Tom Frieske25_Thomas_Frieske@BCBSIL.COM30_Wed, 3 Jun 2009 06:46:58 -0500609_us-ascii I will be out of the office starting 06/03/2009 and will not return until 06/04/2009.
**********
The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas. [...]
5095 59 17_DB2 V8 CM and NFM14_Renzo razzetti24_renzo.razzetti@GMAIL.COM30_Wed, 3 Jun 2009 09:15:17 -0300333_ISO-8859-1 Hello !!!
One customer decide to migrate to DB2 V8 CM and NFM in the same day. They already tested the application in a clon subsystem in NFM. I know the advantages for them to migrate directly to NFM. I would like to know if any of you migrate directly to NFM. And which disadvantages do you see to do that ? [...]
5155 66 21_Re: DB2 V8 CM and NFM11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Wed, 3 Jun 2009 12:38:59 +0000293_ISO-8859-1 You probably know that there is no return from V8 NFM to V7 .... Is your customer running just ONE application on DB2 z/OS? Why to hurry so mutch ? - If he's been living without new NFM features till now, he will surely survive next business cycle ( like month or so ....) ... [...]
5222 283 21_Re: DB2 V8 CM and NFM12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM30_Wed, 3 Jun 2009 07:56:41 -0500306_US-ASCII One good reason to stay in CM for a while is to make sure all your production systems don't have performance or any other problems executing on v8 code and to complete critical period-end applications for the same reason. If you then have a no-go situation you can at least fall back to v7 ! [...]
5506 153 21_Re: DB2 V8 CM and NFM13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO30_Wed, 3 Jun 2009 16:01:46 +0200400_us-ascii No fallback from NFM, but still it was what we did when we migrated. Be sure to have some people to handle performance problems if you get any. Is it possible to test the applications before "the lot" logs on Monday morning?
We are not so big, so a system restore in the weekend would be possible, but if we have to do restore in a weekday (Monday) it will be in the newspapers. [...]
5660 37 33_SUSE Linux 8 and DB2 Connect V9.513_Steve Runtsch26_steve.runtsch@ASSURANT.COM30_Wed, 3 Jun 2009 09:49:12 -0500294_US-ASCII We are currently upgrading to DB2 Connect V9.5. We have a number of old SLES 8 instances which contain a DB2 Run-time Client V8.2. We'd like to upgrade the client to V9.5.3, but SLES 8 is not in IBM's list of validated platforms. That does not give me a warm comfortable feeling. [...]
5698 26 25_[LUW] Bind or Not to Bind2_DJ20_dj53tx-udb@YAHOO.COM30_Wed, 3 Jun 2009 15:21:12 +0000539_windows-1252 I'm looking for references in the age old question: When do you rebind? Do you rebind after every reorg and runstats?
Thanks for any information.
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...]
5725 73 29_Re: [LUW] Bind or Not to Bind14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 3 Jun 2009 11:54:12 -0400604_us-ascii DJ,
I'm a DB2 z/OS guy, I'm not sure how well this relates to LUW but I'll give it a shot anyway.
The question is: If you don't have to rebind, and you're getting satisfactory performance, why rebind at all? Given that you risk a suboptimal access path choice.
My current site rebinds only at code changes and when required by, forex, the V7/V8 upgrade. Previous site made a point of only running RUNSTATS after a REORG so that CLUSTERRATIOF was always optimized. Our experience there was that run times were best with after-REORG stats, even if they didn't accurately [...]
5799 392 40_Re: Data Studio Developer Debugger Error12_Hepp Shery C21_Shery.Hepp@SRPNET.COM30_Wed, 3 Jun 2009 09:12:27 -0700344_us-ascii Hi Greg- good luck with this. I suggest you open a case with Ibm. I had numerous issues trying to debug a SQL lang procedure using this tool. Bottom line is I was never able to get it to work- so we rewrote the procedure in cobol- where at least we have the knowledge set and working tools for debugging in case we have problems. [...]
6192 48 29_Re: [LUW] Bind or Not to Bind12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Wed, 3 Jun 2009 11:23:05 -0500430_us-ascii Phil and DJ:
We find in Z/OS that between 60% to 80% of all rebinds will result in the same access paths. The cost estimates sometimes change, but that is usually traceable to statistic changes that reflect volume increases. With V9 now, and VX coming, complete rebinds are important if they were not done during the CM phase of V8, as some performance is lost because you are still executing in 31-bit mode. [...]
6241 306 29_Re: [LUW] Bind or Not to Bind15_Tony Provenzola27_Anthony.G.Provenzola@KP.ORG30_Wed, 3 Jun 2009 09:39:46 -0700596_US-ASCII This is also DB2 for z/OS, but the concepts should be applicable.
At one place I worked, we generally rebound all dependent packages after reorgs. That way, the access path was based on current information, using the most current optimizing code - "getting satisfactory performance" doesn't mean that it can't be improved. When there was a problem, which frankly wasn't often, we researched and dealt with it, and it was only 1 or 2 programs at a time. Then, when it was necessary to do a mass rebind (i.e. after an upgrade), it wasn't the concern that many shops apparently [...]
6548 93 21_Re: DB2 V8 CM and NFM10_Nicola Nur17_nick_nur@YAHOO.CA30_Wed, 3 Jun 2009 09:51:03 -0700608_iso-8859-1 We migrated to NFM after staying one week in CM. We thought that testing application in a clon subsystem will be enough. It definitely will help but is not enough because the environmental factors will differ between the two subssystems plus the sizes of tables etc. I suggest that you stay at least for one business cycle in CM. Some of the problems we enocuntered were mainly related to access path changes because we did REBINDs. Also some problems were related to dynamic SQL within DSNTIAUL. But on the whole it was good. Hope that helps. Nick Nur ________________________________ From: [...]
6642 149 21_Re: DB2 V8 CM and NFM0_33_armando.jorge.arranja@BANCOBPI.PT30_Wed, 3 Jun 2009 18:06:57 +0100705_utf-8
AVISO DE CONFIDENCIALIDADE
Esta mensagem e quaisquer ficheiros anexos a ela são confidenciais e destinam-se a uso exclusivo da pessoa ou entidade a quem são dirigidos. Se não é o destinatário da mensagem ou a pessoa responsável pelo seu encaminhamento ao respectivo destinatário, fica informado de que recebeu esta mensagem por engano, e de que qualquer utilização, distribuição, reencaminhamento ou outra forma de revelação a outrem, impressão, ou cópia desta mensagem é expressamente proibida. Se recebeu esta mensagem por engano deverá destrui-la, eliminá-la do sistema, e informar o remetente ou o Banco BPI,SA. O Banco BPI, SA utiliza software anti-virus. [...]
6792 63 20_DB2 Connect Tracing?13_Tom Duerbusch26_DuerbuschT@STLOUISCITY.COM30_Wed, 3 Jun 2009 13:12:03 -0500623_US-ASCII I'm running DB2 Connect Server V9.5 SP2.
In this setup it looks like:
Application server | | DB2 Connect Server | | DB2/VSE
Currently, there are only 3 users that are on the application server. Hence, there are only 3 users on DB2 Connect Server. There are several hundred on DB2/VSE.
Most of the time, we get sub-second response time. However, some times, we get 25-35 second response time. I do have a performance production on DB2/VSE and I can see that the database isn't the problem. In using the Operating systems included performance products (VMSTAT for DB2 Connect Server [...]
6856 47 17_Cobol and Varchar12_Matt Yardley19_yardley@MCMASTER.CA30_Wed, 3 Jun 2009 14:31:58 -0400538_ISO-8859-1 We are just now converting to DB2 V8 NFM. As part of the conversion, we have some tables that extract data from the DB2 catalog such as database, tablespace and table names. So we have changed their structures to match the table.
We have changed the process to incorporate the lengths of the varchar in a file for the LOAD utility. The load works as expected. I can SPUFI the table, obtain the lengths and get the lengths as expected. I can further UNLOAD the data, put ISPF HEX ON and see the lengths as expected. [...]
6904 49 38_[OT] a List quandry: list member names14_Larry Kintisch17_LKint@VERIZON.NET30_Wed, 3 Jun 2009 14:09:52 -0400382_us-ascii Hi Listers,
I'm certainly in favor of open listserves and the free exchange of questions and answers -- I've learned a lot and occasionally I add some value back.
I've noticed over the last few years a "bifurcation" of members' names. The first group I've known and loved give us their names and perhaps their company, agency or university affiliations. [...]
6954 109 42_Re: [OT] a List quandry: list member names14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 3 Jun 2009 15:23:00 -0400593_us-ascii Larry --
Individual decisions should rule. Anyone who wants to respond to an on-topic question should be allowed to, and no one who doesn't want to respond should be pressured to.
No one is forced to reply on the List. If someone is rude to you, don't deal with them -- it's not like it's paying work. Some of the most clueless people I've seen on the list, naming no names, have become regular contributors over time. Also, the vendors onlist are getting involved because of potential sales over the long term due to better-educated customers, and they should not [...]
7064 138 42_Re: [OT] a List quandry: list member names9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 3 Jun 2009 14:41:38 -0500498_US-ASCII I tend to check google first - if the answer is on the second or third item, it removes a lot of any urgency I would have to reply. Many times the answer is listed directly in a manual and google will find it for you. I suspect in a couple of years Stephen Wolfram's Alpha project could start to answer a lot of the questions where you have to know the history to know where to find the answers. I would really like to see IBM move their documentation to an alpha friendly structure. [...]
7203 70 27_Locking in old access paths0_24_Bob_Mathews@MANULIFE.COM30_Wed, 3 Jun 2009 17:32:34 -0400296_US-ASCII To All
We have a problem in Version 8 where an access path has deteriorated after a recompile and bind. We do not know of a way to lock in the old, better, access path in the Plan_Table because the statement numbers have changed.
Does anyone know of a way to do this? [...]
7274 308 31_Re: Locking in old access paths14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 3 Jun 2009 17:56:46 -0400642_us-ascii Update the PLAN_TABLE entries with the new statement number and REBIND with HINT.
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bob_Mathews@MANULIFE.COM Sent: Wednesday, June 03, 2009 5:33 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Locking in old access paths
To All
We have a problem in Version 8 where an access path has deteriorated after a recompile and bind. We do not know of a way to lock in the old, better, access path in the Plan_Table because the statement numbers have changed. [...]
7583 124 31_Re: Locking in old access paths10_Nicola Nur17_nick_nur@YAHOO.CA30_Wed, 3 Jun 2009 15:24:16 -0700577_iso-8859-1 Hi Bob I do not know what you mean by recompile. Do you mean precompile then compile. If you mean precompilation (using the Precompiler). I believe that once you precompile a program you take a chance on statement numbers within the DBRM. To my knowledge there is no way of getting back the same access path say by Opthint because Opthint needs an actual statement number within a stable DBRM (package) that is why we always REBIND and not BIND because at REBINDing SQL stmnts do not change even when the Optimizer optimize the query. I believe the only option [...]
7708 113 31_Re: Locking in old access paths9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 3 Jun 2009 17:33:30 -0500556_us-ascii not quite - you have to have opthint with a non-blank value and the new queryno and the version has to match the new version identifier. And the new access path has to be valid for the SQL statement. (the normal programmer I didn't change the SQL statement caveat applies even a change to a host variable can affect the access path). And the rebind has to specify the OPTHINT value. The easiest method is insert into select from plan_table but there are others. I did a presentation for IDUG on working with hints and just using spufi. It is [...]
7822 571 42_Re: [OT] a List quandry: list member names23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Wed, 3 Jun 2009 18:50:40 -0400319_iso-8859-1 It's certainly hard to guess if the yahoo and gmail addresses are the individuals desire to remain anonymous, or because of an employer issue. Maybe some of each.
I have gotten a lot of "thank you" emails from people over the years, and many have gone out of their way to find me at conferences. [...]
8394 235 31_Re: Locking in old access paths11_Nick Cianci19_ncianci@AU1.IBM.COM30_Thu, 4 Jun 2009 10:06:07 +1000428_ISO-8859-1 ... and a respected colleague once told me (haven't tried it) that it won't work for Multi-index access, but that was under v7, which seems like only a month ago !
... Well what do you know it was only a month ago ! WooHoo ... viva la v8
Bob , as suggested : find the corresponding old access path you want; clone the row(s); adjust the QUERYNO; Give it a hint name; REBIND with the hint and hope! [...]
8630 68 31_Re: Locking in old access paths14_Larry Kintisch17_LKint@VERIZON.NET30_Wed, 3 Jun 2009 20:10:01 -0400503_us-ascii Hi Robert, The bull may be out of the barn for 10,000 queries but many old-timers in the DB2 z world don't know about QUERYNO and don't set standards for developers as follows.
For the following statements: SELECT INTO..; DECLARE CURSOR ... SELECT..; INSERT; UPDATE; DELETE
you may [and should] add a QUERYNO nnnnnnn clause at the end. It locks the statement number to nnnnnnn across precompiles so that PLAN_TABLE references remain static. nnnnnnn is an integer you supply. [...]
8699 72 39_Re: [LUW] Bind or Not to Bind Also Z/OS13_Ed Krisiewicz21_ekrisiewicz@YAHOO.COM30_Thu, 4 Jun 2009 01:18:23 +0000425_ISO-8859-1 I set up a batch job that runs daily that performs the following:
1) gives me a list of programs will the most CPU time for the previous few days and hasn't been looked at (ie NOT EXISTS) in the past x days. Take the top 20.
2) Uses a vendor tool that takes the explain output currently in the plan table for these 20 programs and compares it to explain output if these 20 package were rebound. [...]
8772 57 31_Re: Locking in old access paths13_Ed Krisiewicz21_ekrisiewicz@YAHOO.COM30_Thu, 4 Jun 2009 01:30:07 +0000358_ISO-8859-1 To get an old access path that's still in the plan table, I do the following:
First, create a table PLAN_TABLE_OPHINT like PLAN_TABLE. This is only needed once. Then run the following SQL:
INSERT INTO PLAN_TABLE_OPTHINT SELECT * FROM PLAN_TABLE WHERE PROGNAME = program-name AND QUERYNO = query-no AND BINDTIME = bind-timestamp [...]
8830 59 44_[MVS-Fluff] Ruminations on the DB2 optimiser11_Nick Cianci19_ncianci@AU1.IBM.COM30_Thu, 4 Jun 2009 12:28:44 +1000479_UTF-8
Just doing a quick spot of reflection, remembering what a great DBA once said … “What DB2 needs is a pessimiser rather than an optimiserâ€.
Anyway with recent threads I got to thinking; in day to day life we trust the optimiser to make the right choices, and generally it does the correct job, but it does rely on statistics, and the trouble with stat.s are that it shows a point in time picture, not the overall (good & bad) side of a DB2 object. [...]
8890 36 42_Re: [OT] a List quandry: list member names9_Dee Bitou18_deebitou@GMAIL.COM30_Thu, 4 Jun 2009 06:43:52 +0000427_ISO-8859-1 I agree that it's annoying when people hide behind email ids. Obviously there can be reasons not to post whilst appearing to represent an interested company like IBM, BMC, CA, etc. but overall I think that people should say who and what they are.
I also agree re the tendency of late to use the List in place of basic training. An exchange of information and ideas is one thing, one way traffic another. [...]
8927 35 42_Re: [OT] a List quandry: list member names9_Dee Bitou18_deebitou@GMAIL.COM30_Thu, 4 Jun 2009 06:48:06 +0000403_ISO-8859-1 I agree re people hiding behind email ids. I accept that there are times when one can't be seen to be representing an interested company - IBM,BMC, CA etc. - but overall I think that listers should say who and what they are.
I also agree re the increasing use of the list in place of basic DB2 training. An exchange of information and ideas is one thing, one way traffic another. [...]
8963 24 42_Re: [OT] a List quandry: list member names11_Edie M Pool19_ediempool@GMAIL.COM30_Thu, 4 Jun 2009 07:10:08 +0000642_ISO-8859-1 I agree with Dee Bitou.
Edie M Pool
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________
IDUG North America 2008 Attendee Testimonial- "Each session I took away something that I could apply at my shop. I always learn something new." _____________________________________________________________________
8988 38 21_Re: Cobol and Varchar14_Peter Vanroose17_pvanroose@ABIS.BE30_Thu, 4 Jun 2009 07:34:43 +0000557_ISO-8859-1 What exactly is/are the COBOL host variable declaration(s) you are using? And what are the actual lengths in the catalog, to begin with? In my experiences with COBOL "varchar"s, lengths were always received correctly.
On the other hand, in this particular situation, it's probably easier to simply declare the COBOL variables to be X(128), in which case of course the blank-padded data will be returned and the length info will not be returned. But you'll most likely do nothing with that length info inside the COBOL program anyhow. [...]
9027 109 42_Re: [OT] a List quandry: list member names13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Thu, 4 Jun 2009 02:42:00 -0500527_ISO-8859-1 Unfortunately, with the growing number of companies putting restraints on access to anything that even appears to a social network, many cannot get to the DB2-L list from work. So the use of YAHOO, GMAIL, etc... e-mail addresses is increasing. I also personally know people who cannot use an e-mail address to post to the list. Because of these and many other reasons I don't see us ever policing how someone subscribes. It's an open list and we try not to get too involved in who and how subscriptions happen. [...]
9137 53 48_Re: [MVS-Fluff] Ruminations on the DB2 optimiser14_Peter Vanroose17_pvanroose@ABIS.BE30_Thu, 4 Jun 2009 08:00:13 +0000337_ISO-8859-1 Nick Cianci wrote: > If DB2 chooses an AP (Access Path) we don’t like we basically have 2 options: > - Give it a hint and hope that it gets used > - Tweak the stat.s to influence the optimiser to your way of thinking
There is a third option (which, in my impression, is often ignored by DBAs): - rewrite the query [...]
9191 60 42_Re: [OT] a List quandry: list member names13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 4 Jun 2009 11:29:20 +0200371_us-ascii Wow... that was quite a bit of effort to go to. I'm impressed - truly.
Regards to your brother, Rick Isaac David. I see he's finally passed the (2GB) bar exam.
Cheers,
Raymond Bell MSM Software Consultant BMC Software Ltd PS. And there I was, thinking Dee Bitou was a real name... That 'clunk' is the sound of a penny dropping. [...]
9252 81 42_Re: [OT] a List quandry: list member names13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 4 Jun 2009 12:01:06 +0200329_us-ascii 'Dee',
First of all, congratulations on your 25th birthday this year. Managed to pick up an anniversary lapel pin (for quite large lapels, actually, if you've seen one) at IDUG Warsaw last year. Looking forward to seeing your development progress over the years. Your parents, Don & Pat, must be very proud. [...]
9334 309 31_Re: Locking in old access paths13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 4 Jun 2009 12:10:02 +0200382_us-ascii Robert,
As Larry, Nick and possibly others have said, coding QUERYNO in your SQL is a good practice to adopt. If you want to try using optimisation hints it's a good way to make sure you're able to apply a hint to a statement. Otherwise, as you've found, come precompile time the statements may change number and then your life just got a bit harder, hintwise. [...]
9644 144 42_Re: [OT] a List quandry: list member names11_Dirk Johann20_dirkjohann@GMAIL.COM30_Thu, 4 Jun 2009 12:10:13 +0200449_US-ASCII I am one of the list member using a private gmail account for the DB2-L list. Reason is we have limited size of our company (IBM) mailbox and I don't like to go backward and forward with archives searching for interesting topics. I sign my messages with full name and company, so everyone can understand potential conflicts of interest, although I am attending this list because of technical interest and not because of IBM interests. [...]
9789 178 38_[OT] a List quandry: list member names35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Thu, 4 Jun 2009 12:12:30 +0200445_iso-8859-1 Larry, Willie et.al.,
personnally I prefer to know who I'm dealing with, so a real name would be nice, and if one can't use a "proper" address such as "christian_name.surname@company.country" one can always sign an email, just out of politeness. I do realize that there are companies out there who discourage mails to mailing-lists such as DB2-L, and if the use of Joe123.@yahoo.com circumvents that problem, fair enough. [...]
9968 44 21_Re: Cobol and Varchar14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 4 Jun 2009 20:40:14 +1000548_US-ASCII Depending on the query it might be index-only using a not padded index.
James Campbell
On 3 Jun 2009 at 14:31, Matt Yardley wrote:
> We are just now converting to DB2 V8 NFM. As part of the > conversion, we have some tables that extract data from the DB2 catalog > such as database, tablespace and table names. So we have changed > their structures to match the table. > > We have changed the process to incorporate the lengths of the > varchar in a file for the LOAD utility. The load works as expected. > I can [...]
10013 24 42_Re: [OT] a List quandry: list member names12_Liz Prefetch23_LizPrefetch@HOTMAIL.COM30_Thu, 4 Jun 2009 10:35:58 +0000659_ISO-8859-1 Raymond....of course I'm not real.
lol, Liz Prefetch
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________
IDUG North America 2008 Attendee Testimonial- "Each session I took away something that I could apply at my shop. I always learn something new." _____________________________________________________________________
10038 48 42_Re: [OT] a List quandry: list member names13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 4 Jun 2009 12:51:23 +0200508_us-ascii ROFL, as I believe the short-hand is!
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Liz Prefetch Sent: 04 June 2009 11:36 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [OT] a List quandry: list member names
Raymond....of course I'm not real.
lol, Liz Prefetch
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...]
10087 28 31_Re: Locking in old access paths12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Thu, 4 Jun 2009 11:11:32 +0000627_windows-1252 Re the use of QUERYNO - it's one of the standards listed in Cuneyt's http://sqlguideline.blogspot.com/ . Just getting in another plug for Cuneyt's efforts.
The use of HINTs without adopting a QUERYNO standard can be a real nightmare.
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...]
10116 55 31_Next UK GSE meeting - June 18th14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 4 Jun 2009 12:29:12 +0100510_iso-8859-1 It's just been brought to my attention that the next meeting of the UK DB2 Group will NOT be charging a registration fee
For more details and the days agenda, take a look at http://www.gseukdb2.org.uk/
Please note though that even though this is a FREE meeting, the group still needs to have an idea of numbers to organise the catering - if you are planning to attend, please make sure to contact the Treasurer, Richard Strong, on 07867 526298 or by e-mail at strongri@uk.ibm.com [...]
10172 102 42_Re: [OT] a List quandry: list member names12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 4 Jun 2009 04:42:02 -0700541_iso-8859-1 I use a Yahoo account. It has my real name, though. But I have to use Yahoo as my clients restrict the amount of mail allowed in my Inbox. And if I don't keep that inbox small, my client's important mail is rejected. In addition, I travel extensively. I cannot access company mail on the road, but Yahoo is available everywhere. Without an account on something like Yahoo, I wouldn't be able to access DB2-L. Most of the time I try to sign my email responses, but I'm human and do forget, but anybody can see my full name in [...]
10275 109 31_Re: Locking in old access paths12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Thu, 4 Jun 2009 08:40:52 -0500376_us-ascii Robert and Larry:
We often find that people are unaware of the number of factors that go into controlling hints in a production environment. We have helped people with Mike Bell's IDUG 2008 Dallas Presentation: Using SPUFI to Explore DB2 Hints. There was one shop where we were able to walk them through a solution that recovered from a critical problem. [...]
10385 171 21_Re: Cobol and Varchar12_Matt Yardley19_yardley@MCMASTER.CA30_Thu, 4 Jun 2009 10:07:10 -0400577_us-ascii
This is exactly what it was. I rebuilt the index and the query returned the proper lengths.
Thank you.
James Campbell wrote:
>Depending on the query it might be index-only using a not padded index. > >James Campbell > >On 3 Jun 2009 at 14:31, Matt Yardley wrote: > > > >> We are just now converting to DB2 V8 NFM. As part of the >>conversion, we have some tables that extract data from the DB2 catalog >>such as database, tablespace and table names. So we have changed >>their structures to match the table. >> >> We have changed the [...]
10557 114 27_Re: DB2 Developer Workbench22_Frank C. Fillmore, Jr.19_tfgpublic@GMAIL.COM30_Thu, 4 Jun 2009 15:07:30 +0000528_windows-1252 Bill, Hold onto your hat, but there's been another name change: "IBM Optim Development Studio". I posted a link that includes a trial download.
http://www-01.ibm.com/software/data/optim/development-studio/
There are two other resources to understand what the new development environment has to offer (i.e. why you might want to write that check to IBM). My colleague, Kim May, blogged on this topic regarding the Baltimore Washington DB2 Users Group LUW track coming up on June 19 in McLean, VA: [...]
10672 89 48_Re: [MVS-Fluff] Ruminations on the DB2 optimiser13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Thu, 4 Jun 2009 08:21:05 -0700312_us-ascii Unfortunately for us, not only are we locked into CA Gen / All Fusion Gen / Advantage Gen / Cool:Gen / IEF, and cannot change the queries. Yes, some shops do separate their SQL from Gen so they can update their queries, but not here. We're left wishing we could tell the optimizer "don't do that!" [...]
10762 109 48_Re: [MVS-Fluff] Ruminations on the DB2 optimiser12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Thu, 4 Jun 2009 10:39:53 -0500436_us-ascii There is a way to Test a hint interactively and get diagnostics before trying the bind.
Gerald Hodge HLS Technologies, Inc. www.hlstechnologies.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Taddei, Cathy Sent: Thursday, June 04, 2009 10:21 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [MVS-Fluff] Ruminations on the DB2 optimiser [...]
10872 277 65_[AD - Training] IBM Course CE242, Q-Replication, Starts July 13th12_Kimberly May28_kim.may@THEFILLMOREGROUP.COM30_Thu, 4 Jun 2009 14:34:35 -0400626_us-ascii If your organization is using or considering Q-Replication for DB2 high availability, please join us for IBM course CE242, Using Queue Replication, in our Towson, Maryland classroom, starting Monday, July 13th. This class is not scheduled often - if your schedule allows I hope you will join us.
The instructor for the class is DB2 Gold Consultant Frank Fillmore. Frank is an excellent instructor and has worked with many IBM customers to implement Q-Replication. Frank delivered the IBM DB2 Connect course two weeks ago and received a "PERFECT" satisfaction rating from all his students. [...]
11150 37 22_DB2 UDB V8.2 551 error12_Deepak Goyal20_goyal.mnit@GMAIL.COM30_Thu, 4 Jun 2009 21:26:10 +0000384_windows-1252 Hi,
Does anyone know what is SQLC2G15 package used for ? I am getting following error while running below query -
"select * from tab1; select * from tab2"
SQL0551N "XXXXXX" does not have the privilege to perform operation "EXECUTE" on object "NULLID.SQLC2G15". SQLSTATE=42501
We are having DB2 UDB V8.2 database but I am using V9 client. [...]
11188 48 44_Re: allocationg SMS managed VSAM datasets ??9_Dee Reins21_rcwingman@COMCAST.NET30_Fri, 5 Jun 2009 00:05:42 +0000787_utf-8 I thank everyone for their help.
I have been able to solve my space problems.
Dee _______
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________
IDUG North America 2008 Attendee Testimonial- "I made a number of contacts with other people having similar experiences to mine in some of our current initiatives, giving me additional resources to discuss issues and problems with." _____________________________________________________________________
11237 72 26_Re: DB2 UDB V8.2 551 error16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU30_Fri, 5 Jun 2009 09:49:09 +0800347_us-ascii I believe the SQLCnxxx packages are for the CLP - you probably need to bind your V9 client packages to your database and grant to either public or to specific IDs if you're more strict on security.
This can be done from the Configuration Assistant iirc - you may also want to bind the CLI/ODBC and other packages as required. [...]
11310 33 36_Stored Procedure with 200 parameters15_soumitra mandal25_soumitra_mandal@YAHOO.COM30_Thu, 4 Jun 2009 20:21:35 -0700486_us-ascii Hello,
Our development team wants to create a DB2 stored procedure on z/OS with around 200 IO parameters. The stored procedure will make an EXCI call to CICS program as well and return the output parameters to the caller.
The DBA team has refused saying it is against the standards. They have suggested that we should string the parameters and make it a variable length string. Our development team does not believe that is the right way to make parameters. [...]
11344 37 40_Re: Stored Procedure with 200 parameters14_Peter Vanroose17_pvanroose@ABIS.BE30_Fri, 5 Jun 2009 06:50:28 +0000506_ISO-8859-1 We have built, in a similar situation, a stored procedure returning a single VARCHAR(32700) which is XML. Fairly simple and flexible to return hundreds of values this way: e.g., a change in the list of parameters to be returned does not required a drop/create of the SP object.
We are doing this since DB2 v8. The stored proc is written in COBOL. We use simple XML processing from the COBOL compiler, no DB2-related XML processing (although this could have been done, even with v8). [...]
11382 87 26_Re: DB2 UDB V8.2 551 error10_Roger Hecq18_Roger.Hecq@UBS.COM30_Fri, 5 Jun 2009 08:38:18 -0400516_US-ASCII This package is for the DB2 Connect CLP. If it does not exist, one is created for you, with your id as the owner. Apparently, your security product is requiring that you also have execute authority on it.
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 Deepak Goyal Sent: Thursday, June 04, 2009 5:26 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 UDB V8.2 551 error [...]
11470 32 52_CN=Thomas Frieske/OU=IL/O=HCSC is out of the office.11_Tom Frieske25_Thomas_Frieske@BCBSIL.COM30_Fri, 5 Jun 2009 07:43:52 -0500609_us-ascii I will be out of the office starting 06/05/2009 and will not return until 06/06/2009.
**********
The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas. [...]
11503 85 40_Re: Stored Procedure with 200 parameters10_Dave Churn19_davechurn@GMAIL.COM30_Fri, 5 Jun 2009 07:50:10 -0500594_ISO-8859-1 Soumitra,
What is the likelihood that any of the 200 parameters will need to change? If the parameters are defined individually, your friendly DBA will have to change the Stored Procedure definition when the Stored Procedure changes. If there is one input and one output variable that is a combination of the fields, then your friendly DBA does not need to be involved when part of the detail changes. Note, there is also a timing issue. The S.P. definition needs to match the production code so your DBA has to drop and recreate the definition around the same time that [...]
11589 132 40_Re: Stored Procedure with 200 parameters14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 5 Jun 2009 14:13:59 +0100367_iso-8859-1 Isn't a more pertinent question "how likely is it that any single caller will really require ALL 200 parameters"?
I may be wrong, but this is sounding like the stored procedure equivalent of "I don't know which columns I may need, so I'll just select them all" - do the callers REALLY want every one of the 200 values returning on every call?? [...]
11722 24 26_Re: DB2 UDB V8.2 551 error12_Deepak Goyal20_goyal.mnit@GMAIL.COM30_Fri, 5 Jun 2009 14:31:34 +0000518_windows-1252 Thanks for the reply but I don't understand why I didn't face any issue with V8 client ? Is there anything changed with V9 client ?
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...]
11747 27 26_Re: DB2 UDB V8.2 551 error2_DJ20_dj53tx-udb@YAHOO.COM30_Fri, 5 Jun 2009 15:29:42 +0000372_windows-1252 That's because with each new release, and sometimes with a Fix Pack, the name of the package may change. Then you have to rebind to every database you connect to with the client to pick up the new name.
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...]
11775 36 26_Re: DB2 UDB V8.2 551 error12_Deepak Goyal20_goyal.mnit@GMAIL.COM30_Fri, 5 Jun 2009 16:03:55 +0000551_windows-1252 We always bind the db2ubind.lst and db2cli.lst files whenever we apply the fix pack or version upgrade. But I don't see the package SQLC2G15 in those lists. It seems that this package might be created because we are now using new driver in V9 client to connect to V8 databases. Thus V8 database has created this package when someone tried to connect to V8 database through new V9 driver but not given any execute privilege to PUBLIC. Once we granted the EXECUTE privilege to PUBLIC through GRANT statement on this package it worked. [...]
11812 72 26_Re: DB2 UDB V8.2 551 error19_Mezzanotte, Michael26_Michael.Mezzanotte@SSA.GOV30_Fri, 5 Jun 2009 12:04:36 -0400382_utf-8
----- Original Message ----- From: DB2 Data Base Discussion List To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Fri Jun 05 10:31:34 2009 Subject: Re: [DB2-L] DB2 UDB V8.2 551 error
Thanks for the reply but I don't understand why I didn't face any issue with V8 client ? Is there anything changed with V9 client ? [...]
11885 179 59_Baltimore/Washington DB2 Users' Group June 10, 2009 Meeting14_Henry L Nalven25_henry.nalven@MARRIOTT.COM30_Fri, 5 Jun 2009 16:25:16 +0000526_windows-1252 The Baltimore/Washington DB2 Users' Group Meeting Schedule for June 10, 2009 (Wednesday)
When? Wednesday, June 10,2009 9:00 AM-3:30 PM (Registration at 8:30 AM)
Where? Sheraton Columbia, 10207 Wincopin Circle Columbia, MD (410) 730-3900
Meeting Fees (please note changes): Pre-registered members $30 Pre-registered non-members $40 At-the-door for members $40 At-the-door for non-members $50
To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...]
12065 109 24_LOCK TABLE SQL Statement0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 5 Jun 2009 12:54:34 -0400319_US-ASCII If a program needs to update a table and does not want any other program or user to perform updates during the time the program is executing, can this be accomplished via the LOCK TABLE IN SHARE MODE? If the executing program does a commit, the statement would need to be executed again, is that correct? [...]
12175 179 28_Re: LOCK TABLE SQL Statement9_Ford Wong14_fordie@SHAW.CA30_Fri, 5 Jun 2009 11:00:23 -0600637_us-ascii Hello,
I believe you need to use
LOCK TABLE xxxxx.xxxxxx IN EXCLUSIVE MODE;
When you commit all the locks are released. It would have to re-issue the LOCK TABLE again...
Ford
----- Original Message ----- From: barbara.j.nigh@US.HSBC.COM Date: Friday, June 5, 2009 10:57 am Subject: [DB2-L] LOCK TABLE SQL Statement To: DB2-L@WWW.IDUGDB2-L.ORG
> If a program needs to update a table and does not want any other > program > or user to perform updates during the time the program is > executing, can > this be accomplished via the > LOCK TABLE IN SHARE MODE? If the executing program [...]
12355 539 28_Re: LOCK TABLE SQL Statement14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 5 Jun 2009 13:08:39 -0400387_us-ascii Barbara,
For a PLAN using a DBRM with a LOCK TABLE statement, the lock is released at a time which depends on the RELEASE parameter of the BIND. The two options are RELEASE(COMMIT) and RELEASE(DEALLOCATE); the latter holds the lock until the end of the program run. The relevant documentation about SHARE MODE is found in the DB2 SQL Manual, and is reproduced here: [...]
12895 478 28_Re: LOCK TABLE SQL Statement13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Fri, 5 Jun 2009 13:09:37 -0400324_us-ascii Hi Barbara,
As Ford says, yes, you lose the lock on the table.
You probably realize also that while the program holds the lock, other tasks may be queuing up waiting to get locks. As soon as you commit those locks can be granted to the other tasks and your thread will then wait. [...]
13374 374 28_Re: LOCK TABLE SQL Statement0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 5 Jun 2009 10:20:14 -0700404_US-ASCII Dave,
This is a one-time only execution of a program that needs exclusive use of the table for 8 to 10 hours and the requirement is to keep other updates out once the program starts until it finishes.
Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________ [...]
13749 104 28_Re: LOCK TABLE SQL Statement9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 5 Jun 2009 12:21:39 -0500584_US-ASCII The problem is you now have to balance two possibilities.
1. the program issues commits - what every class teaches us is good design however there is a window of opportunity between the COMMIT and the LOCK table that will allow a transaction to process. The lock table will wait for the normal timeout period for the transaction to complete and everything completes cond code = 0 but you now have a transaction in the middle of your batch program. If you have picked a good commit frequency - say every 15-30 seconds, it becomes probable not possible because the [...]
13854 27 28_Re: LOCK TABLE SQL Statement11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Fri, 5 Jun 2009 17:25:22 +0000296_- >The other issue is what will happen to CICS while all these transaction are queueing during the batch run. They will all wait for the timeout period before abending.
To long of a wait, and CICS could go down SOS! BTDT. GTTS. DLI! DWTDIA!
- Too busy driving to stop for gas! [...]
13882 949 28_Re: LOCK TABLE SQL Statement14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 5 Jun 2009 13:29:32 -0400348_us-ascii Bind the plan with BIND parameter RELEASE(DEALLOCATE) and use LOCK TABLE IN SHARE MODE. You'll be fine.
If for some reason you can't BIND the plan wth RELEASE(DEALLOCATE), then you have a problem, as Dave says, with other jobs queuing up to update and getting in after you COMMIT and before you can LOCK again. [...]
14832 760 28_Re: LOCK TABLE SQL Statement13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Fri, 5 Jun 2009 13:34:23 -0400362_us-ascii Can your other apps/transactions and your CICS region(s) handle the 8-10 hour lockout?
Seems to me you can't accomplish this if your program is issuing commits unless you can use RELEASE(DEALLOCATE) as Phil points out. Does the program obtain other locks? Of course the Commits and the RELEASE(DEALLOCATE) apply to all the locked resources. [...]
15593 573 28_Re: LOCK TABLE SQL Statement14_Benjamin Kniaz26_Benjamin.X.Kniaz@CHASE.COM30_Fri, 5 Jun 2009 12:40:12 -0500676_us-ascii Even if not V9 you could do this manually.
Step One Set TABLE_NAME into RO
Step Two Create TABLE_NAME_CLONE
Step Three Copy all data from TABLE_NAME into TABLE_NAME_CLONE
Step Four Run job against TABLE_NAME_CLONE
Step Five Name swap
Step Six Rebind
There are a bunch of different ways to handle this I would think.
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave Sent: Friday, June 05, 2009 1:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LOCK TABLE SQL Statement [...]
16167 1207 28_Re: LOCK TABLE SQL Statement14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 5 Jun 2009 13:54:20 -0400407_us-ascii Benjamin,
True, but name swapping doesn't deal with all the authorizations you need to duplicate, index names, and views. Also, for this to work correctly you have to use a new tablespace (so you can update the clone while locking the original), which has implications for your backup and recovery strategy. Why borrow this much trouble if you don't absolutely have to? [...]
17375 1169 28_Re: LOCK TABLE SQL Statement12_Felton, John25_Felton.John@PRINCIPAL.COM30_Fri, 5 Jun 2009 13:12:16 -0500733_us-ascii Phil's point is well-taken. Instead of Name Swap, you could substitute Unload TABLE_NAME_CLONE, Load Replace TABLE_NAME.
Regards, John Felton
________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Friday, June 05, 2009 12:54 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LOCK TABLE SQL Statement
Benjamin,
True, but name swapping doesn't deal with all the authorizations you need to duplicate, index names, and views. Also, for this to work correctly you have to use a new tablespace (so you can update the clone while locking the original), which has implications for your backup and recovery strategy. Why [...]
18545 213 28_Re: LOCK TABLE SQL Statement9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 5 Jun 2009 12:59:40 -0500333_US-ASCII One advantage of START ACCESS(RO) Run program START ACCESS(RW) Is that update transactions will get -914 immediately instead of waiting for a lock.
What is the plan if the program abends and you need to restart?
Can you restart the transactions or does it have to wait until the entire process completes? [...]
18759 76 28_Re: LOCK TABLE SQL Statement16_mocion@libero.it16_mocion@LIBERO.IT30_Fri, 5 Jun 2009 13:15:12 -0500605_ISO-8859-1 LOCK table in exclusive mode could be better (but every other program will receive a -904 error). And yes it's released at commit so if you use a loop you've to execute LOCK TABLE again.
HTH
Max Scarpa Certifiied missing link (according Darwin's theory)
barbara.j.nigh@US.HSBC.COM ha scritto: > > If a program needs to update a table and does not want any other > program or user to perform updates during the time the program is > executing, can this be accomplished via the > LOCK TABLE IN SHARE MODE? If the executing program does a commit, the > statement [...]
18836 63 21_Re: DB2 V8 CM and NFM16_mocion@libero.it16_mocion@LIBERO.IT30_Fri, 5 Jun 2009 13:24:34 -0500364_ISO-8859-1 Renzo
I hope cloned subsystem is a REAL clone, with REAL workload and REAL users (but I don't think it is). The only way you haveto repair somethign is to use hints (for old access path) and system restore.
IBM will say 'I told you' if something will went wrong. Everyone will tell you it. Managers will blame *YOU*, don't forget it. [...]
18900 1646 28_Re: LOCK TABLE SQL Statement14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 5 Jun 2009 14:47:41 -0400498_us-ascii Oo! Brownie points to John - that's the best idea I've seen in this thread!
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Felton, John Sent: Friday, June 05, 2009 2:12 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LOCK TABLE SQL Statement
Phil's point is well-taken. Instead of Name Swap, you could substitute Unload TABLE_NAME_CLONE, Load Replace TABLE_NAME. [...]
20547 458 28_Re: LOCK TABLE SQL Statement14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 5 Jun 2009 20:29:42 +0100552_iso-8859-1 In which case
Bind ACQUIRE(ALLOCATE) and RELEASE(DEALLOCATE)
LOCK TABLE ,,, IN SHARE MODE (readers will still get access, unless they are trying to read something you have updated)
COMMIT as often as makes sense - this should not now release the lock
And you're done
Phil G CA
________________________________
From: DB2 Data Base Discussion List on behalf of barbara.j.nigh@US.HSBC.COM Sent: Fri 05/06/2009 18:20 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LOCK TABLE SQL Statement [...]
21006 26 31_Ian Gough is out of the office.9_Ian Gough15_Ian.Gough@CN.CA30_Fri, 5 Jun 2009 22:04:49 -0400574_US-ASCII I will be out of the office starting 2009/06/05 and will not return until 2009/06/08.
If this is an emergency please contact ITCC and they will page the ITOPS-SYSTEM-DB person on-call.
_____________________________________________________________________
* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *
There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...]
21033 197 36_Performance from Z/os(V7) to AIX(V9)13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR30_Sat, 6 Jun 2009 06:59:32 +0200398_iso-8859-1 Hello the list,
We are migrating from Zos/DB2 V7 to AIX/DB2 V9 and having the following problem.
A Cobol program run about 10 000 times the following select
SELECT COUNT(*) FROM T1 A,T2 B WHERE A.C1=B.C1 AND A.C2= :val2 AND A.C3= :val3
T1 and T2 have about 5 000 000 rows each, and on T1 we have an index I1 on C1,C2 and on T2 we have an index I2 on C1. [...]
21231 211 28_Re: LOCK TABLE SQL Statement16_mocion@libero.it16_mocion@LIBERO.IT30_Sat, 6 Jun 2009 11:27:24 -0500630_ISO-8859-1 If all is managed by a scheduler you can use it to serialize all work on that table (mainly batch I presume) without the need to use some special SQL statament or some other trick (initiators? ).
Just an idea
Max Scarpa Certified 'Sunset Boulevard rider' Certified DB2 sysghost
barbara.j.nigh@US.HSBC.COM ha scritto: > > Dave, > > This is a one-time only execution of a program that needs exclusive > use of the table for 8 to 10 hours and the requirement is to keep > other updates out once the program starts until it finishes. > > > *Barbara [...]
21443 142 21_Re: DB2 V8, Z/OS 1.1012_Troy Coleman21_troy@TROYLCOLEMAN.COM30_Sun, 7 Jun 2009 17:13:08 -0500