1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 1999, week 4 2 27 26_DB2 Connect Personel vs EE32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Mon, 22 Nov 1999 12:26:10 +0200500_- Hello List,

We have a couple of 2 tier applications written with C and access to OS/390 DB2 through DB2 Connect Personel edition with TCP/IP. They have only one or two users.

Now a new app. is coming and the number of users will be a lot.. My concern is i may think about Connect Gateway in this case. In this case clients will have CAE instead of PE. What are to pros and cons for it ? Would you please send your comments for Performance , Systems Management, Installation etc. [...] 30 24 27_Compuware DBA-XPERT for DB26_JS DBA18_js_dba@HOTMAIL.COM29_Mon, 22 Nov 1999 10:53:46 GMT636_- We are currently doing a DB2 change/migration tool evaluation. One of the tools that we are investigating is Compuware's DBA-XPERT for DB2.

Any feedback on the product, whether positive or negative would be most welcome.

John Powell DBA

J Sainsbury's PLC

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.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. 55 44 31_Re: Compuware DBA-XPERT for DB27_Gustavo26_lozano@BANDEIRANTES.COM.BR31_Mon, 22 Nov 1999 09:28:14 -0300783_iso-8859-1 We have been using DBAXPERT for DB2 for over 4 years... Gustavo Lozano (lozano@bandeirantes.com.br) - Banco Bandeirantes São Paulo - Brazil

-----Mensagem original----- De: JS DBA Grupos de notícias: bit.listserv.db2-l Para: DB2-L@RYCI.COM Data: Segunda-feira, 22 de Novembro de 1999 9:19 Assunto: Compuware DBA-XPERT for DB2



>We are currently doing a DB2 change/migration tool evaluation. >One of the tools that we are investigating is Compuware's DBA-XPERT for DB2. > >Any feedback on the product, whether positive or negative would be most >welcome. > >John Powell >DBA > >J Sainsbury's PLC > >______________________________________________________ >Get Your Private, Free Email at http://www.hotmail.com [...] 100 45 31_Re: Compuware DBA-XPERT for DB217_Anthony Ray Gould17_agould@UAKRON.EDU31_Mon, 22 Nov 1999 07:42:30 -0500412_us-ascii We've used DBA/XPERT for about 5 years.

If you have an ERP like PeopleSoft as we do you may want to reconsider because the current version of DBA/XPERT has trouble handling large numbers of objects during change/migration or clones. You end up having to deal with smaller migrations based on particular databases or tablespaces instead of creating a change/migration for the entire project. [...] 146 42 0_18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Mon, 22 Nov 1999 06:52:28 -0600633_iso-8859-1 Pravat, To tweak is similar to twiddle. One twiddles bits to improve assembler, but one tweaks performance. Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of [...] 189 12 10_Sign-off !0_28_Giuseppe_Di_Donna@CARTASI.IT31_Mon, 22 Nov 1999 13:53:22 +0100267_us-ascii "SIGN-OFF DB2-L"

================================================ 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. 202 126 21_Re: ER Model Exercise18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Mon, 22 Nov 1999 07:49:18 -0600329_iso-8859-1 Linda, Your reply is dead-on. My "children" are 1-0 or 1-1 only. However, you said: "This required one of the three attributes to be supplied, when all 3 were missing on the insert the insert failed." Didn't you mean, "This required at LEAST one of the three attributes...? I may have one value for each "child". [...] 329 44 42_UDB v6.1 Control Center - Client Login ID?0_22_BILL_GALLAGHER@PHL.COM31_Mon, 22 Nov 1999 09:09:23 -0500341_us-ascii Hi,

I'm trying to investigate a problem in which we are unable to perform certain functions against a UDB database using the control center, even though we are set up as SYSADM's for the instance in which the database resides.

Our environment: UDB v6.1 running on AIX; Control Center v6 running on an NT desktop. [...] 374 42 31_Re: Compuware DBA-XPERT for DB214_McCombs, Terry20_tmccomb@SEDGWICK.GOV31_Mon, 22 Nov 1999 08:08:45 -0600461_iso-8859-1 We have used DBA-Xpert for about 4 years, and I'm very pleased with the product.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of JS DBA Sent: Monday, November 22, 1999 4:54 AM To: DB2-L@RYCI.COM Subject: Compuware DBA-XPERT for DB2



We are currently doing a DB2 change/migration tool evaluation. One of the tools that we are investigating is Compuware's DBA-XPERT for DB2. [...] 417 102 43_DB2 Tridex User Group - NYC - Dec 2nd, 19990_17_glloyd@US.IBM.COM31_Mon, 22 Nov 1999 07:15:19 -0700526_iso-8859-1 FYI...



TRIDEX Quarterly User Group Meetings DON?T JUST KEEP UP ? STAY AHEAD! All Meetings are FREE next meetings March 30, and June 8, 2000.

Quarterly Meeting December 2, 1999 8:30 ? 3:15 IBM Corporation 590 Madison Ave., NY, NY Sixth Floor Meeting Room

KEYNOTE PRESENTATIONS Explaining SQL Performance, Randy Ebersole, DB2 Technical Specialist, Computer Associates, Inc., DB2 UDB Raises the Bar in Optimizer Technology, Stephen Brobst, Managing Partner, Strategic Tech. & Systems [...] 520 36 18_Inconsistent Index13_Moss, William15_MossW@AETNA.COM31_Mon, 22 Nov 1999 09:27:17 -0500286_- I recently had a problem with an index which I resolved by recovering the index. It was a fairly large index ( 7 gig ) and the recovery took 90 minutes. Would it have been possible to fix the problem more quickly by using the Repair Uitlity ? We are at DB2 for OS/390 Version 5. [...] 557 59 15_DB2 and OPS/MVS22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM31_Mon, 22 Nov 1999 09:42:30 -0500498_iso-8859-1 Does anyone have any information, hints, procedures, etc. about having the OPS/MVS product controlling the auto start and stop of DB2? I believe OPS/MVS is a Computer Associates product. We have had some problems when OPS/MVS is controlling the auto stop and start. I'd like to find out if this is an incompatibility between the two products or if it is a procedural issue. As usual it is up to our side to prove that it is not a DB2 problem. Thanks in advance for any and all responses. 617 56 22_Re: Inconsistent Index13_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK31_Mon, 22 Nov 1999 14:57:00 -0000572_- William quite possibly but being by religion a Devout Coward I would not use the 'Repair Utility' unless you are very very sure of what you are doing.

Ian McDonald DB2 DBA Abbey National ---------- From: Moss, William To: DB2-L@RYCI.COM Subject: Inconsistent Index Date: 22 November 1999 14:27

I recently had a problem with an index which I resolved by recovering the index. It was a fairly large index ( 7 gig ) and the recovery took 90 minutes. Would it have been possible to fix the problem more quickly by using the Repair Uitlity ? We are at DB2 [...] 674 83 59_Apology Re: 24:00:00 - was APAR for DB2 Timestamp on OS/39014_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Mon, 22 Nov 1999 10:05:28 -0500350_us-ascii Sorry. I realize after I sent that that it was a dumb thing to say. I forgot the scope of the group I was talking to.

As you may have gathered from my earlier comments, I feel that the allegorical signs at the U.S. border say "Here be Dragons" on both sides. Hopefully discussion groups like this help ameliorate that situation. [...] 758 56 49_=?iso-8859-1?B?UulmLiA6IERCMiBhbmQgT1BTL01WUw==?=0_26_denzil.coulter@UNICIBLE.CH31_Mon, 22 Nov 1999 16:02:48 +0100463_iso-8859-1 We are using OPS/MVS to successfully manage the stop/start of our DB2 subsystems and have been for many years. Recent enhancements, to include xxxxSPAS, caused no problems to our OPS guys. HTH







"Kennedy, Tom - Cendant" @RYCI.COM> le 22.11.99 15:42:30

Veuillez répondre à DB2 Data Base Discussion List

Envoyé par : DB2 Data Base Discussion List [...] 815 33 34_Changing password from DB2 Connect13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR31_Mon, 22 Nov 1999 17:32:28 +0200384_windows-1254 From the DB2 discussion list we have some answers related to the subject. We have studied our environment and we could not match the answers with our site. To change the passwords there are two choices to use: MVS/ESA SP and CICS-APPC PEM by using SNA for communication. We have seen that it is possible to use the Cics option for our site. Our CICS version is 4.1. [...] 849 34 13_ADABAS to DB211_Joan Vragel19_Joan_Vragel@RGE.COM31_Mon, 22 Nov 1999 10:29:43 -0500509_us-ascii I've seen in the archives, threads about converting from one database to another - the pros/cons, etc, but it's all been personal opinion. What I'm looking for is actual hard facts from those shops that have actually converted from Adabas to DB2.

- how did the sizing of DB2 compare with Adabas? - what performance gains did you see in response time, both online and batch. - were your software costs truly reduced by dropping Adabas? - were there other products required to support DB2? [...] 884 18 17_Re: ADABAS to DB212_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Mon, 22 Nov 1999 09:47:06 -0600485_us-ascii You will need some DB2 Management tools. You have 2 real choices, BMC or CA-Platinum. There are others out there but these two are the main contenders. For performance, it depends. You will have to tune that as you go. You might want to consider Mullins approach. Put Catalog in BP0, Catalog indexes in BP1, Omegamon or Apptune or Tmon in BP2, Others in BP3, Indexes for the others in BP4, Specific application databases(need special attention) in BP5. Things like that. [...] 903 191 31_Re: Compuware DBA-XPERT for DB216_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Mon, 22 Nov 1999 10:50:54 -0500344_- We had that tool, DBA-Expert, in 2 or 3 years ago. We were NOT pleased with it. Either Platinum or BMC have better -- MUCH better tools. Problems we encountered were:

- Could NOT handle views on views. Would leave them out when we were creating strategy's for table changes. Thus when you dropped the table, you LOST these views. [...] 1095 202 31_Re: Compuware DBA-XPERT for DB216_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Mon, 22 Nov 1999 10:56:51 -0500622_- I have been using Platinum with PeopleSoft for several years and have had no problems with sizes. From my past experience with DBA-Expert, I would quit before I would even attempt to use it with Peoplesoft stuff.

Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: Anthony Ray Gould [SMTP:agould@UAKRON.EDU] > Sent: Monday, November 22, 1999 7:43 AM > To: DB2-L@RYCI.COM > Subject: Re: Compuware DBA-XPERT for DB2 > > We've used DBA/XPERT for about 5 years. > > If you have an ERP like PeopleSoft as we do you may want to reconsider > because [...] 1298 32 32_Sequence numbers in spufi output12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Mon, 22 Nov 1999 11:12:17 -0500326_us-ascii Dear List,

In a DB2 V5 for OS/390 environment, is there any way whatsoever to get SQL to generate sequential row numbers for the result of any SQL SELECT statement in SPUFI or DSNTEP2 ... e.g For a statement like:-

Select col1 from table1;

I would like to generate the output to look like:- [...] 1331 68 22_Re: Inconsistent Index12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 22 Nov 1999 10:07:00 -0500413_- We had an issue at one time where a DBA used REPAIR to remove recover pending on some indexes during a TYPE 1 to TYPE 2 conversion. We started getting Directory and Catalog inconsistencies on one of the indexes. IBM believed that the repair caused the problem. We ended up having to resync the directory and catalog using DBD REBUILD. We use REPAIR very sparingly here and I would suggest you do the same. [...] 1400 67 22_Re: Inconsistent Index15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Mon, 22 Nov 1999 10:18:14 -0600391_ISO-8859-1 Bill,

I think the question would be "What do you repair?" This error means you have a row without an index entry. Could you afford to delete this orphaned row? Would this affect an RI structure? Is this the only row in error?

Repairing the index to point to this row might not be impossible, but chances of doing it correctly the first time would be quite low. [...] 1468 166 21_Re: ER Model Exercise17_Linda F. Claussen18_lindafc@NETINS.NET31_Mon, 22 Nov 1999 10:26:55 -0600657_ISO-8859-1 Rick,

That's exactly what I mean. I requires at least 1 of the 3. Sorry about the wording.

Linda F. Claussen Claussen & Associates DB2 Consulting and Training lindafc@netins.net http://www.netins.net/showcase/lclaussen/ Phone: (319) 343-3216 Office: (319) 847-1985

---------- > From: DAVIS, RICK (SWBT) > To: DB2-L@RYCI.COM > Subject: Re: ER Model Exercise > Date: Monday, November 22, 1999 7:49 AM > > Linda, > Your reply is dead-on. My "children" are 1-0 or 1-1 only. However, > you said: > "This required one of the three attributes to be supplied, when all 3 were > missing on the insert the [...] 1635 38 19_Re: DB2 and OPS/MVS13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Mon, 22 Nov 1999 10:34:03 -0600456_us-ascii We have had OPS/MVS start all our products including 3 DB2 subsystems for 8 or 9 years. The only problem we ever had was initially setting up the timing between DB2 and IMS/TM so that IMS would recognize the DB2 subsystems. Once the OPS person figured that out, we have had no problems with it. We also use it to cancel QMF users who are interfering with online transactions and notify the DBA when there are contention problems. It's great! [...] 1674 55 36_Re: Sequence numbers in spufi output14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 22 Nov 1999 11:39:36 -0500582_iso-8859-1 Actually, DSNTEP2 does print out the sequence number in front of the row.

Thanks, Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Kapil Mathur > Sent: Monday, November 22, 1999 11:12 AM > To: DB2-L@RYCI.COM > Subject: Sequence numbers in spufi output > > > Dear List, > > In a DB2 V5 for OS/390 environment, is there any way > whatsoever to get > SQL to generate sequential row numbers for the result of any > SQL SELECT statement in SPUFI or DSNTEP2 ... > e.g For a statement like:- > > [...] 1730 90 22_Re: Inconsistent Index15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Mon, 22 Nov 1999 10:43:55 -0600345_ISO-8859-1 My colleague, Michael Murley, brought up a good point:

"The 00C90206 points out an inconsistency between the table space and the index. Was the index wrong, or was it really the table space that was wrong? If the latter, the rebuild only propagated the corruption to the (previously correct) index."

Donna Di Carlo [...] 1821 62 31_Re: Compuware DBA-XPERT for DB20_22_Rohn.Solecki@MTS.MB.CA31_Mon, 22 Nov 1999 10:56:33 -0600547_us-ascii I've been using it for over a year now. It has the features and functions to do almost everything we want to do. But ... I've found that unless you know the package, you'll never find anything. The online help and printed manuals are writtin under the assumumption that you already are knowledgeable in the product so they are rather sparse. So unless you've taken their training course (I haven't had the opportunity) or you have someone onsite who knows the product, you'll spend a lot of time 'exploring' and 'discovering' stuff. [...] 1884 158 36_Re: Sequence numbers in spufi output12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Mon, 22 Nov 1999 11:58:45 -0500304_us-ascii

My mistake in asking the wrong question - you are correct.

What i am looking for is a way to refer to the "line number" in SPUFI to use as part of an expression on the select statement - i.e. to ultimately get the functionality of ROWNUM (in Oracle) into a DB2 SQL statement [...] 2043 172 8_Re: TIME24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Mon, 22 Nov 1999 11:21:12 -0800432_us-ascii Michael, I was recently in Seoul on a consulting engagement. I innocently asked one of my co-workers, a native Korean working for IBM with excellent skills in English (a language of which I have a crude working knowledge) how old he was, and he explained to me what I posted to Colin. I will try to get a fuller explanation, but my working assumption is Korean_age = ROUND(human_gestation_period) + years_post_uterus. [...] 2216 65 31_Re: Compuware DBA-XPERT for DB29_Jim Jones33_jim.jones@INGRAMENTERTAINMENT.COM31_Mon, 22 Nov 1999 11:41:10 -0800561_us-ascii John,

I've used DBAxpert for about 5 years, and find that it competes well with some of the more popular, pricier tools. The 5.2.3 release handles a lot of objects in a database fairly well, such as PeopleSoft, better than prior releases, but you have to "play" a bit. The documentation is weak, but it handles migration of databases and changes to a few or a lot of tables well. Also the utility manages is a strong tool to automate Image Copy, Runstat and Reorg utility generation. And it is a good to to use to generate one time utility [...] 2282 85 38_Re: Changing password from DB2 Connect0_15_leon@CA.IBM.COM31_Mon, 22 Nov 1999 12:48:00 -0500607_us-ascii



Murat, everything you need to know about changing a password on OS/390 from a PC or a UNIX workstation with DB2 Connect is described in "Appendix B. Changing Your MVS Password" in the "DB2 Connect User's Guide" (typically installed as part of DB2 Connect install).

If you are unfortunate enough not to be able to use native TCP/IP support then you will need to have either APPC/MVS or CICS/ESA running and configured on your S/390. You do not need these products nor do you need any extensive configuration when you use native TCP/IP support with DB2 for OS/390 V5.1 or [...] 2368 60 38_Re: Changing password from DB2 Connect0_15_leon@CA.IBM.COM31_Mon, 22 Nov 1999 18:12:48 +0000607_us-ascii



Murat, everything you need to know about changing a password on OS/390 from a PC or a UNIX workstation with DB2 Connect is described in "Appendix B. Changing Your MVS Password" in the "DB2 Connect User's Guide" (typically installed as part of DB2 Connect install).

If you are unfortunate enough not to be able to use native TCP/IP support then you will need to have either APPC/MVS or CICS/ESA running and configured on your S/390. You do not need these products nor do you need any extensive configuration when you use native TCP/IP support with DB2 for OS/390 V5.1 or [...] 2429 40 12_-904 abend !27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Mon, 22 Nov 1999 23:00:41 +0530470_iso-8859-1 Hi !

I am working on os390 with DB2 V 5 for MVS.

I seem to be getting a real weird problem. I have a program which uses cursors to insert some data. I have two databases with qualifiers TDP8 and TDPF qualifiers. When I bind it to TDP8 and run the job, I get a -904 abend (resource unavailable) saying that there was a problem with the fetch cursor whereas if I bind it to the TDPF qualifier , the job goes through fine without any problem. [...] 2470 69 16_Re: -904 abend !16_Michael McCarthy24_Michael_McCarthy@DTC.ORG31_Mon, 22 Nov 1999 13:59:59 -0500325_us-ascii Deepak,

A resource can be unavailable for many reasons. You must check the DB2 REASON code associated with the -904 error. the database containing the TDP8 qualifier may be in a STOPped, RECV pending, etc. state. Display the database to find out. The REASON code will tell you exactly what the cause is. [...] 2540 62 16_Re: -904 abend !14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 22 Nov 1999 14:04:48 -0500548_iso-8859-1 Its very much possible... you're going against 2 different sets of resources - Whats the reason code associated with the -904 ?

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Thakral, Deepak Kumar (CTS) > Sent: Monday, November 22, 1999 12:31 PM > To: DB2-L@RYCI.COM > Subject: -904 abend ! > > > Hi ! > > I am working on os390 with DB2 V 5 for MVS. > > I seem to be getting a real weird problem. I have a program which uses > cursors to insert some data. I have two [...] 2603 67 16_Re: -904 abend !17_LaLuzerne, George32_George.LaLuzerne@DOA.STATE.WI.US31_Mon, 22 Nov 1999 13:06:20 -0600472_iso-8859-1 Deepak, If you've re-bound the program with no errors and you are getting a -904 your problem is probably with your JCL. Make sure your run card (in your JCL) is pointing to the right (TDP8?) subsystem, and that you're pointing to the right loadlib for your load module.

HTH,

George LaLuzerne IS Project Leader Specialist Wisconsin Dept. of Admin. - Office of Computer Services (608) 264-7647 x47647 E-Mail : George.LaLuzerne@doa.state.wi.us [...] 2671 91 8_Re: TIME12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Mon, 22 Nov 1999 21:15:05 +0200615_x-user-defined Hi, With all due respect to people working on current date/time, how do we handle archeological times? why not be Y-99999999 ready ;)

