1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2000, week 3 2 42 12_Re: SYSLGRNX14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 15 May 2000 10:09:15 +0200447_us-ascii Hi Dan & thx for your posts.

As far as I can remember I was told (during an IBM course) that if you drop a tablespace you

clean all SYSCOPY records and all SYSLGRN(X) records.......but now i'm not so sure.

And you confirmed it in a previous post:

"For as long as I can remember (and I checked code back thru V4) DB2 deletes obsolete SYSLGRNX records when a tablespace is dropped. If it isn't, its a bug." [...] 45 43 31_Re: DB2 Log records recovery !!23_Ravindra Mohan Nautiyal21_RavindraN@INTERRA.COM31_Mon, 15 May 2000 14:39:31 +0530620_iso-8859-1 Edward, I would love to have see these panels. Regards R M Nautiyal. ---------------------------------------------------------------- > -----Original Message----- > From: Edward C. Benoit Jr. [SMTP:DB2Automate@AOL.COM] > Sent: Thursday, May 11, 2000 4:58 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Log records recovery !! > > Hello Vishy, Good news. We have a tool that will generate a summary of > all your recovery points of a Tablespace by Timestamp and RBA. Be it a > Quiese point by RBA, Image copy, or tablespaceset. It will also generate > the > complete Job JCL and Cntl cards by Pick and Chose. [...] 89 43 24_Linklist libraries & DB212_Mike O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV31_Mon, 15 May 2000 06:34:17 -0400408_US-ASCII We have tried to standardize our environment and JCL for DB2 by doing the following: - create a subsystem specific library for each DB2 subsystem - concatenates first in the steplib. - merging the target libraries into a single base library that is in the linklist: DB2.DSN610.SDSNLOAD DB2.DSN610.SDSNEXIT DB2.DSN610.SDSNLINK DB2.DSN610.SDXRRESL DB2.DGO.V6R1M0.SDGOLINK DB2.DGO.V6R1M0.SDGOLOAD [...] 133 16 48_Re: Pager or Email Notification, what's AFOPER ?14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 15 May 2000 12:50:33 +0200336_us-ascii Excuse the OT question, but what is AFOPER ?

THX and 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. 150 16 71_BILL GALLAGHER/Phoenix Home Life Mutual Insurance is out of the office.0_22_BILL_GALLAGHER@PHL.COM31_Mon, 15 May 2000 01:01:45 -0400410_us-ascii I will be out of the office from 05/13/2000 until 05/22/2000.

If you have any DB2 questions or problems, please refer them to Mark Stankiewicz at x2573.

================================================ 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. 167 43 76_Re: BILL GALLAGHER/Phoenix Home Life Mutual Insurance is out of t he office.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 15 May 2000 06:34:52 -0500592_ISO-8859-1 please don't autoreply to db2-l

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." [...] 211 83 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 15 May 2000 06:48:35 -0500422_ISO-8859-1 Hi Jim, I guess you could lump OS390 overhead into the "cost of doing business" bucket and justify a budget thusly. However, and I think this would be of interest you, if I'm running 100+ percent busy quite frequently, and I have a dynamite I/O subsystem like yours, driving fewer I/Os, where I have that control, like with (V)DWQT, I can reduce some overhead and maybe get a little more "real work" done. [...] 295 63 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 15 May 2000 07:11:23 -0500621_ISO-8859-1 Hi Jim, comment below . . .

>-----Original Message----- >From: Jim Lewandowski [mailto:jlewand@STARNETINC.COM] >Sent: Saturday, May 13, 2000 1:14 PM To: DB2-L@RYCI.COM >Subject: Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING > >DAVIS, RICK (SBCSI) wrote: > > Hi Jim, > Thanks for comments. And, as I told Michael, I'm not taking "cheap > shots" at you or anyone else. I, like you, am trying to gain the knowledge I > need to improve DB2 performance. > I guess I still miss your point; its pretty early in the morning for > me. When considering backwrites, why do you feel locality-of-reference moot [...] 359 35 22_Adding archive logs...0_16_ccemilog@YKB.COM31_Mon, 15 May 2000 15:30:11 +0300802_us-ascii Hi everyone.. Has anybody tried the DSNJU003 utility with the NEWLOG option for adding archive logs to the BSDS? I am trying to use the following syntax but getting the same error with all the possible combinations. The thing is:

NEWLOG DSNAME=archivelogname STRTLRSN=B4074A66CEF6,ENDLRSN=B407868B1B52

And it tells me:

NEWLOG DSNAME=archivelogname DSNJ207I DSNJU103 PARAMETERS INCONSISTENT WITH SPECIFIED OPERATION DSNJ221I PREVIOUS ERROR CAUSED NEWLOG OPERATION TO BE BYPASSED STRTLRSN=B4074A66CEF6,ENDLRSN=B407868B1B52 DSNJ203I STRTLRSN OPERATION IS INVALID DSNJ207I DSNJU001 PARAMETERS INCONSISTENT WITH SPECIFIED OPERATION DSNJ221I PREVIOUS ERROR CAUSED STRTLRSN OPERATION TO BE BYPASSED DSNJ201I DSNJU003 CHANGE LOG INVENTORY UTILITY PROCESSING WAS UNSUCCESSFUL [...] 395 77 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 15 May 2000 14:46:12 +0200650_us-ascii "DAVIS, RICK (SBCSI)" il 15/05/2000 14.11.23

Per favore, rispondere per DB2 Data Base Discussion List

Per: DB2-L@RYCI.COM Cc: (ccr: Massimo Scarpa/CESVE)

Oggetto: Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING







******************************************************************** What Joel, and others are saying is that, in general, simply raising (V)DWQT doesn't ensure updated pages per I/O will go up. You'll have to find those datasets with good locality of reference. ********************************************************************** [...] 473 52 26_Re: Adding archive logs...40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM31_Mon, 15 May 2000 07:53:42 -0500577_iso-8859-1 You need a comma after the archivelogname and before the STRTLRSN. It is trying to interpret this as two separate commands instead of one command.

-----Original Message----- From: ccemilog@YKB.COM [mailto:ccemilog@YKB.COM] Sent: Monday, May 15, 2000 7:30 AM To: DB2-L@RYCI.COM Subject: Adding archive logs...



Hi everyone.. Has anybody tried the DSNJU003 utility with the NEWLOG option for adding archive logs to the BSDS? I am trying to use the following syntax but getting the same error with all the possible combinations. The thing is: [...] 526 39 30_WLM Stored procedures DSNTPSMP18_Michael Arlebrandt28_micke_arlebrandt@HOTMAIL.COM30_Mon, 15 May 2000 15:25:46 CEST441_- Hi everybody,

I am implementing WLM stored procedures and are running trough the IBM supplied samples to verify the functions. I have been successful running DSNTEJ63 , DSNTEJ64 and have run the sample PSM proceure DSN8ES1 in WLM environment from a Netdata macro.

Now I want to invoke the OS/390 Procedure Processor DSNTPSMP and have configured a WLM environment for that, I used the sample DSN8WLMP to create the proc. [...] 566 58 26_Re: Adding archive logs...0_19_mike.holmans@BT.COM31_Mon, 15 May 2000 14:02:39 +0100639_- From the manual:

"For new archive logs defined with Change Log Inventory, the user must specify the start/end RBAs and for data sharing, also the start and end LRSNs."

So the LRSNs are optional extras while the RBAs are required.

Mike Holmans BT ISE Technical Design mike.holmans@bt.com > -----Original Message----- > From: ccemilog@YKB.COM [SMTP:ccemilog@YKB.COM] > Sent: 15 May 2000 13:30 > To: DB2-L@RYCI.COM > Subject: Adding archive logs... > > Hi everyone.. > Has anybody tried the DSNJU003 utility with the NEWLOG option for adding > archive logs to the BSDS? I am trying to use the following syntax but [...] 625 28 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 15 May 2000 09:58:54 -0400642_ISO-8859-1 Threoretically, yes. However, real world situations "rarely" prove this. Most objects are just too large and random to have any significant increase in pages/write....... so the threshold almost always belongs low - when the avg pages/write is a single dgit. Joel Goldstein





Message text written by DB2 Data Base Discussion List >The light finally came on. Statistically, the higher the write threshhold, the higher the likelihood that updated pages for a given space (index/table) will be within 150 CIs of each other. Thus, the write channel programs SHOULD have more segments (updated pages) in them. [...] 654 25 48_Re: Pager or Email Notification, what's AFOPER ?14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Mon, 15 May 2000 07:05:03 -0700561_iso-8859-1 Max, I believe AFOPER would be AF/Operator, an Automated Operations package from (as I recall) Candle. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

Excuse the OT question, but what is AFOPER ?

THX and 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. 680 15 48_Re: Pager or Email Notification, what's AFOPER ?14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 15 May 2000 16:22:00 +0200316_us-ascii Hi Wayne, thanks for infos about AFOPER

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. 696 101 37_Re: Craig Mullins DBA Online Magazine15_Andrea Milligan25_Andrea_Milligan@BCBST.COM31_Mon, 15 May 2000 10:28:45 -0400658_- http://www.dbazine.com/

Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: bjnigh@HOUSEHOLD.COM [SMTP:bjnigh@HOUSEHOLD.COM] > Sent: Friday, May 12, 2000 5:29 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Craig Mullins DBA Online Magazine > > Does anyone have the url address for the Craig Mullin DBA magazine? I > accidentally missplaced 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. 798 24 15_Looking for DBA10_Rod Foster19_Rod.Foster@SLMA.COM31_Mon, 15 May 2000 10:39:37 -0400736_us-ascii SallieMae is looking for a Senior DB2 Database Administrator to apply the latest features of mainframe DB2 to its large, high-performance, high-availability, transaction-processing databases. These databases are accessed from applications on CICS, Windows, and Web platforms. We use the latest tools from IBM, Platinum and BMC to roll-out changes, optimize performance, minimize outages, and diagnose problems. SallieMae, located on it's own campus in suburban Reston, VA, provides competitive salaries, technical training, stock options, 401(k), and other professional benefits. If you have five or more years of recent production support, performance analysis, and database design experience as a mainframe DB2 database [...] 823 59 33_Re: Accessing DB2 (OS390) from OE30_CHRISTOPHER.KELLER@Vereinte.de30_CHRISTOPHER.KELLER@VEREINTE.DE31_Mon, 15 May 2000 13:20:00 +0200504_iso-8859-1 --- Received from BG1.KELLERC 6785-2761 15-05-00 13.20 --------------------------------------------------------------------------------------------------------------

-> db2-l@ryci.com

Hello Benjamin, not much of a response, but maybe this will keep the conversation going. In my limited experience it's difficult finding someone who has really done much here, finding IBM Documentation is also difficult, perhaps it's so simple that no one figures it's worth writing about. [...] 883 50 37_Re: Another Archive log size question24_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Mon, 15 May 2000 11:21:03 -0500515_- Bob,

