1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2005, week 3 2 47 48_Re: Performance tuning of DB2/CICS transactions.0_17_JTonchick@AOL.COM29_Sat, 15 Jan 2005 01:22:04 EST405_US-ASCII Prasad, you have your work cut out planning upgrades. Besides DB2 V6 going off support soon, you are already running unsupported with CICS TS 1.3 and OS/390 2.8. You can go from CICS TS 1.3 to 2.2. We did that last year. But OS/390 2.8 is not a valid migration path for z/OS 1.5. We had to rush an order last August for z/OS 1.4 so we could have a supported migration path from OS/390 2.10. [...] 50 180 26_Re: DB2 for z/OS Version 812_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Mon, 17 Jan 2005 10:22:29 -0000560_iso-8859-1 Lock,

Thanks for that. I don't think any of the specific things you mentioned will affect us but I wiil read the chapter you mentioned.

Thanks again.

Jim McAlpine

-----Original Message----- From: LL581@DAIMLERCHRYSLER.COM [mailto:LL581@DAIMLERCHRYSLER.COM] Sent: 14 January 2005 17:20 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS Version 8





Jim,

Based on my research to-date, there's very little in the 'standard' CICS/Cobol/DB2 application that could 'cause' problems. [...] 231 61 25_Backup compression ratios30_David Wilson (Interactive DBA)35_david.wilson@CAMELOTINTERACTIVE.COM31_Mon, 17 Jan 2005 15:14:24 -0000406_ISO-8859-1 Hi all,

We run DB2V8.1.5 on AIX 5.1.

I am trying to find some benchmark figures to suggest how much we could benefit from using DB2 Backup Compression. Does anybody out there have any figures they could share or point me to a paper of some sort.

Many thanks in advance.

David Wilson Interactive DBA, Camelot Group plc Mobile - 07916-268306 Office - 01923-425049 [...] 293 38 48_DB2 Performance Tuning Courses - Recommendations14_Barbara J Nigh26_barbara.j.nigh@US.HSBC.COM31_Mon, 17 Jan 2005 10:01:57 -0800484_us-ascii Does anyone know which classes would benefit a DB2 DBA who wants to learn about the DB2PM Reports and the various performance tools? I need a class or set of classes that will assist me in identifying performance problems and coming up with possible solutions to address the problem(s).







Barbara J. Nigh - CAL261 Consultant, Database Management Systems - North America Phone: 831.754.1400 Ext. 2048 Pager: 877.406.7868 bjnigh@household.com [...] 332 107 52_Re: DB2 Performance Tuning Courses - Recommendations13_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Mon, 17 Jan 2005 13:08:03 -0600456_iso-8859-1 There are a few combinations of P&T and PM on this page: Click on Administration

IBM Global Services > Training > Course catalog > DB2 Information Management training > DB2 UDB for z/OS and OS/390 training[NL]

Topic areas Certification Special offers, news and more

[PARA] View the recommended order of our classes: Training paths [PARA][PARA]Select a topic area to see all DB2 UDB for z/OS and OS/390 training: [PARA] [...] 440 62 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?4_Paul19_the_baran@YAHOO.COM31_Mon, 17 Jan 2005 18:56:37 -0600592_- On Fri, 14 Jan 2005 14:28:22 -0600, Hayden_Jones@GRAINGER.COM wrote:

>V7 >OS/390 V2.10 >Data Warehouse >Distributed Dynamic queries from Business Objects thru DB2 Connect. > >All user queries against our Data Warehouse are UR. They take no locks >on the application objects. But the DO take locks on the catalog during >the >bind process - and hold them for the duration of the query (may take >hours). > >Same thing happens with a Spufi UR query - the first time. We have dynamic >caching on so if the same exact query executes again, the DSNDB06 locks >don't happen. > >So my [...] 503 61 29_Re: Utilinit lasting too long12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Jan 2005 19:20:24 -0600633_- Are you missing lots of service? APAR PQ46577 matches this description pretty well.

Roger Miller

On Fri, 14 Jan 2005 19:05:29 -0500, Robert Lawrence wrote:

>Reduce then number of objects in your LISTDEF. Break it down to smaller number of objects >in your listdef. By the way what are you trying to copy? All of the tablespaces in and ERP >instance in 1 LIST? > > >Bob Lawrence >DBA >Boscov's Dept Stores LLc > > > >> -----Original Message----- >> From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On >> Behalf Of Horacio Villa >> Sent: Friday, January 14, 2005 6:37 [...] 565 68 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Jan 2005 19:33:19 -0600429_- COMMIT means never having to say you're sorry for having locks left around. A cursor that is defined as WITH HOLD can still be open. There are some ugly problems if a table or table space definition gets changed while your query is running, and avoiding those abends still requires some serialization, with a number of variations. There are also some applications which expect to be able to reuse the prepared statements. [...] 634 68 38_Simple query taking forever to run....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 18 Jan 2005 02:34:06 -0800378_us-ascii SELECT * FROM MACARB.RDATUC C , MACARB.PHSTHO H WHERE C.NAME LIKE 'APPLIED%' AND H.YNAPMOC_ID = 'EM' AND H.YNAPMOC_ID = C.YNAPMOC_ID AND H.TSUC_ID = C.TSUC_ID AND H.PIHS_TO_SUF = C.RDDA_SUF AND H.PIHS_ID = '1' AND H.TSID_ID = '01' AND C.EMAN LIKE 'ABCDEF%'

PHSTHO has following index with a cluster ratio of 58:

NAME FKEYCARD FULLKEY COLNAME COLCARD [...] 703 14 17_Lost ACS routines15_Daniel Cremieux17_c300501@YAHOO.COM31_Tue, 18 Jan 2005 04:39:32 -0600667_- Our new storage guy had a bad idea to thru all of our ACS routines designed for DB2 datasets (index, TS placement , logs ..) - can you plesae send me your ACS routines

Thank you very much

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 718 69 19_Don Haderle retires13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 18 Jan 2005 06:59:45 -0500461_- Acc. to http://www.mercurynews.com/mld/mercurynews/business/10664195.htm

Database father retires, to polka: Don Haderle, 61, the father of IBM's flagship database software, DB2, retired last week after 37 years with the computer giant.

The IBM fellow went out in style. Feted by more than 500 of his colleagues at IBM's Silicon Valley Lab, Haderle and his wife, Gail, treated the crowd to their polka steps, one of their favorite pastimes. [...] 788 121 42_Re: Simple query taking forever to run....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 18 Jan 2005 04:17:22 -0800442_us-ascii Could it have something to do with Bufferepools...the tablespace is defined in a bufferpool which has 1800 pages while index is defined in a bufferpool with 1500 pages. How do I confirm that Bufferpools could be the bottleneck.

Actually, in this shop, in Production, I just checked and it seems that the total allocation for bufferpools is only 80 MB with following allocation (obtained from DISPLAY BUFFERPOOL command): [...] 910 145 42_Re: Simple query taking forever to run....13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 18 Jan 2005 13:39:16 +0100626_US-ASCII Hi Raquel,

I don't think it has anything to do with the BPs. Most likely it is a case of random (synch) I/O. The filtering provided by the first 4 columns in the index is probably very low. You must have high column correlation, because otherwise the first 4 columns could address a 1-billion-row table - you've only got 2.5M. Check this by running SELECT count(distinct H.TSUC_ID, H.PIHS_TO_SUF, H.TSID, H.PIHS) FROM MACARB.RDATUC C , MACARB.PHSTHO H WHERE C.NAME LIKE 'APPLIED%' AND C.EMAN LIKE 'ABCDEF%' AND H.TSUC_ID = C.TSUC_ID AND H.PIHS_TO_SUF = C.RDDA_SUF AND H.PIHS_ID = '1' AND H.TSID_ID = '01' [...] 1056 21 42_Re: Simple query taking forever to run....33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 18 Jan 2005 06:36:13 -0600346_- Raquel

As you said, you cannot cluster the data according to index PHSTHO, but what is the cardinality of the concatenating key? According to what you said, I suspect, that the query uses list prefetch, which falls back to a tablespace scan. So 800 times a tablespace scan on 2,8 million rows of 900 bytes will require a long time. [...] 1078 23 69_Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.213_Hodgin, Scott19_shodgin@SCFBINS.COM31_Tue, 18 Jan 2005 06:45:33 -0600830_iso-8859-1 We recently upgraded a production windows server to DB2 Connect EE v8.2 and now it seems that our dynamic SQL applications accessing data from z/os DB2 are running much slower.

Has anyone else experienced this situation?

Thanks,

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at [...] 1102 144 23_Re: Don Haderle retires15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 18 Jan 2005 14:54:04 +0100582_iso-8859-1 Dave,

I remember Don speaking as keynote at IDUG 2000 in Geneva. As I remember correctly we were having a worktitle "Don Haderly's Crystal Ball". As we said it doesn't matter what he talks about, it's always brilliant... As he came at the conference he had brought his brother-in-law with him. Who had a profession as pathologist. We had a good time and enjoyed his keynote. We went out for dinner and everyone wanted to join, especially some IBM specialist who was very much impressed with Don. As we went out I ordered tartar and the IBM specialist did the [...] 1247 22 23_QUIESCE 600 Tablespaces15_Daniel Cremieux17_c300501@YAHOO.COM31_Tue, 18 Jan 2005 08:28:24 -0600325_- Dear all,

We do a Quiesce of about 600 tablespaces every morning . Currently , there is one statement/ tablespace . I want to change it to

QUIESCE ts1 ts2 ts3 .. ts600



Is there any consideration before doing the change in term of MVS ressource , longer elapsed time ? ...

Thank you [...] 1270 51 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 18 Jan 2005 09:27:53 -0500419_iso-8859-1 Where is it running slower - obviously at the end, but where are the elapsed time, cpu, and wait time components?

Regards, Joel

----- Original Message ----- From: "Hodgin, Scott" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, January 18, 2005 7:45 AM Subject: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2 [...] 1322 50 27_Re: QUIESCE 600 Tablespaces14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Tue, 18 Jan 2005 16:38:07 +0200428_- Hello Daniel

If you are in V7.1 DB2 you can use list





-----Original Message----- From: Daniel Cremieux [mailto:c300501@YAHOO.COM] Sent: Tuesday, January 18, 2005 4:28 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] QUIESCE 600 Tablespaces

Dear all,

We do a Quiesce of about 600 tablespaces every morning . Currently , there is one statement/ tablespace . I want to change it to [...] 1373 77 35_AW: [DB2-L] QUIESCE 600 Tablespaces12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 18 Jan 2005 15:49:23 +0100894_iso-8859-1 Daniel,

one simple idea is to break your 600 tablespaces into "logical groups". E.g. TS001 to TS100 one group, TS101 to TS200 second group,.....

Quiesce each group with a single QUIESCE statement.

The good thing is that these QUIESECE's can be run in parallel to decrease the overall elapsed time...

HTH.

With kind regards - mit freundlichen Gruessen, Georg H. Peter c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- "A journey of a thousand miles must begin [...] 1451 31 29_Multi-core CPUs Db2 licencing13_Agarwal, Amit33_amit_agarwal@STANDARDANDPOORS.COM31_Tue, 18 Jan 2005 09:48:33 -0500729_iso-8859-1 I am trying to learn about multi-core CPU licencing for DB2. Does IBM /DB2 charge for processors based on each core?

Any pointers, white papers, links would be appreciated.

Amit Agarwal

-------------------------------------------------------- The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly [...] 1483 61 27_Re: QUIESCE 600 Tablespaces13_Mertens, Bart18_bart.mertens@CZ.NL31_Tue, 18 Jan 2005 15:53:34 +0100430_- Daniel,

In our shop we quiesce 750 tablespace every evening with a single listdef. This takes about 8 seconds to complete.

