1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2001, week 1 2 15 42_iI will return your note after I get back.15_Mark Hardenburg30_mark.hardenburg@PRUDENTIAL.COM30_Sun, 1 Jul 2001 01:01:38 -0400403_us-ascii I will be out of the office starting 06/29/2001 and will not return until 07/09/2001.

For DB2 help while I am out, contact Frank Yao at 716-8644.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 18 96 26_Re: Another Oracle barrage11_Grant Allen22_grant@TOWERSOFT.COM.AU30_Mon, 2 Jul 2001 09:48:25 +1000552_- Sanfilippo, David[SMTP:David.Sanfilippo@GMACINSURANCE.COM] wrote: > > My only experience was with Oracle for OS/390, and I was so impressed > that I > > reversed field and came running back. I have no desire to supply pro- > or > > anti- Oracle propaganda. However, I stand by my original statement that > an > > Oracle schema, while certainly not equivalent, roughly equates to a DB2 > > database. > > > > - No independent connection/privilege control ... e.g. once you have DBA > > privileges, you get to see all schemas ... very bad. > > > > [...] 115 11 26_Re: Limiting SELECT output10_John Hardy22_john_j_hardy@YAHOO.COM30_Sun, 1 Jul 2001 19:49:44 -0500372_- Look up the 'STRIP' Scalar function in the manual. It was introduced in OS/390 DB2 Version 5 - I assume it's also in other DB2s.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 127 39 36_Re: To measure the CPU and I/o time.10_John Hardy22_john_j_hardy@YAHOO.COM30_Sun, 1 Jul 2001 20:21:04 -0500633_- You need to specify the platform you are running on (e.g. DB2 Version 5 for OS/390), and what software you have available there, e.g. (if OS/390):

Strobe DB2 AM DB2 PM Via/Insight Omegamon for DB2 etc. etc.



On Sat, 30 Jun 2001 09:29:55 -0000, john king wrote:

>Good day listers, here I m posting a question to list for quick answer. How I would measure the CPU and I/O time for a particular query. Is there any tools availavble or any command in db2 is there to know it. > >Your's >John > >____________________________________________________ >Buy Feng Shui Package for [...] 167 15 52_Terry Gawlas/ISG/CORP/Highmark is out of the office.12_Terry Gawlas25_terry.gawlas@HIGHMARK.COM30_Mon, 2 Jul 2001 01:13:41 -0400400_us-ascii I will be out of the office starting 06/29/2001 and will not return until 07/09/2001.

I will only be in the office 7/3 and 7/5 in the morning.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 183 20 31_Re: Question for Tuning Experts10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 2 Jul 2001 11:15:24 +0200462_us-ascii I'm not sure that this is what you need, but in Chuck Hoover paper

'Tickets to the ball park'

there are some hint/tips about CPU usage and cost estimation.

HTH Max Scarpa Useless DB2 sysprog

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 204 102 26_Re: Another Oracle barrage9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Mon, 2 Jul 2001 11:40:17 +0100362_ISO-8859-1 My two most annoying things about Oracle:

1. A DBA user cannot grant authority on tables owned by another schema

2. A user who owns a table cannot create a public synonym on it unless that user also has DBA privileges

Two most annoying things about DB2 were:

1. Lack of function of the SQL (thanks to V6 not an issue) [...] 307 78 20_Package Search Order20_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE30_Mon, 2 Jul 2001 07:51:45 -0500636_ISO-8859-1 Our Environment is DB2 UDB OS/390 V5. Actually we have to expand pklist in a lot of plans with another "collid.*" and are not able to add the appropriate "Set Current Packageset" in all affected progams in the required time. Until now I saw no big problems since I trusted in the fact that package search in a plan (with multiple collid.*) only depends on the order of the pklist entries. But now I found in DB2 magazine (Programs and Packages, Plans and Collections by Robert Catterall, http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml) that this is not always true. A corresponding description I have found [...] 386 46 19_XML Extender Wizard14_Geoff Beaumont21_Geoff@STORMHAMMER.COM30_Mon, 2 Jul 2001 13:06:20 +0100456_us-ascii Hi,

I'm having a bit of trouble with the XML Extender Wizard - it appears to have installed correctly and the CLASSPATH has been modified as per the XML Extender release notes: http://www-4.ibm.com/software/data/db2/extenders/xmlext/docs/v72wrk/release_notes72_eng.htm#Header_7

However, when I run dxxadmin.cmd I get the following response:

> dxxadmin.cmd Unable to initialize threads: cannot find class java/lang/Thread [...] 433 26 20_Excessive Wait Times15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Mon, 2 Jul 2001 08:08:58 -0500423_- Hi listers

Recently we ran a batch job with an elapsed time of about 80 minutes. The Strobe report showed an excessive high amount of wait time. Here is the part of the Strobe report

ATTRIBUTION OF CPU WAIT TIME

.SVC SVC 001 WAIT ---------------INVOKED BY------------------------------------WAIT TIME % XACTION MODULE SECTION RETURN PAGE TOTAL INHA115 .DB2 DSNECP00 DSN START-UP CSECT .00 66.50 [...] 460 71 31_Re: DB2 TCPIP security on OS39012_tim malamphy20_timalamphy@YAHOO.COM30_Mon, 2 Jul 2001 07:10:21 -0700576_us-ascii I haven't seen any replies (although I may have missed some), that fit your request, and don't cause you an awful lot of DBA work. Sometimes, one can take corrective action after the problem has taken place, to keep it from happening again. Assuming that there are corporate barries from giving these folks their own copies of the tables (ie too expensive for a replication scenario), and assuming that it is known policy that people are not supposed to access these tables via query tools, but they continue to do so because you have not been able to lock this [...] 532 20 31_Re: DB2 TCPIP security on OS39020_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE30_Mon, 2 Jul 2001 09:59:09 -0500502_- I don't like dynamic SQL because you run each time in such authorization problems. Unfortunately it can't be wholly avoided - especially when you utilize bought/rented Software or developers decide to use it for other good reasons.

But what's about using only stored procedures with static SQL for DB2 access and maybe some additional coding for checking user's (business) privileges (which in turn can be stored in DB2 tables ...). So only the package binder needs access to the tables. [...] 553 12 35_Re: Page movement between BP and HP6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM30_Mon, 2 Jul 2001 10:00:28 -0500296_- Any help will be greatly appreciated.

-Chenny

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 566 33 23_Primary Key Usefulness?11_Bill Happel25_Bill.Happel@US.FORTIS.COM30_Mon, 2 Jul 2001 10:15:50 -0500276_US-ASCII With DB2 UDB 6.1 for OS/390, "the parent key of a table is either a primary key or a unique key that is part of a referential constraint." The big disadvantage of Primary Keys is: If you have to add a column to the key, you have to drop and re-create the table. [...] 600 173 31_Re: DB2 TCPIP security on OS39012_Troy Coleman19_Colematr@MEIJER.COM30_Mon, 2 Jul 2001 11:20:49 -0400423_US-ASCII You have several design options within your application for this. I will use PeopleSoft as an example.

Option 1: Store the end user USERID and password in an application table with the password encrypted. Connect to DB2 with a common USERID/password. Next validate the end user USERID and password against your security table. This would allow your application to get to the data but not the end user. [...] 774 15 29_Attention Decision Base Users14_Karen Grantham25_Karen.Grantham@COMPAQ.COM30_Mon, 2 Jul 2001 10:14:58 -0500504_us-ascii If anyone is currently using, or especially beta testing the Decision Base product against DB2, we would like to open a dialog off the DB2 discussion list. Please email us directly at Karen.Grantham@compaq.com and/or John.W.Rankin@compaq.com. Thank you.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 790 24 35_How to report size of UDB database?11_Judy Kibler21_judith.kibler@TRW.COM30_Mon, 2 Jul 2001 10:22:04 -0500505_us-ascii Hello all,

We recently migrated our DB2/390 v5.1 database to DB2 UDB for AIX v6.1. I have to provide a monthly report to management to show the actual size of the database. I used to calculate it as the sum of (reclength * card) for all tables in the database (from SYSIBM.SYSTABLES). I just noticed that reclength is not included in the SYSCAT.TABLES table. Can someone point me in the right direction to show me how to report the actual size of the database in our new environment? [...] 815 47 66_Open Source Project: Lib for processing the Snapshot output buffer15_Daniel Scheibli15_scheibd@GMX.NET30_Mon, 2 Jul 2001 17:27:59 +0200429_us-ascii Dear list colleagues,

today I would like to announce DbtuFpsLib an library (including an sample implementation -> alternative LIST APPLICATIONS with idle time and ip address of client) which helps you to process the output buffer of snapshots taken with UDB.

The current status is, that the lib works, but not all snapshot groups are implemented yet. I wanted to provide this lib as soon as possible. [...] 863 55 27_Re: Primary Key Usefulness?13_Keith Gilbert17_kgilbert@CSA1.COM30_Mon, 2 Jul 2001 10:52:36 -0500673_iso-8859-1 The big advantage to Primary Keys is the ability to define Referential Integrity. Foreign Keys always reference the Primary Key of the parent table.

Keith Gilbert DBA Central Service Association Tupelo, MS





-----Original Message----- From: Bill Happel [mailto:Bill.Happel@US.FORTIS.COM] Sent: Monday, July 02, 2001 10:16 AM To: DB2-L@RYCI.COM Subject: Primary Key Usefulness?



With DB2 UDB 6.1 for OS/390, "the parent key of a table is either a primary key or a unique key that is part of a referential constraint." The big disadvantage of Primary Keys is: If you have to add a column to the key, you have to drop [...] 919 86 27_Re: Primary Key Usefulness?11_Bill Happel25_Bill.Happel@US.FORTIS.COM30_Mon, 2 Jul 2001 11:46:22 -0500472_US-ASCII With version 6.1, this is no longer a restriction. You are suppose to be able to define Referential Integrity with any unique key (i.e. not just the Primary Key). To me, this was the only reason to define a Primary Key.

William R. Happel Hartford Life

>>> kgilbert@CSA1.COM 07/02/01 10:52AM >>> The big advantage to Primary Keys is the ability to define Referential Integrity. Foreign Keys always reference the Primary Key of the parent table. [...] 1006 102 27_Re: Primary Key Usefulness?12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 2 Jul 2001 19:50:09 +0200445_iso-8859-1 Hi, There is one more thing with PK: When defined you can see the order of the fields in sysibm.syscolumns. whether that's good reason or not - is up to you.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net

----- Original Message ----- From: "Bill Happel" Newsgroups: bit.listserv.db2-l To: Sent: Monday, July 02, 2001 6:46 PM Subject: Re: Primary Key Usefulness? [...] 1109 71 20_Re: Blobs in UDB 7.111_Hamar, Bela22_Bela.Hamar@VARETIS.COM30_Mon, 2 Jul 2001 19:01:56 +0200589_us-ascii Hi Horacio,

here is a working example for you.

I hope, this helps you.

Cheers, Bela



The column c is a blob(81920). ('conn' is a Connection, of course:-)

You can use several methods of ResultSet to get a blob value from a result set:

Blob getBlob() byte [] getBytes() InputStream getBinaryStream() InputStream getAsciiStream() String getString()





Statement stmt = conn.createStatement(); pstmt = conn.prepareStatement("select c from test.t4 " , ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) ; [...] 1181 221 37_Re: Dynamic SQL - Cache some, not all13_Brunner Don J21_Don.J.Brunner@IRS.GOV30_Mon, 2 Jul 2001 14:40:55 -0400436_iso-8859-1 A question related to this topic: If Dynamic SQL Caching is set to YES within a subsystem, and a Package/Plan is bound with KEEPDYNAMIC(NO), won't any dynamic SQL executed with that Package/Plan NOT be moved into the Dynamic SQL Cache?

-----Original Message----- From: Moulder, Tom [mailto:Tom_Moulder@BMC.COM] Sent: Friday, June 29, 2001 2:24 PM To: DB2-L@RYCI.COM Subject: Re: Dynamic SQL - Cache some, not all [...] 1403 16 39_Jeremy Deaver/WRI is out of the office.13_Jeremy Deaver20_Jeremy_Deaver@WR.COM30_Mon, 2 Jul 2001 15:37:20 -0500466_us-ascii I will be out of the office starting 07/02/2001 and will not return until 07/10/2001.

I will be out of the office until Tuesday, July 10th. Please forward all database questions to Kent Brown or Parke Parker.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 1420 70 22_PARTITION INDEPENDENCE11_Howe, Steve15_SHowe@BCBSM.COM30_Mon, 2 Jul 2001 19:03:28 -0400487_iso-8859-1 We are on DB2 Version 6. We have a Table with 10 Partitions and 3 NPI and about 30 million rows. We have not been able to Load this Table by Partition in Parallel. When we attempt to do this, one gets in and the others fail due to locks on the NPI. We have many other Partitioned Tables and we perform all functions in Parallel with no problem. Does anyone know if there are any conditions that we're missing that would not allow for Parallel Load Partition Processing ? [...] 1491 44 26_Re: PARTITION INDEPENDENCE9_Saddy, Ed16_Ed_Saddy@BMC.COM30_Mon, 2 Jul 2001 18:22:23 -0500350_iso-8859-1 Steve,

If you are loading ALL partitions, then:

- drop the NPIs - run the LOADs in parallel (as many as your MVS / DB2 can handle) - REBUILD the NPIs

If you cannot afford to drop / REBUILD the NPIs you might try running 2 loads at a time or 3, etc. But I don't think you'll get very many to run at the same time. [...] 1536 28 32_Handling LOb tables DB2/OS390 V616_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE30_Tue, 3 Jul 2001 01:24:31 +0200509_- Hi,

how did you handle LOB tables on your DB2 OS/390? UNLOAD? FULLCOPY ? RECOVERY? REORG?

As we have a LOB in our online-environment we might to need at LOG YES instead of LOG NO.

Experience!

LOG-Files ? Backout processing?

Roland

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 1565 55 27_Re: Primary Key Usefulness?15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Tue, 3 Jul 2001 09:21:35 +1000445_- Um, I don't think that's quite right. If you want to change the primary key definition you do *not* have to drop and recreate the table. You will have to drop and recreate the matching index to include the new primary key columns (or make use of an existing/new unique index that has all the new primary key columns) but the table itself doesn't need anything done - apart, of course, from dropping the primary key and creating it again. [...] 1621 38 26_Re: PARTITION INDEPENDENCE15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Tue, 3 Jul 2001 09:24:17 +1000603_- Just a thought, but you haven't omitted the 'into table part blah' clause, have you? I've seen people accidentally try to kick off multiple partition load jobs and they all try to load into the 'whole' TS, rather than be partition-specific. 'Course, *I* would never do that...



Raymond

> -----Original Message----- > From: Howe, Steve [SMTP:SHowe@BCBSM.COM] > Sent: Tuesday, 3 July 2001 9:03 am > To: DB2-L@RYCI.COM > Subject: PARTITION INDEPENDENCE > > We are on DB2 Version 6. We have a Table with 10 Partitions and 3 NPI > and about 30 million rows. We have not been able [...] 1660 15 23_another db2 v6 question16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Mon, 2 Jul 2001 19:26:37 -0400334_us-ascii Hi, what are the current limits on the number of tablespaces per db? TIA Tom Faglon

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 1676 44 26_Re: PARTITION INDEPENDENCE13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 2 Jul 2001 16:32:32 -0700511_iso-8859-1 Probably something to do with your load card. Should be: LOAD ...INTO PART REPLACE/RESUME e.g. LOAD...INTO TB1 PART 1 REPLACE

If you use following or something similar: LOAD...RESUME YES....INTO TB1 PART 1 REPLACE PART 1 This will NOT give you partition independence.

Thanks. Ashish.





-----Original Message----- From: Howe, Steve [SMTP:SHowe@BCBSM.COM] Sent: Monday, July 02, 2001 4:03 PM To: DB2-L@RYCI.COM Subject: PARTITION INDEPENDENCE [...] 1721 199 37_Re: Dynamic SQL - Cache some, not all12_Moulder, Tom19_Tom_Moulder@BMC.COM30_Mon, 2 Jul 2001 19:05:13 -0500310_iso-8859-1 The Keepdynamic option determines if a cached statement will be retained across commits. No, says to flush the statement from cache at commit, Yes says to keep the cached statement across a commit. Look in the SQL Reference Manual Appendix D Page Number 871 -- That is if you have a V6 manual. [...] 1921 57 39_Re: How to report size of UDB database?10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV30_Mon, 2 Jul 2001 20:22:44 -0500680_iso-8859-1 Judy,

Run utility STOSPACE and then you can look in table SYSIBM.SYSSTOGROUP to get the space allocated. See column space just remember the value in space is in "k".



______________________________ Reply Separator _________________________________ Subject: How to report size of UDB database? Author: owner-db2-l@RYCI.COM at INTERNET Date: 7/2/01 6:18 PM



Hello all,