I played with this awhile back and I concluded that 1111 cyl active logs (and 2 cyl BSDS's) worked well. When archived, it comes close to the capacity of our cartridges (800mb rated capacity). This size also allows me to slice up a 3339 cyl dasd vol so that I do not waste dasd space (I put three to a vol); I never extend archives to multiple tapes, which I wanted to avoid; and I use a reasonable amount of every cartridge (roughly 85%). Of course I set it up so no two logs on a given vol are [...] 934 14 23_DB2 SQL reporting tools10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Mon, 15 May 2000 10:21:25 -0700475_iso-8859-1 I would like to know what tools on the mainframe or PC side other people are using to generate adhoc reports for their mainframe DB2 databases besides QMF and EZYTRIEVE. What advantages do these tools have over the others?

================================================ 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. 949 32 22_Global Temporary Table29_=?iso-8859-1?Q?Jos=E9_Moura?=25_jose.antonio.moura@CGD.PT31_Mon, 15 May 2000 18:13:53 +0100395_us-ascii I defined a Global Temporary Table as

CREATE GLOBAL TEMPORARY TABLE PERFIMP (EMPNO CHAR(6) NOT NULL) ;

It was defined and with spufi I used it to

INSERT INTO PERFIMP SELECT EMPNO FROM DSN8510.EMP WHERE WORKDEPT LIKE 'D%' ;

UPDATE DSN8510.EMP SET SALARY = SALARY + 0.9 WHERE EMPNO IN (SELECT EMPNO FROM PERFIMP) ; SELECT EMPNO, SALARY FROM DSN8510.EMP ; [...] 982 98 26_Re: Global Temporary Table0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 May 2000 13:35:57 -0500521_iso-8859-1

I thought that one restriction of Global Temporary tables was that they could not be updated. Did you check the sqlcode after the update? You should have gotten an sqlcode -526.

I hope this helps.

Thanks, Tim













José Moura on 05/15/2000 12:13:53 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM [...] 1081 18 27_Re: DB2 SQL reporting tools0_19_RTurpen@WOODMEN.COM31_Mon, 15 May 2000 13:44:45 -0500527_us-ascii We have been using Cognos Impromptu on Windows 95/98 workstations to read DB2 for OS/390 Version 5.1 tables with DB2 Connect as the middleware. The Impromptu software is easy to implement and very user friendly.

Ray Turpen Systems Technical Support Woodmen of the World

================================================ 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. 1100 17 24_Timestamp in Load Module12_Allen Tsygan18_tsyganaa@AETNA.COM31_Mon, 15 May 2000 13:37:37 -0500530_- Hello to all,

Question came-up regarding the contoken position in the load module. We looked and looked. Does anybody know how to find consistency token and/or precompiler timestamp values in the load module, without knowing what they are from DB2 catalog or DBRM.

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. 1118 86 28_Re: Timestamp in Load Module0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 May 2000 15:24:34 -0500608_us-ascii Allen, Do you have a compile listing that you could look at? If you do: In the Cobol listing, look at the datanames SQL-TIMESTAMP-1, and SQL-TIMESTAMP-2. This should be immediately after the dataname SQL-PROG-NAME. (which contains the program name.) In the listing, the value will be in decimal, you will need to convert each value to hexadecimal, and then concatenate the 2 results together to get the contoken. If you don't: I think that the real challenge is that optimizing compilers put literals like these in a different order than you might expect. However, you might be able to compile [...] 1205 63 17_DB2 Early Code V616_Robert Greenwell19_RLGREENW@ITRWEB.COM31_Mon, 15 May 2000 13:26:00 -0700369_ISO-8859-1 Hello,

The following is from the the V6 installation guide page 66. " If your system is at the prerequisite maintenance level. your version 5 early code is upward compatible with version 6. "



Does anyone know what that prerequisite maintenance level is ? I can not seem to find it in any of the documents related to the install. thanks. 1269 96 37_Re: Another Archive log size question8_Bob Byrd14_BBYRD@PCMH.COM31_Mon, 15 May 2000 16:49:25 -0400514_US-ASCII Hi Dave, Thanks for responding. Here's the confusing part in all this, with a 3490E device, when reading about capacity, a chart is shown, supporting up to 2.4 gb with a 3:1 ratio. Guess thats dependent on what your backing up and it's ability to compress. I've sent a question to ibm about this, should'nt be so unclear. You bring up good points, especially dsnzparm compact=no (we are currently set this way). But my understanding, 3409e is going to compact. Waiting to here back from DB2 support. [...] 1366 20 17_GDG sync question12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 15 May 2000 16:11:03 -0500412_iso-8859-1 Interesting situation here, the ICF catalog and DB2 catalogs are apparently not in sync on GDG's. Someone has apparently deleted several GDG's, and when the job is run that should create the next GDG, it abends because the two catalogs are not in sync. Can someone offer a more detailed explanation (where in the DB2 catalog does this information reside?) and how the situation can be corrected? [...] 1387 28 21_Re: GDG sync question0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 15 May 2000 16:10:55 -0500605_us-ascii If you are talking about Image Copy backups, try in SYSIBM.SYSCOPY.DSNAME





lease respond to DB2 Data Base Discussion List Subject: GDG sync question

Interesting situation here, the ICF catalog and DB2 catalogs are apparently not in sync on GDG's. Someone has apparently deleted several GDG's, and when the job is run that should create the next GDG, it abends because the two catalogs are not in sync. Can someone offer a more detailed explanation (where in the DB2 catalog does this information reside?) and how the situation can be corrected? [...] 1416 52 21_Re: GDG sync question0_19_Mark_Lang@AMWAY.COM31_Mon, 15 May 2000 17:19:49 -0400325_us-ascii you could change your (+1) in the JCL to (+n) to make it n + 1 where n is the most current G000V00 recorded in SYSCOPY for the tbspace(s). This will get you around the 'dataset already used' type error I assume you're getting. Or, if the GDGs deleted are lost, run a MODIFY utility to remove them from SYSCOPY. [...] 1469 48 21_Re: GDG sync question15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Mon, 15 May 2000 17:49:20 -0400554_us-ascii If it is not too many you can create dummy datasets for the generation 1 greater than the max absolute generation that is in SYSIBM.SYSCOPY for that database tablespace (and dsnum if appropriate). Do this with several caveats : Oldest catalogued dataset will be deleted if GDG limit is reached These GDG's will not be known to DB2 so do not try to use them for any DB2 recovery or other processing. Do not create any dummy datasets which match what is in the DB2 catalog and try to use them for DB2 recovery or any other DB2 processing HTH [...] 1518 52 21_Re: GDG sync question15_Murley, Michael22_Michael_Murley@BMC.COM31_Mon, 15 May 2000 16:47:37 -0500539_ISO-8859-1 John,

I'm assuming that the job failed because the copy was trying to generate a copy data set (node.G0001Vnn) that already exists in SYSIBM.SYSCOPY. If that's not your situation, you can ignore the rest of this note.

When I hit this situation , I often just change the copy jobs to create generation (+n), where n is high enough to get past the latest generation registered in SYSIBM.SYSCOPY, but that's only because I'm usually more concerned with my immediate testing that the recoverability of the space. [...] 1571 110 21_Re: GDG sync question0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 May 2000 17:09:06 -0500455_us-ascii John, Could this have been caused by an image copy job that copied multiple tablespaces in a single step, used DISP=(NEW,CATLG,DELETE), AND abended in that step AFTER one of the copies completed? (see SYSIBM.SYSCOPY)

IF this is the case, then perhaps the permanent solution to avoid this problem is to change to DISP=(NEW,CATLG,CATLG). OR, if your copies are to disk, then you might want to change to doing a single copy per jobstep. [...] 1682 44 21_Re: GDG sync question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 16 May 2000 13:21:29 +1000567_iso-8859-1 This happened to us last year after the CA-1's default retention period was changed to 7 days (mutter mutter) and some offsite image copies got uncatalogued before the next set were created.

I would have a carefull look at who/what uncatalogued them.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Vernon, John [mailto:John.Vernon@TRS.STATE.TX.US] Sent: Tuesday, May 16, 2000 7:11 AM To: DB2-L@RYCI.COM Subject: GDG sync question [...] 1727 38 18_DBA's Needed in NJ13_Al DiGiovanni13_al-d@HOME.COM31_Mon, 15 May 2000 23:21:50 -0400754_us-ascii Pershing, a Division of Donaldson, Lufkin and Jenrette with offices in Morris County, New Jersey is looking for both Senior and Junior DB2 Database Administrator(s) to apply the latest features of mainframe DB2 to its large, high-performance, high-availability, transaction-processing databases. Our shop is experiencing large growth due to exceptional business growth and major technological advancements (Data Sharing, Stored Procedures, SQLJ, data replication, and new database development) We use the latest tools from IBM, and BMC to roll-out changes, optimize performance, minimize outages, and diagnose problems. Pershing provides very competitive salaries and yearly bonus, technical training, and many other professional benefits. [...] 1766 84 26_Re: Global Temporary Table10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Tue, 16 May 2000 03:38:47 GMT335_us-ascii Hi Jose, Are you sure that you are performing all the work in a single unit of work because as soon as the commit or rollback is issued GTT losts all its records.So, you should check for any commits/rollback after insert query because both of them issues a DELETE from GTT( except in the case of WITH HOLD with Commit) . [...] 1851 56 28_Re: Timestamp in Load Module14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 16 May 2000 14:22:00 +1000566_iso-8859-1 Allen,

Nope, there is no easy way of finding this information in a load module. If you want a harebrained scheme I can offer:

- dis-assemble the loadmodule; find a call to DSNHLI in it; work backwards from the call to find the a "L 1,???" instruction just before it - note the location of the value loaded into R1; go to that location and note the address stored there - this is the address of the DB2 parameter list; start at the beginning of the program and scan forward looking for the code which initialises the DB2 parameter list; [...] 1908 115 26_Re: Global Temporary Table15_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 15 May 2000 21:38:22 -0400630_iso-8859-1 Hello,

Is there a commit or rollback between the insert to the global temp table and the update statement?

Pat



Tim.Lowe@STPAUL.COM@RYCI.COM> on 05/15/2000 02:35:57 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: Global Temporary Table







I thought that one restriction of Global Temporary tables was that they could not be updated. Did you check the sqlcode after the update? You should have gotten an sqlcode -526. [...] 2024 49 37_Re: Craig Mullins DBA Online Magazine18_Michael Arlebrandt28_micke_arlebrandt@HOTMAIL.COM30_Tue, 16 May 2000 08:58:18 CEST749_- Hi Andrea !

Craig Mullins homepage can be found at

http://www.craigsmullins.com/

regards Michael Ärlebrandt

>From: Andrea Milligan >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Craig Mullins DBA Online Magazine >Date: Mon, 15 May 2000 10:28:45 -0400 > >http://www.dbazine.com/ > >Andrea Milligan >DataBase Administrator >BlueCross BlueShield of Tennessee > > > -----Original Message----- > > From: bjnigh@HOUSEHOLD.COM [SMTP:bjnigh@HOUSEHOLD.COM] > > Sent: Friday, May 12, 2000 5:29 PM > > To: DB2-L@RYCI.COM > > Subject: [DB2-L] Craig Mullins DBA Online Magazine > > > > Does anyone have the url address for the Craig Mullin DBA magazine? [...] 2074 27 27_Re: DB2 SQL reporting tools14_Massimo Scarpa16_mscarpa@CESVE.IT31_Tue, 16 May 2000 09:00:42 +0200344_us-ascii We use REXX execs to manage DB2 DASD space and to generate all control jobs and

utility, using DB2WWWX and DSQCIX instruction to use QMF. No 3rd part tools, often not

used very much.

Autarky is great, you save money and you don't change release every time you upgrade your

operating system. (or not ?) [...] 2102 52 10_SMS VS DB25_ricci20_ricci@MS14.HINET.NET31_Tue, 16 May 2000 15:01:49 +0800125_big5 HELLO :

CAN YOU TELL ME? SMS ENVIRONMENT RUNNING DB2 ,MAYBE HAPPEN ANY PROBLEM ?

THANKS

RICCI HSIAO 2155 22 37_Re: Another Archive log size question14_Massimo Scarpa16_mscarpa@CESVE.IT31_Tue, 16 May 2000 09:24:35 +0200324_us-ascii If you have IBM's Tape Mount Analyzer you can use this tool to have a 'mean' value of tape

usage (that can be converted on 3390 tracks for subsequent processing). We used an iterative

technic to tune our tape usage (and to find an 'optimal' usage) in a Storagetek silo.

Regards Max Scarpa [...] 2178 86 28_Re: Linklist libraries & DB210_Leo Flores23_leoflores@EARTHLINK.NET31_Tue, 16 May 2000 00:45:57 -070065_us-ascii First post did not seem to reach the list. Trying again. 2265 36 14_Re: SMS VS DB220_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Tue, 16 May 2000 09:07:11 +0100734_big5 Yes and also No.

There is a good redbook on the subject: -

http://www.redbooks.ibm.com/abstracts/sg24562.html



Hugh Beighton-Dykes

-----Original Message----- From: ricci [mailto:ricci@MS14.HINET.NET] Sent: 16 May 2000 08:02 To: DB2-L@RYCI.COM Subject: SMS VS DB2



HELLO :

CAN YOU TELL ME? SMS ENVIRONMENT RUNNING DB2 ,MAYBE HAPPEN ANY PROBLEM ?

THANKS

RICCI HSIAO

================================================ 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. 2302 32 14_Re: SMS VS DB220_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Tue, 16 May 2000 09:09:37 +0100672_big5 Sorry, duff url, try this: -

http://www.redbooks.ibm.com/abstracts/sg245 462.html



-----Original Message----- From: ricci [mailto:ricci@MS14.HINET.NET] Sent: 16 May 2000 08:02 To: DB2-L@RYCI.COM Subject: SMS VS DB2



HELLO :

CAN YOU TELL ME? SMS ENVIRONMENT RUNNING DB2 ,MAYBE HAPPEN ANY PROBLEM ?

THANKS

RICCI HSIAO

================================================ 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. 2335 137 29_Fw:Re: Global Temporary Table29_=?iso-8859-1?Q?Jos=E9_Moura?=25_jose.antonio.moura@CGD.PT31_Tue, 16 May 2000 10:56:07 +0100531_us-ascii Thank you for your feedback - Tim, Sanjeev and Patrick - but...

a) I did not do any commit or rollback; b) I was not trying to update the global temp. table itself, but another table (the IBM sample for EMP table); c) The data type is the same in both tables (empno as a predicate of DSN8510.EMP, and empno in the global temp. table); d) I tried the update syntax using another table for getting a IN list of selected EMPNO's for updating in the table I wanted - and the updates were done! e) I tried a select [...] 2473 82 21_Re: DB2 Early Code V60_17_sjvagnier@AEP.COM31_Tue, 16 May 2000 07:33:48 -0400551_us-ascii



Hello Robert,

Look at Information APAR II11442. This APAR documents what PTF's are required for DB2 V5.1 & V6.1 BEFORE MIGRATION.

Hope this helps,

Steve Vagnier American Electric Power









Robert Greenwell @RYCI.COM> on 05/15/2000 04:26:00 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 Early Code V6 2556 225 72_Repost: IBM Statement of Direction for DB2 and DB2 Connect on Lin ux/39013_Morrill, John12_JohnM@VP.NET31_Tue, 16 May 2000 06:12:06 -0600731_- Greetings!

Below is a posting from Mike Swift's DB2 Interested Parties mailing list. If you would like to join his list, you can e-mail him at dbswift@us.ibm.com. Not all posting to DB2 Interested Parties are re-posted here.

Cheers!

J. Michael Morrill Chief DB2-L List Owner =================================================



Dear DB2 Interested Parties,

As previewed in IBM Fellow Don Haderle's (Vice President Data Management Architecture and Technology) keynote presentation yesterday at the 12th Annual International DB2 Users Group (IDUG) conference, IBM intends to start a beta program in July 2000 for a selected set of customers featuring IBM DB2 Universal Database and IBM [...] 2782 186 33_Re: Fw:Re: Global Temporary Table10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Tue, 16 May 2000 12:31:41 GMT288_iso-8859-1 Hi Jose, I am sorry but glad to inform that the thing you were trying at your place did work in my place and i tried exactly the same thing and the records got updated.In my case Autocommit option was NO. Check this option and otherwise some IBM person can only help you. [...] 2969 74 10_vb6-db2UDB13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET31_Tue, 16 May 2000 09:18:59 -0400472_us-ascii I do not know if this is the correct forum, but I am having problems getting the answer elsewhere. We are on DB2 UDB version 6.1 running on solaris box with windows nt clients. We are developing some applications using Microsoft's Visual Basic 6.0. The app can connect to the database fine, it can retrieve data and display on the forms. Our problem comes in when we try to update, or to set the fields in preparation to updating. Below is a sample of code: [...] 3044 30 22_Mapping table deadlock13_Adrian Savory24_adrian.savory@ZURICH.COM31_Tue, 16 May 2000 14:35:23 +0100590_us-ascii Hi all,

I was running some online reorgs yesterday and got a deadlock trying to create mapping tables (our online reorg jobs create mapping tables prior to the reorg step and drop them afterwards). The message details were:

REASON 00C90088 TYPE 00000302 NAME DSNDB06 .SYSDBASE.X'0000B7'

The best (!) part of this was that after the deadlock DB2 promptly abended with an S04F. I suspect that this is a bug that has probably been fixed (we're quite down-level on our maintenance - V5 9907 or thereabouts). If anyone has come across this please let me know. [...] 3075 19 28_Re: Timestamp in Load Module12_Allen Tsygan18_tsyganaa@AETNA.COM31_Tue, 16 May 2000 08:44:42 -0500489_- Tim, The issue I am having is that there is nothing but the load module. The VERSION(AUTO) is not used. If VERSION(AUTO) is used this would not be problem, the problem is when it is not used.



Thank you for your help.

Allen.

================================================ 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. 3095 12 24_DB2 V6 TCPIP connections14_Steve Domarski26_sdom@PROPAPPR.MARION.FL.US31_Tue, 16 May 2000 10:14:52 -0400390_- The Quick Beginnings documentation suggest that DB2 Connect is not necessary if your using TCP/IP as a transport method. Can any one confirm this.

================================================ 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. 3108 31 69_DB2 UDB Admin client software installed on developers' workstatio ns?13_Lynne Flatley17_LFlatley@NEFN.COM31_Tue, 16 May 2000 11:02:45 -0400558_us-ascii I'm wondering what other folks are doing regarding the installation of UDB client software on developers' workstations. We have a test environment in which the developers do not make structure changes but the admin tool would give the developers information in a more user-friendly way than the vanilla client run-time software that I have been installing on their workstations. Am I opening a can of worms? What do other shops install on the developers' workstations, assuming that the developers DO NOT do structure or configuration changes? [...] 3140 64 26_Re: Mapping table deadlock23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 16 May 2000 16:03:55 +0100414_iso-8859-1 Ade I assume you are going to remove the create of the mapping table ... like we do hear .. I now have 8 of them (as you know only the index gets used).

keep me posted if you get an answer on this one.

Leslie

-----Original Message----- From: Adrian Savory [mailto:adrian.savory@ZURICH.COM] Sent: Tuesday, May 16, 2000 2:35 PM To: DB2-L@RYCI.COM Subject: Mapping table deadlock [...] 3205 101 28_Re: Timestamp in Load Module0_19_Tim.Lowe@STPAUL.COM31_Tue, 16 May 2000 10:19:20 -0500476_us-ascii James, Very nice idea about using a dummy DSNHLI routine to pickup the parms. I tried it out, it was very easy to do. I need to keep this one in my back pocket!

Thanks, Tim













James Campbell on 05/15/2000 11:22:00 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM [...] 3307 253 33_Re: Fw:Re: Global Temporary Table0_19_Tim.Lowe@STPAUL.COM31_Tue, 16 May 2000 10:33:11 -0500300_us-ascii Jose, I ran your statements here successfully as well.

I did find several PTFs for global temporary tables and subselects. APAR PQ31008 / DB2 V5 PTF UQ36283 looks to me like a match to your problem. It closed 10/19/99, and you did say you were at 9909. Do you have this applied? [...] 3561 54 27_Positions and Salary Ranges13_Toppins, Mike19_MToppins@UNIPAC.COM31_Tue, 16 May 2000 09:44:32 -0600316_iso-8859-1 I apologize if this is not the proper place for this ...

We're looking to set up career pathing within our DBA group and were looking to get any information that other companies use in terms of career pathing and salary ranges for those paths. Where's the best place to start looking for such info? 3616 41 31_Re: Positions and Salary Ranges18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM31_Tue, 16 May 2000 11:56:22 -0400392_iso-8859-1 I'd look first in the 'Want Ads'! :-) Just kidding.. I think we're all worth alot more then we're getting paid but thats another story...

I'm so busy I can't even find my DBA career path...Good luck...

--Steve....



Steve Whittaker DB2/DBA Carolina Power & Light Mail stop: CPB 17A4 - Raleigh Email: stephen.whittaker@cplc.com Phone: (919) 546-7267 [...] 3658 58 26_Re: Mapping table deadlock12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Tue, 16 May 2000 19:21:29 +0200390_x-user-defined Hi,

Create them in advance. Don't drop. I have 1 TS (segmented segsize 4 priqty 720), 4 tables (I don't want more then 4 OLR jobs in parallel), and indexes as priqty 720 secqty 72000. As only indexes are used and as after end of run all extents are thrown away it is a very low price to pay. (Of course you may choose other sizes according to your site's needs). [...] 3717 39 28_Re: DB2 V6 TCPIP connections0_15_leon@CA.IBM.COM31_Tue, 16 May 2000 11:39:56 -0500357_us-ascii What database server are you planning to connect to: DB2 UDB on UNIX/NT/OS/2 or DB2 for OS/390? If the answer is DB2 for OS/390 then you always need DB2 Connect. Which Quick Beginnings book (DB2 Connect or DB2 UDB) made you think that DB2 Connect is not required?

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com [...] 3757 39 31_Re: Positions and Salary Ranges13_OConner, John26_John.OConner@COURTS.WA.GOV31_Tue, 16 May 2000 09:42:37 -0700351_iso-8859-1 Mike, The answers to your question will vary according to location, work environment, etc. There are several recruiters that monitor this list. Any one of them would probably be a good source of information for you. If you need a place to start, here's a name that I have handy: Email: john@dbajobs.com Website: http://www.dbajobs.com [...] 3797 23 38_Global Temporary Tables (may not work)29_=?iso-8859-1?Q?Jos=E9_Moura?=25_jose.antonio.moura@CGD.PT31_Tue, 16 May 2000 17:20:58 +0100377_us-ascii Thanks Tim, Sanjeev

But you Tim hit the target! It's that APAR PQ31008 (of course we don't have the correspondent PTF applied). It refers the same problem for either a delete or an update when a global temporary table is used in a subselect.