Campbell James wrote: > > I would be interested in seeing a reference to any authority (as much as > anything on the Internet can be an "authority") claiming there was a > year zero. Here a counter authority (ie no year zero between BC and > AD): > > http://www.tondering.dk/claus/cal/calendar21.txt > (in sect 2.10.2) > > /* standard disclaimer */ > James Campbell > DBA > Hansen Corporation, Tooronga > +61 3 9261 1802 > james.campbell@energyis.com.au [...] 2763 60 0_12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Mon, 22 Nov 1999 21:17:38 +0200581_x-user-defined Hi, plainly speaking - If it doesn't work using some force then use brute force. (twiddle is for the smaller elements and tweak is for bigger ones. Sledgehammer is for the extreme cases ...;)

"DAVIS, RICK (SWBT)" wrote: > > Pravat, > To tweak is similar to twiddle. One twiddles bits to improve > assembler, but one tweaks performance. > 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. [...] 2824 70 16_Re: -904 abend !13_Purnima Patel18_ppatel@METLIFE.COM31_Mon, 22 Nov 1999 14:10:39 -0500551_us-ascii Deepak,

Just display the database.

-dis db(xxxxxxxx) limit(*)

This should tell you what is not RW or RO. If it is anythingelse you have a problem.











"Thakral, Deepak Kumar (CTS)" on 11/22/99 12:30:41 PM

Please respond to "DB2 Data Base Discussion List"

To: DB2-L@RYCI.COM cc: (bcc: Purnima Patel/Bsg/MetLife/US) Subject: -904 abend !





Hi !

I am working on os390 with DB2 V 5 for MVS. [...] 2895 52 16_Re: -904 abend !12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 22 Nov 1999 13:24:00 -0500369_- Look at SYSLOG and/or dsnMSTR to find what resource it is complaining about. Just some ideas: Check your HLQ.DSNDBD.DSNDB06.* data sets in the failing DB2. You may be trying to extend the DSNs and are at max extents or out of space on the volume. Also, if you are binding across DB2s, via DDF, make sure the connection between the DB2s is available and working. [...] 2948 134 22_Re: Inconsistent Index13_Paul Weissman21_Paul.Weissman@WDR.COM31_Mon, 22 Nov 1999 14:35:24 -0500376_ISO-8859-1 All:

We upgraded to DB2 5.1 in August this year, rather late than early, and had some problems with indexes. DB2 5.1 had a few errors in maintenance this year, which is unusual for a mature and stable release. In the parlance of systems programming, these errors are called "P.E. APARS". Timing is everything-- we just got ahead of IBM's bleeding edge. [...] 3083 17 23_DB2 V5 Looping (URGENT)11_Hamid Ullah14_hullah@IBM.NET31_Mon, 22 Nov 1999 13:22:54 -0800314_- I brought DB2 V5 down forcing DBM1 address shutdown. Now when I try to bring up DB2, I see the address spaces (all three) in SDSF (it seems that its looping). I get the msg DB2 not Operational, restry count(0). The last msg in DSNQMSTR is 'RESTART....COUNTS AFTER BACKWARD RECOVERY INFLIGHT=0, IN ABORT=0'. [...] 3101 29 31_Conversion from BMC to Platinum13_Horacio Villa17_hvilla@TTI.COM.AR31_Mon, 22 Nov 1999 16:55:57 -0300537_iso-8859-1 Hi,

I've migrated from BMC UNLOAD PLUS to PLATINUM FASTUNLOAD. Almost everything is working OK, but the following SELECT is giving me a hard time: SELECT LNETIMMOV, LNEFECVTO INTO LNETIMMOV, LNEFECVTO IF NULL VALUE (X'00000000000000000000') FROM BTU14000.MGT023

I don't know/haven't found how to convert BMC's "IF NULL VALUE (x'00000000000000000000') to PLATINUM. I tried DEFAULTIF(lnefecvto is null) initial(X'00000000000000000000') but didn't work. Anybody could give me some help? Regards, Horacio Villa [...] 3131 51 16_Re: -904 abend !16_Bachi Allamsetty27_Bachi.Allamsetty@NCMAIL.NET31_Mon, 22 Nov 1999 14:12:51 -0500519_us-ascii Hi Deepak !!!

did you check the reason code ? It might offer some insight to the problem.

"Thakral, Deepak Kumar (CTS)" wrote:

> Hi ! > > I am working on os390 with DB2 V 5 for MVS. > > I seem to be getting a real weird problem. I have a program which uses > cursors to insert some data. I have two databases with qualifiers TDP8 and > TDPF qualifiers. When I bind it to TDP8 and run the job, I get a -904 abend > (resource unavailable) saying that there was a problem with the fetch [...] 3183 21 27_error on describe to os/39011_David Ayers24_david.ayers@HIGHMARK.COM31_Mon, 22 Nov 1999 14:57:53 -0500556_us-ascii We have java code running on an NT client with websphere 3.0. The java code issues a connect to a DB2 connect database on an AIX with DB2 connect (SNA comm server 5.1 / DB2 connect 5.2 fp11). We can see the statement being passed to DB2 /os390 successfully. The statement seems to be in a loop when issuing a DESCRIBE 9this is executed thousands of times for the same statement) and ultimately the connection fails with a vtam error. I can issue a connect with the same SQL via the command center with no problem. Is there something specific [...] 3205 43 27_Re: DB2 V5 Looping (URGENT)0_23_Mike_Levine@TEKHELP.NET31_Mon, 22 Nov 1999 15:02:23 -0500481_us-ascii Hi,

Why did you have to 'force' DB2 down in the first place? I suspect that DB2 is attempting to backout a transaction or batch job that was running at the time you tried to bring it down. The process (job or transaction) must complete backing out before DB2 is available for other work. I suggest that you let it 'spin' for a while to see if it clears itself before taking any other action. If you 'force' it down again it will just add to the recovery time. [...] 3249 36 12_JDBC drivers13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Mon, 22 Nov 1999 14:01:47 -0600271_iso-8859-1 Hi, I would like to know where to get the JDBC drivers for DB2/OS390 for use with Java executed on a network.

I did get some off the internet, but get the following error, so I am not sure if I just have the wrong ones or if I have made a mistake. [...] 3286 94 16_Re: -904 abend !0_20_ddlusk@HOUSEHOLD.COM31_Mon, 22 Nov 1999 14:06:30 -0600558_us-ascii Deepak look at the db2 log, that will tell you more information









Purnima Patel 11/22/99 01:10 PM



Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM cc: (bcc: Derez D. Lusk/Household International) Subject: Re: -904 abend !





Deepak,

Just display the database.

-dis db(xxxxxxxx) limit(*)

This should tell you what is not RW or RO. If it is anythingelse you have a problem. [...] 3381 13 27_Re: DB2 V5 Looping (URGENT)0_22_Bob_Yoder@ILLINOVA.COM31_Mon, 22 Nov 1999 14:09:17 -0600341_us-ascii What is the maintenace level?, and if it is a serious problem call IBM support as a sev 1!

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3395 74 27_Re: DB2 V5 Looping (URGENT)18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Mon, 22 Nov 1999 14:31:10 -0600370_iso-8859-1 Hamid, I agree with Mike. If you can isolate what's being backed out, you could consider bringing down DB2 again and do a conditional restart with BACKOUT=NO. This will get DB2 back up and you can then fix the problems related to the backout. Make sure you determine how to fix it BEFORE you do the BACKOUT=NO. It will probably be some kind of recovery. [...] 3470 97 36_Re: Sequence numbers in spufi output0_24_DB46@DAIMLERCHRYSLER.COM31_Mon, 22 Nov 1999 12:45:18 -0500936_us-ascii Yes by using a nested table as follows:

SELECT COUNT (*), T2.SALES_CODE FROM (SELECT SALES_CODE FROM VWRS.TBSCEQUV) AS T1 , VWRS.TBSCEQUV T2 WHERE T1.SALES_CODE < T2.SALES_CODE GROUP BY T2.SALES_CODE

---------+---------+---------+---------+---------+---------+---------+---------+ SALES_CODE ---------+---------+---------+---------+---------+---------+---------+---------+ 1 K20 2 K30 3 K40 4 K50 5 VJ0 6 VK0 7 VL0 8 VT0 DSNE610I NUMBER OF ROWS DISPLAYED IS 8 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE617I COMMIT PERFORMED, SQLCODE IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1 DSNE621I NUMBER [...] 3568 21 27_Re: DB2 V5 Looping (URGENT)11_Hamid Ullah14_hullah@IBM.NET31_Mon, 22 Nov 1999 14:55:35 -0800558_- Hi, The reason to bring down DB2 was that I could not start or stop a tablespace which was given a user -904. Unavailable resource due to a locking situation(very ordinary common problem). However in this case, the user was logged off from the system, moreover, his thread was active in DIS TRHEADS. At this point, I stopped DB2 using DB2 command, after two hours apprx, DB2 did not come down. At that I brought down IRLM, nothing happened, and finally brought down DBM1 address space. When I brought DB2 up again, I left it 'Spining' for 14 hours to [...] 3590 40 27_Re: DB2 V5 Looping (URGENT)0_23_Mike_Levine@TEKHELP.NET31_Mon, 22 Nov 1999 16:01:05 -0500482_us-ascii Hi,

Was a DB2 user or job 'FORCED' off the system? Before you conditional restart I would IPL (I know that's not a popular option with operations). I've been in this situation before. The IBM support center was recommending a conditional restart (in production). We pushed for an IPL first and that corrected the problem. If the IPL does not correct the problem then you should work with the IBM support center on a conditional restart or possible maintenance. [...] 3631 14 27_Re: DB2 V5 Looping (URGENT)0_22_Bob_Yoder@ILLINOVA.COM31_Mon, 22 Nov 1999 15:02:02 -0600462_us-ascii I f you shut down with the command "-stop DB2,mode(force)", you should not have a problem, it will break all threads before taking down DB2. You may have a short time for backward recovery when you start backup.

================================================ 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. 3646 37 27_Re: DB2 V5 Looping (URGENT)12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Mon, 22 Nov 1999 15:03:00 -0500428_- IBM has specific instructions for DB2 looping. Have you called them? The message you have here is "AFTER" Backward recovery and you have 0 URs INFLIGHT and 0 URs in ABORT. This is saying that this phase is complete. The info from IBM's recovery class that I have about what is next is: -Writes Pending I/Os -Writes System Checkpoint -Updates BSDSs -Accepts reconnection request -Resolves indoubt URs after CICS reconnect [...] 3684 119 8_Re: TIME15_Wells, Davis B.21_davis.wells@TGSLC.ORG31_Mon, 22 Nov 1999 15:14:17 -0600566_x-user-defined The idea of measuring time from the birth of Christ came from an Italian monk in what "we" measure time as 400 AD. He is the one who tried to calculate Christ's birth that established the Christian era. He began the first year after Christ's birth as year 1 not year zero. You can check just about any Encyclopedia, the experts have determined that his calculations are any where from four to seven years late. So at best the "new" millennium actually began in 97. What this really means is the selection of 2000 or 2001 is completely arbitrary. [...] 3804 140 36_Re: Sequence numbers in spufi output8_Peter Yu14_peyu@BTMNA.COM31_Mon, 22 Nov 1999 16:15:18 -0500751_us-ascii You will miss one if you test for less than only. T1.SALES_CODE <= T2.SALES_CODE should be used instead.

















DB46@DAIMLERCHRYSLER.COM on 11/22/99 12:45:18 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@ryci.com

cc:





Subject: Re: Sequence numbers in spufi output

















Yes by using a nested table as follows:

SELECT COUNT (*), T2.SALES_CODE FROM (SELECT SALES_CODE FROM VWRS.TBSCEQUV) AS T1 , VWRS.TBSCEQUV T2 WHERE T1.SALES_CODE < T2.SALES_CODE GROUP BY T2.SALES_CODE [...] 3945 13 27_Re: DB2 V5 Looping (URGENT)11_Hamid Ullah14_hullah@IBM.NET31_Mon, 22 Nov 1999 15:14:39 -0800378_- Mike, We are IPL'ng at 6:00 tonight and I confess that I did not use Mode(Force) will keep in mind for next time(hope i dont need 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. 3959 22 35_DATE function to reduce CPU time???11_Paul Turpin26_Paul.Turpin@FIRSTUNION.COM31_Mon, 22 Nov 1999 15:21:59 -0800370_- In the "DB2 Dvelopers Guide" (Third Edition) on page 501 there is a comment "Although the DATE function is not required in the above SQL statement, its use can reduce CPU time significantly". The SQL statment that is referred to on page 501 is: SELECT ACSTAFF, ACENDATE FROM DSN8510,PROJACT WHERE PROJNO = '000100' AND ACTNO = 1 AND ACSTDATE = DATE('1991-12-31') [...] 3982 12 27_Re: DB2 V5 Looping (URGENT)11_Bernie Watt16_BWATT@KEYFIN.COM31_Mon, 22 Nov 1999 16:17:17 -0500323_us-ascii Check your output from DBM1 it is probiply doing a rollback of inflight.

================================================ 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. 3995 51 19_Re: DB2 and OPS/MVS16_Swinski, Kenneth32_KSwinski@INTERNAL.MASSMUTUAL.COM31_Mon, 22 Nov 1999 16:24:56 -0500474_iso-8859-1 We use OPS/MVS and DB2 extensively, and it frees our operators from having to know the subsystem recognition characters of the various DB2 subsystems. Among the tasks we've automated is calling the DB2 DBAs in case a subsystem does not come down 10 minutes after the STOP command was issued. The logic goes something like the following for the 5 address spaces that make up a subsystem: If (db1 ¬= 0) | (db2 ¬= 0) | (db3 ¬= 0) | (db4 ¬= 0) | (db5 ¬= 0) Then [...] 4047 34 27_Re: DB2 V5 Looping (URGENT)0_23_Mike_Levine@TEKHELP.NET31_Mon, 22 Nov 1999 16:42:50 -0500693_us-ascii Hi,

Canceling IRLMPROC is more powerful than a -STOP MODE(FORCE) and would stop (i.e., terminate) all threads much the same way. It is used when DB2 is not responding to DSN commands. However a -STOP MODE(FORCE) should always be attempted before a cancel.

Regards,

Michael Levine Premier Data Services, Inc.



>I f you shut down with the command "-stop DB2,mode(force)", you should not have >a problem, it will break all threads before taking down DB2. You may have a >short time for backward recovery when you start backup. > >================================================ >To change your subscription options or to cancel your subscription [...] 4082 35 61_How to do a count(*) after doing distinct select on 2 columns8_madhavan16_madhavan@INF.COM31_Mon, 22 Nov 1999 19:52:53 +0530326_- Hi,

We need to find out the count of no of rows which are distinct for a combination of 2 columns Example Count (distinct (ColA, ColB)) from Table1





ColA ColB ---------------- 10 10 10 20 20 10 20 20 20 20 10 10



For this the expected output is 4, ie 4 distinct combinations. [...] 4118 70 39_Re: Workload Balancing over DB2 Connect13_Joaquin Frias19_frias@STARQUEST.COM31_Mon, 22 Nov 1999 13:56:24 -0800722_us-ascii Vitor,

There's another product that does what DB2 connect does and runs on Cisco's routers; hence, load balancing is inherent and scalability is cheaper.

check the following URL:

http://www.cisco.com/warp/public/cc/cisco/mkt/iworks/applic/trans/prodlit/ct rc_ov.htm



At 09:49 AM 11/18/99 +0100, vpacheco@AMADEUS.NET wrote: >Dear all > >We are running distributed connections over DB2 Connect (NT, version 6.1) to >access DB2 on OS/390. We use APPC (LU 6.2) from the DB2 Connect to the DB2/390 >and TCP/IP from the CAE to DB2 Connect. >We are about to put in Production a new application that requires high >availability and the responsible are not so satisfied to have the [...] 4189 174 36_Re: Sequence numbers in spufi output0_24_DB46@DAIMLERCHRYSLER.COM31_Mon, 22 Nov 1999 16:22:58 -0500538_us-ascii Good call Peter, a thousand lashes with a wet noodle for me.

Dean









Peter Yu on 11/22/99 04:15:18 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc:





Subject: Re: Sequence numbers in spufi output















You will miss one if you test for less than only. T1.SALES_CODE <= T2.SALES_CODE should be used instead. [...] 4364 169 8_Re: TIME14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Mon, 22 Nov 1999 17:27:53 -0500363_us-ascii Sorry, I just can't resist this stuff.

The origin of the calendar is arbitrary, but the selection of 2000 vs. 2001 is not. The second millennium of any calendar that numbers its years from 1 comprises years 1001-2000 of that calendar, regardless of when that might be. If you're going to do millennia at all, you might as well do them right. [...] 4534 213 36_Re: Sequence numbers in spufi output14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Mon, 22 Nov 1999 17:29:19 -0500410_us-ascii Of course, as those of us who have been debating the Great Millennium Controversy have observed, numbering from zero has its advantages too.





DB46@DAIMLERCH RYSLER.COM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: Sequence numbers in spufi output Discussion List



1999-11-22 16:22 Please respond to DB2 Data Base Discussion List [...] 4748 46 36_Re: Platinum Rapid Reorg and Maxrows19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Mon, 22 Nov 1999 14:34:20 -0800605_- Are you using the SPACE-DEFN YES option? If this parameter is set to NO by either installation default or specifically in the REORG control cards, then the original dataset will be reused. If it is YES, then a new dataset will be allocated using the catalog space parameters.

HTH

Tim

> -----Original Message----- > From: Marcel Lepage [SMTP:iml@COLBA.NET] > Sent: Friday, November 19, 1999 10:26 PM > To: DB2-L@RYCI.COM > Subject: Platinum Rapid Reorg and Maxrows > > Hi everyone, > > I alter a tablespace for the MAXROWS parameter from 255 to 5 and then > reorg with Platinum [...] 4795 189 8_Re: TIME15_Wells, Davis B.21_davis.wells@TGSLC.ORG31_Mon, 22 Nov 1999 16:57:08 -0600419_iso-8859-1 The assumption that you are making is the calendar has been consistent from year 1. And the calendar is only consistent back to Oct. 15th 1582 when the Gregorian calendar was "arbitrarily" started.

Davis Wells davis.wells@tgslc.org

-----Original Message----- From: Harvey Wachtel [mailto:HWachtel@UAPC.CUNY.EDU] Sent: Monday, November 22, 1999 4:28 PM To: DB2-L@RYCI.COM Subject: Re: TIME [...] 4985 72 11_SQL Problem17_Price, Jeff (EDS)14_jprice@NCL.COM31_Mon, 22 Nov 1999 18:00:47 -0500484_- (OS/390 DB2 V5.1 Putlevel 9909) I am having a problem getting DB2 to match index column for a date field where a subselect returns the date value. Where am I going wrong??????

The query is:

SELECT LOAD_DAT, DSNODE1, DSNODE3, DSNODE4 , ALLOC_TYP, TRKS_PRIMARY, TRKS_SECONDARY , TRKS_TOTAL, EXTENTS_TOTAL, TRKS_TOTAL * 48 AS PRIQTY FROM DBA00DP0.DASDHIST WHERE LOAD_DAT = (SELECT MAX(LOAD_DAT) FROM DBA00DP0.DASDHIST) AND DSNODE1 = 'DB2PAPPL' AND EXTENTS_TOTAL > 8 [...] 5058 227 8_Re: TIME14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Mon, 22 Nov 1999 18:09:33 -0500331_us-ascii The phrase "second millennium" means "the second set of a thousand". If you number a sequence from 1, then the second set of a thousand is 1001 through 2000, even if the numbering is done after the fact. I guess I've got one of those little minds of which a foolish consistency is, according to Emerson, a hobgoblin. [...] 5286 75 65_Re: How to do a count(*) after doing distinct select on 2 columns0_19_Tim.Lowe@STPAUL.COM31_Mon, 22 Nov 1999 16:09:28 -0600660_us-ascii Try: count( distinct( ColA|| Colb ) )













madhavan on 11/22/99 08:22:53 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Tim Lowe/sfm/spc)