We recently migrated our DB2/390 v5.1 database to DB2 UDB for AIX v6.1. I have to provide a monthly report to management to show the actual size of the database. I used to calculate it as the sum of (reclength * card) for all tables in the database (from [...] 1979 32 27_Re: another db2 v6 question15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Tue, 3 Jul 2001 10:22:30 +1000637_- Not sure but I think it's only limited by memory i.e. how big a DBD you can hold in your EDM pool. Please, someone correct me if (when?) I'm wrong.



Raymond

> -----Original Message----- > From: Thomas E. Faglon [SMTP:tfaglon@TELCORDIA.COM] > Sent: Tuesday, 3 July 2001 9:27 am > To: DB2-L@RYCI.COM > Subject: another db2 v6 question > > Hi, > what are the current limits on the number of tablespaces per db? > TIA > Tom Faglon > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The [...] 2012 45 27_Re: another db2 v6 question12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Tue, 3 Jul 2001 10:30:12 +1000424_- Tom, in my travels I haven't come across any hard limits. The only limits that I can think of are :-

* OBID is a Smallint so 32K objects (Tables, TableSpaces, Indexes) which is still an awful lot * and indirectly the size of your EDM pool will limit, as you've got to be able load in the DBD, with all of the plans/packages you may try to run. This is the area I would expect people to have problems with 1st. [...] 2058 52 26_Re: Another Oracle barrage14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU30_Mon, 2 Jul 2001 19:54:09 -0500371_iso-8859-1 > >2. A user who owns a table cannot create a public synonym on it unless that >user also has DBA privileges >

Try "GRANT CREATE PUBLIC SYNONYM TO " - no DBA privilege required. Works in 8.1.6

No I'm not pushing O*acle - I just have to work with it....and *nix . I'm exposed to the dark side of the force....save me Obe-wan! [...] 2111 126 21_Re: Strange RC in DB214_Crichton, Jean21_jcrichton@FEDDATA.COM30_Mon, 2 Jul 2001 21:10:43 -0400598_iso-8859-1 Hi Robert,

Were there any other symptoms besides the 00E30104? Any RESOURCE UNAVAILABLE conditions? Any SVC dumps? If it's an internal error, you should probably find some indication of it in SYS1.LOGREC. Ask your sysprog to run EREP against that time interval in SYS1.LOGREC. Search on the 00E30104 in the EREP report and check before and after it for other records that may have been generated to see if there is anything unusual in your DxxxMSTR address space. If you got an SVC dump, that's a strong indication that it probably is an internal error and I would check the [...] 2238 38 37_Re: Dynamic SQL - Cache some, not all12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 2 Jul 2001 20:32:21 -0500469_us-ascii Ok, how 'bout this...

(Given that I'd like to get some sort of monitor in house, ala Mainview or PM before I begin lobbying for something like Pool Advisor, which admittedly, does sound very cool.)

On page 507 of the Application Programming and SQL Guide, Version 6, in section 7.1 "Coding Dynamic SQL in Application Programs", it says: "Statements in Plans or Packages bound with REOPT(VARS) are not elibible for caching. See...blah blah". [...] 2277 20 3_WLM12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM30_Mon, 2 Jul 2001 22:09:58 -0500 2298 78 27_Re: Primary Key Usefulness?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Jul 2001 09:52:27 +0530601_- With V6 for OS390 and above, neither the table needs to be dropped nor the index only. We need to drop the primary key (ALTER TABLE DROP P.K) and recreate it with ALTER TABLE ADD PRIMARY KEY (cols).....

I think this is correct.

Regards Sanjeev

> -----Original Message----- > From: Bell, Raymond W [SMTP:Raymond.W.Bell@TEAM.TELSTRA.COM] > Sent: Tuesday, July 03, 2001 4:52 AM > To: DB2-L@RYCI.COM > Subject: Re: Primary Key Usefulness? > > Um, I don't think that's quite right. If you want to change the primary > key > definition you do *not* have to drop and recreate the [...] 2377 34 35_Re: Page movement between BP and HP16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Jul 2001 09:56:05 +0530674_- Hi Chenny,

1 or 2 months back we discussed almost the same thing. You can look into the archive and collect some thoughts. The title of the mail is "Buffer pool question". Let us know if it helps.

Regards Sanjeev

> -----Original Message----- > From: Chenny [SMTP:solaiyappan_chenniappan@MAIL.AMSINC.COM] > Sent: Monday, July 02, 2001 8:30 PM > To: DB2-L@RYCI.COM > Subject: Re: Page movement between BP and HP > > Any help will be greatly appreciated. > > -Chenny > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The [...] 2412 51 24_Re: Excessive Wait Times16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Jul 2001 10:01:54 +0530594_- Hi Walter,

Did you try monitoring the same for more than one instance of execution ?. There might be HIGH WAIT reasons at the time when this stats is taken.

I am not sure about the strobe but if this problem may not be due to the application only.

Regards Sanjeev

> -----Original Message----- > From: Walter Janissen [SMTP:walter.janissen@VICTORIA.DE] > Sent: Monday, July 02, 2001 6:39 PM > To: DB2-L@RYCI.COM > Subject: Excessive Wait Times > > Hi listers > > Recently we ran a batch job with an elapsed time of about 80 minutes. The > Strobe report showed [...] 2464 39 31_Re: DB2 TCPIP security on OS39011_Michael Lim22_MichaelLim@HKEX.COM.HK30_Tue, 3 Jul 2001 15:02:49 +0800362_- Are you using DB2 Connect? If so, where do you perform authentication - at the server or at the client? If server, the users will have to have a server logon id with the appropriate access in DB2. If client, I guess you can force all access to go through the web server using a firewall. You also have to make sure QMF is not available on the web server. [...] 2504 15 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM30_Tue, 3 Jul 2001 02:12:33 -0500462_us-ascii I will be out of the office from 07/02/2001 until 07/05/2001.

For DB2 page PIN # 7270526, for informix/sqlserver page PIN # 7270525, for network infrastructure, page 18007598352, PIN # 7270063 or 7270300.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 2520 34 11_IRLM - MCSA0_22_The-Thanh.Luong@AGF.BE30_Tue, 3 Jul 2001 09:41:17 +0200405_- Hi,

Environment OS/390, DB2 V4.01. We had yesterday an out-of-storage indication from IRLM during 2 minutes, as shown here:

16.10.11 DSNT501I > DSNILMCL RESOURCE UNAVAILABLE CORRELATION-ID=VI£RLDLT CONNECTION-ID=BATCH LUW-ID=* REASON 00C90092 TYPE 00000905 NAME IRLM 16.10.15 DXR175E DRLM IRLM IS UNABLE TO OBTAIN STORAGE - MCSA. 16.12.35 DXR176I DRLM IRLM STORAGE SHORTAGE RELIEVED. [...] 2555 29 15_Re: IRLM - MCSA10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 3 Jul 2001 10:39:55 +0200553_us-ascii Hi....

take a look at APAR



APAR NUMBER: PQ37950

in IBM APAR database (http://techsupport.services.ibm.com/s390/techKnow) - closed APARs.

There's a message quite similar to your and a brief description.

HTH





Max Scarpa Useless DB2 sysprog

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 2585 131 20_Re: Blobs in UDB 7.111_Hamar, Bela22_Bela.Hamar@VARETIS.COM30_Tue, 3 Jul 2001 10:45:54 +0200513_us-ascii Hi Horacio,

which driver are you using? What is the output of the following code:

try { Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

Driver d = DriverManager.getDriver("jdbc:db2:") ;

System.out.println("Driver: "+d.getMajorVersion()+"."+d.getMinorVersion ()+"\n" ) ; } catch (Exception e) { e.printStackTrace(); }

Have you run the batch file 'sqllib\java12\usejdbc2.bat' ? This enables the use of the JDBC 2.0 driver (which defines the Blob and Clob classes). [...] 2717 16 24_Re: Excessive Wait Times15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 3 Jul 2001 04:06:03 -0500331_- Sanjeev

The job ran about 80 minutes and in very short intervals (I don't know how short) Strobe took a probe. During the 80 minutes there was a wait time of about 60 minutes and 66 percent of that wait time was charged to the module DSNECP00. Other instances of execution showed high wait times in that module, too. [...] 2734 23 27_Re: another db2 v6 question13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 3 Jul 2001 07:06:14 -0400404_iso-8859-1 > what are the current limits on the number of tablespaces per db?

Hello Tom, If there were a documented limit, you'd find it in Appendix A of the SQL reference.

Our friends Nick and Raymond have suggested the good considerations regarding numbers of tablespaces in databases.

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com [...] 2758 38 24_Re: Excessive Wait Times16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Jul 2001 16:43:58 +0530600_- The module you have mentioned is the TSO attachment module and it seems some strobe expert need to analyse the report sent by you earlier.

Regards Sanjeev





> -----Original Message----- > From: Walter Janissen [SMTP:walter.janissen@VICTORIA.DE] > Sent: Tuesday, July 03, 2001 2:36 PM > To: DB2-L@RYCI.COM > Subject: Re: Excessive Wait Times > > Sanjeev > > The job ran about 80 minutes and in very short intervals (I don't know how > short) Strobe took a probe. During the 80 minutes there was a wait time of > about 60 minutes and 66 percent of that wait time was [...] 2797 56 27_Re: Primary Key Usefulness?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Jul 2001 16:29:10 +0530287_- Bill,

I think you raised a very good point. From V6 onwards, we can have Unique index used for RI, do we need primary key at all ? As i mentioned with PK, we need to use ALTER TABLE for changing its column definition, but for unique index we need to change the index only. [...] 2854 83 13_GBP Duplexing31_Tekin Yavuz (Garanti Teknoloji)21_TekinY@GARANTI.COM.TR30_Tue, 3 Jul 2001 14:26:42 +0300510_iso-8859-9 Hi all,

We decide to redefine our GBP's in duplexing mode after migrating DB2 V6 in OS/390.For our test system we have activated one of our GBP's in Duplex(allowed) mode. I wanna test how duplexing works when a failure in any Global Bufferpool occurs . I searched the books for any tool or a scenario for testing it,I mean to ba able to simulate a loss in primary structure of a GBP to switch db2 to use the secondary structure, but I coluld'nt find it.Has anyone have a tool like that. [...] 2938 168 37_Re: Dynamic SQL - Cache some, not all16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM30_Tue, 3 Jul 2001 07:38:56 -0400557_us-ascii The parameter KEEPDYNAMIC on the BIND is actually independent from the zparm system parameter Dynamic Statement Cache. They refer to two different things. The zparm parameter gets most of the cpu savings because it results in the caching of the results of the Prepare including the access path determined by the optimizer. To get the maximum benefit, both options must be on; but the greatest benefit comes from the ZPARM option. It is my understanding that KEEPDYNAMIC(YES) results in saving some syntax -type checking for the sql statement. [...] 3107 50 15_Re: IRLM - MCSA12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM30_Tue, 3 Jul 2001 06:46:05 -0500 3158 100 24_Re: Excessive Wait Times0_29_sflindsey@HIGHLIGHTS-CORP.COM30_Tue, 3 Jul 2001 07:53:11 -0400557_us-ascii Hi, Walter! In my experience with reading Strobe reports from DB2 programs, you should be looking for the "Wait by SQL Statement" section. This section details the wait time by SQL statement. The only thing that is helpful in the module wait time section is when your program is calling other programs. There is not much you can really do about the DB2 system service modules. Check out the SQL statement section to see where most of your time is being spent. This can lead you to how you might be able to tune the SQL to perform better. HTH, [...] 3259 66 38_Re: Deletes with Referential Integrity11_David Nance16_DWNance@FHSC.COM30_Tue, 3 Jul 2001 08:07:02 -0400458_US-ASCII Glenn, One thing I haven't seen mentioned is what is the lock contention on the children/granchildren tables? In past environments we have had to ensure that certain processes were not running at the same time we were attempting to delete rows from a parent table.

-----Original Message----- From: Glenn Andrews [mailto:gandrew3@CSC.COM] Sent: Monday, June 25, 2001 3:45 PM To: DB2-L@RYCI.COM Subject: Deletes with Referential Integrity [...] 3326 39 36_Closing a long run UOW and rollback.17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT30_Tue, 3 Jul 2001 14:18:30 +0100286_us-ascii It's not the first time we face, on our production shop, a problem with jobs running for a long time, without committing, and have to cancel it due to a loop or similar. (On Sat. a job looped for 8 ours inserting 45 millions of rows and we estimated a 30 hours rollback). [...] 3366 18 24_Re: Excessive Wait Times15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 3 Jul 2001 07:33:04 -0500416_- Hi Scott

I hoped, that this figure would tell somebody else, oh yes, I have had a similar strobe report and the reason for the wait time in that module is not excessive I/O due to a SQL, but that and that.

Well I haven't yet the whole report from Strobe, but I am quite sure that there wasn't a SQL-statement causing this wait time otherwise the application programmer would have told it to me. [...] 3385 68 43_Error code :DSN1802I RING/LINK CHAIN BROKEN11_Manish Lall17_db2list@YAHOO.COM30_Tue, 3 Jul 2001 06:31:19 -0700298_us-ascii Hi ,

I ran DSN1CHKR on DSNDB01.DBD01 tablespace and got the following output which indicates that there is a broken link chain ..... CAN anybody in the list let me know how do i rectify the error . I checked the the db2 list archives but couldn't get anything on this subject. [...] 3454 223 15_Re: IRLM - MCSA14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Tue, 3 Jul 2001 09:39:18 -0400584_iso-8859-1 You might also want to evaluate setting your MAXCSA to a higher value in your IRLM address space JCL. I ran into this problem myself after our application mix had undergone substantial changes to the point that we actually needed to change the MAXCSA to accommodate certain conditions. In V4.1 and prior versions, the default is 5M, but in later versions, the default is 6M, and my experience has been that once the value is set when DB2 is first installed, nobody thinks to evaluate whether or not to change it (I certainly didn't until I hit this error condition!). [...] 3678 35 0_0_23_Sharon.Fields@PCSHS.COM30_Tue, 3 Jul 2001 07:02:51 -0700571_us-ascii Okay -- I need some conversation on this one. I am currently speccing out a DB2 alternative for a root-segment only IMS database that is coming up in estimator at 1.65terabytes of data spread over 100 partitions. We will need at least 2 secondary indexes. Most use is random, online -- and it gets chewed up 7 days a week. Hoping to use V7 Load Resume with change as our daily load of 1million rows; current load processes take 2.5 hours daily while offline. Deletes would happen often approximately 1mill are eligible on a daily basis but I haven't nailed [...] 3714 73 47_Re: Error code :DSN1802I RING/LINK CHAIN BROKEN12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Tue, 3 Jul 2001 10:05:00 -0400412_- OUCH! I'd call IBM on this. See page 337 in the V6.1 Diag. Guide and Ref. >>> DB2-L@RYCI.COM@inter2 07/03/01 08:31AM >>> Hi ,

I ran DSN1CHKR on DSNDB01.DBD01 tablespace and got the following output which indicates that there is a broken link chain ..... CAN anybody in the list let me know how do i rectify the error . I checked the the db2 list archives but couldn't get anything on this subject. [...] 3788 75 47_Re: Error code :DSN1802I RING/LINK CHAIN BROKEN13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Tue, 3 Jul 2001 10:24:19 -0500539_us-ascii Just curious.. Before you attempt to "fix" anything, was DBD01 stopped? Remember that DSN1CHKR only checks what is on disk and is not aware of anything in the pools.

W

Manish Lall wrote:

> Hi , > > I ran DSN1CHKR on DSNDB01.DBD01 tablespace and got > the > following output which indicates that there is a > broken link chain ..... CAN anybody in the list let > me know how do i rectify the error . I checked the the > db2 list archives but couldn't get anything on this > subject. > > Thanks , > Manish [...] 3864 137 40_Re: Closing a long run UOW and rollback.14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Tue, 3 Jul 2001 11:26:25 -0400609_iso-8859-1 Massimo,

The rollback is so slow because DB2 has to not only do the rollback but has to write compensating undo/redo records for the rollback itself! In DB2 V6, DB2 has gotten smart enough during startup to identify long UOWs that must be completed/rolled back and will schedule them for deferred recovery so that startup can complete during a normal amount of time. At that point, the deferred UOWs will begin recovery. Also, you can't force a rollback to end. Once DB2 has initiated the process, it considers the rollback a "must-complete" function. The only way to prevent this from [...] 4002 15 21_Re: Strange RC in DB29_Jim Ruddy18_jaruddy@US.IBM.COM30_Tue, 3 Jul 2001 10:14:48 -0500414_- You probably had an abend in DB2 to get this reason code.

Look in the MVS console log or logrec for that abend.

Jim Ruddy DB2 of z/OS and OS/390 Development

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 4018 153 47_Re: Error code :DSN1802I RING/LINK CHAIN BROKEN14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Tue, 3 Jul 2001 11:30:09 -0400552_iso-8859-1 Manish,

If I were you in this situation, I would go straight to IBM and enlist their assistance if you have that kind of support contract. Shooting a pointer chain is a complicated and tedious process and if you are down, you might not be able to afford the time. I would try to recover first before you try anything else. It is possible to map broken chains and run the REPAIR utility to correct a problem like this, but unless you are absolutely sure of what you are doing, you could wind up with an even more serious problem. [...] 4172 75 37_Re: Dynamic SQL - Cache some, not all12_Moulder, Tom19_Tom_Moulder@BMC.COM30_Tue, 3 Jul 2001 10:41:51 -0500567_iso-8859-1 I'm constantly amazed at the ingenuity of the dba community. I can't think of any reason at the moment why that would not work.

I should add another word about the keepdynamic option -- When this is used, DB2 caches the statment in what it calls a local cache. This is in the "DBM1" private address space, but not in the EDM pool with all the other cached statements and is only available for recall by the thread it is associated with. Think carefully before using this to any great extent because it will use storage and could cause problems [...] 4248 88 40_Re: Closing a long run UOW and rollback.14_Richard Yevich17_ryevich@YAHOO.COM30_Tue, 3 Jul 2001 08:43:53 -0700637_us-ascii

Worth noting that in DB2 OS/390 V7, there is a cancel without backout at the thread level. Then you only have to clear the RECP on the objects. Much nicer, much faster... CANCEL THREAD NOBACKOUT

"Crichton, Jean" wrote: Massimo,

The rollback is so slow because DB2 has to not only do the rollback but has to write compensating undo/redo records for the rollback itself! In DB2 V6, DB2 has gotten smart enough during startup to identify long UOWs that must be completed/rolled back and will schedule them for deferred recovery so that startup can complete during a normal amount [...] 4337 91 0_12_Susan Lawson22_lawson_susan@YAHOO.COM30_Tue, 3 Jul 2001 08:44:45 -0700392_us-ascii Sharon,

We have built many simliar tables of this nature (6-140 billion rows - multi-terabyte). There are many design and implementation considerations that differ from they way we have traditionally built tables/indexes. We mainly design for maintenance and DBA sanity!

I do have a white paper written on our experiences if you would like to request it off line. [...] 4429 64 0_12_Moulder, Tom19_Tom_Moulder@BMC.COM30_Tue, 3 Jul 2001 11:01:59 -0500429_iso-8859-1 Sharon

Just curious ... Is this a fullfunction IMS database or Fast Path. Because if you are using Fast Path I would question why you are planning to go to DB2. Also, IMS 7.1 has a feature called HALDB - High Availability Large Data Base - have you investigated whether this would also provide an alternative? I'm not trying to discourage you use of DB2, just making sure you knew of all the alternatives. [...] 4494 107 40_Re: Closing a long run UOW and rollback.14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Tue, 3 Jul 2001 12:08:13 -0400454_iso-8859-1 Much less painful... Do I hear a collective sigh of relief from everyone who has had to do a conditional restart for this before??? Thanks for the heads-up, Richard. Now I really can't wait to get my hands on DB2 V7!

Jean E. Crichton Logicon

-----Original Message----- From: Richard Yevich [mailto:ryevich@YAHOO.COM] Sent: Tuesday, July 03, 2001 11:44 AM To: DB2-L@RYCI.COM Subject: Re: Closing a long run UOW and rollback. [...] 4602 75 35_Larry Is At It Again-More Oracle BS0_21_db2_dba@BELLSOUTH.NET30_Tue, 3 Jul 2001 12:08:44 -0400621_iso-8859-1 Here is some of the latest from Larry:



If Oracle's 'last database' is so good ...

By John Taschek, eWEEK

Oracle chief Larry Ellison said the code name for Oracle9i is the "last database," which I concluded to mean that Ellison thought the database market was dead.He later clarified his remarks. It's not the "last" database, he said—Oracle9i features the biggest, most radical improvements in database computing since the first relational database. There probably won't be any other improvements in this technology this great, ever. I'm glad Ellison hasn't lost his bravado. [...] 4678 15 53_Cathy Drummond/Santa Teresa/IBM is out of the office.14_Cathy Drummond18_cdrummo@US.IBM.COM30_Tue, 3 Jul 2001 10:03:37 -0600393_us-ascii I will be out of the office starting June 8, 2001 and will not return until July 9, 2001.

I will respond to your message when I return.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 4694 111 47_Re: Error code :DSN1802I RING/LINK CHAIN BROKEN11_Manish Lall17_db2list@YAHOO.COM30_Tue, 3 Jul 2001 09:29:00 -0700340_us-ascii Hi Jean,

Thanks for your guidance , i think it's better to go to IBM for this , we checked our database one of them is in inconsistent state rest is fine .the REPAIR utility was in my mind but was not sure of using it ..as u said it might go worse... when we want to display that database its gives the following error [...] 4806 33 21_Compression questions24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Tue, 3 Jul 2001 09:29:45 -0700628_- Hi Guys!

I have a couple of questions regarding compression. * I understand that RUNSTATS need to uncompress rows in order to process them. Does anyone have any rule-of-thumb regarding this cost? * Regarding bufferpool usage, the compression dictionary needs to be in the buffer pool whenever a compressed row is accessed. Typically, this dictionary size is 16 pages. This implies that the dictionary for compressed tablespaces and partitions would be nearly resident in the pools at all times. At what point does this impact usage of the pools? Does the Buffer Pool Tool from Responsive Systems account for these [...] 4840 81 25_Re: Compression questions15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Tue, 3 Jul 2001 12:27:58 -0500461_iso-8859-1 As in everything dealing with DB2, it depends.

In reality, if you have a decent compression ratio, the dictionary pages in the bufferpool are noise. If you are getting 50% compression, to buy back the cost of the dictionary pages, you only need a tablespace of 32 pages if they were uncompressed. Now most people that have mentioned compression ratios on the list have been in the 75% range, so you would need an even smaller tablespace. [...] 4922 44 29_Northern Oregon DBMS Exchange24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Tue, 3 Jul 2001 13:41:33 -0700350_- Hi Everyone!

For those of you in the Pacific northwest, our regular August NODE meeting will be held a couple of weeks early. This is to take advantage of a trip to the area being made by Blair Adamache. Please note that the meeting will be on a Tuesday instead of the regular Monday. We hope to resume our regular schedule in October. [...] 4967 14 43_Alan M Reed/OR1/AEPIN is out of the office.9_Alan Reed14_amreed@AEP.COM30_Tue, 3 Jul 2001 17:04:09 -0400365_us-ascii I will be out of the office from 07/03/2001 until 07/16/2001.

I will respond to your message when I return.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 4982 25 27_Re: another db2 v6 question12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 3 Jul 2001 18:43:51 -0500429_- The cost of a database is almost nil, so more often the question is why not have more databases. If you are interested in concurrency for DDL, utilities and dynamic SQL, then you generally want to keep the number of tables per database down. I put some rough sizing information into the Installation Guide under Calculating the EDM Pool Space Needed for Database Descriptors. -display db provides actual size information. [...] 5008 91 16_Case Insensitive11_Rolf Kramer23_RKramer@POWERLAN.COM.AU30_Wed, 4 Jul 2001 10:44:59 +1000371_iso-8859-1 We have a system that runs in both DB2 UDB and SQL Server. Originally developed in SQL, so we have a few issues.

SQL searches can be set up to be CASE INSENSITIVE, but DB2 does an exact match on CHAR column. We want to search this column regardless of case. Currently we use the UCASE function, but I believe this is not very efficient ie stage2 ? [...] 5100 61 27_Re: Primary Key Usefulness?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 3 Jul 2001 20:46:30 -0500362_- There are a number of tools that use the primary key to identify a row. For example, Princeton Softech's Move for DB2 uses it to identify whether a row being moved into an environment is a replacement or a new row - a la Oracle's UPSERT. With non Primary Key unique indexes, you're left with a - 803. (And how long will it be before DB2 also has UPSERT?) [...] 5162 174 27_Re: Primary Key Usefulness?11_Rolf Kramer23_RKramer@POWERLAN.COM.AU30_Wed, 4 Jul 2001 12:20:46 +1000638_- Reverse Engineering products also tend to use this to automate relationship creation.

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Wednesday, 4 July 2001 11:47 AM To: DB2-L@RYCI.COM Subject: Re: Primary Key Usefulness?



There are a number of tools that use the primary key to identify a row. For example, Princeton Softech's Move for DB2 uses it to identify whether a row being moved into an environment is a replacement or a new row - a la Oracle's UPSERT. With non Primary Key unique indexes, you're left with a - 803. (And how long will it be before DB2 also has [...] 5337 28 27_Re: Primary Key Usefulness?13_Henry J. Cobb14_hcobb@SLIP.NET30_Tue, 3 Jul 2001 22:27:03 -0500396_- > From: "S, Sanjeev (CTS)" > Is there anyone in the list who can give some exclusive benifits from the > keyword "Primary Key".

It helps to clean up the mistakes that DJRA makes in ASN.IBMSNAP_SUBS_COLS.IS_KEY, thereby preventing Apply from blowing up on updates.

How? By making the programmers intent clear to silly automated replication tools. [...] 5366 36 58_Delete performance when index not present on foreign keys.9_Linda DBA21_linda_dba@HOTMAIL.COM30_Wed, 4 Jul 2001 00:08:47 -0500358_- Dear List,

We have a parent table with 30 million rows and child table with 350 million records with RI of delete restrict.

Primary key of the child table is the partitioning key and we do not have any NPIs at this time.

Parent table primary key is : VENDOR_ID, SEQ_NO Child table primary key is : VENDOR_ID, SEQ_NO, CHILD_SEQ_NO [...] 5403 39 45_Catalog Tables - UDB "others" versus "OS/390"15_Matthias Pohlig12_mattp@WEB.DE30_Wed, 4 Jul 2001 00:55:27 -0500443_- Hi all you "gurus",

I'm currently trying to write a bunch of queries against the catalog to get a first notion about "foreign" databases, as I'm in the consulting business and it happens quite often to me to work with systems and databases I've never seen before.

This queries should "mainly" run on /390, but as I have no access to a mainframe right now, I had thougth I could start off with my local database on Win/NT. [...] 5443 15 25_Re: Compression questions15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Jul 2001 02:42:15 -0500510_- Hello Francis

I can't answer all your questions, but the second (dictionary pages in the bufferpool) I know for sure, that these pages are not in the bufferpool, but somewhere else in the DBM1-address-space, occupiing 64K for every open compressed tablespace.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 5459 25 23_compare dsntiaul output7_Eric Ng22_ngyh@PUBLICBANK.COM.MY30_Wed, 4 Jul 2001 02:50:59 -0500390_- We are using RRDF LOG APPLY to shadow production database DBx at Disaster Recovery(DR) site.

At DR site, besides the shadow database DBx, we created another database DBy with exactly the same DDLs except for the database name and tables qualifier.

At certain time, let says time T1, we will stop the LOG APPLY, take image copy at production site and restore it to DBy. [...] 5485 14 62_Re: Delete performance when index not present on foreign keys.15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Jul 2001 02:51:11 -0500477_- Linda

I am quite sure, that you will get a tablespace scan for the child table, if you delete a parent row. Why don't you change your partitioning key of your parent table and insert in front a YEAR-column in that key as well?

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 5500 25 25_Data base size vs extents8_duam lee16_duam_lee@USA.NET28_Wed, 4 Jul 2001 02:28:12 MDT438_US-ASCII Hi Listers What happens when a seconadry quantity is allocated once the primary quantity is full , like extension took place. Does it take more space than the estimated KBYTES for primary quantity. Or shall a designer have to calculate the space size taking the sec qty into consideration. Like a PRIQTY is 40 KB and its SECqty is 4 KB. So the size will be like (40 + maxextent*4). I m expecting a quick reply from experts. [...] 5526 43 27_Re: compare dsntiaul output15_Matthias Pohlig12_mattp@WEB.DE30_Wed, 4 Jul 2001 03:28:36 -0500309_- Eric, DSNTIAUL is nothing more than a dynamic SQL program. It also accects an order-by clause, pls. see "Utilities Guide: Running DSNTIAUL" In that way, you could "force" the correct sequence in (both) your unload data. - BTW, don't forget to supply a "good" index for the sorting :-)

HTH, Matt [...] 5570 27 49_What tables plan depends on and related questions15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL30_Wed, 4 Jul 2001 11:49:20 +0200348_us-ascii Hello Listers,

I've tried to get a list of tables plan depends on. This is quite easy (thanks to sysplandep and syspackdep catalog tables) as long as you do not use session temporary tables in a query. In such a case there is no information about dependencies in mentioned tables. Any suggestions how to get this information ? [...] 5598 49 29_Re: Data base size vs extents15_Matthias Pohlig12_mattp@WEB.DE30_Wed, 4 Jul 2001 04:57:42 -0500350_- Hi Duam,

IF you are talking about UDB for OS/390, yes, you'll have to take the secondary quantity specified in the create tablesapce into account.

The primary space is allocated ONCE, then , if the table grows, the secondary space will be allocated "n" times, "n" being the maximum number of Extents VSAM allows (normally, 122). [...] 5648 10 53_Re: What tables plan depends on and related questions15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Jul 2001 05:17:40 -0500312_- What's about looking at the DBRM. The SQL therein is quite readable.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 5659 47 20_Re: Case Insensitive14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 4 Jul 2001 09:45:47 -0500373_- DB2 UDB - but which version and platform. DB2 UDB for OS/390 is **not** the same as DB2 UDB for Windows Unix and OS/2. V6, V7 and now V7.2 have different capabilities.

I would create a pair of triggers (BEFORE INSERT and BEFORE UPDATE) to populate an indexed column with the uppercase version of the text, and then do searches against the uppercased version. [...] 5707 214 29_Re: Data base size vs extents0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 4 Jul 2001 10:05:56 -0500355_iso-8859-1 (also for OS390) If your tablespace is segmented then primary allocation will be minimum of the segsize. ie if your SEGSIZE=64 (pages) and PRIQTY=12 (pages) and SECQTY=12 (pages) then first allocation will be at least 7 extents, Priqty 12 + 6 * 12 secqty = 72 pages which is smallest combination of priqty and secqty greater than segsize. [...] 5922 77 29_Re: Data base size vs extents11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 4 Jul 2001 08:50:31 -0700568_us-ascii Hi, I think , in later release of OS/390 (Probably V2R9 onwards), the maximum no of extents of DB2 LDS dataset has been increased to 255.

Regards, Bikash --- Matthias Pohlig wrote: > Hi Duam, > > IF you are talking about UDB for OS/390, yes, > you'll have to take the secondary quantity > specified in the create tablesapce into account. > > The primary space is allocated ONCE, then , if the table grows, > the secondary space will be allocated "n" times, "n" being > the maximum number of Extents VSAM allows (normally, 122). > > [...] 6000 49 53_Re: What tables plan depends on and related questions11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 4 Jul 2001 08:54:34 -0700545_us-ascii Hi, It is quite easy to write a small REXX EXEC to read the DBRMs. Parse it to get the list of table names..

Regards, Bikash --- Piotr Tarnowski wrote: > Hello Listers, > > I've tried to get a list of tables plan depends on. This is quite > easy > (thanks to sysplandep and syspackdep catalog tables) as long as you > do not > use session temporary tables in a query. In such a case there is no > information about dependencies in mentioned tables. > Any suggestions how to get this information ? > > [...] 6050 66 49_Re: Catalog Tables - UDB "others" versus "OS/390"11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 4 Jul 2001 09:02:11 -0700587_us-ascii Hi , SYSCAT views are not available on DB2 on OS/390. But all the catalog tables are common with few exceptions and qualifier for them on all platform is SYSIBM. So instead of writing queries using SYSCAT views, use tables(qualifier as SYSIBM).

Regards, Bikash --- Matthias Pohlig wrote: > Hi all you "gurus", > > I'm currently trying to write a bunch of queries against the > catalog to get a first notion about "foreign" databases, > as I'm in the consulting business and it happens quite often to me > to work with systems and databases I've never [...] 6117 114 37_Re: Dynamic SQL - Cache some, not all14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 4 Jul 2001 11:12:21 -0500449_iso-8859-1 Let me try and make it all very plain.

1 - No Caching - CACHEDYN=NO - KEEPDYNAMIC=NO 2 - Local Caching Only - CACHEDYN=NO - KEEPDYNAMIC=YES 3 - Global Caching Only - CACHEDYN=YES - KEEPDYNAMIC=NO 4 - Full Caching - CACHEDYN=YES - KEEPDYNAMIC=YES

Regards, Richard +-----+-----+-----+-----+-----+ Richard_Yevich@YLAssoc.com YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS [...] 6232 23 9_Log apply20_Robert Gillespie (P)21_GillesRP@TELKOM.CO.ZA30_Wed, 4 Jul 2001 14:39:16 +0200297_iso-8859-1 Hi All

We have DB2 UDB for OS/390 v6

Is it possible to to analyze the log to extract all changes to a table and then apply these changes to a copy of the table on another subsytem and if possible what tool do you use to do this.

Thanks Robert Gillespie Telkom [...] 6256 27 62_Re: Delete performance when index not present on foreign keys.9_Linda Dba21_linda_dba@HOTMAIL.COM30_Wed, 4 Jul 2001 11:20:42 -0500406_- Walter,

Thanks for your reply. The reason we cannot add a year as a first column to the parent table(say T2)primary key is because the parent table is itself a child of another parent(say T1). T2 is very widely used in the application and it would be a tremendous effort to change the primary key of T2. Also T2 has a number of other children whose foreign keys would all have to be altered. [...] 6284 29 62_Re: Delete performance when index not present on foreign keys.9_Linda Dba21_linda_dba@HOTMAIL.COM30_Wed, 4 Jul 2001 12:14:00 -0500509_- Walter,

Here is another idea. Will this work? The SQL Reference manual states

The list of column names in the parent key must be identical to the | list of column names in a primary key or unique index in the parent | table T2. The column names must be specified in the same order as in | the primary key or unique index. If the list of column names does not | identify a primary key or unique key in the parent table, a unique key | constraint on the parent table is created implicitly. [...] 6314 53 62_Re: Delete performance when index not present on foreign keys.16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Wed, 4 Jul 2001 14:08:31 -0400762_us-ascii Hi, have you looked at DB2 Row Archive Manager Utility" http://www-4.ibm.com/software/data/db2imstools/details/#ram

HTH Tom Faglon







Linda Dba on 07/04/2001 12:20:42 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Re: Delete performance when index not present on foreign keys.





Walter,

Thanks for your reply. The reason we cannot add a year as a first column to the parent table(say T2)primary key is because the parent table is itself a child of another parent(say T1). T2 is very widely used in the application and it would be a tremendous effort to change [...] 6368 100 25_Bind index choice problem13_Neil Courtney19_NCOURTN@CLEAR.CO.NZ30_Thu, 5 Jul 2001 10:00:40 +1200342_iso-8859-1 Hi all, running DB2 UDB for OS/390 v6.1

I have some SQL that is now using index KP039X01 (details below), matching 1 column, in our production LPAR. Previously in production, and currently in our test LPAR, it was using index KP039X02, matching all 3 cols.

Any idea why it would now be choosing to use KP039X01? [...] 6469 28 22_Sort Of Oracle Barrage13_Mark kirkwood30_Mark.R.Kirkwood@POLICE.GOVT.NZ30_Thu, 5 Jul 2001 10:35:53 +1200413_us-ascii Having encountered DB2 after several years of Oracle work, I like the product ( using 7.1 on Solaris and Linux ).

The only gripe I have is the lack of features to mimic Oracle's autoextend and (manual) shrink on DMS containers - so even an experienced DBA can have loads halt due to size blowout within a tablespace etc - ( or else waste disk space due to overallocation to prevent such ...) [...] 6498 87 62_Re: Delete performance when index not present on foreign keys.13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM30_Wed, 4 Jul 2001 18:01:52 -0500451_us-ascii we had a simular problem, so what we did was an image copy, then unloaded to tape the rows to be deleted on both tables, removed the ri and deleted away. then replaced the ri - did a reorg and runstats.









"Thomas E. To: DB2-L@RYCI.COM Faglon" cc: index not present on foreign keys. Sent by: DB2 Data Base Discussion List [...] 6586 13 29_Re: Bind index choice problem8_Kim D.H.24_dahyunkim@LGEDS.LG.CO.KR30_Wed, 4 Jul 2001 19:14:39 -0500362_- How about using 'optimize for 1 row clause' ? I think it would make the SQL work as you want.

Have a Nice day..

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 6600 54 15_Re: IRLM - MCSA19_=?Big5?B?tPa3VqS4?=21_A000896@MS.SKH.ORG.TW30_Wed, 4 Jul 2001 15:10:04 +0800458_big5 HI : You can enlarge sys1.parmlib(ieasys00) CSA=(a,b) , where b is max ECSA ,let DB2 locking use ECSA to 2GB storage Tang shen yuan ----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, July 03, 2001 3:41 PM Subject: IRLM - MCSA



Hi,

Environment OS/390, DB2 V4.01. We had yesterday an out-of-storage indication from IRLM during 2 minutes, as shown here: [...] 6655 75 27_Re: compare dsntiaul output15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Thu, 5 Jul 2001 10:21:51 +1000416_- Hi Eric,

I'd like to suggest a good index would be the clustering index on each table, if you can't fit the directives on one line you could use the PARM('SQL') option of DSNTIAUL to order the selected columns by cluster index sequence (as Pohlig referred to).

If you unload the data using the same DSNTIAUL SQL you'll then be able to compare two data files easily, and they should be the same. [...] 6731 88 47_compilation error in Create procedure statement22_Sajja Nagalinga Murthy19_snlmurthy@YAHOO.COM30_Wed, 4 Jul 2001 19:54:00 -0700368_us-ascii

Hi,

I am getting this error while trying to create a procedure. The precompilation is working fine and .c and .sqc files are created. But is complaining of some munch attribute error. The error log is given below. Have anybody experienced this type of error in creating procedures with a new DB2 database. Please see the error at the end. [...] 6820 87 47_compilation error in Create procedure statement22_Sajja Nagalinga Murthy19_snlmurthy@YAHOO.COM30_Wed, 4 Jul 2001 19:56:04 -0700368_us-ascii

Hi,

I am getting this error while trying to create a procedure. The precompilation is working fine and .c and .sqc files are created. But is complaining of some munch attribute error. The error log is given below. Have anybody experienced this type of error in creating procedures with a new DB2 database. Please see the error at the end. [...] 6908 22 16_DB2 Data Sharing15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Thu, 5 Jul 2001 11:07:33 +1000350_- Hi,

Can anyone recommend a good book on Data Sharing? We don't want to become instant experts, so something that gives a broad overview of the various components and the benefits/drawbacks would be good enough - and if there's descriptions of various implementation strategies that would be a bonus.

Our plex is all OS390 DB2 V6 [...] 6931 70 20_Re: DB2 Data Sharing12_Susan Lawson24_susan_lawson@YLASSOC.COM30_Wed, 4 Jul 2001 23:00:40 -0500343_iso-8859-1 Hi Steve,

Here is my list of favorites...while some are a bit old, they still give the best solid overview information about Data Sharing. I do recommend reading redbooks on Parallel Sysplex (the 3 vol collection in particular) to understand the components in a data sharing environment that the DB2 books do not cover. [...] 7002 196 29_Re: Bind index choice problem13_Terry Purcell23_purcell_terry@YAHOO.COM30_Wed, 4 Jul 2001 21:31:05 -0700346_us-ascii Neil,

You have made no mention of which access path is actually the better performing, and have focused on which index provides the greater matchcols.

To answer why index X01 was chosen, simply because the optimizer believes this to be the most efficient access path. My assumptions on the access path are as follows: [...] 7199 27 49_Re: Catalog Tables - UDB "others" versus "OS/390"15_Matthias Pohlig12_mattp@WEB.DE30_Thu, 5 Jul 2001 01:31:37 -0500519_- Hi Bikash , thanks for the reply.

I must have been mistaken somewhere that the SYSCAT thing shows up on "big iron" UDB.

The headache this is giving me, as soon you dig in a bit deeper, you'll find a lot of smaller "discrepancies" between both worlds, e.g. differing colnames for the same thing (LAST_BIND_TIME / BOUNDTS in SYSPLAN), differing contents and formats (ISOLATION is CHAR(1) - R,S,T,U or CHAR(2) - RR,RS,CS,UR or different tables meaning the same - SYSINDEXCOLUSE vs. SYSKEYS etc. etc.) [...] 7227 34 30_Declarative RI and prefetching31_Douwe van Sluis, Infodemic B.V.26_d.b.van.sluis@INFODEMIC.NL30_Thu, 5 Jul 2001 08:49:15 +0200490_iso-8859-1 Dear List,

I was looking at the performance characteristics of declarative RI. I have set up a simple parent/child relationship. Looking at the DELETE CASCADE option I noticed that a DELETE of a parent did not use any prefetching (seq., dyn. or list) for deleting the childs. When deleting the childs with a explicit DELETE DB2 showed seq. prefetch in the access path and used it at execution time. But somehow DB2 is not using any prefetching during DELETE CASCADE? [...] 7262 26 62_Re: Delete performance when index not present on foreign keys.15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Thu, 5 Jul 2001 03:38:06 -0500314_- Linda

Concerning the matching index access, I am quite sure that your reasoning is correct. A long time ago, I attented a seminar about RI and if I remember it right, DELETE CASCADE can exploit an index, where the first columns are the primary key of the parent table, but the CHECK-utility can not. [...] 7289 138 29_Re: Bind index choice problem16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Jul 2001 14:27:33 +0530439_iso-8859-1 Hi Neil,

I believe Terry have explained everything but looking at the data you have provided, i certainly can say that the accesspath which is choosen i.e. X01 index is better than X02 because of the cluster ratio of X02 and the ORDERing required for your query. The difference between the firstkeycard for X01 and the fullkeycard for X02 is not much so that it can give the advantage over 44% vs 99% cluster ratio. [...] 7428 148 21_FW: [DB2-L] Log apply14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 5 Jul 2001 10:09:04 +0100377_iso-8859-1 Hi Robert,

"Howz-it" (is that how you spell it??)

Anyway, what you need is CA Log Analyzer.

This will extract log records from both the active and archive logs and format them for either dynamic or static "log apply" to an alternate subsystem. If you need any further information, please feel free to contact me (or Rudy de Sousa) offline. [...] 7577 68 62_Re: Delete performance when index not present on foreign keys.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Jul 2001 14:51:58 +0530560_- Linda,

If you can manage with adding the YEAR at the end of the partitioning key columns then you will not have any problems and idea will work quite nicely. You can also do one more thing.

Is it possible for you to add YEAR in the parent table as well and and define the columns YEAR, VENDOR_ID, SEQ_NO as an additional unique index. After this you can remove the present RI definition of this particular parent child relations from primary key vs foreign key to unique index vs child table columns. With V5 and above unique index can be [...] 7646 121 13_Re: Log apply15_Lankester, Andy22_Andy_Lankester@BMC.COM30_Thu, 5 Jul 2001 04:59:35 -0500379_iso-8859-1 Hi Robert,

Phil is right (hi Phil!) BMC Software does have a product to do this: LOG MASTER for DB2 (see below for more details).

A couple points about using this propagation technique:

1) If you do mass deletes from segmented tspaces then you will need to have CAPTURE DATA CHANGES on for those tables to force the log records to be written [...] 7768 49 18_ksh where are you?14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Thu, 5 Jul 2001 10:56:26 +0100383_iso-8859-1 Hi folks,

I tried to install DB2 UDB 7.1 for Linux on my home computer (I just can't get enough! ;-) ), running Linux-Mandrake 8.0, but it won't work because it's expecting a "ksh" shell, which Mandrake hasn't got.

Where can I get ksh?

Mandrake _has_ however, a pdksh shell (possibly a public domain ksh, going by the name). Is this good enough? [...] 7818 20 22_Re: ksh where are you?10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 5 Jul 2001 12:08:33 +0200374_us-ascii Try this

http://linux.davecentral.com/4910_programshell.html

HTH

MAx Scarpa

Useless DB2 sysprog

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 7839 18 22_Re: ksh where are you?10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 5 Jul 2001 12:13:58 +0200373_us-ascii Or better

http://www.linux-mandrake.com/en/72/index.php3?cd=installpwp&s=./pdksh

HTH again

Max Scarpa

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 7858 91 13_GBP Duplexing31_Tekin Yavuz (Garanti Teknoloji)21_TekinY@GARANTI.COM.TR30_Thu, 5 Jul 2001 13:27:48 +0300542_iso-8859-9







Hi all,

We decide to redefine our GBP's in duplexing mode after migrating DB2 V6 in OS/390.For our test system we have activated one of our GBP's in Duplex(allowed) mode. I wanna test how duplexing works when a failure in any Global Bufferpool occurs . I searched the books for any tool or a scenario for testing it,I mean to ba able to simulate a loss in primary structure of a GBP to switch db2 to use the secondary structure, but I coluld'nt find it.Has anyone have a tool like that. [...] 7950 111 17_Re: GBP Duplexing0_19_mike.holmans@BT.COM30_Thu, 5 Jul 2001 11:54:01 +0100417_iso-8859-1 IBM have a GBP-trasher, though it's not a supported product or anything like that. You need to talk to your local IBM rep and plead for it.

The difficulty is that coupling facility structures don't usually go wrong and it's therefore quite difficult to simulate a failure.

Mike Holmans Database Consultant BT Affinitis Computing Partners mike.holmans@bt.com [...] 8062 46 53_Re: What tables plan depends on and related questions16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Jul 2001 16:59:58 +0530575_- You can also use SYSIBM.SYSSTMT and parse it thru' REXX to get the table names. I believe if it is there in DBRM, it should be out there in SYSSTMT as well.

Regards Sanjeev

> -----Original Message----- > From: Piotr Tarnowski [SMTP:TarnowskiP@PROKOM.PL] > Sent: Wednesday, July 04, 2001 3:19 PM > To: DB2-L@RYCI.COM > Subject: What tables plan depends on and related questions > > Hello Listers, > > I've tried to get a list of tables plan depends on. This is quite easy > (thanks to sysplandep and syspackdep catalog tables) as long as you do not > use [...] 8109 23 12_Stored Table16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Jul 2001 17:08:32 +0530314_- Hi All,

Just now i was asked by someone if there is anything exists like STORED TABLE in DB2. I was also shown the book "A Guide to DB2" by C.J.Date(4th edition). I read it but could hardly "try" to gather anything. Is this exists physically or the physical existence is described as stored table ?. [...] 8133 92 29_Re: Data base size vs extents16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Jul 2001 17:12:41 +0530550_- Isn't it in SMS V1.3 and above ? Earlier it was 122.

Regards Sanjeev

> -----Original Message----- > From: Bikash Paul [SMTP:bikash_db@YAHOO.COM] > Sent: Wednesday, July 04, 2001 9:21 PM > To: DB2-L@RYCI.COM > Subject: Re: Data base size vs extents > > Hi, > I think , in later release of OS/390 (Probably V2R9 onwards), the > maximum no of extents of DB2 LDS dataset has been increased to 255. > > Regards, > Bikash > --- Matthias Pohlig wrote: > > Hi Duam, > > > > IF you are talking about UDB for OS/390, yes, > [...] 8226 58 30_Rexx calling DSNTPSMP on os39012_Bill Kimball19_wpkimball@YAHOO.COM30_Thu, 5 Jul 2001 05:28:25 -0700688_us-ascii I am working on a Rexx exec to interface with DSNTPSMP, copying a PL1 example and modifying it for Rexx. I am having trouble (-104) with an ALLOCATE CURSOR command that looks OK to me... Here are some snips: ----------------------------------------------------- RESULTSIZE = 32703 RESULT = LEFT(' ',RESULTSIZE,' ') BUILDSPNAME = "SYSPROC.DSNTPSMP" ADDRESS DSNREXX "EXECSQL DESCRIBE PROCEDURE :BUILDSPNAME INTO :SQLDA" SQLSTMTA= , "CALL :BUILDSPNAME (:ACTION, ", " :MEMBER_NAME, ", " :PSM_PROGRAM, ", " :BIND_OPTS, ", " :COMP_OPTS, ", " :PRECOMP_OPTS, ", " :PRELINK_OPTS, ", " :LINK_OPTS, ", " :LE_OPTS, ", " :INPUT_DSN, ", " :BUILDSCHEMA, ", " :BUILDNAME, ", " :OUT_STRING) [...] 8285 43 13_Re: Log apply16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Jul 2001 09:00:23 -0400436_iso-8859-1 Despite the unfavorable things many people say about CA (and I am one of them!), the CA Log Analyzer product does this quite well.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Robert Gillespie (P) [mailto:GillesRP@TELKOM.CO.ZA] Sent: Wednesday, July 04, 2001 8:39 AM To: DB2-L@RYCI.COM Subject: Log apply



Hi All

We have DB2 UDB for OS/390 v6 [...] 8329 31 19_Reference(s) needed0_24_lightsey@ITS.STATE.MS.US30_Thu, 5 Jul 2001 08:13:18 -0500398_us-ascii I've just been flung into the DB2/6000 world as an installation/support/expert(?!!) dba/sysprog. What I need, I guess, is a list of resources that can help me get as comfortable with AIX and DB2/6000 as I am in the OS/390 world. All suggestions welcome ( Mr. Spock and a mind-meld would be great) - in the meantime, I will soak up as many redbooks that say both AIX and DB2 as I can. [...] 8361 76 39_Re: How to report size of UDB database?12_tim malamphy20_timalamphy@YAHOO.COM30_Thu, 5 Jul 2001 06:32:07 -0700574_us-ascii I sure couldn't find a stospace utility or table in my UDB... And I sure hope someone else knows a better way of doing this, and lets us both know, but here's what I'd do...

If you've specified a file system when you created your database, ie CREATE DATABASE on /filesystemname just do a "df -k" AIX command and next to each filesystem, it will give you the amount of space. All the tables, directories, catalog, etc will be included in this number. If you let it default, you will have to chase down the /home/userid/db2instance name directory to find [...] 8438 143 17_Re: GBP Duplexing12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 5 Jul 2001 08:54:27 -0500381_us-ascii



When I was in the IBM datasharing workshop class, I believe they simulated a CF failure by varying offline the CF links. Unless you are running on a testbed-type system, i.e. not sharing the system with a production workload, you may not be able to do this.











mike.holmans@BT.COM on 07/05/2001 05:54:01 AM [...] 8582 33 0_11_Barry Noble17_bdnoble@YAHOO.COM30_Thu, 5 Jul 2001 06:57:43 -0700332_us-ascii SET DB2-L DIGEST

How long a minute is depends on what side of the bathroom door you're on.

Documentation is like sex: when it is good, it is very, very good; and when it is bad, it is better than nothing. - Dick Brandon

C.O.B.O.L - Completely Obsolete Boring Old Language - But at least it works! [...] 8616 133 42_Omegamon monitoring of distributed threads0_20_vpacheco@AMADEUS.NET30_Thu, 5 Jul 2001 16:40:09 +0200523_us-ascii Hello DB2 List

Any idea about the problem I have just reported to Candle?

I wonder if any of you have experienced similar situations and if you manged to resolve it.

TIA Vitor Pacheco ----- Forwarded by Vitor Pacheco/MUC/AMADEUS on 05/07/01 16:37 -----

From: Vitor Pacheco (ADP/DS/SO/SOD) on 05/07/2001 16:36



To: GERMAN_SUPPORT@Candle.com -



cc: Konrad Lechner/MUC/AMADEUS@AMAWORLD ADP/DS/SX/SXM DBA DB2 Marion Roidl/MUC/AMADEUS@AMAWORLD ADP/DS/SX/SXM [...] 8750 124 51_Re: compilation error in Create procedure statement11_Jakobs, Jef18_JJakobs@HARTIC.COM30_Thu, 5 Jul 2001 09:49:22 -0500704_iso-8859-1 Murthy,

To me this sounds like you need to customize your compilation command... From "ReleaseNotes.txt" (part of fixpak 3, UDB 7.1):

34.4.3 Customizing the Compilation Command

The installation of the Application Development Client provides a default compilation command that works for at least one of the compilers supported on each platform:

AIX: IBM C Set++ for AIX Version 3.6.6 Solaris: SPARCompiler C++ Versions 4.2 and 5.0 HP-UX: HP-UX C++ Version A.12.00 Linux: GNU/Linux g++ Version egcs-2.90.27 980315 (egcs-1.0.2 release) PTX: ptx/C++ Version 5.2 OS/2: IBM VisualAge C++ for OS/2 Version 3 Windows NT and Windows 2000: Microsoft Visual C++ Versions [...] 8875 14 10_mail help?12_Confidential26_confidential@ECFACTORY.NET30_Thu, 5 Jul 2001 11:06:12 -0400377_iso-8859-1 What command(s) do I use and where do I send the e-mail to suspend my listserv mail while on vacation? Thanks for your help.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 8890 21 15_PC parm in IRLM9_Bob Smith29_rjs900@EMAIL1.DSS.STATE.VA.US30_Thu, 5 Jul 2001 11:21:18 -0400460_iso-8859-1 Hello All,

Does anyone know what the parameter PC stands for in reference to the IRLM?

Thanx, -Bob P.S.I know how it is used, and that the general guideline is to set PC=NO to optimize locking.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 8912 32 35_Re: [Re: Data base size vs extents]8_duam lee16_duam_lee@USA.NET28_Thu, 5 Jul 2001 09:23:16 MDT272_US-ASCII Hi all , thanks to all. I m getting quick reply but i feel most of you did not get my probelm. I m writing bit clearly. While estimating for a systems data space requirement the methodology to be followed is like this or some thing wrong with my assumption. [...] 8945 69 20_SQL CASE Expressions19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 5 Jul 2001 10:22:59 -0500467_ISO-8859-1 Hi all and TIA, We'd like to avoid dynamic SQL in a situation as follows: There are 7 possible values, for 7 columns spread across 4 tables passed to the program, at least one of which must be populated before the SQL will be executed. In this example we wish to retrieve all rows that are a hit on the one populated column and the other possible values passed to the program are blank. The following SQL works but doesn't perform well as you'll see: [...] 9015 24 21_Internal DATE storage10_Mark Labby16_mlabby@PHEAA.ORG30_Thu, 5 Jul 2001 11:20:32 -0400345_us-ascii Does anyone definitely know how date and timestamp columns are stored in DB2? I was always under the impression that it is the number of days past a set date in history. (01/01/0001 perhaps?)

I was just told that it is stored in a 4 byte unsigned field that would have the hex equivalent of 20|01|01|01 for January 1. 2001. [...] 9040 38 34_Re: Rexx calling DSNTPSMP on os3909_Jim Ruddy18_jaruddy@US.IBM.COM30_Thu, 5 Jul 2001 10:06:32 -0500540_- Please put the following in your exec after the ALLOCATE CURSOR execution and post the results:

SAY 'SQLCODE ='SQLCODE SAY 'SQLERRM ='SQLERRM SAY 'SQLERRMC='SQLERRMC SAY 'SQLERRP ='SQLERRP SAY 'SQLERRD ='SQLERRD.1',', || SQLERRD.2',', || SQLERRD.3',', || SQLERRD.4',', || SQLERRD.5',', || SQLERRD.6

SAY 'SQLWARN ='SQLWARN.0',', || SQLWARN.1',', || SQLWARN.2',', || SQLWARN.3',', || SQLWARN.4',', || SQLWARN.5',', || SQLWARN.6',', || SQLWARN.7',', || SQLWARN.8',', || SQLWARN.9',', || SQLWARN.10 SAY 'SQLSTATE='SQLSTATE [...] 9079 32 25_Re: Internal DATE storage13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 5 Jul 2001 11:34:38 -0400425_iso-8859-1 Hello Mark,

This is documented in the SQL reference. Here's the link for DB2 UDB V7 Os/390 http://publibz.boulder.ibm.com:80/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH10/2.8.7 .1 in which it states:

"The internal representation of a date is a string of 4 bytes. Each byte consists of two packed decimal digits. The first 2 bytes represent the year, the third byte the month, and the last byte the day." [...] 9112 32 14_Re: mail help?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 5 Jul 2001 11:38:22 -0400357_iso-8859-1 Hello Confidential, (can we call you confidenti, for short?)

The easiest way to do this is go to the web site (see below) and follow the instructions there.

Let me personally commend you for wishing to do this rather than let those annoying out-of-office notices be sent to the list or to everyone posting to the list. Thanks. [...] 9145 63 25_Re: Internal DATE storage9_Chris Tee23_chris.tee@UK.ZURICH.COM30_Thu, 5 Jul 2001 16:47:27 +0100419_us-ascii Mark,

The date (and time and timestamp) IS stored as unsigned packed decimal. I think it is mentioned in the Diagnosis Guide.

Chris







Mark Labby cc: Sent by: DB2 Subject: [DB2-L] Internal DATE storage Data Base Discussion List



05/07/01 16:20 Please respond to DB2 Data Base Discussion List [...] 9209 57 25_Re: Internal DATE storage15_BARCLAY RICHARD30_richard.barclay@FORTISBANK.COM30_Thu, 5 Jul 2001 17:43:01 +0200299_- Not sure if you've won or lost your soda but Diagnosis Guide and Ref states,

Date: 4 bytes packed decimal 2 bytes for year, 1 for month 1 for day. as you show below. Time: 3 bytes, 1 for hour, 1 for mins etc, Timestamp, concatenates date and time and then has 3 bytes for microseconds [...] 9267 63 14_Re: mail help?9_Chris Tee23_chris.tee@UK.ZURICH.COM30_Thu, 5 Jul 2001 16:51:19 +0100515_us-ascii Is that Al Confidenti??







"Seibert, Dave" cc: Sent by: DB2 Data Subject: Re: [DB2-L] mail help? Base Discussion List



05/07/01 16:38 Please respond to DB2 Data Base Discussion List











Hello Confidential, (can we call you confidenti, for short?)

The easiest way to do this is go to the web site (see below) and follow the instructions there. [...] 9331 89 23_Re: Reference(s) needed11_Judy Kibler21_judith.kibler@TRW.COM30_Thu, 5 Jul 2001 10:57:02 -0500552_us-ascii Bruce,

Having recently been thrust into the same situation as you have been, I asked the same questions. Here are some of the things that these kind folks replied to me, so that I can try to help you as they helped me!

"A Complete Guide to DB2 Universal Database" by Don Chamberlin - I have this one on order, but it has not arrived yet, so I can't comment personally. "DB2 Universal Database v ??? for UNIX, Windows, and OS/2 Certification Guide - I have the v 6.1 edition. Even though it is a certification guide, it is a [...] 9421 126 19_Re: PC parm in IRLM14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Thu, 5 Jul 2001 12:01:42 -0400518_iso-8859-1 Bob,

I always wonder why we have so many DB2 installation defaults where we typically DON'T want to do something (PC=NO)? Anyway, PC stands for Cross-Address-Space Program Call. PC=YES puts the IRLM lock control block structure in the IRLM private address space and uses the cross-address-space program call to address the structure. PC=NO puts the lock control block structure in the ECSA and uses the MAXCSA parameter to determine the maximum amount of CSA/ECSA storage that can be allocated. [...] 9548 81 35_Re: [Re: Data base size vs extents]12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 5 Jul 2001 11:01:09 -0500546_us-ascii Hello Duam, I'm still not sure we understand your question. Are you doing a space estimate for a database that does not exist yet, or are you trying to calculate the total size of an existing database ? If you are estimating for a future database, the old-school way to do it is to calculate: table size = average row length * estimated number of rows (becomes primary quantity) database size = sum(all table sizes) * 1.5 (to account for indexes) total dasd needed = database size * 3 (to account for workfiles, image copies, etc.) [...] 9630 117 23_Re: Reference(s) needed13_Terry McCombs20_tmccomb@SEDGWICK.GOV30_Thu, 5 Jul 2001 11:08:03 -0500529_iso-8859-1 Try this link for the "other" DB2 list:

http://people.mn.mediaone.net/scottrmcleod



-----Original Message----- From: Judy Kibler [mailto:judith.kibler@TRW.COM] Sent: Thursday, July 05, 2001 10:57 AM To: DB2-L@RYCI.COM Subject: Re: Reference(s) needed



Bruce,

Having recently been thrust into the same situation as you have been, I asked the same questions. Here are some of the things that these kind folks replied to me, so that I can try to help you as they helped me! [...] 9748 34 15_DB2 V6 00E500130_19_csutfin@AMSOUTH.COM30_Thu, 5 Jul 2001 11:13:35 -0500458_us-ascii I am hoping someone has encountered this problem. We have had a question on IBMLINK for some time but have not received any answers.

We have recently migrated from DB2V5 to DB2V6, running on OS390 V2.8.

Occasionally a utility job will abend with the following message ABEND=S04E U0000 REASON=00E50013 or UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E50013' . When the job is started over, it runs successfully. [...] 9783 172 19_Re: PC parm in IRLM9_Bob Smith29_rjs900@EMAIL1.DSS.STATE.VA.US30_Thu, 5 Jul 2001 12:19:36 -0400425_iso-8859-1 RE: PC parm in IRLMHello Jean,

Thank you for that clarification. Where did you find this answer? I spent three hours of my own time trying to find out to no avail!

Thank you for your time, -Bob -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Crichton, Jean Sent: Thursday, July 05, 2001 12:02 PM To: DB2-L@RYCI.COM Subject: Re: PC parm in IRLM [...] 9956 79 35_Re: [Re: Data base size vs extents]10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Thu, 5 Jul 2001 11:18:52 -0500454_us-ascii Duam, When we define a DB2 subsystem, the basic requirements are 6 volumes (to spread out the BSDS, logs, DSNDB07 sortwork files & catalog/directory onto), minimum system memory of about .2 GB (point 2) for small bufferpools, EDM pool & RID pools.

Once you have your DB2 OS390 type system defined, you can add many user defined databases, whose space requirements will differ depending on how many tablespaces & indexes you require. [...] 10036 13 19_Re: DB2 V6 00E5001315_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM30_Thu, 5 Jul 2001 11:35:46 -0500365_us-ascii Check and see if your subsystem came down while the utility was running. That is what the error message indicates.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 10050 15 24_Re: Excessive Wait Times12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 5 Jul 2001 11:41:40 -0500308_- You have found one of the situations where the Strobe diagnosis is not useful. If you have multiple tasks and all are waiting, then which one or ones do you identify? If you're running under the DSN command, DSNECP00 is supposed to be waiting almost all of the time.

Roger Miller, DB2 for z/OS [...] 10066 19 10_WEPR state6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM30_Thu, 5 Jul 2001 11:42:39 -0500541_- All,

Has anyone encountered WEPR state either on tablespaces or indices ? We encountered with WEPR state on a partition of a partitioned tablespace immediately after a successful reorg on that partition.

What may have triggered this to happen? Any thoughts ?

-Chenniappan USDA

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 10086 182 19_Re: PC parm in IRLM14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Thu, 5 Jul 2001 12:47:24 -0400375_iso-8859-1 Bob,

You can find some good information in the DB2 Installation Guide section on the DSNTIPJ IRLM panel (topic 2.4.21 in the V7 installation manual) and the section on virtual storage for address spaces (Common Service Area topic 2.1.2.6 in the V7 manual). Also, I've had problems with MAXCSA that have made me pretty well memorize that information!!! [...] 10269 98 35_Re: [Re: Data base size vs extents]50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM30_Thu, 5 Jul 2001 12:54:24 -0400592_iso-8859-1 I would put the BSDS & LOGS are separate volumes attached to separate controllers in case of failures.

Bob Knight

-----Original Message----- From: Missy Case [mailto:Missy.Case@FIRSTDATACORP.COM] Sent: Thursday, July 05, 2001 12:19 PM To: DB2-L@RYCI.COM Subject: Re: [Re: Data base size vs extents]



Duam, When we define a DB2 subsystem, the basic requirements are 6 volumes (to spread out the BSDS, logs, DSNDB07 sortwork files & catalog/directory onto), minimum system memory of about .2 GB (point 2) for small bufferpools, EDM pool & RID pools. [...] 10368 23 40_Re: Closing a long run UOW and rollback.12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 5 Jul 2001 11:57:23 -0500539_- I always worry when we talk about the cancel without a rollback. This means that you have to recover to a prior point in time. When you cancel, you have trashed all of the data updated by that unit of work and taken responsibility for the data integrity. If you don't have a good quiesce point, then I've seen situations where customers require weeks of work to get almost everything back. You will need a good log analysis tool to do the job. If you were updating a customer or account master or another table that is used by most [...] 10392 61 19_Re: DB2 V6 00E5001311_Suresh Sane21_data_arch@HOTMAIL.COM30_Thu, 5 Jul 2001 11:58:36 -0500358_- Carol,

Besides the obvious case of DB2 subsysyem coming down (I assume your subsystem does NOT crash that often!), I would check for any CANCEL THREAD. At a client site we used to have a job cancelling all threads in preparation for a systemwide quiesce that generated the 00E50013. Is it possible an explicit batch job cancel is being issued? [...] 10454 53 23_Re: Reference(s) needed18_Gert van der Kooij15_geko@WANADOO.NL30_Thu, 5 Jul 2001 18:55:49 +0200688_iso-8859-1 Hi, You can try this redbook, it's an old one but almost all of it still applies: http://www.redbooks.ibm.com/redbooks/GG244431.html



----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Thursday, July 05, 2001 3:13 PM Subject: Reference(s) needed



> I've just been flung into the DB2/6000 world as an > installation/support/expert(?!!) dba/sysprog. > What I need, I guess, is a list of resources that can help me get as > comfortable with AIX and DB2/6000 as I am in the OS/390 world. > All suggestions welcome ( Mr. Spock and a mind-meld would be great) - in > the meantime, [...] 10508 38 17_Hai Listers C/37015_Durga Vamsidhar22_durga.vamsidhar@DB.COM30_Thu, 5 Jul 2001 18:52:33 +0100303_iso-8859-1 Hi Listers,

Does anybody know mainframe C, basically C/370. Can somebody help me how to find out the Consistency token in the Load module of the C-program. Since I am getting an abend -805 but plan and package bound properly.



Thanks in advance, Vamsidhar



10547 145 23_Re: Reference(s) needed14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Thu, 5 Jul 2001 10:05:05 -0700443_iso-8859-1 Bruce, In addition to what others have recommended, the redbook, DB2 UDB V7.1 Performance Tuning Guide, is geared towards UDB on AIX, Essential System Administration, AEleen Frisch, from O'Reilly covers AIX as well as other UNIX administration, and AIX Administration by DeRoest. Phil



-----Original Message----- From: lightsey@ITS.STATE.MS.US To: DB2-L@RYCI.COM Sent: 7/5/01 6:13 AM Subject: Reference(s) needed [...] 10693 141 19_Re: DB2 V6 00E5001314_Crichton, Jean21_jcrichton@FEDDATA.COM30_Thu, 5 Jul 2001 13:10:49 -0400583_iso-8859-1 Carol,

The 00E50013 is generated by the CANCEL exit in DB2, which is driven by any connected TCB abend or the issuing of the -STOP DB2 MODE(FORCE) command. I don't know what you might have already done for this problem, but the first thing I would do (after determining whether a human had done anything to cause the internal cancel) is to run EREP against SYS1.LOGREC to get a report of software errors for a time interval that includes the time before and after your error. Search for the 00E50013 record(s) and see if any other software error was generated [...] 10835 24 44_REORG utility and SORTWK's question (DB2 V6)11_Paul Turpin32_pault@ALUMMAIL.SSB.ROCHESTER.EDU30_Thu, 5 Jul 2001 12:15:04 -0500334_- In regard to the REORG utility, what happens if SORTDEVT is not included and SORTWK's are not provided in the jcl? Here is an excerpt from the Utility Guide, but it doesn't answer my question.

In section 2.16.1.4 Option descriptions for REORG TABLESPACE options of the DB2 UDB for OS/390 V6 Utility Guide and Reference: [...] 10860 33 25_Re: Compression questions12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 5 Jul 2001 12:21:41 -0500381_- There is so much variation in compression, that rules of thumb are not very helpful. One of the classic rules of thumb is that it's a lot cheaper to compress if the compression ration is good, say 50% or more. We've seen big improvements (in the range of two times to four times faster) in compression on the zSeries. There is no compression on the indexes, so that's easy. [...] 10894 65 21_Re: Hai Listers C/37012_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Thu, 5 Jul 2001 12:28:57 -0500351_iso-8859-1 Vamsidhar:

Each language handles it differently because of the differences in their optimizers. Do you have the listing from the compile? I would be willing to work with you off line. We handle tokens for just about everything except C, because we have seen no interest.

If interested contact me at ghodge@attglobal.net. [...] 10960 50 14_Re: WEPR state10_Tom Taylor17_ttaylor@CHUBB.COM30_Thu, 5 Jul 2001 13:26:29 -0400462_us-ascii Chenny We had WEPR's in the past.... It was primarily due to our OEM product CDB supercopy. They were intercepting the updates and not applying them correctly. The problem has since been fixed Tom







Chenny @RYCI.COM> on 07/05/2001 12:42:39 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 11011 70 19_Re: DB2 V6 00E500130_19_csutfin@AMSOUTH.COM30_Thu, 5 Jul 2001 12:35:59 -0500640_us-ascii No, the subsystem stays up and active. If it didn't I would be pressing IBM for answers.

I get the following in the DBM1 task:

IEA995I SYMPTOM DUMP OUTPUT SYSTEM COMPLETION CODE=0C4 REASON CODE=00000011 TIME=00.13.09 SEQ=04256 CPU=0000 ASID=003A PSW AT TIME OF ERROR 077C2000 00026212 ILC 4 INTC 11 ACTIVE LOAD MODULE ADDRESS=0000B598 OFFSET=0001AC7A NAME=IDCAMS DATA AT PSW 0002620C - 5860B04C 1F334330 60031E34 GPR 0-3 00000001 0000A828 00000000 00000000 GPR 4-7 00000001 0000A068 0BB60C12 0003F440 GPR 8-11 00041648 C9C4C3E3 E2C4C5F0 00034200 GPR 12-15 010001C0 0000A7D0 40026202 00000000 END OF SYMPTOM DUMP [...] 11082 70 23_Re: Reference(s) needed10_Raghu Kota21_raghukota@HOTMAIL.COM30_Thu, 5 Jul 2001 17:45:34 -0000725_- This is very good source man!! Thankyou.



>From: Gert van der Kooij >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Reference(s) needed >Date: Thu, 5 Jul 2001 18:55:49 +0200 > >Hi, >You can try this redbook, it's an old one but almost all of it still >applies: >http://www.redbooks.ibm.com/redbooks/GG244431.html > > >----- Original Message ----- >From: >Newsgroups: bit.listserv.db2-l >To: >Sent: Thursday, July 05, 2001 3:13 PM >Subject: Reference(s) needed > > > > I've just been flung into the DB2/6000 world as an > > installation/support/expert(?!!) dba/sysprog. > > What I need, I guess, is [...] 11153 74 34_Re: Rexx calling DSNTPSMP on os39012_Bill Kimball19_wpkimball@YAHOO.COM30_Thu, 5 Jul 2001 10:45:58 -0700396_us-ascii OK, thanks.... I had that, but didn't send it...Here you go...

SQLCODE = -104 SQLERRM = SQLERRM SQLERRMC = C1:C101, C102, ... C200 SQLERRP = DSNTZALO SQLERRD = 0,0,0,0,0,0 SQLWARN = , , , , , , , , , , SQLSTATE = 42601 SQLCODE = -104 SQLERRM = SQLERRM SQLERRMC = C1:C101, C102, ... C200 SQLERRP = DSNTZDSC SQLERRD = 0,0,0,0,0,0 SQLWARN = , , , , , , , , , , SQLSTATE = 42601 [...] 11228 66 19_Re: DB2 V6 00E5001314_Johnson, Julie23_julie.johnson@UCONN.EDU30_Thu, 5 Jul 2001 13:36:17 -0400463_iso-8859-1 Hi All,

We are DB2V5 OS390 V2.8 and have been encountering the same abend when running runstats against some very large Peoplesoft databases. Immediately, the DBM1 address space gets an 80A-10 abend. We recently migrated the DB2 subsystems in question to T26 processor from an R63 (where the jobs ran without a problem). Both the T26 and R63 are running v2.8 at the same maintenance level as guest operating systems under VM CPLEVEL 2.3.0. [...] 11295 176 21_Re: Hai Listers C/37012_Isaac Yassin20_yassini@BEZEQINT.NET30_Thu, 5 Jul 2001 20:50:27 +0200351_iso-8859-1 Hi, It is splitted to 4 parts, reversed and those part that start wit 0 are not included ;-) I put a rexx sometime ago about how to do it. Here it goes again: /* REXX */ TRACE n PARSE upper arg PARM call init /****************************************************************/ LIBS: LIB = "'HLQ.DBRMLIB.INWORK("PARM")'" CALL CHKLIB LIB [...] 11472 16 24_Timestamp in Primary Key0_20_bjnigh@HOUSEHOLD.COM30_Thu, 5 Jul 2001 10:44:44 -0700494_us-ascii Are there any performance problems associated with having a timestamp column as part of a primary key in DB2 for OS/390? If data is to be retreived for a specific month, does having the timestamp pose a problem (like having to parse the date)?

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 11489 67 32_Re: BIND and SQL errors in Rel 69_Colin Fay13_cfay2@CSC.COM30_Thu, 5 Jul 2001 13:26:25 -0400334_us-ascii Hi,

OS/390 Db2 v 6.1



The following SQL fails in version 6.1 but runs fine in version 5.

The addition of last predicate statement ' AND TABLE_OWNER = CURRENT SQLID ' causes if to fail with a SOC4 reson code 0011. Removing it .. .it runs. The SP_CP_... are views of the system catalog tables. [...] 11557 167 51_Re: compilation error in Create procedure statement22_Sajja Nagalinga Murthy19_snlmurthy@YAHOO.COM30_Thu, 5 Jul 2001 11:18:11 -0700493_us-ascii



Thanks Jakobs, The problem was the new compiler was installed in a different directory than the old conventional directory. By setting the compiler variable DB2_SQLROUTINE_COMPILE_COMMAND with the path, it is working fine. Thanks again for your fix. Murthy





"Jakobs, Jef" wrote: Murthy,

To me this sounds like you need to customize your compilation command... From "ReleaseNotes.txt" (part of fixpak 3, UDB 7.1): [...] 11725 95 19_Re: DB2 V6 00E5001312_McKown, John22_JMckown@HEALTHAXIS.COM30_Thu, 5 Jul 2001 13:20:03 -0500673_- I don't know DB2 well, but the 80A-10 indicates that the REGION is too small. Or maybe too fragmented to get the amount of storage requested.

---------------------------------------------------------------------- John McKown HealthAxis

All opinions are my own and are not the opinions of my employer. 2+2=5 For sufficiently large values of "2".

> -----Original Message----- > From: Johnson, Julie [SMTP:julie.johnson@UCONN.EDU] > Sent: Thursday, July 05, 2001 12:36 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 V6 00E50013 > > Hi All, > > We are DB2V5 OS390 V2.8 and have been encountering the same abend when > running runstats against some very [...] 11821 14 19_Re: DB2 V6 00E5001315_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM30_Thu, 5 Jul 2001 13:13:03 -0500427_us-ascii Since it is IDCAMS getting the OC4, I would definitely speak with IBM about it. What put level are you at with V6? We were having problems with reorg before we came up to 0104.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 11836 160 19_Re: DB2 V6 00E500130_19_csutfin@AMSOUTH.COM30_Thu, 5 Jul 2001 13:24:44 -0500731_iso-8859-1 A little more info on this.

The DB2 subsystem stays up and active.

All I get in the MSTR task is a message about the job abending 00.13.10 STC31421 DSN3201I - ABNORMAL EOT IN PROGRESS FOR USER=SCHDMODL CONNECTION-ID=UTILITY CORRELATION-ID=CSMA172D JOBNAME=CSMA172 TCB=009FD528

In the DBM1 task, I get the abend message: IEA995I SYMPTOM DUMP OUTPUT SYSTEM COMPLETION CODE=0C4 REASON CODE=00000011 TIME=00.13.09 SEQ=04256 CPU=0000 ASID=003A PSW AT TIME OF ERROR 077C2000 00026212 ILC 4 INTC 11 ACTIVE LOAD MODULE ADDRESS=0000B598 OFFSET=0001AC7A NAME=IDCAMS DATA AT PSW 0002620C - 5860B04C 1F334330 60031E34 GPR 0-3 00000001 0000A828 00000000 00000000 GPR 4-7 00000001 0000A068 0BB60C12 0003F440 [...] 11997 113 32_Re: BIND and SQL errors in Rel 60_19_csutfin@AMSOUTH.COM30_Thu, 5 Jul 2001 13:32:43 -0500506_us-ascii Colin,

What version of CA/Platinum batch processor are you running? If it's not P99D, that may be your problem.

I have found that what we think is toleration and what CA thinks of as toleration are totally different.

Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214







Colin Fay cc: Sent by: DB2 Subject: Re: BIND and SQL errors in Rel 6 Data Base Discussion List [...] 12111 41 49_Use of order by in subselect of create view fails12_tim malamphy20_timalamphy@YAHOO.COM30_Thu, 5 Jul 2001 11:38:33 -0700496_us-ascii I'm using UDB 7.1 on AIX... I'm trying to create a view which joins 5 tables. I got it working. Now I (read : my user) wants to sort the output with an order by. This seems to work fine when executed outside the CREATE VIEW, but gives me a syntax error (DB2 wants a GROUP BY statement) when executed as part of a view creation. Can I do this? I don't see anything, anywhere in the doc that says I can't. Just for fun I changed the ORDER BY to a GROUP BY. Then DB2 gives me an error [...] 12153 14 34_Re: Rexx calling DSNTPSMP on os3909_Jim Ruddy18_jaruddy@US.IBM.COM30_Thu, 5 Jul 2001 13:28:26 -0500447_- The C1:C101..C200 says that C1 was found but a cursor name in the range of C101 to 200 was expected. Allocated cursors must be in this range for REXX.

Jim Ruddy DB2 for z/OS and OS/390 Development

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 12168 16 48_Re: REORG utility and SORTWK's question (DB2 V6)9_Jim Ruddy18_jaruddy@US.IBM.COM30_Thu, 5 Jul 2001 13:32:02 -0500327_- What happens depends on how many records need to be sorted and how your Sort product is installed. You may get lucky and the sort can be done in memory or the sort product may dynamically allocate the required sort work datasets anyway (or a combination of the two).

Jim Ruddy DB2 for z/OS and OS/390 Development [...] 12185 195 19_Re: DB2 V6 00E5001315_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 5 Jul 2001 13:48:56 -0500360_iso-8859-1 "Explanation: A DB2 execution unit has been abended. This abend code must ultimately be issued by resource managers when the CANCEL exit is driven as a result of a suspend request. "

The phrase "the CANCEL exit" bothers me. Is there such a thing as a CANCEL exit that would be invoked automatically when something requests a suspension? [...] 12381 51 19_Re: DB2 V6 00E500130_19_csutfin@AMSOUTH.COM30_Thu, 5 Jul 2001 13:57:15 -0500445_us-ascii Dan,

We have an ETR open with IBM, just waiting for an answer.

We are at level 0103 on DB2 V6.

Thanks for the suggestions

Carol Sutfin







Daniel Sullivan cc: Sent by: DB2 Data Subject: Re: DB2 V6 00E50013 Base Discussion List



07/05/01 01:13 PM Please respond to DB2 Data Base Discussion List [...] 12433 81 32_Re: BIND and SQL errors in Rel 616_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Thu, 5 Jul 2001 12:00:49 -0700553_iso-8859-1 Try 'AND TABLE_OWNER = USER'

-----Original Message----- From: Colin Fay [mailto:cfay2@CSC.COM] Sent: Thursday, July 05, 2001 10:26 AM To: DB2-L@RYCI.COM Subject: Re: BIND and SQL errors in Rel 6



Hi,

OS/390 Db2 v 6.1



The following SQL fails in version 6.1 but runs fine in version 5.

The addition of last predicate statement ' AND TABLE_OWNER = CURRENT SQLID ' causes if to fail with a SOC4 reson code 0011. Removing it .. .it runs. The SP_CP_... are views of the system catalog tables. [...] 12515 111 34_Re: Rexx calling DSNTPSMP on os39012_Troy Coleman19_Colematr@MEIJER.COM30_Thu, 5 Jul 2001 15:21:10 -0400306_US-ASCII I had a similar problem but mine was for cursor in the range of C1 to C99 and I was using C101. I thought this was silly but I followed the message and named my cursor C99. Does anyone know what the naming convention is for cursors? I thought I would name cursor like I do in COBOL...WRONG!! [...] 12627 94 14_Re: WEPR state12_Susan Lawson22_lawson_susan@YAHOO.COM30_Thu, 5 Jul 2001 12:24:12 -0700579_us-ascii Chenny,

A WEPR status is a 'write error page range'. It can occur when a page range is in error. You will need to run a RECOVER utility on the affected data.

Regards, Susan --- Tom Taylor wrote: > Chenny > We had WEPR's in the past.... It was primarily due > to our OEM product CDB > supercopy. They were intercepting the updates and > not applying them > correctly. > The problem has since been fixed > Tom > > > > > Chenny > @RYCI.COM> > on 07/05/2001 > 12:42:39 PM > > Please respond to [...] 12722 46 42_DSNTIAUL and CASE - char(1) to varchar(3)?14_Jessen Michael27_JessenMichael@JOHNDEERE.COM30_Thu, 5 Jul 2001 14:27:22 -0500313_iso-8859-1 DB2 on OS/390 V6. I'm using the SQL parm with DSNTIAUL to create an unload dataset & the associated load cards.

When I use a case expression within the sql, it's unloading a 3 character varchar field instead of the actual 1 character format of the field. My select: SELECT T1.CR_AGRMT_KEY, [...] 12769 116 32_Triggers - standards enforcement23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 5 Jul 2001 15:28:33 -0400433_ISO-8859-1 Hi,

We are planning to enforce some sort of standard check against triggers when they are migrated to QA/PROD environment. We already have a naming standard in place, for triggers. But I was wondering if there are any sites that enforces any other rules against triggers, based upon their experience/problems with triggers. List of DOs/DONTs for trigger other than the limitation/features mentioned in manual. [...] 12886 61 34_Re: Rexx calling DSNTPSMP on os39012_Bill Kimball19_wpkimball@YAHOO.COM30_Thu, 5 Jul 2001 12:31:50 -0700554_us-ascii By the way.... thanks.... this worked.... I have another strange thing happening, but at least the allocate cursor worked (I used C101).

--- Troy Coleman wrote: > I had a similar problem but mine was for cursor in > the range of C1 to C99 and I was using C101. > I thought this was silly but I followed the message > and named my cursor C99. Does anyone know what the > naming convention is for cursors? I thought I would > name cursor like I do in COBOL...WRONG!! > > > Troy Coleman > Coleman Consulting, Inc. [...] 12948 60 19_Re: DB2 V6 00E5001315_Hagedorn, Linda20_lindah@EPOCRATES.COM30_Thu, 5 Jul 2001 12:34:03 -0700194_iso-8859-1 Have you checked the console log for any OS/390 MVS related messages, and has the sysprog pulled the sys1.logrecs?

Can you post them here?

Linda Hagedorn



13009 51 27_Airline reservation systems9_Ken Paris17_KenParis@LVCM.COM30_Thu, 5 Jul 2001 12:43:40 -0700258_iso-8859-1 This is not a technical question, but I was wondering if anyone knows if any airlines (especially the majors, both domestic or international) are running their reservation systems using DB2 instead of ACP or IMS fastpath or whatever?

Thanks 13061 79 46_Re: DSNTIAUL and CASE - char(1) to varchar(3)?12_Bill Kimball19_wpkimball@YAHOO.COM30_Thu, 5 Jul 2001 12:51:18 -0700632_us-ascii Yes.... I ran into that.... try this:

CASE B.PME_ENTITY_TYPE_CD WHEN '001' THEN SUBSTR('MED',1,3) WHEN '002' THEN SUBSTR('MED',1,3) WHEN '003' THEN SUBSTR('NON',1,3) ELSE SUBSTR('ERR',1,3) END

--- Jessen Michael wrote: > DB2 on OS/390 V6. > I'm using the SQL parm with DSNTIAUL to create an > unload dataset & the > associated load cards. > > When I use a case expression within the sql, it's > unloading a 3 character > varchar field instead of the actual 1 character > format of the field. > My select: > SELECT T1.CR_AGRMT_KEY, > > ... > T1.ORIGL_ACCT_NUM, > CASE > WHEN [...] 13141 64 46_Re: DSNTIAUL and CASE - char(1) to varchar(3)?11_David Nance16_DWNance@FHSC.COM30_Thu, 5 Jul 2001 15:51:28 -0400553_US-ASCII Try a substr.

SUBSTR(CASE WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N' ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,1,1)

Dave Nance First Health Services, Corp. (804)527-6841

>>> JessenMichael@JOHNDEERE.COM 07/05/01 03:27PM >>> DB2 on OS/390 V6. I'm using the SQL parm with DSNTIAUL to create an unload dataset & the associated load cards.

When I use a case expression within the sql, it's unloading a 3 character varchar field instead of the actual 1 character format of the field. My select: SELECT T1.CR_AGRMT_KEY, [...] 13206 131 32_Re: BIND and SQL errors in Rel 69_Colin Fay13_cfay2@CSC.COM30_Thu, 5 Jul 2001 15:42:36 -0400335_us-ascii Hi Duane,

This works as do several other variants NOT using CURRENT SQLID (e.g. AND TABLE_OWNER = 'x' ) in this place. But as I said it's vendor supplied DBRM's (and no source) so I am hoping there is a fix that doesn;t require application changes. Besides which releases are supposed to be downwardly compatible. [...] 13338 37 31_Re: Airline reservation systems11_Randy Scott19_Randy_Scott@VFC.COM30_Thu, 5 Jul 2001 16:05:38 -0400670_us-ascii I don't believe any majors are using anyting except TPF for the reservation systems.











Ken Paris @RYCI.COM> on 07/05/2001 03:43:40 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Airline reservation systems

This is not a technical question, but I was wondering if anyone knows if any airlines (especially the majors, both domestic or international) are running their reservation systems using DB2 instead of ACP or IMS fastpath or whatever? [...] 13376 158 32_Re: BIND and SQL errors in Rel 69_Colin Fay13_cfay2@CSC.COM30_Thu, 5 Jul 2001 15:46:00 -0400451_us-ascii Hi Carol,

Platinum could also be a problem but I also ran it under SPUFI on-line with identical results.

I think , but am hoping not , that it is a problem with the parsing of this statement by the pre-compiler.





Colin









csutfin@AMSOU TH.COM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: BIND and SQL errors in Rel 6 Discussion List [...] 13535 59 31_Re: Airline reservation systems10_Chris Kern23_Chris_Kern@VANGUARD.COM30_Thu, 5 Jul 2001 16:07:28 -0400642_us-ascii I don't know this for a fact, but I worked with folks from USAIR awhile back and I believed they talked about USAIR using DB2.







Ken Paris on 07/05/2001 03:43:40 PM Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Chris Kern/IT/VGI) Subject: Airline reservation systems





This is not a technical question, but I was wondering if anyone knows if any airlines (especially the majors, both domestic or international) are running their reservation systems using DB2 instead of ACP or IMS fastpath or whatever? [...] 13595 62 46_Re: DSNTIAUL and CASE - char(1) to varchar(3)?8_Yvan Roy21_yvan_roy_ca@YAHOO.COM30_Thu, 5 Jul 2001 16:07:38 -0400605_us-ascii The following change will keep the original format: WHEN T1.AGRMT_RVSN_IND = ' ' THEN SUBSTR('N',1,1)

Yvan

--- Jessen Michael wrote: > DB2 on OS/390 V6. > I'm using the SQL parm with DSNTIAUL to create an unload dataset & the > associated load cards. > > When I use a case expression within the sql, it's unloading a 3 character > varchar field instead of the actual 1 character format of the field. > My select: > SELECT T1.CR_AGRMT_KEY, > > ... > T1.ORIGL_ACCT_NUM, > CASE > WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N' > ELSE T1.AGRMT_RVSN_IND END AS [...] 13658 211 36_Re: Triggers - standards enforcement12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Thu, 5 Jul 2001 15:17:15 -0500454_us-ascii Hello,

You may want to state something about "sequence". We've designed our triggers such that the order in which they fire is not important. (I'm speaking of multiple BEFORE or multiple AFTER triggers at the same granularity.)

However, if it were important, dropping and adding an "earlier" trigger would need to be followed by dropping and adding all of the triggers from that one on, in the order you want them to fire in. [...] 13870 165 34_Re: Rexx calling DSNTPSMP on os39015_Robert lawrence21_rlawrence@BOSCOVS.COM30_Thu, 5 Jul 2001 16:20:31 -0400450_iso-8859-1 Troy, There is a naming convention that is required for using DSNREXX that is given in the application guide. DSNREXX is using those names in its calls.

HTH Bob Lawrence DBA Boscov's Dept Stores LLc



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Troy Coleman Sent: Thursday, July 05, 2001 3:21 PM To: DB2-L@RYCI.COM Subject: Re: Rexx calling DSNTPSMP on os390 [...] 14036 34 20_Exoprt Utility (UDB)15_Rajesh Udandrao17_urajes1@YAHOO.COM30_Thu, 5 Jul 2001 13:25:05 -0700466_us-ascii Hi All The Export utility is using the different packages for different clients in UDB. Is there any way we can make only one package for all the Export utilities. We need this because the export utility is bound with

different isolation levels at different clients and they are behaving differently as usual. pls. let me know if there is any solution to situation so that all exports have same isolation levels without binding each seperately. [...] 14071 50 31_Re: Airline reservation systems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Jul 2001 16:27:49 -0400442_iso-8859-1 For reservations? I know several airlines using DB2 for 'business systems' but I've not heard of one using it for reservations. Ditto for other airline res systems such as Worldspan.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Chris Kern [mailto:Chris_Kern@VANGUARD.COM] Sent: Thursday, July 05, 2001 4:07 PM To: DB2-L@RYCI.COM Subject: Re: Airline reservation systems [...] 14122 181 32_Re: BIND and SQL errors in Rel 69_Colin Fay13_cfay2@CSC.COM30_Thu, 5 Jul 2001 16:31:24 -0400416_us-ascii Hi,

It takes a bizarre turn ..... a longer length of the literal seems to make or break it

The column predicate TABLE_OWNER

COLUMN: TABLE_OWNER TABLE: SP_CP_DELETE1 CREATOR : SYBASE COLNO : 1 OF 6 TYPE : VIEW CREATEDBY: B15665

COLTYPE: CHAR NULLS : N LOW2KEY : LENGTH : 8 UPDATES: N HIGH2KEY: SCALE : 0 DEFAULT: N COLCARD : N/A KEYSEQ : 0 FLDPROC: SUBTYPE : SBCS STATSTIME: [...] 14304 33 10_Field proc14_Michael Bedard28_michael_p_bedard@HOTMAIL.COM30_Thu, 5 Jul 2001 16:35:59 -0400549_- Hello all,

I am considering using fieldprocs to invert a number that we are going to be using to partition a table which will be loaded with over a billion rows. It is a number composed of several subfields of which the last will provide an even spread of data over the 100 partitions we plan to implement. Can anyone give me some idea on there experiences with using fieldprocs. Is it going to incur serious overhead? One alternative is to derive a partitioning column from the last 3 digits of the number and add it to the table for [...] 14338 192 19_Re: DB2 V6 00E5001319_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 5 Jul 2001 15:36:11 -0500615_ISO-8859-1 Carol, what's your maintenance level? I got several hits on 00E50013 in the APAR database, most closed and some with a 01/01/31 closing date.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, [...] 14531 27 42_Access DB2 read/write from a VSAM program?12_McKown, John22_JMckown@HEALTHAXIS.COM30_Thu, 5 Jul 2001 15:48:05 -0500555_- CA-IDMS and CA-DATACOM both have an interface option which can intercept VSAM requests and satisfy the I/O from the corresponding data base. Is there anything like that for DB2? I would really like to try to "sell" DB2 to management, but it difficult. Especially if I tell them that we would need to recode our applications. But if there were a way to use the current VSAM oriented (batch & CICS) programs without needing to rewrite and recompile, that would be a BIG help. I've done a web search until my eyes are about to fall out and have found [...] 14559 125 46_Re: Access DB2 read/write from a VSAM program?15_Joaquim Carmona34_joaquim.carmona@CIRCULOLEITORES.PT30_Thu, 5 Jul 2001 22:04:59 +0100651_iso-8859-1 If you have VSE/ESA, take a look on DB2 VSAM Transparency.

-----Original Message----- From: McKown, John [mailto:JMckown@HEALTHAXIS.COM] Sent: Quinta-feira, 5 de Julho de 2001 21:48 To: DB2-L@RYCI.COM Subject: Access DB2 read/write from a VSAM program?



CA-IDMS and CA-DATACOM both have an interface option which can intercept VSAM requests and satisfy the I/O from the corresponding data base. Is there anything like that for DB2? I would really like to try to "sell" DB2 to management, but it difficult. Especially if I tell them that we would need to recode our applications. But if there were a way to use the [...] 14685 67 46_Re: Access DB2 read/write from a VSAM program?12_Mark Granger22_the_grange@MBOX.COM.AU30_Thu, 5 Jul 2001 16:16:50 -0500547_us-ascii John, If you go to http://www.isi.com.au and select 2B DB2, you will see there is information on the 2BDB2 Transparency tool from CA-Datacom to DB2. There is also a VSAM to DB2 Transparency tool along the same lines, but at the moment there is some packaging/marketing work underway. For further information on the VSAM to DB2 Transparency go to http://www.2bdb2.com (or http://www.longgrasssystems.com.au/) and you will find information under the DBGate Products. Contact me offline if you don't find the information you need here. [...] 14753 46 14_Re: Field proc9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 5 Jul 2001 15:24:06 -0600361_us-ascii How do you plan to initially load the billion row table? Having the field proc invoked for each row seems like a waste if it's entire purpose is for a partitioning key. How is your front end app going to create this inverted key for the where clause? Seems like a lot of data manipulation to just partition the data. Maybe I am missing something? [...] 14800 42 34_Re: Rexx calling DSNTPSMP on os3909_Jim Ruddy18_jaruddy@US.IBM.COM30_Thu, 5 Jul 2001 16:12:20 -0500627_- From the V7 Application Programming and SQL Guide

Using cursors and statement names In REXX SQL applications, you must use a predefined set of names for cursors or prepared statements. The following names are valid for cursors and prepared statements in REXX SQL applications:

c1 to c100 Cursor names for DECLARE CURSOR, OPEN, CLOSE, and FETCH statements. By default, c1 to c100 are defined with the WITH RETURN clause, and c51 to c100 are defined with the WITH HOLD clause. You can use the ATTRIBUTES clause of the PREPARE statement to override these attributes or add additional attributes. For example, [...] 14843 82 46_Re: Access DB2 read/write from a VSAM program?22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Thu, 5 Jul 2001 16:34:00 -0500704_- -----Original Message----- From: Mark Granger [mailto:the_grange@MBOX.COM.AU] Sent: Thursday, July 05, 2001 4:17 PM To: DB2-L@RYCI.COM Subject: Re: Access DB2 read/write from a VSAM program?



John, If you go to http://www.isi.com.au and select 2B DB2, you will see there is information on the 2BDB2 Transparency tool from CA-Datacom to DB2. There is also a VSAM to DB2 Transparency tool along the same lines, but at the moment there is some packaging/marketing work underway. For further information on the VSAM to DB2 Transparency go to http://www.2bdb2.com (or http://www.longgrasssystems.com.au/) and you will find information under the DBGate Products. Contact me offline if you [...] 14926 83 25_Re: Internal DATE storage13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Thu, 5 Jul 2001 14:42:11 -0700534_- On os390 ver 6.1

SELECT HEX(CURRENT TIMESTAMP) TS , HEX(CURRENT DATE) DATE , HEX(CURRENT TIME) TIME FROM SYSIBM.SYSDUMMY1



TS DATE TIME -------------------- -------- ------ 20010705143854493854 20010705 143854



-----Original Message----- From: BARCLAY RICHARD [mailto:richard.barclay@FORTISBANK.COM] Sent: Thursday, July 05, 2001 9:43 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Internal DATE storage



Not sure if you've won or lost your soda but Diagnosis Guide and Ref states, [...] 15010 53 38_SOS - Index tablespace in DROP PENDING10_Umesh Apte19_apteumesh@YAHOO.COM30_Thu, 5 Jul 2001 15:45:10 -0700309_us-ascii Hi list,

I am running with essentially two tablespaces in which all application related data is stored. One is Data TS and other one is Index TS. Due to device / disk issues, I can not read my index tablespace at all. I had to Restart the database using DROP PENDING on index tablespace. [...] 15064 83 53_Re: Use of order by in subselect of create view fails13_Terry Purcell23_purcell_terry@YAHOO.COM30_Thu, 5 Jul 2001 16:37:59 -0700506_us-ascii Tim,

Check out the SQL Reference guide.

Although not stated explicitly (but it probably is somewhere), a CREATE VIEW can only contain a FULLSELECT.

The section in the SQL Reference guide titled Queries (Chapter 5 in my softcopies) outlines the differences between a SUBSELECT, FULLSELECT and SELECT statement.

An ORDER BY (and FOR FETCH ONLY etc) can only be specified in a SELECT statement (poor wording if you ask me.....because they are all select statements). [...] 15148 31 29_Re: Data base size vs extents15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Fri, 6 Jul 2001 09:19:02 +1000323_- Used to be 119. 123 is/was the VSAM extent limit but, as you can have up to 5 extents for the primary allocation, the effective number of extents was 119 (1 of the 5 possible primary extents, plus another 118 secondaries).

However, as someone pointed out, it's all academic now as the limit is 255 (I think). [...] 15180 64 35_Need help on SQL Performance - ASAP4_Beth20_bdato@PSGAUS.JNJ.COM30_Thu, 5 Jul 2001 22:09:50 -0500531_- We are an DB2 v6 OS/390 shop. We have an application with a VB frontend that submits these 2 dynamic queries and that takes 20 seconds each for us to get results back. The application team is pushing us to create a monthly summary table similar to the daily summary table to improve performance. Can we just add indices to boost performance? Or the only solution is to create a monthly summary table? Please note that there are currently only about 140,000 records in the table. Any assistance will be very much appreciated [...] 15245 20 42_DB2 for OS/390 v7 - physical modeling tool11_Daniel Adam16_dadam@GRATEX.COM30_Fri, 6 Jul 2001 00:55:08 -0500432_- Hi, What are you using for physical modeling with DB2 for OS/390 v7 ? I know this comes up quite often here, but my questions are regarding db2 v7 on mainframe. Can anyone recommend any tool that can actually help me create not only tables and such, but also specify physical placement for the data (tablespaces etc) ? Is any vendor planning this kind of functionality any time soon ? Thanks in advance, Best regards, Daniel [...] 15266 82 42_Re: SOS - Index tablespace in DROP PENDING16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 12:34:13 +0530554_- Hi,

You have not mentioned the platform and i am assuming it as DB2 UDB for OS390 (my comfort !!). If i have understood your question correctly, your index is in drop pending status and tablespace is in RW status. I believe you shall drop the index and recreate it with DEFER YES/NO. If DEFER YES is used, you can rebuild the index. As far as getting away with Index while selecting the data is concerned. Try and not use indexeable columns in the query. This would be a temporary solution. Who knows when optimizer can choose index again. [...] 15349 40 46_Re: DB2 for OS/390 v7 - physical modeling tool16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 12:35:29 +0530541_- If i am not wrong CONTROL CENTER for UDB for OS390 does that. Have you tried that ? I am not too sure about this but still worth trying if you have not yet.

Regards Sanjeev

> -----Original Message----- > From: Daniel Adam [SMTP:dadam@GRATEX.COM] > Sent: Friday, July 06, 2001 11:25 AM > To: DB2-L@RYCI.COM > Subject: DB2 for OS/390 v7 - physical modeling tool > > Hi, > What are you using for physical modeling with DB2 for OS/390 v7 ? > I know this comes up quite often here, but my questions are regarding > db2 v7 on [...] 15390 46 25_Re: Internal DATE storage16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 12:38:12 +0530577_- Its purely in the packed decimal format. 2 bytes for 4 character year and 1 byte each for 2 char month and days.

Keep the soda with you and pass the hot to the list (If that was with the bet). :-))...