Still, does anyone uses global temp. tables in a production environment? And is he (or she) happy with it? [...] 3821 71 31_Re: Positions and Salary Ranges0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 16 May 2000 11:49:17 -0500523_iso-8859-1 Try some of the industry magazines, I remember seeing "annual salary reviews" in some of the mags. I'm pretty sure that Information week, www.informationweek.com, does one.









"OConner, John" @RYCI.COM> on 2000/05/16 11:42:37 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: Positions and Salary Ranges [...] 3893 39 26_Re: Mapping table deadlock10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Tue, 16 May 2000 11:55:45 -0500561_us-ascii We can't afford the DDL running constantly against our catalogs either as we have dynamic SQL running 23.5 x 7 on almost all regions. So - we created 5 mapping tables (720,720) with indexes of varying sizes (large, large; larger, larger & HUGE, HUGE) and then control the assignment of which online reorg mapping table to use based on # of rows from a setup card to the program. The program selects against the mapping control table we created and based on # of row, etc, assigns a mapping table, flags it as in use and populates the symbolics in [...] 3933 22 20_DB2 needs your help!0_15_leon@CA.IBM.COM31_Tue, 16 May 2000 12:06:19 -0500428_us-ascii The nice people who write DB2 Connect and DB2 UDB manuals need your feedback to deliver better books in the future. They've created a survey and placed it on the web at http://www-4.ibm.com/software/ucd/db2id_apr00.html. The first 100 people to complete the survey will receive a thank-you gift from IBM.

Please, take a few minutes to give us your feedback. It will help us make DB2 products easier to use. [...] 3956 119 0_11_Anshu Mohan20_anshu_garg@YAHOO.COM31_Tue, 16 May 2000 10:31:49 -0700317_us-ascii Hi List,

I have the following doubt I have been racking my brains with for the past some time but have not been able to find an answer.

The scenario is as follows :



NO.OF RECORDS IN TABLE T1 : 330632 NO.OF ACTIVE PAGES IN TABLE : 30024

Details of index on table T1 : [...] 4076 22 8_00E4003023_Trivedi, Kaushal J, IOG17_kjtrivedi@ATT.COM31_Tue, 16 May 2000 14:01:39 -0400360_iso-8859-1 Hi All, Someone had run a utility and its probably abended and its out there , I did a display and it showed it stopped , Upon trying to Terminate it I get the above message , and looking into the manuals it just says to refer to the Diagnosis guide , I do not have these could anyone let me know what could clear this utilid from the sysutil. [...] 4099 138 31_Re: Positions and Salary Ranges14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Tue, 16 May 2000 14:19:30 -0400703_us-ascii Hi Mike,

Or you can refer to Data masters survey site

or @ http://www.datamasters.com/survey.html#wc if the above link doesn't work.

HTH Bill G.



Rohn.Solecki@MTS.MB.CA wrote:

> Try some of the industry magazines, I remember seeing "annual salary > reviews" in some of the mags. I'm pretty sure that Information week, > www.informationweek.com, does one. > > "OConner, John" @RYCI.COM> on 2000/05/16 > 11:42:37 AM > > Please respond to DB2 Data Base Discussion List > > Sent by: DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > cc: > Subject: Re: Positions and Salary Ranges > > [...] 4238 123 24_Re: DB2 needs your help!27_Radonjic, Bora (Consultant)23_BRadonjic@ACNIELSEN.COM31_Tue, 16 May 2000 13:26:00 -0500615_- First 100? That isn't right, what about guys around the globe in different time zones, or is P&H to expensive. I suggest 100 randomly selected. Regards Bora Radonjic

On Tuesday, May 16, 2000 12:06 PM, leon@CA.IBM.COM [SMTP:leon@CA.IBM.COM] wrote: > The nice people who write DB2 Connect and DB2 UDB manuals need your > feedback to deliver better books in the future. They've created a survey > and placed it on the web at > http://www-4.ibm.com/software/ucd/db2id_apr00.html. The first 100 people to > complete the survey will receive a thank-you gift from IBM. > > Please, take a few minutes to give [...] 4362 38 46_Re: DBM1 storage requirements - crash and burn0_31_truman.g.brown@BELLATLANTIC.COM31_Tue, 16 May 2000 15:22:32 -0400459_us-ascii This is a question for Richard Yevich, Joel Goldstein, or anyone else with 2 cents (or more) to throw in.

We had a V5 subsystem crash in April that was due to a code prob- lem. I don't remember the details (other people were on the problem), but the subsystem was up for a long time and evidently there were GETMAINs without subsequent FREEMAINs, so ultimately a GET- MAIN failed. Theoretically that problem has been fixed with an APAR.. [...] 4401 107 13_Access path..11_Anshu Mohan20_anshu_garg@YAHOO.COM31_Tue, 16 May 2000 14:06:07 -0700285_us-ascii Hi!

I posted this message but the alignment got messed up. So, I am resending my mail as an attachment.

Thanks.

Anshu.

__________________________________________________ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. 4509 36 28_Re: Timestamp in Load Module19_Saunders, William C27_William_Saunders@CGUUSA.COM31_Tue, 16 May 2000 17:26:09 -0400380_- I wrote a DBRM/load comparison tool and was forced to work from the DBRMs to the loads, since the contoken can easily be found in the DBRM. I gather all contokens from the many versions of the DBRM in various libraries, then search the load module for each contoken (actually, half a contoken) until I find a match. Of course, this won't help you when there isn't a match! [...] 4546 126 17_Re: Access path..12_craig patton21_prgpatton@HOTMAIL.COM29_Tue, 16 May 2000 14:47:37 PDT506_- Anshu, In the first example with C25 colcard = 1, it is figured that every row will have the same value and it isn't considered for access, but when you chyange it to a colcard of 20, then DB2 is assuming that only 1/20th of the rows from T2 will qualify. since the number of active pages for the 2 tables is about the same and with colcard of 1 for c25, looks like DB2 just kept the tables in the order that you listed them. quite frankly I would change those indexes and put c12 and c22 above c11 [...] 4673 21 28_Re: Timestamp in Load Module12_Jim Harrison12_jimh@QIS.NET31_Tue, 16 May 2000 17:54:41 -0400545_us-ascii That sounds like an interesting bit of code. Have you/can you post it to the document list?

At 05:26 PM 5/16/00 -0400, Saunders, William C wrote: >I wrote a DBRM/load comparison tool and was forced to work from the DBRMs to >the loads, since the contoken can easily be found in the DBRM. I gather all >contokens from the many versions of the DBRM in various libraries, then >search the load module for each contoken (actually, half a contoken) until I >find a match. Of course, this won't help you when there isn't a match! [...] 4695 36 29_SYSLGRNX records left by DROP28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Tue, 16 May 2000 14:30:10 PDT352_- I have been able to reproduce this problem on our new version at STL.

There are two places where SYSLGRNX should be cleaned up.

1) DROP attempts to clear out obsolste records. However, if it fails (resource unavailable) the DROP continues. Cleanup will be performed later, at the time the same DBID/PSID pair is reused by a CREATE [...] 4732 20 29_SYSLGRNX records left by DROP28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Tue, 16 May 2000 16:00:42 PDT540_- The CREATE cleanup works just fine so I looked further at the DROP logic. There is logic in there to skip the cleanup which I need to discuss with the module owner.

This may be working as designed.

I'll update again tomorrow.

Dan DB2 Utilities Development dcourter@us.ibm.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. 4753 48 12_Re: 00E4003011_rick creech18_ykcirc@HOTMAIL.COM29_Tue, 16 May 2000 16:12:41 PDT467_- Hi, That sounds like the problem in version 5 where someone drops a tablespace while there is a stopped utility in sysutilx that was processing the same tablespace. A process was published on the list serve a few months ago on how to get rid of the utility i.d. You can probably find it in the archives. It has to do with running Repair on sysutilx in ordeer to change a couple of values for the offending utilty, and then the term will work. HTH, Rick Creech [...] 4802 28 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Mon, 15 May 2000 20:23:38 -0500463_us-ascii DAVIS, RICK (SBCSI) wrote: > > Hi Jim, > I guess you could lump OS390 overhead into the "cost of doing > business" bucket and justify a budget thusly. However, and I think this > would be of interest you, if I'm running 100+ percent busy quite frequently, > and I have a dynamite I/O subsystem like yours, driving fewer I/Os, where I > have that control, like with (V)DWQT, I can reduce some overhead and maybe > get a little more "real work" done. [...] 4831 24 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Mon, 15 May 2000 20:25:45 -0500322_us-ascii Joel Goldstein wrote: > > Threoretically, yes. However, real world situations "rarely" > prove this. Most objects are just too large and random to have > any significant increase in pages/write....... so the threshold > almost always belongs low - when the avg pages/write is a single dgit. > Joel Goldstein [...] 4856 43 29_SYSLGRNX records left by DROP28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Tue, 16 May 2000 16:15:33 PDT352_- It turns out there is no "bug". DROP is working as designed. I knew we had an APAR against this long ago (V3 ?) but what I did *NOT* know was that it was closed "working as designed".

I had the module owner read the logic to me and this is what we came up with. Its probably more than you need. Call it my penance for being wrong before. [...] 4900 307 13_Access path..11_Anshu Mohan20_anshu_garg@YAHOO.COM31_Tue, 16 May 2000 17:11:23 -0700424_us-ascii Hi Craig ,

You have mentioned :

'..., looks like DB2 just kept the tables in the order that you listed them...'. This is not the case. I reversed the order but access path remains the same.

In fact the point you made is my concern. As C25 has a COLCARD of 1, it should not have been considered at all for access path evaluation. But the following two queries give different access paths : [...] 5208 76 28_Re: Timestamp in Load Module14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Wed, 17 May 2000 09:25:56 +1000421_us-ascii William, With all our COBOL code it seems the contoken is split in half and you can search for the halves separately. With our C code it gets split into quarters and you have to search separately for the quarters. I don't understand the mechanism, but that's the result.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 17/05/2000 09:22 --------------------------- [...] 5285 34 46_Re: DBM1 storage requirements - crash and burn14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 16 May 2000 21:37:49 -0400547_ISO-8859-1 Message text written by DB2 Data Base Discussion List >SORTPOOL is allocated to 64 MB, and of course each user gets the allocation. If SORTPOOL is part of DBM1, and a sudden peak in con- current SORTPOOL users occurs, could this push DBM1 beyond avail- able storage?<

Yes. However, the 64Meg is the max a user can get. For really small sorts, it's possible thaat a user might get less than the 64M, if al the sort can be done in memory... and not go the VP. Haven't looked at the getmain traces for years, but I think it it [...] 5320 19 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 17 May 2000 00:11:54 -0400436_ISO-8859-1 The same problem exists with large indexes.

Joel Goldstein

>I suppose smaller footprint indexes are statistically a different story >from the tablespace. >Jim Lewandowski<

===============================================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. 5340 25 24_Reporting/Querying Tools7_Db2 Dba24_data_analyst@HOTMAIL.COM31_Tue, 16 May 2000 23:57:03 -0500378_- Hi everyone,

I am working on requirement gathering for selection of a reporting/querying tool for our environment.

The server is UDB EEE on AIX and clients are NT stations.

Has anyone done any comparison study of the popular tools who can share the results/reports with me?

Is there a url on the web that can shed some light on this question. [...] 5366 19 50_Getting Data for Test environment from Production.11_Linda Smith22_linda_home@HOTMAIL.COM31_Wed, 17 May 2000 00:30:30 -0500329_- Dear List,

I need to create a test environment. We have production database of size 100 GB, this is needed for the current development work.

What are the different ways of getting a subset of data from production and load in the test database which i am going to create.

Thanks & Regards, Linda Smith [...] 5386 127 17_Re: Access path..10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 17 May 2000 06:56:44 GMT570_us-ascii Hi Anshu, It is not always neccessary that smaller table which we feel is choosen as the outer table.I found it is very nicely explained in one of the Michael Hannan's article in DPJ which is in the www.YLAssoc.com website of Yevich and Lawson.It is "New ways to influence access path". The table in the join sequence is choosen roughly on this basis i.e whichever is less : (T1 access cost with local predicates) + (# of rows qualified from T1) * (T2 access cost with local/join predicate) or (T2 access cost with local predicates) + (# of rows qualified [...] 5514 124 33_Re: SYSLGRNX records left by DROP0_18_mebert@AMADEUS.NET31_Wed, 17 May 2000 09:37:49 +0100289_us-ascii Hi Dan,

glad to hear that. I had read the text about cleaning up SYSLGRNX records in the SQL Ref and thought to myself, "Well, that's another cleanup job waiting for me". Good to know that I can spare the effort. Thanks for your time and work you put into this issue. [...] 5639 29 8_00E4003028_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Wed, 17 May 2000 01:53:46 PDT332_- This is one of those "general" abends. "something went wrong" when we tried to switch to the command processor. I think this code was shown on message DSNU114I. There are a couple of other codes that the switch failure passes back that should have been included in this message, but were not. That makes it hard to diagnose. [...] 5669 73 12_Re: 00E4003014_Massimo Scarpa16_mscarpa@CESVE.IT31_Wed, 17 May 2000 11:37:27 +0200397_us-ascii Hi, here a solution (not mine, suggested by a verrry expert man) if you have an utility stopped

AND you drop the tablespace after utility was stopped, to clen SYSUTILX.

I used it to clen up SYSUTILX and worked very well. BUT, of course, you use this solution at

YOUR OWN RISK.

I prefere this solution insted to stop DB2 and delete/define SYSUTILIX again. [...] 5743 55 54_Re: Getting Data for Test environment from Production.12_craig patton21_prgpatton@HOTMAIL.COM29_Wed, 17 May 2000 05:07:06 PDT542_- Linda, There are a number of ways to accomplish the copy of data. DB2 provides you with DSNTIAUL which can unload using a WHERE predicate, optionally it can also create the LOAD control cards, which can be used to LOAD the unloaded data to the test environment. Very manual and if you have a lot of tables, it can be a pain. Both Platinum(CA) and BMC have FAST UNLOAD and LOAD tools that do the same thing, but MUCH faster, again some work on your part to set them up. Platinum(CA) also has 2 other tools for migrating data, RC/UPDATE [...] 5799 37 26_DB2 OS/390 V5.1 and TCP/IP0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 17 May 2000 08:12:29 -0400344_us-ascii Hi,

We have DB2 OS/390 V5.1 (OS/390 is at 2.7). All have the latest maintenance. We are trying to establish TCP/IP connectivity and received the error below. (1) Could you tell us what this error says and what we need to do to rectify it? (2) Can a single DB2 subsystem support both VTAM (SNA) and TCP/IP at the same time? [...] 5837 66 30_AW: DB2 OS/390 V5.1 and TCP/IP16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Wed, 17 May 2000 14:16:52 +0200617_ISO-8859-1 Check APAR PQ30957 and II10962

This return-code means there is somethink wrong with your IP config

Roland