Listdef sample statement: LISTDEF FDRDUMP INCLUDE TABLESPACE ZLCZ*.* PARTLEVEL INCLUDE TABLESPACE ZLLI*.* PARTLEVEL INCLUDE TABLESPACE ZLZE*.* PARTLEVEL INCLUDE TABLESPACE ZLPE*.* PARTLEVEL INCLUDE TABLESPACE ZLEH*.* PARTLEVEL INCLUDE TABLESPACE DB*LCZ.* PARTLEVEL QUIESCE LIST FDRDUMP [...] 1545 59 27_Re: QUIESCE 600 Tablespaces20_Friedman, Avram (IT)32_Avram.Friedman@MORGANSTANLEY.COM31_Tue, 18 Jan 2005 09:53:37 -0500476_us-ascii A single QUIESCE causes an entry to be made in SYSCOPY indicating the named objects can be recovered consitantly to the associated LRSN QUIESCE TS1; QUIESCE TS2; Provides a marker where either TS1 and TS2 can be recovered to a point of consistancy but these points are not the same for both table spaces. QUIESCE TS1, TS2; Provides a marker where either TS1 and TS2 can be recovered to a point of consistancy and these points are the same for both table spaces. [...] 1605 105 35_AW: [DB2-L] QUIESCE 600 Tablespaces12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 18 Jan 2005 15:58:41 +0100598_iso-8859-1 Antonis,

for the LISTDEF control statement I have found a useful link:

http://www.craigsmullins.com/dbu_0402.htm

With kind regards - mit freundlichen Gruessen, Georg ---------------------------------------------------------------------- "A journey of a thousand miles must begin with a single step...."



-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Galeos Antonis Gesendet: Dienstag, 18. Januar 2005 15:38 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] QUIESCE 600 Tablespaces [...] 1711 173 36_SQL30082N - reason code 24 UDB Linux13_Dawn Kastelic26_dawn.kastelic@HIGHMARK.COM31_Tue, 18 Jan 2005 10:20:57 -0500369_us-ascii I am running DB2 V 8.1.6 on Linux. I have 2 instances with several databases catalogued on each instance. I am able to connect to a database using the following:

chln088|/home/lidxpbb> db2 connect to sasite

Database Connection Information

Database server = DB2/LINUX 8.1.6 SQL authorization ID = LIDXPBB Local database alias = SASITE [...] 1885 178 42_Re: Simple query taking forever to run....10_Jwkwan200025_jwkwan2000@HOUSTON.RR.COM31_Tue, 18 Jan 2005 09:46:16 -0600427_iso-8859-1 I agree with Dr. Elbert's theory. However the query Dr. showed you might be running 'forever'. I would just query the PHSTHO table to get a sense of how data are distributed. Use SELECT 4 columns, COUNT(*) from MACARB.PHSTHO GROUP BY 4 columns. Look for values with the big count number. Check to see if the result from RDATUC table actually fall into these values. Is it possible to add REDRO_ID in the query? [...] 2064 37 34_Re: REQUEST FOR - REXX CAF ROUTINE11_Carl Nelson22_carl.nelson@ANTHEM.COM31_Tue, 18 Jan 2005 10:20:23 -0600635_- I can not find this in the DB2-L. Would some one send me an example of REXX calling DB2 via CAF or repost this.







On Fri, 11 May 2001 08:25:37 +0200, Max Scarpa wrote:

>Hi Roger > >I post in DB2-L documents a REXX exec with CAF (DSNALI) call to connect to >a DB2 subsys to use IFI. > >Tale a look if this 'inutility' may be useful. > >Regards > >Max Scarpa > >DB2 sysprog (believe it) > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of [...] 2102 158 14_Re: Cloudscape0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 18 Jan 2005 08:28:31 -0800494_- Phil, there's also an article about cloudscape/derby in the fourth quarter 2004 DB2magazine. www.db2mag.com

cliff:-)



-----Original Message----- From: pjfjacks [mailto:pjfjacks@SWBELL.NET] Sent: Thursday, January 13, 2005 7:11 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] CloudScape



Many thanks for your answer, Fred. I wonder how it fares alongside such popular open source products such as MySQL and Postgres? thanks, [...] 2261 63 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.219_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Tue, 18 Jan 2005 08:29:11 -0800645_us-ascii What version of DB2 Connect did you upgrade from?

We had a similar issue when upgrading to V8 Info Integrator from Data Joiner (v2.7)when we discovered that an undercover default taken for the host server authentication had been changed.

Specifically, the CATALOG DATABASE command used to point to the z/OS subsystem had left AUTHENTICATION defaulted, which used SERVER authentication up through V7. Under V8, it was changed to SERVER_ENCRYPT which in turn caused some USS RSA decryption code (on z/OS 1.4 or 1.5 at the time) to be executed for every query across the system... That added considerable overhead which [...] 2325 75 34_Re: REQUEST FOR - REXX CAF ROUTINE13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 18 Jan 2005 11:30:24 -0500456_- Hey Carl,

What do you wish to do with Rexx?

You can probably do it via the DSNREXX interface that is now part of the DB2 product. Info is in the SQL & Appl Pgming Guide.

Dave



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Carl Nelson Sent: Tuesday, January 18, 2005 11:20 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] REQUEST FOR - REXX CAF ROUTINE [...] 2401 277 40_Re: SQL30082N - reason code 24 UDB Linux13_Mackey, Glenn20_GMackey@GUIDEONE.COM31_Tue, 18 Jan 2005 10:34:47 -0600313_us-ascii I had a similar issue recently. In my case, the Linux password had expired or something. I used root and reset/changed the password. (passwd) That solved my problem.

So to begin with, try to logon to the linux instance with the user id and see if that sheds any light.

Just a thought. [...] 2679 46 16_DDF Thread reuse13_Matthew Stitt33_Matthew.Stitt@ABCDISTRIBUTING.COM31_Tue, 18 Jan 2005 11:39:21 -0500565_iso-8859-1 We've been seeing some instances where DDF threads are being reused with no change in the user id coming across. This causes headaches for us as we have created WLM classifications based on the thread user id. If a thread gets created with a user id that places it into the High Velocity service class, that is fine. Later on, another request comes in that is assigned to that thread which generates a long running heavy CPU workload. This is where the problem comes in. Based on the type of request, it should have been in the Low Velocity service [...] 2726 307 40_Re: SQL30082N - reason code 24 UDB Linux13_Dawn Kastelic26_dawn.kastelic@HIGHMARK.COM31_Tue, 18 Jan 2005 11:47:48 -0500492_us-ascii Thanks for the tip, however, I am able to successfully logon to the instance with that user id so it's not a password issue.









"Mackey, Glenn" Sent by: "DB2 Data Base Discussion List" 01/18/2005 11:34 AM Please respond to "DB2 Database Discussion list at IDUG"



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] SQL30082N - reason code 24 UDB Linux [...] 3034 87 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.213_Hodgin, Scott19_shodgin@SCFBINS.COM31_Tue, 18 Jan 2005 12:57:41 -0600575_iso-8859-1 thanks for the replies.

I did check the authentication and mine is still SERVER.

My initial research seems to indicate that .net apps running under the oledb provider are much slower than apps using the DB2 .net managed provider.

I'm still looking and I have opened up an incident with IBM

-----Original Message----- From: Ohling, Tim R - CNF [mailto:Ohling.Tim@CNF.COM] Sent: Tuesday, January 18, 2005 11:29 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2 [...] 3122 28 67_BMC Catalog Manager question: Moving back between LOB T/S & Aux t/s13_P K.Ganapathy21_pkganapathi@YAHOO.COM31_Tue, 18 Jan 2005 13:09:10 -0600337_- HI,

Is there a one-line command like AUXTS or TS or TB within BMC Catalog Manager, that will allow me to switch back and forth between a LOB auxilliary table and its regular "parent" table?

Currently, I use the DDL command on the auxilliary table to find the parent table(one that occures after the STORES keyword). [...] 3151 155 34_Re: REQUEST FOR - REXX CAF ROUTINE11_Carl Nelson22_Carl.Nelson@ANTHEM.COM31_Tue, 18 Jan 2005 14:27:32 -0500330_us-ascii I need to run some SQL. I have been doing this successfully on my other LPARs but ran into problems on an LPAR using CAF. I did not know if I needed to change my call or if this was a problem with the REXX set up. The manual made no distinction as to CAF or TSO. Does it make a difference? Should this work for CAF? [...] 3307 63 71_Re: BMC Catalog Manager question: Moving back between LOB T/S & Aux t/s12_Hilton, Tina23_thilton@RANDOMHOUSE.COM31_Tue, 18 Jan 2005 14:49:53 -0500480_us-ascii If you're looking at the LOB table, you can enter XT to get the corresponding AUX table. I couldn't find anything for going the other way, although I didn't look too closely.

Tina

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of P K.Ganapathy Sent: Tuesday, January 18, 2005 2:09 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] BMC Catalog Manager question: Moving back between LOB T/S & Aux t/s [...] 3371 73 71_Re: BMC Catalog Manager question: Moving back between LOB T/S & Aux t/s13_P K.Ganapathy21_pkganapathi@YAHOO.COM31_Tue, 18 Jan 2005 13:55:04 -0600658_- Thanks Tina. That was what I was looking for.

-PK.

On Tue, 18 Jan 2005 14:49:53 -0500, Hilton, Tina wrote:

>If you're looking at the LOB table, you can enter XT to get the corresponding >AUX table. I couldn't find anything for going the other way, although I >didn't look too closely. > >Tina > >-----Original Message----- >From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of >P K.Ganapathy >Sent: Tuesday, January 18, 2005 2:09 PM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] BMC Catalog Manager question: Moving back between LOB T/S & >Aux t/s > >HI, > >Is there a one-line [...] 3445 32 34_Re: REQUEST FOR - REXX CAF ROUTINE19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 18 Jan 2005 14:21:29 -0600351_us-ascii I don't remember the errors we had originally.... But when we bound DSNREXX on a v7 subsystem then we couldn't connect to a v6 subsystem.

What we did was bind v6 dsnrexx on all sub-systems (v6 or v7) and it worked. Then later when we got all sub-systems at v7 then we bound v7 dsnrexx everywhere and began running the v7 version. [...] 3478 71 21_LOB Locks and LOCKMAX0_49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 18 Jan 2005 15:29:35 -0500443_us-ascii Okay, dumb question. Sort of.

Does DB2 release LOB Locks in timely fashion when bound with CS?

We're executing a SQL statement using DSNTEP2 which copies (INSERT INTO / SELECT FROM) a row with a LOB from one table to another. We say LOCK TABLESPACE (destination auxtablespace) IN EXCLUSIVE MODE; LOCK TABLESPACE (destination basetablespace) IN EXCLUSIVE MODE; then we execute the INSERT statement to copy the LOBs. [...] 3550 70 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?0_25_Hayden_Jones@GRAINGER.COM31_Tue, 18 Jan 2005 15:19:35 -0600317_iso-8859-1 Paul and Roger, Thanks for taking an interest in my question. I'm afraid I'm a little too thick to see the implications of the responses you have given. The locks are IS or S locks, but I don't yet understand why there should be any at all. And yes, there are problems with the locks being out there. [...] 3621 57 16_Trigger Question0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 18 Jan 2005 16:32:45 -0500502_US-ASCII If a after update/delete/insert trigger has the following code:

declare pres_key integer default 0;

set pres_key = (select CPG_CYCLE_FUNCTION_ID from IMPACT.CPG_CYCLE_FUNCTIONS where CPG_FUNCTION_ID = pres_function_id and CPG_CYCLE_ID = old_del.CPG_CYCLE_ID);

if (pres_key > 0) then

And the select statement in the parens does not return a row, will pres_key always be zero. If not, is there a better way to check and/or get a value from the select statement? [...] 3679 177 20_Re: Trigger Question15_Jackson Reavill18_damcon2@US.IBM.COM31_Tue, 18 Jan 2005 16:53:48 -0500566_US-ASCII If no row is returned I believe the value will be null.

HTH, Jay





Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 356-5317, Tie Line 8-697-5317