Subject: How to do a count(*) after doing distinct select on 2 columns















Hi,

We need to find out the count of no of rows which are distinct for a combination of 2 columns Example Count (distinct (ColA, ColB)) from Table1 [...] 5362 36 61_How "Safe" is Lotus Approach as a FrontEnd to DB2/UDB for NT?16_Christine Sawyer20_cosawyer@HOTMAIL.COM29_Mon, 22 Nov 1999 19:40:32 EST592_- Hi List, We are considering installing Lotus Approach97 as a front-end to several aplication developers (30) with installed DB2/UDB and it is strictly used for testing and so forth.

Currently, we have zero experience with UDB front-end and will like to hear from anyone with experience using Approach97 as described. What can anyone tell me about it from experience? As a front-end to DB2, What are Approach97 strengths for manupulating data in DB2 tables, and What are its weakness? What are its strengths in doing multiple table joins, subqueries, and other advance SQL, etc. [...] 5399 107 15_Re: SQL Problem14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Tue, 23 Nov 1999 11:38:53 +1100309_- Jeff,

The issue is most likely a mismatch of nullability between LOAD_DAT and the result of the subquery; ie. LOAD_DAT is defined as NOT NULL, and the subquery is nullable. This mismatch results in the predicate evaluation to be performed at stage 2, and thus no exact index match is available. [...] 5507 23 4_-90415_Pravat Banerjee32_pravatbanerjee@LOT.TATASTEEL.COM31_Tue, 23 Nov 1999 10:25:57 +0530585_us-ascii hi deepak , You can check the reason code . i had also faced a similar problem (-904) with the following msg::Unsuccessful execution caused by an unavailable resource. Reason code 00C2010D, type of resource 00000220, and resource name

COMMT.DSNDBC.COMMDB01.JRMBOOK.I0001.A001. for the code which has been hilighted above you can run recover/rebuild index as there was a problem with index space.. Otherwise for the particular reason code which u may be getting u can look into help books that are available which will suggest u what specific action u should take. [...] 5531 50 11_unsubscribe13_Console - SDP21_console@PENTAFOUR.COM31_Tue, 23 Nov 1999 12:14:06 +0530560_iso-8859-1 unsubscribe > ---------- > From: Cuneyt Goksu (Garanti > Teknoloji)[SMTP:CuneytG@GARANTI.COM.TR] > Reply To: DB2 Data Base Discussion List > Sent: Monday, November 22, 1999 15:56 > To: DB2-L@RYCI.COM > Subject: DB2 Connect Personel vs EE > > Hello List, > > We have a couple of 2 tier applications written with C and access to > OS/390 DB2 through DB2 Connect Personel edition with > TCP/IP. They have only one or two users. > > Now a new app. is coming and the number of users will be a lot.. > My concern is i may think about Connect Gateway [...] 5582 85 65_Re: How to do a count(*) after doing distinct select on 2 columns4_vcei14_vcei@CEGEKA.BE31_Tue, 23 Nov 1999 09:46:18 +0100696_iso-8859-1 Another possibility: Select count(*) from (select colA, colB from .... where ... group by colA, colB) as Q

