1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2000, week 3 2 30 27_DB2 STOREPROCEDURE QUESTION12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Wed, 15 Nov 2000 06:24:19 GMT445_- Hi guys:



I've got a user that require to create a db2 store procedure in order to access some db2 tables and also some ksds vsam datasets (outside db2). Really I don't know how do it and if it possible. He should specified a sort of ddname or something similar inside his store procedure to be able to reference those vsam files. Please I would appreciate if anyone could help me to clarify how do it , thanks and regards, [...] 33 22 20_Upgrade DB2 V4 to V611_Michael Lim22_MichaelLim@HKEX.COM.HK31_Wed, 15 Nov 2000 14:16:39 +0800289_- Dear lister's,

I am just starting to plan a DB2 V4 to V6 upgrade. The first way to do this is to migrate to V5 and then to V6 immediately afterwards. The second way is to install a new V6 subsystem and then copy everything from the V4 subsystem over to the new V6 subsystem. [...] 56 64 22_Re: Batch QMF Question0_24_Ray.Price@DRESDNERKB.COM31_Wed, 15 Nov 2000 07:23:26 -0000310_iso-8859-1 Hi Tom,

We run a lot of batch QMF. We solved the ISPTLIB problem by putting an IEBCOPY on the front of any QMF job to copy the ISPTLIB dataset to a temporary dataset. So each job has it's own copy of ISPTLIB.

Hope this helps.

Ray Price DBA Dresdner Kleinwort Benson London [...] 121 13 26_Re: uppercase vs lowercase15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 15 Nov 2000 01:35:28 -0600408_- Hello Kate

I think STRING is better than MIXED, because on the literals in Queries remain lowercase, but QMF-commands like SAVE will be changed to uppercase.

================================================ 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. 135 174 31_Creating Compression Dictionary14_Bjarne V. Ravn15_bvr@BANKDATA.DK31_Wed, 15 Nov 2000 08:43:31 +0100361_iso-8859-1 We have a Large Partitioned Tablespace with 254 partitions, clustered by Insert Timestamp. The table is estimated to 750 million rows.

The boundaries are on a monthly basis, and partitions for future use are allocated with Primary quantity = 48 and Secondary quantity = a large number. All of the partitions are defined with Compression. [...] 310 20 58_Re: Using DFDSS to copy databases to different DB2 systems10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 15 Nov 2000 09:08:29 +0100530_us-ascii Few REXX execs and few skeletons (to generate JCLs and to perform xxID traslation) are less expensive than any other toolz (they do the same things).

If you have Control-M you can generate a job flow to run generated jobs in the test environment.

Regards 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. 331 117 22_Re: SMS Storage Groups0_27_Tom.Decabooter@HONDA-EU.COM31_Wed, 15 Nov 2000 09:11:27 +0100483_us-ascii DPetro@AOL.COM on 15/11/2000 03:33:11

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Tom Decabooter/HE/EU/HONDA)

Subject: Re: SMS Storage Groups Dave,

1. Yes, there are a lot of sample batch jobs. You can look at them using the ISMF panels. Go into ISMF and select '11. Enhanced ACS Management' select '7. Batch Testing/Configuration Management' select '3. Configuration Changes Batch Samples' [...] 449 20 37_Re: Please post to DB2 *DBA* Listserv16_Richard A Yevich21_ryevich@BELLSOUTH.NET31_Wed, 15 Nov 2000 02:13:19 -0600456_iso-8859-1 LazyDBA is a job search site with supports a list for Oracle/UDB/SQL Server DBA's. If SD SDAD wants to spend his time there, fine. As for the rest of us...

Myron - kudos!!!!!

Richard Yevich

================================================ 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. 470 99 35_Re: Creating Compression Dictionary15_Douwe van Sluis20_d.b.vansluis@KPN.COM31_Wed, 15 Nov 2000 09:46:17 +0100611_iso-8859-1 Hello Bjarne,

This is how we have handled the creation of compression dictionaries.

We have created a simular table in a test environment. We loaded in some sample data, then reorged to create the compression dictionary, then a load replace keep dictionary with a dummy file. We now have created an empty partition with a compression dictionary. Now with DSN1COPY one can copy this 'file' to the production site using OBIDXLAT. Make sure that you have the same number of partitions in both tables and that you copy the same partition number from source to target. In your case you [...] 570 91 27_Re: uppercase and lowercase14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT31_Wed, 15 Nov 2000 09:35:48 +0100346_ISO-8859-1 Hi Kate.

Another suggestion is to write your query in QMF then to edit yuor query writing edit query on command line. When you are in ispf edit you can change to caps off and write the lower or upper cases you need. At this point you can return to QMF just typing PF3, when you are in QMF run your query directly with PF2. [...] 662 33 21_Help me with password14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT31_Wed, 15 Nov 2000 09:43:12 +0100314_ISO-8859-1 Hi everybody.

I'm new for the list, I tried to give an answer by the web site to a question but I couldn't do it because I don't have the password for the login. What have I to do?

BYE

PS: this is not a query about the DB2, just about my problems with the use of the web site. [...] 696 35 35_Re: Creating Compression Dictionary10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 15 Nov 2000 10:13:46 +0100572_us-ascii I had the same problem some years ago with a table of 330 millions rows and a secondary index.

Every tablespace or partition has its own dictionary in the page range 2-17 so you cannot 'share' a dictionary.

We used a method to build a dictionary based of a 'simulation' that is we built a input file based on the data of the 'old' partitions (for instance we had a column 'customer' so the customers with the highest number of rows were take into account to build dictionary strings) and as you know the timestamp of partitions you can collect [...] 732 93 52_Re: Trying to define TS with Extended Addressability15_Douwe van Sluis20_d.b.vansluis@KPN.COM31_Wed, 15 Nov 2000 10:31:10 +0100334_- Hello Linda,

The primary quantity needs to be allocated within 5 extents on one volume. Maybe your volumesize is less than 4G (3390-3 about 2.8G). Try lowering the primary quantity and try to extent beyond the 4G limit to test whether the EA attribute works.

Kind regards, Douwe van Sluis Sr. Technisch Architect [...] 826 102 35_Re: Creating Compression Dictionary16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Wed, 15 Nov 2000 15:17:24 +0530594_- Hi Bjarne Ravn, First of all I am writing my understanding of your question and requirements.You have a partitioned tablespace partitioned on timestamp, basically months range and for that matter you have got some partitions which ofcourse will be having records in the future as time comes in.The tablespace is defined with COMPRESS YES but data is not getting compressed once inserted , data gets compressed only if the reorg is done and you want to avoid the reorg because of its overhead. As we know for tablespace defined with COMPRESS YES, data gets compressed first only if it is [...] 929 123 37_Re: Please post to DB2 *DBA* Listserv14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 15 Nov 2000 11:12:14 -0000566_iso-8859-1 Alternatively, you may try

http://www.news2web.com/cgi-bin/dnewsweb.exe?cmd=xover&group=comp.databases. ibm-db2&utag=

and see what happens...

Prego.

Ciao, Marcus -----Original Message----- From: Aurora Dell'Anno [mailto:aurora.dellanno@SISTINF.IT] Sent: 15 November 2000 00:08 To: DB2-L@RYCI.COM Subject: Re: Please post to DB2 *DBA* Listserv



Dave,

what in the name of love is "comp.databases.ibm-db2"?

please send me (or the list if you think it might be of interest) the complete address!!! [...] 1053 87 34_Re: POTENTIALLY INCONSISTENT DATA.23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 15 Nov 2000 12:15:51 -0000550_iso-8859-1 are you datasharing?

if yes - get intouch with IBM support .. there are loads of fixes for these ..

are you V6 ... ????

We have had loads of these recenlty and have loads of PMRs open.

mail me direct for more info.

regards

Leslie Pendlebury-Bowe DB2 DBA OS390 SAP DB2 Certified







-----Original Message----- From: Alvarez, Maximiliano [mailto:MAXIMILIANO@IADB.ORG] Sent: Monday, November 06, 2000 7:26 PM To: DB2-L@RYCI.COM Subject: POTENTIALLY INCONSISTENT DATA. [...] 1141 33 84_Why is Fisher-Price knowledge required for OS/390 Certification b ut not vice versa?20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Wed, 15 Nov 2000 12:23:36 -0000411_iso-8859-1 My boss has asked me to look into getting some of us OS/390 DBAs certified. The problem is we know plenty about DB2 on OS/390 but next to nothing about this new-fangled UDB stuff.

To get certified you have to pass two tests: 516 'OS/390 Data Administration' plus 512 'UDB Family Fundamentals'. If you're not familiar already, take a moment to look at the requirements for test 512 at ... [...] 1175 61 27_Re: tablespace status WEPR?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 15 Nov 2000 12:20:22 -0000491_iso-8859-1 yes - a real bugger to understand why though ..

we had it appear due to a reorg aswell.

I have a feeling(but not the time to look into it) it got fixed with some maint we put on recently.

Leslie Pendlebury-Bowe DB2 DBA OS390 SAP DB2 Certified







-----Original Message----- From: McDowell, Andy C, [mailto:Andy.Mcdowell@NSCORP.COM] Sent: Wednesday, November 08, 2000 2:51 PM To: DB2-L@RYCI.COM Subject: tablespace status WEPR? [...] 1237 65 43_Re: Version 6 - Recover Utility Parallelism23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 15 Nov 2000 12:25:41 -0000538_iso-8859-1 John

maybe they are eluding to DB2 doing certain things in parallel internally .....

this has been mentioned to me recently ... V6 is able to do certain thing in parallel on on NPIs ...

sorry I cannot be more specific

Leslie Pendlebury-Bowe DB2 DBA OS390 SAP DB2 Certified







-----Original Message----- From: John Hardy [mailto:john_j_hardy@CONSULTANT.COM] Sent: Monday, November 13, 2000 11:31 AM To: DB2-L@RYCI.COM Subject: Version 6 - Recover Utility Parallelism [...] 1303 106 18_Re: unit test data0_19_mike.holmans@BT.COM31_Wed, 15 Nov 2000 12:32:19 -0000397_iso-8859-1 For my sins, I evaluated both Princeton Softech's and Compuware's products a couple of years ago. Such comparative results as I may have obtained at the time are of no interest here, as the products have no doubt moved on in the interim and anything I said now would be unfair to someone. (We ended up buying neither, for what were essentially internal company political reasons.) [...] 1410 106 24_Re: Upgrade DB2 V4 to V615_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Wed, 15 Nov 2000 07:39:49 -0500614_iso-8859-1 Mike,

I just upgraded from V4.1 to V6.1 for our Development/Training/User Acceptance Test environment this past weekend and will be upgrading our production this upcoming Saturday.

Be sure to have at minimal the DB2 V6 OS/390 RML 0003 Level - I'm using 0005 of V6.1 with some additional PTFs.

I used the method of unloading/building new tables/loading/runstat/bind/authorizations for the application and all of it took about 13 hours to perform with the inclusion of some other tasks mentioned below. The application itself consisted of 3 database environments, small in size. [...] 1517 98 37_Re: Please post to DB2 *DBA* Listserv16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 08:09:07 -0500546_iso-8859-1 Aurora, That is a USENET news group. It discusses mostly small-machine DB2 issues.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Aurora Dell'Anno [mailto:aurora.dellanno@SISTINF.IT] Sent: Tuesday, November 14, 2000 7:08 PM To: DB2-L@RYCI.COM Subject: Re: Please post to DB2 *DBA* Listserv



Dave,

what in the name of love is "comp.databases.ibm-db2"?

please send me (or the list if you think it might be of interest) the complete address!!! [...] 1616 49 31_Re: DB2 STOREPROCEDURE QUESTION14_McCombs, Terry20_tmccomb@SEDGWICK.GOV31_Wed, 15 Nov 2000 08:00:10 -0600355_iso-8859-1 Yes, it is possible. The PROC for the stored procedure address space (we are not WLM, so don't know how that environment might be different) needs to include the DD card for the VSAM dataset. In his stored procedure, he opens, uses, and closes the file using the DDNAME on the DD card. Quite a bit of overhead with that, but it does work. [...] 1666 80 31_Re: DB2 STOREPROCEDURE QUESTION0_26_JCameron@MSI-INSURANCE.COM31_Wed, 15 Nov 2000 07:56:53 -0600314_us-ascii You need to define the VSAM dataset names in the stored procedure address space startup proc. If the procedure is using Work Load Manager, the DSN needs to be included in the WLM address space that this procedure is using. If not using WLM, it needs to be in the default DB2 address space (dsnSPAS). [...] 1747 59 16_Re: SQLCODE -55115_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 15 Nov 2000 08:20:42 -0600275_iso-8859-1 Hi, Jeff, I am not in a VM/VSE shop but I would assume that DB2 would still work basically the same way. Does the owner of the plan have authority to access the tables that the plan uses? I assume that you already gave ACIDSWHS authority to execute the plan. [...] 1807 67 27_Re: tablespace status WEPR?32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 15 Nov 2000 16:23:49 +0200517_- PTF Number is UQ25726.

We had same problem sometime ago. A ts has gone into WEPR status. It was because of a DASD Problem.



Cuneyt GOKSU DBA IBM CSE - DB2 Universal Database V5 Database Administration GARANTέ TEKNOLOJέ Istanbul / TURKEY Tel : 0090-212-4783210 mailto:Cuneytg@garanti.com.tr





-----Original Message----- From: John Arbogast [mailto:arbo@CP-DEMO.CP.NET] Sent: Wednesday, November 08, 2000 11:13 PM To: DB2-L@RYCI.COM Subject: Re: tablespace status WEPR? [...] 1875 29 63_Automated TS and IX space calculator - Any good ones out there?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 15 Nov 2000 08:29:23 -0600451_iso-8859-1 We were curious. Are there any (other than CA's old Platinum Space Calculator) space calculators out there for calculating primary allocations for tablespaces and index spaces? Normally, we use CDB's REXX utility for automated reorgs and image copies (Quite nice) and it automatically calculates this for us (as well as create the jobs and submit them) but occasionally we find the need to hand calculate space for special situations. [...] 1905 37 34_Pipeline Newsletter - DB2 & Oracle9_Cam White20_cwhite@REVEALNET.COM31_Wed, 15 Nov 2000 20:40:34 -0500552_iso-8859-1 The Pipeline Newsletter is now available:

http://www.revealnet.com/newsletter/newsletter_1100.htm

Key DB2 Articles include:

* "44 Tips in 66 Minutes", by Craig Mullins * DB2 Tip of the Month - "Improving Performance of the REBUILD INDEX Utility" * Fall/Winter Release of the "Knowledge Base for DB2" now available. * DB2 V6 Stored Procedures * Large Objects (LOBs) * User-Defined Functions (UDFs) * User-Defined Data Types (UDTs) * Development Guidelines more info: http://www.revealnet.com/Products/DB2/db2adm.htm [...] 1943 50 24_Re: Upgrade DB2 V4 to V612_Matt Yardley19_yardley@MCMASTER.CA31_Wed, 15 Nov 2000 09:37:01 -0500390_us-ascii We have done the second method about 4 years ago. We were forced into this type of upgrade because we were too far behind (V2.2 on XA 2.2). We felt we did not find the needed expertise from IBM to know a whether we could perform the normal upgrade migration path jumping to 2.3, 3 and then 4. Version 5 had been out, but, we are not bleeding edge. We also went to OS390 V1.2. [...] 1994 13 67_Re: Automated TS and IX space calculator - Any good ones out there?16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Wed, 15 Nov 2000 09:36:46 -0500282_us-ascii db2 estimator from the ibm site

================================================ 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. 2008 107 27_Re: tablespace status WEPR?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 09:38:13 -0500455_iso-8859-1 We had UQ25726 applied long before we had a tablespace go into WEPR status. Like yours, our WEPR originated with a DASD problem.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Cuneyt Goksu (Garanti Teknoloji) [mailto:CuneytG@GARANTI.COM.TR] Sent: Wednesday, November 15, 2000 9:24 AM To: DB2-L@RYCI.COM Subject: Re: tablespace status WEPR?



PTF Number is UQ25726. [...] 2116 22 67_Re: Automated TS and IX space calculator - Any good ones out there?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 15 Nov 2000 15:45:32 +0100549_us-ascii There's DB2 Estimator (free download from IBM site), but it's a windows application.

I'm sure that if you dig Xephon archives you may find some REXX/Clists to perform space calculations... I know that CA/Platinum utilities can do some space calculations, too....

HTH

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. 2139 57 89_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 09:44:32 -0500545_iso-8859-1 Maybe because IBM figures everyone wanting certification will buy a DB2 for Barbie/OS* license for learning purposes?

Outfox them. Get a free Linux and a free trial DB2/UDB for Linux. Learn that way.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Beighton-Dykes, Hugh [mailto:hugh.beighton-dykes@EDS.COM] Sent: Wednesday, November 15, 2000 7:24 AM To: DB2-L@RYCI.COM Subject: Why is Fisher-Price knowledge required for OS/390 Certification b ut not vice versa? [...] 2197 31 52_Auth error on bind using secondary authids and views14_Loux, Jonathan23_Jonathan.Loux@UCONN.EDU31_Wed, 15 Nov 2000 09:42:02 -0500283_iso-8859-1 We are using DB2 v5.1, OS/390 r2.8 and ACF2 6.3 (we are not using ACF2 for DB2, just the canned secondary authid exits). A secondary authid is able to do a select against a view in dynamic SQL, but when the select is in a COBOL program, we get an error at bind time: [...] 2229 13 67_Re: Automated TS and IX space calculator - Any good ones out there?15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Wed, 15 Nov 2000 08:55:01 -0600311_us-ascii Have you tried the space estimator that come with version 6?

================================================ 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. 2243 59 56_Re: Auth error on bind using secondary authids and views16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 09:57:21 -0500419_iso-8859-1 By default, authorization checking for dynamic SQL is done at run time. For static SQL, either: 1) Specify VALIDATE RUN on your BIND or 2) BIND from an ID having sufficient authority. Does DB29852 or any of its RACF GROUPs have the noted SELECT authority?



We get around this by having all the BINDs done through a production process which uses a SYSADM ID which does not have TSO access. [...] 2303 31 68_Re: Automated TS and IX space calculator - Any good ones out ther e?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 15 Nov 2000 09:06:17 -0600450_iso-8859-1 Thanks for the responses, so far. We do have DB2 Estimator downloaded. We wanted to see if there were any more.

Linda

-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHUS.COM] Sent: Wednesday, November 15, 2000 8:55 AM To: DB2-L@RYCI.COM Subject: Re: Automated TS and IX space calculator - Any good ones out there?



Have you tried the space estimator that come with version 6? [...] 2335 136 20_DB2 Storage Problems12_Dallas Focht21_dallas.focht@PMIC.COM31_Wed, 15 Nov 2000 09:45:08 -0600492_us-ascii We are running DB2 Version 6 and there appears to be a single batch job that is causing major storage problems in the DBM1 address space. Normally the working set size for DB2 is 10-15M. Currently is is 340M and causing severe page thrashing. Below is a screen print from TMON/DB2 of the private area subpools. It looks like SP229 is the culprit. We have a call open with IBM and so far they have asked for a dump of the address space. Has anyone encountered a similar problem? [...] 2472 236 24_Re: DB2 Storage Problems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 10:44:18 -0500684_iso-8859-1 What are your bufferpool sizes?



eric pearson ITO DB2 support Eric.Pearson@nscorp.com

-----Original Message----- From: Dallas Focht [mailto:dallas.focht@PMIC.COM] Sent: Wednesday, November 15, 2000 10:45 AM To: DB2-L@RYCI.COM Subject: DB2 Storage Problems



We are running DB2 Version 6 and there appears to be a single batch job that is causing major storage problems in the DBM1 address space. Normally the working set size for DB2 is 10-15M. Currently is is 340M and causing severe page thrashing. Below is a screen print from TMON/DB2 of the private area subpools. It looks like SP229 is the culprit. We have a call open with IBM [...] 2709 239 24_Re: DB2 Storage Problems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 10:54:50 -0500684_iso-8859-1 ALso: Have you applied PTF PQ35696?



eric pearson ITO DB2 support Eric.Pearson@nscorp.com

-----Original Message----- From: Dallas Focht [mailto:dallas.focht@PMIC.COM] Sent: Wednesday, November 15, 2000 10:45 AM To: DB2-L@RYCI.COM Subject: DB2 Storage Problems



We are running DB2 Version 6 and there appears to be a single batch job that is causing major storage problems in the DBM1 address space. Normally the working set size for DB2 is 10-15M. Currently is is 340M and causing severe page thrashing. Below is a screen print from TMON/DB2 of the private area subpools. It looks like SP229 is the culprit. We have a call open with [...] 2949 30 30_DB2 OS/390 Performace Monitors10_Robert Ord21_robertord@HOTMAIL.COM29_Wed, 15 Nov 2000 16:03:35 GMT411_- Dear All,

We are looking to replace our DB2 performance monitor. We currently have CA-Insight which in it's day was pretty good but with all the takeovers has been left high and dry.

If anyone can give any pointers on the latest offerings in this area I would be very grateful. If anyone has recently been through this exercise I would be interested in their choice and the reasons for it. [...] 2980 54 34_Re: DB2 OS/390 Performace Monitors16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 11:03:58 -0500539_iso-8859-1 For The DB2 subsystems we use Omegamon/DB2 (the classic version, not the CUA version) and are very pleased. For application monitoring it is hard to beat Platinum Detector. We looked at Subsystem Analyzer when it was still Platinum and decided we liked Omegamon better.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Robert Ord [mailto:robertord@HOTMAIL.COM] Sent: Wednesday, November 15, 2000 11:04 AM To: DB2-L@RYCI.COM Subject: DB2 OS/390 Performace Monitors [...] 3035 101 56_Re: Auth error on bind using secondary authids and views14_Loux, Jonathan23_Jonathan.Loux@UCONN.EDU31_Wed, 15 Nov 2000 11:08:23 -0500376_iso-8859-1 Hi, Eric. Thanks for your response. DB29852 belongs to the RACF group PSDV, which is the owner of the view and underlying tables. If I run dynamic SQL under DB29852, I can select against the view. When I bind a package and execute the program in batch, it fails. The bind is successful with a message due to the VALIDATE(RUN). However, at run time I still get [...] 3137 65 26_Runstats problems DB2 V5.116_Martin Berchtold31_Martin.Berchtold@SYSTEMATICS.DE31_Wed, 15 Nov 2000 10:07:13 -0600437_- Hello,