---------------------------------------------------------------- Happiness is not around the corner. Happiness is the Contour (SVT) of the road. ---------------------------------------------------------------- The early bird gets the worm, but the second mouse gets the cheese. ---------------------------------------------------------------- [...] 3857 45 20_Re: Trigger Question14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Tue, 18 Jan 2005 16:10:42 -0600 3903 119 17_optimization hint10_Grace Chen20_grace.chen@CELERO.CA31_Tue, 18 Jan 2005 16:11:52 -0700711_us-ascii Hi List,





I knew we can bind package with option OPTHINT. Is it possible to use OPTHINT on dynamic SQL? If it is possible, how to do it?





Thanks,

Grace Chen







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4023 108 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.212_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 18 Jan 2005 15:42:19 -0800325_us-ascii Scott, There is an open APAR JRsomething that talks about increased signon times with 8.2. The problem is that even with authentication SERVER, DB2 Connect goes out and tries to do a Windows network authentication. This can take a long time. This will be fixed in FP9 or you can get the special build for this. [...] 4132 137 21_Load Utility Question10_Ed Gephart20_edgep@ATTACHMATE.COM31_Tue, 18 Jan 2005 21:40:49 -0800706_us-ascii I would like to use the load utility to load a del file but I have a column that is char for bit data. Is this possible? I tried using x'hex_digits' format but that doesn't work.





Thanks,

Ed

------------------------------------------------------------------------ --------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm [...] 4270 26 13_dblink in DB213_Manoj Krishna25_manoj_krishna@HOTMAIL.COM31_Wed, 19 Jan 2005 11:31:44 +0530900_- Hi,

Is there a function/arguement in DB2 similar to 'dblink' in Oracle?

How can I collect data from multiple databases in DB2 as offered by the dblink in Oracle?

Appreciate your response.

Regards -Manoj

_________________________________________________________________ The MS Office product suite. Make efficiency a habit. http://www.microsoft.com/india/office/experience/ Simplify your life.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4297 47 34_Re: REQUEST FOR - REXX CAF ROUTINE14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 00:50:07 -0600609_- You might find it usefull to add a //DSNTRACE DD SYSOUT=*,RECFM=F,LRECL=F,BLKSIZE=F to your JCL (or equivalent TSO ALLOC). This generates some trace information that doesn't appear elsewhere. (Documented in the Diagnosis Guide + Ref)

James Campbell

On Tue, 18 Jan 2005 14:27:32 -0500, Carl Nelson wrote:

>I need to run some SQL. I have been doing this successfully on my other >LPARs but ran into problems on an LPAR using CAF. I did not know if I >needed to change my call or if this was a problem with the REXX set up. >The manual made no distinction as [...] 4345 34 17_Re: dblink in DB214_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 00:58:08 -0600613_- Websphere Information Integrator http://www-306.ibm.com/software/data/integration/db2ii/

James Campbell