> -----Ursprüngliche Nachricht----- > Von: Ed_Vetock@NAVYFEDERAL.ORG [SMTP:Ed_Vetock@NAVYFEDERAL.ORG] > Gesendet am: Mittwoch, 17. Mai 2000 14:12 > An: DB2-L@RYCI.COM > Betreff: DB2 OS/390 V5.1 and TCP/IP > > Hi, > > We have DB2 OS/390 V5.1 (OS/390 is at 2.7). All have the latest > maintenance. > We are trying to establish TCP/IP connectivity and received the error > below. > (1) Could you tell us what this error says and what we need to do to > rectify it? > (2) Can a [...] 5904 81 30_Re: DB2 OS/390 V5.1 and TCP/IP32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 17 May 2000 15:19:42 +0300638_- Here is the explanation for the message..

-------

This dsnl512i msg with gethostbyaddr has the following possible causes: 1) The TCPDATA dataset cannot be accessed by the DDF application. 2) DNS not available. 3) No entry in the DNS for this ipaddr. 4) No entry in /etc/hosts. 5) No entry in hlq.HOSTS.ADDRINFO dataset. ... In OpenEdition, the search order that is used to find the TCPDATA dataset for the DDF application is: 1. /etc/resolv.conf file 2. //SYSTCPD dd statement 3. jobname.TCPIP.DATA 4. SYS1.TCPPARMS(TCPDATA) 5. TCPIP.TCPIP.DATA The first file in this list will be used as the TCPDATA file. Once this [...] 5986 30 38_db2pm - quick poll of who is using it?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 17 May 2000 13:25:18 +0100724_iso-8859-1 if you are using the IBM monitor .. and fancy communicating with another site that uses it with SAP (not that it matters if you are SAP or not) then can you drop me a line?

Many thanks

Leslie Pendlebury-Bowe DB2 (V6) OS390 (2.8) SAP (4.6) ...and now Datasharing :-)



********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Sony cannot accept liability for statements made which are clearly the sender's own and not made on behalf of Sony. (01) ********************************************************************** [...] 6017 100 26_Re: Mapping table deadlock23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 17 May 2000 13:29:23 +0100314_x-user-defined Isaac

just out of interest .. we have automated schedule here which we run weekly and it's 8 streams .. goes like a steam train.

any reason why you went for 4 .. just enquiring ... no other reason really.... maybe you do not have to reorg more than 4 streams.

All the best [...] 6118 53 30_Re: DB2 OS/390 V5.1 and TCP/IP8_Bob Byrd14_BBYRD@PCMH.COM31_Wed, 17 May 2000 08:37:23 -0400581_US-ASCII We had this error recently during our implimentation. To resolve, added SYSTCPD DD DISP=SHR,DSN=TCPIP.PARMS(TCPDATA)

VTAM and TCPIP connections, once setup, can coexist.

>>> 05/17/00 08:12AM >>> Hi,

We have DB2 OS/390 V5.1 (OS/390 is at 2.7). All have the latest maintenance. We are trying to establish TCP/IP connectivity and received the error below. (1) Could you tell us what this error says and what we need to do to rectify it? (2) Can a single DB2 subsystem support both VTAM (SNA) and TCP/IP at the same time? [...] 6172 150 0_16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Wed, 17 May 2000 06:58:40 -0700528_iso-8859-1 I don't know the algorithm used, but I can guess at the logic behind it. There is one expected value for C15. You included it in the where clause and didn't hard-code it. Therefore, your SQL tells DB2 that you will always specify an explicit value for C15, but it will not necessarily be the 1 value it contains. Out of all of the possible values in that variable, all but 1 will reject every row without having to look at T2. If you run the query 10 times with 10 different values, then at least 9 of them will [...] 6323 109 28_Re: Timestamp in Load Module0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 17 May 2000 08:53:20 -0500473_us-ascii How about expanding the question to include PL/1. We are having a problem figuring out how to determine what submodules are linked into a mainline or other submodule. Anyone have any suggestions.









Paul A Redhead @RYCI.COM> on 2000/05/16 06:25:56 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 6433 20 54_Re: Getting Data for Test environment from Production.12_Allen Tsygan18_tsyganaa@AETNA.COM31_Wed, 17 May 2000 09:35:41 -0500532_- Linda, "Move for DB2" is a good tool to use for moving data. It allows you to create a strategy that defines related set of tables, using either the DB2 RI or user defined dependencies. It also has the WHERE clause support. It creates one file for the entire unload, which you can FTP to the other system and use it to populate your new database. The software uses a proprietary format, so you can't browse it outside of "Move for DB2". But it creates a report of all the extracted rows and the totals for every table. It is [...] 6454 52 30_Re: DB2 OS/390 V5.1 and TCP/IP13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 17 May 2000 10:04:59 -0500466_US-ASCII Ed, Make sure the etc/hosts in OE is configured correctly. Kurt

>>> Ed_Vetock@NAVYFEDERAL.ORG 05/17/00 07:12AM >>> Hi,

We have DB2 OS/390 V5.1 (OS/390 is at 2.7). All have the latest maintenance. We are trying to establish TCP/IP connectivity and received the error below. (1) Could you tell us what this error says and what we need to do to rectify it? (2) Can a single DB2 subsystem support both VTAM (SNA) and TCP/IP at the same time? [...] 6507 82 33_Re: SYSLGRNX records left by DROP16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Wed, 17 May 2000 08:22:48 -0700598_iso-8859-1 Why? Saying "working as designed" instead of "bug" doesn't justify or explain it. Whether obsolete records are left in SYSLGRNX by accident or on purpose, they're still there. The fact that the manual process of cleaning it up was documented shows that it was a "known" "work as designed", but doesn't answer the question of "why?". If a DROP stopped deleting rows from SYSCOPY in future versions of DB2, we could still follow the manual process, but that doesn't make it a good design. Whether it really works that way was a secondary question of this thread. The primary question [...] 6590 304 28_Re: Timestamp in Load Module27_Radonjic, Bora (Consultant)23_BRadonjic@ACNIELSEN.COM31_Wed, 17 May 2000 10:30:35 -0500780_- LISTIDR OUTPUT=ALL,DDN=SYSLIB,MEMBER=AMBLIST ***** M O D U L E S U M M A R Y ***** MEMBER NAME: AMBLIST MAIN ENTRY POINT: 00000000 LIBRARY: SYSLIB AMODE OF MAIN ENTRY POINT: 31 ** ALIASES ** ENTRY POINT AMODE HMBLIST 00000000 31 IMBLIST 00000000 31 ------------------------------------------------------------------------------------------------------------------------ **** ATTRIBUTES OF MODULE **** ** BIT STATUS BIT STATUS BIT STATUS BIT STATUS ** 0 RENT 1 REUS 2 NOT-OVLY 3 NOT-TEST 4 NOT-OL 5 BLOCK 6 EXEC 7 MULTI-RCD 8 NOT-DC 9 ZERO-ORG 10 EP-ZERO 11 RLD 12 EDIT 13 NO-SYMS 14 F-LEVEL 15 NOT-REFR ------------------------------------------------------------------------------------------------------------------------ MODULE SSI: 01115953 APFCODE: 00000000 RMODE: 24 [...] 6895 28 12_Dataset Move15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 17 May 2000 11:34:00 -0400494_iso-8859-1 > To all list members, > > We need to move all the DB2 table spaces and index space on one volume to > other volumes. Let us say, we have around 500 hundred small tables in this > drive. Right now we removed the volume from the stogroup and re-orging > each tablespace to move to other drive. We need to do this for couple of > other volumes too. This is time consuming, so is there any other efficient > way to do this? > > Thanks for the help. > > > Thanks > Sibimon Philip > [...] 6924 54 16_Re: Dataset Move13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 17 May 2000 08:39:07 -0700633_us-ascii Hi!

Take image copies of all tablespaces involved , remove the volume from stogroup and do recover to image copy . This should be much faster.

Thanks.

Ashish.

-----Original Message----- From: Philip, Sibimon [SMTP:SPhilip@CSXLINES.COM] Sent: Wednesday, May 17, 2000 8:34 AM To: DB2-L@RYCI.COM Subject: Dataset Move

> To all list members, > > We need to move all the DB2 table spaces and index space on one volume to > other volumes. Let us say, we have around 500 hundred small tables in this > drive. Right now we removed the volume from the stogroup and re-orging > each tablespace [...] 6979 67 16_Re: Dataset Move0_17_sjvagnier@AEP.COM31_Wed, 17 May 2000 11:56:18 -0400356_us-ascii Hi,

I believe the quickest way would be to:

1). Stop the applicable databases 2). Use IBM's DFDSS to move the DB2 linear datasets to the new volume(s) 3). Start the applicable databases 4). If using Stogroups, alter Stogroups to point to the new volumes

I have used this method on several occassions with no problems.... [...] 7047 59 16_Re: Dataset Move13_Terry Shockey30_Terry.Shockey@OCSE.STATE.AR.US31_Wed, 17 May 2000 10:54:34 -0500324_iso-8859-1 you could use IBM's DFDSS UTILITY TO MOVE ALL YOUR DATASETS FROM ON VOLUME TO ANOTHER THE PROCESS TAKES MINUTES TO MOVE A VOLUME AND IF YOU ARE MOVING TO A DIFFERENT DEVICE

3380 TO 3390 DFDSS will recognize this and allocate the datasets on the target volume accordingly.

refer to IBM's manual [...] 7107 124 26_Re: Mapping table deadlock12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Wed, 17 May 2000 19:15:54 +0200376_x-user-defined HI,

Budget... (for disks).

We can't afford to catch too much of the "free" disk space.

What we did was to split the streams all over the week (even on day time for systems that have "night" activity). The idea was to spread the activity according to application activity. We can live with that as long as we keep control on the disks. [...] 7232 76 19_FW: Here's a gotcha12_Lisle, Kathy29_Kathy.Lisle@GMACINSURANCE.COM31_Wed, 17 May 2000 12:20:37 -0400435_iso-8859-1 Following up on last week's MODIFY discussion, here's a situation found by two of my former colleagues. They forwarded it to me, and, with permission, I'm passing it on to you.

-----Original Message----- From: Steve Carson [mailto:Steve.J.Carson@sabre.com] Sent: Friday, May 12, 2000 10:13 AM To: kathy.lisle@GMACInsurance.com Subject: Here's a gotcha



Just thought I'd brighten your day a little. [...] 7309 38 29_RUNSTAT and REBIND frequency.13_Brendan Friel18_b.friel@PECORP.COM31_Wed, 17 May 2000 08:51:33 -0500484_iso-8859-1 I've got a client with a slew of COBOL programs with embedded SQL. The application is fairly stable so that hardly any of the programs have been rebound for years. However, REORGs and RUNSTATS are run frequently, even though there isn't a huge amount of activity against the tables.

Question one : Is it 100% true that the RUNSTATS won't have ANY effect until REBIND ? Or is DB2 smart enough to peek at the RUNSTATS information on the fly even for static SQL ? [...] 7348 23 16_Re: Dataset Move10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 17 May 2000 12:26:17 -0500443_us-ascii If you stop the objects and do an FDR move of the DSNDBC file (cluster) it's a lot faster. You can only move single volume datasets tho - no volume spanning datasets with FDR move. But this should cut down your reorgs tremendously. Your DASD person should have a move utility that will work if it's not FDR move. When the move is complete, you start the objects again and you're good to go. The move Vs the reorg is MUCH FASTER! [...] 7372 58 30_Re: DB2 OS/390 V5.1 and TCP/IP0_14_mtdage@ATT.NET31_Wed, 17 May 2000 17:28:10 +0000590_- We got this error because one of the TCPIP dataset (TCPIP.HOSTS.SITEINFO) was cataloged in a wrong volume on the LPAR where DB2 was running.

Check APAR II10962. This give you list of possibilities on why you are getting the error.

Good luck. Mayflor

> Ed, > Make sure the etc/hosts in OE is configured correctly. > Kurt > > >>> Ed_Vetock@NAVYFEDERAL.ORG 05/17/00 07:12AM >>> > Hi, > > We have DB2 OS/390 V5.1 (OS/390 is at 2.7). All have the latest maintenance. > We are trying to establish TCP/IP connectivity and received the error below. > (1) Could you tell [...] 7431 113 15_ColdFusion User14_subrata mondal25_subratamondal@HOTMAIL.COM29_Wed, 17 May 2000 17:43:40 GMT624_- Hi Db2 users,

I had this in my mail. If by any chance there are Cold fusion users in this list and again within the Virginia, DC and Maryland vicinity. Have yourself registered and attend this cold fusion event.



Subrata *********





> > ----- Forwarded message from mdcfug@teratech.com ----- > > Date: Wed, 10 May 2000 16:11:17 -0400 > > From: mdcfug@teratech.com > > Reply-To: mdcfug@teratech.com > > Subject: May 17 ColdFusion User Group Reminder > > To: vputhen@assyst.net > > > > > > REMINDER NEXT WEEK!!!--MARYLAND CFUG HOSTS ALLAIRE’S BEN > > FORTA AND ASHLEY KING > > [...] 7545 28 33_Re: RUNSTAT and REBIND frequency.41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR31_Wed, 17 May 2000 14:41:55 -0300497_iso-8859-1 >Question three: What is the consensus (or range of opinions) on frequency >and methodology of REORG/RUNSTATS/REBIND ?



We are a small shop - 500 tables, the biggest tables have an average of 6 million lines. There are about 700 COBOL programs/packages. We run RUNSTATS and BIND (REPLACE) on Sundays for all static plans and packages. REORGs are executed monthly only for tablespaces which need it or, eventually, when a table is changed by an ALTER TABLE statement. [...] 7574 59 16_Re: Dataset Move12_craig patton21_prgpatton@HOTMAIL.COM29_Wed, 17 May 2000 10:54:51 PDT372_- Philip,

DFDSS as well as FDR can do this at the Dataset level (You can specify the volume and the highlevel for you DSNs and move them all in a single job). Just be sure to STOP all objects and then move them. It will save a ton of time. I have done this on more than 1 occasion with complete success, although if I have the time, I prefer the REORG method. [...] 7634 43 30_DB2 for OS/390 as a DSS DBMS ?15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Wed, 17 May 2000 14:59:01 -0400305_iso-8859-1 Hi group,

We are running DB2 V4.1 for OS/390 R2.6 on an IBM 9672-RC6 hardware platform in which our application using DB2 is very small in size. I will be upgrading to DB2 V6.1 in August and setting up NET.DATA so this application can start internet development in the near future. [...] 7678 74 34_Re: DB2 for OS/390 as a DSS DBMS ?14_John P Shipley24_jpshiple@DUKE-ENERGY.COM31_Wed, 17 May 2000 15:29:59 -0400570_us-ascii If you correctly partition your tablespaces and allocate enough memory to the bufferpools you should be able to run any kind of analysis query needed on such a small amount of data since 45k tracks is less the 3gig. If you have plenty of capacity on your existing box it would seem more cost effective to use up those mips already paid for instead of buying more mips and a new set of software licences. The database should use good data warehouse design concepts (such as star schema) to maximize performance and ease of query writing. We can table space [...] 7753 32 28_Re: Timestamp in Load Module11_Jon Nolting18_nolting@AMDAHL.COM31_Wed, 17 May 2000 13:50:24 -0700427_us-ascii AMBLIST is part of the base operating system from IBM. It will run on a S/390 machine from IBM, Amdahl, or HDS without issue.

Speaking about IBM (I do not know if the same or similar program is available on HDS or Amdahl) there is program AMBLIST (aliases IMBLIST, HMBLIST) that can list load module information. It gives list of all linked modules and time stamp (but not exactly as DB2 contoken). [...] 7786 61 33_Re: RUNSTAT and REBIND frequency.17_Vijay Subramanyam33_vijay.subramanyam@HPSBLR.SOFT.NET31_Thu, 18 May 2000 09:54:16 +0530431_- Hi Brendan,

Answer to Question one: We have to REBIND after RUNSTATS otherwise DB2 cannot pick up the latest statistic information from the catalog. REBIND is done so that it picks up the latest statistics from the DB2 catalog.

VIJAY