we got several serious problems with RUNSTATS yesterday. A few jobs abended with `funny` errorcode/reasoncodes. My seach in the APAR database wasn't very successful, all the PTFs mentioned in the list were already installed.



For the moment we are running on DB2 V5.1, we have a data sharing environemt. The tablespaces are segmented tablespaces, the tables are small, there is only one index defined. [...] 3203 154 22_Re: DB2 and SMPe setup14_Dean Montevago18_DMonteva@VNSNY.ORG31_Wed, 15 Nov 2000 11:20:43 -0500665_iso-8859-1 Eric, I tried this and it worked great. Thanks. Dean

> -----Original Message----- > From: Pearson, Eric L, [SMTP:Eric.Pearson@NSCORP.COM] > Sent: Monday, November 13, 2000 11:19 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 and SMPe setup > > NOT HERE!!!! > If for no other reason, we like to reduce the enqueues on the CSI. > > BTW, here is a tweak for improved throughput on RECEIVE: > > Change your JCL to: > //SMPCSIX DD DSN=csidatasetname, > // DISP=SHR > //SMPCSI DD SUBSYS=(BLSR,'DDNAME=SMPCSIX', > // 'BUFND=200','BUFNI=50','HBUFND=200','HBUFNI=50', > // 'MSG=I','DEFERW=YES','SHRPOOL=15'), > // DSN=csidatasetname, > // DISP=SHR > > to [...] 3358 26 18_Re: unit test data15_Melvin Demmicks25_melvin_demmicks@YAHOO.COM31_Wed, 15 Nov 2000 08:33:10 -0800638_us-ascii Thanks to all who responded (and in advance to anyone else who does).

Glad to hear from you all that RI is critical to even unit test environments.

Thanks for the other information about download methods also.

Melvin

__________________________________________________ Do You Yahoo!? Yahoo! Calendar - Get organized for the holidays! http://calendar.yahoo.com/

================================================ 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. 3385 96 30_Re: Runstats problems DB2 V5.119_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 15 Nov 2000 10:32:37 -0600632_ISO-8859-1 Martin, Search of APARs turned up about 10 - 15 hits on "DSNUSIDX+0316".

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 [...] 3482 18 50_Re: PeopleSoft HRMS Question - PAY CONFIRM process0_39_brian_holroyd.consultant@PEOPLESOFT.COM31_Wed, 15 Nov 2000 08:47:11 -0800485_us-ascii Fritz, Well Fritz, I'm at Peoplesoft and I've indicated a willingness to work with you. I'll forward a whole series of e-mails I've had with development support about this query to you directly so I don't clutter up the listserv Brian

================================================ 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. 3501 21 30_Re: Runstats problems DB2 V5.116_Martin Berchtold31_Martin.Berchtold@SYSTEMATICS.DE31_Wed, 15 Nov 2000 11:09:35 -0600535_- Hi Rick,

Thanks a lot, answering so fast. Perhaps I use the wrong plattform to do my search - I'm using the follwoing address http://techsupport.services.ibm.com/support/s390.

If you have another or a better one, please let me know.

Many thanks in advance.

Martin

================================================ 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. 3523 47 30_Re: Runstats problems DB2 V5.112_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 15 Nov 2000 09:22:24 -0800714_us-ascii Do you or someone at your company have an account with IBM InfoLINK? This is really the best place to do support searches and opening of problems with IBM. If so, then https://www2.ibmlink.ibm.com/cgi-bin/master?xh=p*Hyo4P17RgSyN1USenSnF9332&request=logoff&xhi=usa%2emain%7cusa%2emain



This is the signon page for that site. Another would be the PASSPORT ADVANTAGE site. --- Martin Berchtold wrote: > Hi Rick, > > Thanks a lot, answering so fast. Perhaps I use the > wrong plattform to do my > search - I'm using the follwoing address > http://techsupport.services.ibm.com/support/s390. > > If you have another or a better one, please let me > know. > [...] 3571 184 24_Re: DB2 Storage Problems12_Dallas Focht21_dallas.focht@PMIC.COM31_Wed, 15 Nov 2000 11:28:00 -0600620_us-ascii Yes, that PTF is applied.

"Pearson, Eric L," wrote:

> ALso:Have you applied PTF PQ35696? eric pearson > ITO DB2 support > Eric.Pearson@nscorp.com > > -----Original Message----- > From: Dallas Focht [mailto:dallas.focht@PMIC.COM] > Sent: Wednesday, November 15, 2000 10:45 AM > To: DB2-L@RYCI.COM > Subject: DB2 Storage Problems > > We are running DB2 Version 6 and there appears to be a > single batch job that is causing major storage problems in > the DBM1 address space. Normally the working set size for > DB2 is 10-15M. Currently is is 340M and causing severe page > thrashing. Below is [...] 3756 135 56_Re: Auth error on bind using secondary authids and views13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 15 Nov 2000 09:56:52 -0800362_iso-8859-1 I could be wrong here but I am hazarding a guess because no one else has responded. To bind a package, the package 'owner' must have requisite authorities for all static SQLs in the program. So, if you name PSDV as the owner, your bind should go through . Because DB29852 is connected to PSDV, it should have no problems executing the plan then. [...] 3892 55 89_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa?17_Necessary, Cheryl31_Cheryl.Necessary@INGRAMBOOK.COM31_Wed, 15 Nov 2000 12:19:42 -0600398_iso-8859-1 I can't explain why it was done that way, but it is not as scary as it seems. I took both tests at the tech conference. I was pretty stressed over 512 but did pass it. If you just do some general review, read some of the postings on this list server, and talk to others "in the know" you should be able to pass the test without heavy duty investment in training on other platforms. [...] 3948 67 34_UDB Version 7 and OLE/DB interface23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Wed, 15 Nov 2000 13:40:33 -0500577_iso-8859-1 DB2 Data Base Discussion List

Question: Will UDB Version 7 be an "OLE/DB provider" and eliminate the problem noted below?



These applications use Microsoft Transaction Server with UDB v5.2. The applications were written under Microsoft's design guidelines which call for the application to open a DB connection, execute SQL, then close the connection. This is repeated for all SQL activity in the same transaction. MTS uses pooled DB connections so that the application does not physically open connections multiple times in a transaction. [...] 4016 94 40_Help- Access path changes after reorg V612_HEPP SHERY C17_schepp@SRPNET.COM31_Wed, 15 Nov 2000 11:43:02 -0700612_ISO-8859-1 I have a question for the fellow PSDB2 and DB2L list members.

We're 7.53 psoft financials- 7.58 psoft tools OS/390 2.7 data sharing. The end of September we upgraded to DB2 V6 level 0004 + hypers in production. Performance was acceptable for most processes. Now that we've been running V6 for a while- our normal utility schedule (reorg) has started to kick in . What we are noticing now is queries/processes that used to perform efficiently are now choosing less than optimal access paths. This has occurred mainly on dynamic sql using multiple table joins- we have parallelism disabled. [...] 4111 78 68_Matching Index Access with only 2nd Column of Index in WHERE Clau se16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Wed, 15 Nov 2000 16:53:53 +0530550_- Hi All, One of my old friend sent me a query with the EXPLAIN output and that was using index access with match cols = 1.The column used in the WHERE predicate was the 2nd column of the index.I was surprised to see how without specifying the 1st column of the index DB2 has choosen matching index scan.I am sure he has sent me the accurate information. If someone have any specific idea about this kind of situation please pass that to me.The query is having some other non-indexeable predicates as well but as far as the indexeable column in [...] 4190 23 21_DB2 UDB for OS/390 V60_26_mmetcalf@NOTES.STATE.NE.US31_Wed, 15 Nov 2000 13:12:44 -0600468_us-ascii Does anyone know why the copy utility would create 3 threads for an image copy where we only specify one tablespace per step? It appears that parallelism is invoked but we do not specify the PARALLEL option on our copy statements. Only one of the 3 threads have a status of INDB2, the other 2 have a status of INAPPL. We just migrated to V6 and we are also in datasharing mode. I have searched the DB2-L archives but found no information regarding this. [...] 4214 27 52_DB2PM, DPMPARMS Dataset Not Compatible After Upgrade0_24_tkelly@NOTES.STATE.NE.US31_Wed, 15 Nov 2000 13:15:06 -0600389_us-ascii Has anyone run into the problem of not being able to use DPMPARMS after upgrading to the next version of DB2PM?

The DPMPARMS dataset maintains report layout changes but you can not use it after upgrade and must delete, allocate, and manually make all report layout changes again.

I have contacted IBM but they just consider it an enhancement and not a problem. [...] 4242 136 44_Re: Help- Access path changes after reorg V610_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Wed, 15 Nov 2000 14:08:13 -0600709_iso-8859-1 Shery,

I didn't see any mention in your e-mail of RUNSTATS after the REORG and before the BIND.



______________________________ Reply Separator _________________________________ Subject: Help- Access path changes after reorg V6 Author: owner-db2-l@RYCI.COM at INTERNET Date: 11/15/00 1:52 PM



I have a question for the fellow PSDB2 and DB2L list members.

We're 7.53 psoft financials- 7.58 psoft tools OS/390 2.7 data sharing. The end of September we upgraded to DB2 V6 level 0004 + hypers in production. Performance was acceptable for most processes. Now that we've been running V6 for a while- our normal utility schedule (reorg) has started to kick [...] 4379 34 56_Re: Auth error on bind using secondary authids and views14_Loux, Jonathan23_Jonathan.Loux@UCONN.EDU31_Wed, 15 Nov 2000 14:20:51 -0500300_iso-8859-1 Thank you, Ashish. This did work. There is still a difference in the way views and tables are being handled, since there was no problem generating a package when the program did a select directly against the table. It was only when there was a view involved that there was a problem. [...] 4414 25 38_SMS conversion - placement of datasets12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Wed, 15 Nov 2000 14:34:19 -0500575_- We are converting our production DB2 databases to SMS in the next month and are trying to decide whether to go with one SMS pool in total or two pools - one for tablespaces, one for index spaces. These databases handle online activity 22 hours a day, 7 days a week and significant batch activity 5 nights a week. We've read opinions advocating each approach and were wondering if anyone has experience with a very active online system being placed in one SMS pool? We move approximately 25 million plan executions through DB2 daily and the current allocation of these [...] 4440 138 44_Re: Help- Access path changes after reorg V613_Zessin, Peter25_Peter.Zessin@ATCOITEK.COM31_Wed, 15 Nov 2000 12:49:45 -0700439_iso-8859-1 We have discovered, what appears to be a V6 RUNSTAT problem.

If we collect index freq value stats for an index with one RUNSTAT statement per index, then we get bad stats in SYSCOLDIST and similar access path problems. FREQUENCYF > 100%

If we collect index freq value stats for an index with one RUNSTAT statement for each component concatenation of the index, then we get good stats and better access paths. [...] 4579 44 42_Re: SMS conversion - placement of datasets14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 15 Nov 2000 15:04:54 -0500338_ISO-8859-1 One pool and you will be in really big trouble. Two pools will be better, but you will still have performance problems. Every system has some small/manageable number of performance critical datasets.... these need to handled either through additional pools, or ACS exits to be sure they can get the performance they need. [...] 4624 99 30_Re: Runstats problems DB2 V5.114_Richard Bowles23_RBowles@DOA.STATE.LA.US31_Wed, 15 Nov 2000 14:06:00 -0600486_iso-8859-1 I actually ran into the exact error message just yesterday. We are running V6.1 on OS/390 2.8 in a non-data sharing environment. I received that message while running the IBM COPY utility against the catalog tablespaces. On looking over the JCL I found that I was attempting to copy a tablespace that was not defined to the subsystem - DSNDB06.SYSSEQ. I was running JCL created with a new install (PUT0009) against an older subsystem. Here is the utility error message: [...] 4724 40 22_Re: Batch QMF Question15_Connie Shockley26_connie.m.shockley@LMCO.COM31_Wed, 15 Nov 2000 14:00:35 -0600647_- On Tue, 14 Nov 2000 14:01:52 -0500, Tom Johnson wrote:

>We just recently started running batch QMF jobs. Periodically we get a return code of 998 on the ISPSPROF member in the ISPTLIB. This is due to an enque from another batch qmf job running concurrently. The following ISP datasets have DSN parameters: > ISPPLIB > ISPMLIB > ISPSLIB > ISPTLIB >The following datasets have no DSN parm and are dynamically assigned: > ISPPROF >Now, finally, the question. Which datasets must exist and be specified with a DSN parm in a batch QMF job??? TIA. > >================================================ >To change [...] 4765 56 42_Re: SMS conversion - placement of datasets16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 15:15:33 -0500302_iso-8859-1 Why not many SMS pools - Say one pool for all partition 1 data one for all partition 1 index one for all partition 2 data ....

or are you specifying GUARANTEED SPACE (with its space penalties for multi-volume pools) or is paralell I/O not really that important to your workload? [...] 4822 14 30_Adding DDF entries to the BSDS13_Toppins, Mike19_MToppins@UNIPAC.COM31_Wed, 15 Nov 2000 13:43:15 -0700426_iso-8859-1 I'm aware that to add DDF entries to a subsystem, I need to run DSNJU003 with the DDF parms. Can this be safely run while the DB2 subsystem is up or does it need to be down?

================================================ 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. 4837 35 34_Re: Adding DDF entries to the BSDS22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 15 Nov 2000 15:50:10 -0500553_iso-8859-1 DISP=OLD on BSDS. DB2 subsystem must be down.



Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749



-----Original Message----- From: Toppins, Mike [mailto:MToppins@UNIPAC.COM] Sent: Wednesday, November 15, 2000 15:43 To: DB2-L@RYCI.COM Subject: [DB2-L] Adding DDF entries to the BSDS



I'm aware that to add DDF entries to a subsystem, I need to run DSNJU003 with the DDF parms. Can this be safely run while the DB2 subsystem is up or does it need to be down? [...] 4873 110 42_Re: SMS conversion - placement of datasets0_19_Tim.Lowe@STPAUL.COM31_Wed, 15 Nov 2000 14:48:52 -0600588_us-ascii Lynda, Unfortunately, I have to disagree with Joel about this. I believe that the correct answer to this is simply "it depends". You could have problems in one SMS pool, or you might not. You could have problems in two SMS pools, or you might not. You could have problems if you hand-placed every dataset today, or you might not. You might not have any "performance critical" datasets, or you could have many. You could have well-tuned bufferpools, or you could have bufferpool problems that are driving heavier I/O. You could have a ample cache in you dasd controllers, or [...] 4984 34 34_Re: Adding DDF entries to the BSDS16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 15:59:16 -0500591_iso-8859-1 Serialization will not allow this if you have followed the IBM recommendation on either the disposition (OLD) or the SHROPTNS (2,3).

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Toppins, Mike [mailto:MToppins@UNIPAC.COM] Sent: Wednesday, November 15, 2000 3:43 PM To: DB2-L@RYCI.COM Subject: Adding DDF entries to the BSDS



I'm aware that to add DDF entries to a subsystem, I need to run DSNJU003 with the DDF parms. Can this be safely run while the DB2 subsystem is up or does it need to be down? [...] 5019 60 42_Re: SMS conversion - placement of datasets12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Wed, 15 Nov 2000 16:08:39 -0500319_- Thanks, Joel,

This is the first large online system we're converting, so we're a little cautious. Although, we have handled our non-SMS volumes as a virtual pool with one DB2 stogroup for the tablespaces and one DB2 stogroup for the indexspaces and only had one hot spot / dataset to move in three years. [...] 5080 136 42_Re: SMS conversion - placement of datasets20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 15 Nov 2000 16:07:31 -0500316_iso-8859-1 Tim

I don't see what disagreement is ? I think what Joel was suggesting was a rule of thumb and what you are telling is very generic. However rule of thumb is mostly correct but not always.

I am assuming you must be disagreeing with the strong words "you will be in really big trouble" [...] 5217 83 42_Re: SMS conversion - placement of datasets16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 15 Nov 2000 16:18:55 -0500440_iso-8859-1 Wow! That is vast good fortune. So maybe you only need one storage group plus rapid access to your SMS person should specific placement issues arise.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Stone, Lynda [mailto:Lynda.Stone@LIBERTYMUTUAL.COM] Sent: Wednesday, November 15, 2000 4:09 PM To: DB2-L@RYCI.COM Subject: Re: SMS conversion - placement of datasets [...] 5301 149 42_Re: SMS conversion - placement of datasets12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Wed, 15 Nov 2000 16:33:12 -0500324_iso-8859-1 Thanks, Tim,

Yes, I was afraid "it depends" that would be the 'right' answer! You bring up a few extra points that we will need to check on. For the most part, the DASD and bufferpools are fairly well tuned. Our partitioning has the data spread nicely and our queries use I/O parallelism effectively. [...] 5451 108 71_Re: Matching Index Access with only 2nd Column of Index in WHERE Clause11_David Nance16_DWNance@FHSC.COM31_Wed, 15 Nov 2000 16:16:01 -0500307_US-ASCII Not sure about how it chose that access path in the first place, but how about using col4 = literal or col4 = 2nd lit. or col4 = 3rd lit. etc... Wouldn't like the looks of it much, but might work better if starts using index again.

Dave Nance First Health Services, Corp. (804)527-6841 [...] 5560 181 31_Re: Can some one explain this ?23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 15 Nov 2000 16:45:25 -0500370_iso-8859-1 Issac, We tested the cursor approach and it takes about 20 sec to fetch the first row. So we are going to leave it for now.

Sanjeev, The DMTH value is not reached for the buffer pool when this query is run. but it could be another event/threshold being reached that might be triggering GETPAGE for each row. Seems like time to read manuals again. [...] 5742 128 44_Re: Help- Access path changes after reorg V612_HEPP SHERY C17_schepp@SRPNET.COM31_Wed, 15 Nov 2000 14:56:53 -0700435_ISO-8859-1 Daniel- runstats is ran after the reorg utility and this application is mostly dynamic sql.

Shery

-----Original Message----- From: DANIEL CYR [mailto:DCYR@EMAIL.USPS.GOV] Sent: Wednesday, November 15, 2000 1:08 PM To: DB2-L@RYCI.COM Subject: Re: Help- Access path changes after reorg V6



Shery,

I didn't see any mention in your e-mail of RUNSTATS after the REORG and before the BIND. [...] 5871 29 47_Call for Authors (DB2 UDB for NT, Unix, AS/400)9_Cam White20_cwhite@REVEALNET.COM31_Thu, 16 Nov 2000 04:09:23 -0500546_iso-8859-1 RevealNet is currently looking for technical content for DB2 UDB development and administration. If you have material you wish to write or contribute, please contact Steve Hilker, Director of Publications at shilker@revealnet.com. Contributors will be compensated for content provided, if accepted. Key areas of interest include:

* DB2 UDB on NT/Unix installation, configuration, tuning and troubleshooting * DB2 UDB Web Development and Web Administration * QA work and enhancements to our Knowledge Base for DB2 on OS/390 [...] 5901 170 44_Re: Help- Access path changes after reorg V60_19_Tim.Lowe@STPAUL.COM31_Wed, 15 Nov 2000 15:58:36 -0600416_us-ascii Shery, Does your runstats gather KEYCARD information, and if so, are the queries accessing the most frequent values?

Tim





HEPP SHERY C cc: Sent by: DB2 Subject: Re: Help- Access path changes after Data Base reorg V6 Discussion List



11/15/2000 03:56 PM Please respond to DB2 Data Base Discussion List [...] 6072 192 44_Re: Help- Access path changes after reorg V612_HEPP SHERY C17_schepp@SRPNET.COM31_Wed, 15 Nov 2000 15:16:37 -0700453_ISO-8859-1 Tim- RUNSTATS TABLESPACE tsname TABLE(ALL) INDEX(ALL) SHRLEVEL CHANGE

I'm running a test runstats now using the keycard freqval numcols to see if that will make a difference. I'll let you know if it does

Shery



-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Wednesday, November 15, 2000 2:59 PM To: DB2-L@RYCI.COM Subject: Re: Help- Access path changes after reorg V6 [...] 6265 190 24_Re: DB2 Storage Problems12_Dallas Focht21_dallas.focht@PMIC.COM31_Wed, 15 Nov 2000 16:27:10 -0600631_us-ascii Yes, that PTF is applied. However, now IBM is telling us it will not look at the problem until rebinding under version 6. Is it standard practice to rebind all packages/plans when migrating to a new release? I was under the impression it was not.

"Pearson, Eric L," wrote:

> ALso:Have you applied PTF PQ35696? eric pearson > ITO DB2 support > Eric.Pearson@nscorp.com > > -----Original Message----- > From: Dallas Focht [mailto:dallas.focht@PMIC.COM] > Sent: Wednesday, November 15, 2000 10:45 AM > To: DB2-L@RYCI.COM > Subject: DB2 Storage Problems > > We are running DB2 Version 6 and there appears to [...] 6456 79 16_Re: SQLCODE -55116_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 16 Nov 2000 00:31:01 +0100459_us-ascii jeff,

please have a look at SYSTEM.SYSPROGAUTH and SYSTEM.SYSTATAUTH.

also, please feel free to contact me offline for any help on Mickey Mouse DB2......

ciao!

Aurora Emanuela Dell'Anno Systems Programmer Sistemi Informativi S.p.A. e-mail: aurora.dellanno@sistinf.it tel. +39 335 7429486

no trees were killed in the sending of this message. however - a large number of electrons were seriously inconvenienced. [...] 6536 149 27_Re: Tablespace status AREST12_Kirk Hampton16_khampto1@TXU.COM31_Wed, 15 Nov 2000 18:11:10 -0600351_us-ascii We also encountered this problem after an Abend involving parallelism. We still have an open problem with IBM on it. I am the Sysprog, but the DBA's here chose to work around the AREST by Dropping/ReCreating the tablespace and restoring with DSN1COPY from Image Copies. Fortunately, this happened on our Development DB2, not Production. [...] 6686 61 56_Re: Auth error on bind using secondary authids and views12_Carol Sutfin19_csutfin@AMSOUTH.COM31_Wed, 15 Nov 2000 11:38:07 -0600351_- Yep, My application DBa's get this all of the time. Try adding (or changing) you OWNER parameter to the secondary authid from ACF2. I have found that for some reason when binds are done in batch the submitter ID does not get translated into the secondary authid.

Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 csutfin@amsouth.com [...] 6748 48 33_Stored Procedures and Packagesets10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 16 Nov 2000 10:11:05 +1000615_ISO-8859-1 Hello everybody,