(PS "As of January 11, 2005. IBM DB2 Information Integrator is now WebSphere Information Integrator" http://www-1.ibm.com/support/docview.wss? uid=swg21195631 )



On Wed, 19 Jan 2005 11:31:44 +0530, Manoj Krishna wrote:

>Hi, > >Is there a function/arguement in DB2 similar to 'dblink' in Oracle? > >How can I collect data from multiple databases in DB2 as offered by the >dblink in Oracle? > >Appreciate your response. > >Regards >-Manoj > [...] 4380 29 33_Re: Multi-core CPUs Db2 licencing14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 01:18:06 -0600398_- Which DB2? - DB2 z/OS is licensed on MSUs (ie on system capacity - of which multiple CPUs is only one component); - DB2 LUW "yes, it is licensed on a per-CPU basis" - see, eg http://www- 1.ibm.com/support/docview.wss?uid=swg21008057; - other DB2s - don't know.

James Campbell



On Tue, 18 Jan 2005 09:48:33 -0500, Agarwal, Amit wrote: [...] 4410 40 20_Re: Trigger Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 01:34:54 -0600741_- try

set pres_key = (select coalesce(min(CPG_CYCLE_FUNCTION_ID),0) from ... ?

James Campbell







On Tue, 18 Jan 2005 16:32:45 -0500, db46@DAIMLERCHRYSLER.COM wrote:



>If a after update/delete/insert trigger has the following code: > > declare pres_key integer default 0; > > set pres_key = (select CPG_CYCLE_FUNCTION_ID from >IMPACT.CPG_CYCLE_FUNCTIONS where CPG_FUNCTION_ID = pres_function_id and >CPG_CYCLE_ID = old_del.CPG_CYCLE_ID); > > if (pres_key > 0) then > >And the select statement in the parens does not return a row, will >pres_key always be zero. If not, is there a better way to check and/or >get a value from the select statement? > >Dean J. Burchill >248-576-8319 [...] 4451 31 21_Re: optimization hint14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 01:35:43 -0600351_- try

SET CURRENT OPTIMIZATION HINT

James Campbell





On Tue, 18 Jan 2005 16:11:52 -0700, Grace Chen wrote:

>Hi List, > > > >I knew we can bind package with option OPTHINT. Is it possible to use >OPTHINT on dynamic SQL? If it is possible, how to do it? > > > >Thanks, > >Grace Chen [...] 4483 70 33_Re: Multi-core CPUs Db2 licencing12_Travis, John25_john.travis@CAPGEMINI.COM31_Wed, 19 Jan 2005 08:44:47 -0000410_us-ascii Amit, DB2 LUW ESE is licensed per CPU with a proviso that all CPUs on the server need to be licensed. DB2 LUW WSE is licensed for up to 4 CPUs

John Travis

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: 19 January 2005 07:18 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Multi-core CPUs Db2 licencing [...] 4554 12 27_Re: QUIESCE 600 Tablespaces15_Daniel Cremieux17_c300501@YAHOO.COM31_Wed, 19 Jan 2005 03:43:53 -0600637_- thank you , we generate the tablespace list dynamically , in the past we use listdef but our naming convention implies the risk of forgetting new comers tablespaces

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4567 200 25_Re: Load Utility Question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Jan 2005 11:05:12 -0000448_us-ascii You should just be able to load it as CHAR data.....

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Gephart Sent: 19 January 2005 05:41 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Load Utility Question [...] 4768 363 25_Re: Load Utility Question10_Ed Gephart20_edgep@ATTACHMATE.COM31_Wed, 19 Jan 2005 03:48:14 -0800411_us-ascii The data is binary and there will be a possibility that the binary pattern will end up being a delimiter. Is there another way?





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Wednesday, January 19, 2005 6:05 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Load Utility Question [...] 5132 17 21_Re: optimization hint15_Patrick Bossman18_bossman@US.IBM.COM31_Wed, 19 Jan 2005 06:02:04 -0600754_- Hello Grace, Here is link to a presentation on using optimization hints. It covers the common pitfalls, limitations, and some new maintenance related to dynamic SQL with the statement cache. http://www-1.ibm.com/support/docview.wss?rs=64&uid=swg27005489

Regards, Pat Bossman

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 5150 143 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.213_Hodgin, Scott19_shodgin@SCFBINS.COM31_Wed, 19 Jan 2005 06:31:31 -0600493_iso-8859-1 Myron,

Thanks for the reply. The connect is not where the delay seems to be. However, I did get the hotfix you are referring to, but it did not seem to make a difference. I'll be sending some traces to IBM today.

Scott

-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Tuesday, January 18, 2005 6:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.2 [...] 5294 407 25_Re: Load Utility Question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Jan 2005 13:03:03 -0000434_us-ascii Ahhh

Good point - sorry, I hadn't noticed that you were doing a delimited load. I guess there is the possibility of ANY of the 256 bit combinations could occur, which I think means you cannot do a delimited load. Any chance of making it fixed format??

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 5702 124 8_dsnutils12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV31_Wed, 19 Jan 2005 08:04:47 -0500370_iso-8859-1 Hi,

I'm a mainframe DB2 system programmer supporting peoplesoft applications coming from distributed servers.

The PSOFT folks are in process of implementing an upgrade (PS 8.2 to PS 8.4x) , and they have requested access to my mainframe DSNUTILS stored procedure, to allow them to run the %UpdateStats metasql function during installation. [...] 5827 195 34_Re: REQUEST FOR - REXX CAF ROUTINE13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 19 Jan 2005 08:19:34 -0500432_- Hello Carl,

There should be a reason code accompanying the -924. What is it?

In my experience, -924 errors have been environmental. In compiled programs, this has been from a bad linkedit. That's obviously not the case with your Rexx code. It could be an incomplete install of the Rexx interface. I think I may have seen -924 when the subsystem you're attempting connection to is not up or not up on your LPAR. [...] 6023 30 25_Stored Procedure question9_Joe Huang16_tzs61x@YAHOO.COM31_Wed, 19 Jan 2005 06:18:28 -0800306_us-ascii Is there a way to write a DB2 stored procedure to do SELECT against 10 tables and SELECT only 1 column from each table, then pass the value(10 columns from 10 tables) back to the calling program. We are z/OS DB2 V71 shop. using procedure builder. Thanks in advance Sample code will be super. [...] 6054 25 49_Looking for a good electronic catalog poster (V8)15_Daniel Cremieux17_c300501@YAHOO.COM31_Wed, 19 Jan 2005 08:14:24 -0600250_- If someone has an user friendly electronic catalog poster , other the the one from CA , can you please send me . The one i've downloaded from CA is really ugly , it is obvious that the person who designed it doesn't have to use it every day ! [...] 6080 113 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM31_Wed, 19 Jan 2005 08:24:15 -0600576_iso-8859-1 Hayden, I've been bitten by this exact problem myself. The issue is that isolation level UR is really only for read only access to application data. However, in order to ensure that consistency is guarenteed in the catalog, DB2 cannot use read through locks on the catalog when the bind (prepare) is done. The only way you might get around this would to ensure that cacheing is on, and code your application to issue the prepare, then issue a commit, then execute the prepared statement. Again, I have not tried this, so this is just a presumption on my part. [...] 6194 70 53_Re: Looking for a good electronic catalog poster (V8)14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Jan 2005 14:30:15 -0000405_us-ascii You're right Daniel - I don't have to work with it every day!

Mind you, although I did do most of the work on the physical poster, the electronic version was just copied from that work and, yes, it assumes you already know the layout of the poster.

I'd be interested to know how many people DO use the electronic one and whether it would be appreciated if we redid the design? [...] 6265 48 15_BMCDSN in batch13_Bell, Raymond22_raymond.bell@LANDG.COM31_Wed, 19 Jan 2005 14:48:40 -0000585_iso-8859-1 All,

Quick question. Does anyone have some JCL to run the BMCDSN command processor in batch that doesn't look like this?

//BMCDSN EXEC PGM=IKJEFT1A,DYNAMNBR=20 //* //BMCUTILB DD DISP=SHR,DSN=SA1.P.BMC.AUTHLOAD // DD DISP=SHR,DSN=BMC.LOAD //SYSTSPRT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * ABUDSN /DB2B/ABUD2300/ +DIS UTIL(*) +TERM UTIL(UTILITY_ID) /*

I'd kinda like something similar to DSNUPROC, like 'EXEC PGM=ABUD2300,PARM=(DB2B)' and the SYSIN just being the +DIS UTIL(*) command. Or is the above the only way? [...] 6314 24 29_Questions for Education sites12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Wed, 19 Jan 2005 09:52:29 -0500390_iso-8859-1 Does any one out there use PeopleSoft-z/OS DB2 for HR and Finance in an Education environment?

Also does any one know of any "student" system that run on DB2 for z/OS.

John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS Office 561-357-7523 Cell 772-485-1592 Email lendman@palmbeach.k12.fl.us [...] 6339 47 33_Re: Questions for Education sites12_Joan Stevens23_jstevens@BINGHAMTON.EDU31_Wed, 19 Jan 2005 09:57:57 -0500446_iso-8859-1 We don't use PeopleSoft - went to oracle for HR & Finance. SCT Banner has student systems. Know it has oracle & I believe there is a DB2 version. Joan Stevens DB2 DBA Binghamton University

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of John Lendman Sent: Wednesday, January 19, 2005 9:52 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Questions for Education sites [...] 6387 58 29_Re: Stored Procedure question9_Agus Kwee19_askwe@OPTONLINE.NET31_Wed, 19 Jan 2005 10:02:50 -0500561_us-ascii Joe,

If you are using stored procedure builder to write a DB2 Stored Procedure, then you must be coding your stored procedure in SQL or Java Progamming Language. If your SELECT statement against 10 tables produces no more than 1 result row, you can use the SELECT INTO statement that can specify 10 call parameters to return the result values to the caller. If the SELECT result has more than 1 row, you must use cursor to define the SELECT in the stored procedure and you probably should use RESULT SET to return the values to the caller. [...] 6446 171 73_Re: Dynamic SQL against z/os DB2 slow after upgrading to DB2 Connect V8.29_Nic Honan20_NicHonan@HOTMAIL.COM31_Wed, 19 Jan 2005 09:16:36 -0600605_- Can you tell me wath the APAR number is. I've given up trying to search for it. Or better still give me tips on searching for it.

Ta - Nic



On Wed, 19 Jan 2005 06:31:31 -0600, Hodgin, Scott wrote:

>Myron, > >Thanks for the reply. The connect is not where the delay seems to be. >However, I did get the hotfix you are referring to, but it did not seem to >make a difference. I'll be sending some traces to IBM today. > >Scott > >-----Original Message----- >From: Myron Miller [mailto:myronwmiller@YAHOO.COM] >Sent: Tuesday, January 18, 2005 6:42 PM [...] 6618 19 34_AS/400 DB2 Bind Parms from z/Os v713_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Wed, 19 Jan 2005 10:13:01 -0600344_iso-8859-1 Folks, We are using the copy to migrate thru the environments on z/os. Using DRDA/DDM to the AS/400, so for a program running on z/os, and accessing AS/400 data, we do a remote bind from z/os to the AS/400. As yet, I have not been able to find a list of bind parms which are valid/specific to the AS/400 ? thanks, Mark Vickers. [...] 6638 82 51_Re: UDB 8.2 on Aix - db2look inconsistent behaviour8_P. Evans19_pevansdba@YAHOO.COM31_Wed, 19 Jan 2005 08:24:44 -0800472_us-ascii Thanks Kiran, unfortunately the instance that does not work properly is not EEE, and the home directory has all the appropriate permissions. I have seen the temporary file created in there,though it appears to be always empty. Another discovery i've made is that a test 32 bit instance on the same server works ie the db2look contains all the container info. It looks like i'll be writing a perl script to extract the info. Thanks for the suggestion. Pierre [...] 6721 171 23_Re: Don Haderle retires12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jan 2005 10:45:08 -0600387_- Don was a good speaker, but there were moments. His real gift as the chief architect of DB2 and then as the CTO for Data Management is a deep understanding, with both breadth and scope. His memory is amazing, going back to a module he had not seen for 10 years and remembering the logic and rationale. He could also talk easily at the level of CEOs, as they played golf together. [...] 6893 69 12_Re: dsnutils19_Database Consultant32_databaseconsultant@SBCGLOBAL.NET31_Wed, 19 Jan 2005 08:59:02 -0800462_us-ascii Tom, I am working on an 8.8 upgrade and we are not doing this. No where have I seen that it is mandatory, however, it might help with some poor performing conversion queries. We have lots of them and are tuning them with indexes. I am certain runstats would not have solved the performance problems we experienced, but your conversion may be different. Try it with a test run of the conversion and see if it helps, if it doesn't, take it back out. [...] 6963 20 28_synonym , i don't understand12_Billy Larsen19_billarsen@YAHOO.COM31_Wed, 19 Jan 2005 11:11:36 -0600747_- table : owner.table set current sqlid = 'test' create synonym table for owner.table



The program can access to owner.table with the Bind option QUALIFIER(TEST)



Why when i do : Select TEST.TABLE , it doesn't work ? (not found ? )

Thank you

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 6984 71 42_Re: Simple query taking forever to run....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 19 Jan 2005 09:18:47 -0800329_us-ascii Not using List prefetch; no compression (what would that have suggested BTW?)

Could not execute the query suggested by Dr.Ebert and had to cancel it after around 1.5 hours of execution.

Also, index PHSTHO10 was newly created (for this very query), so I am assuming it is in a good physical condition. [...] 7056 39 27_Help with writing query....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 19 Jan 2005 09:22:05 -0800514_us-ascii Ok, can’t believe I am not able to code a query for something as simple as the following requirement:

UPDATE RABMAC.DEHTRO T1 SET NED_CER = T2.NED_CER WHERE EXISTS (SELECT 1 FROM RABMAC.QUOTATION T2 WHERE T1.ORD_RB = T2.ORD_RB AND T1.ORD_ID =T2.ORD_ID AND T1.COMP_ID =T2.COMP_ID )

Please suggest.

TIA Raquel.







__________________________________ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo [...] 7096 166 27_Re: Space on DB2 Packs ....16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Wed, 19 Jan 2005 12:31:31 -0500344_iso-8859-1





Amit.

If you would like, you can submit this to 'THE CODE PLACE' AREA within the www.idug.org and then anyone can d/l without needing to request that you do so individually.

This is where these type of helpful codes etc can be found that other have felt the 'desire' to share (so to speak) [...] 7263 83 31_Re: Help with writing query....12_Colleen Clow23_Colleen_Clow@BCBSIL.COM31_Wed, 19 Jan 2005 11:55:49 -0600568_utf-8







What about this instead?

UPDATE RABMAC.DEHTRO T1 SET NED_CER = ( SELECT T2.NED_CER FROM RABMAC.QUOTATION T2 WHERE T1.ORD_RB = T2.ORD_RB AND T1.ORD_ID = T2.ORD_ID AND T1.COMP_ID = T2.COMP_ID )



cmc





"Raquel Rodriguez" cc: Sent by: "DB2 Data Subject: [DB2-L] Help with writing query.... Base Discussion List"



01/19/2005 11:22 AM Please respond to "DB2 Database Discussion list at IDUG" [...] 7347 52 21_Re: optimization hint10_Grace Chen20_grace.chen@CELERO.CA31_Wed, 19 Jan 2005 10:55:33 -0700536_us-ascii Hi Pat,

Thanks for your document which is really help. Now I have the problem to get the HINT to be used. I got SQLCODE +395 and reason code 32 which means invalid CREATOR or TNAME. We are running DB2 V8 compatibility mode which I still use the V7's plan table since the plan table migration for V8 is in New Function Mode. I tested it in the New Function Mode with V8 plan table without problem. I suspect this error only happens on V8 CM. Is there anyone use HINT on V8 Compatibility Mode? Is there anyway we can [...] 7400 261 12_Re: dsnutils15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Wed, 19 Jan 2005 12:58:19 -0500585_iso-8859-1 Tom, I feel for you. We had a similar upgrade story last year. The consultants said they could not certify the upgrade once we deviated from the packaged solution for the upgrade. I helped the DBA assigned to the upgrade by building several REXX /ISPF tools to help him. Several of the tools operated outside of the PS upgrade process. I do not know what the final "CERTIFCATION" result was but we accomplished the final production upgrade on the cut over weekend in approximately one third of the time it would have taken using the plain vanilla upgrade processes. We [...] 7662 66 32_Re: synonym , i don't understand13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 19 Jan 2005 13:00:59 -0500582_- Hi Billy,

You get -204 SQLCode, right?

Synonyms are not shareable.

What is the authid for the BIND that works? Unless it is TEST, I don't understand how the BIND can work.

I'm curious. What are you using Synonyms to accomplish?

In my book, synonyms are pretty much obsolete after the Qualifier parameter came along -- it allows one to tell the BIND which table qualifiers to use. I guess they could be still useful to users for shortening table names in interactive SQL. I haven't seen a need for them in PRODUCTION code for quite a while. [...] 7729 238 12_Re: dsnutils14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM31_Wed, 19 Jan 2005 13:08:13 -0500505_us-ascii I worked on a PSOFT upgrade project for about 6 months about 18 months ago. At the time, we would have liked to use the DSNUTILS stored procedure in the conversion because, if I recall correctly, there are many temp/work tables that would have benefited from good stats but the tables were empty at the beginning and end of the conversion process. It was a moot point whether the in-stream runstats would have been beneficial because the system programmers were not willing to let us do it. [...] 7968 429 12_Re: dsnutils20_Friedman, Avram (IT)32_Avram.Friedman@MORGANSTANLEY.COM31_Wed, 19 Jan 2005 14:04:08 -0500532_us-ascii Comment about DSNUTILS (not People Soft related) We do not use People Soft as far as I know

We had a request from a distributed UNIX application team to install and activate DSNUTILS. There requirement was that they had a table on the mainframe that had a load utility run against it daily. The application said they did not know how to log on to the mainframe, interface with our job scheduler etc. They wanted to submit there load utility from a UNIX command prompt and proposed DSNUTILS as a way to do this. [...] 8398 369 12_Re: dsnutils11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US31_Wed, 19 Jan 2005 13:20:59 -0600454_iso-8859-1 another interesting requirement is that the wlm environment must be defined with 'NO LIMIT' in terms of spawning address spaces

-----Original Message----- From: Friedman, Avram (IT) [mailto:Avram.Friedman@MORGANSTANLEY.COM] Sent: Wednesday, January 19, 2005 1:04 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] dsnutils



Comment about DSNUTILS (not People Soft related) We do not use People Soft as far as I know [...] 8768 281 34_Re: REQUEST FOR - REXX CAF ROUTINE11_Carl Nelson22_Carl.Nelson@ANTHEM.COM31_Wed, 19 Jan 2005 14:24:55 -0500444_us-ascii Thanks. The DSNTRACE showed 00F30034. I can resolve this.

Now for this next issue... This is the code that I have for SQL codes.

SQLC = D2X(SQLCODE,8) SQLC = X2C(SQLC) SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(0000)||COPIES(' ',78) SQLCA = SQLCA||COPIES(X2C(00),24)COPIES(' ',16) TIAR_MSG = X2C(0190)COPIES(' ',400) TEXT_LEN = X2C(00000050) ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN' SAY SUBSTR(TIAR_MSG,4,400) [...] 9050 128 41_DB2 Connect 8.1.3 and DB2 UDB for z/OS V813_Pearson, Eric19_Eric.Pearson@53.COM31_Wed, 19 Jan 2005 15:08:10 -0500352_- Anyone have this combination working yet?

I can connect to all my z/OS 7.1 subsystems.

Attempting to connect to a z/OS 8.1 gets:

SQL0332N There is no available conversion for the source code page "1252" to the target code page "UNKNOWN". Reason code "1". SQLSTATE = 57017

The z/OS 8.1 DB2 has: ASCCSID=1252 SCCSID=37 [...] 9179 17 39_Using DDF to go form AIX to DB2 on z/OS15_Matthew Sherman17_msherman@WBMI.COM31_Wed, 19 Jan 2005 14:32:29 -0600295_- We currently use DB2 Connect as our Gateway and we'd like to go from AIX to z/OS DB2 without it. Can this be done using DDF? I know we have a connection to AIX using DDF but I thought a separate gateway product was required for non-z/OS applications wanting to access mainframe DB2 data. [...] 9197 59 32_Re: synonym , i don't understand14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Jan 2005 21:02:18 -0000422_us-ascii A synonym only exists for the user that created it

SO

If you are "test" then you CAN select from TEST.TABLE

If you are anyone else then you cannot as YOUR synonym does not exist. Synonyms are not like views or aliases



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 9257 15 21_Re: optimization hint15_Patrick Bossman18_bossman@US.IBM.COM31_Wed, 19 Jan 2005 15:04:41 -0600605_- Hi Grace, Can you open a PMR/ETR? I think we'll have to get some information from you to look into this further.

Regards, Pat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9273 42 21_Re: optimization hint10_Grace Chen20_grace.chen@CELERO.CA31_Wed, 19 Jan 2005 14:54:13 -0700439_us-ascii Pat,

I opened ETR on IBM and will keep you posted.

Thanks, Grace

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Patrick Bossman Sent: January 19, 2005 2:05 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] optimization hint