HTH

Regards Sanjeev

> -----Original Message----- > From: Mark Labby [SMTP:mlabby@PHEAA.ORG] > Sent: Thursday, July 05, 2001 8:51 PM > To: DB2-L@RYCI.COM > Subject: Internal DATE storage > > Does anyone definitely know how date and timestamp columns are stored in > DB2? I was always under the impression that it is the number of days past > [...] 15437 43 28_Re: Timestamp in Primary Key16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 12:54:22 +0530511_- Hi,

It depends how you are going to access the data. I am sure no one will be having exact supplied timestamp to put in the WHERE predicate. The DATA, TIME and other scalar functions are there to play with all the components of timestamp but those are not as good as keeping the exact columns like DATA, TIME or DAY, MONTH, YEAR etc from the performance point of view. If i am not wrong, these are moved to STAGE 1 recently but still DB2 has to do some processing to get those which is not cheap. [...] 15481 15 3_RSA15_Priyanka Deswal19_prdeswal@IN.IBM.COM30_Fri, 6 Jul 2001 12:54:14 +0530318_us-ascii Does Db2 support RSA encryption algorithm..

Regards Priyanka

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 15497 28 21_Re: Hai Listers C/37013_Patric Becker25_Patric_Becker@SIS-WEST.DE30_Fri, 6 Jul 2001 02:25:11 -0500306_ISO-8859-1 Hi Vamsidhar !