I am trying to get some help regarding stored procedures and packagesets.

I have a VB application calling a stored procedure (lets call it SP1) which is bound into a particular collid (lets call it COLL01) on DB2 for OS390 V5, thru DB2 Connect.

When I executed the application, I got an error message saying "package NULLID.SP1.token not found". I then realised that the DB2 connect modules were bound into NULLID on DB2/OS390, and DB2 uses the COLLID of the calling application for the SP. So I bound SP1 into NULLID, and then was able to successfully execute [...] 6797 18 34_Re: DB2 OS/390 Performace Monitors8_Bob Abad16_BobRabad@AOL.COM29_Wed, 15 Nov 2000 19:23:17 EST525_ISO-8859-1 I recently joined a company, and we are using BMC's Mainview for DB2 products. Personally, I like it because it has lots of features, and easier for me to translate/understand..Please visit the BMC website and click on Mainview from their product lists..

Good luck!

================================================ 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. 6816 39 56_Re: Auth error on bind using secondary authids and views15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 16 Nov 2000 11:24:30 +1100637_- Hi Carol,

You have to explicitly give an Owner value because otherwise, which of your secondary IDs is to be the owner? DB2 doesn't know, and can't possibly. That's why you must specify the Owner parm.

Regards,



Raymond



> -----Original Message----- > From: Carol Sutfin [SMTP:csutfin@AMSOUTH.COM] > Sent: Thursday, 16 November 2000 4:38 am > To: DB2-L@RYCI.COM > Subject: Re: Auth error on bind using secondary authids and views > > Yep, My application DBa's get this all of the time. > Try adding (or changing) you OWNER parameter to the secondary authid from > ACF2. I have found that [...] 6856 103 31_Re: DB2 STOREPROCEDURE QUESTION12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Thu, 16 Nov 2000 02:32:57 GMT640_- Thanks a lot to all of you,

harbry



>From: JCameron@MSI-INSURANCE.COM >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 STOREPROCEDURE QUESTION >Date: Wed, 15 Nov 2000 07:56:53 -0600 > >You need to define the VSAM dataset names in the stored procedure address >space startup proc. >If the procedure is using Work Load Manager, the DSN needs to be included >in the WLM address >space that this procedure is using. If not using WLM, it needs to be in >the default DB2 address >space (dsnSPAS). > >John Cameron >jcameron@msi-insurance.com >651.631.7188 > > > > > > > > [...] 6960 60 8_sql -44411_Tom Marcano23_marcano_db2@HOTMAIL.COM29_Thu, 16 Nov 2000 03:13:50 GMT502_- Hello all,

I got the sql -444, when I executed the update for my table, the sql code said something about the load module could not be found, I don't know why......what I'm trying to do is a SQL procedure so Am I supposed to have a load module? I suspected that my procedure was created like external procedure instead SQL procedure If anyone know how to create a SQL procedure I'd appreciate your help , also if I select the sysibm.sysroutines I could be able to identify my problem.... [...] 7021 95 12_Re: sql -44410_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 16 Nov 2000 14:13:01 +1000589_ISO-8859-1 Tom,

-444 occurs when ur stored proc doesnt have a corresponding load module.

Here is an extract from one of the IBM redbooks.(DB2 UDB Server for OS/390 Version 6 Technical Update, SG24-6108-00 - U can download it from the net)

" You write your SQL SP by using SQL. It is not executed in this form, however; you need to process the source code to generate a load module and a data base request module (DBRM). The load module must be located in a library accessible to the workload manager stored procedures address space from which it executes and the [...] 7117 227 38_Re: UDB Version 7 and OLE/DB interface11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU31_Thu, 16 Nov 2000 16:44:19 +1100534_- George, I can't answer your original question regarding V7 being an OLE/DB provider, but I might have a workaround for you. We also ran into locking problems a couple of years ago, and we were advised by IBM to set DB2_RR_TO_RS=ON. This causes DB2 not to take the infamous "next key" locks which I believe are the cause of your problem. UDB uses row locking only, no index locking (similar to type 2 indexes on z/OS - at least as far as I know), but those next key locks still interfere with one row other than the one you are [...] 7345 19 89_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa?13_Henry J. Cobb14_hcobb@SLIP.NET31_Thu, 16 Nov 2000 00:09:32 -0600435_- > Outfox them. Get a free Linux and a free trial DB2/UDB for Linux.

You didn't mention that you don't need to buy a PC for this.

Just run it all on the mainframe. %)

-HJC

================================================ 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. 7365 46 42_Re: SMS conversion - placement of datasets10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 16 Nov 2000 09:07:04 +0100559_us-ascii Hi Linda.

I used SMS for DB2 datasets from many years and (in my opinion) it's better to use at least two SMS pools (1 for tablespaces, 1 for indexes. It's useful in reorgs with SHRLEVEL REFERENCE, for instance ), to use controllers with big cache (> 2 Gb), do not use GUARANTEED SPACE (in the long run this may cause problems, we found DB2 tablespaces even in DASDs reserved to S390 system datasets....) if possible, and try to allocate DB2 on DASD like SHARK (if any) or at least of last generation (like HDS7700E. In this case use the [...] 7412 25 30_Re: Runstats problems DB2 V5.116_Martin Berchtold31_Martin.Berchtold@SYSTEMATICS.DE31_Thu, 16 Nov 2000 03:44:48 -0600601_- Richard, Thanks for your comment. I'm sure, that the indexspace was there during the execution of the runstats utility. The operators were able to restart the utilitiy later on without getting any further problems. Additionally your CSECT (DSNUFOBD+02E4) is different to our CSECT (DSNUSIDX+0316). The load module is the same (DSNUTILA). I guess the reasoncode is used for different problems. In the next step I'm trying to use the suggested IBM InfoLINK /thanks to Myron). As far as I could see is this feature similar to the old DIALIBM and so I thing, that I will find the right information [...] 7438 18 37_Archive factilities within Peoplesoft10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM31_Thu, 16 Nov 2000 06:32:32 -0500379_ISO-8859-1 Can anybody give me details of the archiving capabilities within the PeopleAoft application?





Alan Johns

================================================ 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. 7457 23 35_IRLM RESOURCE WAIT - DB2 Vn 5/OS39014_Marilyn Millar31_marilyn.millar@SOMERFIELD.CO.UK31_Thu, 16 Nov 2000 12:14:47 -0000544_iso-8859-1 Hi list,

we are just about to implement on-line reorgs and would like your experienced observations please ! Is it necessary to increase the IRLMRWT threshold please? We currently run with the default of 60,

thanks for your time,

Marilyn Millar

Dba Somerfield PLC

================================================ 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. 7481 36 39_Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS39012_Coles, Peter18_PETER.COLES@CA.COM31_Thu, 16 Nov 2000 12:35:54 -0000576_iso-8859-1 marilyn, this will only apply to the switch2 and build 2 phases for shrlevel change. I would wait and see based on experience!

-----Original Message----- From: Marilyn Millar [mailto:marilyn.millar@SOMERFIELD.CO.UK] Sent: Thursday, November 16, 2000 12:15 PM To: DB2-L@RYCI.COM Subject: IRLM RESOURCE WAIT - DB2 Vn 5/OS390



Hi list,

we are just about to implement on-line reorgs and would like your experienced observations please ! Is it necessary to increase the IRLMRWT threshold please? We currently run with the default of 60, [...] 7518 132 58_Re: Using DFDSS to copy databases to different DB2 systems15_Stewart S Smith32_stewart_s_smith@STANDARDLIFE.COM31_Thu, 16 Nov 2000 12:57:01 -0000279_iso-8859-1 In case anybody was following this, I was contacted directly by someone at CA with information that Fast Recover does handle the PGLOGRBA by checking the PGLOGID to see if the PGLOGRBA is set or not and then if it is, it is reset to current RBA of target system. [...] 7651 41 56_Calling Ex Cobol programmers - how to flush BSAM buffers15_Stewart S Smith32_stewart_s_smith@STANDARDLIFE.COM31_Thu, 16 Nov 2000 13:11:03 -0000425_iso-8859-1 We have discovered lately a vunerability within our restartability standards for OS/390 DB2 batch jobs where we write to a sequential file as part of the processing (BSAM access). Our standards had decreed that the design for any such program must allow for the possibility of duplicates in the sequential file following a restart cos the sequential file writes will not be rolled back with the DB2 rollback. [...] 7693 58 42_Re: SMS conversion - placement of datasets19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 16 Nov 2000 07:11:28 -0600310_ISO-8859-1 Lynda, Good advise from Joel, Tim, Max, others . . . One thing I've seen that sometimes causes problems is that Storage Management types have a habit of setting up pools down a string of DASD rather than across several controllers, when available. This can be really important on non-PAV DASD. [...] 7752 12 39_Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS39012_Praveen K.C.19_cpraveen@IN.IBM.COM30_Thu, 8 Jul 1999 15:57:11 +0530257_us-ascii cancel

================================================ 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. 7765 47 21_Abend S0C4 in DSNDBM113_Michael Fleck14_m.fleck@LVR.DE31_Thu, 16 Nov 2000 14:39:58 +0100477_ISO-8859-1 Hi list members,

we're having some serious problems in our DB2 environment. We're running DB2 V5.1 on OS/390 V2.8.

Last weekend we did two things. We brougt a new version of BMC-Utilities into production and also brought a lot of DB2 tablespaces and indexes under SMS-Conrol using IBM-Reorg.

Since Monday we've experienced 4 DB2 crashes (1 in production, 3 in test). We always get the same error in the same module in DSNDBM1 adress space. [...] 7813 92 37_Re: Stored Procedures and Packagesets13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 16 Nov 2000 07:50:20 -0600601_iso-8859-1 I do not know of a real neat solution. On the stored procedure definition, you can specify a collid. Then you can leave DB2Connect in the nullid collection and have your own collid for the sp.

This then starts to get a bit more complicated when you want multiple environments (dev, usertest etc.) in the same sub-system. Then you only have one SP definition linked to one collection id. Usually you have each environment in different collections. To solve that problem you start using the AUTHID (or LUNAME which V6 is making obsolete I think) to direct the calling application [...] 7906 241 24_Re: DB2 Storage Problems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 08:49:37 -0500397_iso-8859-1 Dallas, While I as at IBM and afterwards, I have upgraded from 1.1 to 2.1 to 2.2 to 2.3 to 3.1 to 4.1 to 5.1. *NEVER* did my shop find a need to REBIND *. I do not understand why, nor do I see in the product documentation why this REBIND would be required. I suspect IBM does not really have an answer and is asking the customer to do unreasonable things in order to buy IBM time. [...] 8148 40 89_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 08:52:23 -0500392_iso-8859-1 I find it much cheaper to buy a beater PC than to buy a mainframe ;=)

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Henry J. Cobb [mailto:hcobb@SLIP.NET] Sent: Thursday, November 16, 2000 1:10 AM To: DB2-L@RYCI.COM Subject: Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa? [...] 8189 49 39_Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 08:54:52 -0500564_iso-8859-1 We just did that - the conversion to online REORG.