Hi Grace, Can you open a PMR/ETR? I think we'll have to get some information from you to look into this further. [...] 9316 41 43_Re: Using DDF to go form AIX to DB2 on z/OS12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 19 Jan 2005 14:33:19 -0800509_us-ascii You should be able to go from AIX to Z/OS DB2 with no problems. I'm supporting about 80 AIX servers that do exactly that right now with DB2 Connect. It's just a matter of cataloging the connection to DB2 correctly. It's documented in the quick beginnings manual for AIX. If your gateway is on another box from the AIX box, you'll need the connection software from the other box to the AIX box (can't remember the exact name of the DB2 Connect component at this moment as I never use a gateway). [...] 9358 19 32_Re: synonym , i don't understand12_Billy Larsen19_billarsen@YAHOO.COM31_Wed, 19 Jan 2005 17:02:12 -0600366_- thank you Phil , Dave

Dave, We use synonyms to give an unique Qualifier to tables with different creator. Exemple : Package PGMA access tables from different applications : ACCOUNT.T1 ACCOUNT.T2 COMMON.T8 TREASURE.T0

the synonyms PROD.T1 , PROD.T2 PROD.T8 PROD.T0 are created and the Bind statement is : BIND ... OWNER(PROD) QUALIFIER(PROD) ... [...] 9378 106 47_Cache dynamic statements x Static SQL Profiling19_Luiz Roberto Foroni27_lforoni@CAIXASEGUROS.COM.BR31_Wed, 19 Jan 2005 21:34:05 -0300859_iso-8859-1 Hi listers

What diferences about cache dynamic statements and static sql profiling ?

In which situation one is more efficient than another ?

Is there any technical article about this topic ?



Thanks in advance

Luiz Roberto Foroni

DBA DB2 Z/OS









=========================================================================== ===========================================================================

" Esta mensagem e seus anexos podem conter informação confidencial e / ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nestas informações. Se você recebeu esta mensagem por engano, por favor, avise o remetente, [...] 9485 66 46_Re: CLI/ODBC/JDBC static profiling dynamic SQL12_HEPP SHERY C17_schepp@SRPNET.COM31_Wed, 19 Jan 2005 16:54:24 -0700604_us-ascii Hi Luiz- funny you should ask the question about the differences between Cache dynamic statements x Static SQL Profiling.

I sent the note below out over a week ago and received no response. We do have dynamic cache enabled. Basically I think it enables to avoid the prepare required on dynamic sql. It is my understanding that static sql profiling allows you to freeze your dynamic access path making it static sql. In this situation your access path would only change if you're statement changes or if you rebind the package. Whereas with dynamic cache your sql will be re-prepared [...] 9552 249 46_Re: CLI/ODBC/JDBC static profiling dynamic SQL15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Thu, 20 Jan 2005 11:18:17 +1100303_US-ASCII Isn't this a UDB (for small things) only feature ? There is often a lot of confusion as all the DB2s are called UDB but lets say some are more UDB than others. I have sometimes got several pages into an article before realising that it is talking about DB2 for small things platform only. [...] 9802 257 46_Re: CLI/ODBC/JDBC static profiling dynamic SQL12_HEPP SHERY C17_schepp@SRPNET.COM31_Wed, 19 Jan 2005 17:24:22 -0700488_us-ascii







Hi Richard- The article in IDUG solutions journal specifically states for Z/OS- however I've yet to hear from someone using it in Z/OS





Regards, Shery Hepp





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Richard Simpson Sent: Wednesday, January 19, 2005 5:18 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] CLI/ODBC/JDBC static profiling dynamic SQL [...] 10060 319 12_Re: dsnutils13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 19 Jan 2005 17:30:54 -0800565_us-ascii Just curious, about DSNUTILS... Is there some security or other kind of risk in making it generally available? Installation job DSNTIJSG grants it to PUBLIC, so I did that. I had to add a few DD statements to my APF authorized WLM proc in order to run IVP job DSNTEJ6U, but that seemed like no big deal. Nobody else is using it here, but for future reference I'd like to know what the objections are. Also, any guess as to the implications of running it with NUMTCB=8? I don't plan to create any more application environments until a stored procedure [...] 10380 115 12_Re: dsnutils25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Wed, 19 Jan 2005 17:32:29 -0800565_us-ascii I think you could get into serialization errors with NUMTCB > 1 --- "Taddei, Cathy" wrote:

> Just curious, about DSNUTILS... Is there some security or other kind of > risk in making it generally available? Installation job DSNTIJSG grants > it to PUBLIC, so I did that. I had to add a few DD statements to my APF > authorized WLM proc in order to run IVP job DSNTEJ6U, but that seemed > like no big deal. Nobody else is using it here, but for future > reference I'd like to know what the objections are. Also, any [...] 10496 130 12_Re: dsnutils12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 19 Jan 2005 17:58:24 -0800317_us-ascii Cathy, Understand that DSNUTILS allows you to run almost any utility that DB2 provides. So if you don't care that anybody can run any DB2 utility, then it's no big deal. If you don't want the average joe running reorg's, repairs, or whatever's against anything they desire, then you need to protect it. [...] 10627 46 34_Re: REQUEST FOR - REXX CAF ROUTINE14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jan 2005 20:23:41 -0600839_- http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0008A&L=DB2-L&P=R9404&I=- 3 with a minor error corrected in http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0307C&L=DB2-L&P=R6273&I=- 3

James Campbell

On Wed, 19 Jan 2005 14:24:55 -0500, Carl Nelson wrote:

>Thanks. The DSNTRACE showed 00F30034. I can resolve this. > >Now for this next issue... >This is the code that I have for SQL codes. > >SQLC = D2X(SQLCODE,8) >SQLC = X2C(SQLC) >SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(0000)||COPIES(' ',78) >SQLCA = SQLCA||COPIES(X2C(00),24)COPIES(' ',16) >TIAR_MSG = X2C(0190)COPIES(' ',400) >TEXT_LEN = X2C(00000050) >ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN' >SAY SUBSTR(TIAR_MSG,4,400) > > It does not fill in all the blanks. It shows only this. Appears that my >code above is not [...] 10674 48 21_Re: optimization hint12_Cianci, Nick19_nick.cianci@EDS.COM31_Thu, 20 Jan 2005 14:47:02 +1100386_- Patrick,

was chatting with some-one the other day about hints, and the word was that hints did not effect table join order. I notice that this was not in the limitations in your presentation.

Do you know if this is a limitation of hints in V7 & / or V8?

Cheers, Nick F. Cianci IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for OS/390 [...] 10723 24 41_"dataset placement does not matter !" guy12_Billy Larsen19_billarsen@YAHOO.COM31_Thu, 20 Jan 2005 01:54:22 -0600436_- Dear all, Seeing recently a thread speaking about ACS routine , i also check our routines and what a surprise when the DASD guy tells me : "dataset placement does not matter these days, it was a problem 10+ years ago ! " , and go to explanation with UCB, controllers , cache ...etc .. I am not DASD expert but I know this is not true,i give him recents DB2 documentation ... but he is still convinced that i live in the past :-) [...] 10748 149 34_Re: Re-using OBID's in DB2 on Z/OS24_Slegtenhorst, A (Astrid)29_A.Slegtenhorst@RN.RABOBANK.NL31_Thu, 20 Jan 2005 09:23:59 +0100733_iso-8859-1 Sally,

Appologies for the late reaction but thanks for your input. REORG/MODIFY works fine.

Regards, Astrid Slegtenhorst

-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]namens sally.mir@WACHOVIA.COM Verzonden: vrijdag 14 januari 2005 15:21 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Re: [DB2-L] Re-using OBID's in DB2 on Z/OS



If the OBID you want to reuse belongs to a table that has been dropped, you must first REORG the tablespace that contained the dropped table to remove all traces of that OBID, then MODIFY RECOVERY to eliminate any image copies that could be used to recover the tablespace to an image that contained that OBID. [...] 10898 220 45_Re: "dataset placement does not matter !" guy15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Thu, 20 Jan 2005 19:52:12 +1100574_US-ASCII Interestingly, with modern DASD and a well configured SMS setup, he is becoming increasingly correct.

There are still exceptions, even many of them where the DASD is older or the SMS not well configured, but they are growing less.

One the disks that MVS sees became "Virtual" and then the limitation of one IO per pack at once was removed via PAV, the limitations became those of the backend store, and the backend stores are pretty amazing in the latest stuff. Couple that with SMS getting smarter and the need to hand place datasets goes down. [...] 11119 75 45_Re: "dataset placement does not matter !" guy14_Andy Lankester30_andy.lankester@CDBSOFTWARE.COM31_Thu, 20 Jan 2005 09:06:16 -0000674_windows-1250 You should insist that your duplicated datasets (logs/BSDS) go on to physically separate 'boxes', particularly if they are being mirrored.

Andy

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Billy Larsen Sent: 20 January 2005 07:54 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] "dataset placement does not matter !" guy



Dear all, Seeing recently a thread speaking about ACS routine , i also check our routines and what a surprise when the DASD guy tells me : "dataset placement does not matter these days, it was a problem 10+ years ago ! " , and go to explanation with [...] 11195 192 19_Re: BMCDSN in batch0_20_Gerhard.Heiss@RUV.DE31_Thu, 20 Jan 2005 10:58:11 +0100783_us-ascii Raymond,

you might consider using IFI to let your programs issue commands to DB2. Below is a quick example, coded in REXX

Regards Gerhard

/* REXX */ SSID = 'DB2T' RET=CONN_DB2(SSID) ; IF RET^=0 THEN EXIT RET

CALL IFI '-DIS UTIL(*)'