-----Original Message----- From: Brendan Friel [SMTP:b.friel@PECORP.COM] Sent: Wednesday, May 17, 2000 7:22 PM To: DB2-L@RYCI.COM Subject: RUNSTAT and REBIND frequency. [...] 7848 132 33_Re: RUNSTAT and REBIND frequency.0_18_mebert@AMADEUS.NET31_Thu, 18 May 2000 09:51:54 +0100597_us-ascii In our installation, I have been trying hard since one year now to have the weekly REBINDs removed from the main application. The reason is as follows: many of the batch programs used by this application contain rather ugly joins between 3 or 4 tables (they're really several SELECTs merged into one, based on the value of host variables, so there's constructs like "... OR (:hv1='constant' AND ... " in the WHERE clauses). REORG is run every two weeks, RUNSTATS weekly. It turned out that in certain cases, DB2 flip-flopped weekly between two different access paths, one ok and one [...] 7981 131 34_Re: DB2 for OS/390 as a DSS DBMS ?0_18_mebert@AMADEUS.NET31_Thu, 18 May 2000 10:03:29 +0100541_us-ascii I hope it's not a "no-no" on this list, but I would not recommend DB2 (or another RDBMS) as a DSS system. My favourite would be the SAS system (which I've used extensively before switching to DB2). My personal CPU-time-conversion factor for the usual DSS type queries (access of most or all of a table) is 30: a job that takes 1 CPU sec in SAS, uses 30 in DB2. The elapsed time difference is less because a good SAS job is I/O-bound but it's still maybe a factor of 5 (NOT taking parallelism into account). Of course there are [...] 8113 171 23_Re: FW: Here's a gotcha0_18_mebert@AMADEUS.NET31_Thu, 18 May 2000 10:52:14 +0100547_us-ascii To explain why this is so, let's take a simple example: you have a partitioned TS. 30 days ago you took a full TS IC (DSNUM 0). After that, you took daily ICs of partition 2 only (DSNUM 2). Now you run a MODIFY TABLESPACE ... DSNUM 2 DELETE AGE(15). You will get the "No SYSCOPY records removed" message. MODIFY cleans up SYSCOPY and SYSLGRNX. So if you remove entries older than 15 days concerning partition 2, you cannot do a recovery to a point before. But you still have the DSNUM 0 IC, and because this implicitly includes info [...] 8285 98 43_Balt./Wash. DB2 Users' Group - Bonnie Baker0_19_valeriet@US.IBM.COM31_Thu, 18 May 2000 06:08:04 -0400663_us-ascii SPECIAL DB2 COURSE WITH BONNIE BAKER Sponsored by the Baltimore/Washington DB2 Users' Group

When? Tuesday June 14,2000

Where? Sheraton Columbia Inn, 10207 Wincopin Circle, Columbia, MD (410) 730-3900

Who? Bonnie Baker is an internationally-recognized DB2 educator, consultant, and application architect. She has over 18 years of information systems experience, concentrated in DB2 for OS/390. While Bonnie spends much of her time performing design reviews and performance tuning consulting engagements, she is best known for creating and presenting a series of DB2 seminars entitled, "Things I Wish They'd Told Me 8 Years Ago". [...] 8384 213 33_Re: RUNSTAT and REBIND frequency.12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Thu, 18 May 2000 13:44:43 +0300615_windows-1253 We have also experienced problems with weekly RUNSTATS and REBIND in the case where we have flip-flop tables. One of them is emptied every day before batch so that online transactions can go on adding movements while batch programs work with the other table (two identical tables for the same data flip-flopping on a daily basis for continuous operation). If RUNSTATS finds a table empty, then during rebind DB2 reverts the SQL access to these tables to Tablespace scan and then during batch process (when the specific table is full with data) we have experienced very poor performance because of [...] 8598 82 33_Re: RUNSTAT and REBIND frequency.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 18 May 2000 07:05:38 -0500555_ISO-8859-1 Brendan, please see embedded comments below indicated by *****

-----Original Message----- From: Brendan Friel [mailto:b.friel@PECORP.COM] Sent: Wednesday, May 17, 2000 8:52 AM To: DB2-L@RYCI.COM Subject: RUNSTAT and REBIND frequency.



I've got a client with a slew of COBOL programs with embedded SQL. The application is fairly stable so that hardly any of the programs have been rebound for years. However, REORGs and RUNSTATS are run frequently, even though there isn't a huge amount of activity against the tables. [...] 8681 244 33_Re: RUNSTAT and REBIND frequency.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 18 May 2000 07:13:16 -0500373_windows-1253 Gina, Use of DEGREE(ANY), you will discover, only benefits SQL with a very large result set. The reason being that it takes DB2 quite some time to set it up parallelism. You should first compare "wall time" between using DEGREE(ANY) and DEGREE(1) before choosing it. Search the DB2-L archives with DEGREE(ANY) to find several good comments on this issue. [...] 8926 21 21_Re: GDG sync question12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Thu, 18 May 2000 08:21:26 -0500341_iso-8859-1 To all who very kindly responded (and there were many!!) my sincerest Thank You! We have determined that a BMC image copy job was indeed causing the problem (not BMC, one of the user-set parameters) which we have changed from (+1) to (&gdg). Many of you helped point out several pitfalls to be aware of, and I appreciate it. [...] 8948 32 33_Re: RUNSTAT and REBIND frequency.15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Thu, 18 May 2000 09:36:31 -0400436_us-ascii Gina, Are you "BIND"ing or "REBIND"ing the packages. If you "REBIND"ing the packages the following should maintain your previous parameters with no changes

REBIND PACKAGE(colid.program)

HTH Bob Lawrence DBA Boscov's Dept Stores

-----Original Message----- From: Fertaki Gina [SMTP:GFertaki@EUROBANK.GR] Sent: Thursday, May 18, 2000 6:45 AM To: DB2-L@RYCI.COM Subject: Re: RUNSTAT and REBIND frequency. [...] 8981 26 0_14_george mclaren31_george_mclaren@STANDARDLIFE.COM31_Thu, 18 May 2000 14:40:46 +0100420_iso-8859-1 The following piece of SQL produces the correct results in a V4 Subsystem ..but not in a V5 subsystem

SELECT REVIEW_DATE FROM OB01PROD.REVIEW_DETAILS WHERE (REVIEW_DATE - CURRENT_DATE <= 50) ;



The following piece of SQL produces the correct results in both a V4 and V5 subsystem

SELECT REVIEW_DATE FROM OB01PROD.REVIEW_DETAILS WHERE (REVIEW_DATE <= CURRENT_DATE + 50 DAYS) ; [...] 9008 51 35_Just a curiosity about datasharing.14_Massimo Scarpa16_mscarpa@CESVE.IT31_Thu, 18 May 2000 15:43:27 +0200416_us-ascii Shortly I'll start to work in a big company were data sharing is active, so I'm reading

the Planning and Administration guide.

In the paragraph 'Installing and enabling DB2 data sharing', when Mr. (or Mrs.?) IBM describe the

procedure for merging pre-existent DB2s there's the phrase:

'The procedure described here is very difficult' in bold face type (never seen in an IBM [...] 9060 76 0_0_19_Tim.Lowe@STPAUL.COM31_Thu, 18 May 2000 08:49:16 -0500390_us-ascii George, The result of the first sql statement appear to be a comparison between a "date duration" (format yyyymmdd) to the number 50. The results of which would certainly be questionable depending on the input values.

The result of the second sql statement is a comparison between 2 dates.

I do not know how the results would be different between DB2 V4 and V5. [...] 9137 137 34_Data Warehouse/DASD Info Requested13_Brunner Don J21_Don.J.Brunner@IRS.GOV31_Thu, 18 May 2000 10:32:44 -0400354_iso-8859-1 Hi,

The IRS is currently evaluating purchasing DASD for a data warehouse that will be over eight terabytes in size. The primer contractor, who will remain anonymous, has been informed by others that EMC DASD is the only way to go. Our concerns are strictly on the information being used to justify purchasing this particular DASD. [...] 9275 19 11_DB2 Connect42_Franklin, Lorenzo (CC-Application Support)28_Lorenzo.Franklin@CONAGRA.COM31_Thu, 18 May 2000 09:32:32 -0500549_iso-8859-1 I am looking into setting up a DB2 Connect Gateway machine, that would be handling 300 concurrent connections to AS/400. The network is already taken into account, but what my concern is what amount of RAM should I estimate for each connection passing through this server?

Lorenzo Franklin

================================================ 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. 9295 37 38_Re: Data Warehouse/DASD Info Requested14_Massimo Scarpa16_mscarpa@CESVE.IT31_Thu, 18 May 2000 17:03:13 +0200424_us-ascii 1) And an huge amount of RAID - 1 papyri is better and without write penalty (you can eliminate

the scribe). Or it it better to write data in stone tables ?

2) I heard this story for EVERY contractor's DASD microcode (and storage silos, VTS, etc etc)

3) ...............No comment..............

Now seriously I know that there are companies with 14 Tb of data warehouse and 'normal' [...] 9333 41 15_Re: DB2 Connect0_15_leon@CA.IBM.COM31_Thu, 18 May 2000 09:56:51 -0500707_us-ascii What is the operating system that you will be running on the gateway?

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Franklin, Lorenzo (CC-Application Support)" on 05/18/2000 09:32:32 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: DB2 Connect







I am looking into setting up a DB2 Connect Gateway machine, that would be handling 300 concurrent connections to AS/400. The network is already taken into account, but what my concern is what amount of RAM should I estimate for each connection passing through this server? [...] 9375 27 9_DB2 roles14_jack fernicola20_bowling730@YAHOO.COM31_Thu, 18 May 2000 08:20:38 -0700623_us-ascii I'm looking to establish some basic guidelines for DB2 roles of a DBA versus a DB2 Systems programmer.

Is anyone willing to share their thoughts on this topic.

Thanks, Jack

===== Jack Fernicola

__________________________________________________ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.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. 9403 29 13_Re: DB2 roles14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 18 May 2000 10:25:44 -0500528_ISO-8859-1 Jack,

generally the DB2 SysProg (in all the shops I have worked) has been responsible for installing DB2, upgrades, maintenance, 3rd-Party SW installs, applying fixes etc. The DBAs are responsible for database design, implementation, performance & tuning, backup & recovery, housekeeping.

This can vary however. In a small site the roles could be combined or split further e.g. the database design could be carried out by one group and the production responsibilities carried out by another group. [...] 9433 120 39_Re: Just a curiosity about datasharing.23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 18 May 2000 16:34:06 +0100449_iso-8859-1 Max

I have installed and configured DS for a SAP DB2 environment (here) , infact one of the biggest in the world of its stype ( I do beleive the biggest but I hate it when people say they have the biggest system as you just never know).

Our system seems to be working very well at the moment with nearly 100% sharing .. but I am fully aware of the tuning required as and when things creep above pre-defined thresholds. [...] 9554 50 13_Re: DB2 roles9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM31_Thu, 18 May 2000 09:43:13 -0600481_iso-8859-1 In most shops I have worked in the DB2 DBA is responsible for supporting the Application Development side of the house. This includes installation, monitoring and tuning of all DB2 objects and supporting DB2 Application Developers in problem resolution.

I work for an outsourcing company now and the DB2 DBA's are responsible for all of the above (in most cases) as well as install and implement PTF's and new Versions of DB2 (the Systems Programmer stuff). [...] 9605 14 31_DB2 Connect on an NT Web Server12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 18 May 2000 10:46:00 -0400373_- Which is better for an NT Web Server, DB2 Connect Personal Edition or DB2 Connect Enterprise Edition? And why? Thanks in advance.

================================================ 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. 9620 57 13_Re: DB2 roles23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 18 May 2000 16:47:17 +0100552_iso-8859-1 Jack

I think Colin summs it up really ...

Les

-----Original Message----- From: Clayton, Colin [mailto:Colin_Clayton@BMC.COM] Sent: Thursday, May 18, 2000 4:26 PM To: DB2-L@RYCI.COM Subject: Re: DB2 roles



Jack,

generally the DB2 SysProg (in all the shops I have worked) has been responsible for installing DB2, upgrades, maintenance, 3rd-Party SW installs, applying fixes etc. The DBAs are responsible for database design, implementation, performance & tuning, backup & recovery, housekeeping. [...] 9678 56 15_Re: DB2 Connect42_Franklin, Lorenzo (CC-Application Support)28_Lorenzo.Franklin@CONAGRA.COM31_Thu, 18 May 2000 10:53:59 -0500492_iso-8859-1 We will be using Windows NT 4.0.

-----Original Message----- From: leon@CA.IBM.COM [mailto:leon@CA.IBM.COM] Sent: Thursday, May 18, 2000 9:57 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect



What is the operating system that you will be running on the gateway?

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Franklin, Lorenzo (CC-Application Support)" on 05/18/2000 09:32:32 AM [...] 9735 33 35_Re: DB2 Connect on an NT Web Server42_Franklin, Lorenzo (CC-Application Support)28_Lorenzo.Franklin@CONAGRA.COM31_Thu, 18 May 2000 10:55:15 -0500461_iso-8859-1 Depends if you'll be using it as a Gateway, such as how many connections you'll be doing to DB2.

-----Original Message----- From: BOB JEANDRON [mailto:BOB.JEANDRON@USDA.GOV] Sent: Thursday, May 18, 2000 9:46 AM To: DB2-L@RYCI.COM Subject: DB2 Connect on an NT Web Server Sensitivity: Personal



Which is better for an NT Web Server, DB2 Connect Personal Edition or DB2 Connect Enterprise Edition? And why? Thanks in advance. [...] 9769 32 44_Landmarks TMON for DB2 / SQL Capture feature18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM31_Thu, 18 May 2000 11:58:28 -0400513_iso-8859-1 If anyone out there is using Landmarks TMON for DB2 and you are currently using the SQL Capture feature could you give me your thoughts on it?? I am currently running a '30-day trial' license for SQL Capture and would like to get some real-world info on it. It seems like a good feature that allows us to capture the SQL stmts and host variables, host values etc. I was just wondering if someone might be using it more extensively and what they think of it. Any info would be greatly appreciated. [...] 9802 114 39_Re: Just a curiosity about datasharing.15_Toine Michielse18_vndobtm@US.IBM.COM31_Thu, 18 May 2000 18:03:53 +0100421_us-ascii Hello Max,

I've supported a number of data sharing implementation projects when DS was still very new. In two projects, both major banks in EMEA, we had to merge catalogs. In both cases we didn't bother with OBIDXLAT in DSN1COPY because it would take way too much time. Instead we opted to rename the VSAM clusters and REPAIR the headerpage in the tablespaces. Worked absolutely fine and very quick. [...] 9917 63 13_Re: DB2 roles12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 18 May 2000 09:05:52 PDT605_- Jack,

I mostly agree with Colin, but would like to emphasize that there are areas where the roles can be intermixed between Sysprog and DBA. Especially when working with DB2's Zparms and buffer sizes. I have found that a good System DBA should have input into how these are set to keep the performance of the system working with the design of applications and the usage of the system Ie: Number of concurrent Threads, sizes of Sortpool, RID pool, EDM pool, cacheing dynamic statements, or authorities, IDFORE and IDBACK (which establish each user's workarea size) DDF Settings and so forth. [...] 9981 71 0_16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Thu, 18 May 2000 09:11:43 -0700316_iso-8859-1 I don't know exactly what you mean by "correct" results, because the first format doesn't deal with '50 days' even in v4, but these two queries are definitely not synonymous. As I understand it, the first expression would only return the values within the last month. If I run the following on 5/18: [...] 10053 40 35_Re: DB2 Connect on an NT Web Server0_15_leon@CA.IBM.COM31_Thu, 18 May 2000 11:18:50 -0500420_us-ascii It is not a question of which is better. DB2 Connect Personal Edition is licensed for use by a SINGLE PERSON and not the entire web. You can not legally use it on a web server.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



BOB JEANDRON on 05/18/2000 09:46:00 AM

Please respond to DB2 Data Base Discussion List [...] 10094 81 15_Re: DB2 Connect0_15_leon@CA.IBM.COM31_Thu, 18 May 2000 11:16:18 -0500459_us-ascii You should budget 250-300K per concurrent connection. So, for 300 concurrent connections you should be looking to have roughly 90Meg. of available memory so you would not swap.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Franklin, Lorenzo (CC-Application Support)" on 05/18/2000 10:53:59 AM