You need to increase IRLMRWT if your programmers have Bachelor Programmer Syndrome (can't COMMIT).

A better solution is to have all programs have COMMIT logic which results in COMMIT more frequently than IRLMRWT.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Marilyn Millar [mailto:marilyn.millar@SOMERFIELD.CO.UK] Sent: Thursday, November 16, 2000 7:15 AM To: DB2-L@RYCI.COM Subject: IRLM RESOURCE WAIT - DB2 Vn 5/OS390 [...] 8239 64 39_Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS39014_Marilyn Millar31_marilyn.millar@SOMERFIELD.CO.UK31_Thu, 16 Nov 2000 14:04:35 -0000414_iso-8859-1 Thanks Eric - made I laugh ! Marilyn

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: 16 November 2000 1:55 PM To: DB2-L@RYCI.COM Subject: Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS390



We just did that - the conversion to online REORG.

You need to increase IRLMRWT if your programmers have Bachelor Programmer Syndrome (can't COMMIT). [...] 8304 74 25_Re: Abend S0C4 in DSNDBM112_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Thu, 16 Nov 2000 09:40:34 -0500581_iso-8859-1 Michael,

What is your put level on DB2??????



Robert

-----Original Message----- From: Michael Fleck [mailto:m.fleck@LVR.DE] Sent: Thursday, November 16, 2000 8:40 AM To: DB2-L@RYCI.COM Subject: Abend S0C4 in DSNDBM1



Hi list members,

we're having some serious problems in our DB2 environment. We're running DB2 V5.1 on OS/390 V2.8.

Last weekend we did two things. We brougt a new version of BMC-Utilities into production and also brought a lot of DB2 tablespaces and indexes under SMS-Conrol using IBM-Reorg. [...] 8379 52 24_Re: DB2 Storage Problems10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 16 Nov 2000 15:39:57 +0100542_us-ascii Hi Eric. We migrated from v4 to v5 in a datasharing environment some month ago.





All was OK (more or less) but we noted that suddenly a STATISTICS counter, QISTCOLS jumped from 0 to an verrry high value after migration (zillions). In SDSNMACS the description of this counter is:





Total number of columns (rows x columns) for which a Select Procedure was bypassed because the Select Procedure was invalidated by applying service to DB2. Everything will continue to function correctly, [...] 8432 142 12_Re: sql -44411_Tom Marcano23_marcano_db2@HOTMAIL.COM29_Thu, 16 Nov 2000 14:46:13 GMT463_- Kals,

Thanks for helping me,

I knew how create an external store procedure, but if I like to create procedure like (sql procedure) I have to have a source and load module too. you mean my source code can be it below: > > > >CREATE PROCEDURE CREDIT > >(IN ORD_NO_VAR CHAR(9), > > IN order_line_VAR CHAR(29)) > >LANGUAGE SQL > > NO WLM ENVIRONMENT > >UPDATE owner.CREDIT_INFO_B > > SET order_line = oder_line_VAR > > WHERE ORD_NO = ORD_NO_VAR+ [...] 8575 92 31_Antw: Re: Abend S0C4 in DSNDBM113_Michael Fleck14_m.fleck@LVR.DE31_Thu, 16 Nov 2000 15:55:56 +0100620_ISO-8859-1 Hi Robert,

it's an old level (maybe 9908) , but we've never experienced any problems with it until last Monday.

Regards, Michael

Datenbank- und Ressourcendienste Landschaftsverband Rheinland Infokom Ottoplatz 2, 50679 Kφln Tel: 0221/809/2826

>>> "Hall, Robert" Donnerstag, 16. November 2000 15:40:34 >>> Michael,

What is your put level on DB2??????



Robert

-----Original Message----- From: Michael Fleck [mailto:m.fleck@LVR.DE] Sent: Thursday, November 16, 2000 8:40 AM To: DB2-L@RYCI.COM Subject: Abend S0C4 in DSNDBM1 [...] 8668 84 25_Re: Abend S0C4 in DSNDBM119_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 16 Nov 2000 08:58:57 -0600623_ISO-8859-1 Michael, I found one hit on the MVS APARS: OW31560

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." [...] 8753 73 24_Re: DB2 Storage Problems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 09:55:15 -0500462_iso-8859-1 Max, This is very interesting. Where do you find this counter? Is it in a (DB2PM or equivalent) report? Other?

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Thursday, November 16, 2000 9:40 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Storage Problems



Hi Eric. We migrated from v4 to v5 in a datasharing environment some month ago. [...] 8827 27 20_OS-390 real storage:7_DB2 fan16_askdb2@YAHOO.COM31_Thu, 16 Nov 2000 09:07:41 -0600583_- Hello List, Could someone clarify this question about memory. Recently in a shop, where I was on a tuning assignment, we almost exhausted all the available options. The customer still wanted to probe further, so he brought an expert for a opinion. That person commented that we need more memory(central storage)to handle the volume and the mix of operations. We are on OS-390 operating on a LPAR mode. My understanding is that one cannot have more than 2GB as real memory because it cannot be addressable anyway(in a 31 bit addressing mode). I am quoted to be wrong here. How [...] 8855 118 24_Re: DB2 Storage Problems13_Lynne Flatley17_LFlatley@NEFN.COM31_Thu, 16 Nov 2000 10:31:49 -0500517_us-ascii This is the explanation that I got from one of our DB2 systems programmers,

Lynne, it appears that earlier versions of DB2 had applied internal optimization to SQL statements that has become obsolete. The SQL statements do in fact still work but this message is an informational message that is returned in an SMF record IFCID224. If you have a very high transaction volume encountering this problem you may see some performance degradation because of the time it takes to issue the message etc. [...] 8974 47 24_Re: OS-390 real storage:14_Dean Montevago18_DMonteva@VNSNY.ORG31_Thu, 16 Nov 2000 10:28:47 -0500594_- You are correct. The max real and vitual is 2GB. As you already know, the 64 bit world changes all this, with the new processors and operating system you can go beyond this 2GB limitation.

> -----Original Message----- > From: DB2 fan [SMTP:askdb2@YAHOO.COM] > Sent: Thursday, November 16, 2000 10:08 AM > To: DB2-L@RYCI.COM > Subject: OS-390 real storage: > > Hello List, > Could someone clarify this question about memory. > Recently in a shop, where I was on a tuning assignment, we almost > exhausted > all the available options. The customer still wanted to probe further, so [...] 9022 28 12_Online Reorg16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 16 Nov 2000 15:35:13 -0000266_- I have a question regarding Online Reorg which bothers me a bit. DB2 chooses to do a parallel build of INDEXES - but how is the number of tasks determined. The attached example shows 21 - but I have also had examples where the number of tasks were set to 96. [...] 9051 59 25_Re: DB2 UDB for OS/390 V60_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Thu, 16 Nov 2000 09:45:35 -0600533_us-ascii Hi Mitchell, Yes we were quite surprised by this change as well and ran into the problem of " maximum number of DB2 users reached " because we didn't realize the increase in the number of threads in many of the utilities. For us we also found 3 threads for image copies but what caught us is when you reorg items and use SORTKEYS , there are two threads created for every index, so for some tables that have three or four indexes you might see six to eight additional threads for those indexes. We run many reorgs at a [...] 9111 125 24_Re: DB2 Storage Problems12_Dallas Focht21_dallas.focht@PMIC.COM31_Thu, 16 Nov 2000 09:49:06 -0600570_us-ascii Thanks everyone for your input. We have bent IBM's ear a little and they will be taking a closer look. I have also rebound all of our packages just to see if that will make a difference. I'll let you know.



Lynne Flatley wrote:

> This is the explanation that I got from one of our DB2 systems programmers, > > Lynne, it appears that earlier versions of DB2 had applied internal > optimization to SQL statements that has become obsolete. The SQL statements > do > in fact still work but this message is an informational message that is [...] 9237 32 89_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on b ut not vice versa?14_Philip Gunning18_pgunning@PHEAA.ORG31_Thu, 16 Nov 2000 10:50:12 -0500611_us-ascii This won't directly answer the question but I have worked on both sides and I have noticed with DB2 V6.1 for OS/390 that the family is merging closer together, although there will probably always be some platform specifics to exploit the particular platform Operating System. Example, Stored Procedures, Identity columns, UDTs, Declared Temporary tables, and UDFs are defined and administered with many similarities across platforms. So, I think we will see more common features and administration functions in future releases and that may be a reason for requiring the subject knowledge. -- Phil [...] 9270 115 37_Re: Stored Procedures and Packagesets13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 16 Nov 2000 09:53:55 -0600377_iso-8859-1 ... Another point I did not mention. I know you are on V5.1 But in V6.1 the SPs can be qualified by a schema name, similar functionality as a qualifier for tables is my understanding.

That may impact your solution if you are going V6.1 soon - i.e. do not put a lot of effort into solving the V5.1 issue when V6.1 will have an alternative to investigate. [...] 9386 46 34_Re: DB2 OS/390 Performace Monitors13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 16 Nov 2000 09:52:24 -0600537_iso-8859-1 Robert,

We just went through this process. We dropped Omegamon for BMC's Mainview (we also have BMC's Apptune).

Dave

-----Original Message----- From: Robert Ord [mailto:robertord@hotmail.com] Sent: Wednesday, November 15, 2000 10:04 AM To: DB2-L@RYCI.COM Subject: DB2 OS/390 Performace Monitors



Dear All,

We are looking to replace our DB2 performance monitor. We currently have CA-Insight which in it's day was pretty good but with all the takeovers has been left high and dry. [...] 9433 51 24_Re: OS-390 real storage:16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 10:49:54 -0500457_iso-8859-1 This is a configuration issue. OS/390 supports a max of 2GB of CSTOR. If your machine or LPAR has more the 2 GB of storage, you can specify the additional storage as ESTOR, even if it is purchased as CSTOR.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: DB2 fan [mailto:askdb2@YAHOO.COM] Sent: Thursday, November 16, 2000 10:08 AM To: DB2-L@RYCI.COM Subject: OS-390 real storage: [...] 9485 68 60_Re: Calling Ex Cobol programmers - how to flush BSAM buffers16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Thu, 16 Nov 2000 07:56:03 -0800443_iso-8859-1 The first thing I would do is make sure that the BSAM file is unblocked, because the program will output a physical record, not a logical one. I don't know of any command to flush a buffer, but I would play with buffer parameters (like the COBOL RESERVE clause, or the JCL BUFOUT parm) to reduce the output buffer to 1, if possible. If the best you can get is 2, and you can handle dups, consider writing each entry out twice. [...] 9554 24 24_Re: DB2 Storage Problems10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 16 Nov 2000 16:53:44 +0100398_us-ascii Hi Eric, nice to meet you.

We detected this columns using Boole & Babbage Mainview, while loading B&B historical tables (we had errors because a field was define too small to contain a value of zillion).

A description of this field is in the DSNDQIST member of SDSNMACS library and in DB2PM manual (chapter 78 , pag. 78-9, table 78-1 part 8, offset 1392 in my manual) . [...] 9579 107 42_Re: SMS conversion - placement of datasets12_Kirk Hampton16_khampto1@TXU.COM31_Thu, 16 Nov 2000 09:57:41 -0600588_us-ascii Hello Lynda, The former storage management regime here at TXU was bent on merging everything into one SMS storage pool, and managing the exceptions via ACS rules by dataset name. The current manager has backed off from that, having seen unneccesary difficulty at other sites. We currently have at least 8 different SMS storage groups, which may be excessive in the other direction. Our DB2 datasets mostly go to 2 of those pools, but by primary allocation size, <50 meg in one pool, larger datasets in the other, not by whether they are tablespaces or indexes. If I had the [...] 9687 14 24_Re: OS-390 real storage:7_DB2 fan16_askdb2@YAHOO.COM31_Thu, 16 Nov 2000 10:23:58 -0600452_- Yeah, but the question was whether CSTOR can be more than 2GB. I was told that the dynamic address translation can translate to any real address(even >2GB)while the virtual address is only 2GB. thanks, db2fan

================================================ 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. 9702 30 24_Re: OS-390 real storage:14_Dean Montevago18_DMonteva@VNSNY.ORG31_Thu, 16 Nov 2000 11:28:59 -0500619_- No, in 31 bit mode the limit is 2GB for both real and virtual.

> -----Original Message----- > From: DB2 fan [SMTP:askdb2@YAHOO.COM] > Sent: Thursday, November 16, 2000 11:24 AM > To: DB2-L@RYCI.COM > Subject: Re: OS-390 real storage: > > Yeah, but the question was whether CSTOR can be more than 2GB. I was told > that the dynamic address translation can translate to any real > address(even > >2GB)while the virtual address is only 2GB. > thanks, > db2fan > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage [...] 9733 35 24_Re: OS-390 real storage:16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 16 Nov 2000 11:41:13 -0500520_iso-8859-1 You cannot specify CSTOR > 2GB in OS/390.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: DB2 fan [mailto:askdb2@YAHOO.COM] Sent: Thursday, November 16, 2000 11:24 AM To: DB2-L@RYCI.COM Subject: Re: OS-390 real storage:



Yeah, but the question was whether CSTOR can be more than 2GB. I was told that the dynamic address translation can translate to any real address(even >2GB)while the virtual address is only 2GB. thanks, db2fan [...] 9769 39 34_Re: DB2 OS/390 Performace Monitors13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 16 Nov 2000 11:02:42 -0600554_us-ascii We have used BMC's Mainview for years. It is very easy to use to identify problem programs or queries.

Helen

Robert Ord wrote:

> Dear All, > > We are looking to replace our DB2 performance monitor. We currently have > CA-Insight which in it's day was pretty good but with all the takeovers has > been left high and dry. > > If anyone can give any pointers on the latest offerings in this area I would > be very grateful. If anyone has recently been through this exercise I would > be interested in their choice and the [...] 9809 14 24_Re: OS-390 real storage:8_Karl Huf23_Karl_Huf@NOTES.NTRS.COM31_Thu, 16 Nov 2000 10:53:15 -0600460_us-ascii You are correct. Unless you are bleeding edge OS and hardware (2.10 or z/OS + Freeway hardware) the max for central (aka real) storage is 2 GB. Advise your client to ask for their money back from the 'expert'.

================================================ 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. 9824 63 60_Re: Calling Ex Cobol programmers - how to flush BSAM buffers0_24_John_McComb@VANGUARD.COM31_Thu, 16 Nov 2000 13:34:24 -0500531_us-ascii How about writing the output to a DB2 table instead of a flat file. If the program abends, your output table is rolled back along with the rest of the DB2 updates, and the job can be restarted with no duplicates and no lost records. When the program ends, you unload the output table to a flat file using a utility or simple extract program. If the unload fails with a B37 or other abend, it can be restarted from the top. After the successful extract or before the next batch cycle, the table would be initialized.. [...] 9888 76 34_DB2Connect and MSAccess/Outer Join9_Colin Fay13_cfay2@CSC.COM31_Thu, 16 Nov 2000 13:49:07 -0500426_us-ascii Hi, I have a client running querying MSAccess 97 , DB2Connect V7.1 against DB2/OS390 v 5.1.

In doing an outer join get this message

IBM CLi Driver DB2 SQL0104N An unexpected token "(" was found following "''" . Expected tokens may include "" SQLSTATE = 42601

This has been determined to be caused by the incuision of parens ")" in the ON statement of the outer join For example. [...] 9965 37 30_Archive logs to DASD then tape11_Robert Jans21_Jans.Robert@AMSTR.COM31_Thu, 16 Nov 2000 12:40:00 -0700628_- This question was sent to me by our sysprog with the request I post it for him. ---------------------------------------------------------------------------

Our current DB2 subsytems archive directly to tape, but we would like to change to a process in which we archive to DASD and then migrate the archives to tape. One of our considerations is that we would like to stack multiple archives onto a single tape volume. This would probably mean using an in-house process to spin the disk datasets to tape because we don't have anything that could do this and maintain the archive dataset names that DB2 knows about. [...] 10003 26 3_BPS15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 17 Nov 2000 09:43:52 +1100393_- Nice one, Eric. Must remember that. Good for a Friday morning chuckle.



Raymond



> -----Original Message----- > From: Pearson, Eric L, [SMTP:Eric.Pearson@NSCORP.COM] > Sent: Friday, 17 November 2000 12:55 am > To: DB2-L@RYCI.COM > Subject: Re: IRLM RESOURCE WAIT - DB2 Vn 5/OS390 > ...your programmers have > Bachelor Programmer Syndrome (can't COMMIT)... > [...] 10030 120 60_Re: Calling Ex Cobol programmers - how to flush BSAM buffers0_31_greg.palgrave@UNISYSWEST.COM.AU31_Fri, 17 Nov 2000 09:16:29 +0800291_us-ascii I think - and I'm prepared to be corrected here, I haven't played Cobol for a while! - that you then end up doing one physical I/O per record. This is OK for a small file, but if you're writing a few tens or hundreds of thousand records to it, watch your runtime go way up.... [...] 10151 82 89_Re: Why is Fisher-Price knowledge required for OS/390 Cer tification b ut not vice versa?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 17 Nov 2000 14:43:26 +1100550_iso-8859-1 Having just done these tests at AP IDUG, I'll make a few points - the main gotya for 512 is that when it has a question about the effect of SQL, it assumes OS/390 SQL RULES='STD' _not_ 'DB2'. There were questions where the 'correct os/390 answer' was not an option. - mostly you can wing the V7 specific questions - the information in the What's New is enough. (I must add however, that there was one question that I would have had no idea about if it hadn't been specifically mentioned in the previous presentation I had gone to.) - [...] 10234 14 55_Daryl G Spletzer/Pr/Consumers/CMS is out of the office.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Fri, 17 Nov 2000 02:10:54 -0500365_us-ascii I will be out of the office from 11/16/2000 until 11/20/2000.

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. 10249 18 17_Resynch Databases19_Margaritis Dimitris20_margardi@NOVABANK.GR31_Fri, 17 Nov 2000 09:13:13 +0200448_iso-8859-7 Hi all, I have implement an one -way replication between two DB2 Servers on NT. Which is the best procedure in order to resyncronize these databases if where out of synch?

Regards Dimitris

================================================ 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. 10268 11 18_test-please ignore11_?????? ????24_dimokap@INTERAMERICAN.GR31_Fri, 17 Nov 2000 08:44:30 +0200244_-

================================================ 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. 10280 15 53_Raymond L Gardner/AMER/TRS/AEXP is out of the office.17_Raymond L Gardner26_Raymond.L.Gardner@AEXP.COM31_Fri, 17 Nov 2000 01:17:09 -0700365_us-ascii I will be out of the office from 11/17/2000 until 11/27/2000.

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. 10296 26 22_Re: test-please ignore17_Kumar, Arun (CTS)22_KArun@CAL.CTS-CORP.COM31_Fri, 17 Nov 2000 04:11:03 -0500702_- why?



> ================================================ > 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. This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail [...] 10323 132 87_Re: Why is Fisher-Price knowledge required for OS/390 Certification but not vice versa?0_18_mebert@AMADEUS.NET31_Fri, 17 Nov 2000 10:55:52 +0100602_us-ascii Hello Hugh,

at the recent Geneva IDUG I took all necessary tests for all 4 IBM DB2 Solutions Expert certifications. All my experience is with mainframe DB2 administration. But no problem, I still passed all tests first try. My "training" for the non-OS/390 exams I got from the sample tests. Most of the questions are generic. With some of the more specialised questions, you simply guess. The passing criteria are not so difficult; it's between 55% and 65% answers correct or so. Also at the IDUG, they (IBM) gave out free CDs: DB2 UDB Fastpath for Experienced Relational DBAs. I [...] 10456 185 44_Re: Help- Access path changes after reorg V60_18_mebert@AMADEUS.NET31_Fri, 17 Nov 2000 11:10:35 +0100557_us-ascii Hello Shery,

this is not going to help you much but the problem is not restricted to V6 and PS. I have several times mentioned severe access path differences with minimal data differences in something as simple as a 3-table join (with less than optimal indexes, to be sure; my attempts to have them reengineered got bogged down a long time ago). I think the DB2 Optimizer is trying to be far too clever for its own (or anybodys) good. The best solution is to keep the SQL and table/index design so simple that it can't help but pick an [...] 10642 32 18_Connection Pooling23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Fri, 17 Nov 2000 07:35:53 -0500615_iso-8859-1 Our shop recently went into production with DB2 6.1. Since the implementation of DB2 6.1 there has been a few strange behaviors with the processing of DBAT's. 1- The execution of these distributed threads are not being terminated when exceeding the liberal timeout value of 1800 seconds, 30 minutes. 2- When viewing the lock ownership with Omegamon, it appears that the lock list is populated with many locks on objects that are not associated with the executing DBAT. The locks listed are from a previous thread execution and I believe are being carried over through the use of Connection Pooling. [...] 10675 31 15_New V6 UDB Apps14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 17 Nov 2000 08:04:51 -0500477_iso-8859-1 List,

Our shop will be moving to DB2 UDB V6 for OS/390 in a couple of weeks. We have virtually no experience w/ triggers and very little w/ stored procedures. Can anyone provide real world experiences as to the performance impact, specifically response time, when these features are used to assist in enforcing data integrity, capturing 'before' data (logging), etc. I am assuming that our triggers and stored procedures will be fairly simple in design. [...] 10707 32 11_Opportunity12_Kent Collins15_kcollins@WF.NET31_Fri, 17 Nov 2000 07:44:46 -0600400_iso-8859-1 Looking for Senior Level DB2 UDB DBA to work in an exciting environment. Location: Dallas/Ft Worth Texas Contract or Perm positions available.

Requirements: More than 10 years experience Multi-platform environment, Sun, HP, AIX and AS/400 HA experience EEE and EE Production Support and Development Support areas

Some experience in Oracle and SqlServer would be a plus. [...] 10740 84 22_Re: Connection Pooling14_Philip Gunning18_pgunning@PHEAA.ORG31_Fri, 17 Nov 2000 08:42:29 -0500386_us-ascii Gerge, What do you have the following ZPARMS set to:

POOLINAC -- default is 2 minutes, if 0 then no connection pooling. IDTHTOIN -- default is 0, no timeout CMTSTAT -- Inactive or active?



Do these threads that are hanging around show up as inactive when issuing a -display thread(*) type(inactive)







Phil Gunning DB2 DBA [...] 10825 120 52_Re: Trying to define TS with Extended Addressability15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 17 Nov 2000 07:50:26 -0600568_iso-8859-1 Thanks for the comments that I received. It seems that not too many are trying this. This is what I found out so far for those who are interested.

First, your primary quantity of any partition of the tablespace is limited to the maximum size of the devices on which you want to allocate the tablespace. This is a limitation within DFSMS/dfp. Here, I can't make my primary quantity larger than the 2.8G devices that we use. You need to make the secondary quantity the same size and let it go into extents across several devices. Second, I had to [...] 10946 75 34_Re: Archive logs to DASD then tape12_Sue Janowitz18_SJanowitz@NEFN.COM31_Fri, 17 Nov 2000 08:52:58 -0500367_us-ascii I am the DB2 SYSADM here. We archive directly to tape, but I'll put in my thoughts on archive dataset stacking. I think it's risky, because if you need to recover or rollback, you'd be stuck with single-threading all jobs, since you could only use one of the archives at a time. (Unless you're prepared to load them onto DASD any time they are needed.) [...] 11022 121 34_Re: Archive logs to DASD then tape0_17_cflink@AWGINC.COM31_Fri, 17 Nov 2000 08:17:30 -0600565_us-ascii We currently archive our logs to a dasd "tape pool" then HSM migrates the pool every hour to physical tape. The storage administrators call this tape mount management. It stacks small tape files together on tape via/ DFSMS/ HSM. When the archived log is needed HSM will mount the appropriate tape and restore it to dasd then any applications would then access the dasd version of the log. You have to be aware in a disaster recovery situation that the HSM archive logs have to be availiable. So you need to have a copy of them sent offsite along with [...] 11144 151 60_Re: Calling Ex Cobol programmers - how to flush BSAM buffers16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Fri, 17 Nov 2000 06:17:43 -0800440_iso-8859-1 You're right, but if you want to use a flat file, and you want to make sure that the last record is in the file instead of the buffer (if you get a B37, you lose the entire block - the bigger the block, the more you lose - if the block is larger than 1 record, you lose COMMITTED checkpoints as well as the TO-BE-COMMITTED checkpoint), and you want a solution cheaper than closing and reopening the file, this is an option. [...] 11296 190 35_Re: Creating Compression Dictionary0_18_mebert@AMADEUS.NET31_Fri, 17 Nov 2000 15:27:02 +0100602_us-ascii Hello Bjarne,

with your partitioning scheme, you're going to run into problems after 21 years....

There have been some responses to this one. I will add one more, because I just last week did the exact same thing. That's the way I did it:

1) Unload about 10.000 rows of data from an existing partition. I used REORG UNLOAD EXTERNAL and an unload dataset with SPACE=(CYL,(5,0)). That way the Unload job will abend after unloading 3.5 MB of data without the need for specifying any complicated WHERE clause. 2) That sample data will have to be edited before each load. [...] 11487 84 22_Re: Connection Pooling0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Fri, 17 Nov 2000 08:28:46 -0600518_us-ascii Hi George,

I think what you are encountering is the new V6 thread reuse. When we migrated to V6, we too saw that locks would extend on even after the plan completed. What we discovered is that it was the new V6 thread reuse feature.

Our scenario was that a plan would run that was bound with release (deallocate) which indicates to get rid of locks when the program terminates but if the thread is still active, DB2 does not think its done so IS locks remain active at the TS and TB level. [...] 11572 19 28_DB2 Object Creation Products16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Fri, 17 Nov 2000 09:47:31 -0500530_iso-8859-1 Hey Folks... Does anyone know if IBM offers a product to create databases/tablespaces/tables/indexes/Foreign Keys, etc.? I'm looking for something other then BMC's Catalog Manager & CA-Platinums products...

Michael Dempsey Data Base Administration UNC Health Care System

================================================ 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. 11592 20 0_19_Vanderbeck, Raymond29_Raymond.Vanderbeck@SCHWAB.COM31_Fri, 17 Nov 2000 06:47:22 -0800425_iso-8859-1

DB2-L signoff

Raymond K. Vanderbeck Senior Staff DB2 System Support STech-Host DBMS Location:PHXDCANX-02-241 Phone:602-659-3043 Raymond.Vanderbeck@Schwab.com

================================================ 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. 11613 29 9_RI puzzle12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Fri, 17 Nov 2000 08:54:28 -0600602_iso-8859-1 I am posting this for a co-worker of mine, and we would really appreciate a response!

We have defined a duplicate index on the parent table to establish a foreign key with the unique/primary key fields of the child table. AND IT WORKS. It was our intention to establish this constraint on a duplicate index of the parent table with the unique index of the child table for a valid, logical, application constraint check. When we started having problems with our change management software we were directed to page 222 of the SQL Reference Guide which indicates that this approach [...] 11643 96 32_Re: DB2 Object Creation Products0_18_mebert@AMADEUS.NET31_Fri, 17 Nov 2000 15:52:42 +01001813_us-ascii SPUFI & DSNTEP2?

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany







From: "Dempsey, Michael" on 17/11/2000 14:47 GMT

Please respond to DB2 Data Base Discussion List









|---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |To: | |---------> >---------------------------------------------------------------------------> |DB2-L@RYCI.COM | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> |cc: | |---------> >---------------------------------------------------------------------------> | (bcc: Michael Ebert/MUC/AMADEUS) | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |Subject: | |---------> >---------------------------------------------------------------------------> |DB2 Object Creation Products | >---------------------------------------------------------------------------> >-------------------------------------------| [...] 11740 38 32_Re: DB2 Object Creation Products12_Helson, Greg20_GHelson@AEGONUSA.COM31_Fri, 17 Nov 2000 08:55:23 -0600392_iso-8859-1 Take a look at IBM's DB2 ADMIN TOOL offering. It doesn't have all the features or functions as BMC's Cat Mgr, but it certainly covers administration basics such as creating objects.

Greg

-----Original Message----- From: Dempsey, Michael [SMTP:MDempse@UNCH.UNC.EDU] Sent: Friday, November 17, 2000 9:48 AM To: DB2-L@RYCI.COM Subject: DB2 Object Creation Products [...] 11779 29 32_Re: DB2 Object Creation Products13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 17 Nov 2000 10:13:52 -0500313_iso-8859-1 Have a look at the DB2 Admin tool and look at the Control Center that comes with DB2 UDB for Windows, Unix, OS/2, Linux.

You'll find they do some of the DBA functions but not all.

Look at the DB2 Home page for the other tools they have. IBM is putting a lot of resources into tools. [...] 11809 47 18_Re: unit test data14_Thomas, Steven21_Steven_Thomas@BMC.COM31_Fri, 17 Nov 2000 09:19:25 -0600583_iso-8859-1 One small addition - if you don't have RI, you might not spot any errors where a program tries to add a child row before the parent. That's just an addition to all the other repsonses that have already been posted.

Steve

> -----Original Message----- > From: Melvin Demmicks [mailto:melvin_demmicks@YAHOO.COM] > Sent: 15 November 2000 16:33 > To: DB2-L@RYCI.COM > Subject: Re: unit test data > > > Thanks to all who responded (and in advance to anyone > else who does). > > Glad to hear from you all that RI is critical to even > unit test environments. [...] 11857 200 88_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on but not vice versa?11_Kwan, James18_James_Kwan@BMC.COM31_Fri, 17 Nov 2000 09:49:28 -0600530_iso-8859-1 Dr.

I agree with you that the tests do not really show the ability and knowledge of each individual who took the test. I do believe this is an indication of whether you will be qualify for the job if you pass the tests. The reason you can pass the test is you have the basic knowledge of DB2. And I believe it is easier for someone with DB2/OS390 knowledge to pickup UDB on other platform. Even though you might not know how to create an instance eg on UDB window. You will know it once you have done once. [...] 12058 72 24_Re: Upgrade DB2 V4 to V614_Thomas, Steven21_Steven_Thomas@BMC.COM31_Fri, 17 Nov 2000 10:01:54 -0600339_iso-8859-1 Hi Mike,

In a previous life, I helped someone go from V2.3 to 5 like this. At the time, we used RC/Merger from CA to set up the database migrations, but you could achieve the same manually (ugh!) or by using other products like Change Manager (look up Merging subsystems using Alter in Chapter 8 of the User Guide). [...] 12131 50 22_Re: Connection Pooling13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 17 Nov 2000 10:02:00 -0600429_iso-8859-1 George,

Are you using DB2 Connect 6.1 as well? With connection pooling turned on (it's the default)?

You might see if apar pq33473 ( it might be as ptf UQ38560) is on your v6 DB2 OS/390 system.

Thanks Dave

-----Original Message----- From: Sercombe, George W A133 [mailto:George.Sercombe@CIGNA.COM] Sent: Friday, November 17, 2000 6:36 AM To: DB2-L@RYCI.COM Subject: Connection Pooling [...] 12182 127 22_Re: Connection Pooling23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Fri, 17 Nov 2000 11:08:38 -0500446_iso-8859-1 Philip,

POOLINAC is likely the default value for I am positive about the "on" status of connection pooling. IDTHTOIN is set at 1800 seconds. CMTSTAT is set to Inactive.

Thanks, George

-----Original Message----- From: Philip Gunning [SMTP:pgunning@PHEAA.ORG] Sent: Friday, November 17, 2000 8:42 AM To: DB2-L@RYCI.COM Subject: Re: Connection Pooling

Gerge, What do you have the following ZPARMS set to: [...] 12310 105 22_Re: Connection Pooling12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 17 Nov 2000 08:24:45 -0800343_us-ascii George, Locks with 6.1 can be carried over in the thread DISCONN. If you have bound RELEASE(DEALLOCATE) they will especially be carried over. If I remember correctly, RELEASE(COMMIT) and cursor nohold at the PC will release the locks. That's what my current client has and we're not having the locks held at session termination. [...] 12416 51 21_DB2 UDB for OS/390 V60_26_mmetcalf@NOTES.STATE.NE.US31_Fri, 17 Nov 2000 10:26:37 -0600570_us-ascii This is a response from IBM on why the V6 image copies now use multiple threads:

What you have observed is indeed true. This is an enhancement that has taken place in DB2 V6.1 to improve the elapsed time for copy/recover utilities. You have no control over it and there is no way to disable sub-task creation. Many customers have observed this, and in the short term you will need to monitor (because of the additional threads being created) the jobs to make sure that you are not running into max threads being reached, if so then CTHREAD may need [...] 12468 147 22_Re: Connection Pooling23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Fri, 17 Nov 2000 11:28:51 -0500581_iso-8859-1 Shan,

I found not only IS locks remain active at the TS and TB level but a small amount of IX locks remain active. Then when one of my batch jobs that doesn't properly commit it's UOW and the almighty "X" lock from lock escalation occurs then the problem arises. I checked all of the DB2 Connect packages used through ODBC (SQLL....) and they are all release(commit). Then I looked at the SKPT and found the stored procedure list that had executed under the current thread. All of these programs were bound with release(deallocate) and I believe this is the [...] 12616 111 34_Re: Archive logs to DASD then tape11_Pedro Cunha18_pplfilho@IG.COM.BR31_Fri, 17 Nov 2000 14:20:52 -0200438_- Hello,

May I suggest here to keep 24 hours of active log on DASD? It is a recommendation when we go up to DB2 Data Sharing! Of course you need to have DASD space enough for that!

Salutations, Pedro Cunha

----- Original Message ----- From: Sue Janowitz Newsgroups: bit.listserv.db2-l To: Sent: Friday, November 17, 2000 11:52 AM Subject: Re: Archive logs to DASD then tape [...] 12728 88 34_Re: Archive logs to DASD then tape12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 17 Nov 2000 08:46:46 -0800537_us-ascii Hopefully you have enough active logs, and your commit rate is such that you won't ever 'rollback' from archive tapes. It can be a real killer if a job runs for long enough without committing, and you have to rollback through 30-40 tapes to get it out of the system, (or if your operators don't think the cancelling of the job worked and they stop DB2 so the rest of that night's "batch" could continue, and you have to read all the tapes to get DB2 back up during restart.) I'd go directly to tape, if I had enough active [...] 12817 73 88_Re: Why is Fisher-Price knowledge required for OS/390 Certificati on but not vice versa?9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Fri, 17 Nov 2000 09:40:22 -0700496_iso-8859-1 Howdy All,

For those of you that have been Mainframe DB2 DBA's for years you might consider learning DB2 UDB on Open platforms. I work for an outsourcing company and most of our DB2/OS390 customers are connected to Open systems DBMS's from DB2 from the OS390 mainframe.

In order to fully service these customers I found it necessary to learn both sides "of the fence" so I ordered a book. This book is called "SAMs Teach Yourself DB2 Universal Database in 21 days". [...] 12891 29 53_Re: Subject: Help- Access path changes after reorg V612_HEPP SHERY C17_schepp@SRPNET.COM31_Fri, 17 Nov 2000 09:55:29 -0700384_ISO-8859-1 Many many thanks to all of the responses rec'd regarding the access path changes after reorg/runstats. I ran another runstats on the index with keycard specified and got the previous access path. Run times have returned to normal levels. (Yeah- one down now onto the next set of problems!)

Also our systems programmer found this- APAR Identifier ...... PQ38749 [...] 12921 60 44_Re: Help- Access path changes after reorg V612_HEPP SHERY C17_schepp@SRPNET.COM31_Fri, 17 Nov 2000 10:15:17 -0700638_ISO-8859-1 Hello Dr. Ebert-

Yes I agree that 'fudging statistics' is a maintenance nightmare in a production environment. The only time I have looked at this as a viable option in production was regarding some of the temporary and work tables within peoplesoft. By nature these tables only house transient data during processes. I have moved them into their own database and they are not considered part of the reorg/runstats utility process. As for the handpicked indexes- we have defined some additional indexes for performance reasons. However this can be tricky within peoplesoft due to the high number of indexes already [...] 12982 59 24_QMF V610 FPARM Parameter16_Brookman, Gerald32_gerald.brookman@US.ORIGIN-IT.COM31_Fri, 17 Nov 2000 12:54:22 -0500294_iso-8859-1 On page 975 of Craig Mullins "DB2 Developers Guide - Fourth Edition", he talks about the "QMF F Parameter" called "FPARM". Can someone tell me where to find this parameter? I have looked in all my QMF V6.1.0 manuals and there is no listing for it in any of the indexes. Thanks. [...] 13042 189 22_Re: Connection Pooling0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Fri, 17 Nov 2000 12:29:15 -0600391_us-ascii Will be interested in your results. When I raised this issue at the Tech Conferenct I talked with Curt Cotner from IBM about it and he was very confident that that the thread reuse locks should only carry IS locks on theTS and TB. If you are encountering IX locks that persist after a plan is done running I would report it to IBM as I don't think that is working as designed. [...] 13232 48 34_Re: DB2 OS/390 Performace Monitors12_Greg Parrish15_zengreg@JPS.NET29_Fri, 17 Nov 2000 18:30:21 GMT405_iso-8859-1 Hi Robert, Actually, CA-Insight is still very much alive and growing, currently sporting a number of nice usability and design enhancements. Also, it exploits DB2v6 features, including all new IFCID records and fields, new feature reporting, additional buffer pool types, usage stats for GBP duplexing, triggers, new plan table columns for Explains... and perhaps more I don't know about. [...] 13281 55 64_Repost: (Non-IBM) DB2 for OS/390 Links Web Site: www.db2usa.com:13_Morrill, John12_JohnM@VP.NET31_Fri, 17 Nov 2000 11:42:46 -0700597_- Dear DB2 Interested Parties,

Marcel Levy has created and maintains a DB2 UDB for OS/390 "portal" site at http://www.db2usa.com (available in both English and French). Check it out.

From the navigation bar, here is a sample of the site's content:

* * *

Survey: Do you use DB2 packages in production ?

Back issues Headlines Links about DB2 Technical papers Bookmanager: IBM documentation on line Newsgroup and FAQ Redbooks Conferences DB2 versions and announcements News Documentation Hints and tips IBM Users groups Homepages Books Training More DB2 links [...] 13337 24 28_Re: QMF V610 FPARM Parameter11_Matt Kelley18_mgk333@HOTMAIL.COM31_Fri, 17 Nov 2000 12:49:42 -0600426_- On Fri, 17 Nov 2000 12:54:22 -0500, Brookman, Gerald wrote:

>On page 975 of Craig Mullins "DB2 Developers Guide - Fourth Edition", he >talks about the "QMF F Parameter" called "FPARM". >Can someone tell me where to find this parameter? I have looked in all my >QMF V6.1.0 manuals and there is no listing for it in any of the indexes. >Thanks. > >Gerald R. (Garry) Brookman > > [...] 13362 205 18_Fwd: Re: RI puzzle13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Fri, 17 Nov 2000 14:26:20 -0500487_US-ASCII I haven't seen a response to this yet, so I'll take a stab at it. Forgive my tendency to rant a bit.

John, I'm puzzled by this puzzle! Most of my puzzlement comes from what's NOT said. For example:

I assume that you're on DB2 for OS/390 Version 5?

I assume that the "parent" table you mention is called that because it has: (a) a PRIMARY KEY clause, *and* (b) the "child" table has a FOREIGN KEY clause that REFERENCES the aforementioned parent table. [...] 13568 40 32_Re: DB2 Object Creation Products14_Michael Finnis28_MFinnis@PRINCETONSOFTECH.COM31_Fri, 17 Nov 2000 18:30:28 -0500632_ISO-8859-1 Hi Michael,

Up front, I work for Princeton Softech who have Relational Tools for DB2 which will easily handle the tasks you require.

Mike Finnis Product Specialist Princeton Softech (Australia)

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Saturday, November 18, 2000 1:18 AM To: DB2-L@RYCI.COM Subject: DB2 Object Creation Products



Hey Folks... Does anyone know if IBM offers a product to create databases/tablespaces/tables/indexes/Foreign Keys, etc.? I'm looking for something other then BMC's Catalog Manager & CA-Platinums products... [...] 13609 34 29_Stored Procedure Builder V7.112_Keister, Jim16_JKeister@QRS.COM31_Fri, 17 Nov 2000 15:30:55 -0800400_iso-8859-1 Does anyone have the DB2 SPBuilder V7.1 installed and executing successfully??

This version requires two new DB2 Catalog tables: SYSIBM.SYSPSM & SYSIBM.SYSPSMOPTS If any of you can send me the DDL for these tables and their associated indexes, I'd appreciate it.



Thanks,

Jim Keister Sr. DB2 DBA

QRS, Corp. 1400 Marina Way South Richmond, CA. 94804 [...] 13644 43 58_DB2 V6 assembler pre-processor and ROWID, BLOB, CLOB, etc.10_Paul Wendt14_PaulW@ENET.COM31_Fri, 17 Nov 2000 18:31:04 -0800398_us-ascii Does anyone invoke static SQL from assembler language, and has tried the new V6 datatypes ROWID, BLOB, etc?

When invoking SQL from assembler (EXEC SQL), the data definitions for the variables have been regular DS statements in a DSECT.

The new V6 types have you give a pre-processor directive, "SQL TYPE IS" following the variable name, and followed by the new V6 type. [...] 13688 71 34_Re: Archive logs to DASD then tape15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 17 Nov 2000 16:08:30 +0000476_us-ascii Toine Michielse DB2 S/390 Advocate Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



Robert Jans @RYCI.COM> on 11/16/2000 07:40:00 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Archive logs to DASD then tape [...] 13760 36 18_Urgent Help Needed0_20_vpacheco@AMADEUS.NET31_Sat, 18 Nov 2000 14:12:42 +0100516_us-ascii Hello DB2 Community

I have a BIG problem and I would appreciate a direct reply (I subscribe only the Digest) if you have any idea to help me. I am running DB2 on OS/390 V6 as a DB Server for an SAP R/3 installation 4.6B.

Accidentally we DROPed one Database. YES, one Database. And it contains a LOT of relevant data - over 3000 Cyl. We have a Full Image Copy and some subsequent Incremental Image Copies for the only TS contained on that DB. We have also all the Logs since the last IIC. [...] 13797 142 67_Performance problem - DB2 for OS390 V5 - high commits, unit switc h12_Sue Janowitz18_SJanowitz@NEFN.COM31_Sat, 18 Nov 2000 11:07:26 -0500555_iso-8859-1 We have a vendor package, VSAM to DB2 conversion, which our end-users are very anxious to have moved to Production. However, in testing a quarterly job with full-stream input, it is taking a bit longer than our batch window will allow - projections are that it would go 32 hours if left to run all the way through. They've been killing it after about 3 or 4 hours in these test runs. They are running on the Production LPAR, so all environmentals are as close as can be to real-life. Exept that they're running with no runstats - they are [...] 13940 114 65_Performance problem - high commits, unit switch - additional info12_Sue Janowitz18_SJanowitz@NEFN.COM31_Sat, 18 Nov 2000 11:15:34 -0500386_us-ascii Here are the additional screens for my previous mail:

Buffer Pool statistics:

BP ID : BP4K00 BP STATISTICS TOTAL SUCCESSFUL GETPAGES : 26028640 26028640

GETPAGES PER HPOOL PAGES READ : 0.0 N/A GETPAGES PER I/O PAGES READ : 60.1 N/A SYNC READ I/O : 125239 125239

SYNC READ FROM HIPERPOOL : 0 0

SEQUENTIAL PREFETCH REQUEST : 117870 117870 [...] 14055 179 22_Re: Urgent Help Needed0_20_vpacheco@AMADEUS.NET31_Sat, 18 Nov 2000 19:04:42 +0100293_us-ascii Thanks to all of you that have replied to my note.

Now we can see the light at the end of the tunnel and I would like to share with you our experience. We adopted a mixture of the processes described on the attached notes. Unfortunately the plan A did not work very well. [...] 14235 63 34_DB2Connect and MSAccess/Outer Join9_Colin Fay13_cfay2@CSC.COM31_Sat, 18 Nov 2000 13:24:55 -0500438_us-ascii Hi,

I am resending this, having received no notification that it was sent. I apologize if you already recieved this.



I have a client running querying MSAccess 97 , DB2Connect V7.1 against DB2/OS390 v 5.1.

In doing an outer join he gets this message

IBM CLi Driver DB2 SQL0104N An unexpected token "(" was found following "''" . Expected tokens may include "" SQLSTATE = 42601 [...] 14299 84 13_Re: RI puzzle0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Sat, 18 Nov 2000 13:43:42 -0600488_us-ascii Hi John,

We ran into a similar problem in V6 Its not exactly the same as yours but has some commonalities so I'll share with you what we found.

We were trying to alter a table to add a foreign key. The foreign key column refers to a column of a unique index instead of the primary key column . However the referenced table has a primary key, and DB2 wouldn't let us make the change. We didn't know if it was because it was refering to a unique index column... [...] 14384 51 15_Re: Opportunity12_James Garvey18_garvey1@US.IBM.COM31_Sat, 18 Nov 2000 14:15:34 -0700382_us-ascii I would be curious on how many hits you get on your job offer.

I am down here in Houston this weekend. I had a bad cough for the last three weeks - this thing seems to happen every 5 years or so. Finally went to the Doctor and after the five day antibiotic regium - I am feeling fine again. Cold and WET here this weekend. Cold ? ha, ha it must be in the 50's. [...] 14436 32 38_inappropriate email: > was Opportunity15_Melvin Demmicks25_melvin_demmicks@YAHOO.COM31_Sat, 18 Nov 2000 14:24:10 -0800484_us-ascii Pardon me James, But what in blazes does your email to the 2600 or so folks on db2-l have to do with DB2?

Personally, I'm sorry to hear about your cough and your hygiene problems and your dissatisfaction with a few political situations.

But I would prefer you made those kinds of comments in a chat room with a bunch of other teenagers who might be interested, or anywhere where they didn't bother 2600 individuals interested in DB2 technology discussion. [...] 14469 59 22_Re: Urgent Help Needed14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Sat, 18 Nov 2000 17:00:31 -0600547_- Do you have a reovery product? like BMC? What SAP table is it ? Can you copy from dev or qas system? I don't know what your landscape looks like but is this a start?

> -----Original Message----- > From: vpacheco@AMADEUS.NET [SMTP:vpacheco@AMADEUS.NET] > Sent: Saturday, November 18, 2000 7:13 AM > To: DB2-L@RYCI.COM > Subject: Urgent Help Needed > > Hello DB2 Community > > I have a BIG problem and I would appreciate a direct reply (I subscribe > only the > Digest) if you have any idea to help me. > I am running DB2 on OS/390 V6 [...] 14529 50 42_Re: inappropriate email: > was Opportunity11_Doug Fuerst14_djfirst@LI.NET31_Sat, 18 Nov 2000 18:17:25 -0500581_us-ascii Easy Melvin, I think this is simply a misdirected e-mail. I hardly think James was looking to share this with the group, and it is clearly not meant to go to the group at large. Relax, and watch der blinkenlights... as we used to say....

Doug





At 02:24 PM 11/18/2000 -0800, you wrote: >Pardon me James, > But what in blazes does your email to the 2600 or >so folks on db2-l have to do with DB2? > >Personally, I'm sorry to hear about your cough and >your hygiene problems and your dissatisfaction with a >few political situations. > >But [...] 14580 195 71_Re: Performance problem - DB2 for OS390 V5 - high commits, unit switc h15_Toine Michielse18_vndobtm@US.IBM.COM31_Sun, 19 Nov 2000 05:06:40 +0000477_us-ascii Hello Sue,

Since you mentioned that you are running with an empty table I suspect that preformatting is a large portion of your suspends due to execution unit switches. I suggest for the next run you prime the tablespace by running a LOAD REPLACDE job with a dummied input dd and the PREFORMAT option. This will make sure preformatting does not occur for the primary quantity. (You also want to make sure that you won't be acquiring secondary extents...). [...] 14776 67 22_Re: Urgent Help Needed15_Toine Michielse18_vndobtm@US.IBM.COM31_Sun, 19 Nov 2000 05:08:04 +0000540_us-ascii Call me on +31 6 537 23 256

Regards,

Toine Michielse





vpacheco@AMADEUS.NET on 11/18/2000 01:12:42 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Urgent Help Needed







Hello DB2 Community

I have a BIG problem and I would appreciate a direct reply (I subscribe only the Digest) if you have any idea to help me. I am running DB2 on OS/390 V6 as a DB Server for an SAP R/3 installation 4.6B. [...] 14844 16 22_Re: Urgent Help Needed26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Sat, 18 Nov 2000 23:30:14 -0600426_- Need to use the LOG utility to print out the RBAs up until the DB was dropped then do a recover to that point. Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ 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. 14861 20 14_Pending State.7_Navid Q21_navid@DPI2.DPI.NET.IR31_Sun, 19 Nov 2000 15:02:07 +0330401_US-ASCII Hi there,

I want to know what is a pending state? When a database goes to that state? And what should we do against that?

Thanks. Navid.

================================================ 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. 14882 15 8_00E400180_27_Bud.Greenman@ONONDAGA.NY.US31_Sun, 19 Nov 2000 07:19:13 -0500334_- We were running a load (replace) utility to load up an empty tablespace. The job was cancelled then the utility terminated. When the utility was submitted again this message came up. I tried dropping and rebuilding the tablespace, but it didn't make any difference. How do I clear this up? We are on os390 2.8 and DB2 v6.1 TIA [...] 14898 43 18_Re: Pending State.19_Elmer J. Valenzuela18_elmer@KFUPM.EDU.SA31_Sun, 19 Nov 2000 15:21:14 +0300757_iso-8859-1 assuming you have cancelled the operation, try doing an image copy. this normally 'resets' the pending status hth Elmer Valenzuela elmer@kfupm.edu.sa

http://www.sixfigureincome.com/FREE/?554576

----- Original Message ----- From: "Navid Q" Newsgroups: bit.listserv.db2-l To: Sent: Sunday, November 19, 2000 2:32 PM Subject: Pending State.



> Hi there, > > I want to know what is a pending state? When a database goes to that > state? > And what should we do against that? > > Thanks. > Navid. > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The [...] 14942 16 71_Re: Performance problem - DB2 for OS390 V5 - high commits, unit switc h12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 19 Nov 2000 09:55:13 -0600322_- If you are running DB2 V6, then the times are split out separately, with a separate field for log writes, and the switch time is split out for open/close, define/extend/delete, SYSLGRNX, commit, and others. It looks like the commits and Toine's suggestions need to be addressed.

Roger Miller, DB2 for OS/390 [...] 14959 57 12_Re: 00E4001810_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Sun, 19 Nov 2000 13:37:45 -0600577_iso-8859-1 What has happened is your utility has registered its self in the directory as restartable and you have to either restart the load (replace) or terminate the utility -TERM UTIL(????.????).

If its the term path you want to take then get into SPUFI and first display the utility -DIS util(*). It should show your utility in STOPPED status. Then run -TERM UTIL(????.????). Just so we are on the same page when terminating a utility always use qualified because if another utility is running and you issue a -TERM UTIL(*) you will stop all utilities running. [...] 15017 13 12_Re: 00E400180_27_Bud.Greenman@ONONDAGA.NY.US31_Sun, 19 Nov 2000 18:38:14 -0500505_- Actually I had terminated the utility using the -term util comand. However when I try to run another job I get this error message which basically says that db2 thinks there is a restartable utility waiting, but there is none when using the -dis utility command.

================================================ 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. 15031 50 69_What exactly is Schema? (WAS : RE: Stored Procedures and Packagesets)10_teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 20 Nov 2000 09:55:50 +1000462_ISO-8859-1 Glenn,

We are actually going to V6 in the near future. I was in fact investigating the possibility of Schema for Stored procs, but I dont think I have completely understood what it actually means in terms of DB2/OS390.

Is it similar to setting the current SQLID, or is it similar to the current PACKAGESET? In other words, when I qualify the procname in my VB application, do I specify the schema name as the COLLID, or the AUTHID? [...] 15082 53 12_Re: 00E4001810_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Sun, 19 Nov 2000 19:20:38 -0600355_iso-8859-1 We had the same problem here. A couple of months ago we had a MODIFY utility running that was stopped. When we -dis util(*) nothing showed. We finally changed the utility name and the utility has been running without incident.

It seems that the utility was posted to SYSUTILX, but the -term util(*) did NOT remove it from SYSUTILX. [...] 15136 44 12_Re: 00E4001815_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 20 Nov 2000 11:41:51 +1100632_- Dan, etc.

Whilst a good short-term solution, this is really only treating the symptom, not actually fixing the problem. Brighter, more experienced minds than mine can shed some light on how to flush Sysutilx (not done it myself) if there's details in there that shouldn't be.



Raymond



> -----Original Message----- > From: DANIEL CYR [SMTP:DCYR@EMAIL.USPS.GOV] > Sent: Monday, 20 November 2000 12:21 pm > To: DB2-L@RYCI.COM > Subject: Re: 00E40018 > > > We had the same problem here. A couple of months ago we had a > MODIFY utility running that was stopped. When we -dis util(*) > nothing [...] 15181 18 72_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witc h13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Sat, 18 Nov 2000 18:51:51 -0500356_iso-8859-1 > commiting after every update ???!!!!!

Put it in the box and send it back.

David Seibert

================================================ 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. 15200 12 12_Re: 00E400180_27_Bud.Greenman@ONONDAGA.NY.US31_Sun, 19 Nov 2000 20:53:25 -0500471_- I tried that also, unfortunately it didn't make a difference. I just tried it again, this time after running a completely different utility (reorg) and then running the load with a different name. This time it did work. Thanks!

================================================ 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. 15213 70 12_Re: 00E4001823_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 20 Nov 2000 06:16:44 -0000587_iso-8859-1 Bud

hi.

if you look through the archives :

http://jupiter.ryci.com/archives/db2-l.html

you will see some threads on this very thing - but I do not remember seeing this return code in the messages.

I do believe, if my memory serves me right, that you have a crocked SYSUTILX. This will require you to create a new one. The procedure was posted on the list a couple of months back. I had assumed (assumption is the whatever of all evil) that a PTF had come out to prevent this happening - but obviously it hasn't or it has and you do not [...] 15284 96 33_Re: Stored Procedure Builder V7.137_=?iso-8859-1?Q?=C4rlebrandt_Michael?=28_Michael.Arlebrandt@VOLVO.COM31_Mon, 20 Nov 2000 07:17:37 +0100407_iso-8859-1 Hi,

The DDL to create these new tables can be found in DSN610.SDSNSAMP(DSNTIJSG),

you can find the steps to perform in the hold action for PTF UQ39824 (V6).

and yes, I'm running the SPBuilder from a UDB V7.1 PE against my DB2 v6 system. Still have some problem with the REXX SP DSNTPSMP that does not do all of it's job correctly, the TSO DELETE function does not work. [...] 15381 62 18_Re: Pending State.25_Sinha, Ranjay Kumar (CTS)24_SRanjay@CAL.CTS-CORP.COM31_Mon, 20 Nov 2000 02:32:31 -0500419_- Hi Navid, A databaes is in pending state if the database is in restricted state. Use "display database(dbname) " with "restrict" option to find list of database, tablespace or indexes in restricted status.

Use of Database is restricted if the database in any of the following state : 1)if it is stopped. 2) If it is started with read-only status 3) if the database is started with utility-only processing [...] 15444 79 18_Re: Pending State.16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Mon, 20 Nov 2000 13:36:33 +0530572_- Hi Navid, It is something like a database(tought by IBM) says to you, me and others in the list that "I am not in comfortable position with whatever is the present situation so i am 'sort' of forcing you(me and others as well) to perform some operation which will put me(DB2 database) in comfortable position. These uncomfortable positions, when not put in pending status by DB2 then it will cause some problems like data inconsistency in most of the cases.So what DB2 does, it puts the database in the pending status and ask us to resolve that by some generalized [...] 15524 34 22_CLUSTER RATIO OF < 95%10_Elsie Tama24_elsie.m.tama@VERIZON.COM31_Mon, 20 Nov 2000 03:24:34 -0500414_us-ascii HI there,

I am currently working on a project where-by I need to analyze

a) Why Certain production Tables keeps coming out of Clustering, or better yet (Tables keep on De-Clustering)