ADDRESS DSNREXX "DISCONNECT" EXIT /*--------------------------------------------------------------------*/ IFI: ARG CMD /* REMOVE ENCLOSING QUOTES */ IF LEFT(CMD,2) = ""'" & RIGHT(CMD,2) = "'"" THEN CMD = SUBSTR(CMD,2,LENGTH(CMD)-2) ELSE IF LEFT(CMD,2) = """'" & RIGHT(CMD,2) = "'""" THEN CMD = SUBSTR(CMD,3,LENGTH(CMD)-4) COMMAND = SUBSTR("COMMAND",1,18," ") /****************************************************************/ /* SET UP THE IFCA, RETURN AREA, AND OUTPUT AREA FOR THE */ /* IFI COMMAND [...] 11388 37 42_Re: Simple query taking forever to run....33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 20 Jan 2005 04:19:20 -0600468_- Raquel

First of all, I thought access path was using list prefetch, because in some cases list prefetch can switch to a tablespace scan. So reading all the pages with that big rows will last.

Compression will give you fewer pages, meaning fewer pages to scan.

As Dr Ebert said, if those 4 columns have a very low correlation than with these colcards, they would address over 1 billion rows. So, what is the colcard of the concatenated key? [...] 11426 268 46_Re: CLI/ODBC/JDBC static profiling dynamic SQL19_Luiz Roberto Foroni27_lforoni@CAIXASEGUROS.COM.BR31_Thu, 20 Jan 2005 10:29:30 -0300526_iso-8859-1 Hi Richard,

There is an article in DBAzine about use of Stored Procedures on Z/OS

Regards

Luiz Roberto Foroni DBA DB2 Z/OS ---------------------- Encaminhado por Luiz R Foroni/MATRIZ/SASSE em 20/01/2005 10:20 ---------------------------



Richard Simpson @IDUGDB2-L.ORG> em 19/01/2005 22:18:17

Favor responder a DB2 Database Discussion list at IDUG

Enviado Por: DB2 Data Base Discussion List [...] 11695 75 34_Re: Using a trigger to get a value0_19_mike.holmans@BT.COM31_Thu, 20 Jan 2005 12:41:08 -0000413_iso-8859-1 Peter,

Thanks very much. I tried it on our DB2 for z/OS V7 system and it too worked like a charm.

I'm a bit antsy about the various stages of trigger this process goes through, but since the triggered UPDATE and its own after trigger count as part of the original UOW, and therefore failures down the line will rollback everything, I'm probably just scaring myself for the hellavit. [...] 11771 24 21_Re: optimization hint15_Patrick Bossman18_bossman@US.IBM.COM31_Thu, 20 Jan 2005 07:23:15 -0600401_- Hello Nick, Your ascertion is false. Join sequence can be changed. Changing join sequence manually in the plan_table is one of the more complicated things to do... You have to remember to change PLANNO for all of the affected tables, check all the sort flags, make sure the prefetch flags (if any were set) still make sense. The context of any indexes which were being used might be different. [...] 11796 60 19_Re: BMCDSN in batch11_Chris Bowen22_chris.bowen@MACRO4.COM31_Thu, 20 Jan 2005 07:20:01 -0600611_- Hi Ray,

If you're prepared to countenance a two step proc then you could try something like this: -

//RAYSBMC PROC DB2SYS= //BMCBLD EXEC PGM=IRXJCL,PARM='BMCBUILD &DB2SYS' //SYSEXEC DD DSN=your rexx library,DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSTSIN DD DUMMY //SYSPRINT DD SYSOUT=* //SYSUT2 DD DSN=&&BMCPARMS,DISP=(NEW,PASS), // etc etc //BMCDSN EXEC PGM=IKJEFT1A,DYNAMNBR=20 //* //BMCUTILB DD DISP=SHR,DSN=SA1.P.BMC.AUTHLOAD // DD DISP=SHR,DSN=BMC.LOAD //SYSTSPRT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD DSN=&&BMCPARMS,DISP=(OLD,DELETE) // DD DDNAME=RUNPARMS [...] 11857 435 12_Re: dsnutils20_Friedman, Avram (IT)32_Avram.Friedman@MORGANSTANLEY.COM31_Thu, 20 Jan 2005 08:56:59 -0500445_us-ascii Concerning NUMTCBS= non 1 DSNUTILS writes to several allocated message files added via DD to the WLM Stored Procedure Proc If two tasks try to open the SYSOUT file the WLM SP address space will 'fall down and go boom' (I forget the various abend codes) This is actually a fairly common problem. In the High Level Language environment for user coded stored procedures there are some LE options to address this, MSGFILE for example. [...] 12293 335 45_Re: "dataset placement does not matter !" guy14_Krnic, Ivan B.18_ikmrb@ALLSTATE.COM31_Thu, 20 Jan 2005 08:13:28 -0600442_us-ascii "...well configured SMS setup, he is becoming increasingly correct..."

Now there is a topic one can talk about for days.

IVAN B. KRNIC DBA





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Richard Simpson Sent: Thursday, January 20, 2005 2:52 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] "dataset placement does not matter !" guy [...] 12629 441 45_Re: "dataset placement does not matter !" guy20_Friedman, Avram (IT)32_Avram.Friedman@MORGANSTANLEY.COM31_Thu, 20 Jan 2005 09:26:11 -0500601_us-ascii Two remarks on this subject.

1. The business of data processing is processing data. Data demographics like, size, access patterns, update frequencies, backups, recoveries, reorgs and yes even data set placement will by definition always be relevant issues.

2. Virtual DASD and PAVS is not a cure all ... for example we found VSAM Stripping of our logs (wow a placement issue) still provided great performance benefits in our state of the art, highly tuned, large and busy DASD environment. Also (hope I am not jinxing anything here) no one has suggested striping our BSDS. [...] 13071 73 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?0_25_Hayden_Jones@GRAINGER.COM31_Thu, 20 Jan 2005 08:54:02 -0600501_us-ascii Wayne, You seem to have a pretty good grasp of the problem since you have also had to deal with it. Here are some of the significant factors.

1. Cursors declared both with and without HOLD seem to be irrelevant. In either case the catalog locks seem to be held over the commit. This is probably because the SQL requestor doesn't hold the locks. A program within DB2 is doing the bind and is holding the locks until the end of the request (my guess. I don't know this for a fact). [...] 13145 12 19_LOAD LOG NO logging5_Patty32_patrizia.andreetta@ELECTROLUX.IT31_Thu, 20 Jan 2005 08:39:52 -0600623_- Does anybody known if in a LOAD with LOG NO logging occurs during the ENFORCE phases when the RI violations and duplicates are deleted ? Kind Regards,

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 13158 87 45_Re: "dataset placement does not matter !" guy35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 20 Jan 2005 09:39:40 -0500401_iso-8859-1 As usual, it's very much an "it depends" situation. A blanket statement that it just does not matter, comes from ignorance.

If you are getting good performance, with ZERO iosq (requires dynamic PAV), then your environment is probably fine from an overall dataset placement performance perspective.

However, I still see many environments showing I/O performance glitches. [...] 13246 24 22_DB2 Connect on Solaris14_Toppins, Smike21_smike.toppins@GWL.COM31_Thu, 20 Jan 2005 08:16:40 -0700343_US-ASCII Let me preface this with the fact that I'm not a UNIX-type person...

I'm trying to install DB2 Connect on a Solaris box. When I try to start the Configuration Assistant, I get the message " db2javit: not found". I have not had much luck trying to find any hits on IBM's site. Any suggestions would be greatly appreciated. [...] 13271 20 23_DDF Transmission Limits10_Jorg Lueke21_jorg_lueke@EFUNDS.COM31_Thu, 20 Jan 2005 09:28:47 -0600373_- I am hoping someone can guide me to aplce where I can deepen my understanding of how the DDF works.

We have four subsystems, 4 DDF's, each has it's own IP address on the zOS server. Good enough. When I want to know the maximum capacity for sending and receiving data for each DDF is htis limited just by the hardware, by some limits in DDF, or a combination? [...] 13292 97 26_Re: DB2 Connect on Solaris12_Alex Pauliah28_Apaulia1@CAPGEMINIENERGY.COM31_Thu, 20 Jan 2005 09:30:22 -0600287_us-ascii Smike,

Are you running as the install DB2 user or root ?? If you are running as root, open another session and login as the install user. there is a logon script that you run (db2profile.sh ... ) to set up all the paths that may not have run when you logged in. HTH [...] 13390 88 88_Re: [Maybe spam] [Probability = 57%] Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS16_Sherman, Matthew17_MSherman@WBMI.COM31_Thu, 20 Jan 2005 10:09:12 -0600430_us-ascii Thanks for the response Myron. However, I'm trying to figure out how to do this without DB2 Connect only using DDF. Any ideas?

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Myron Miller Sent: Wednesday, January 19, 2005 4:33 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [Maybe spam] [Probability = 57%] Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS [...] 13479 174 12_Re: dsnutils10_Jwkwan200025_jwkwan2000@HOUSTON.RR.COM31_Thu, 20 Jan 2005 10:14:12 -0600577_iso-8859-1 I agree with your comment about letting people running DSNUTILS without proper control will be a disaster. I just don't understand how 'a really smart hacker can use it to invade your DB2...' can happen. Unless someone modifies the DSNUTILS code on the host computer which should have been protected by RACF or other security tool, it is very unlikely a hacker can access your DB2 systems and Z/OS files 'at will'. One way I can see your point is someone can potentially destroy your data or modify your data by repair, unload and load. But this has nothing to [...] 13654 84 43_Re: Using DDF to go form AIX to DB2 on z/OS12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 20 Jan 2005 08:17:46 -0800564_us-ascii Matthew- You need DB2 Connect or something similar to it, to cross platforms. You can go mainframe to mainframe using DDF without a Connect product. tim --- Myron Miller wrote:

> You should be able to go from AIX to Z/OS DB2 with > no problems. I'm supporting > about 80 AIX servers that do exactly that right now > with DB2 Connect. It's > just a matter of cataloging the connection to DB2 > correctly. It's documented > in the quick beginnings manual for AIX. If your > gateway is on another box from > the AIX box, [...] 13739 126 88_Re: [Maybe spam] [Probability = 57%] Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS16_Sherman, Matthew17_MSherman@WBMI.COM31_Thu, 20 Jan 2005 10:24:09 -0600549_us-ascii That is my understanding also. We have someone here (my supervisor) who worked with DB2 back at about Version 4 and says when she set up the initial DDF connections to go to AIX, there was a way to set it up to come back, that was documented in the DDF Redbook. I've started to look through the Redbook, and see only evidence confirming that you DO need DB2 Connect (or another gateway product) to come back to z/OS from AIX. But I've just started to look through the Redbook and thought I might be missing something. Also, since it's [...] 13866 28 38_Re: AS/400 DB2 Bind Parms from z/Os v712_Roger Miller19_millerrl@US.IBM.COM31_Thu, 20 Jan 2005 10:33:22 -0600644_- This looks like the top page with DB2 for iSeries information http://www.ibm.com/servers/eserver/iseries/db2/db2tch_m.html

I could not find the BIND parameters and need to catch a plane.

Roger Miller

On Wed, 19 Jan 2005 10:13:01 -0600, Vickers, Mark wrote:

>Folks, >We are using the copy to migrate thru the environments on z/os. >Using DRDA/DDM to the AS/400, so for a program running on z/os, and >accessing AS/400 data, we do a remote bind from z/os to the AS/400. >As yet, I have not been able to find a list of bind parms which are >valid/specific to the AS/400 ? >thanks, >Mark [...] 13895 196 55_Re: Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 20 Jan 2005 08:57:38 -0800587_us-ascii I assume you're reading the "WOW!" book? AIX versions of UDB had DB2 Connect imbedded in them, so she might not have known that she was really using DB2 Connect services. Especially since it would work without purchasing/enabling a Connect license. I don't think you even got a license warning message in the old days. There should be a lot of stuff in the db2-l archives from Leon Katsnelson, who was the IBM product "owner" regarding this. Maybe he'll join in with this conversation as well, and give us the official line from IBM regarding current license requirements. [...] 14092 198 43_Re: Using DDF to go form AIX to DB2 on z/OS12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM31_Thu, 20 Jan 2005 11:22:48 -0600373_us-ascii Hi Sherman, If you have a DB2 database on the AIX machine, assuming it is not Personal Edition, then you probably already have the DB2 Connect code embedded within the database software. If you are trying to come from an AIX machine with no DB2 software on it whatsoever, AFAIK you cannot do that. DB2 Connect at a minimum is required to talk to DB2 on z/OS. [...] 14291 152 12_Re: dsnutils11_Matt Kelley18_mgk333@HOTMAIL.COM31_Thu, 20 Jan 2005 11:24:19 -0600472_- DB2 still checks for required authorizations when you invoke a utility via DSNUTILS, just as it does if I run DSNUTILB in batch. DSNUTILS does not allow me to run things I'm not authorized for.

And yes, DSNUTILS runs APF-authorized, but so do many DB2 components that I access directly or indirectly, locally or remotely. To say that opening up DSNUTILS exposes you to hackers seems to me to be the same as saying that opening up DDF exposes you to hackers. [...] 14444 57 45_Re: "dataset placement does not matter !" guy12_Billy Larsen19_billarsen@YAHOO.COM31_Thu, 20 Jan 2005 11:24:35 -0600586_- Here is an extract of the RMF report on our DB2 volumes (one storage group)

After this , i understand that we are not PAV , we experience IOSQ , the controller corresponds to "LCU" , right ?

Thank you for your explanation





- DEVICE AVG AVG STORAGE DEV DEVICE VOLUME PAV LCU ACTIVITY RESP IOSQ GROUP NUM TYPE SERIAL RATE TIME TIME SGDB2PC 1040 33903 D11100 0047 0.021 13.0 0.0 SGDB2PC 1041 33903 D11110 0047 0.074 15.2 0.0 SGDB2PC 1042 33903 D11120 0047 0.014 3.2 0.0 SGDB2PC 1043 33903 D11130 0047 0.041 7.7 0.0 SGDB2PC 1044 33903 D11140 0047 [...] 14502 77 31_Re: Help with writing query....18_Vasu, Suresh {PBG}21_Suresh.Vasu@PEPSI.COM31_Thu, 20 Jan 2005 13:09:13 -0600437_- It should be UPDATE RABMAC.DEHTRO T1 SET NED_CER = (SELECT T2.NED_CER FROM RABMAC.QUOTATION T2 WHERE T1.ORD_RB = T2.ORD_RB AND T1.ORD_ID =T2.ORD_ID AND T1.COMP_ID =T2.COMP_ID ) WHERE EXISTS (SELECT 1 FROM RABMAC.QUOTATION T2 WHERE T1.ORD_RB = T2.ORD_RB AND T1.ORD_ID =T2.ORD_ID AND T1.COMP_ID =T2.COMP_ID )

If you are not specifying the 'WHERE' clause it would update the non-matching rows with null values on that column. [...] 14580 85 45_Re: "dataset placement does not matter !" guy35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 20 Jan 2005 14:30:08 -0500450_iso-8859-1 Yes, LCU is the control unit. Hopefully it's obvious where your larger performance problems are. So in your environment dataset placement means a LOT. D11170 is killing you..

Regards, Joel ----- Original Message ----- From: "Billy Larsen" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, January 20, 2005 12:24 PM Subject: Re: [DB2-L] "dataset placement does not matter !" guy [...] 14666 127 46_Re: CLI/ODBC/JDBC static profiling dynamic SQL9_Tim Baker22_Tim_Baker@MANULIFE.COM31_Thu, 20 Jan 2005 14:56:12 -0500570_US-ASCII Thought I would put my two cents in. We turned on the Dynamic SQL caching on our Z/OS machine. This was able to capture a lot of the dynamic SQL coming from our open edition applications (AIX, NT). Significant reductions in our CPU usage were accomplished using this. We have also experimented with the Static profiling of the same statements to the Z/OS DB2 regions. Simple test have shown that it is much faster with approximately a 10-15% reduction in the CPU used to do the same query. Pretty easy thing to accomplish you put the INI file into capture [...] 14794 55 74_DB2 Connect and MS Service Pack 2 - Anyone having a problem on Windows XP?16_Wolters, Jeff B.24_Jeff.Wolters@PGNMAIL.COM31_Thu, 20 Jan 2005 15:39:06 -0500410_US-ASCII I am currently investigating the compatibility of DB2 Connect V7.2 FP10a and Microsoft's Service Pack 2 on Windows XP platforms.

I'm running into this problem with DB2 Connect V7.2 RTC (Run Time Client) when trying to invoke the Command Center:

"db2cctr.exe - Application Error" "The application failed to initialize properly (0xc0000005). Click on OK to terminate the application" [...] 14850 22 78_Re: DB2 Connect and MS Service Pack 2 - Anyone having a problem on Windows XP?12_Bob Pingston24_Bob.Pingston@ALTICOR.COM31_Thu, 20 Jan 2005 15:45:18 -0500709_US-ASCII What type of XP system are you running? I think if you are 64 bit XP on the client side, your server must also be 64 bit OS.



Bob Pingston Alticor Inc. Bob_Pingston@Alticor.com 616.787.1186 "It's all about the data"

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 14873 108 45_Re: "dataset placement does not matter !" guy16_Graeme St. Clair23_Graeme.St.Clair@HDS.COM31_Thu, 20 Jan 2005 13:09:12 -0800270_- Well, is it? It does jump out at you, but it's only hitting .031 I/O's per sec (right? It's been a few years!). If so, that's only one I/O every 32 seconds. The rest looks OK to me, tho maybe one or two devices might be swapped from 0047 to 0048 to even them up. [...] 14982 52 12_Re: dsnutils13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 20 Jan 2005 13:20:38 -0800569_us-ascii Thanks to all who responded to my question about DSNUTILS. Now all I need to do is talk my boss into buying the DB2 utilities so we would have something to run. :)

I agree that, were I to work in a larger shop in my next sysprog instantiation, I wouldn't want utilities running haphazardly. However, in the last 7 years, I have seen the mainframe increasingly marginalized, and I know that a lot of executives hold the opinion that "the mainframe is difficult to use". I take every opportunity to try to change that opinion, and make the mainframe [...] 15035 219 55_Re: Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS16_Sherman, Matthew17_MSherman@WBMI.COM31_Thu, 20 Jan 2005 15:30:00 -0600295_us-ascii Actually I'm not reading that book, but it sounds like you nailed it by mentioning that older versions of UDB for AIX had DB2 Connect built in and didn't require a license. I'll do some searches in the archives and see if I can pull anything up.

Thanks for the information. [...] 15255 201 43_Re: Using DDF to go form AIX to DB2 on z/OS12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM31_Thu, 20 Jan 2005 11:22:48 -0600373_us-ascii Hi Sherman, If you have a DB2 database on the AIX machine, assuming it is not Personal Edition, then you probably already have the DB2 Connect code embedded within the database software. If you are trying to come from an AIX machine with no DB2 software on it whatsoever, AFAIK you cannot do that. DB2 Connect at a minimum is required to talk to DB2 on z/OS. [...] 15457 100 26_Re: DB2 Connect on Solaris12_Alex Pauliah28_Apaulia1@CAPGEMINIENERGY.COM31_Thu, 20 Jan 2005 09:30:22 -0600287_us-ascii Smike,

Are you running as the install DB2 user or root ?? If you are running as root, open another session and login as the install user. there is a logon script that you run (db2profile.sh ... ) to set up all the paths that may not have run when you logged in. HTH [...] 15558 354 55_Re: Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS15_Leon Katsnelson15_leon@CA.IBM.COM31_Thu, 20 Jan 2005 17:44:44 -0500469_US-ASCII Let me chime in and provide a definitive answer so that the people on this list don't have to invent one :-) I also want to preface my answer with a statement that I am not interested in discussing licensing or pricing in this list. I am simply trying to clarify some facts. If anybody on this list is interested in additional information in how DB2 Connect or other IBM products are licensed or priced, you need to contact your IBM sales representative. [...] 15913 95 33_Application User "Authentication"19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Thu, 20 Jan 2005 15:04:24 -0800517_us-ascii

> DB2 UDB 8.1 FP5 (waiting for FP8) on AIX 5.3. > > The box is acting as a database server. The application (under > development by a 3rd party vendor under contract with a subsidiary) is > on it's own server box. I have NO technical information on the server > app - it's all at a remote site two timezones away with a remote IT > manager that doesn't think we need to know much. > > The Unix Admin tells me that he has to set up every application user > on the database server box. Apparently [...] 16009 117 45_Re: "dataset placement does not matter !" guy12_Cianci, Nick19_nick.cianci@EDS.COM31_Fri, 21 Jan 2005 10:36:34 +1100527_- I'm no DASD guru, so I could be talking out of my back-side here.

However seeing the IOSQ times on D11170 got me to thinking, either there a lot of IO happening every time you are going to D11170 ... or maybe there is predominantly a fair bit of pre-fetch happening to that pack. I'm trying to remember the mechanism but I believe if you are pre-fetching 32 pages, that these would be 32 x 4K chained I/Os. If the pages were not in disk cache, there could be a bank up effect that might account for the high IOSQ [...] 16127 74 45_Re: "dataset placement does not matter !" guy0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 20 Jan 2005 17:48:18 -0600685_US-ASCII Hello,

How does one generate this report?

Thanks.

Stg



|---------+----------------------------> | | Billy Larsen | | | | | | Sent by: DB2 Data| | | Base Discussion | | | List | | | | | | | | | | | | 01/20/2005 11:24 | | | AM | | | Please respond to| | | DB2 Database | | | Discussion list | | | at IDUG | |---------+----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: DB2-L@WWW.IDUGDB2-L.ORG | | cc: | | Subject: Re: [DB2-L] "dataset placement does not matter !" [...] 16202 127 12_Re: dsnutils14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM31_Thu, 20 Jan 2005 17:48:20 -0600533_us-ascii DSNUTILS needs to be NUMTCB=1 because it DSNUTILB can only read from SYSIN and write to SYSPRINT, unlike many of the DFP utilities that support alternate DD names. If you have NUMTCB > 1 and a second request for DSNUTILS comes in while the first is still running there will be, as others have mentioned, serialization issues, which may include abends accessing the sysprint file, and any other files required by the utility. Wayne Driscoll Product Developer Western Metal Supply NOTE: All opinions are strictly my own. [...] 16330 171 12_Re: dsnutils14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM31_Thu, 20 Jan 2005 17:54:30 -0600628_us-ascii Myron, All that the DSNUTILS stored procedure does is build utility control statements, dynamically allocate files (if required) then attach DSNUTILB. Just because anyone can call DSNUTILS doesn't mean that they can run any utility that they want. Standard DB2 utility authorization checks are still performed. Also, since DSNUTILB has a strict syntax checker, a hacker can't use DSNUTILS to "run almost any program it wants." Also, even though it does require an APF authorized WLM AE, all that means is that any and all libraries in the STEPLIB must be APF authorized, but DSNUTILS doesn't do anything to bypass [...] 16502 71 12_Re: dsnutils11_Chris White18_crick616@YAHOO.COM31_Thu, 20 Jan 2005 18:03:21 -0600366_- Cathy,

I agree completely with what you said about dsnutils and mainframe usage. If the mainframe is going to survive (let alone compete) as a strategic part of IT architecture, we have to find ways to USE it, not prevent its use.

Chris White Caterpillar Inc.



These are just my opinions, not my employer's and blah, blah, blah... [...] 16574 58 21_Re: optimization hint12_Cianci, Nick19_nick.cianci@EDS.COM31_Fri, 21 Jan 2005 11:10:15 +1100289_- Thanks Patrick,

my "modus operandi" would be to use DB2 stat.s to influence the path I wanted in a test environment, and once that was obtained save it as a hint to use down the line. The trouble was that if the join sequence could change, we could quickly lose performance. [...] 16633 135 45_Re: "dataset placement does not matter !" guy12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 20 Jan 2005 16:15:54 -0800541_us-ascii Steve-

One easy way to do this online is from a TSO prompt. enter the RMFMON command. Note all the interesting statistics available. PF#6 gets you the disk information.

tim

--- Steve_Grimes@AISMAIL.WUSTL.EDU wrote:

> Hello, > > How does one generate this report? > > Thanks. > > Stg > > > |---------+----------------------------> > | | Billy Larsen | > | | | | COM> | > | | Sent by: DB2 Data| > | | Base Discussion | > | | List | > | | | | ORG> | > | | | > [...] 16769 135 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?0_35_paul.a.redhead@TRANSPORT.QLD.GOV.AU31_Fri, 21 Jan 2005 10:59:02 +1000362_us-ascii Hayden, I think in an earlier message Roger mentioned DDL. My understanding is that currently if 'your' SQL were running on a system and I issued a DROP TABLE statement where I included one of your tables, that I would get an SQLCODE of -904 on the catalog due to not being able to take an exclusive lock since 'your' SQL was holding a lock on it. [...] 16905 151 45_Re: "dataset placement does not matter !" guy35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 20 Jan 2005 23:31:35 -0500565_iso-8859-1 Yep, you're right. The column alignments are whacked in my email and I didn't notice that. But - whenever an application goes there, it's waiting a loooong time.

One vital piece of info we're missing here, is what the dasd subsystem is running on. If it's a RAID box, we have a very mixed performance bag. Some of the higher volume devices are showing pretty good response times, after you take out the IOSQ. D10220 is getting great performance with an excellent cache hit rate. Others are a bit elongated, indicating a high cache miss rate. [...] 17057 159 45_Re: "dataset placement does not matter !" guy35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 20 Jan 2005 23:42:43 -0500348_iso-8859-1 With the low I/O rate, as Graeme pointed out, it could be a prefetch effect. Control unit logic (depending on whose box it is) should detect the prefetch and stage (about a Cyl?) of data into the cache, so it's there for the next prefetch.. But with a low I/O rate, this could be part of the problem, rather than a viable solution. [...] 17217 84 25_Re: LOB Locks and LOCKMAX15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Fri, 21 Jan 2005 06:54:47 +0100340_us-ascii Phil,

actually, this looks like the same kind of problem that was discussed about 6 weeks ago. The subject was "NUMLKUS Exceeded for simple Unload".

To repeat it a little bit:

have a look at the DB2 Administration Guide, chapter 30, paragraph on LOB locks. The following is the text on LOB lock duration: [...] 17302 78 12_Re: dsnutils13_Michael Ebert18_mebert@AMADEUS.NET31_Fri, 21 Jan 2005 08:59:00 +0100586_US-ASCII Since DB2 V7, you can provide EXEC SQL statements in the Utility SYSIN Stream, so you can do other things than just run utilities through DSNUTILB or DSNUTILS. I don't see whether this causes any security issues, but then I don't think like a hacker. Furthermore, I wouldn't call the syntax checker strict; in fact the Utility SYSIN parsing seems to be rather primitive. I remember the PMR I opened with IBM because OLR ignored the STATISTICS clause. Turned out it would work if you put the MAPPING TABLE clause after the STATISTICS clause, but not the other way round... [...] 17381 12 45_Re: "dataset placement does not matter !" guy12_Billy Larsen19_billarsen@YAHOO.COM31_Fri, 21 Jan 2005 05:01:02 -0600514_- Joel , The disks are Hitachi 9009 , RAID5

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 17394 39 23_Re: LOAD LOG NO logging10_Novice DBA23_julia_chand@HOTMAIL.COM31_Fri, 21 Jan 2005 12:41:27 +0000710_- Hi as far as I know , there is no logging when you enforce the RI and duplicate deletion happens. But I m a novice in this DBA world. I may not be correct.





>From: Patty >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] LOAD LOG NO logging >Date: Thu, 20 Jan 2005 08:39:52 -0600 > >Does anybody known if in a LOAD with LOG NO logging occurs during the >ENFORCE phases when the RI violations and duplicates are deleted ? >Kind Regards, > >--------------------------------------------------------------------------------- >Welcome to the IDUG DB2-L list. To unsubscribe, go to [...] 17434 25 28_Subselects in DB2 UDB V8.1.616_Jaiwant Jonathan25_jjaiwant328@BELLSOUTH.NET31_Fri, 21 Jan 2005 07:47:00 -0600366_- Hi list, I noticed that when I run a query with a subselect like select colA from tableA where colA in (select ColX in tableX ),

DB2 does NOT flag an error if ColX is not a valid column name of tableX. On the other hand, it simply returns no rows found with SQLcode 0, which is incorrect and misleading. This error happens in DB2 UDB ver 8.1.6 on AIX. [...] 17460 147 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?0_25_Hayden_Jones@GRAINGER.COM31_Fri, 21 Jan 2005 08:18:11 -0600338_iso-8859-1 Paul, I'm not ready to buy that arguement for 2 reasons.

1. The second (third, forth, ......) time the same query runs, the statement is found in cache. No bind happens. No locks are taken on the catalog. Is the query LESS important the second or seventeenth time it runs than it was the first time?. Well, ... no. [...] 17608 36 21_Re: optimization hint15_Patrick Bossman18_bossman@US.IBM.COM31_Fri, 21 Jan 2005 08:16:56 -0600515_- Hi Nick, Based on your response, I think I my answer wasn't clear.

I thought you were asking if YOU could change the join sequence with an optimization hint. The answer is - YES. Whatever the join sequence is that is specified in the hint, we'll honor it or fail the hint (there are join dependencies in outer join, so if you violate the dependency, then you've provided an impossible hint). DB2 will not consider alternate join sequences unless the hint is fails (then we go through normal costing). [...] 17645 24 42_What is the DB2agentgp process on Solaris?12_Bob Jeandron21_bobjeandron@YAHOO.COM31_Fri, 21 Jan 2005 06:28:44 -0800414_us-ascii We are using the db2btree command to list DB2 Connect V8.1 FP4a processes on Solaris and have a process that I can't find anywhere,DB2agentgp. It looks like a gateway process that is pending something (termination)?

Thanks in advance.

__________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com [...] 17670 105 46_Re: What is the DB2agentgp process on Solaris?16_Sreedhar Bobbadi24_sbobbadi@DUKE-ENERGY.COM31_Fri, 21 Jan 2005 10:09:44 -0500718_us-ascii The following article should answere your question.

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0406qi/index.html

HTH Sreedhar







Bob Jeandron Sent by: DB2 Data Base Discussion List 01/21/2005 09:28 AM Please respond to DB2 Database Discussion list at IDUG



To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] What is the DB2agentgp process on Solaris?



We are using the db2btree command to list DB2 Connect V8.1 FP4a processes on Solaris and have a process that I can't find anywhere,DB2agentgp. It looks like a gateway process that is pending something (termination)? [...] 17776 108 46_Re: What is the DB2agentgp process on Solaris?16_Sreedhar Bobbadi24_sbobbadi@DUKE-ENERGY.COM31_Fri, 21 Jan 2005 10:13:54 -0500366_us-ascii A subagent that is currently performing work on behalf of the coordinating agent it is associated with. These processes provide intra-partition parallelism, i.e. the ability to execute a query in parallel within a database instance / partition

It will appear when the INTRA_PARALLEL dbm cfg parameter is set to YES.

HTH Sreedhar Bobbadi. [...] 17885 106 57_Re: Dynamic Queries with UR Still Take Locks - Necessary?12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 21 Jan 2005 10:04:10 -0600560_- The real complexities mean that I don't have a complete handle on it either, but I think that the facts differ a bit. If this is dynamic SQL, it's either though an ODBC or JDBC call or direct. There is a PREPARE statement, and this comes from the SQL requester. Unlocked, but consistent information would be nice, but it's a fairly tale. For the catalog, we tend to choose consistent, not unlocked. I think that data integrity should come first. I'm seeing other situations that don't have this type of locking issue, so there must be some differences. [...] 17992 73 36_SQL capture from client app. to Zos.0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Fri, 21 Jan 2005 08:49:39 -0800348_- Hi all, One of my users has a Cold fusion app. that will/can generate different dynamic SQL queries against a ZOS DB2 table. He would like to capture the queries for performance tuning and tracking how the application is being used.

Question, without any third party software available how can we capture the queries coming into DB2? [...] 18066 44 26_Re: DB2 Connect on Solaris14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 21 Jan 2005 09:51:49 -0700662_us-ascii That took care of the issue...Thanks!

SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Alex Pauliah Sent: Thursday, January 20, 2005 8:30 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Connect on Solaris

Smike,

Are you running as the install DB2 user or root ?? If you are running as root, open another session and login as the install user. there is a logon script that you run (db2profile.sh ... ) to set up all the paths that may not have run when you logged [...] 18111 414 55_Re: Re: [DB2-L] Using DDF to go form AIX to DB2 on z/OS16_Sherman, Matthew17_MSherman@WBMI.COM31_Fri, 21 Jan 2005 10:58:21 -0600333_us-ascii Thank you for the information, Leon, it is helpful.

So historically speaking, there was never any point in time (or version of DB2) where connections could have been made strictly through DDF, without using DB2 Connect from AIX. But there was a time when no separate DB2 Connect licensing was required, correct? [...] 18526 68 12_Re: dsnutils0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Fri, 21 Jan 2005 09:18:23 -0800564_- "the mainframe is difficult to use"!! what are those executives finding easy to use, Xbox, PS2? I find ZOS much easier to use compared to WINDOWS or UNIX. cliff:-)







-----Original Message----- From: Taddei, Cathy [mailto:Cathy.Taddei@PACIFICORP.COM] Sent: Thursday, January 20, 2005 1:21 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] dsnutils



Thanks to all who responded to my question about DSNUTILS. Now all I need to do is talk my boss into buying the DB2 utilities so we would have something to run. :) [...] 18595 28 28_DB2 for LUW: fixpack8 is out9_Nic Honan20_NicHonan@HOTMAIL.COM31_Fri, 21 Jan 2005 11:50:05 -0600397_- I see fix pack 8 is just out for DB2 UBD for LUW. It addresses some security issues. See: http://www.ibm.com/support/docview.wss? rs=71&context=SWB30&dc=D600&uid=swg21196289&loc=en_US&cs=utf-8&lang=en

The recommendation is: DB2 Clients FP7a installations should go fp8 but no pressing need DB2 Connect Gateways should go fp8 DB2 Servers should go fp8 DB2 XML Extenders should go fp8 [...] 18624 86 12_Re: dsnutils13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 21 Jan 2005 10:09:02 -0800570_us-ascii Here at Pacificorp, they find Oracle/Unix easy to use. In my last shop, they found SQL Server/Windows easy to use. I have pondered possible reasons for these prejudices against the mainframe, and I have observed that it has little to do with the underlying technology, and more to do with the people in charge of those technologies. I try to make z/OS easy to use by saying "yes we can" as often as possible, and suppress my curmudgeon tendencies. My competition is tough, though -- they're all younger, unreasonably confident, and disgustingly upbeat. :O [...] 18711 108 12_Re: dsnutils14_Krnic, Ivan B.18_ikmrb@ALLSTATE.COM31_Fri, 21 Jan 2005 12:41:05 -0600592_us-ascii Ditto !!! Also, what was the last time "Mainframe" was down due to virus intrusion ? Unheard of, yet very common on other platforms.







-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Maurice.C.BOLEY@ODOT.STATE.OR.US Sent: Friday, January 21, 2005 11:18 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] dsnutils



"the mainframe is difficult to use"!! what are those executives finding easy to use, Xbox, PS2? I find ZOS much easier to use compared to WINDOWS or UNIX. cliff:-) [...] 18820 144 12_Re: dsnutils13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Fri, 21 Jan 2005 12:58:05 -0600564_iso-8859-1 Let's face it in general, people (especially non technical users and managers) think: - if you want to add capacity to a LUW (Linux / Unix / Windoze) server, you just drop $5-10-20,000 on the table, and plug the new box in. But everyone know that upgrading a 'mainframe' is a major undertaking. - you can just plug that new LUW box into the wall and throw it under the receptionist's desk, a MF requires an expensive air conditioned, power conditioned, false floor, glass room. - Compare the price of DB2 license on LUW and DB2 z/OS. - Compare how [...] 18965 79 62_Re: What is the DB2agentgp process on Solaris? - Mark Horrocks14_Julie Horrocks22_agentlease@HOTMAIL.COM31_Fri, 21 Jan 2005 19:35:10 +0000713_- It will appear regardless of INTRA_PARALLEL being set on a EEE system. You will have more db2agntp when INTRA_PARALLEL is set to YES.

Mark Horrocks.



>From: Sreedhar Bobbadi >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] What is the DB2agentgp process on Solaris? >Date: Fri, 21 Jan 2005 10:13:54 -0500 > >A subagent that is currently performing work on behalf of the coordinating >agent it is associated with. These processes provide intra-partition >parallelism, i.e. the ability to execute a query in parallel within a >database instance / partition > >It will appear when the [...] 19045 160 23_SQLCODE -904 on SYSCOPY13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Fri, 21 Jan 2005 14:06:58 -0600334_iso-8859-1 DB2 V7 z/OS

We have an ongoing problem where at least 3 or 4 times a week we have image copy jobs abend on a SQLCODE -904 on SYSCOPY. I've checked the catalog, the Tablespace is set to row level locking. I've checked the mstr log and there is no indication of lock escalation on the SYSCOPY since the last IPL. [...]