Please respond to DB2 Data Base Discussion List [...] 10176 196 20_Access path question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Thu, 18 May 2000 12:04:02 -0400373_iso-8859-1 Greetings !!!



I have a scenario where an SQL takes around a minute to get result. The SQL can not be changed for the time being (strange eh !!), but I have to cut the time down to less than 10 seconds. I have been able to tune the SQL and run it in 3 seconds, but like I said SQL can not be changed for some reason. Here is the SQL structure [...] 10373 75 39_Re: Just a curiosity about datasharing.0_23_Mike_Levine@TEKHELP.NET31_Thu, 18 May 2000 12:54:30 -0400343_us-ascii Hi Max,

This is only a problem if you have more than one production DB2 subsystem. Most of the problems would then occur because of duplicate object names when you merge catalogs. If you have only one production DB2 subsystem (which has been the general IBM recommendation before data sharing) then you should be all set. [...] 10449 20 29_Question on Runstats Sampling13_Denise Rogers22_Denise.Rogers@WCOM.COM31_Thu, 18 May 2000 10:54:00 -0600581_- All,

Has anyone started using Runstats with the Sampling option on DB2 V5? If so, can you share what sampling rates you used and when they started to impact the optimizer choices?

Thanks in advance, Denise Rogers Team Lead EDS - Worldcom Infrastructure Delivery Network Management Applications e-mail: Denise.Rogers@eds.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. 10470 25 35_DB2v6 OS/390 Functions and the RRS.11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Thu, 18 May 2000 10:04:12 -0700457_iso-8859-1 Hi all, I'm trying to test the new user defined functions of v6. I'm up to the part where the WLM tries to start the DB2 WLM address space and it fails because it can't attach to the RRS address space.

The WLM can't attach to the RRS because its not there. Now for my question, Can anyone point me to where I can find instructions for setting up the RRS task and what are the bare minimums needed to get the DSNWLM/functions to work. [...] 10496 134 39_Re: Just a curiosity about datasharing.15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 18 May 2000 12:23:14 -0500382_ISO-8859-1 Toine,

I can understand how you could just repair the dbid/psid in the header page, but what about the obids in the space map and data pages ?

Michael Murley BMC

-----Original Message----- From: Toine Michielse [mailto:vndobtm@US.IBM.COM] Sent: Thursday, May 18, 2000 12:04 PM To: DB2-L@RYCI.COM Subject: Re: Just a curiosity about datasharing. [...] 10631 21 13_Re: Piecesize24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM31_Thu, 18 May 2000 13:29:45 -0400576_iso-8859-1 Hi fellow list lovers: We have been DB2 V5 (OS/390) for a while and PIECESIZE is starting to interest me. In reading about it in the manuals, I see the old adage about attempting to spread the index datasets across devices. However, I also seem to remember reading either in some book or in an exchange on the discussion group, that the Optimizer MAY be able to treat a non-partitioned index like an index that is partitoned if the index is 'split' into multiple PIECESIZE(s)(that is you might be able to achieve some degree of parallelism). Can anyone say if [...] 10653 42 39_Re: DB2v6 OS/390 Functions and the RRS.15_Zander, Barb J.19_bzander@STATE.ND.US31_Thu, 18 May 2000 12:29:52 -0500560_iso-8859-1 I used the redbook "Getting Started with DB2 Stored Procedures" SG24-4693-01. Chapter 3 has most of the info you need.

Barb

-----Original Message----- From: Cliff Boley [mailto:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Thursday, May 18, 2000 12:04 PM To: DB2-L@RYCI.COM Subject: DB2v6 OS/390 Functions and the RRS.



Hi all, I'm trying to test the new user defined functions of v6. I'm up to the part where the WLM tries to start the DB2 WLM address space and it fails because it can't attach to the RRS address space. [...] 10696 19 21_query help with count15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM31_Thu, 18 May 2000 10:40:26 -0700485_iso-8859-1 This seems simple but I'm just not getting it. I want a count by date of rows added to a table.

select date(timestamp), count(*) returns a count of 1 for each timestamp. How do a get a sum of counts by date?

TIA, Sandi

================================================ 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. 10716 55 39_Re: DB2v6 OS/390 Functions and the RRS.15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Thu, 18 May 2000 13:45:03 -0400550_iso-8859-1 There is an IBM book on RRS which is located at either the IBM website or on the CDROM disk 7 of the Dec 1999 OS/390 collection called:

OS/390 V2R6.0 MVS Programming: Resource Recovery GC28173903

There was also a short presentation from Share Technical Conference presented on RRS but I don't have the CD Rom yet to forward you a copy of the material presented (I understood they're in the mail) and I cannot seem to open the presentations from SHARE's website WWW.SHARE.ORG yet either - so we'll have to wait for that. [...] 10772 27 25_Re: query help with count15_kathryn Dempsey19_katdemp@MED.UNC.EDU31_Thu, 18 May 2000 13:46:02 -0400553_us-ascii you need to group by date...

"Mitchell, Sandi" wrote: > > This seems simple but I'm just not getting it. I want a count by date of > rows added to a table. > > select date(timestamp), count(*) returns a count of 1 for each timestamp. > How do a get a sum of counts by date? > > TIA, > Sandi > > ================================================ > 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. [...] 10800 45 25_Re: query help with count13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Thu, 18 May 2000 10:50:36 -0700439_us-ascii Hi!

Try SELECT DATED,COUNT(*) FROM ( SELECT DATE(TIMESTAMP) DATED FROM ) T1 GROUP BY DATED

Thanks.

Ashish.

-----Original Message----- From: Mitchell, Sandi [SMTP:Sandi.Mitchell@AVISTACORP.COM] Sent: Thursday, May 18, 2000 10:40 AM To: DB2-L@RYCI.COM Subject: query help with count

This seems simple but I'm just not getting it. I want a count by date of rows added to a table. [...] 10846 29 25_Re: query help with count11_Bill Tucker22_BTUCKER@CCGROUPNET.COM31_Thu, 18 May 2000 13:44:35 -0400332_US-ASCII use a group by date.

>>> "Mitchell, Sandi" 05/18/00 01:40PM >>> This seems simple but I'm just not getting it. I want a count by date of rows added to a table.

select date(timestamp), count(*) returns a count of 1 for each timestamp. How do a get a sum of counts by date? [...] 10876 47 25_Re: query help with count15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Thu, 18 May 2000 14:12:04 -0400608_us-ascii also distinct as in: select distinct date(timestamp), count(*) from tbl group by timestamp

Bob Lawrence

-----Original Message----- From: kathryn Dempsey [SMTP:katdemp@MED.UNC.EDU] Sent: Thursday, May 18, 2000 1:46 PM To: DB2-L@RYCI.COM Subject: Re: query help with count

you need to group by date...

"Mitchell, Sandi" wrote: > > This seems simple but I'm just not getting it. I want a count by date of > rows added to a table. > > select date(timestamp), count(*) returns a count of 1 for each timestamp. > How do a get a sum of counts by date? > > TIA, > Sandi > > [...] 10924 23 43_securing one member of a Data sharing group10_Tom Taylor17_ttaylor@CHUBB.COM31_Thu, 18 May 2000 14:19:34 -0400372_us-ascii Hi All

I'm securing batch access to a db2 member in a Data sharing group, using 'db2p.batch' racf profile. I have distributed users through ddf using the stored procedure address space. What userid does the SPAS get past when access to a stored procedure is requested? The spas address space userid? The authid of the user? db2pmstr address space id? [...] 10948 74 25_Re: query help with count0_24_db46@DAIMLERCHRYSLER.COM31_Thu, 18 May 2000 14:16:53 -0400399_us-ascii I'm assuming that your SQL is as follows select distinct date(timestamp), count(*) from tablea group by timestamp order by timestamp

Now what is happening is that the timestamp is probably unique and you had to put the distinct on there to get one row. Plus you have to put the grouping on or the 'count' won't work. So the '1' is being returned since each timestamp is unique. [...] 11023 41 25_Re: query help with count13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 18 May 2000 13:31:29 -0500372_US-ASCII Hi Sandy, You need to use an inline view. ex. SELECT DATECOUNT, COUNT(*) FROM (SELECT SUBSTR(CHAR(CREATED),1,10) AS DATECOUNT FROM creator.tablename) AS A GROUP BY DATECOUNT; HTH Kurt





>>> Sandi.Mitchell@AVISTACORP.COM 05/18/00 12:40PM >>> This seems simple but I'm just not getting it. I want a count by date of rows added to a table. [...] 11065 37 25_Re: query help with count13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 18 May 2000 13:42:21 -0500448_US-ASCII Sandi, This might be more efficient SELECT DATECOUNT,COUNT(*) FROM (SELECT DATE(TIMESTAMP) AS DATECOUNT FROM creator.tablename) AS A GROUP BY DATECOUNT Kurt

>>> Sandi.Mitchell@AVISTACORP.COM 05/18/00 12:40PM >>> This seems simple but I'm just not getting it. I want a count by date of rows added to a table.

select date(timestamp), count(*) returns a count of 1 for each timestamp. How do a get a sum of counts by date? [...] 11103 40 20_SAP DASD consumption13_Gary Sakamoto17_garys2@US.IBM.COM31_Thu, 18 May 2000 15:05:56 -0400603_us-ascii Background: The customer is running SAP 40B on SP/2 nodes escon attached to R36 running DB2 V5.1 and OS/390 2.5. The productoin SAP database is approx 300 GB and growing. They also have 3 other SAP environments for development, test and production support plus image copy and SNAP volumes. Total DASD comsumption for database and database related volumes is over 2 terabytes, storage media is IBM RAMAC T82 and X82 DASD. Due to financial constraints, the customer is looking to cut back on DASD costs among other things. In a meeting with the customer I suggested we look into compressing [...] 11144 153 39_Re: Just a curiosity about datasharing.15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 18 May 2000 14:29:58 -0500467_ISO-8859-1 Oh, I see. You included a requirement to use the OBID clause. Sorry, I missed that before.

One other question, though. A segmented, compressed table space includes one segment entry with the PSID of the space. Wouldn't you need to repair that, too ?

-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@bmc.com] Sent: Thursday, May 18, 2000 12:23 PM To: DB2-L@RYCI.COM Subject: Re: Just a curiosity about datasharing. [...] 11298 34 29_SYSLGRNX records left by DROP28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Thu, 18 May 2000 09:55:09 PDT445_- Tony,

Somewhere in the noise taking me to task for not following the thread I think you had a question:

"Why does it work that way?"

I don't know. I wrote these cleanup routines in 1979, but, regretably, not their caller. The few of us that remain from those days don't recall and the design documents don't say. Our early builds of DB2 V1 didn't even have SYSLGRNG (old spelling intentional), let alone clean it up. [...] 11333 76 24_Re: SAP DASD consumption11_Ray H Janes27_ray_h_janes@EMAIL.MOBIL.COM31_Thu, 18 May 2000 14:32:21 -0500504_us-ascii We are a SAP R/3 shop running DB2 V5.1 and have compression on for about 300 tablespaces in production. We have been turning it on for almost two years now. We also run OS MVS 2.8 and it is maxed out cycle wise. Compression has not caused any problems. Be aware. The SAP Cluster (NOT DB2 cluster) tables will not compress. We also have some DB2 V6.1 Datasharing also using compression. No problems. We have 278,632,588KB in one of our three production environments. Conclusion? Turn it on. [...] 11410 87 38_Re: Data Warehouse/DASD Info Requested12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 18 May 2000 22:53:14 +0200440_x-user-defined Hi, Without going into a DASD battle (I had enough of that during the last 6 months...), you can certainly look after either SHARK or the HDS7700E and EMC as well. Each vendor has it's own pros and cons and it would be reckless to put more then that here. The amount of CHPIDs, NVS, CACHE (just to name few factors) affect the disks performance, BUT poor SQL and/or bad database design can kill your system much faster. [...] 11498 81 24_Re: SAP DASD consumption10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Thu, 18 May 2000 15:12:43 -0500404_us-ascii Gary, I drug my users into turning on Hardware assisted compression in our SAP environment in my old shop. We FIRST took off the 'editprocs' which are/were SAP's answer to compression (software assisted compression) and that required a bit more work as you need to alter the tablespace to compress Y & drop/recreate the table to alter the editproc off, so it required a bit more down time. [...] 11580 106 24_Re: SAP DASD consumption0_20_John_Lendman@FPL.COM31_Thu, 18 May 2000 16:22:21 -0400741_us-ascii As of 4.5B there are no EDITPROCS that come from SAP defined on the tables. John







"Missy Case" @RYCI.COM> on 05/18/2000 04:12:43 PM

Please respond to "DB2 Data Base Discussion List"

Sent by: "DB2 Data Base Discussion List"



To: DB2-L@RYCI.COM cc:

Subject: Re: SAP DASD consumption