b) I need to find out the impact on this tables on load job. (E.G, Loading a table with say a Cluster Ration of 0%, or 62%.0 What impact will this have and How Should it be fix, or even avoided.? [...] 15559 61 21_R: Urgent Help Needed20_Carlo Petrone WZBKD627_carlo.petrone@ISTRUZIONE.IT31_Mon, 20 Nov 2000 09:41:46 +0100593_iso-8859-1 First - You have a definition of databese (DDL) If Yes you can recreate the db and after with DSN1COPY replace the Data.

-----Messaggio originale----- Da: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]Per conto di vpacheco@AMADEUS.NET Inviato: sabato 18 novembre 2000 14.13 A: DB2-L@RYCI.COM Oggetto: Urgent Help Needed



Hello DB2 Community

I have a BIG problem and I would appreciate a direct reply (I subscribe only the Digest) if you have any idea to help me. I am running DB2 on OS/390 V6 as a DB Server for an SAP R/3 installation 4.6B. [...] 15621 91 26_Re: CLUSTER RATIO OF < 95%16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Mon, 20 Nov 2000 14:37:57 +0530588_- Hi Elsie, First of all this is a very interesting project. I did a project on application performance tuning in my previous shop which also includes the portion described by you. As far as the clustering is concerned, bad cluster ratio always degrades performance(nothing new !!!) and at the same time we can't have all the indexes defined on the table have cluster ration 95% or more.First and foremost thing is look at the tables which doesn't have explicitly defined clustering index.In Old days people used to think it is a lot of effort coding CLUSTER in the index definition [...] 15713 47 42_Re: inappropriate email: > was Opportunity14_Scott Trometer22_scott.trometer@RCI.COM31_Mon, 20 Nov 2000 06:49:05 -0500429_iso-8859-1 Sounds to me like a simple mistake...maybe you, Melvin, should take the pill you prescribe!