I can´t help you getting the consistency token out of a C/370 load module, but a -805 doesn´t have to occur because of a wrong load module.

What about the reason code ? If you can´t find the reason for your -805, can you provide the SQLCA for further details ? [...] 15526 31 22_Re: ksh where are you?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 6 Jul 2001 02:36:51 -0500614_- In fact, pdksh is specified as a software pre-requisite for DB2 UDB 7.1. (see Quick Beginnings for Linux)

James Campbell

On Thu, 5 Jul 2001 10:56:26 +0100, Davage, Marcus wrote:

>Hi folks, > >I tried to install DB2 UDB 7.1 for Linux on my home computer (I just can't >get enough! ;-) ), running Linux-Mandrake 8.0, but it won't work because >it's expecting a "ksh" shell, which Mandrake hasn't got. > >Where can I get ksh? > >Mandrake _has_ however, a pdksh shell (possibly a public domain ksh, going >by the name). Is this good enough? > >Kind regards, [...] 15558 20 36_Re: Triggers - standards enforcement15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 6 Jul 2001 03:22:32 -0500308_- Hi

You can run into some trouble, if you code your SQL unqualified and if you want to rebind your trigger package with EXPLAIN(YES). I think the current SQLID which is in effect when you create the trigger drives which table will be accessed. And the current SQLID = OWNER of package = COLLID. [...] 15579 19 18_Problem with RXSQL15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 6 Jul 2001 03:27:11 -0500387_- Hello list

I have a probem with the REXX-Interface RXSQL. If you have a statment like "SELECT Number FROM Table" you can get the results by referencing Number.1. But if I code SELECT DATE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1, how can I get the result? SELECT DATE(CURRENT TIMESTAMP) AS C FROM SYSIBM.SYSDUMMY1 doesn't work. You can't get the result by referencing C.1. [...] 15599 51 21_Re: Hai Listers C/37014_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 6 Jul 2001 03:31:06 -0500645_- Well, firstly and most obviously, the -805 tokens contain the consistancy token in the load module that is actually being executed. So, one of two situations exist: - you are not executing the load module you thought you were - the plan/package have not been bound correctly.

To determine if you are executing the load module you thought you were, use the rexx at http://jupiter.ryci.com/cgi/wa.exe?A2=ind0003&L=DB2-L- DOCUMENTS&P=R2 to convert the -805 contoken to a displayable timestamp. The contoken is based in the internal clock, so apply your time-zone offset to get the local timestamp. Normally this will be a few seconds [...] 15651 83 28_Re: Timestamp in Primary Key19_Carrington, Michael37_Michael.Carrington2@DRESDNER-BANK.COM30_Fri, 6 Jul 2001 11:23:33 +0200414_iso-8859-1 Accessing the data is definitely the thing to watch out for. Had to fix a system once which had severe performance problems with selects like:

SELECT ... FROM ... WHERE DATE(timestampcol) = '01.01.2001'

on a table with 'timestampcol' defined as the PK. They hadn't caused a problem in test because the volumes were small, but in production they were a disaster. Changing them to use: [...] 15735 36 21_Duplicates in IN-list10_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 6 Jul 2001 19:50:59 +1000390_us-ascii Hi,

When we code an IN list with duplicate values, I notice that the o/p is returned for only occurence of a value. This is also consistent with whats described in the manual (the IN list is equivalent to a UNION). But at the same time, being a UNION, is the data retrieved for all occurences of the value and then duplicates eliminated, or is it retrieved just once ? [...] 15772 33 42_Calling subprograms from stored procedures10_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 6 Jul 2001 19:57:16 +1000295_us-ascii Hi,

Given a scenario where my client program calls a stored procedure which in turn has to call other modules (containing or not containing SQL), is it better to call the other modules as a system call, or to create the modules as Stored procedures and call them thru SQL ? [...] 15806 18 23_how to compare 2 tables7_Eric Ng22_ngyh@PUBLICBANK.COM.MY30_Fri, 6 Jul 2001 04:43:52 -0500507_- HI, if I have 2 tables which are supposed to contain the same data, what is the best way to confirm that ?

When I use SUPERC byte option to compare the 2 DSNTIAUL unload output, it shows difference.

DB2 experts out there, pls advise.

Regards,

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 15825 52 27_Re: how to compare 2 tables16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 15:47:52 +0530529_- Hi,

SELECT 1 FROM ((SELECT COUNT(*) as COUNT 1 FROM T1) as V1, (SELECT COUNT(*) as COUNT 2 FROM T1, T2 WHERE T1.KEY(s) = T2.KEY(s) and T1.other columns = T2.other columns) as V2) WHERE V1.COUNT 1 = V2.COUNT 2

If the SQLCODE is 100, it means count is different. If 1 is returned and SQLCODE is 0, the count is same. If you mean to look at the same data by key values then include only key values in the join otherwise include all the columns. Performance might be an issue in the later case. I am not sure if [...] 15878 57 25_Re: Duplicates in IN-list16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 6 Jul 2001 15:39:05 +0530291_- Hi Kals,

BTW, what's the explain is saying ? I believe it is identified if the table is same and the where predicate is having exactly same value then it need not be fetched twice as far as UNION is concerened.

This is the reason it is said "equivalent" not the "same". [...] 15936 17 27_Re: how to compare 2 tables15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 6 Jul 2001 05:09:50 -0500437_- What do you think about:

SELECT * FROM table1 UNION SELECT * FROM table2

count the number of rows and compare this number to the number of rows from the same query using UNION ALL

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 15954 55 14_AW: WEPR state11_Martin Balz23_Martin.Balz@HVBINFO.COM30_Fri, 6 Jul 2001 12:13:14 +0200455_iso-8859-1 Hi,

we encountered a WEPR on a DSNDB07 TableSpace, a few minutes after Msg DSNB224I (I/O-Error during Write) for this Space. The WEPR-Status was resolved by STOP / START Command, but I think this may only work for the Sort/Work-Tablespaces. (???)

By the way: DSNB224I showed CSW-Status=X'0C40', meaning that DB2 wrote the wrong Record Length to the Cluster (At least that's what our HW-People say...) I am still wondering... [...] 16010 33 29_Re: Data base size vs extents14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Fri, 6 Jul 2001 05:39:14 -0500511_iso-8859-1 One other thing I have not yet seen about this...

If you have a concern about how large an object can grow to, then the other things may come into play.

Many sites have SMS routines or other software that will increase the secondary space allocation if it is requested frequently. The formula used will be site-dependant. Sometimes the space request is set to a percentage of the total object size, sometimes it increases by a fixed amount every time secondary space is requested. [...] 16044 116 35_Re: how to compare 2 tables - PART112_Troy Coleman19_Colematr@MEIJER.COM30_Fri, 6 Jul 2001 07:30:08 -0400407_US-ASCII Well I'm having problems sending this in one message so I will split it into two parts.

I would do the following: Assume that table1 and table2 have the same definition with three columns. COL1 is the primary key. You will not know which column is different but if you put the two columns you are comparing next to each other it will be easier to scan the report and see the different. [...] 16161 50 27_Re: how to compare 2 tables15_BARCLAY RICHARD30_richard.barclay@FORTISBANK.COM30_Fri, 6 Jul 2001 13:42:32 +0200563_- Did you use an 'order by' on the unload?

If not (and you're using superc) have you sorted your unload datasets?

Richard

> -----Original Message----- > From: Eric Ng [SMTP:ngyh@PUBLICBANK.COM.MY] > Sent: vrijdag 6 juli 2001 11:44 > To: DB2-L@RYCI.COM > Subject: how to compare 2 tables > > HI, if I have 2 tables which are supposed to contain the same data, > what is the best way to confirm that ? > > When I use SUPERC byte option to compare the 2 DSNTIAUL unload output, > it shows difference. > > DB2 experts out there, pls advise. [...] 16212 25 43_Off-topic post - replacement for Quickstart15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Fri, 6 Jul 2001 07:09:20 -0500622_iso-8859-1 Does anyone know of any products that are similar to Quickstart? We want to replace it. Please contact me off-line.

Thanks,

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services



"The distance between insanity and genius is measured only by success." James Bond - Tomorrow Never Dies

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 16238 127 36_Re: how to compare 2 tables - PART 212_Troy Coleman19_Colematr@MEIJER.COM30_Fri, 6 Jul 2001 08:28:41 -0400605_US-ASCII I've had problems sending this when I had the SQL formatted. I've taken on the blanks out. I hope this works!! In this example I'm using a CASE statement. You could change this to bring back a result for each column or leave it as is and bring back the information on the first column it found having a difference. Have fun: SELECT A.NAME AS COL_NAME, A.TBNAME, A.TBCREATOR as SRC_CREATOR, B.TBCREATOR as TGT_CREATOR , CASE WHEN A.COLNO <> B.COLNO THEN 'COLNO ' CONCAT CHAR(A.COLNO) CONCAT ' ' CONCAT CHAR(B.COLNO) WHEN A.COLTYPE <> B.COLTYPE THEN 'COLTYPE ' CONCAT CHAR(A.COLTYPE) CONCAT ' [...] 16366 164 46_Re: Calling subprograms from stored procedures14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Fri, 6 Jul 2001 05:39:15 -0700585_iso-8859-1 Kals, I would probably go with system calls, rather than SP calls for those programs that aren't directly accessed by clients. The main reason is performance. When calling an SP, you will need to (assuming DB2 on OS/390) go through DB2 to set up the environment, which, may involve WLM and the creation of a separate address space. By using system calls, you will run in the current environment. Also, using system calls will be more natural to your developers, since this is the method of coding they will be used to. Just my 2 cents. Wayne Driscoll Product Developer [...] 16531 63 14_Re: Field proc19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Fri, 6 Jul 2001 07:40:25 -0500658_ISO-8859-1 Hi Michael, Another consideration: What is the major or most important access to the table? Skip-sequential, sequential, true random?

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or [...] 16595 42 27_Re: how to compare 2 tables10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 6 Jul 2001 15:03:21 +0200349_us-ascii Just an idea if you want a qualitative confirmation (and you do not want to know what rows are duplicated), if your tables

are equal AND you have some 'uniqueness' (and if the tables are small it's better - you may create a suitable index in

the clone-table).

1) Create an empty clone-table with CREATE TABLE LIKE [...] 16638 144 36_Re: Triggers - standards enforcement23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 6 Jul 2001 10:01:44 -0400428_iso-8859-1 Steve,

Thank you for your response. I agree with the sequencing bit. We use '#' as the SQL delimiter/terminator. But we do qualify every table accessed, in the trigger. Thanks once again

Sanjay



-----Original Message----- From: Steve Grimes [mailto:Steve_Grimes@AISMAIL.WUSTL.EDU] Sent: Thursday, July 05, 2001 4:17 PM To: DB2-L@RYCI.COM Subject: Re: Triggers - standards enforcement [...] 16783 14 19_Re: DB2 V6 00E5001318_Sandra Lakenburger34_sandra.lakenburger@MAIL.SPRINT.COM30_Fri, 6 Jul 2001 09:02:38 -0500373_- We're DB2 V6 and OS/390 V2.10. We started seeing a similar type problem with several of our utility jobs along with an S906, RC08 abend in DSNPSCTC in our logrec data. Utilities that abended once, would run successfully when resubmitted. We're in the process of rolling out PQ48650/UQ54709 and it seems to have addressed the issue so far. It became available 06/15. [...] 16798 198 14_Re: Field proc14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 6 Jul 2001 15:23:41 +0100628_iso-8859-1 Michael,

I did exactly the same thing some while back (the development team INSISTED on an ascending primary key for their partitioned table and inverting it gave a very nice spread over the partitions).

However, there were a couple of gotchas

- The first was that I forgot that Fieldprocs are re-entrant so I wasn't being careful enough with storage. When my fieldproc went live, associated CICS regions became VERY unstable!!! - The second is the fact that the column is encoded by the Fieldproc in a very interesting part of the path between DASD and your application. Essentially, INDEX [...] 16997 52 36_Re: Triggers - standards enforcement23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 6 Jul 2001 10:27:17 -0400315_iso-8859-1 Walter,

Thank you for your input. Yes, the "REBIND TRIGGER PACKAGE" statement will use the SQLID in effect at the time the trigger was created, as the qualifier and owner. I will try to check if any developer has used WHEN clause in existing triggers and what kind of access path is selected [...] 17050 23 23_390 SQL runtime control14_Cheung, Teresa19_cheungt@TOYSRUS.COM30_Fri, 6 Jul 2001 10:32:20 -0400496_iso-8859-1 Hi All,

I am writing a 390 Server(COBOL programs) that perform real-time process. It takes requests from NT Client via MQSeries, runs transactions against DB2 on 390 and returns results to NT Client in few seconds.

I am wondering if there is a way to control the run time of a query. I mean, at any time, for any 390 SQL statement ( INSERT/UPDATE) takes longer than the time that I expected, can I stop the SQL process and return control back to the Server program? [...] 17074 35 7_LevelId15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA30_Fri, 6 Jul 2001 09:30:51 -0500647_- I hope someone can point me in the right direction, as we have a strange problem.

One of my colleagues had reported that certain jobs were failing for resource unavailable. On further investigation, he got the message code 00c2010d - and eventually used REPAIR LEVELID tablespace to solve the problem. Strangely enough, the problem continued re-occurring, even on tablespace partitions that he had already repaired. As we understand it, the applications are doing SQL access (select only we're told), and DSNTIAUL to do selective unloads. These applications have been running stably for several months, and last ran the night before [...] 17110 53 19_Re: DB2 V6 00E500130_19_csutfin@AMSOUTH.COM30_Fri, 6 Jul 2001 09:50:45 -0500498_us-ascii Sandra and others

This is the same PTF that IBM has suggested that we apply. We'll both find out if this helps.

Thanks Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214







Sandra Lakenburger cc: Sent by: DB2 Data Base Subject: Re: DB2 V6 00E50013 Discussion List



07/06/01 09:02 AM Please respond to DB2 Data Base Discussion List [...] 17164 28 28_Very Low Pagesave (Negative)11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Fri, 6 Jul 2001 10:17:40 -0500474_iso-8859-1 Hi,

Can someone help me in understanding how come Pagesave be so low as -1600. As per the manuals

The value can be negative if using data compression causes an increase in the number of pages in the data set.

Any reasons for such high numbers ?

For one of the tablespace for which the Pagesave was -15 I did reorg and runstats the Pagesave is 74 now. I can understand Compression Dictionary and Data out of Whacked but how much ! [...] 17193 13 11_Re: LevelId0_26_truman.g.brown@VERIZON.COM30_Fri, 6 Jul 2001 11:18:04 -0400370_us-ascii DB2 OS/390? What vers/lvl of DB2? Any reorgs run in the interim; IBM or BMC? Anyone moving datasets around using DFDSS?

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 17207 66 11_Re: LevelId19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Fri, 6 Jul 2001 10:30:09 -0500620_ISO-8859-1 Regan, A quick guess -- DSN1COPY without RESET?

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 17274 23 50_DB2 for OS/390 DBA Certification - two complaints.13_Brendan Friel28_Brendan_Friel@NOTES.NTRS.COM30_Fri, 6 Jul 2001 10:30:01 -0500356_us-ascii I'm interested in investing some time in passing these exams.

Complaint #1 - the first test requires me to study non-OS/390 flavors of DB2.

Complaint #2 - the IBM Web Site shows no existing study guides for any of the tests. If I'm studying for Java or VB certication tests, Amazon shows dozens of certification oriented books. [...] 17298 51 27_Re: 390 SQL runtime control19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Fri, 6 Jul 2001 10:33:11 -0500625_ISO-8859-1 Teresa, check out the OS390 Resource Limit Facility.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 17350 17 34_Re: Declarative RI and prefetching12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Jul 2001 10:21:19 -0500323_- What kind of processing does DB2 use for RI checking. I assume it is some under the covers SQL?

There is no SQL for RI checking in DB2 for z/OS & OS/390. It was implemented at a lower level for performance. So you won't see any access path or explain information. The primary improvement was index lookaside. [...] 17368 33 11_Re: LevelId15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA30_Fri, 6 Jul 2001 10:42:17 -0500444_- On Fri, 6 Jul 2001 11:18:04 -0400, truman.g.brown@VERIZON.COM wrote:

>DB2 OS/390? What vers/lvl of DB2? Any reorgs run in the interim; IBM or >BMC? Anyone moving datasets around using DFDSS? > >================================================ >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 the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 17402 19 11_Re: LevelId15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA30_Fri, 6 Jul 2001 17:48:16 +0200363_us-ascii Nope - we haven't used any DSN1COPY jobs on this. I did see previous list's mentioning that possibility while searching on LevelId.

Thanx though

Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be those of Sanlam, to whom no liability shall attach whatsoever. [...] 17422 47 27_Re: 390 SQL runtime control13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Fri, 6 Jul 2001 09:53:27 -0600377_iso-8859-1 Resource limit facility works on dynamic sql. Its predictive governing can

"Set warning and error thresholds by which the govenor can inform users (via your application programs) that a certain processing limit might be exceeded....."

Haven't explored this yet myself. It can be set at the package level. Check out the Admin Guide page 700 or so. [...] 17470 14 32_Re: Very Low Pagesave (Negative)15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM30_Fri, 6 Jul 2001 10:56:03 -0500446_us-ascii This has happened to me when the table contains very few rows. Which makes you wonder if compression is really buying you anything.. If the table will grow in the future than maybe, if not then ?

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 17485 51 27_Re: 390 SQL runtime control13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Fri, 6 Jul 2001 11:09:17 -0500524_iso-8859-1 Hi, I do not think there is such a mechanism, if there is we would like to use it as well.

It sounds like you are doing something similar top what we are doing. We have Java apps executing MQ which executes a DB2 program. Java will wait 15 secs for a reply from MQ. If this 15 secs is exceeded, the user is given a timeout error by the Java application. When/if the DB2 program does respond (say 20 secs later), the requestor has of course gone. The reply goes to a dead letter queue (DLQ) and we use [...] 17537 45 54_Re: DB2 for OS/390 DBA Certification - two complaints.14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 6 Jul 2001 11:15:48 -0500412_us-ascii DB2 V7.1 Certification Guide for OS/390, z/OS is currently being printed by Prentice Hall and should be available within 1 to 2 weeks, everywhere.

Richard

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Brendan Friel Sent: Friday, July 06, 2001 10:30 AM To: DB2-L@RYCI.COM Subject: DB2 for OS/390 DBA Certification - two complaints. [...] 17583 15 11_Re: LevelId12_Gary R. Sako18_SAKO.GARY@NTRS.COM30_Fri, 6 Jul 2001 11:31:01 -0500375_US-ASCII Are you in a data sharing environment? There are problems where the coupling facility doesn't report back the correct level.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 17599 165 11_Re: LevelId14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Fri, 6 Jul 2001 12:45:04 -0400562_iso-8859-1 Regan,

Just a shot in the dark -- are you in a data sharing environment, and if yes, was the encountered levelid higher than the expected levelid? If so, then there may be rows for your tablespace missing from SYSLGRNX. If your maintenance level is prior to PUT0010, this could happen if a MODIFY running on one DB2 deleted a row from SYSLGRNX when it was still open for another DB2 in the data sharing group. If this is your problem, it would also explain why you still have it after running REPAIR LEVELID, since I don't believe DB2 can [...] 17765 58 54_Re: DB2 for OS/390 DBA Certification - two complaints.12_Susan Lawson24_susan_lawson@YLASSOC.COM30_Fri, 6 Jul 2001 11:57:47 -0500317_US-ASCII Brendan,

There is a DB2 V7.1 Certification Guide for OS/390 that will be available in the next week or so. There are also crammer courses offered at IDUG NA(previous) and the IBM Technical Conference in the fall to help you prepare. This course offers all the key information to pass both exams. [...] 17824 69 27_Re: 390 SQL runtime control14_Cheung, Teresa19_cheungt@TOYSRUS.COM30_Fri, 6 Jul 2001 12:52:41 -0400450_iso-8859-1 Thanks for your reply. I am looking into different ways to improve the Server response time and performance. My worry is what if my 390 server program is held up by DB2 process, what other techniques should I be using in order not to affect other clients?

-----Original Message----- From: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Sent: Friday, July 06, 2001 12:09 PM To: DB2-L@RYCI.COM Subject: Re: 390 SQL runtime control [...] 17894 31 39_Passing parameters to stored procedures14_Geoff Beaumont21_Geoff@STORMHAMMER.COM30_Fri, 6 Jul 2001 19:03:55 +0100587_US-ASCII Hi,

I've run into a problem with stored procedures in DB2 7.2 (on SuSE Linux 7.1).

I can create SQL stored procedures and insert them into the database, either manually or using DB2 Stored Procedure Builder, and within these procedures I can declare and use variables. However, if I create a procedure with input parameters then while I can insert it into the database, I can only run it from within the Stored Procedure Builder. Attempting to run it from Rapid SQL or an ASP script generates a library not found error (sorry I can't tell you the exact error [...] 17926 82 54_Re: DB2 for OS/390 DBA Certification - two complaints.10_Raghu Kota21_raghukota@HOTMAIL.COM30_Fri, 6 Jul 2001 18:11:03 -0000667_- Hi

You guys(Susan,Richard) are great asset this mailing list. Keep up good work.

Thanks Raghu.



>From: Susan Lawson >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 for OS/390 DBA Certification - two complaints. >Date: Fri, 6 Jul 2001 11:57:47 -0500 > >Brendan, > >There is a DB2 V7.1 Certification Guide for OS/390 that will be available >in >the next week or so. There are also crammer courses offered at IDUG >NA(previous) and the IBM Technical Conference in the fall to help you >prepare. This course offers all the key information to pass both exams. > [...] 18009 21 11_QMF Reports11_David Nance16_DWNance@FHSC.COM30_Fri, 6 Jul 2001 15:12:11 -0400341_US-ASCII Quick question(I hope). Does anyone know of a way to export a report to a PDS without all the garbage QMF adds to the report? The report I am generating is to be a sysin card for a subsequent batch job. I can using a qmf proc strip off all the excess trash, but I was hoping for an easy way to just export what I want. Thanks. [...] 18031 145 15_Re: QMF Reports0_24_Bruce.Williamson@PSIR.IE30_Fri, 6 Jul 2001 20:35:45 +0100621_windows-1252 Howzit Dave?

I've never managed to get rid of the carriage control characters in column 1.

Let me know when you find a way.

Cheers Bruce



-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: 06 July 2001 20:12 To: DB2-L@RYCI.COM Subject: QMF Reports



Quick question(I hope). Does anyone know of a way to export a report to a PDS without all the garbage QMF adds to the report? The report I am generating is to be a sysin card for a subsequent batch job. I can using a qmf proc strip off all the excess trash, but I was hoping for [...] 18177 61 15_Re: QMF Reports0_19_Tim.Lowe@STPAUL.COM30_Fri, 6 Jul 2001 14:55:35 -0500420_us-ascii David, I am not sure what you mean by "all of the garbage" or "excess trash". If you are referring to all of the column headings and spaces, could you just run DSNTIAUL with the SQL option instead of QMF?

I hope this helps.

Thanks, Tim





David Nance cc: Sent by: DB2 Subject: QMF Reports Data Base Discussion List [...] 18239 97 15_Re: QMF Reports15_Jackson Reavill18_damcon2@US.IBM.COM30_Fri, 6 Jul 2001 16:27:53 -0400449_us-ascii Dave,

Not sure this is the slickest way to do it, but you can use the print report command in conjunction with the form to remove all but the carriage control. Then execute a clist to remove the 1 from the first column using ispfexec commands. And actually you can just have your record layout for the sysin start at position 2. Then you don't even need to mess with the clist. Or maybe even better yet is to just do an unload. [...] 18337 42 15_Re: QMF Reports13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Fri, 6 Jul 2001 13:50:00 -0600405_- David: DSNTIAUL will run any sql that qmf will. You have to put PARM('sql') on the RUN card and a ';' at the end. The error message for the missing ';' is missleading and scares people off. This format could be a little too sparce compared to what you want. I would concat in spaces where needed and substr away varchars not needed. It may be closer to what you want than starting from qmf output. [...] 18380 25 28_Row-lock /Page-lock Decision14_Cheung, Teresa19_cheungt@TOYSRUS.COM30_Fri, 6 Jul 2001 17:11:52 -0400533_iso-8859-1 Hi All, DB2 on the 390 platform, it is an option between row-locking and page-locking. From a Server point of view, to improve its concurrency , I would prefer row locking when there is a high chance that a page of data is simultaneously used by different users but each user interest is on a different row. Spoke to the DBA in my shop, he's kind of hesitating to use row-locking because it will cost a lot of overhead. Can anyone provide some positive impacts of having row-lock instead of page-lock? Thanks, Teresa [...] 18406 65 15_Re: QMF Reports11_David Nance16_DWNance@FHSC.COM30_Fri, 6 Jul 2001 17:29:34 -0400356_US-ASCII Thanks for the suggestions. DSNTIAUL doesn't work due to all the variables I am passing to the Proc in qmf, the form gets everything the way I need it for my sysin card. I have found that if you export using dataformat=html you do not get the leading carriage control info, but you still get 8 header and 3 footer lines, better than nothing. [...] 18472 73 15_Re: QMF Reports13_Villa Horacio17_hvilla@TTI.COM.AR30_Fri, 6 Jul 2001 18:44:54 -0300596_iso-8859-1 Have you considered REXX? Horacio Villa

-----Mensaje original----- De: DB2 Data Base Discussion List En nombre de David Nance Enviado el: Viernes 6 de Julio de 2001 18:30 Para: DB2-L@RYCI.COM Asunto: Re: QMF Reports



Thanks for the suggestions. DSNTIAUL doesn't work due to all the variables I am passing to the Proc in qmf, the form gets everything the way I need it for my sysin card. I have found that if you export using dataformat=html you do not get the leading carriage control info, but you still get 8 header and 3 footer lines, better than nothing. [...] 18546 18 46_Re: DB2 for OS/390 v7 - physical modeling tool0_17_JTonchick@AOL.COM28_Fri, 6 Jul 2001 23:44:09 EDT508_US-ASCII The Estimator for DB2 is also an excellent modeling tool for OS/390 versions of DB2. It even can estimate CPU based on your model of s/390 box and various DASD devices. You can download it for free from the IBM DB2 web site.

Jim Tonchick Fiserv, Inc.

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 18565 21 15_Re: QMF Reports0_17_JTonchick@AOL.COM28_Fri, 6 Jul 2001 23:56:18 EDT335_US-ASCII You might want to search the DB2-L archives or the IDUG website for old presentations. I seem to remember a presentation on using the QMF report FORMs to format the output sans regular printer control characters.

Sorry I couldn't be more helpful. I'll check my "archive" on my desk when I get back to work Monday. [...] 18587 12 15_Re: QMF Reports15_herman Yarbough24_Herman_Yarbough@KCMO.ORG30_Fri, 6 Jul 2001 23:05:02 -0500297_us-ascii I am out of the office today I will be back on

================================================ 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 the list can be reached at DB2-L-REQUEST@RYCI.COM. 18600 53 15_Re: QMF Reports10_Price, Ray18_Ray.Price@DRKW.COM30_Sat, 7 Jul 2001 17:50:25 +0100367_iso-8859-1 Hi Dave,

There a several things QMF can't do. I have a REXX that we use to invoke QMF in batch. The REXX is able to manipulate the QMF output into the format needed, that includes removing the QMF "extras" and things like converting to CSV format.



Regards,

Ray Price Database Manager Dresdner Kleinwort Wasserstein London [...] 18654 45 30_Restart(current) in a load job13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM30_Sat, 7 Jul 2001 16:50:19 -0400500_us-ascii I have a lod job , which was cancelled before completion . Once during the reload and once during the build phase. The job was resubmitted , with various options for the restart parameter .

For the following , the job completed successfully :

//LOAD102 EXEC DSNUPROC,PARM='DB0D,RCDLOAD,RESTART' and //LOAD102 EXEC DSNUPROC,PARM='DB0D,RCDLOAD,RESTART(PHASE)'



For the following , the job abended : //LOAD102 EXEC DSNUPROC,PARM='DB0D,RCDLOAD,RESTART(CURRENT)' [...]