Don`t forget the AS clause !

---------------------------------------------------------------------------- -





Try: count( distinct( ColA|| Colb ) )













madhavan on 11/22/99 08:22:53 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Tim Lowe/sfm/spc)





Subject: How to do a count(*) after doing distinct select on 2 columns [...] 5668 202 36_Re: Sequence numbers in spufi output0_18_mebert@AMADEUS.NET31_Tue, 23 Nov 1999 10:56:07 +0100423_us-ascii I would not try this on a significant table, for two reasons:

1. It will certainly have a quadratic complexity (running-time). I tried it in SPUFI on SYSIBM.SYSDATABASE (128 rows), time: ca. 1 sec CPU/elapsed; SYSIBM.SYSTABLESPACE (1864 rows), time: 1 min 25 sec CPU, 2 min 05 sec elapsed.

2. This is part of the SPUFI output I got in the SYSTABLESPACE example (the SYSDATABASE example was ok): [...] 5871 12 11_unsubscribe0_26_Frank_Crissy@HOMEDEPOT.COM31_Tue, 23 Nov 1999 06:13:41 -0500261_us-ascii unsubcribe

================================================ 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. 5884 70 15_R: JDBC drivers18_Campidoglio, Elena29_E.Campidoglio@CEDACRIOVEST.IT31_Tue, 23 Nov 1999 12:25:08 +0100542_- Hi Glenn, I had a similar problem and even if I havn't solved yes, I collected a lot of useful suggestions. Here they are: you need the fmid of jdbc applied (you can order it from ibm, it's free I think), you need CLI interface enabled on db2 db2, of course, must be running and 5.1 you have to update CLASSPATH, LIBPATH, STEPLIB and LD_LIBRARY_PATH if you are using websphere, bettere work with websphere 1.2 and at last you need java installed at 1.8 and about this point I have to say that what we downloaded from internet had some [...] 5955 16 9_dba-xpert16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Tue, 23 Nov 1999 08:40:33 -0500489_us-ascii Have been using since 1992. We think it's a good product. Saves dba's from tedious work. Besides migrating all database objects from test to prod. Creates all db2 utilities the only thing you have to do is submit the jcl that was created.

================================================ 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. 5972 235 36_Re: Sequence numbers in spufi output10_Robert Ord21_robertord@HOTMAIL.COM29_Tue, 23 Nov 1999 13:54:07 GMT644_- The comparison has to use a unique key otherwise you are correct the result is just a list of counts. Performance is also much better.

Rob



>From: mebert@AMADEUS.NET >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Sequence numbers in spufi output >Date: Tue, 23 Nov 1999 10:56:07 +0100 > >I would not try this on a significant table, for two reasons: > >1. It will certainly have a quadratic complexity (running-time). I tried it >in >SPUFI on SYSIBM.SYSDATABASE (128 rows), time: ca. 1 sec CPU/elapsed; >SYSIBM.SYSTABLESPACE (1864 rows), time: 1 min 25 sec CPU, 2 min 05 [...] 6208 97 42_UDB v6.1 Control Center - Client Login ID?20_BREIDENSTINE JOHN L.21_jlb@LEGIS.STATE.PA.US31_Tue, 23 Nov 1999 08:58:26 -0500390_us-ascii Hello Bill,

We ran in to the same problem and here is what IBM told us.

The NT user Client Login ID of "SYSTEM" is not a member of the SYSADM_GROUP. So, either make the value of the SYSADM_GROUP = "his group", or add him to the SYSADM_GROUP, or change the value of CATALOG_NOAUTH to YES. Changing CATALOG_NOAUTH to YES will cause DB2 to ignore the SYSADM_GROUP. [...] 6306 37 6_Create0_25_joseph.burns@HIGHMARK.COM31_Tue, 23 Nov 1999 09:28:10 -0500478_us-ascii We are running DB2 V5 for OS/390. Also, we have DataJoiner (DB2 UDB) on a Unix box. We have requirements to access data that resides on the DB2 UDB UNIX box from the OS/390 box. The question is about DCLGENS on the OS/390 box.

From the OS/390 box, we know that we can do a remote bind from an OS/390 DBRM down to the UNIX box. We have done this with the SPUFI package. The question now is, can we also do this with the OS/390 DCLGEN DBRM? (dbrm: DSNECP68)? [...] 6344 46 39_Re: DATE function to reduce CPU time???14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 24 Nov 1999 01:54:02 +1000368_us-ascii Paul,

I am going to guess that this DATE function would make virtually no difference to performance of static SQL if same access path. Looks like misinformation to me. We never bother to use DATE function like this unless we want to hide the literal value from the optimizer so that it can't use frequent value stats in access path determination. [...] 6391 12 8_Re: TIME0_27_Bud.Greenman@ONONDAGA.NY.US31_Tue, 23 Nov 1999 10:02:20 -0500403_- I have to add my 2 cents now too. For those of us who are not Christians the proper term are not BC and AD but BCE (Before the Common Era) and CE (Common Era).

================================================ 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. 6404 54 27_Re: DB2 V5 Looping (URGENT)11_Robert Hall25_robert.hall@OCC.TREAS.GOV29_Tue, 23 Nov 1999 09:51:23 EST352_us-ascii Hi,

We are on DB2 V5 put 9905 and we occasionally have threads that will not terminate even with mode(force). Usually operations cancels the IRLM (if we are lucky) and DB2 comes down and restarts in the morning by itself. (We IPL every night, I know I know it is unusal, but we do it and the IPL schedule isn't under my control!). [...] 6459 46 8_Re: TIME15_Lyle Kelly-LH-117_kelly.lh.1@PG.COM31_Tue, 23 Nov 1999 10:31:02 -0500308_us-ascii So, you use the terms you prefer, and I use the terms I prefer. As long as we understand each other, I see no problem. If we all agree to use "industry" terms (whatever they may be), I see no problem. I do see a problem if you insist I use only your terms, or if I make the same demand of you. [...] 6506 65 39_Re: DATE function to reduce CPU time???20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 23 Nov 1999 10:36:25 -0500372_- I agree with michael. I am not even sure if it makes difference even for dynamic sqls. Also Michael mentioned that it could make difference while SYSCOLDIST is populated but in the example below, the column is last column of the index and there is a full match on the index, how would even SYSCOLDIST could possibly make difference other than using list prefetch ? [...] 6572 78 27_Re: DB2 V5 Looping (URGENT)0_23_Mike_Levine@TEKHELP.NET31_Tue, 23 Nov 1999 10:53:46 -0500535_us-ascii Hi Robert,

Why, oh why would anybody IPL a production system nightly? I would think that to be extremely disruptive and completely unnecessary. Until a few years ago we used to recycle DB2 nightly which was a hold over from the EDM pool fragmentation problem in V2. It was tough to get OPS to stop doing this but when they finally did the world did not end (much to their surprise)! Sometimes operations will keep doing something unless someone tells them to stop. Maybe suggest that they IPL once a week instead! [...] 6651 450 31_Re: Seq numbers in spufi output12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Tue, 23 Nov 1999 10:50:46 -0500450_us-ascii

Thanks to all those that responded ... BTW, let me rephrase the question so that there is less scope for misinterpretation.

I am wondering if there is any way to implement the functionality of implicit variable (available in Oracle SQL) into DB2 SQL i.e. in Oracle SQL, the statement

SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10); [...] 7102 30 31_Re: Seq numbers in spufi output12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Tue, 23 Nov 1999 11:00:38 -0500425_us-ascii Thanks to all those that responded ... BTW, let me rephrase the question so that there is less scope for misinterpretation.

Is there any simple way to get the functionality of implicit variable (available in Oracle SQL) into DB2 SQL i.e. in Oracle SQL, the statement

SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE rownum <= 10 MINUS SELECT rowid FROM t1 WHERE rownum < 10); [...] 7133 70 27_Re: TIME BLAH BLAH BLAH!!!!13_John Arbogast16_jfarbo@YAHOO.COM31_Tue, 23 Nov 1999 08:32:46 -0800530_us-ascii Enough already!

--- Lyle Kelly-LH-1 wrote: > So, you use the terms you prefer, and I use the > terms I prefer. As long as we > understand each other, I see no problem. If we all > agree to use "industry" > terms (whatever they may be), I see no problem. I > do see a problem if you > insist I use only your terms, or if I make the same > demand of you. > > > > > Internet Mail Message > Received from host: [208.225.214.81] > [208.225.214.81] > > > > Envelope Sender: owner-db2-l@ryci.com [...] 7204 28 23_-632 on Foreign key DDL15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 23 Nov 1999 11:41:50 -0500337_iso-8859-1 It seems strange to me. I have two table TAB1 and TAB2. I created two new column COL1 and COL2 in TAB2 and tried to define both column as foreign key to TAB1 table with SET NULL rule. Foreign key for COL1 created successfully, but foreign key for COL2 failed with -632. I looked at the error message and it is as follows [...] 7233 145 39_Re: Workload Balancing over DB2 Connect0_20_vpacheco@AMADEUS.NET31_Tue, 23 Nov 1999 18:22:34 +0100421_us-ascii Joaquin





Thanks for your information. I was already aware of this Product. I had the chance to attend to IDUG in Nice last October and I have approached some competitors of the IBM distributed connection solution.





There are two points, though, that do not make this solution so attractive for us:

1. Re-investement and initial cost of the CISCO solution; [...] 7379 92 15_Re: SQL Problem9_Mark Ruhe15_MRuhe@QUEST.COM31_Tue, 23 Nov 1999 09:59:35 -0800460_iso-8859-1 I have run into similar performance issues with MAX/MIN functions. One thing that can help improve access is making LOAD_DAT a decending column of the index(if you haven't already done so). This will improve the performance of the non-matching scan.

Mark Ruhe Quest Software

-----Original Message----- From: Price, Jeff (EDS) [mailto:jprice@NCL.COM] Sent: Monday, November 22, 1999 5:01 PM To: DB2-L@RYCI.COM Subject: SQL Problem [...] 7472 71 8_Re: TIME15_Rameswara Pavan24_RPAVAN@ISETS.STATE.IN.US31_Tue, 23 Nov 1999 13:17:41 -0500543_us-ascii Please stop discussion on this thread.

Pavan





-----Original Message----- From: Lyle Kelly-LH-1 [SMTP:kelly.lh.1@PG.COM] Sent: Tuesday, November 23, 1999 10:31 AM To: DB2-L@RYCI.COM Subject: Re: TIME

So, you use the terms you prefer, and I use the terms I prefer. As long as we understand each other, I see no problem. If we all agree to use "industry" terms (whatever they may be), I see no problem. I do see a problem if you insist I use only your terms, or if I make the same demand of you. [...] 7544 64 27_Re: -632 on Foreign key DDL18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Tue, 23 Nov 1999 12:40:34 -0600510_iso-8859-1 Philip, See DB2 for OS390, V5, Admin. Gde., bookmanager ref; 2.9.5.3.3, Rules for Foreign Keys: "If multiple pathways exist between tables T1 and T2 in which T1 is a parent of T2 or T1 has a CASCADE relationship to a parent of T2, then the relationships that enter T2 must be the same and must not be SET NULL." You can use anything but SET NULL. DB2 doesn't allow constraints it considers time-dependent. My knee-jerk reaction was one of thinking maybe the tables could be further normalized. [...] 7609 16 16_DB2PM parameters14_Joop Ammerlaan29_Joop.Ammerlaan@NL.ABNAMRO.COM31_Tue, 23 Nov 1999 16:13:43 +0100300_- We are in the process of evaluating the DB2 Performance Monitor. The moment we start the data collector we are unable to do anything with the DB2 subsystem. I've changed several start-up parameters but nothing seems to work. Can someone supply me with a working sample for the data collector? [...] 7626 39 73_Important Difference between DB2 CAE V5.2 and DB2 Runtime Client/SDK V6.10_21_RompotK@SCHNEIDER.COM31_Tue, 23 Nov 1999 12:10:36 -0600595_us-ascii Just a little heads up so other people can avoid my grief. In the past, all I used to do was install new versions of DB2 CAE and bind the ddcsmvs.lst against all of my OS/390 subsystems. I would usually do so with the Client Configuration Assistant.

We recently migrated to DB2 V6.1. I installed the Run-Time Client and used CCA to bind against DB2 OS/390. I got some bind errors but didn't pay too much attention ot them. Then our Impromptu server started flaking out. It was getting an error message saying that package "DP1.SQLL79N3.5142617443524760" could not be found. [...] 7666 75 46_Re: UDB v6.1 Control Center - Client Login ID?17_Jeff B Schumacher29_jeffschumacher@SPRINTMAIL.COM31_Tue, 23 Nov 1999 11:55:21 -0800584_- Starting with v6.1 the NT DB2 Control Center is running under the account associated with the NT Service called something like "JAVA Applet Control Center Service". The NT DB2 Command Line Processor is not. When I did an upgrade install the NT service was installed to run under the NT SYSTEM account. The SYSTEM account has not been given SYSADM authority. What I did was change the NT service (on the client) to run under the db2admin account (of the server). Changing to this account allowed me to do DB2 tasks that require Attach level access, (e.g., Create Database), but [...] 7742 115 46_Re: UDB v6.1 Control Center - Client Login ID?0_22_BILL_GALLAGHER@PHL.COM31_Tue, 23 Nov 1999 15:33:37 -0500407_us-ascii Jeff,

Thanks for the info. I was able to do what you did, and now everything is working the way I would expect it to.

I also agree that this is more of a work around than a solution. Before I received your response, I opened up a problem with IBM/Toronto, and am still waiting for a callback. Once I talk to them, I will ask them if there is a better solution to this situation. [...] 7858 95 27_Re: -632 on Foreign key DDL15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 23 Nov 1999 15:56:08 -0500474_iso-8859-1 Rick,

In my case 'T1 has a CASCADE relationship to a parent of T2' is not true. But 'If multiple pathways exist between tables T1 and T2 in which T1 is a parent of T2' is true when I create the second foreign key on COL2 to TAB1. What is the problem of having two foreign key with SET NULL in TAB2 to TAB1? What I am expecting is DB2 should make COL1 or COL2 to null when a row is deleted in TAB1 if there are records satisfying the foreign key rule. [...] 7954 138 15_Re: SQL Problem17_Price, Jeff (EDS)14_jprice@NCL.COM31_Tue, 23 Nov 1999 15:58:34 -0500590_iso-8859-1 Terry: You were correct, thank you. Now I'm wondering if this is a feature or flaw. After all, since the subselect was targeting the same column/table "LOAD_DAT" defined as NOT NULL, the only way it could return a NULL value in this instance would be if the table had no rows, hence no MAX value. My hope would be that DB2 will soon push this NULL compatibility-issue down to stage-1. I have seen many applications use MAX(DATE) type subqueries (without COALESCE). I never realized the penalty of not defining the DATE columns as NULLABLE or not taking the extra precaution [...] 8093 57 27_Re: -632 on Foreign key DDL17_Linda F. Claussen18_lindafc@NETINS.NET31_Tue, 23 Nov 1999 16:26:14 -0600433_ISO-8859-1 Philip,

It would be helpful to know what the Primary Key definition on TAB1 is and the exact DDL you used to define the Foreignt Key columns in TAB2. It sounds to me as if you have a mismatch between the PK and the FK.

The way you have described the problem it sounds as if you executed the ADD FOREIGN KEY twice, once for each new column. Do you have a single column PK on TAB1 or is it a 2 column PK? [...] 8151 122 27_Re: -632 on Foreign key DDL15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 23 Nov 1999 17:42:06 -0500509_ISO-8859-1 Linda,

TAB1--- OOTLOCN NAME COLTYPE LENGTH SCALE NULLS DEFAULT KEYSEQ COLNO ------------------ -------- ------ ------ ----- ------- ------ ------ LOC_ID INTEGER 4 0 N N 1 1 TIME_ZONE_BLOCK_I INTEGER 4 0 Y B 0 2 LOC_CP_LATITUDE_QT DECIMAL 9 4 N Y 0 3 LOC_CP_LONGTD_QT DECIMAL 9 4 N Y 0 4 LOC_DATA_S_TYPE_CD SMALLINT 2 0 N Y 0 5 LOC_TYPE_CD SMALLINT 2 0 N Y 0 6 CREATE_DT TIMESTMP 10 0 N Y 0 7 UPDATE_DT TIMESTMP 10 0 N Y 0 8 LOC_NAME_DS VARCHAR 70 0 N Y 0 9 LOC_ID_AN CHAR 34 0 N Y 0 10 [...] 8274 27 30_People Soft Proccess Scheduler12_Silvia Motta21_silviamotta@YAHOO.COM31_Tue, 23 Nov 1999 12:26:41 -0800391_us-ascii Hi,

Anybody has experience with PS on DB2/MVS v4 ??? I have the Proccess Schedule on the MVS, I have three envioroment DEMO, DES and TEST. So I have 3 plans for the program PTUPRCS, one in each enviorment. But only one for the SQR program DBCLASS. Is this ok ??? The problem is that I don´t know how work the proccess scheduler in the mainframe ?? Thanks for your help. [...] 8302 29 36_Auditing in a DB2 UDB/NT environment0_30_Milorad_Cerovac@TAC.VIC.GOV.AU31_Wed, 24 Nov 1999 10:25:03 +1000552_us-ascii We're a Version 5 UDB site running on an NT platform. Following a visit by the auditors we now have a requirement to track changes made by the DBA group - in relation to granting/revoking of privileges, changes to the database, et al. Our question is what is the best way of doing this? We've had a look at the documentation - in particular the audit log. However the information contained in this log appears somewhat insufficient - e.g. if I add a new column to an existing table, then an audit record would be created to show that the [...] 8332 197 15_Re: SQL Problem14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Wed, 24 Nov 1999 12:22:24 +1100407_iso-8859-1 Jeff,

My opinion is that it is a "flawed" feature. You are right also, because a column function (such as MAX or MIN) may return a null value if there is no match, then the resultant value is defined as nullable.

Note: The nullability issue is only relevant to non-correlated subqueries which are stage-1, as all other subqueries (including all correlated) are stage-2 anyway. [...] 8530 45 18_Inconsistent Index0_27_mamccormack@STATESTREET.COM31_Tue, 23 Nov 1999 20:38:47 -0500482_US-ASCII To Bill Moss,

You have received advice from others to stick with Recover Index rather than Repair. I will not differ from that.

If you recover the index(s), you at least get the table and the index(s) back in sync. If the index was defective, then it is fixed. If the table had a row which should not be there (a possibility suggested by Donna Di Carlo/Michael Murley), then you can delete the offending row and its index entries via an SQL delete stmt. [...] 8576 64 31_Re: Compuware DBA-XPERT for DB211_Long, Jerry17_jlong@NEPI.GOV.AU31_Wed, 24 Nov 1999 14:09:54 +1100603_iso-8859-1 Hi John,

I have been using DBA-XPERT for c. 6 months at a relatively small site. I like the fact that it has just about everything I want for everyday stuff in a single wrapper, eg DDL/bind generation, utility JCL generation, impact reporting, etc, as opposed to the various BMC & CA/Platinum equivalents which tend to be spread across multiple products. Also, it has a single, consistent layout and format, with commands common to the various functions, as opposed to the big boys tools which in my experience are sometimes anything but consistent across the different products. [...] 8641 33 25_Archive Log Dataset Names24_Peter Schwarcz (Bigpond)26_Peter_Schwarcz@BIGPOND.COM31_Wed, 24 Nov 1999 14:07:12 +1100595_iso-8859-1 I have noted an MQ PTF (UQ29080) that changes the meaning of the TSTAMP ZPARM option.

" When using TSTAMP=YES in the CSQ6ARVP macro in CSQZPARM, the Julian date is included in the dataset name in the format Dyyddd. This will cause a problem to those customers who rely on sorting datasets in chronological order, using this field, during the transition to Year 2000. This APAR has been raised to 1. allow TSTAMP=YES to be used in this situation by changing Dyyddd to Eyyddd from year 2000 onwards. 2. introduce a new option TSTAMP=EXT which will generate 4-digit years in [...] 8675 14 29_Re: Archive Log Dataset Names12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 23 Nov 1999 19:28:50 -0800338_us-ascii That was done in APAR PQ06642 just over two years ago on Version 5.

Roger Miller

================================================ 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. 8690 154 27_Re: -632 on Foreign key DDL17_Linda F. Claussen18_lindafc@NETINS.NET31_Tue, 23 Nov 1999 21:53:44 -0600345_ISO-8859-1 Rick is correct in saying that there is a Restriction on these types of relationships. The actual restriction is:

"If T1 is delete-connected to T2 through multiple paths, those relationships in which T1 is a dependent and which form all or part of those paths must have the same delete rule and it must not be SET NULL. " [...] 8845 60 36_Re: Platinum Rapid Reorg and Maxrows13_Marcel Lepage13_iml@COLBA.NET31_Tue, 23 Nov 1999 23:48:55 -0700334_iso-8859-1 Hi Tim,

The parameter SPACE-DEFN as nothing to do with it, the result is absolutly the same in both cases. We open a problem at Platinum about that issue and still waiting for their next answer (the first one was... there is no problem).

To be continued...



"Ohling, Tim R - CNF" a écrit : [...] 8906 36 19_Level-ID problem...14_Ravi Phanindra21_raviv@WIPSYS.SOFT.NET31_Wed, 24 Nov 1999 11:00:22 +0530364_us-ascii Hi, A few of the TS' have sprung LPL problems. The Admin. guide says that

issuing a start on the . with access(rw/ro) should reset it. But that is not happening. We tried Repair on the partition with the LPL but the util. ends with a msg DSNU205I - Logical error range (Logical Page List) on table space prohibits processing. [...] 8943 55 19_Level-ID problem...14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Wed, 24 Nov 1999 16:10:19 +1000586_us-ascii Ravi, You can recover the pages in error. See V5 Administration Guide section 4.2.1.2.2 for a discussion on the subject (if you are at another version I don't have a reference for you).

Paul.

>>>>>>>>>>>>>>> Hi, A few of the TS' have sprung LPL problems. The Admin. guide says that

issuing a start on the . with access(rw/ro) should reset it. But that is not happening. We tried Repair on the partition with the LPL but the util. ends with a msg DSNU205I - Logical error range (Logical Page List) on table space prohibits processing. [...] 8999 97 34_Re: People Soft Proccess Scheduler12_Brad Kiemann31_Brad.Kiemann@DCB.DEFENCE.GOV.AU31_Wed, 24 Nov 1999 12:08:49 +1100456_US-ASCII Silvia,

This is OK.

The plan is not specifically for the process schedulers it is for the PTPSQLRT program. This program is called by all PS COBOL to actually execute SQL. It requires a plan for each environment so it can issue an unqualified SELECT on PSLOCK.

As PTPSQLRT is called as a subprogram the main program must be run with the right plan thus the process scheduler, and any other COBOL, are run with this plan. [...] 9097 92 39_Re: DATE function to reduce CPU time???13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Wed, 24 Nov 1999 14:37:47 +0530506_iso-8859-1 I am sure for both Dynamic as well as Static SQLs the CPU time as well as Cost is drastically gets reduced if we use DATE('2000-01-01') instead of '2000-01-01'. I am very clear about this and hope this would resolve all the questions in our mind regarding this!

----- Original Message ----- From: Pillay, Venkat (PCA) Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, November 23, 1999 9:06 PM Subject: Re: DATE function to reduce CPU time??? [...] 9190 111 39_Re: DATE function to reduce CPU time???14_Jackie Gribbon23_jgribbon@GSSEC.BT.CO.UK31_Wed, 24 Nov 1999 10:40:18 -0000411_iso-8859-1 Hmm, two very strongly held and opposing opinions here! I don't know either way, but I'd like to know. Does anyone have any test figures to support either argument?

Jackie

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Deepak Kini M Sent: 24 November 1999 9:08 To: DB2-L@RYCI.COM Subject: Re: DATE function to reduce CPU time??? [...] 9302 141 39_Re: DATE function to reduce CPU time???14_Jackie Gribbon23_jgribbon@GSSEC.BT.CO.UK31_Wed, 24 Nov 1999 10:42:53 -0000532_iso-8859-1 And can you share the figures with us, or at least the percentage reduction?

Jackie

-----Original Message----- From: Deepak Kini M [mailto:deepakk@wiphyd.ge.com] Sent: 24 November 1999 10:41 To: jackie.gribbon Subject: Re: DATE function to reduce CPU time???



Yeah Jackie, I tested myself and we have implemented this in our Shop. This step was taken as one of the cost reduction project where we did Optimization of the existing code and we have ended-up with significantly good result. [...] 9444 171 39_Re: DATE function to reduce CPU time???13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Wed, 24 Nov 1999 16:38:43 +0530417_iso-8859-1 Yeah, For about 50,000 rows in a table, by just Using DATE Clause the percentage reduction in cost as well as CPU Time is about 17.5%. This is definitely a significant gain.

----- Original Message ----- From: Jackie Gribbon Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, November 24, 1999 4:12 PM Subject: Re: DATE function to reduce CPU time??? [...] 9616 96 27_Re: -632 on Foreign key DDL0_19_mike.holmans@BT.COM31_Wed, 24 Nov 1999 11:01:59 -0000323_- I'm sorry, I'm feeling very, very stupid this morning. What on earth does "DB2 doesn't allow constraints it considers time-dependent." mean, and why does it have anything to do with SET NULL? I know that when someone expands on this, it will all seem terribly obvious, but at the moment it just makes me go "Uh????" [...] 9713 96 34_Re: People Soft Proccess Scheduler12_Brad Kiemann31_Brad.Kiemann@DCB.DEFENCE.GOV.AU31_Wed, 24 Nov 1999 16:46:17 +1100456_US-ASCII Silvia,

This is OK.

The plan is not specifically for the process schedulers it is for the PTPSQLRT program. This program is called by all PS COBOL to actually execute SQL. It requires a plan for each environment so it can issue an unqualified SELECT on PSLOCK.

As PTPSQLRT is called as a subprogram the main program must be run with the right plan thus the process scheduler, and any other COBOL, are run with this plan. [...] 9810 153 39_Re: DATE function to reduce CPU time???12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Wed, 24 Nov 1999 11:55:00 -0000612_iso-8859-1 I tried it with dynamic SQL as follows:- WITHDATE = "SELECT ACSTAFF, ACENDATE", "FROM DEV8410.PROJACT", "WHERE PROJNO = '000100'", "AND ACTNO = 1", "AND ACSTDATE = DATE('1991-12-31')" NODATE = "SELECT ACSTAFF, ACENDATE", "FROM DEV8410.PROJACT", "WHERE PROJNO = '000100'", "AND ACTNO = 1", "AND ACSTDATE = '1991-12-31'" DO J = 1 TO 5 S = SYSVAR('SYSCPU') DO I = 1 TO 500 WITHDATE -- issue command with date function END E = SYSVAR('SYSCPU') SAY 'WITH DATE TOTAL CPU='E-S S = SYSVAR('SYSCPU') DO I = 1 TO 500 NODATE -- issue command without date function END E = SYSVAR('SYSCPU') SAY 'WITHOUT DATE [...] 9964 18 28_converting plans to packages15_Pelle Svennberg24_per-erik.svennberg@NB.SE29_Wed, 24 Nov 1999 11:56:25 GMT508_us-ascii We are finally going to Packages all over in our system, and now I wonder if anyone know about products (either IBM or any vendor) that can help us doing all the stuff like Bind Packages using our current DB2 catalog as input?

regards pelle svennberg

================================================ 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. 9983 14 32_Re: converting plans to packages0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 24 Nov 1999 07:05:38 -0500334_us-ascii BMC Catalog Manager has the ability to do this for you (command "PACKIT")

Ed

================================================ 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. 9998 38 32_Re: converting plans to packages0_19_mike.holmans@BT.COM31_Wed, 24 Nov 1999 12:24:13 -0000551_- This probably won't match Pelle's needs, but I have a REXX exec which, given the name of an existing old-style plan interrogates the DB2 catalog and generates BIND PACKAGE statements and then executes them. It uses the RLX/REXX interface from Relational Architects, which means that some of the code is invalid for the DB2/SQL/REXX interface (like there is a cursor which isn't called Cnn), and one or two hard-coded parms like OWNER and SSID, but it would be easily adaptable. If anyone wants it on an unsupported as is basis, please mail me. [...] 10037 353 32_Re: converting plans to packages7_Gustavo26_lozano@BANDEIRANTES.COM.BR31_Wed, 24 Nov 1999 10:42:43 -0300737_iso-8859-1 Hi, Pelle... We used the query below when converting plans into packages... Hope it helps. Here it goes:

SELECT A.NAME, B.NAME, 'A', 'BIND PACKAGE('||A.QUALIFIER||') -' FROM SYSIBM.SYSPLAN A, SYSIBM.SYSDBRM B WHERE A.CREATOR = 'PRODMN' AND A.NAME = B.PLNAME UNION ALL SELECT A.NAME, B.NAME, 'B', ' OWNER('|| A.CREATOR||') QUALIFIER('||A.QUALIFIER||') -' FROM SYSIBM.SYSPLAN A, SYSIBM.SYSDBRM B WHERE A.CREATOR = 'PRODMN' AND A.NAME = B.PLNAME UNION ALL SELECT A.NAME, B.NAME, 'C', ' MEMBER('||B.NAME|| ') LIBRARY('''||B.PDSNAME||''') -' FROM SYSIBM.SYSPLAN A, SYSIBM.SYSDBRM B WHERE A.CREATOR = 'PRODMN' AND A.NAME = B.PLNAME UNION ALL SELECT A.NAME, B.NAME, 'D', ' ACTION(REPLACE) '|| CASE WHEN A.ISOLATION = 'S' [...] 10391 127 27_Re: -632 on Foreign key DDL18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Wed, 24 Nov 1999 07:11:58 -0600588_iso-8859-1 Mike, Its my fault you feel "Uh???". I should have pasted in the entire reference yesterday. I'm sure you're perfectly fine. The manual will say it better than I did. See DB2 for OS390, V5, Admin. Gde., bookmanager ref 2.9.5.3.3, Foreign Key Rules. Sorry, 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 [...] 10519 153 27_Re: -632 on Foreign key DDL0_19_mike.holmans@BT.COM31_Wed, 24 Nov 1999 13:36:00 -0000465_ISO-8859-1 Rick,

I'm obviously not perfectly fine, because I've looked it up and I still don't understand.

I can see why DB2 wouldn't allow there to be different delete rules for two relationships with the same parent and child, because it would make the result of the delete dependent on which rule you followed first, and that would breach the principle that the result of an SQL statement should be independent of the physical implementation. [...] 10673 152 39_Re: DATE function to reduce CPU time???0_18_mebert@AMADEUS.NET31_Wed, 24 Nov 1999 14:51:12 +0100618_us-ascii I remember that long time ago I also stumbled across this statement, didn't believe it, so tested it... it didn't make any difference. This is not surprising really. Conversion of the date string to the DATE datatype (a packed decimal with no sign) requires just masking and shifting. IBM Assembler has a machine instruction that converts between strings and packed decimal; however it's been 5 years since I've programmed in BAL and I don't remember whether you can use it for unsigned decimals. In any case, the conversion shouldn't take more than maybe 1 microsecond and this is certainly negligible. [...] 10826 185 27_Re: -632 on Foreign key DDL18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Wed, 24 Nov 1999 08:20:52 -0600320_iso-8859-1 Mike, Now that I've reread my reply I agree with you. I ran two rules together leaving a wrong impression. Previously, I emailed Sibimon directly saying I agreed with him in that there doesn't seem to be any reason why two SET NULL "children" can't exist for a single "parent". I'd like to know why too! [...] 11012 71 34_Re: People Soft Proccess Scheduler9_Jim Jones33_jim.jones@INGRAMENTERTAINMENT.COM31_Wed, 24 Nov 1999 08:37:00 -0800341_iso-8859-1 Silvia,

The one SQR plan is fine, along with a PRCS plan for each database. I've had success with PS 5 and 7 on DB2 V4 and 5, making a one process scheduler proc for each database. I bind the plan with currentdata(no) and isolation(ur) because of contention on process scheduler tables, which is the biggest problem.. [...] 11084 13 22_Re: Inconsistent Index0_19_Tim.Lowe@STPAUL.COM31_Wed, 24 Nov 1999 08:41:47 -0600359_us-ascii Can we start saying REBUILD INDEX instead of RECOVER INDEX to talk about rebuilding an index from the table?

================================================ 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. 11098 36 32_Re: converting plans to packages13_Thomas, Janis20_JANIS.THOMAS@CAI.COM31_Wed, 24 Nov 1999 09:59:27 -0500642_ISO-8859-1 Package/IT from Computer Associates can also be used to convert plans to packages. More information can be found here: http://www.cai.com/products/db2/packageit/overview.htm

Janis Thomas

-----Original Message----- From: Pelle Svennberg [mailto:per-erik.svennberg@NB.SE] Sent: Wednesday, November 24, 1999 5:56 AM To: DB2-L@RYCI.COM Subject: converting plans to packages



We are finally going to Packages all over in our system, and now I wonder if anyone know about products (either IBM or any vendor) that can help us doing all the stuff like Bind Packages using our current DB2 catalog as input? [...] 11135 209 39_Re: DATE function to reduce CPU time???20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 24 Nov 1999 10:00:05 -0500379_iso-8859-1 Deepak

The correct benchmarking would be to stop/start the tablespace & indexes and run the two test under similar condition (same # of I/O). If pages are in hyperpool, it could spoil your test. I doubt that you will ever get 17.5% gain.

Watch out for CLASS 2 TCB time. I don't find any gains in my experiments under following categories (DB2 V5) - [...] 11345 110 36_Re: Platinum Rapid Reorg and Maxrows19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 24 Nov 1999 08:09:13 -0800573_iso-8859-1 Let us know how it goes! Our shop uses the reallocation all the time here and it works fine (with SMS managed DB2 DASD pool).

"The light bulb works on my desk"

Tim > -----Original Message----- > From: Marcel Lepage [SMTP:iml@COLBA.NET] > Sent: Tuesday, November 23, 1999 10:49 PM > To: DB2-L@RYCI.COM > Subject: Re: Platinum Rapid Reorg and Maxrows > > Hi Tim, > > The parameter SPACE-DEFN as nothing to do with it, the result is absolutly > the same in both cases. We open a problem at Platinum about that issue and > still waiting for their [...] 11456 61 22_Re: Fwd: ADABAS to DB212_Doug Clifton16_dclifton@EV1.NET31_Wed, 24 Nov 1999 09:59:42 -0600779_us-ascii Don't forget about IBM's Database Tools for DB2 (and IMS). IBM offers a full range of database tools including Administration, Performance Analysis & Monitoring, and Utilities.

Regards, Doug Clifton Sr. Software Specialist Database Tools IBM cliftonw@us.ibm.com









> >X-Lotus-FromDomain: ZURICH@ZURICH-INTERNET > >Newsgroups: bit.listserv.db2-l > >Date: Mon, 22 Nov 1999 09:47:06 -0600 > >Reply-To: DB2 Data Base Discussion List > >Sender: DB2 Data Base Discussion List > >From: Dan Sullivan > >Subject: Re: ADABAS to DB2 > >To: DB2-L@RYCI.COM > >X-RCPT-TO: > > > >You will need some DB2 Management tools. You have 2 real choices, BMC or [...] 11518 36 23_DB2 Tools - CA/Platinum13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 24 Nov 1999 11:09:24 -0600423_iso-8859-1 I have a task to select the minimum subset of tools required to support a PeopleSoft environment from the CA/Platinum offerings.

Below is a list of the products in order of usefulness, and why. I have not used the products.

1. RC/Update - To make DDL changes to existing tables etc. 2. RC/Migrator - To manage the DDL changes through environments. 3. Database Analyzer - To execute utilities. [...] 11555 54 27_Re: DB2 Tools - CA/Platinum14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Wed, 24 Nov 1999 12:42:32 -0500466_us-ascii Hi Glenn,

I have been using Platinum (CA) since the early days, 1992?... and IMHO you neglected to mention one that I see as very important - RCQuery. It aids in viewing information stored in the DB2 catalog and acts a hub for the other product and the DBA to work from. Example - say you list all tables in a DB then enter a command 'Alter' next to a table listed - the product initiates an RCUpdate alter table process on that object. So IMHO [...] 11610 40 51_Oracle Transparent Gateway TCP/IP to DB2 for OS/39022_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 24 Nov 1999 12:46:47 -0500549_- We are DB2 for OS/390 V5.1 @9906, OS/380 V2.5 and are using TCP/IP & APPC connections into DB2 for OS/390 in a production environment.

We have a need to move DB2 for OS/390 data to an Oracle data base on a HP-UX box on a monthly schedule. The decision has pretty much been made to use the Oracle Transparent Gateway for IBM DRDA to move the data from DB2 to Oracle. On Oracle's web site I found a document copyrighted in 1996 that states that the Oracle Transparent Gateway for IBM DRDA supports APPC connections into DB2. It does not [...] 11651 32 27_Re: DB2 Tools - CA/Platinum9_Colin Fay13_cfay2@CSC.COM31_Wed, 24 Nov 1999 12:56:19 -0500519_us-ascii Hi Glenn,

In my opinion the most indespensible in RC/Migrator because you will be migrating tens of thousands of objects, depending on the modules you are purchasing. You should be aware that PS has it;s own upgrade tool and you will have to stake out your turf as to the extent that you will allow other tools and other parties updating table structures. Depending on the release this tool may or may not keep track of security authorizations, etc , so be careful. Make sure you have DDL backups. [...] 11684 93 30_Non-Uniform Distribution Stats10_Mark Labby16_mlabby@PHEAA.ORG31_Wed, 24 Nov 1999 13:08:41 -0500363_us-ascii Can anyone help explain to me how to use an index to support Non-Uniformly distributed data?

My understanding that the DB2 (OS/390 v5.1) Optimizer will potentially use the information in SYSCOLDIST & SYSCOLDISTSTATS when a query includes literals for a value that has very few occurrences, to greatly reduce the results set. Is this correct? [...] 11778 86 27_Re: DB2 Tools - CA/Platinum11_Danny Davis16_DTDAVIS@PCMH.COM31_Wed, 24 Nov 1999 13:25:53 -0500645_US-ASCII My choices

rc/query database analyzer rc/migrator

Danny Davis Systems Analyst Univerity Health Systems dtdavis@pcmh.com

>>> William Gannon 11/24 12:42 PM >>> Hi Glenn,

I have been using Platinum (CA) since the early days, 1992?... and IMHO you neglected to mention one that I see as very important - RCQuery. It aids in viewing information stored in the DB2 catalog and acts a hub for the other product and the DBA to work from. Example - say you list all tables in a DB then enter a command 'Alter' next to a table listed - the product initiates an RCUpdate [...] 11865 69 27_Re: DB2 Tools - CA/Platinum10_Ulsas, Tom23_tulsas@DOIT.STATE.IN.US31_Wed, 24 Nov 1999 13:16:17 -0500476_iso-8859-1 Glenn,

Peoplesoft demands significant effort managing objects, managing data and tuning SQL (some of it very poorly written). To that end my top three choices would be: RC/Migrator Database Analyzer Plan Analyzer.

As far as monitoring the application, you can limp along with a general purpose monitor, but SubSystem Analyzer and Detector are designed expressly for drilling into those errant SQL statements plus lots of other neat capabilities. [...] 11935 128 27_Re: DB2 Tools - CA/Platinum12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Wed, 24 Nov 1999 13:38:50 -0500630_us-ascii

As I am currently in a PeopleSoft DB2 OS/390 environment too, I would definitely ask for RC/Query first (unless you already have another tool like BMC Catalog Manager ...)

Number #2 would be RC/Migrator to help with object migrations from one DB2 environment to another...

IMHO, I would next get Database Analyzer to help with automated object monitoring and utility generation... However there is a long learning curve (and a lot of product customization) with Database Analyzer and quite a few DBA folks just give up before they actually learn how to use the product fully - i.e. the biggest [...] 12064 21 30_People Soft Proccess Scheduler0_39_brian_holroyd.consultant@PEOPLESOFT.COM31_Wed, 24 Nov 1999 10:52:27 -0800385_us-ascii You need to bind PTPSQLRT to create 3 different plans against your 3 environments (not PTPUPRCS - that doesn't have any SQL in it). Then you run PTPUPRCS as a batch job on the mainframe using RUN PROGRAM (PTPUPRCS) - PLAN ()

You also need to do the same thing for the program called SQR RUN PROG(SQR) - PLAN() - LIB('SYS2.SQR.LOAD') - [...] 12086 132 27_Re: DB2 Tools - CA/Platinum12_Kapil Mathur25_Kapil.Mathur@WACHOVIA.COM31_Wed, 24 Nov 1999 18:39:17 +0000630_us-ascii

As I am currently in a PeopleSoft DB2 OS/390 environment too, I would definitely ask for RC/Query first (unless you already have another tool like BMC Catalog Manager ...)

Number #2 would be RC/Migrator to help with object migrations from one DB2 environment to another...

IMHO, I would next get Database Analyzer to help with automated object monitoring and utility generation... However there is a long learning curve (and a lot of product customization) with Database Analyzer and quite a few DBA folks just give up before they actually learn how to use the product fully - i.e. the biggest [...] 12219 40 30_need help with pc to mainframe14_Marg Gozdowski27_marg.gozdowski@LA-Z-BOY.COM31_Wed, 24 Nov 1999 14:32:50 -0500355_iso-8859-1 Thanks to the group's help, I have created a ddf on a test db2 system on a test lpar.

It looks like the pc is connecting to the host ok.

According to instructions for pc access, all I should have to do is create a collection id for the pc on the mainframe db2, & when an exe runs, it will create packages in this collection. [...] 12260 155 34_Re: Non-Uniform Distribution Stats20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 24 Nov 1999 14:47:09 -0500563_iso-8859-1 Mark

You are right that filter factor is based on COLCARDF, FIRSTKEYCARDF (for single column index) or FREQUENCYF . Tool may be wrong by not displaying 1/FREQUENCYF as filter factor. But I don't agree with you that 2% of 11 million row is small subset.

2% is 220000 rows and based on non-clustering index there may be massive I/O - A rough estimate would be NLEVEL + NLEAF/FREQUENCYF + 220000 * Weightage for CLUSTERRATIO. List prefetch could help alleviate # of I/Os but still too many pages & I/O and that is why index is ignored. [...] 12416 153 34_Re: Non-Uniform Distribution Stats18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Wed, 24 Nov 1999 13:47:21 -0600549_iso-8859-1 Mark, You might not want to use your second index even if WE BOTH understood non-uniform distribution statistics. Looks to me like you've got a "triple whammy" going here. Not only is the high-order column of the primary key index needed by two of your predicates, its needed to satisfy the ORDER BY. Even if you forced it to use the second index it might not be worth it based on the number rows returned and the subsequent sort required for ORDER BY. I'd try it both ways and measure "wall time". Try this to force the use of your [...] 12570 73 34_Re: need help with pc to mainframe17_Linda F. Claussen18_lindafc@NETINS.NET31_Wed, 24 Nov 1999 14:11:35 -0600491_ISO-8859-1 Marg,

You're confusing Plans and Packages. A package is contained within a collection, not the reverse.

The Application Programming Guide has a section on Preparing your application and the Step about Binds should help you.

We normally create a PLAN with the PKLIST option to setup a collection:

BIND PLAN(plan-name) PKLIST(location-id.collection-id.*)

Then we Grant the appropriate BIND and CREATE IN COLLECTION (collection-id) TO auth-id [...] 12644 21 20_Year 2000 procedures0_28_staci_stephenson@ACS-INC.COM31_Wed, 24 Nov 1999 14:13:50 -0600384_US-ASCII Is anyone implementing any special procedures or schedules with their DB2 subsystems for the actual 1/1/2000 date change? (i.e. taking extra backups on 12/31; bringing DB2 down on 12/31 before midnight and up on 1/1 after the date has rolled; etc.) We are going to do some of this with our IMS systems and I was curious if anyone is planning on doing the same with DB2. [...] 12666 47 24_Re: Year 2000 procedures22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 24 Nov 1999 15:22:22 -0500720_- We're DB2 for OS/390 V5.1.

We're keeping DB2 up during the 12/31/1999 to 1/1/2000 roll over. We've tested this many times on our Y2K test LPAR and have not discovered any problems.



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: > staci_stephenson@ACS-INC.COM[SMTP:staci_stephenson@ACS-INC.COM] > Sent: Wednesday, November 24, 1999 3:13 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Year 2000 procedures > > Is anyone implementing any special procedures or schedules with their DB2 > subsystems for the actual 1/1/2000 date change? (i.e. taking extra backups > on > 12/31; bringing DB2 down on 12/31 before midnight and up on 1/1 [...] 12714 28 24_Re: Year 2000 procedures0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 24 Nov 1999 14:23:42 -0600292_us-ascii I'm not directly involved with the date roll over (I got Xmass instead, yea!). From what I've heard, we are planning on shutting down everything we can except for OS/390. Then things will be restarted and tested one at at time, to try and localize problems as much as possible. [...] 12743 134 27_Re: DB2 Tools - CA/Platinum14_Mark R. Walker26_mark.walker@CUNAMUTUAL.COM31_Wed, 24 Nov 1999 14:34:35 -0600462_us-ascii



Glenn,

If you are limited to having only two tools, go for RC/Migrator and RC/Compare. We live and die by these for managing our environments. If you can get RC/Query that would help too. We've been using the Platinum tools for close to seven years now.

Mark Walker CUNA Mutual Group







(Embedded image moved "Mackey, Glenn" to file: 11/24/99 11:09 AM pic03481.pcx) [...] 12878 92 34_Re: need help with pc to mainframe11_rick creech18_ykcirc@HOTMAIL.COM29_Wed, 24 Nov 1999 12:32:01 PST638_- ALSO, THE USERID DOING THE BINDS WILL NEED BINDADD AUTH. THE NAME OF THE COLLECTION NEEDED FOR DB2 CONNECT FOR THE 'CREATE IN' AUTH IS NULLID. REGARDS, RICK CREECH >From: "Linda F. Claussen" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: need help with pc to mainframe >Date: Wed, 24 Nov 1999 14:11:35 -0600 > >Marg, > >You're confusing Plans and Packages. A package is contained within a >collection, not the reverse. > >The Application Programming Guide has a section on Preparing your >application and the Step about Binds should help you. > >We normally create [...] 12971 13 24_Re: Year 2000 procedures12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Wed, 24 Nov 1999 14:33:20 -0600342_us-ascii We are shutting down at about 8:00 P.M. and bringing it back up at 4:00 A.M. on 01/01/2000.

================================================ 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. 12985 53 24_Re: Year 2000 procedures15_Lyle Kelly-LH-117_kelly.lh.1@PG.COM31_Wed, 24 Nov 1999 15:41:39 -0500347_us-ascii Our DB2 operations team (the SYSADMs) will bring down all of our production subsystems, and will make copies of non-application tables, before Midnight. These subsystems will be brought up and checked after the new year has begun. Additional application-level testing will occur before the first business day in 2000. -- Lyle at P&G [...] 13039 28 24_Re: Year 2000 procedures12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 24 Nov 1999 14:47:30 -0600489_us-ascii At our shop, we aren't using DB2 in production yet. But the plans regarding existing production systems are to bring up *every* system on Saturday (1/1/2000) as though it were a "work" day (we are not a 24x7 shop), with programmers entering data directly into production files (I wonder if the auditors know about that!) and then running full "work" day night production streams, jimmying date cards and Job Scheduler (Zeke) date $Variables as necessary to make it all work. [...] 13068 36 24_Re: Year 2000 procedures0_23_Mike_Levine@TEKHELP.NET31_Wed, 24 Nov 1999 15:51:34 -0500338_us-ascii Hi,

Wouldn't it be great if everyone did this? Then nobody would have to be on-call or on-site and could enjoy the Millennium change with their friends and families! (note: yes I know it's really not THE Millennium change, but it's still a pretty exciting and noteworthy event).

All systems will be up here... [...] 13105 51 24_Re: Year 2000 procedures11_Ron Johnson25_rjohnson@DATA-TRONICS.COM31_Wed, 24 Nov 1999 14:55:50 -0600572_iso-8859-1 We are planning on bringing down everything but OS/390 just before midnight. We might leave up DB2...I'm not sure if that has been decided. Since everything else will be down I don't think it matters much. I have been kicking around the idea of generating a mass image copy job for tablespaces not copied in the last few days. Since the 31st is a Friday, the objects copied only on the weekend will have lengthy recovery times if needed. I'm not worried about DB2 itself. I'm more concerned with applications needing to recover to a point in time prior to [...] 13157 14 24_Re: Year 2000 procedures12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Wed, 24 Nov 1999 14:52:55 -0600399_us-ascii We are only doing that incase there are problems with electricity, the building, etc... There will be no problems with DB2 everyone knows that......

================================================ 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. 13172 19 23_UDB 6.1 Features - AST?0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 24 Nov 1999 14:51:56 -0600361_us-ascii I was reading about features in UDB 6.1 in the Fall DB2 Magazine, and saw something I could use right now. Automatic Summary Tables (ASTs) would fit the bill for some reports I have to generate tracking CICS usage. We are currently OS390 V2.4, DB2 V5.1. I haven't had a chance to check out the IBM site yet so I'll toss the questions at the list. [...] 13192 76 27_Re: DB2 Tools - CA/Platinum14_Marilyn Stokes28_mstokes@OPPENHEIMERFUNDS.COM31_Wed, 24 Nov 1999 14:05:38 -0700372_us-ascii Glenn,

I have all of the Platinum DBA tools, and this is what I would recommend:

RC/Query - a must. RC/Migrator (RC/Compare is built in) if you are responsible for migrating changes from one environment to another. Does your vendor supply the DDL/conversion processes? Database Analyzer - I hear it is great, which brings me to my last point - [...] 13269 17 46_Re: UDB v6.1 Control Center - Client Login ID?0_15_leon@CA.IBM.COM31_Wed, 24 Nov 1999 16:01:07 -0500516_us-ascii The inconvenience of needing a special id and a need to log on have been removed in Version 7.1 of Control Center. V7.1 is not too far away so the pain is not going to last for too long.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.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. 13287 32 23_DB2 Tools - CA/Platinum10_Derek Lund22_derek.lund@UTORONTO.CA31_Wed, 24 Nov 1999 16:10:34 -0500529_US-ASCII IMHO,

RC/Migrator (must have, biggest time saver) RC/Update (sure nice to have)

RC/Query (okay, but personally prefer to go to catalog directly using QMF) PDA (Prefer IBM utilities and to not rely on 3rd party)

We lease IBM's monitor, somewhat clunky, but relatively cheap.

We are planning on investigating a "LOG ANALYZER" product to 'redo' or backout changes done through programs/sql by error (Versus DSN1COPY/headache/headache) (and/or a bigger bazooka for the users and developers) [...] 13320 58 27_Re: UDB 6.1 Features - AST?16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Wed, 24 Nov 1999 16:39:41 -0500628_iso-8859-1 Rohn,

AST's according to IBM are 1 of the top 2 priorities for BI for the OS/390, being worked on for a future enhancement. They are not in V6 and at this time.



Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...] 13379 16 27_Re: UDB 6.1 Features - AST?12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 24 Nov 1999 13:41:30 -0800408_us-ascii Summary tables are not in Version 6 of DB2 for OS/390. Trying to put a change this massive in with an APAR would be called death by APAR.

Roger Miller

================================================ 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. 13396 25 29_Cobol compilation data(24/31)9_Gerard Ip26_Gerard.Ip@COLESMYER.COM.AU31_Thu, 25 Nov 1999 16:47:42 +1100407_iso-8859-1 James,

Is there a way to find out whether one of the modules in a load module uses compilation option DATA(24) or DATA(31)?

Assumpting load module A consists of modules (sub-program) A1, A2 & A3. Can I find out whether A1 is compiled using DATA(24) or DATA(31)?

Regards Gerard IP IMS/DB2 DBA Group ITD, Coles Myer Ltd Phone: 9829 5438 email : Gerard.IP@colesmyer.com.au [...] 13422 58 33_Re: Cobol compilation data(24/31)14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Thu, 25 Nov 1999 19:00:59 +1000356_us-ascii Gerard,

Which "James" do you mean, there are so many of us. In fact, there are even two James Campbells on this list now!

Which version of COBOL? The VS Cobol Debugging guide shows a string of bits, just after the program name and date/time of compilation, that indicate various options used for the compilation including DATA. [...] 13481 66 24_Re: Year 2000 procedures18_Krom, A.G. - SPLXM15_Ton.Krom@KLM.NL31_Thu, 25 Nov 1999 09:24:01 +0100327_iso-8859-1 We bring down IMS and DB2 at 23:45 and start them again at 00:05. Not because we expect problems with IMS or DB2 (it's all tested), but because it's the easiest way to get a general syncpoint for all userdata. In case of application-problems we can recover all concerned data to the nearest point before 24:00. [...] 13548 43 24_Re: Year 2000 procedures22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 25 Nov 1999 08:11:31 +0000298_ISO-8859-1 we are taking offline backups on the 31/12 .. shutting the machine down and then bringing things up one at a time from 12 lunchtime on Newyears day .. user testing is to be done in the pm .. then normal processing starts shortly after ..

Leslie Pendlebury-Bowe DB2 SAP OS390 [...] 13592 286 39_Re: DATE function to reduce CPU time???13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Thu, 25 Nov 1999 14:49:33 +0530482_iso-8859-1 Hi Venkat,

I ran both the jobs 'with Date Clause' and 'Without Date Clause' in similar conditions(Our shop works on DB2 4.1v). Let me tell you exactly what I did during my testing this 'Date Clause'. ===> I have a Ezt program which access DB2. The SQL what I used is : SELECT TYPE , EFF_DT , PROC_DT , PROC_TSTMP FROM QZ4PBAT.V_IN_BEN_COMP WHERE SSN = :WW-SSN AND SSN_TYPE = 'S' AND INV_DT = '0001-01-01' ORDER BY EFF_DT DESC , PROC_DT DESC , PROC_TSTMP DESC [...] 13879 173 27_Re: DB2 Tools - CA/Platinum22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 25 Nov 1999 08:16:17 +0000381_ISO-8859-1 Marilyn/All,



I think PDA is a must .. if you wish to automate things .. I am not going to bang on about it hear though. Note that PDA does have shortcomings .. if you wish to know about them then email me direct.

What about your Disaster Recovery? do you automate this at all ? Platinum Recovery analyser is getting there ... and does work .. [...] 14053 69 27_Re: DB2 Tools - CA/Platinum13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Thu, 25 Nov 1999 09:55:46 +0000403_us-ascii Glenn,

I'd make the order -

1) RC/Migrator 2) RC/Compare 3) Database Analyzer 4) RC/Update

RC/Update is for ad hoc changes and we use RC/Migrator for everything.

RC/Compare will become essential when you plan to upgrade PeopleSoft, unless you plan to follow all the PeopleSoft scripts which often have errors. (Contact me if you need more info on what I mean). [...] 14123 161 27_Re: DB2 Tools - CA/Platinum10_Ven Ilagan18_vilagan@ONE.NET.AU31_Thu, 25 Nov 1999 20:43:25 +1100621_iso-8859-1 Hi There,

My two cents worth........

1. RC/Migrator - A dog in terms of run-times, i.e. Analyzing and updating is sometimes VERY SLOW, but still an essential tool in terms of managing lots of environments (Devt to System to Testing to Acceptance to Production). It's also got Compare built into it so comparing two environments and spotting differences is quite easy - even manages to generate DDL for the difference. This is a MUST in my opinion and serves as an Audit trail of all mods/revisions to the DB2 objects under your Administration. It can generate strategies to unload, redefine [...] 14285 327 39_Re: DATE function to reduce CPU time???0_19_mike.holmans@BT.COM31_Thu, 25 Nov 1999 10:30:52 -0000514_ISO-8859-1 Deepak,

What happens if you run the query using the DATE() function first, and then the one without the DATE() function immediately afterwards? Do you still see the first one running at 17% less cost?

My immediate reaction on seeing your account was that the difference in cost and time could be attributed entirely to the rows not being in the bufferpool the first time you execute, but being there the second time, which means that you don't incur the I/O cost of the table access. [...] 14613 376 39_Re: DATE function to reduce CPU time???13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Thu, 25 Nov 1999 17:41:57 +0530537_iso-8859-1 Yeah Mike, I did that. I ran with 'DATE' Clause first and then without 'DATE' Clause.... Now I am finding totally different result.. a loss of 17%. You are right when you said about bufferpool. So I have come to a conclusion that 'DATE' clause does not have any impact or negligible impact on CPU time or Cost. I said negligible b'cos only little time saved when we include 'DATE' Clause in SQL query. The saved time is solely on verification of the value that we have given in the condition (i.e of DATE Format or not). [...] 14990 188 27_Re: DB2 Tools - CA/Platinum0_19_mike.holmans@BT.COM31_Thu, 25 Nov 1999 12:34:50 -0000513_ISO-8859-1 We've been using RC/Migrator for years.

In terms of it being a dog for run-time, the main reason for that is that it issues catalog queries which don't match the default indexes on the catalog. If you watch the SQL statements it executes through a performance monitor and build indexes on the catalog, or you use the alternate catalog mechanism so that it queries a shadow catalog which you've optimized with the ideal indexes, then your runtime problems with the tool are sharply reduced. [...] 15179 39 36_Lost Archive Log (relatively URGENT)22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 25 Nov 1999 10:48:09 -0200429_iso-8859-1 Our achive log was partially lost (fortunately in our test subsystem) and it is impossible to start DB2 again because of this lack of archive logs. Our BSDS remains OK. It seems to be necessary to perform a cold restart to a new RBA grater than the last used. We did not find any clear instructions about it in Administration or Utility guides. Which are the steps to perform such cold start? We are planning to: [...] 15219 61 55_Re: Oracle Transparent Gateway TCP/IP to DB2 for OS/39010_Robert Ord21_robertord@HOTMAIL.COM29_Thu, 25 Nov 1999 13:29:11 GMT646_- As far I am aware the Oracle transparent gateway doesn't support a TCP/IP DRDA connections. We had to install SNA on our unix boxes.



Rob



>From: Piontkowski Michael ML >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Oracle Transparent Gateway TCP/IP to DB2 for OS/390 >Date: Wed, 24 Nov 1999 12:46:47 -0500 > >We are DB2 for OS/390 V5.1 @9906, OS/380 V2.5 and are using >TCP/IP & APPC connections into DB2 for OS/390 in a production >environment. > >We have a need to move DB2 for OS/390 data to an Oracle data base >on a HP-UX box [...] 15281 71 40_Re: Lost Archive Log (relatively URGENT)12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Thu, 25 Nov 1999 14:10:00 -0000605_- It sounds like you need chapter 4-8 in the Admin Guide, RECOVERY FROM BSDS OR LOG FAILURE DURING RESTART. (p 4-199 Admin v4.1).

Determine if you are failing during Forward Recovery or Backward Recovery from your startup messages in the system log ie. what phases has restart completed before it falls over? If its completed the forward log recovery and is in the backward recover phase you will only need to specify BACKOUT=NO not an end rba. You will then need to go through the steps under the heading Resolving Inconsistencies Resulting from Conditional Restart (p4-226 ADmin Guide v4.1) [...] 15353 67 16_Re: JDBC drivers0_15_leon@CA.IBM.COM31_Thu, 25 Nov 1999 00:51:18 -0500281_us-ascii If your Java code is running on a UNIX or Windows NT web server or if you are coding Java applets then you will need to have DB2 Connect Enterprise Edition. The JDBC drivers you get from DB2 for OS/390 web site are strictly for running Java code on the S/390 itself. [...] 15421 141 39_Re: Workload Balancing over DB2 Connect0_15_leon@CA.IBM.COM31_Wed, 24 Nov 1999 23:58:25 -0500557_us-ascii Victor, there are several solutions for fail over and load balancing of DB2 Connect gateways. One that we recommend is not to use a gateway at all for 2-tier applications and use direct TCP/IP connectivity instead. You will be rewarded with much better performance, lower host CPU and considerably simpler environment to set up. DB2 Connect Enterprise Edition product should be used for web and application servers and not as a gateway when native TCP/IP is available. But I know tat you've heard this message before so I will stop preaching. [...] 15563 40 16_SLQ Optimization53_=?iso-8859-1?Q?=B0G=E9nie_Logiciel_Admin_M=E9thodes?=29_madvie@AXA-FRANCEASSURANCE.FR31_Thu, 25 Nov 1999 17:10:17 +0100403_iso-8859-1 Hi anyone,

I've got a basic problem of SQL optimization: the end user gives 2 Host Variables which can be in three different columns:

SELECT NO_INTERNE , NOM_ENTREPRISE FROM TTISRN WHERE ( MOT_CLE_1 = :HST-MOT-CLE-1 OR MOT_CLE_2 = :HST-MOT-CLE-1 OR MOT_CLE_3 = :HST-MOT-CLE-1 ) AND ( MOT_CLE_1 = :HST-MOT-CLE-2 OR MOT_CLE_2 = :HST-MOT-CLE-2 OR MOT_CLE_3 = :HST-MOT-CLE-2 ) [...] 15604 42 11_Hybrid Join16_Michael McMorrow25_michael.g.mcmorrow@AIB.IE31_Thu, 25 Nov 1999 16:38:53 -0000368_ISO-8859-1 Hi,

A follow on from my mail of 19 Nov titled 'Uncomitted Read'.

Thanks to Steve, Paul and Moira for replies.

The problem I described very strongly now points towards Hybrid Join rather than Uncomitted Read. We've identified that the problem cursor seems to have missed every 429th row which should have existed in the result set. [...] 15647 67 20_Re: SLQ Optimization11_Gary Wilson21_Gary_Wilson@NPI.CO.UK31_Thu, 25 Nov 1999 11:05:56 -0800677_us-ascii how many rows in the table?



______________________________ Reply Separator _________________________________ Subject: SLQ Optimization Author: madvie@AXA-FRANCEASSURANCE.FR at INTERNET Date: 25/11/1999 16:44



Hi anyone,

I've got a basic problem of SQL optimization: the end user gives 2 Host Variables which can be in three different columns:

SELECT NO_INTERNE , NOM_ENTREPRISE=20 FROM TTISRN =20 WHERE ( MOT_CLE_1 =3D :HST-MOT-CLE-1=20 OR MOT_CLE_2 =3D :HST-MOT-CLE-1 =20 OR MOT_CLE_3 =3D :HST-MOT-CLE-1 ) =20 AND ( MOT_CLE_1 =3D :HST-MOT-CLE-2 =20 OR MOT_CLE_2 =3D :HST-MOT-CLE-2 =20 OR MOT_CLE_3 =3D :HST-MOT-CLE-2 ) [...] 15715 110 40_Re: Lost Archive Log (relatively URGENT)12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Thu, 25 Nov 1999 17:24:00 -0000558_- First, I would consider recovering the whole system to a prior backup, at least you'll know it's consistent.

If you can't or want to try all other options first get volume dumps of all of your system done if possible, get DFDSS dumps of all DB2 datasets if not.



It looks like there was a SPUFI rolling back which was running under the ID SYSADM. Whatever this was doing will be left in an inconsistent state with data left on the table which shouldn't be there. If you can live with this then you should get away with a CRESTART [...] 15826 20 34_Controling Resources at User Level10_Ravi Verma26_ravi.verma@TELECOMMAND.COM31_Thu, 25 Nov 1999 09:47:16 -0800412_us-ascii Dear Friends,

Is there a way in DB2 to limit the resources a database user can consume in terms of CPU time, memory etc?

Regards.

Ravi Verma

================================================ 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. 15847 36 36_Re: Lost Archive Log - Poblem Solved22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 25 Nov 1999 17:14:39 -0200361_iso-8859-1 I followed the steps suggested by Colin and it worked very well. I used the DSNJU003 (option CRESTART CREATE,BACKOUT=NO) with success. After that, I started the subsystem with ACCESS(MAINT) and executed the steps in "Resolving Inconsistencies Resulting from Conditional Restart". I did not find any inconsistency (LPL) in both catalog and data. [...] 15884 173 34_Re: Non-Uniform Distribution Stats14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 26 Nov 1999 09:29:28 +1000419_us-ascii Mark,

Imbedded replies below, but bear in mind the index you want to use is probably not the best on its own. Try adding BF_SSN to your index as second column.

From: Michael Hannan



>From: Mark Labby >Subject: Non-Uniform Distribution Stats >To: DB2-L@RYCI.COM > >Can anyone help explain to me how to use an index to support Non-Uniformly >distributed data? [...] 16058 72 20_Re: SLQ Optimization14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 26 Nov 1999 09:29:35 +1000461_iso-8859-1 Something to try for fun.

Please rewrite query predicates:

WHERE (MOT_CLE_1 = :HST-MOT-CLE-1 AND MOT_CLE_2 = :HST-MOT-CLE-2 OR MOT_CLE_1 = :HST-MOT-CLE-1 AND MOT_CLE_3 = :HST-MOT-CLE-2 OR MOT_CLE_2 = :HST-MOT-CLE-1 AND MOT_CLE_1 = :HST-MOT-CLE-2 OR MOT_CLE_2 = :HST-MOT-CLE-1 AND MOT_CLE_3 = :HST-MOT-CLE-2 OR MOT_CLE_3 = :HST-MOT-CLE-1 AND MOT_CLE_1 = :HST-MOT-CLE-2 OR MOT_CLE_3 = :HST-MOT-CLE-1 AND MOT_CLE_2 = :HST-MOT-CLE-2 ) [...] 16131 17 63_Any Utility in UDB V5.2 on NT equivalent to DSN1CHKR on OS/390?11_Roland Chua21_rolandchua@SES.COM.SG31_Fri, 26 Nov 1999 09:38:14 +0800487_us-ascii Hi,

I need to verify the intergrity of UDB directory and catalog table spaces (running on NT platform). Does anyone know which utility to run? On MVS, I know I can used DSN1CHKR, but I could not find an equivalent utility in UDB.

================================================ 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. 16149 64 23_Re: Level-ID problem...13_Marcel Lepage13_iml@COLBA.NET31_Thu, 25 Nov 1999 22:43:46 -0700552_iso-8859-1 Hi Ravi,

You REPAIR should be use in order to solve your problem.

Marcel Lepage



Paul A Redhead a écrit :

> Ravi, > You can recover the pages in error. See V5 Administration Guide section > 4.2.1.2.2 for a discussion on the subject (if you are at another version I > don't have a reference for you). > > Paul. > > >>>>>>>>>>>>>>> > Hi, > A few of the TS' have sprung LPL problems. The Admin. guide says that > > issuing a start on the . with access(rw/ro) should reset it. But > that > is not [...] 16214 67 68_Re: Any Utility in UDB V5.2 on NT equivalent to DSN1CHKR on OS /390?0_29_Greg.Palgrave@BANKWEST.COM.AU31_Fri, 26 Nov 1999 12:21:30 +0800486_us-ascii Roland,

There is a DB2DART utility in the SQLLIB\BIN directory - check the doco on how to run it. Use at your own risk!

Regards,

Greg Palgrave Database Administration Group Information Services, Level 10, Tower Bank of Western Australia eMail : greg.palgrave@bankwest.com.au



Roland Chua on 26/11/99 09:38:14

Please respond to DB2 Data Base Discussion List [...] 16282 15 45_STANLEY GOODWIN/DACMech is out of the office.0_29_STANLEY_GOODWIN@MECH.DISA.MIL31_Fri, 26 Nov 1999 01:00:51 -0500385_us-ascii I will be out of the office starting 11/25/99 and will not return until 11/30/99.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16298 21 16_Resource Control10_Ravi Verma26_ravi.verma@TELECOMMAND.COM31_Thu, 25 Nov 1999 22:45:21 -0800462_iso-8859-1 Hi,

I would like to know if there is a way in DB2 to limit the amount of resources a particular user can use in terms of CPU time, memory etc. Oracle has this feature.

Regards.

Ravi Verma.

================================================ 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. 16320 54 20_Re: Resource Control14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Fri, 26 Nov 1999 17:54:58 +1000543_us-ascii Hi Ravi,

You can limit the CPU time for a single dynamic SQL using the 'Resource Limit Facility' (governor), which is described in the Admin Guide Sect 5.5.2(?).

Static SQL is presumed to have been validated by an appropriate person.

There are additional methods that can be used to limit the resources limit a 'session' (eg a job; TSO session or transaction) consumes. But these depend on what type the session is and are outside DB2's control (but see the Admin Guide Sect 5.5.1 for a brief introduction). [...] 16375 85 11_SQL Problem14_Romeo B Titong26_rbtitong@ACISYSTEMS.COM.PH31_Fri, 26 Nov 1999 14:52:03 +0800458_us-ascii Hi,

I need some help in constructing a select statement that will sort according to time but still grouped by name. As shown below, the result should be sorted by time but all records of that employee should follow immediately.

Thanks in advance.



Employee Table InOut Table Empno Lastname Empno In Out 001 Smith 001 9:20 11:54 002 Doe 001 12:58 16:30 003 Ford 002 9:23 004 Carlson 003 9:10 12:00 003 13:05 004 9:15 [...] 16461 73 15_Re: SQL Problem14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Fri, 26 Nov 1999 18:18:42 +1000375_us-ascii Romeo

Off the top of my head, how about:

select E.Empno, E.Lastname, IO.In, IO.Out, mi.min_in from Employee E, InOut IO , (select Empno, min(In) as min_in from InOut group by Empno) as mi where E.Empno = mi.Empno and E.Empno = IO.Empno order by mi.min_in, E.Empno, IO.In

The mi.min_in has to be in the select list for the order by to work. [...] 16535 139 15_Re: Hybrid Join0_29_Adrian.Talbot@BANKWEST.COM.AU31_Fri, 26 Nov 1999 15:41:07 +0800451_us-ascii Hi Micheal,

We had exactly the same problem after going to DB2 V5.1 with Hybrid join when you have CURSOR WITH HOLD.

At the time we were out of luck as IBM hadn't got a fix and didn't have local fix.

What I tried was forcing the cursor to materialise by adding an extra column to the sort so that it had to do sort instead of using the index for the order by. IBM then added this in the problem text as a local fix. [...] 16675 15 42_Gordon Reich/WCBAlta is out of the office.0_22_gordon.reich@WCB.AB.CA31_Fri, 26 Nov 1999 01:01:51 -0700389_us-ascii I will be out of the office starting 1999/11/26 and will not return until 1999/11/29.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16691 19 24_Re: s0d7 reason 00c200d312_Perry Pounds27_Perry.Pounds@DIRECTLINE.COM31_Fri, 26 Nov 1999 08:44:48 +0000451_us-ascii We have preiodically expereinced the following error that has resulted in Db2 abending : s0d7 reason 00c200d3 Explanation: A prior abend occurred in a 'must complete' processing window of a buffer manager (BM) function. IBM have been unable to provide an answer or indeed to recreate the problem. I wondered if anyone else has exeperience the above problem. My only course of action is to ensure that I am up to date on Db2 maintenance. [...] 16711 15 19_Re: Truncated logs.12_Perry Pounds27_Perry.Pounds@DIRECTLINE.COM31_Fri, 26 Nov 1999 08:48:21 +0000307_us-ascii We currently have some truncate logs. To fix the problem means that I have to close Db2, and edit the BSDS. We are fortunate in that we do not have a 24x7 system. I was curious as to how sites that operate 24x7, in a non-sysplex environment, deal with the problem, or indeed guard against it. [...] 16727 34 19_Re: Truncated logs.12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Fri, 26 Nov 1999 09:14:00 -0000354_- I'm not sure on this one but I thought truncated logs only remained truncated for the current log dataset cycle. If you wait for the logs to cycle around you may find that they have reverted to normal.

Colin Allen DBA Abbey National ---------- From: Perry Pounds To: DB2-L@RYCI.COM Subject: Re: Truncated logs. Date: 26 November 1999 08:48 [...] 16762 15 19_Re: Truncated logs.12_Perry Pounds27_Perry.Pounds@DIRECTLINE.COM31_Fri, 26 Nov 1999 09:20:46 +0000491_us-ascii You are correct, have just switched our logs and the truncation goes. It does seem a coincidence that it is the same set of journal that appears to be truncated. Anyway, maybe should have RTFM prior to sending note. Thank you for your help.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16778 153 20_Re: SLQ Optimization0_18_mebert@AMADEUS.NET31_Fri, 26 Nov 1999 10:43:24 +0100595_us-ascii The query below is equivalent only if :HST-MOT-CLE-1 and :HST-MOT-CLE-2 are guaranteed to be different. Otherwise you'd have to add ... OR (MOT_CLE_1=:HST-MOT-CLE-1 AND MOT_CLE_1=:HST-MOT-CLE-2) OR.... for all 3 columns, making your query somewhat lengthy and obscure (Parentheses added for clarity).

Further, you should probably have 3 indexes, the two by Michael Hannan and also MOT_CLE_1, MOT_CLE_3. You might also try using 3 indexes (MOT_CLE_1), (MOT_CLE_2), (MOT_CLE_3) together with your original query. The best choice would depend on filter factors, so experiment. [...] 16932 51 19_Re: Truncated logs.11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Fri, 26 Nov 1999 06:31:02 -0500376_ISO-8859-1 Perry

Colin is correct. Truncated log datasets will be reused normally unless the cause of the truncation persists. In your case the cause of the truncation is probably an -ARCHIVE LOG command. Use DSNJU004 to print the BSDS. Check the date/time of the end of the truncated log against the list of Archive Log commands - you will probably find a match. [...] 16984 97 6_MLT IX4_vcei14_vcei@CEGEKA.BE31_Fri, 26 Nov 1999 12:24:36 +0100563_iso-8859-1 Hello Listers,

We`ve posted this problem several weeks ago yet, but still haven`t found a solution. Thought we`d try it again, hoping that someone could explain *why* DB2 V5 (OS/390) does the following. Several queries use 2 indexes, where - in our humble opinion - using the second one is complete nonsense: index1 - matching 4 cols (out of 5) index2 - matching 2 cols (out of 4) First 2 columns are identical !!!! Moreover: col 3 and 4 of index2 are not referred to in the query. Even if index screening would be used - and it`s not, due [...] 17082 273 27_Re: DB2 Tools - CA/Platinum13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Fri, 26 Nov 1999 12:24:41 +0000379_us-ascii To add to Mike's note, our Platinum rep gave us documentation about what additional catalog indexes are recommended to improve the performance of the Platinum toolset queries.

Ask your rep and he should be able to get this for you.

If not, I'll try to dig out my copy.

Phil

On Thu, 25 Nov 1999 12:34:50 -0000, mike.holmans@BT.COM wrote: [...] 17356 97 55_Re: Oracle Transparent Gateway TCP/IP to DB2 for OS/39026_Singh, Robi R SSI-TSEA-34225_Robi.R.Singh@IS.SHELL.COM31_Fri, 26 Nov 1999 13:15:47 +0100305_iso-8859-1 Rob,

Yes it does. We run Oracle 7 (and 8) to DB2 V5 using OTG V 4011 over TCPIP. As far as I know this has been possible for quite some time. Michael can contact me directly if he wants more details. Because seeing is believing, see below for some messages from the network task ;- [...] 17454 37 20_SYSPROCEDURES Insert14_Julian Stuhler20_stuhlej@TRITON.CO.UK31_Fri, 26 Nov 1999 12:51:13 -0000421_iso-8859-1 Hi everyone.

I'm currently having some discussions with a client regarding the process for stored procedure implementation, and I'd be interested in a quick poll as to what the rest of you do.

If you a) use stored procedures and b) use Endevor (or a similar code management tool), have you automated the insert into SYSPROCEDURES or is it done manually? Why did you chose to do it that way? [...] 17492 59 23_DB2 Tools - CA/Platinum12_Dennis Raher27_Dennis_Raher@COMPUSERVE.COM31_Fri, 26 Nov 1999 08:56:47 -0500581_ISO-8859-1 Hi Ven,

Your comment:

"1. RC/Migrator - A dog in terms of run-times, i.e. Analyzing and updating is sometimes VERY SLOW"

A tip that makes the difference between migrator being useable or not in our shop is the use of the ACM function. Without using ACM I could not tolorate the time required but with ACM used it is quite exceptable. This also holds true for the other products as well. It makes a huge difference in RC/Query. The issue doesn't seem to be the products but the access to the catalog itself. The ACM catalog does not differ from [...] 17552 176 39_Re: Workload Balancing over DB2 Connect13_Paul Weissman21_Paul.Weissman@WDR.COM31_Fri, 26 Nov 1999 09:08:54 -0500458_us-ascii Another TCP/IP implementation to avoid a single point of failure is VIPA or Virtual IP Addressing. It's documented in the IBM TCP/IP manuals. The idea is to have multiple connections to the network but an IP address that will use any connection. ______________________________ Reply Separator _________________________________ Subject: Re: Workload Balancing over DB2 Connect Author: leon (leon@CA.IBM.COM) at unix,mime Date: 11/24/99 11:58 PM [...] 17729 341 27_Re: DB2 Tools - CA/Platinum22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Fri, 26 Nov 1999 13:13:42 +0000481_ISO-8859-1 Mike I am sure I have seen someting somewhere about proposed alternative indexes for the Platinum Catalog .. I now a similar document exists for BMC ..

I will have a look and see what I can find in my office at home.

Leslie Pendlebury-Bowe DB2 OS390 SAP



______________________________ Reply Separator _________________________________ Subject: Re: DB2 Tools - CA/Platinum Author: mike.holmans@BT.COM at Internet Date: 11/25/99 12:34 PM [...] 18071 387 27_Re: DB2 Tools - CA/Platinum22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Fri, 26 Nov 1999 13:15:08 +0000615_ISO-8859-1 Phil

oopppppsss ... just sent a note out about this sorry.

if you dig it out before me can you send me another copy .. assuming you have it online???

regards

Leslie



______________________________ Reply Separator _________________________________ Subject: Re: DB2 Tools - CA/Platinum Author: Philip Nelson at Internet Date: 11/26/99 12:24 PM



To add to Mike's note, our Platinum rep gave us documentation about what additional catalog indexes are recommended to improve the performance of the Platinum toolset queries. [...] 18459 411 39_Re: DATE function to reduce CPU time???13_Paul Weissman21_Paul.Weissman@WDR.COM31_Fri, 26 Nov 1999 09:17:04 -0500485_iso-8859-1 There is an IBM APAR which dates back to 1995, PN73268 which sounds like there was a problem with some literal constants in datetime expressions. Sounds like it was fixed. The book in question comes from SAMS, not IBM so maybe the author can be contacted.

______________________________ Reply Separator _________________________________ Subject: Re: DATE function to reduce CPU time??? Author: deepakk (deepakk@WIPHYD.GE.COM) at unix,mime Date: 11/25/99 7:11 AM [...] 18871 95 33_Re: Cobol compilation data(24/31)13_Paul Weissman21_Paul.Weissman@WDR.COM31_Fri, 26 Nov 1999 09:31:25 -0500487_us-ascii All the compile options, even those that have no effect on the code, are recorded in the "Program Signature Information Bytes" -- see the debugging guide for VS-COBOL-2 or COBOL for MVS. There is a procedure documented in the manuals for locating these. You'll need a dump or an assembler program like the classic program called COBREAD written by Rex Widmer which can be found on some of the public domain tapes (CBT, NASPA, etc.) that can do the job with some tinkering. [...] 18967 159 12_Partitioning8_Paul May14_dbas@CWCOM.NET31_Fri, 26 Nov 1999 14:47:15 -0000447_iso-8859-1 Here is a question on partitioning that I cannot seem to find an answer to in any manuals or books.

System information Running DB2 V4.1 OS390 V2.7

I currently have a segmented tablespace that is a candidate for partitioning. When partitioned it would benefit from parallel operations. The problem I have is getting reasonably balanced quantities of data in each partition. The current clustering index is as follows: [...] 19127 188 15_Re: Hybrid Join16_Michael McMorrow25_michael.g.mcmorrow@AIB.IE31_Fri, 26 Nov 1999 15:19:32 -0000360_ISO-8859-1 Adrian,

Thanks a million.

IBM had quoted a couple of other APARs but not yours. Based on some of your info we have now been able to recreate the data loss in test. Your highlighting of Cursor Non-Materialisation (with Hybrid Join with 'With Hold' with RID Overflow) as a key component to the bug scenario was especially valuable. [...] 19316 81 10_Re: MLT IX14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 27 Nov 1999 02:10:55 +1000659_us-ascii JP,

If you mean Multi-Column Cardinalities by "partial statistics" then I would recommend them to be populated in general with occasional exceptions where want to cause a Frequent Value Stat to be used in preference.

As for Multi-Index processing, DB2 is known to pick silly combinations of multi-index processing at times, due to lack of understanding of commonalities between the indexes. Multi-index processing particularly favoured where clusterratios are poor and DB2 may misunderstand combined match col filter factors. Bear in mind however that if 1st index reduces result set to 32 RIDs or less, then 2nd index will not be [...] 19398 138 12_Partitioning14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 27 Nov 1999 02:20:41 +1000311_us-ascii Paul,

Why not try it. Define a new Tablespace, table & partitoning index and see if DB2 accepts your index limit key specs. No need to load any data. If it works check the DB2 Catlg to see what it defines as limit keys.

BTW, Its not essential to have all partitions evenly balanced. [...] 19537 179 16_Re: Partitioning13_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK31_Fri, 26 Nov 1999 15:53:00 -0000378_iso-8859-1 Paul I think you might have a problem here.

An index is comprised of columns of which the whole column is used not part.

Therefore if you use 99,'aa','bb','abc1234........' this will equate to smallint + 2 + 2 +50. The 'abcd1234.......' will be equivalent of 'abcd1234..... space space space...' up to 50 characters. So I think you are struggling. [...] 19717 163 16_Re: Partitioning0_18_damcon2@US.IBM.COM31_Fri, 26 Nov 1999 10:54:18 -0500531_us-ascii



Paul,

Yes, you can use Col 4. DB2 will just give you a warning (sqlcode +664) and ignore anything past 40 bytes.

Cheers, Jay



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



Paul May on 11/26/99 09:47:15 AM [...] 19881 75 10_Re: MLT IX12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 26 Nov 1999 19:00:33 +0200298_x-user-defined Hi, I would check the size of the indexes as well. If one is much smaller then the other then DB2 may prefer to use it. I had something similar ab out a month ago where one index was about NLEAF=32000 and the other was NLEAF=52000 (mainly due to differences in FREEPAGE and PCTFREE). 19957 17 22_Row level locking ????19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Fri, 26 Nov 1999 09:21:12 PST486_- Hi , How can we lock a row of a table in COBOL application program ? ( without mentioning LOCKSIZE option during bind ..... )

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.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. 19975 36 30_How much objects per database?17_Schreiber, Ursula34_Ursula.Schreiber@DRESDNER-BANK.COM31_Fri, 26 Nov 1999 18:11:39 +0100374_iso-8859-1 We are using DB2 for OS/390 5.1.

One of our projects wants to put more than 500 tables into one database.

Because of DBD size we asked them to distribute their tables over a set of databases. The question is, what is a reasonable number of objects (tablespaces, tables, check constraints, indexes and referential relationships) for a database. [...] 20012 50 34_Re: How much objects per database?0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 26 Nov 1999 12:00:07 -0600367_us-ascii Funny thing, I was talking to our DB2 guru about exactly that thing just last night. We have an application with just that number of tables, currently in one DB and another application that has several smaller DBs. When I asked about the cost vs benifit of splitting to smaller DBs he felt that in our case one DB was simpler for the following reasons: [...] 20063 29 26_Re: Row level locking ????0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 26 Nov 1999 12:02:50 -0600515_us-ascii I think what you are looking for is declaring a cursor with hold for update.









kishore erukulapati on 99/11/26 11:21:12 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Rohn Solecki/MTSCommunications/MTS) Subject: Row level locking ????



Hi , How can we lock a row of a table in COBOL application program ? ( without mentioning LOCKSIZE option during bind ..... ) [...] 20093 53 34_Re: How much objects per database?13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 26 Nov 1999 12:34:54 -0600420_iso-8859-1 Craig Mullins book quotes a number of about 40 objects/database. This is due both for administrative reasons and DBD size. I achieve this by creating a database for each subsystem of an application. gm

-----Original Message----- From: Schreiber, Ursula [mailto:Ursula.Schreiber@DRESDNER-BANK.COM] Sent: Friday, November 26, 1999 11:12 AM To: DB2-L@RYCI.COM Subject: How much objects per database? [...] 20147 77 34_Re: How much objects per database?14_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 26 Nov 1999 14:25:24 -0800498_us-ascii I agree with Glenn. Keep in mind, backups, reorgs, maintenance activities. A database for each subject area or application system is a decent place to start. Phil

Philip K. Gunning DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Cetfified Solutions Expert -- CICS TS Assoc List Owner

-----Original Message----- From: Mackey, Glenn [SMTP:GMackey@GUIDEMAIL.COM] Sent: Friday, November 26, 1999 10:35 AM To: DB2-L@RYCI.COM Subject: Re: How much objects per database? [...] 20225 25 33_Another Data Compression question11_Paul Turpin26_Paul.Turpin@FIRSTUNION.COM31_Fri, 26 Nov 1999 15:07:52 -0800569_- This one will probably be an easy one and I'm probably staring right at the solution and missing it. I searched the DB2-L archives and found many posts / answers to compression, but not the one that I am looking for.

I'm trying to determine the amount of space I saved by compressing a tablespace. After doing the compression I looked in SYSIBM.SYSTABLEPART at the PAGESAVE column, which has a value of 44. The description of the column indicates that this value is "%, multiplied by 100, of pages saved in the tablespace or partition as a result of data [...] 20251 28 27_Re: DB2 Tools - CA/Platinum13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 26 Nov 1999 16:46:36 -0500440_iso-8859-1 Hello Glenn, Has your company sold its soul to CA/Platinum? Or is there another reason you're looking only to them for your tools?

There are several other tool vendors I'd suggest you consider. Among the competitors and products I think you should consider are: Compuware(of course) with File-AID for DB2 and DBA-XPERT; BMC with the components of ChangeManager; IBM also now offers a DB Administration tool as of V6. [...] 20280 51 27_Re: DB2 Tools - CA/Platinum13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 26 Nov 1999 16:06:43 -0600341_iso-8859-1 Dave, I know there are other companies out there, but currently looking at CA/Platinum and I needed to come upto speed with the product set in a PeopleSoft environment. Thanks for mentioning what IBM has to offer, a I was unaware that they had a line of products to compete with the tools offered by the CompuWare, CA's etc. [...] 20332 11 0_15_Pravat Banerjee32_pravatbanerjee@LOT.TATASTEEL.COM31_Sat, 27 Nov 1999 08:56:36 +0530265_us-ascii SIGN OFF DB2-L

================================================ 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. 20344 98 36_Re: Platinum Rapid Reorg and Maxrows13_Marcel Lepage13_iml@COLBA.NET31_Fri, 26 Nov 1999 23:12:09 -0700577_iso-8859-1 Tim,

It is not a problem of reallocation of the file... It's about Platinum Rapid Reorg doesn't manage MAXROWS parameter properly.

Marcel

"Ohling, Tim R - CNF" a écrit :

> Let us know how it goes! Our shop uses the reallocation all the time here > and it works fine (with SMS managed DB2 DASD pool). > > "The light bulb works on my desk" > > Tim > > -----Original Message----- > > From: Marcel Lepage [SMTP:iml@COLBA.NET] > > Sent: Tuesday, November 23, 1999 10:49 PM > > To: DB2-L@RYCI.COM > > Subject: Re: Platinum Rapid Reorg and [...] 20443 58 16_Re: Partitioning10_Ven Ilagan18_vilagan@ONE.NET.AU31_Sat, 27 Nov 1999 18:52:54 +1100439_us-ascii Hi Paul,

I generally balance my partitions based only on the values of the 1st column. Why not run this query and balance the parts on the results...

SELECT PSEUDO,COUNT(*) as No FROM (SELECT SUBSTR(DIGITS(COL1),1,3)) AS PSEUDO FROM TABLE_WHATEVER) AS TEMP GROUP BY PSEUDO

put the results in an excel spreadsheet, get a running cumulative summary and divide to the number of partitions based on the sum. [...] 20502 76 16_Re: Partitioning10_Ven Ilagan18_vilagan@ONE.NET.AU31_Sat, 27 Nov 1999 21:57:53 +1100379_us-ascii Oooops - sorry - wasn't thinking - I was so used to our normal 10 char 1st column..I forgot your 1st column was only a smallint so you do need more than the 1st column....

Perhaps it should be....

SELECT PSEUDO,COUNT(*) as No FROM (SELECT SUBSTR(DIGITS(COL1)||COL2||COL3||SUBSTR(COL4,1,34),1,3)) AS PSEUDO FROM TABLE_WHATEVER) AS TEMP GROUP BY PSEUDO [...] 20579 23 16_BMCFAST (BMCDSN)0_14_DPetro@AOL.COM29_Sat, 27 Nov 1999 16:54:26 EST524_us-ascii Our systems folks have setup another version of BMCDSN which they call BMCFAST. They've turned off an option that normally is set to display all the BMC tables on invocation of BMCSDN. What I'd like to be able to do, is call BMCDSN (BMCFAST) from a REXX and trap the output so I can display it on a customized panel. The only problem is, even with an OUTTRAP and the BMC commands 'queued', it still asks for a hit of at the start of BMCDSN and at the end. Has anyone tried to do what I'm doing and run [...] 20603 30 22_db2-L-Request@ryci.com10_Ven Ilagan18_vilagan@ONE.NET.AU31_Sun, 28 Nov 1999 11:25:06 +1100320_us-ascii John,

I'm going on a long holiday overseas again and have sent SET NOMAIL DB2-L to listserv@ryci.com - I hope that's enough to suspend all mails from db2-L flooding my ISP. To reinstate myself later, I understand SET MAIL DB2-L would be enough - no reply from you would mean I've got it right then! [...] 20634 94 8_Re: TIME14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Sat, 27 Nov 1999 22:13:09 -0500537_us-ascii The gratuitous "official" introduction of religion into secular matters has always been offensive to non-members of the religion in question. The latest defense of the majoritarians in the U.S., often accepted by the Supreme Court, has been that these religious terms are so commonly accepted that they have lost their religious meaning. This has led to some pretty silly decisions, such as that it's OK to have a creche on the town hall grounds as long as there are a sufficient number of plastic Santas to secularize it. [...] 20729 122 39_!Message from the list Owners: RE: TIME13_Morrill, John12_JohnM@VP.NET31_Sat, 27 Nov 1999 20:24:00 -0700466_iso-8859-1 Greetings!

As a general rule, I take a country club approach to managing the content of the post to DB2-L. I don't jump right in and order threads which have wander off topic to stop, I wait a bit to see if they will burn themselves out. However, when I feel they are getting out of hand, I peer over the top of my glasses at the posts, and with a very disapproving expression on my face and tone in my voice say, "Ladies and Gentleman.....". [...] 20852 465 39_Re: DATE function to reduce CPU time???14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Sat, 27 Nov 1999 22:39:39 -0500571_us-ascii Our tests showed that the only noticeable difference was due to the order in which the queries were executed, which we also attributed to buffer pool contents,

If omitting the DATE function slows the query down, then IBM should be fixing it. It's seems that the engine has already done the conversion during statement preparation (or at the start of execution, if the date is in a host variable) because you'll get an error if the value isn't a valid date. It's not as if DB2 is doing character comparisons if you omit DATE (). If it knows what it's [...] 21318 38 22_What's it really like?10_John Sroka17_dbajobs@BRICK.NET31_Sun, 28 Nov 1999 09:06:11 -0600421_us-ascii Please excuse the non-technical interruption, but what's it really like to work at your company?

We are a Search Firm that specializes nationally (US Only) in Relational Database talent. Many database professionals ask us what it's really like to work at a company. Although we try to get to know our clients (your employers) fairly well, it's a little hard to answer that question as non-employees. [...] 21357 52 37_Re: Another Data Compression question12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Sun, 28 Nov 1999 21:33:18 +0200304_x-user-defined Hi, if PAGESAVE is 44% then you use 56% (100-44). Your SPACE value is 550,080 so - 550,080/56 = 982,286 (estimation) for uncompressed data 982,286-550,080=432,206. OR: 550080*44/56 in one go. You can use NPAGES instead of SPACE - all depends when did you do your RUNSTATS/STOSPACE.

21410 43 34_Re: How much objects per database?12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 28 Nov 1999 16:43:33 -0800298_us-ascii The key considerations tend to be EDMPOOL impact, the DBD locking, and logging. If you change any of the data objects often, then all of these can cause problems. With SAP, we used a rule of thumb with 100 tables per table space and database. I would have decreased the number to 50. [...] 21454 43 20_Re: DB2PM parameters14_zhangxiaoliang28_zhangxiaoliang@MAIL.DCAC.COM31_Tue, 30 Nov 1999 09:04:18 +0800619_iso-8859-1 Hi, We are using DB2PM for OS/390.I don't know where your product locates.Perhaps I can do something helpful. On OS/390,we first started trace for DB2 events with the command '-DSN1 START TRACE(MONITOR)'.Certainly you can use other paremeters instead of MONITOR.Then in DB2PM we can use 'COLLECT REPORT DATA' in choice 'VIEW ONLINE DB2 ACTIVITY'.You can config one to four tasks to fit your various commands.After you finish setting one task,you may set TIGGER BY option by 4 ---IMMEDIATE START . Now you can start the collect whenever you want.You can choose option 2--- START to set up any task you'd [...] 21498 14 24_Re: Year 2000 procedures14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Sun, 28 Nov 1999 21:43:24 -0500416_us-ascii We're shutting down everything before 1999-12-31-19:00 EST (midnight UT) and starting up around 2000-01-01-12:00. It's just one less set of things we've had to test.

================================================ 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. 21513 95 22_Re: Inconsistent Index15_Murley, Michael22_Michael_Murley@BMC.COM31_Sun, 28 Nov 1999 23:28:08 -0600532_ISO-8859-1 I agree that it is generally better to use Rebuild Index rather than Repair to correct data integrity problems - if you can. And, by the way, I agree with Tim Lowe that we should all use 'Rebuild' to mean the unload/sort/build approach to indexes and 'Recover' to describe the image copy/log record method. In fact, I began forcing myself to make that distinction when discussing index recovery long ago, in an attempt to retain a modicum of my sanity. Some would argue that I failed in that attempt. But I digress. [...] 21609 16 55_How does 'DATE' Clause internally work in a SQL querry?13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Mon, 29 Nov 1999 11:39:24 +0530403_iso-8859-1 Can anybody put some light on 'DATE' Clause. We have talked much about this on list but can somebody tell how does this work internally?

Deepak

================================================ 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.