-----Original Message----- From: Melvin Demmicks [mailto:melvin_demmicks@YAHOO.COM] Sent: Saturday, November 18, 2000 5:24 PM To: DB2-L@RYCI.COM Subject: inappropriate email: > was Opportunity



Pardon me James, But what in blazes does your email to the 2600 or so folks on db2-l have to do with DB2? [...] 15761 129 69_Fw: [DB2-L] Calling Ex Cobol programmers - how to flush BSAM buff ers15_Stewart S Smith32_stewart_s_smith@STANDARDLIFE.COM31_Mon, 20 Nov 2000 12:47:51 -0000557_iso-8859-1 Thanks for the replies folks

Sounds like there's some scope in area of writing dummy records and unblocking the sequential file and then reworking the output file to removed dupes. I didnt quite follow in the case of the attached how the switch is actually made to QSAM.

From the point of view of corporate standards, I like the idea of writing to a db2 table rather than a sequential file and then unloading that table, when creating report files. I think we should steer developers in that direction - 24*7 friendly as well! [...] 15891 75 26_Re: CLUSTER RATIO OF < 95%15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 20 Nov 2000 07:04:41 -0600619_iso-8859-1 1) From my experience tables get out of cluster order because there is not enough freespace defined within the table to contain the inserted rows. Perhaps you need to visit the PCTFREE and FREEPAGE definitions to make sure that you have enough space available to accomodate your inserts for a maintenance cycle. Occasionally, no matter what you do, you will have to reorganize your tablespaces on a regular basis whether monthly, biannually, or whatever. Have you examined the growth rate of the tables? Platinum had a formula for computing how much freespace to define given growth rate, size of rows, [...] 15967 14 58_Re: Performance problem - DB2 for OS390 V5 - high commits,0_27_Bud.Greenman@ONONDAGA.NY.US31_Mon, 20 Nov 2000 08:37:33 -0500324_- I had a similar problem. One way to see what statement(s) are causing the bottle neck is to use the -DIS THREAD(threadname) DETAIL command. this will show the statement number and the module at the time you execute the command. You should see one statement showing up frequently. That is the one that is the problem. [...] 15982 90 12_Re: 00E4001815_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 20 Nov 2000 07:52:02 -0600552_iso-8859-1 Hi, Les,

Yes, I did answer a question similar to this one. Please check the archives.

Linda

-----Original Message----- From: Pendlebury-Bowe, Leslie [mailto:Leslie.Pendlebury-Bowe@EU.SONY.COM] Sent: Monday, November 20, 2000 12:17 AM To: DB2-L@RYCI.COM Subject: Re: 00E40018



Bud

hi.

if you look through the archives :

http://jupiter.ryci.com/archives/db2-l.html

you will see some threads on this very thing - but I do not remember seeing this return code in the messages. [...] 16073 182 72_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witc h16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 20 Nov 2000 08:46:56 -0500310_iso-8859-1 Sue, The vast majority of the ...DSN* load modules are in ...SDSNLOAD a few are in ...SDSNEXIT and there are some used mostly at DB2 startup in ...SDSLINK.

You have found the package. Does its name start DSN? If so, it is probably part of DB2. If not, it is not part of the DB2 product. [...] 16256 46 12_Re: 00E4001816_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 20 Nov 2000 08:54:14 -0500535_iso-8859-1 Bud, 00E40018 means the old utility did not finish so DB2 is reluctant to run another one against the same tablespace.

Your options: 1) Rerun the first utility with parm 'RESTART(PHASE)' and any needed JCL changes 2) Issue the command -TERM UTIL(oldutilityname)

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Bud.Greenman@ONONDAGA.NY.US [mailto:Bud.Greenman@ONONDAGA.NY.US] Sent: Sunday, November 19, 2000 7:19 AM To: DB2-L@RYCI.COM Subject: 00E40018 [...] 16303 90 74_Re: What exactly is Schema? (WAS : RE: Stored Procedures and Pa ckagesets)13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Mon, 20 Nov 2000 08:22:44 -0600543_iso-8859-1 Kals,

I am not on V6 yet, so my information is from reading, not actual experience.

I view the schema name similar to the authid or a qualifier, which you can set via the currentSQLID if you do not explicitly specify it when calling.

It is not the collid - that I am sure. In the VB App, you should be able to set the SQLID to your authid (assuming authid = schema name), and then call the sp. I guess the idea is to make the schema name the same as the other table qualifiers you application is accessing [...] 16394 27 21_Authorization problem15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 20 Nov 2000 09:40:48 -0500407_iso-8859-1 To all,

Past weekend we combine two LPAR to one LPAR. So now our test db2 subsystem and production db2 subsystem is in one LPAR. Previously from one LPAR we were able to select tables in the other LPAR using 3 part names. Now from production DB2 QMF we are not able to access TEST DB2 tables using 3 part name. It is giving -551 even though public has the select access on the table. [...] 16422 114 26_Re: CLUSTER RATIO OF < 95%11_Kwan, James18_James_Kwan@BMC.COM31_Mon, 20 Nov 2000 08:55:20 -0600367_iso-8859-1 Sanjeev,

Refer to your point b. I believe V7 Online load will have the same rule as insert since it is using the SQL to insert the record.

James Kwan

-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.CTS-CORP.COM] Sent: Monday, November 20, 2000 3:08 AM To: DB2-L@RYCI.COM Subject: Re: CLUSTER RATIO OF < 95% [...] 16537 56 25_Re: Authorization problem15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 20 Nov 2000 08:57:53 -0600373_iso-8859-1 This is just a shot in the dark but are there any changes required for the APPLID in VTAM with the merge to one LPAR? I know an APPLID has to be defined for the DDF address space. Perhaps it is getting hosed there.

Just a thought,

Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin [...] 16594 80 25_Re: Authorization problem11_David Ayers24_david.ayers@HIGHMARK.COM31_Mon, 20 Nov 2000 10:00:51 -0500626_us-ascii sibi, are the tables granted "public at all locations" ?







"Billings, Linda" @RYCI.COM> on 11/20/2000 09:57:53 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: Authorization problem

This is just a shot in the dark but are there any changes required for the APPLID in VTAM with the merge to one LPAR? I know an APPLID has to be defined for the DDF address space. Perhaps it is getting hosed there. [...] 16675 54 25_Re: Authorization problem14_Scott Trometer22_scott.trometer@RCI.COM31_Mon, 20 Nov 2000 10:00:34 -0500628_iso-8859-1 It sounds like you 'were' using DB2's DDF via system directed access. This requires some entries in the DB2 Communication Database (V4 or less) or the System catalog communication tables (V5 and up). This is how DB2 knows what the location is when you use 3 part names.

I would check the communication tables to see if you lost a location name or to see if the linkname changed for one of the locations...especially if the standard was previously to name the linkname the same as the location name(LPAR). Check Syslocations,Syslunames, Sysusernames on both subsystems(remove the 'sys' tablename prefix if [...] 16730 61 25_Re: Authorization problem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 20 Nov 2000 09:58:58 -0500342_iso-8859-1 Did two RACF databases get replaced by one? This could be an issue if you are using the sample exit to exploit secondary authorization id processing.

Can you do three-part-name access via an ID which is defined as a SYSADM on the destination system? If not, look for connection (TCP/IP ip address, VTAM APPLID) issues. [...] 16792 105 25_Re: Authorization problem16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 20 Nov 2000 10:10:26 -0500557_us-ascii Sibi,

Glad that worked!

Unfortunately, there is no easy shortcut to the resolution of this issue (i.e., without granting "super" authority, like DBADM to public, at the database level) .

Everytime a table is dropped and created, the GRANTS must be re-issued.

Take care,

Rick Molera

Mainframe DB2 DBA











"Philip, Sibimon" on 11/20/2000 09:53:16 AM

To: RICHARD E MOLERA/SallieMae@SallieMae cc: Subject: RE: Authorization problem [...] 16898 85 25_Re: Authorization problem15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 20 Nov 2000 10:20:03 -0500467_iso-8859-1 Thanks for all the reply.

1. ID with sysadm is able to do a select with 3 part name. 2. When I granted select access to public at all locations, all the user-ids were able to use the 3 part names based on suggestion from the list. 3. We grant the access to RACF group, but I cannot use at all locations with RACF group. So when I remove the public access, nobody is able to select table. 4. This was working when DB2 subsystem was in two LPAR. [...] 16984 189 24_Re: DB2 Storage Problems12_Dallas Focht21_dallas.focht@PMIC.COM31_Mon, 20 Nov 2000 09:30:38 -0600627_us-ascii APAR PQ30892 does require packages to be rebound in order to fix a problem with "storage leaks" in sub-pool 229. Affects long running threads of which our batch thread was one. This was even the case though we were doing frequent commits.

"Pearson, Eric L," wrote:

> What are your bufferpool sizes? eric pearson > ITO DB2 support > Eric.Pearson@nscorp.com > > -----Original Message----- > From: Dallas Focht [mailto:dallas.focht@PMIC.COM] > Sent: Wednesday, November 15, 2000 10:45 AM > To: DB2-L@RYCI.COM > Subject: DB2 Storage Problems > > We are running DB2 Version 6 and there appears to be a > [...] 17174 118 25_Re: Authorization problem15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 20 Nov 2000 09:36:00 -0600530_iso-8859-1 I am puzzled as to why this worked when you had this in two LPARs but not in one LPAR. I would assume that all the DB2 required stuff would not need to be changed in order to bring your system up on a different LPAR, that is, if you have shared DASD. Operating system, security and networking stuff would need to be changed though. I certainly hope that you didn't have to drop and recreate your tables for a mere LPAR switch if that was how you lost your authorities to the tables. Fallback would be a real pain. [...] 17293 19 25_Re: Authorization problem15_Ali Osman ERDEM22_aliosman_erdem@USA.NET29_Mon, 20 Nov 2000 17:27:43 EET507_US-ASCII Please check .DIST resource for READ access for failing auth ids (external security definiton).

____________________________________________________________________ Get free email and a permanent address at http://www.amexmail.com/?A=1

===============================================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. 17313 116 24_Re: DB2 Storage Problems16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 20 Nov 2000 10:35:24 -0500341_iso-8859-1 Wow!

We are going to V6 or V7 soon. I will definitely

make sure this APAR or its V7 version is applied as we

have lots of 'queue processor' type long running started tasks

(all of which use packages). Thanks for the heads up!!!!

Wow!

We are going to V6 or V7 soon. I will definitely [...] 17430 48 50_Daily collection of performance-related statistics14_Spiegel, Barry21_barry.spiegel@EDS.COM31_Mon, 20 Nov 2000 09:33:21 -0600568_iso-8859-1 My platform is DB2 UDB v5.2 (soon to be v6.1) on AIX 4.3.3

My goal is to get snapshots of the database, bufferpool, tablespace and locks for an entire processing day -- to collect a day's worth of statistics.

It seems to me that I should be able to execute snapshots at 23:45 without any additional steps, but during my testing, I see some 'First database connect timestamp' values very close to when I am getting the snapshot, even though there is activity in the database all day long. Here is an example of a recent bit of feedback at [...] 17479 78 68_What exactly is Schema? (WAS : RE: Stored Procedures andPackagesets)13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 20 Nov 2000 09:51:20 -0600534_US-ASCII Kals, To qualify your procedure from VB you would use CURRENTSCHEMA to point to the schema.procname You can use something like this while connecting to your data source. Session("DSNsrc")= "DSN=DSN;CURRENTSCHEMA=XXXXXXX;" HTH Kurt



>>> teldb2kals@TELSTRA.COM 11/19/00 05:55PM >>> Glenn,

We are actually going to V6 in the near future. I was in fact investigating the possibility of Schema for Stored procs, but I dont think I have completely understood what it actually means in terms of DB2/OS390. [...] 17558 72 54_Re: Daily collection of performance-related statistics13_Fletcher, Jim21_jim.fletcher@BSIS.COM31_Mon, 20 Nov 2000 10:51:35 -0500583_iso-8859-1 Barry,

I believe the counters are reset if all connections to the database are ended. I'm guessing at some point during the periods of light activity, there are no connections and the performance data throughout the day is lost. It's probably best to always maintain a "dummy" connection for this type of work. You can also use the ACTIVATE command to keep the database open. I've worked with snapshots and it can be confusing as to which output values are cumulative counters, point-in-time values, or high water marks and to make sense of it all. Good luck. [...] 17631 80 54_Re: Daily collection of performance-related statistics14_Scott Saunders20_ssaunders@SIEBEL.COM31_Mon, 20 Nov 2000 08:01:17 -0800564_iso-8859-1 Barry; That timestamp probably reflects your connection to take the snapshot. The other information, sorts, bufferpools and so forth will be accumulated from when you turned the monitors on. If you disconnect, they will be shut off again (unless set at the instance level, of course). So, if you sign on , set the monitors on and take a snapshot, you will not capture much useful information. If you leave the monitors on and do not log off, you will accumulate more activity or turn them on at the instance level, you will get better information. [...] 17712 231 72_Re: Performance problem - DB2 for OS390 V5 - high commits, unit switch h0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 20 Nov 2000 10:15:41 -0600615_us-ascii The other frequent commits are because they are continuing the logic of the VSAM programs they are replacing, committing after every update, Question, do you mean that they are doing a commit after every insert/update/delete statement, just at the end of each logical unit of work (i.e. transaction)? I know this may sound like a 'dumb question', but I'm asking because you may be talking about a 'dumb conversion' (i.e. fast, simple, and (allegedly) cheap' minimum manual effort' conversion). We survived a VSAM (well it was a VSAM like DBMS) to DB2 conversion, and it was PAINFUL! [...] 17944 93 18_Re: unit test data12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 20 Nov 2000 09:19:16 -0700543_ISO-8859-1 Dave- It's been a while since I've been involved with the said database I was referring too. Please bear with me while I dust off the cob webs regarding my experience with supporting other applications (I've been on the peoplesoft tour of duty for the past couple of years)

Now that I think of it- that's a good question. I have to say I'm not exactly sure why this was done this way. (Lame I know) I've asked my counterparts why the process was set up that way- and they said that they didn't want to deal with the ri. [...] 18038 107 54_Re: Daily collection of performance-related statistics0_29_Joseph_Esposito@PUTNAMINV.COM31_Mon, 20 Nov 2000 11:18:32 -0500753_us-ascii Database Guys have tools that will produce the reports that you are looking for and much more.

http://www.database-guys.com/









Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: Daily collection of performance-related statistics



Barry; That timestamp probably reflects your connection to take the snapshot. The other information, sorts, bufferpools and so forth will be accumulated from when you turned the monitors on. If you disconnect, they will be shut off again (unless set at the instance level, of course). So, if you sign on , set the monitors [...] 18146 46 73_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witch h15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Mon, 20 Nov 2000 10:27:09 -0600408_iso-8859-1 FYI there are third party vendor products that let you control the 'actual' commit frequency regardless of how the commits are issued. They will also do the correct restart for you among other things.

Andy Lankester BMC Software Ltd

One such is APPLICATION RESTART CONTROL for DB2 for OS/390 from BMC Software. http://www.bmc.com [...] 18193 25 38_Re: DB2Connect and MSAccess/Outer Join12_Jason Hughes20_jason.hughes@TRW.COM31_Mon, 20 Nov 2000 10:48:58 -0600502_- Colin, I have used DB2 Connect 5.2 in your situation so I'll answer from that point of view. The PATCH2=19 does work against DB2/OS390 v 5.1 with one exception. I had to have FixPak 9 (I believe) for DB2 Connect installed for the PATCH2 setting to work properly. According to whoever I talked to at IBM nearly 2 years ago, this fix had been put in place for V4.1 but never made it into the early releases of Connect V 5.2. (Or something along that line was said, since it has been so long ago). [...] 18219 18 22_We need your opinion!!8_ecsurvey25_ecsurvey@MAIL.BADM.SC.EDU31_Mon, 20 Nov 2000 11:57:38 -0500537_us-ascii WIN! Valuable Package of Office Supplies by participating in our brief survey. We are conducting a study on Internet purchasing and would appreciate your participation. The survey will only require about 15 minutes of your time. In return, you will be entered into a drawing for a package of office supplies valued at $150. Additionally, we are offering a summary of our findings to those who are interested. Click on the link below to begin the survey. You must be 18 years old to participate. One survey per participant. [...] 18238 30 72_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witc h19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Mon, 20 Nov 2000 09:13:46 -0800481_- Taking a different angle on this:

You say this is starting with empty tables.

Does the application do any pre-insert data validation (i.e. check for duplicate key) or updating of existing data as part of the process.

I've run into a similar case before where the performance was greatly affected by simply breaking up the conversion process, starting with some very small chunks, and doing RUNSTATS and REBIND in between to bring the indexes into play. [...] 18269 66 54_Re: Daily collection of performance-related statistics11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Mon, 20 Nov 2000 17:35:46 +0000498_us-ascii Barry,

I agree principly with the information already supplied about activating the database - however that will then be your first connection timestamp. It is worth activating to avoid the memory allocation etc.

One thing that is unclear, but was suggested, was that it might be your own connection. You do not need to be connected to the database to take a snapshot - so trying it without connecting to the database might give you a clearer idea on what is happening. [...] 18336 36 18_QMF Administration14_Karthik Ganesh21_karthik_gus@YAHOO.COM31_Mon, 20 Nov 2000 09:38:21 -0800545_us-ascii List,

I have a problem that has been raised by systems people which pointed me to the direction of the QMF adminsitration. Here in my shop, the QMF activity is more and users uses the QMF to save data( Mllions of rows sometimes). This creates the problem with the EDM pool siince it is growing large each day because the users do not erase the data. I would like get some suggestions to maintain the QMF efficiently at the same time the users are affected minimally. So that I do not run into these kind of system problems. [...] 18373 70 22_Re: QMF Administration15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 20 Nov 2000 11:58:59 -0600529_iso-8859-1 One thing to do is to give each user their own database to create their tables in. You must have one general database for all of your QMF users. The DBD is getting larger and larger so it fills up the EDMPOOL. No one ever wants to admit that they aren't using any data or they have forgotten that they have saved data so getting them to delete anything is next to impossible. You might also try sending out a message to your user community that says, "We will be deleting your data on this date unless you have a [...] 18444 69 26_QMF Administration (Reply)14_Mike Ockenfels28_michael.d.ockenfels@WCOM.COM31_Mon, 20 Nov 2000 12:01:33 -0600424_us-ascii We have a process where we (in batch) do listcats, examine the last used date and if the table has not been referenced in the past 7 days, we drop it.

Mike Ockenfels MCI-Worldcom







Karthik Ganesh To: DB2-L@RYCI.COM Ockenfels/CDR/BSM/MCI) Subject: QMF Administration 11/20/2000 11:38 AM Please respond to DB2 Data Base Discussion List [...] 18514 116 30_Re: QMF Administration (Reply)0_22_BILL_GALLAGHER@PHL.COM31_Mon, 20 Nov 2000 13:12:30 -0500534_us-ascii We do something similar in that we have a "sandbox" database in which users can create their own tables. There are four tablespaces: a 2-day, a 7-day, a 1-month, and a 3-month tablespace, which users can choose to store their tables in. We have an automated process which runs every night which will delete all "expired" tables (based on SYSIBM.SYSTABLES.CREATEDTS), no matter when they were last used. However, for the 2-day tablespace, it takes weekends into account so that the 2-day tablespace is really 2 weekdays. [...] 18631 14 28_AS/400 to DB2 MVS Conversion0_20_bjnigh@HOUSEHOLD.COM31_Mon, 20 Nov 2000 10:16:53 -0800394_us-ascii Has anyone ever migrated data/ddl from the AS/400 to DB2 on MVS before? If so, can you give me some suggestions on how to accomplish this task?

================================================ 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. 18646 192 61_Re: Calling Ex Cobol programmers - how to flush BSAM buff ers13_Zessin, Peter25_Peter.Zessin@ATCOITEK.COM31_Mon, 20 Nov 2000 12:01:20 -0700613_iso-8859-1 We are using Global Temporary Tables to deal with this.

Regards, Peter



-----Original Message----- From: Stewart S Smith [SMTP:stewart_s_smith@STANDARDLIFE.COM] Sent: 20 November, 2000 5:48 AM To: DB2-L@RYCI.COM Subject: Fw: [DB2-L] Calling Ex Cobol programmers - how to flush BSAM buff ers

Thanks for the replies folks

Sounds like there's some scope in area of writing dummy records and unblocking the sequential file and then reworking the output file to removed dupes. I didnt quite follow in the case of the attached how the switch is actually made to QSAM. [...] 18839 56 18_Error during Reorg15_Enrique Escobar17_enesc@HOTMAIL.COM29_Mon, 20 Nov 2000 19:27:57 GMT493_- Hi All,

Currently, we are executing some reorgs in a weekly basis and we are getting an error with the following message: Unsucessfull sort 106 S reason = 00000028 Error during getmain sys code = 878-10

I know this error belongs to the Operating System caused because Insufficient LSQA was available.

Here are the conditions used to execute this REORG job: 1) Using SORTDATA and SORTKEYS 2) Not using any explicit sort work area (SORTWK01.... SORTWKNN) in the jcl. [...] 18896 79 22_Re: Error during Reorg16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 20 Nov 2000 14:36:35 -0500440_iso-8859-1 Enrique, Is it still failing with 878? If so, try *reducing* the region size a bit. This will allow some of the storage currently allocated to the private area to be available for LSQA.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Enrique Escobar [mailto:enesc@HOTMAIL.COM] Sent: Monday, November 20, 2000 2:28 PM To: DB2-L@RYCI.COM Subject: Error during Reorg [...] 18976 117 22_Re: QMF Administration13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Mon, 20 Nov 2000 14:06:27 -0600530_us-ascii Karthik,