Gary, I drug my users into turning on Hardware assisted compression in our SAP environment in my old shop. We FIRST took off the 'editprocs' which are/were SAP's answer to compression (software assisted compression) and that required a bit more work as you need to alter the tablespace to compress Y & [...] 11687 55 35_Re: DB2 Connect on an NT Web Server16_Swinski, Kenneth32_KSwinski@INTERNAL.MASSMUTUAL.COM31_Thu, 18 May 2000 16:46:00 -0400612_us-ascii Also, I've heard that Personal Edition is not "thread safe" (can't handle multi-tasking) under Microsoft Transaction Server.

> -----Original Message----- > From: leon@CA.IBM.COM [SMTP:leon@CA.IBM.COM] > Sent: Thursday, May 18, 2000 12:19 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Connect on an NT Web Server > > It is not a question of which is better. DB2 Connect Personal Edition is > licensed for use by a SINGLE PERSON and not the entire web. You can not > legally use it on a web server. > > Leon Katsnelson, DB2 Connect Development Manager > mailto:leon@ca.ibm.com > > > BOB JEANDRON [...] 11743 25 38_Re: Data Warehouse/DASD Info Requested14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 18 May 2000 16:58:24 -0400363_ISO-8859-1 Don, Sorry for the delayed response, but I was in the air on my back from IDUG. This is not a simple discussion, and there are many considerations.

As a base comment though, I think your contractor should provide some staff with a higher level of expertise.

Give me a call at 732-972-1261 if you want to discuss any of these issues. [...] 11769 64 24_Re: SAP DASD consumption0_39_Patrick_D_Poziwilko@EMAIL.WHIRLPOOL.COM31_Thu, 18 May 2000 16:57:59 -0400415_US-ASCII Gary,

One of our productions is about the same size as your production. Most tables are compressed. A number of tables do not compress well and will prove to have a negative compression value. We look for a 50% savings or greater to let the tablespace stay in compression. There are a number of tables that we do not compress, like the VB... We have mostly good things happen with compression. [...] 11834 16 38_Re: Data Warehouse/DASD Info Requested26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Thu, 18 May 2000 16:09:59 -0500451_- Yep, guess the first thing I'd do is get a second opinion, a good one at that! Many of the items in this posting sound like vendor FUD. 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. 11851 191 39_Re: Just a curiosity about datasharing.15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 19 May 2000 00:48:43 +0100471_us-ascii Hello Michael,

Can you be more specific, as I have no idea what you are talking about... (could be the time in my timezone when I'm reading this.. 00.45)

Regards,

Toine Michielse DB2 for OS/390 Technical Specialist Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



"Murley, Michael" @RYCI.COM> on 05/18/2000 08:29:58 PM [...] 12043 234 39_Re: Just a curiosity about datasharing.15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 18 May 2000 18:25:02 -0500954_ISO-8859-1 Toine,

Sorry for being so vauge (It's been a long day). Here's an example of what I mean:

HEADER PAGE: PGCOMB='10'X PGLOGRBA='F02BF92A5745'X PGNUM='00000000'X HPGOBID='02750009'X HPGHPREF='000002D0'X HPGCATRL='00'X HPGTORBA='000000000000'X HPGTSTMP='00010101000000000000'X HPGFOID='0008'X HPGPGSZ='1000'X HPGSGSZ='0008'X HPGPART HPGZ3PNO='000002'X HPGZNUMP='10'X HPGTBLC='0001'X HPGRO HPGZ4PNO='00000000'X HPGMAXL='00EC'X HPGNUMCO='0004'X H HPGCONTM='20000518180838630332'X HPGSGNAM='SYSDEFLT' HPG HPGRBRBA='F02BF9353948'X HPGCLRSN='F02BF90F658E'X HPGLEV HPGPLEVL='F02BF90F9C47'X HPGCLRSN='F02BF90F658E'X HPGCAT SEGMENTED SPACEMAP PAGE: PGCOMB='10'X PGLOGRBA='F02BF93527EA'X PGNUM= SEGNUM='0152'X SEGFREE='0107'X SEGENT='004C' SEGLENT='00000254'X FOEND='N' FIRST PART OF SEGMENTED SPACE MAP: SEG 0001 000000020009C0 FFFFFFFF SEG 0002 00000000000980 FFFFFFFF SEG 0003 00000004000AC0 33333333 SEG 0004 00000005000A80 33333333 [...] 12278 30 30_DProp/R between OS/390 and AIX12_Raymond Bell20_raymondb@NZ1.IBM.COM31_Fri, 19 May 2000 16:20:56 +1200586_us-ascii Colleagues,

Could someone please advise on the following. We're toying with the idea of migrating an application from DB2 for OS/390 V5 to DB2 for AIX V6. We're looking at using Data Propagator to keep the two databases in sync while we set up the rest of the application before cutting over. We already have DProp/R to replicate between two different subsystems on the host. I'd briefly like to know a) if it's even possible to propagate changes from a host-based DB2 database to an AIX-based one, and b) if anyone is doing it, if they wouldn't mind sharing their [...] 12309 81 35_Re: DB2 Connect on an NT Web Server0_15_leon@CA.IBM.COM31_Fri, 19 May 2000 00:46:13 -0400410_us-ascii DB2 Connect Personal Edition IS thread-safe but you are absolutely correct in saying that it does not support MTS. The issue is not the thread-safe aspect but rather support for XA. XA is supported only on DB2 Connect EE.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Swinski, Kenneth" on 05/18/2000 04:46:00 PM [...] 12391 272 39_Re: Just a curiosity about datasharing.15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 19 May 2000 07:30:01 +0100459_us-ascii Hello Michael,

As far as I can see it's never checked for the dictionairy pages. Also, in the merge projects that have been performed this has never been an issue but it's worth a test. My educated guess is it'll work without patching.

Regards,

Toine Michielse DB2 for OS/390 Technical Specialist Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS [...] 12664 250 24_Re: Access path question10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Fri, 19 May 2000 05:58:09 GMT537_us-ascii Hi Sanjay, First of all i cann't get the clear picture of the access path of the query given by you except for the fact that Table2 is the first table accessed and as it is very large compared to table1 ,but i think this is the only thing which is causing the problem. Still i would like to apply some tricks to influence the optimizer for choosing the table join sequence. I am giving below the modified query with some changes.I will be very happy if it can change the explain report to choose Table1 as the outer table. [...] 12915 86 48_Re: Landmarks TMON for DB2 / SQL Capture feature13_Merrett, Paul22_Paul.Merrett@QR.COM.AU31_Fri, 19 May 2000 16:24:54 +1000548_ISO-8859-1 Steve,

We have been using SQL Capture for a little while now and are quite happy with it. Since you have it for a trial I won't bore you with the details of what it can and can't do. I guess you already know that.

Just be careful capturing everything in production (sounds obvious but some people do just that from time to time). Ensure the number 4 logs for SQL capture are quite large too. Every now and then people set off massive captures (and forget about them). You later look back thru the log and see heaps of [...] 13002 83 11_DRDA Access9_Anli Chao23_anlichao@MS14.HINET.NET31_Fri, 19 May 2000 14:48:20 +0800418_big5 Dear All,

We're testing DRDA functions with MASS insert. The following SQL statement was executed in SPUFI and received SQL code ¡V512:

INSERT INTO LOCATION1.CREATOR1.T1 SELECT * FROM LOCATION2.CREATOR2.T1

However, a single SELECT without using INSERT is working fine. Database Protocol DRDA was selected in the install panel DSNTIP5.

Does anyone knows what could be the problem? [...] 13086 90 39_Re: Just a curiosity about datasharing.11_rick creech18_ykcirc@HOTMAIL.COM29_Fri, 19 May 2000 01:19:22 PDT560_- Massimo, The reason it is in bold print is because when a company goes to data sharing there is the possibility that they are attempting to take two separate sub-systems and make them one data sharing system. In such a case there is no such thing as merging catalogs. One of the members is chosen as the originating member to be migrated to datasharing, and then in order to get all of the tablespaces, tables, indexes, etc. into the data sharing system the DDL from the second sub-system must be run. After the objects have been created anew, the data [...] 13177 148 33_Re: Question on Runstats Sampling0_18_mebert@AMADEUS.NET31_Fri, 19 May 2000 10:22:13 +0100556_iso-8859-1 I have an automated procedure for generating RUNSTATS jobs which is still very rudimentary. I use a sampling rate based on the used space in the VSAM file. The strategy is this: sample 50 MB+5% of the used space, with minimum 150 MB. Convert this to a percentage. If this is more than 66%, OR the DB in question is DSNDB06, do not use sampling. This is because SYSPKAGE, the largest DSNDB06 TS in our environment, contains both very large and very small tables; the statistics for the small ones would be useless because of sampling error. [...] 13326 51 16_Compress no rows14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 19 May 2000 09:27:48 +0100390_iso-8859-1 Hmmm..... Here's a good one.... We've a 11000 track table which only has data in for a short period of time each night's batch run, is it empty the rest of the time. The more data that's processed, the bigger it's going to get.

I have two questions:

What about runstats? The access path is going to be rubbish unless runstats are run at an appropriate moment. [...] 13378 79 38_Re: Data Warehouse/DASD Info Requested11_rick creech18_ykcirc@HOTMAIL.COM29_Fri, 19 May 2000 01:30:11 PDT562_- HI, You should have been at IDUG this week. An IBM speaker in the opening session emphasized that the greatest reason for failures in creating data warehouses are the human failures of those who just do not know enough to get it right. Your company is definitely heading that way, if you rely on the information you have put in your email. On the last day of IDUG there was a very good presentation that you really should take a look at called a "Multi-Terabyte Database: A Re-Partitioning Story" by Konstantin Tadenev of UPS. You need to beg, borrow, or [...] 13458 100 24_Re: SAP DASD consumption13_Adrian Savory24_adrian.savory@ZURICH.COM31_Fri, 19 May 2000 09:47:09 +0100604_us-ascii Gary,

No problems using compression with SAP R/3. Just make sure you run DSN1COMP first, as you you'll see a huge difference in the compression ratio between tablespaces. In my experience general leger, COPA and some sales tables compress well, as somebody already pointed out don't consider the SAP cluster tables as they don't compress at all. Obviously the way tables are being accessed will have an impact on your decision to compress or not - look at the statistics in transaction ST10 to help you decide. You can download this to a spreadsheet and play around with the figures [...] 13559 141 20_Re: Compress no rows0_18_mebert@AMADEUS.NET31_Fri, 19 May 2000 10:50:24 +0100439_us-ascii That's half a gig of data... and you are filling it with SQL, not LOAD (LOAD can build a compression dictionary), every night?? In that case: - run RUNSTATS when the table is full; don't run it again afterwards. - run REORG (after setting COMPRESS YES) when the table is full; don't run it again afterwards. If you user LOAD REPLACE with DUMMY SYSREC to kill the data afterwards, use the KEEPDICTIONARY option with the LOAD. [...] 13701 127 20_Re: Compress no rows13_Adrian Savory24_adrian.savory@ZURICH.COM31_Fri, 19 May 2000 10:06:08 +0100349_us-ascii Marcus,

I would set your catalog stats for this table(space) and avoid running runstats. Make sure you EXPLAIN the SQL so that you are getting the access paths that you want. If you want to force index access, a good test is a delete using predicates matching the primary key - it should go via the index without any pre-fetch. [...] 13829 151 24_Re: SAP DASD consumption23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 19 May 2000 10:16:32 +0100493_iso-8859-1 Gary Hi. Sounds like you are having the same issues that we had.

To help us I wrote a complex rexx to read in DB2 Catalog Stats, ST10 detail report based over a month(current and detailed) and a large file of DSN1COMP reports of ALL our SAPR3 objects .. with the help of a buddy of mine way back when (Adrian Savory) who created the internal Asembler bits in PDA to get the DSN1COMP report production automated based on our previous nights shrlevel change image copies. [...] 13981 165 20_Re: Compress no rows0_18_mebert@AMADEUS.NET31_Fri, 19 May 2000 11:32:25 +0100318_us-ascii Additional idea: if you can't run the REORG/RUNSTATS in your batch cycle, just run a SHRLEVEL CHANGE IC, which should not cause undue problems. In the daytime, restore this data to the then empty TS using RECOVER TOCOPY or DSN1COPY (don't bother about data inconsistencies), and then run your utilities. [...] 14147 162 20_Re: Compress no rows23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 19 May 2000 10:32:28 +0100604_iso-8859-1 or use the new ZPARM paramter if you are at V6 ...

Leslie

-----Original Message----- From: Adrian Savory [mailto:adrian.savory@ZURICH.COM] Sent: Friday, May 19, 2000 10:06 AM To: DB2-L@RYCI.COM Subject: Re: Compress no rows



Marcus,

I would set your catalog stats for this table(space) and avoid running runstats. Make sure you EXPLAIN the SQL so that you are getting the access paths that you want. If you want to force index access, a good test is a delete using predicates matching the primary key - it should go via the index without any pre-fetch. [...] 14310 92 14_ddf won't stop19_Elmer J. Valenzuela18_elmer@KFUPM.EDU.SA31_Fri, 19 May 2000 13:05:48 +0300583_iso-8859-1 Hello, On OS/390 2.4, DB2510, TCP/IP 3.2 We usually stop then restart DDF when users get some problems connecting to DB2 thru DDF. Recently we've had several occassions when a -stop ddf command nor a -stop ddf mode(force) cannot bring DDF down. We tried bringing the whole DB2 down but it won't go down totally. Seems like DB2 was waiting for DDF to stop. Checked all DDF threads and there was none. One thing we noticed is that OMVS appications also stopped working. But we cannot recycle OMVS without an IPL. We tried stopping the DSNVEUS3 process from OMVS (which [...] 14403 134 15_Re: DRDA Access14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 19 May 2000 08:35:52 -0400512_iso-8859-1 Anli:

you cannot refer to tables from more than one location in a single sql statement. I believe DataJoiner lets you get around it.

Manas. -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Anli Chao Sent: Friday, May 19, 2000 2:48 AM To: DB2-L@RYCI.COM Subject: DRDA Access



Dear All,

We're testing DRDA functions with MASS insert. The following SQL statement was executed in SPUFI and received SQL code ¡V512: [...] 14538 58 18_Re: ddf won't stop19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 19 May 2000 07:37:19 -0500374_ISO-8859-1 Hi Elmer, When trying to shutdown DB2, did you try to first stop (P xxxxIRLM) then cancel it (C xxxxIRLM) for the affected DB2 subsystem? Another issue could be that DB2 was backing out a large UOW from a cancelled Remote Thread. If that's true, starting and stopping DB2 won't help (unless you're V6), DB2 will simply continue the backout when it restarts. [...] 14597 25 15_Re: DRDA Access15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Fri, 19 May 2000 08:48:57 -0400380_us-ascii I do not believe you can reference 2 different locations in the same SQL statement Bob Lawrence DBA Boscov's Dept Stores

-----Original Message----- From: Anli Chao [SMTP:anlichao@MS14.HINET.NET] Sent: Friday, May 19, 2000 2:48 AM To: DB2-L@RYCI.COM Subject: DRDA Access

<< File: ATT00000.txt; charset = big5 >> << File: ATT00001.htm; charset = big5 >> [...] 14623 87 18_Re: ddf won't stop19_Elmer J. Valenzuela18_elmer@KFUPM.EDU.SA31_Fri, 19 May 2000 15:46:13 +0300464_iso-8859-1 Hi Rick, No, we did not stop IRLM. We thought that IRLM is shutdown by DB2 himself. I guess our main concern is really how to force stop DDF when mode(force) fails. This way we can restart DDF without bringing down the whole of DB2.

thanks./elmer ----- Original Message ----- From: DAVIS, RICK (SBCSI) Newsgroups: bit.listserv.db2-l To: Sent: Friday, May 19, 2000 3:37 PM Subject: Re: ddf won't stop [...] 14711 26 34_Re: DB2 for OS/390 as a DSS DBMS ?16_Michael G Vanner19_mvanner@CANTIRE.COM31_Fri, 19 May 2000 09:09:20 -0400558_us-ascii We are using DB2 on OS/390 (V5.1) as a Data Warehouse platform. The data is stored in a star schema (mostly), with some data store in a normalized relational form. We have end user access thru MS-Access, IDS, SAS, and QMF. We are in the process of adding Cognos products to the mix for more analytical processing. Becuase DB2 is used to store data, we have spent a considerable amount of time and resources working with the users to ensure any SQL that goes against DB2 is as efficient as possible. The DB2 subsystem in question runs on an its [...] 14738 113 17_ADUG June 2, 200014_C. L. Phillips25_clphillips@MINDSPRING.COM31_Fri, 19 May 2000 09:27:15 -0400 14852 91 18_Re: ddf won't stop19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 19 May 2000 08:28:50 -0500584_ISO-8859-1 Hi Elmer, I've never done this so caution is advised because I don't know any of the impact. After canceling all remote threads, you could try to P and then C xxxxDIST. 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 [...] 14944 310 24_Re: Access path question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Fri, 19 May 2000 09:35:20 -0400324_iso-8859-1 Sanjeev,

Thanks for your reply. You are right about preventive the transitive closure by changing the '=' predicate to 'IN' predicate, but that does not change the order of table join. The large table2 remains the outer table, and the matching column is reduced to 1 from 3. Here is the first explain [...] 15255 61 39_Re: Just a curiosity about datasharing.14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Fri, 19 May 2000 06:58:40 -0700530_iso-8859-1 Toine, One other thing that must be taken into account BEFORE the tablespaces are switched over in this manner is that ALL tablespaces should be reorged. There are 2 reasons for this, the first is to avoid the problem you mentioned regarding OBD's marked as variable due to ALTER TABLE ADD COLUMN, and the second being to clean up any rows from DROPPED TABLES (only a concern for SIMPLE tablespaces). Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 15317 30 57_Is posible recover a tablespace without prior Image copy?12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Fri, 19 May 2000 07:40:20 PDT520_- Hi folks:

I not pretty sure but I don't where It was here in the list or outside where I read something about that is posible to recover a tablespace without prior full image copy. Here in our shop we have a new huge aplication from IBM (HOST ON-DEMAND) and it makes daily a huge amounts of insert. One's of their tablespaces has about 2M actives pages (32k size) and the owner of the aplication is asking me to set up an image copy backup. I just want to be sure if we would be able to recover the entire [...] 15348 65 61_Re: Is posible recover a tablespace without prior Image copy?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 19 May 2000 10:30:02 -0500417_ISO-8859-1 Harbry, You didn't say what version and platform of DB2. I'll assume DB2/OS390 because I don't know if Host On Demand would indicate it. To recover a tablespace without image copies requires that you have every log since it was created, including the one in use at creation of the tablespace. I would consider daily incremental image copies, then merged into a full copy and a weekly full image copy. [...] 15414 59 61_Re: Is posible recover a tablespace without prior Image copy?12_Moreno, Jose19_Jose_Moreno@BMC.COM31_Fri, 19 May 2000 12:05:40 -0500547_ISO-8859-1 Harbry,

If you use the standard recovery procedures for DB2, there is no way to Recover a TS without prior Full IC. Exept if you can store all the logs since the TS was created. In your case this is not usable because with such amount of logging your recovery will run for weeks ! The alternate solution is to use BMC's Recove Plus which has a unique backward recovery capability. Instead of Restoring the IC and then apply the log to the PIT you want to recover to it unapply (undo records) the log from the current to the [...] 15474 104 18_Re: ddf won't stop12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 19 May 2000 09:09:00 -0700560_us-ascii There's also a couple of MVS level commands...CANCEL which can be followed by a FORCE command which really pulls the rug out from DB2. If you specify the xxxDIST address space, it will come down. There's also the OMEGAMON product's KILL command. Depending on exactly what was going on in the system when I had to use these commands, I've had to IPL a couple of times to get DB2 back up, but since you're having to IPL anyway, you don't have a lot to lose by trying them. Tim --- "DAVIS, RICK (SBCSI)" wrote: > Hi Elmer, > [...] 15579 25 39_Subject: precompile c for db2 v6 on AIX17_Edward.Bullerwell27_Edward.Bullerwell@SOBEYS.CA31_Thu, 18 May 2000 15:53:00 +0000266_- My apologies if this is a simple question ... I'm and oracle dba with no db2 training as yet. I'm trying to compile a c program with imbedded sql. The db2 sql manual and other sources shows the use of the command precompile or prep , but I get error message: [...] 15605 39 61_Re: Is posible recover a tablespace without prior Image copy?20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Fri, 19 May 2000 16:03:19 EDT562_US-ASCII Hi Harbry, The IBM Recover utility will recover the tablespace using only the logs since the creation of the tablespace. You will get message dsnu510I and dsnu549I telling you it is using only the logs and you will received a rc=4. This job would run forever (at least for a very long time) and that is if you have all the logs since the creation of the Tablespace. We have a tool that will display all the different recovery points of a Tablespace by RBA, Timestamp, Tocopy, ETC., ETC. It will also Automate the generation of the recovery Job JCL [...] 15645 59 26_Re: ProEdit vs. RC/Update?12_Franz, Scott31_sfranz@POSTOFFICE.MUSICLAND.COM31_Fri, 19 May 2000 14:55:19 -0500601_iso-8859-1 We have RC/Update and are just in the process of upgrading it. However, the most current version does not exploit DB2 v 6 features either, it only tolerates.

Scott

> -----Original Message----- > From: Steve G [mailto:steve_g@USA.COM] > Sent: Thursday, May 11, 2000 11:59 AM > To: DB2-L@RYCI.COM > Subject: Re: ProEdit vs. RC/Update? > > > Andrea, > > They were told that ProEdit will not exploit DB2 6.1 > features, and were encouraged to switch to RC/Update. > > If you know of differences between ProEDIT and RC/Update, > please reply. > > Thanks! > > > ------Original [...] 15705 40 18_Distributed Binds?0_18_damcon2@US.IBM.COM31_Fri, 19 May 2000 16:57:45 -0400584_us-ascii Hi all,

I'm not having much success finding good examples of binds for distributed access using DRDA protocol. The piece that is puzzling me is... at each location how is the relationship between the local package and the remote package handled? From what I can tell (which I'm sure isn't accurate) it seems that two local binds need to be done; one for the package and then one for the plan to include the remote package. And then at the remote location only one bind for the package. But that's where it's fuzzy. Do the package names, collections, and/or plans [...] 15746 65 18_Distributed Binds?0_18_damcon2@US.IBM.COM31_Fri, 19 May 2000 17:01:08 -0400524_us-ascii Sorry, forgot to mention this is DB2 V6 for OS/390.

Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - BMW -----------------------------------------------------

---------------------- Forwarded by Jackson Reavill/Tampa/Contr/IBM on 05/19/2000 05:00 PM ---------------------------

Jackson Reavill 05/19/2000 04:57 PM [...] 15812 127 19_FW: Sort pool usage10_STEVE HOWE15_SHOWE@BCBSM.COM31_Fri, 19 May 2000 11:18:18 -0400603_iso-8859-1 We could use some help with the following DB2 V5.1 issues:

> 1) Eliminate the extend failures with physical db2 temporary work files. > 2) Reduce the number of omegamon 'sort/merge workfile request denied'. > 3) Reduce the number of times we hit the vdwqt. > > The message we get for the extend failures is: > > dsnp007i -dsne- dsnpxtno - extend failed for > dsne.dsndbc.dsndb07.dsn4k01.i0001.a001. > rc=00d70027 ......etc > > Solutions: > 1)So far...adding more additional work files does not help. Were using 16 > with (1600,0) cylinders each and were going to add more. > > 2) [...] 15940 79 22_Re: Distributed Binds?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 19 May 2000 17:20:16 -0400507_iso-8859-1 Jay:

Its been a while since I set up remote binds , but from what I can recollect it works as follows:

Lets say you have 4 locations as L1 , R1, R2 & R3 where L1 is the local DB2 accessing data from remote R1, R2 & R3. Then you would bind your package to remote locations R1, R2 and R3 using collections CR1, CR2 or CR3 (they can all have the same name) and a local bind package to location L1 using collection CL1 (which can also have the same name as the remote collection). [...] 16020 20 17_Big DB2 V7 change11_Burch, Todd18_Todd_Burch@BMC.COM31_Fri, 19 May 2000 16:22:05 -0500365_ISO-8859-1 For those that may have missed it, IBM annouced at IDUG on Monday that the utilities for DB2 V7 for OS/390 would be a separate charge-for feature and that there would be no "common utility" support delivered with DB2 V7. ("Common utility" being defined as a utility that is generally available from a variey of vendors like LOAD, REORG, COPY, etc.) [...] 16041 73 19_FW: Sort pool usage14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 19 May 2000 17:36:21 -0400393_ISO-8859-1 Steve, The critical piece you omotted is the size of your VP for the DB07 objects. You want to be careful about increasing the 8Meg sortpool size, since this a a max per user.... so multiple users may cause the system paging rate to increase. Re # 2 below, this is a direct indication that the number of buffers for the pool is too low. Re # 3 - hitting vdwqt isnot a problem. [...] 16115 15 21_Re: Big DB2 V7 change26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Fri, 19 May 2000 16:50:14 -0500347_- Blood suckers comes to mind.... 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. 16131 80 21_Re: Big DB2 V7 change0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 May 2000 16:46:47 -0500317_us-ascii Todd, No, I had not heard that! $#@$% I guess my main question would simply be "how much"? Are they perhaps going to reduce the base cost of DB2 V7, and introduce new charges for the utilities? (as in revenue-neutral?) Or, is everything going up? What is the "net result"? Do they have any numbers yet? [...] 16212 96 32_Online REOG ABEND 04E RC00E4000910_Wang.James24_Wang.James@AAA-CALIF.COM31_Fri, 19 May 2000 15:08:03 -0700462_iso-8859-1 Does anyone have experience with this before?

Running DB2 V5.1. Doing online reorg against a tablespace. Reorg done OK. Indexes built OK. Switch failed with RESOURCE NOT AVAILABLE, REASON=X'00C9008E'. .DSNT500I DSNUGBAC - RESOURCE UNAVAILABLE . REASON 00C200EA . TYPE 00000201 . NAME ......

James Wang Sr. Systems Programmer Automobile Club of Southern California (714) 850-2851 Wang.James@aaa-calif.com 16309 308 24_Re: Access path question10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Sat, 20 May 2000 03:15:01 GMT290_us-ascii Hi Sanjay, You must have tried a lot of combinations but i would like to give another try to it.The problem which we could see is that index ILNPLRX1 is used to scan the table2 and then joined with table1.Did you try disabling usage of this index by (1=1) or (0=1) technique. [...] 16618 125 18_Re: ddf won't stop19_Elmer J. Valenzuela18_elmer@KFUPM.EDU.SA31_Sat, 20 May 2000 08:05:03 +0300351_iso-8859-1 Tim, Rick, I think the next time that happens, though I certainly hope that it wouldn't, I'll see if taking XXXDIST will do it. Thanks a lot../elmer ----- Original Message ----- From: tim malamphy Newsgroups: bit.listserv.db2-l To: Sent: Friday, May 19, 2000 7:09 PM Subject: Re: ddf won't stop [...] 16744 41 21_Re: Big DB2 V7 change19_Elmer J. Valenzuela18_elmer@KFUPM.EDU.SA31_Sat, 20 May 2000 08:25:33 +0300412_iso-8859-1 I guess my question is 'why?'. I don't think anyone could get by without these utilities. It's like taxing the air you breathe. who stands to gain here? other utility vendors? hmmm.....i wonder.

../elmer ----- Original Message ----- From: Burch, Todd Newsgroups: bit.listserv.db2-l To: Sent: Saturday, May 20, 2000 12:22 AM Subject: Big DB2 V7 change [...] 16786 59 28_Re: Linklist libraries & DB210_Leo Flores23_leoflores@EARTHLINK.NET31_Mon, 15 May 2000 11:33:54 -0700332_us-ascii Hi Mike,

When I first started out as a DBA I remember that having SDSNLOAD concatenated before the SDSNEXIT caused us some problems. A consultant that was working for us discovered during some research that the SDSNEXIT should be concatenated before the SDSNLOAD. We switched them and our problem was resolved. [...] 16846 64 37_Links about DB2 for OS/390 (05/20/00)6_DB2usa18_db2usa@HOTMAIL.COM31_Sat, 20 May 2000 16:23:25 -0500595_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.multimania.com/db2usa/eliendb2.htm - Français: http://www.multimania.com/db2usa/liendb2.htm

Last update on Saturday, May 20th 2000 ======================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliste.htm RevealNet added a DB2 Tip of the Month page on his website. BMC launched dbazine, an online portal for different DBMS including a page about DB2. DB2 Migration's IBM website : technical papers and présentations. [...] 16911 24 21_Re: Big DB2 V7 change0_17_JTonchick@AOL.COM29_Sat, 20 May 2000 17:55:15 EDT363_US-ASCII If IBM is charging separately for utilities, but the overal price is still within the cost of V6, then our financial department will not have a problem.

But, if V7 base price goes up AND they charge separately for utilities on top of that, it will blow out our budget.

How can you market a database engine without supplying utilities? [...] 16936 100 22_Re: Distributed Binds?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sun, 21 May 2000 17:25:12 +1000654_iso-8859-1 Jay,

There doesn't need to be any local package.

Program runs at location L1, I assume DBRM source is here as well Data exists at location L2

at L1 run: BIND PACKAGE(L2.) MEMBER(...) LIBRARY(...) etc.

BIND PLAN() PKLIST(L2..*) CURRENTSERVER(L2) ...

RUN PROGRAM(...) PLAN()

Now, you can get fancier: - if you leave "CURRENTSERVER(L2)" off the plan bind, the initial current server for the plan will be L1. You'll have to get a program to: EXEC SQL SET CURRENT SERVER = L2 END-EXEC SET CURRENT SERVER is one of a small set of SQL statements that does not need a package [...] 17037 62 21_Re: Big DB2 V7 change9_jim.szabo22_jim.szabo@MCIWORLD.COM31_Sun, 21 May 2000 06:28:00 -0400521_iso-8859-1 I think that there may be a hidden issue here: I know of one third-party vendor, that when you currently use their copy utility to backup the DB2 catalog, they just call the IBM utility under the covers - how will they be able to do this now? Either IBM opens up their closet and shows the third-party vendors the interface required to manage catalog/directory, or all accounts wishing to keep their third-party products would have to buy both (!). Here's my early handicapping on the utilities involved: [...] 17100 26 17_Consistency Token9_Anli Chao23_anlichao@MS14.HINET.NET31_Sun, 21 May 2000 23:11:55 -1000471_us-ascii Does anyone know how to interpret consistency token in DBRM or in SYSIBM.SYSPACKAGE?

We are running DB2 V6. DBRMs have been included into different packages. We would like to compare the time stamp in the load module with the consistency token in the package when SQL code -805 occurs. It is a CICS transaction, and the DBA claimed a BIND has performed after the load module was link edited. By the way, -805 has become a frequent issue in our site. [...] 17127 45 12_DRDA package15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Sun, 21 May 2000 17:04:04 -0400434_iso-8859-1 To all the list members,

We are trying to use DRDA to select and update DB2 tables in mainframe from Windows NT. OS version is 2.6 and DB2 version is 5.01. We used Grandvu's product to create a static package in DB2 from Windows NT. But when we execute this package, we are getting the column name in output as COL1, COL2...etc. Does anybody know how to get the actual column name from the static SQL we bound. [...] 17173 54 21_Re: Consistency Token10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Sun, 21 May 2000 17:48:09 -0500749_iso-8859-1 Use the below SQL

SELECT strip(COLLID)]]'.']]NAME , hex(CONTOKEN) FROM SYSIBM.SYSPACKAGE WHERE NAME = '???'

HTH



______________________________ Reply Separator _________________________________ Subject: Consistency Token Author: owner-db2-l@RYCI.COM at INTERNET Date: 05/21/2000 11:05 AM



Does anyone know how to interpret consistency token in DBRM or in SYSIBM.SYSPACKAGE?

We are running DB2 V6. DBRMs have been included into different packages. We would like to compare the time stamp in the load module with the consistency token in the package when SQL code -805 occurs. It is a CICS transaction, and the DBA claimed a BIND has performed after the load module was link edited. By [...] 17228 23 21_Re: Big DB2 V7 change12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 21 May 2000 18:35:42 -0500449_- We don't know the numbers yet, but wanted to tell customers as early as possible. There is a paragraph or two in the announcement that you can get from www.ibm.com/ibmlink or www.ibm.com/isource

The paragraph is vague, and that's not an accident, but rather the reality of what we know now. The process is complicated, and there are no easy answers. When the process is completed, we'll have another announcement with the information. [...] 17252 58 28_Re: Linklist libraries & DB212_Roger Miller19_millerrl@US.IBM.COM31_Sun, 21 May 2000 18:57:32 -0500652_- On Mon, 15 May 2000 06:34:17 -0400, Mike O'Neill wrote:

> We have tried to standardize our environment and JCL for DB2 by doing > the following: > - create a subsystem specific library for each DB2 subsystem - > concatenates first in the steplib. > - merging the target libraries into a single base library that is in > the linklist: > DB2.DSN610.SDSNLOAD > DB2.DSN610.SDSNEXIT > DB2.DSN610.SDSNLINK > DB2.DSN610.SDXRRESL > DB2.DGO.V6R1M0.SDGOLINK > DB2.DGO.V6R1M0.SDGOLOAD > > My problem/question is why this will not work with the DB2PM collector > task, and the nnnnSPAA (stored procedures) task. > > I [...] 17311 16 21_Re: Big DB2 V7 change26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Sun, 21 May 2000 19:20:24 -0500449_- It seems more of a marketing affectation that a technical "paradigm shift". Guess we'll find out more as the blanks get pencilled in. 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. 17328 45 21_Re: Big DB2 V7 change14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Sun, 21 May 2000 20:24:32 -0400325_iso-8859-1 There are many issues here that have not been presented on this listserv. There is a large list of new utilities, and greatly enhanced other utilities, presented in different packages. The one package, with most of the old and new utilities, etc., is on a 60 to 90 day try and buy (depending on the utility). [...] 17374 12 58_Patch for Running JAVA Stored Procedures for DB2 on OS/3909_SrinivasG17_SRINIVASG@INF.COM31_Mon, 22 May 2000 08:40:17 +0530266_iso-8859-1 Any news yet?

================================================ 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. 17387 70 26_Re: ProEdit vs. RC/Update?9_Rob Crane22_racrane@CONCENTRIC.NET31_Sun, 21 May 2000 21:33:11 -0600543_us-ascii The most current release of RC/Update is tape P99C and this codes does exploit DB2 V6.

-Rob Crane

"Franz, Scott" wrote: > > We have RC/Update and are just in the process of upgrading it. However, the > most current version does not exploit DB2 v 6 features either, it only > tolerates. > > Scott > > > -----Original Message----- > > From: Steve G [mailto:steve_g@USA.COM] > > Sent: Thursday, May 11, 2000 11:59 AM > > To: DB2-L@RYCI.COM > > Subject: Re: ProEdit vs. RC/Update? > > > > > > Andrea, > > > > They were [...] 17458 89 21_Re: Consistency Token27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Mon, 22 May 2000 00:55:12 -0400552_iso-8859-1 Anli,

I You can get the consistency token from the DBRM and getting the hex form of CONTOKEN

If I query SYSIBM.SYSPACKAGES, the contoken column looks something like this : CONTOKEN -------- LF ñuÐ ;c8 ÿ1Ø Ê b ÈKp H8Sn "ï 4{\ ¼µt øßZeÖwh ?Or ôðÚ

Use HEX scalar function on CONTOKEN to get the hex value of it. 2. Use LEVEL precompiler option if you do not change the SQL stmts in your program. The value you supply in this will be used by DB2 to generate the consistency token. You can do away with your bind. 3. It [...]