We have a policy that data is not to be saved for more than 7 days. That gives the user a long enough time to finish their sequence of queries. If they need longer storage, the user can export their table. Then we execute the follow jcl weekly: It copies the tablespace and runs a batch QMF proc to product a file to drop the old tables and prints the output to a dataset. The dataset is edited to remove the first line (with the 1 in column 1 for the carriage control), The drops in the dataset are [...] 19094 15 30_s0c7 when selecting from table0_27_Bud.Greenman@ONONDAGA.NY.US31_Mon, 20 Nov 2000 15:27:22 -0500492_- I have been getting an s0c7 abend in spufi when trying to select from a particular table. The select statement is a simple (select col1 from table) query and there are only 23 rows in the table. The user is getting a similar error in cics. Help TIA

================================================ 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. 19110 42 34_Re: s0c7 when selecting from table12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 20 Nov 2000 13:33:55 -0700434_ISO-8859-1 Bud- can you please provide additional information- like what version of db2, are you experiencing the soc7 in one of the db2 modules and if so which one? Are there any corresponding messages in the mstr log? Also any additional info such as has is this table in test/production and has the structure changed recently or data been loaded to it? And if yes it was loaded by what method (i.e. load utility or dsn1copy)? [...] 19153 10 34_Re: s0c7 when selecting from table0_27_Bud.Greenman@ONONDAGA.NY.US31_Mon, 20 Nov 2000 15:38:31 -0500286_- Yes it is V6 - do you have the ptf for it?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19164 15 34_Re: s0c7 when selecting from table0_27_Bud.Greenman@ONONDAGA.NY.US31_Mon, 20 Nov 2000 15:46:48 -0500349_- 1) we are on v6.1 2) it is abending in an application program as far as we can tell 3) The message just says dns3201i abnormal eot in progress 4) This is working in test (also v6) but not in prod 5) The data was recently added to the file using unloaded (external) files from the test table then the load utility (in v4 at the time) on prod. [...] 19180 81 34_Re: s0c7 when selecting from table12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 20 Nov 2000 13:55:41 -0700537_ISO-8859-1 Bud- we encountered a data exception- here's what our systems programmer found- don't think it's available yet- check to see if this applies to you-



Item PQ32357 Bottom of Form 1 Top of Form 2 <<...>> <<...>> <<...>> <<...>> APAR Identifier ...... PQ32357 <<...>> Last Changed..00/07/14 <<...>> INCORROUT AFTER DOING AN 'ALTER TABLE ADD COLUMN' TO A TABLE WHICH IS USED IN A VIEW. [...] 19262 16 13_Re: RI puzzle12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 20 Nov 2000 15:06:32 -0600419_iso-8859-1 Our sincerest Thanks to Shan and Lockwood for their help and advice with the RI puzzle, looks like we'll be happy to see Version 7 come out!

Best Regards, john

================================================ 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. 19279 53 16_Re: Online Reorg11_Hans Selman26_hans.selman@NL.ABNAMRO.COM31_Mon, 20 Nov 2000 16:03:06 -0600648_- On Thu, 16 Nov 2000 15:35:13 -0000, Rasmussen, Steen wrote:

>I have a question regarding Online Reorg which bothers me a bit. >DB2 chooses to do a parallel build of INDEXES - but how is the number of tasks determined. The attached example shows 21 - but I have also had examples where the number of tasks were set to 96. > >DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = STEEN.TESTREOA >DSNUGUTC - REORG TABLESPACE A042X000.XSAP SHRLEVEL CHANGE STATISTICS MAPPINGTABL >E TESTREO.TESTREO >SORTDEVT 3390 SORTNUM 20 COPYDDN(COPYA001) > >DSNUGSRT - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=4487458 FOR [...] 19333 29 15_Hiperpool Size.12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Mon, 20 Nov 2000 22:07:44 -0600509_- Hi Guys:





I have a quick inquire. I was told by someone that I should define the hiperpool size as double of my bufferpools. (if bp1=10000 hiperpool should be hpool=20000). I don't remember exactly the reason why but I think that it should because HIPERPOOL PAGES are placed in Expanded Storage and actually more of new CMOS machine has a lot of it which is more cheaper than Central Storage. I know there must be a lot diferents reasons that I would like to hear. Thanks a lot , [...] 19363 80 73_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witch h15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 21 Nov 2000 05:20:45 +0000331_us-ascii Andy,

How 'regardless of how the commits are issued' would that be? I would most certainly hope that the software would retain the boundary of a 'business transaction'. In my opinion it would be unwise to just commit at random points in processing. I would certainly be very cautious with something like this. [...] 19444 14 33_John Ordman is out of the office.0_29_John_Ordman@READERSDIGEST.COM31_Tue, 21 Nov 2000 01:00:38 -0500365_us-ascii I will be out of the office from 11/20/2000 until 11/22/2000.

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. 19459 14 0_0_25_jwstimmel@DUKE-ENERGY.COM31_Tue, 21 Nov 2000 01:01:42 -0500389_us-ascii I will be out of the office starting 11/20/2000 and will not return until 11/27/2000.

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. 19474 39 22_DSN1LOGP UTILITY usage12_Marcus Green18_mgreen@AAPT.COM.AU31_Tue, 21 Nov 2000 16:29:53 +1100399_US-ASCII Hi,

I'm trying to run a DSN1LOG job to find what may have modified a table between two certain days (about 5 days apart). The table in question has VERY limited updates against it, so I thought I might be OK attempting this.

I've specified in the SYSIN parms the STARTRBA, ENDRBA, DATAONLY(YES) SUMMARY(NO) DBID(xxxx) OBID(xxxx) and pointed it to where the bootstrap is. [...] 19514 86 45_=?ISO-8859-7?Q?=C1=D0=3A_Error_during_Reorg?=19_Alekos Papadopoulos13_apapad@NBG.GR31_Tue, 21 Nov 2000 09:03:55 +0200547_ISO-8859-7 Add a REGION=8M in your job card and try again

Alekos Papadopoulos M.Sc. System DB Administrator National Bank of Greece



-----Αρχικό μήνυμα----- Από: Enrique Escobar [mailto:enesc@HOTMAIL.COM] Αποστολή: Δευτέρα, 20 Νοεμβρίου 2000 9:28 μμ Προς: DB2-L@RYCI.COM Θέμα: Error during Reorg



Hi All,

Currently, we are executing some reorgs in a weekly basis and we are getting an error with the following message: Unsucessfull sort 106 S reason = 00000028 Error during getmain sys code = 878-10 [...] 19601 76 26_Re: DSN1LOGP UTILITY usage14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 21 Nov 2000 17:51:26 +1100391_iso-8859-1 Marcus,

DSN1LOG is called a 'stand-alone' utility because it _doesn't_ use any DB2 resources, eg SYSLGRNX - which is what I think you are thinking of when you wrote "[d]oesn't the bootstrap have the info in it to know which tables are modified within each archive log". The BSDS is used directly (not through db2) to find the names and RBA ranges of the archive logs. [...] 19678 66 26_Re: DSN1LOGP UTILITY usage15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 21 Nov 2000 07:51:21 +0000321_us-ascii Hello Marcus,

No, it's SYSLGRNX that keeps the information about when a pageset was open for update. If you really want to search such a large interval you can run a report recovery against the pageset. That will report the logranges. Use that output to run the DSN1LOGP jobs with smaller intervals. [...] 19745 14 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM31_Tue, 21 Nov 2000 01:45:50 -0500365_us-ascii I will be out of the office from 11/18/2000 until 11/27/2000.

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. 19760 21 18_recover tablespace12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Tue, 21 Nov 2000 07:03:47 -0000608_- All, When a tablespace is in recovery pending status due to space problem what can be done to restore that without starting the tablespace in access(force) . Thanx in advance.

With regards rakesh _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

================================================ 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. 19782 21 23_Replication Performance19_Margaritis Dimitris20_margardi@NOVABANK.GR31_Tue, 21 Nov 2000 09:26:42 +0200294_iso-8859-7 Hi all, Which are the most important parameters that affect performance of replication between two DB2 on NT on a LAN? Is there a parameter that affects APPLY Cycle? I want the lag between the primary and secontary database to be <1 minute. Has anyone any ideas how to test it? [...] 19804 19 22_Re: Error during Reorg10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 21 Nov 2000 08:29:04 +0100401_us-ascii Hi Enrique.

Is it a 3rd party reorg or standard IBM ? Are you using a 3rd party SORT program (like SYNCSORT) ?

Regards

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. 19824 71 19_Re: Hiperpool Size.16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Tue, 21 Nov 2000 12:55:01 +0530552_- Hi Harbry, I am not sure if it is exactly double but the intention is to keep as much as pages transferred from BP to HP so that read from disk can be minimized as HP to BP transfer is of very little cost than disk to BP.Most importantly frequently sequentially read pages which are in chunk and accessed frequently say a BP associated with the sequentially scanned tablespaces and a HP associated with that.So, instead of reading it again from the disk , keeping in HP is better.Similarly for the random pages as well.It is the matter of heavy [...] 19896 100 73_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witch h15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Tue, 21 Nov 2000 02:26:08 -0600484_iso-8859-1 There are several mechanisms, but the simplest is just to say 'only do a real commit on every nth commit issued by the program'. This is safe in that by definition it must be on a transaction boundary as understood by the program.

Andy Lankester

-----Original Message----- From: Toine Michielse [mailto:vndobtm@US.IBM.COM] Sent: 21 November 2000 05:21 To: DB2-L@RYCI.COM Subject: Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witch h [...] 19997 107 74_Re: What exactly is Schema? (WAS : RE: Stored Procedures and Pa ckagesets)10_teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 21 Nov 2000 18:30:41 +1000560_ISO-8859-1 Thanx Glen, I think that sort of clears things up a bit for me, atleast for now. And since we will be moving over to V6 shortly, I will just wait, and get back with more questions later if any.

Kals

>===== Original Message From DB2 Data Base Discussion List ===== >Kals, > >I am not on V6 yet, so my information is from reading, not actual >experience. > >I view the schema name similar to the authid or a qualifier, which you can >set via the currentSQLID if you do not explicitly specify it when calling. > >It [...] 20105 40 32_DB2 UDB for NT Control Center V510_teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 21 Nov 2000 18:41:05 +1000594_ISO-8859-1 Hello everybody,

We are currently trying to use Control Center on Db2 UDB V5 to access OS/390 (V5) databases. But the tree structure doesnt display the tables on OS390, saying " SYSCAT.TABLES not found". I am though able to issue direct select from the Command Center from the OS390 tables.

I was going thru the archives for solutions, and I did find reference to fix "390 Enablement FMID JDB551D" which wd install some stored procedures and create some views under SYSCAT on the mainframe. I also found that these FMIDs exist both for DB2 UDB for OS390 V5 and V6 [...] 20146 54 22_Re: recover tablespace16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM31_Tue, 21 Nov 2000 13:06:02 +0530611_- Hi Rakesh, Run RECOVER utility depending on the kind of Image copy availability and point where you want to store to.If you do not have image copy then i think you are having some bad times.



Regards

Sanjeev

> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Tuesday, November 21, 2000 12:34 PM > To: DB2-L@RYCI.COM > Subject: recover tablespace > > All, > When a tablespace is in recovery pending status due to space problem > what > can be done to restore that without starting the tablespace in > access(force) > . > Thanx in advance. > [...] 20201 55 19_Re: Hiperpool Size.15_Ali Osman ERDEM22_aliosman_erdem@USA.NET29_Tue, 21 Nov 2000 12:44:43 EET363_US-ASCII Hello Harbry,

I think that main reason is MVS addressing limit. Memory is addressable up to 2GB (since 31 bit adrressing), therefore real memory is sharable for all address spaces (MVS,VTAM,TX Managers,Database Managers etc.) Also system paging is a very critical performance issue, for this reason, real memory allocation is very important. [...] 20257 135 34_Rejected posting to DB2-L@RYCI.COM0_20_vpacheco@AMADEUS.NET31_Tue, 21 Nov 2000 12:00:30 +0100541_us-ascii Good point here, but I do not still do not know how to do it. On DSN1COPY we can perform OBIDXLAT. With REPAIR we can patch datapages, but on my DBA DB2 initial training, the recommendation was to rip off these pages out of the manuals ;-)

So I believe the right process is the following: 0. Stop DB2; 1. Dump the Catalog, Directory, BSDS and Active Logs Volumes; 2. Perform a cold start of DB2 with an RBA previous to the DROP; 3. Recover the Catalog; 4. Recover the Lost TS's; 5. Unload the Data to a sequential file; [...] 20393 30 20_Parallel Development19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 21 Nov 2000 06:28:01 -0600416_ISO-8859-1 Hi all and TIA, Environment: DB2 OS390 V6, OS390 V2.8, COBOL II, batch. I need to set up for parallel development against two different structures of the same database, for the same application (using same program names), on the same DB2 subsystem. (Please don't ask why, lol.) Other than using SET CURRENT PACKAGESET to point at the two different structures (via QUALIFIER), is there an easier way? [...] 20424 31 34_Re: s0c7 when selecting from table12_rl_cotterill24_rl_cotterill@BIGPOND.COM31_Tue, 21 Nov 2000 23:39:35 +1100580_us-ascii Bud, We have managed to do this after loading the table with a non-IBM product. To remove it we were able to just do an UPDATE on the column in question (normally the one defined as DECIMAL).

Not sure if your problems but worth mentioning I hope.

Richard

Bud.Greenman@ONONDAGA.NY.US wrote:

> I have been getting an s0c7 abend in spufi when trying to select from a > particular table. The select statement is a simple (select col1 from table) > query and there are only 23 rows in the table. The user is getting a similar > error in cics. [...] 20456 73 24_Re: Parallel Development0_24_Ray.Price@DRESDNERKB.COM31_Tue, 21 Nov 2000 12:34:14 -0000702_- How about different plan names with different PKLIST?

Have a nice day.

Ray Price DBA Dresdner Kleinwort Benson London

-----Original Message----- From: DAVIS, RICK (SBCSI) [SMTP:RD8246@MOMAIL.SBC.COM] Sent: Tuesday, November 21, 2000 12:28 PM To: DB2-L@RYCI.COM Subject: Parallel Development

Hi all and TIA, Environment: DB2 OS390 V6, OS390 V2.8, COBOL II, batch. I need to set up for parallel development against two different structures of the same database, for the same application (using same program names), on the same DB2 subsystem. (Please don't ask why, lol.) Other than using SET CURRENT PACKAGESET to point at the two different structures (via QUALIFIER), [...] 20530 61 24_Re: Parallel Development0_19_mike.holmans@BT.COM31_Tue, 21 Nov 2000 12:46:22 -0000580_- I'll admit to reading Rick's question and thinking it was akin to asking whether there was an easier way of backing up a database than taking image copies, but then it's always worth asking whether the bleeding obvious really is the bleeding obvious.

Managing four different levels of schema across 72 database images under one subsystem on a very large project development was a doddle for us, using the SET CURRENT PACKAGESET method. If we had to do something similar in production, which is what this looks like, I'm pretty sure I'd choose the same method again. [...] 20592 70 24_Re: Parallel Development19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 21 Nov 2000 07:05:03 -0600612_ISO-8859-1 I think that would work, thank you!

Regards, 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." [...] 20663 36 24_Version 6 LOBS and COBOL11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US31_Tue, 21 Nov 2000 07:38:41 -0600579_- Greetings Listers:

One of our technical support personnel recently attended a seminar and was told that the COBOL precompiler under Version 6 creates a lot of code in COBOL programs to accomodate the LOBS. This is alleged to be true even if the LOBS are not being used or referenced in the program. This allegedly causes application programs to run unacceptably slower in production. We are in the process of beginning to migrate to Version 6 and would like to know if anyone else has encountered this problem. As we have a lot of COBOL applications we do not want [...] 20700 46 40_Performance Degradation !!! Reorganize ?17_Pantazis Pantazis20_zakis@CYTANET.COM.CY31_Tue, 21 Nov 2000 03:46:17 +0200426_iso-8859-1 Hi,

Lately we are facing some serious performance degradation which could be attributed to the fact that no Reorganization exists at the current time for any database.

Our installation is DB2 V4 for OS390 and very soon to be DB2 V6 for OS390.

My question is about the volume of free space that will be needed so as to reorganize the whole DB2 weekly. Currently we have about 100GB of DB2. [...] 20747 42 19_Re: Hiperpool Size.14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 21 Nov 2000 09:18:07 -0500597_ISO-8859-1 Generally, a starting point (rule of thumb) is to define a HP twice the size of the VP..... and tune form there. This approach may also include reducing the size of the VP, as long as you can maintain similar or better performance when using the HP.

Regards. Joel +++++++++++++++++++++++

Message text written by DB2 Data Base Discussion List > I have a quick inquire. I was told by someone that I should define the hiperpool size as double of my bufferpools. (if bp1=10000 hiperpool should be hpool=20000). I don't remember exactly the reason why but I think that it [...] 20790 108 34_Re: s0c7 when selecting from table19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US29_Tue, 21 Nov 2000 09:15:43 EST432_us-ascii Hi,

Is this a great list or what? Just since yesterday, we have been getting a S0C4 on a Select on a single table with the following message in the system log:

DUMP TITLE=DB2T,ABND=0C4-00000010,U=HSSCD02 ,C=XYR00.510.RDS -S QL ,M=DSNTFRCV,LOC=DSNXGRDS.DSNXOGBM+020A

I have passed the APAR number to our tech support. Hopefully that will help speed up the response. Looks like it might be related. [...] 20899 75 28_Re: Version 6 LOBS and COBOL20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 21 Nov 2000 09:34:38 -0500432_us-ascii Hi Tony

We had the same fear and I have done some bench-mark on this. The pre-compiler adds code to working-storage as well as procedure division.

The increase in the working-storage size depends on the number of host-variables used in such program and also the increase in procedure division code, which is mostly calls to DSNHADDR, is directly related to number of host-variables the program is using. [...] 20975 37 36_Re: DB2 UDB for NT Control Center V513_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 21 Nov 2000 07:52:28 -0500517_iso-8859-1 Hi Kals, I find this very interesting. I've never seen this with the control center, but I've seen other products looking for SYSCAT.TABLES and SYSCAT.COLUMNS. I have remedied the other products by creating these 2 views. I got the impression that SYSCAT views are used on DB2 NT (& other platforms) for the information we on OS/390 get from SYSIBM.SYSCOLUMNS and SYSIBM.SYSTABLES. I think I remember getting os/390 lists of tables from the control center. (But I think I remember a lot of things...) [...] 21013 80 19_Re: Hiperpool Size.13_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK31_Tue, 21 Nov 2000 14:43:02 -0000453_iso-8859-1 Folks if you are on CMOS boxes then it depends on how your sysprogs define storage. If they give you lots of expanded then use HPs but, why define lots of Expanded Storage when you can have lots of Real Storage or Customer Storage as it is now called? Our sysprogs keep a little, 256M, out of 2G i.e. 1792M Central Storage for whatever needs it. I personally only use HPs for the sort bufferpool and then only a small pool just in case. [...] 21094 33 19_Re: Hiperpool Size.0_26_truman.g.brown@VERIZON.COM31_Tue, 21 Nov 2000 09:43:31 -0500441_us-ascii This is another "it depends" issue. The assumption is that central storage is exhausted for normal VBP expansion and a HP must be used instead.

What is the current VBP efficiency and what is the GETPAGE mix? If it's mostly lots of prefetch with few or no rehits on prefetched pages after they're no longer "in use", an HP may not do much good unless it can contain most of the pages of all tables allocated to that VBP. [...] 21128 145 30_Re: QMF Administration (Reply)12_Lisle, Kathy29_Kathy.Lisle@GMACINSURANCE.COM31_Tue, 21 Nov 2000 09:44:37 -0500409_iso-8859-1 I notice that your original note mentions problems with the EDM pool. QMF SAVE tablespaces are infamous for large numbers of dropped tables that increase the size of the DBD (and so fill up the EDM Pool). At my former place of employment, we had a regularly scheduled job to Reorg, Modify Recovery Delete Age(*), and Image Copy on all tablespaces that held QMF SAVE tables. It worked wonders. [...] 21274 33 10_RC4 in R150_19_mike.holmans@BT.COM31_Tue, 21 Nov 2000 13:47:07 -0000573_- It seems that LE propagates return codes in a different way to COBOL II.

Since we moved over from COBOL II to LE, we've had some funnies in one of our applications. It does some sophisticated error-handling, but it's now getting confused because of different behaviour.

We have detected that when a deadlock occurs, a value of 4 appears in R15 on return from DSNCLI and DSNELI. It didn't do this before, and we think it's because LE propagates return codes whereas the previous regime wiped them out (like the difference betwee IKJEFT01 and IKJEFT1B). [...] 21308 55 32_Re: AS/400 to DB2 MVS Conversion12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 21 Nov 2000 07:32:19 -0800280_us-ascii I migrated a set of tables from an AS/400 to DB2 on OS/390 earlier this year. I basically took the listing of the table definitions and created the corresponding DDL on DB2/MVS. Then we wrote the data out via a quick copy program and used DB2 LOAD to load the data. [...] 21364 54 34_Re: s0c7 when selecting from table0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Tue, 21 Nov 2000 09:08:19 -0600297_us-ascii Interestingly enough we experienced an OC7 last night in Production on a plan that also working fine in test. We are also V6. What finally worked for us was to recompile the failing package and bind it and everything worked just fine. No changes had been made to any of the packags. [...] 21419 75 36_Re: DB2 UDB for NT Control Center V514_Philip Gunning18_pgunning@PHEAA.ORG31_Tue, 21 Nov 2000 10:09:23 -0500329_us-ascii I missed the beginning of this thread, but access to OS/390 objects from the Control Center did not become available until DB2 Connect V6.1 with appropriate Management Tools package on DB2 for OS/390. I have also seen this problem when trying to connect to a remote database that is not configured properly. --Phil [...] 21495 110 19_Re: Hiperpool Size.11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Nov 2000 09:58:09 -0500289_US-ASCII I didn't think it was a good idea to use HPs for the sort buffer. Due to the fact that a page has to be written to dasd prior to moving to HP. Thus HP's are good for high read/low update/insert tables and indexes.

Dave Nance First Health Services, Corp. (804)527-6841 [...] 21606 21 5_UDF's15_Kuashal Trivedi17_c_triveka@BAM.COM31_Tue, 21 Nov 2000 10:29:53 -0500337_iso-8859-1 Hi All, I would like to know if anyone has used User Defined Functions, in their installation, if so could you share the sample COBOL code with me ( any basic UDF is good enough) , as I am looking for a sample and am not able to find it, I have looked at the IBM website too. please email me offline at c_triveka@bam.com [...] 21628 101 21_Re: Order by Question11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Nov 2000 10:35:49 -0500525_US-ASCII Apparently, there is a problem with the order by clause. IBM has a PTF out there, UQ47603, for a problem(PQ41749) with the order by clause not returning correct order in a merge join. I'll let you all know how this works out.

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

>>> yassin@NETVISION.NET.IL 11/13/00 03:42PM >>> Hi, Looks like an undocumented feature ;-) Can you try doing it as a select from inline-view ? e.g: "select col1,col2,col3... from ( ---your select---) order by col1 " [...] 21730 137 19_Re: Hiperpool Size.0_26_truman.g.brown@VERIZON.COM31_Tue, 21 Nov 2000 10:55:11 -0500368_us-ascii We have several dedicated VBP's for DSNDB07 that make good use of an HP. Again, "it depends"...

I'm not sure what David means by "a page has to be written to DASD prior to moving to HP." I see no evidence to support this in our V5 subsystems.

George







"David Nance" on 11/21/2000 09:58:09 AM [...] 21868 22 19_Re: Hiperpool Size.14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Tue, 21 Nov 2000 09:56:39 -0600602_iso-8859-1 Dave,

apologies if I am misunderstanding your statement. Hiperpools are used to back Virtual Pools and as such do not require the page to be written to DASD. The whole point is to avoid I/O.

Colin

Usual disclaimers apply etc...ad nauseam..bla..blah..

(See also relevant Chapter of the Admin Guide for further explanation)

================================================ 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. 21891 54 32_CA/Platinum Fast Recover problem18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM31_Tue, 21 Nov 2000 10:58:24 -0500598_iso-8859-1 Could use some info, if anyone has any, on the problem we are having with CA/Platinum again.

Our environment: DB2 V5 (put 0002) OS/390 CA/Platinum - P97F

Problem: We were notified via a hiper notification that CA has a problem with their Fast Recover utility. Seems if you run this recover and then use an IBM utility against the object you will get a 'resource unavailable' rc 00C20110. I've tried to create this problem in our test environments but I can't get it to fail. This is quite frustrating but not surprising considered CA says this may be a 'intermittent' [...] 21946 24 19_Re: Hiperpool Size.14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 21 Nov 2000 11:06:51 -0500416_ISO-8859-1 Maybe we're getting bogged down in semantics, but a page cannot be "dirty" (meaning updated and not written out) for it to be moved to a HP..... so all updated pages must be written out before they are eligible to be moved to a HP. Regards, Joel

Message text written by DB2 Data Base Discussion List >I'm not sure what David means by "a page has to be written to DASD prior to moving to HP."< [...] 21971 29 19_Re: Hiperpool Size.14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 21 Nov 2000 11:08:59 -0500266_ISO-8859-1 I somewhat dis-agree. It all depends on your system, but many sites get a good performance gain by using HPs behind the sort pool. You just have to try it and see if it helps. It will save you on the "read" side if pages are in the HP. Regards, Joel [...] 22001 29 29_RESOLVING INDEX DE-CLUSTERING10_Elsie Tama24_elsie.m.tama@VERIZON.COM31_Tue, 21 Nov 2000 11:25:55 -0500358_us-ascii Any suggestion/recommendation will be greatly appreciated.

a)How does one define explicitly defined Custering Index in a Tables.( I mean what is the SYNTAX) ?

b) Is the Load Tables/Data, and Insert Rule the Same?

c) What is a good Archival Policy and how is this implemented as it relates to avoiding INDEX DE-CLUSTERING ? [...] 22031 192 19_Re: Hiperpool Size.11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Nov 2000 11:20:27 -0500589_US-ASCII Here it is from IBM:

V5 Administration Guide

5.4.1.1 Buffer Pools and Hiperpools

Virtual buffer pools hold the most frequently accessed data, while hiperpools serve as a cache for data that is accessed less frequently. When a row of data is needed from a page in a hiperpool, the entire page is read into the corresponding virtual buffer pool. If the row is changed, the page is not written back to the hiperpool until it has been written to DASD: all read and write operations to data in the page, and all DASD I/O operations, take place in the virtual [...] 22224 75 36_Re: CA/Platinum Fast Recover problem9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 21 Nov 2000 09:55:35 -0700383_us-ascii P97F was shipped back in December of 1998. P97G was shipped in May of 1999. The current release in the P97 series is P97H, you are running with code that is old, I would recommend upgrading to the P97H release or the P99C-RML release. The P99 series has the new V6 functionality. The next release in the P99 series is P99D, around mid December if I remember correctly. [...] 22300 215 19_Re: Hiperpool Size.20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 21 Nov 2000 11:55:11 -0500538_iso-8859-1 Dave

The sort pages could be written to DASD not because they have to be written to HP but it is the other way round. They get move to HP after they are written to DASD. Write to HP does not trigger them to be written to DASD.

Here in my shop we started experimenting with putting HP for our SORT pool about 7 months ago and we are happy with it. There are many occasions when DB2 has to re-read these pages, which means that activity on SORT pool is not purely write activity. In all those situations (when [...] 22516 188 28_Degree (1 ) and Degree (any)0_29_WilliamsByron@RUSSELLCORP.COM31_Tue, 21 Nov 2000 11:11:18 -0600491_iso-8859-1 Any information on this? (Fixes, APARS...)

The table JRZ_INV_INTRANSIT is partitioned - If the DEGREE is set to ANY the COUNT( DISTINCT... returns the incorrect value of 3 - if the DEGREE is set to 1 the COUNT( DISTINCT... returns the correct value of 10.

SELECT COUNT( DISTINCT STYLE_CD||COLOR_CD||SIZE_CD ) into :total_count FROM DB2.JRZ_INV_INTRANSIT WHERE MANIFEST_NO = 'COT0310' AND DIVISION_CODE = '01' AND COMPANY_CODE = '01' AND LABEL_STATUS_CD = '260' [...] 22705 20 18_QMF Administration14_Karthik Ganesh21_karthik_gus@YAHOO.COM31_Tue, 21 Nov 2000 08:26:22 -0800465_us-ascii Thanks for all your responses.



Karthik

__________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/

================================================ 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. 22726 78 36_Re: CA/Platinum Fast Recover problem15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Tue, 21 Nov 2000 11:15:24 -0600456_iso-8859-1 Hi Steve,

BMC RECOVER PLUS for DB2 had the same problem. In order to recreate it, you must first have UQ39685 (DB2 V5.1) or UQ39686 (DB2 V6.1) applied. Then you must perform a recovery that shrinks the HURBA of the underlying data set. Voila! Your next attempt to access the table space will generate a 00C20110. A REPAIR LEVELID will make the space accessible after the error is encountered. Check out the IBM APAR's for more info. [...] 22805 17 40_Data compression using DSN8HUFF editproc11_Ajit Behera25_ajit.behera@USA.XEROX.COM31_Tue, 21 Nov 2000 11:03:39 -0600459_- Hi,

I have a question about using DSN8HUFF editproc for data compression. I want to know how good it is and what is the effect on performance if the table is used in an online environment. Thanks,

Ajit

================================================ 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. 22823 82 44_Re: Performance Degradation !!! Reorganize ?19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Tue, 21 Nov 2000 10:13:16 -0800485_- The standard answer of "it depends" applies here.

Presumably you aren't going to be able to run REORG utilities against all tablespaces at one time. If you were to single thread all the utility processing, the largest tablespace (partition) would determine the amount of free space required in your DASD pool. If you are running multiple reorgs, then the amount of free DASD space required would be the sum of the largest tablespaces that would be processed in parallel. [...] 22906 13 23_used vs allocated space11_Trace Jones31_Trace_R_Jones@RELIANTENERGY.COM31_Tue, 21 Nov 2000 12:51:57 -0600380_us-ascii What are my alternatives for getting the used vs allocated space in all my tablespaces for my DB2 database version 6.1 in OS/390?

================================================ 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. 22920 35 27_Re: used vs allocated space16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 21 Nov 2000 13:55:14 -0500536_iso-8859-1 Trace, If your maintenance is up to speed, SUM(HURBA) vs SUM(HARBA) from LISTCAT for the objects should be pretty close.

eric pearson ITO DB2 support Eric.Pearson@nscorp.com



-----Original Message----- From: Trace Jones [mailto:Trace_R_Jones@RELIANTENERGY.COM] Sent: Tuesday, November 21, 2000 1:52 PM To: DB2-L@RYCI.COM Subject: used vs allocated space



What are my alternatives for getting the used vs allocated space in all my tablespaces for my DB2 database version 6.1 in OS/390? [...] 22956 14 55_Re: What are the performance impacts when using BLOBs ?13_Davis, Ronald17_DavisRo@CONED.COM31_Tue, 21 Nov 2000 14:24:26 -0500374_- Can anyone share with me any knowledge they may have regarding performance considerations when using Binary Large Objects (BLOBS).

================================================ 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. 22971 208 32_Re: Degree (1 ) and Degree (any)24_Koleto, Kenneth M. [SLC]21_KKoleto@CSCUS.JNJ.COM31_Tue, 21 Nov 2000 14:43:05 -0500620_- Byron,

We have seen this problem in testing V6. We have not yet received a fix. The work-around is to use degree 1.

Ken

> -----Original Message----- > From: WilliamsByron@RUSSELLCORP.COM [SMTP:WilliamsByron@RUSSELLCORP.COM] > Sent: Tuesday, November 21, 2000 12:11 PM > To: DB2-L@RYCI.COM > Subject: Degree (1 ) and Degree (any) > > Any information on this? (Fixes, APARS...) > > The table JRZ_INV_INTRANSIT is partitioned - If the DEGREE is set to > ANY the COUNT( DISTINCT... returns the > incorrect value of 3 - if the DEGREE is set to 1 the COUNT( DISTINCT... > returns the correct value [...] 23180 15 7_compare12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Tue, 21 Nov 2000 14:50:38 -0500491_us-ascii Hello all, We currently have a compare product that we use to compare Peoplesoft DDL to existing information in a DB2 subsystem. We have been looking for a replacement to this product and are wondering if and what other folks are using. TIA

================================================ 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. 23196 53 19_Re: Hiperpool Size.14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 21 Nov 2000 15:47:43 -0500285_ISO-8859-1 You have a slight mis-interpretation of what the manual says... The pages do not get written out when they are moved to a HP. Pages must be writen out before they are ELIGIBLE to be moved to a HP. They are usually written by hitting either VDWQT or DWQT. Regards, Joel [...] 23250 25 28_Re: Version 6 LOBS and COBOL13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Tue, 21 Nov 2000 14:49:27 -0600431_- Hi Venkat

> > We had the same fear and I have done some bench-mark on this. The >pre-compiler adds code to working-storage as well as procedure division. > >The increase in the working-storage size depends on the number of >host-variables used in such program and also the increase in procedure >division code, which is mostly calls to DSNHADDR, is directly related to >number of host-variables the program is using. [...] 23276 68 33_Re: RESOLVING INDEX DE-CLUSTERING13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 21 Nov 2000 15:57:56 -0500685_iso-8859-1 Hi Elsie, Your questions are mostly answered in the DB2 publications.

If you have wwweb access, see http://www.s390.ibm.com:80/bookmgr-cgi/bookmgr.exe/Shelves/DSNSH0G3 for the Version 6 pubs. Other pubs are viewable if you remove DSNSH0G3 from the above URL.

Meanwhile I'll try answering them.

a. You merely add the word CLUSTER to your create index statement. Here's an example: CREATE UNIQUE INDEX BFHDJS1.STAFFIX ON BFHDJS1.STAFF (ID ASC) GBPCACHE ALL USING STOGROUP STG001 PRIQTY 10 SECQTY 5 ERASE NO FREEPAGE 5 PCTFREE 5 CLUSTER BUFFERPOOL BP0 CLOSE YES; For all the options you'll want to see the SQL Reference (available at the site above). [...] 23345 27 38_Partitioning Challenge, DB2 OS/390 V 616_Jerry Bustamante20_ggbusta@LANDSEND.COM31_Tue, 21 Nov 2000 14:51:09 -0600358_iso-8859-1 Hey Folks,

We have a 34 char(acter), alphanumeric, near random column value we need to try and partition on. I'm looking for an algorithm to apply to this value to calculate a partitioning value. I would prefer a minimum of 50 partitions up to the maximum. Any suggestions?

Thanks,

:-)

Jerry Bustamente Lands' End [...] 23373 70 37_Re: Idle Thread time-out ZPARM value.15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Tue, 21 Nov 2000 13:03:18 -0800560_iso-8859-1 Stephen,

If the query is "running" then the thread would not be considered idle by DB2. So that is not where the timeout is occurring. Your query is likely completing, control goes back to the client, then the client goes idle. It could be your client does not autocommit or explicit commit and then goes to user interface. Not the best design. Or it could be a driver flaw. Bottom line is you need to investigate what's happening on the client side. If you don't you may continue to increase IDTHTOIN and not change the problem at all. [...] 23444 41 55_Re: What are the performance impacts when using BLOBs ?15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 21 Nov 2000 22:47:31 +0000473_us-ascii Hello Ronald,

I've worked with lobs. Can you be a little more specific about the information you need?

Regards,





"Davis, Ronald" @RYCI.COM> on 11/21/2000 07:24:26 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: What are the performance impacts when using BLOBs ? [...] 23486 17 16_GMT Time and DB214_Dean Montevago18_DMonteva@VNSNY.ORG31_Tue, 21 Nov 2000 16:54:03 -0500483_- Hi, I am running GMT=LOCAL right now on my 9672. We are upgrading in a couple of weeks and I would like to change to GMT=GMT, since it will simplify time changes in the spring and fall. Will DB2 be affected by this in anyway ? Thanks, Dean

================================================ 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. 23504 20 26_Debugging Distributed Apps14_Dean Montevago18_DMonteva@VNSNY.ORG31_Tue, 21 Nov 2000 16:57:29 -0500430_- Hi, As I've mentioned in previous threads, we have a web application that goes to DB2 on OS/390 via DB2 Connect. We have intermittent performance problems with the application, it might fine for a week, then it goes in the tiolet, and then gets better. Everything is being done dynamic. Does anyone on the list have any suggestions as to how to gather info, or suggestions on debugging techniques that we can use. TIA Dean [...] 23525 69 42_Re: Partitioning Challenge, DB2 OS/390 V 611_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 21 Nov 2000 15:59:09 -0600454_- Jerry: I am not sure I fully understand your question but I will take a stab at it. I assume you are trying to calculate the limit keys that result in roughly equal-sized parts.

The simplest way I have found is not use sql at all - simply issue select ... from ... in qmf and use the CSUM or CPCT functions. Based on this (and how may parts you need), you decide the limits. e.g. if you wanted 50 parts, you would break at 2,4,6,...98 etc. [...] 23595 136 73_Re: Performance problem - DB2 for OS390 V5 - high commits, unit s witch h15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 21 Nov 2000 23:00:29 +0000521_us-ascii Hello Andy,

Thanks for the reply.

But surely there must be more to the product? Experience shows that applications in the real world commit too infrequently. VERY rarely do I see applications commit more than wanted.

Regards,

Toine Michielse





"Lankester, Andy" @RYCI.COM> on 11/21/2000 08:26:08 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 23732 47 28_Re: Version 6 LOBS and COBOL20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 21 Nov 2000 17:07:37 -0500307_us-ascii Ya... All the stuff is there in V5 but V6 has structure change to SQL-PLIST and also there are tons of additional calls to DSNHADDR (ofcourse it depends how many host variables are used). So much so that it prompts a performance alarm comparing the V5 pre-compile code to V6 pre-compile code. [...] 23780 53 42_Re: Partitioning Challenge, DB2 OS/390 V 615_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 22 Nov 2000 10:04:48 +1100401_- Hi Jerry,

Rather than bust a boll, er, valve working out how to calculate a partitioning value, how about this: as the column value is random, and you want at least 50 partitions, why not just have 72 partitions (two lots of 36) keyed AR, A9, BR, B9... 9R, 99? Of course, it depends on just how random your key is, but so what if you have a few partitions a bit bigger than some others? [...] 23834 41 44_Re: Data compression using DSN8HUFF editproc13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 21 Nov 2000 17:37:49 -0600502_iso-8859-1 I have not a reply to this yet, and I do not know what version you are on or your circumstances, but preferred method of data compression is the "standard" compression which comes with DB2 and invoked via the COMPRESS YES clause in the CREATE TABLESPACE.

hth glenn





-----Original Message----- From: Ajit Behera [mailto:ajit.behera@USA.XEROX.COM] Sent: Tuesday, November 21, 2000 11:04 AM To: DB2-L@RYCI.COM Subject: Data compression using DSN8HUFF editproc [...] 23876 64 30_Re: Debugging Distributed Apps10_Rob Wright17_rwright@LIC.CO.NZ31_Wed, 22 Nov 2000 12:34:28 +1200636_us-ascii Hi Dean,

"We have intermittent performance problems with the application"

Firstly, I would break the problem down into the components of your application:

Web browser Internet Web server LAN/WAN TCPIP or VTAM OS/390 TCPIP or VTAM DB2 Application I/O CPU REAL storage DASD

Next, you have to define "application". Do you mean the application that runs on the OS/390 system, or the web (server?) application, or the web browser? Does the web server issue dynamic SQL, or do you utilise a stored procedure to build and execute the dynamic SQL? Is the dynamic SQL cacheable? ie do you use parameter [...] 23941 110 42_Re: SMS conversion - placement of datasets12_Kirk Hampton16_khampto1@TXU.COM31_Thu, 16 Nov 2000 09:57:41 -0600588_us-ascii Hello Lynda, The former storage management regime here at TXU was bent on merging everything into one SMS storage pool, and managing the exceptions via ACS rules by dataset name. The current manager has backed off from that, having seen unneccesary difficulty at other sites. We currently have at least 8 different SMS storage groups, which may be excessive in the other direction. Our DB2 datasets mostly go to 2 of those pools, but by primary allocation size, <50 meg in one pool, larger datasets in the other, not by whether they are tablespaces or indexes. If I had the [...] 24052 37 11_Re: compare15_Sydney Brereton21_sbrereton@MMAL.COM.AU31_Wed, 22 Nov 2000 13:42:12 +1030500_US-ASCII We use BMC's Change Manager product and find it is good enough for us. It lets you compare against a DDL script and DB2 catalog or between catalogs in different subsystems. If you want more info you can e-mail me direct. Cheers, Syd Brereton MMAL DB2 DBA Support sbrereton@mmal.com.au



______________________________ Reply Separator _________________________________ Subject: compare Author: DB2 Data Base Discussion List at MMALHQ01 Date: 21/11/00 14:50 [...] 24090 62 11_Re: compare11_Phil Castle20_phil@ECNETWORK.CO.NZ31_Wed, 22 Nov 2000 16:53:53 +1300299_iso-8859-1 you could also look at Advanced Query Tool (see www.cardett.co.nz) which has a very easy to use compare function. However it is only catalog-catalog (on different systems) and not DDL-catalog so it may not do exactly what you want. At $50 USD it is a lot cheaper than BMC's product. [...] 24153 58 42_Re: Partitioning Challenge, DB2 OS/390 V 612_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Wed, 22 Nov 2000 16:21:27 +1100416_- Adding to Raymond's note :- If you don't mind carrying the "embryonic" empty partitions around insert empty partitions between each partition (using the strategy that Raymond mentioned). This should make relancing easier when you get to DB2 V7.

It's sort of like using a FREEPAGE parm on your partition, except that you need to rebalance the data into the FREEPART yourself. Just my way of viewing it. [...] 24212 18 12_-ve PAGESAVE0_26_Bruce.Williamson@NT.GOV.AU31_Wed, 22 Nov 2000 15:07:43 +0930491_us-ascii Howzit Folks?

What does a negative value in PAGESAVE indicate? If it indicates that there is no savings due to compression and the volume is unlikely to increase, is this a candidate for switching compression off?

Cheers Bruce

================================================ 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. 24231 50 27_Re: used vs allocated space12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Wed, 22 Nov 2000 16:40:02 +1100532_- Trace, Pretty much as Eric mentions Hi-Used Vrs. Hi Alloc RBAs.

A couple of things to be aware of though is that these values could wildly vary (up or down) after a reorg. Either because of PCTFREE / FREEPAGE parameters or because of reclamation of pseudo-deleted RIDS in type-2 indices.

PS: I have a JCL/REXX job that tallies space used from a ListCat. It also highlights Datasets exceeding threshold extents or unused space. If you need it let me know and I'll e-mail it to you (*Standard disclaimers apply*) [...] 24282 42 16_Re: -ve PAGESAVE15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 22 Nov 2000 16:42:52 +1100382_- Got it in one, Bruce. If Pagesave is negative, the tablespace is bigger compressed than it was uncompressed. So either your data doesn't compress very well, or the size of the compression dictionary is larger than the space you saved by compressing. If the latter's the case, I wouldn't bother digging 'round for the extra few tracks/cyls. But, that's just my opinion... ;o) [...]