1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2000, week 4 2 14 55_Daryl G Spletzer/Pr/Consumers/CMS is out of the office.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Fri, 22 Dec 2000 02:09:27 -0500365_us-ascii I will be out of the office from 12/21/2000 until 12/23/2000.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17 75 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)18_Siegfried Lindhoff44_Siegfried_Lindhoff@INFORMATIK-KOOPERATION.DE31_Fri, 22 Dec 2000 08:13:31 +0100470_us-ascii Hi,

when we saw it the OBID always matched to a table in a segmented tablespace and the timeout was caused by a lock escalation.

kind regards Siegfried Lindhoff









DB2 Data Base Discussion List on 21.12.2000 15:33:25

Bitte antworten an DB2 Data Base Discussion List

An: DB2-L@RYCI.COM Kopie: Thema: Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID) [...] 93 34 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 22 Dec 2000 08:50:40 +0100600_us-ascii As far as I can see from replies there is almost one DB2 dataAdmin/sysprog that needs to reorg tablespaces with SHRLEVEL REFERENCE without the execution of concurrent inline copy. And (if true) this is a BUG (and I believe it, I cannot believe that it was left intentionallly).

What I cannot admit is a bullet-proof utility (like reorg SHRLEVEL REFERENCE) that obligates users to allocate a lot of DASDs space for inline copy. So you can avoid datasets for SYSUT1s & SYSRECs, you can reorg tablespace with a high degree of safety with SHRLEVEL REFERENCE, you think you can sleep [...] 128 16 12_Re: DDF hung10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 22 Dec 2000 08:54:17 +0100309_us-ascii You're welcome !!!

Merry XMAS !!!

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 145 38 59_Re: Thanks for the T-shirt Jennifer Moody, CDB Software Inc14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Fri, 22 Dec 2000 09:16:22 +0100585_us-ascii Hmmmm, up to now the t-shirt hasn't made it to Germany yet ...

anyway, all you guys and gals out there have yourself a merry Christmas



Rob Wright schrieb:

> Just to say thanks to Jennifer Moody of CDB Software Inc for the t-shirt. A very > nice Christmas present, all the way to New Zealand. > > Rob Wright > > ================================================ > 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. 184 17 59_Re: Thanks for the T-shirt Jennifer Moody, CDB Software Inc10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 22 Dec 2000 09:17:59 +0100370_us-ascii I always say that italian mail service it's veeerrrrryyy slllloooowwwww .......

Merry XMAX....

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 202 79 38_Re: Stored Procedure with IMS - Sample16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Fri, 22 Dec 2000 09:25:55 +0100404_us-ascii Jacob,

I am posting this reply I received, from posting your question on the IMS-L.

I am sure Dougie will not mind if you contact him offline, as he himself suggests.

once again, happy festive season and btw Dougie thanks a bunch :-) .

Aurora Emanuela Dell'Anno Systems Engineer Sistemi Informativi S.p.A. e-mail: aurora.dellanno@sistinf.it tel. +39 335 7429486 [...] 282 51 59_Re: Thanks for the T-shirt Jennifer Moody, CDB Software Inc16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 22 Dec 2000 14:05:21 +0530304_- Max, Few observations in happy and joking mood !! It doesn't seems to me the problems of the receiver side rather it can be called as the problem of sender's mail service. I have not received it either in India.

Wish you all very happy and joyful holidays and X-Mas

Regards, Sanjeev [...] 334 190 36_Erase YES on partitioned Tablespaces18_Botha, Canvas JHVZ25_JHVZBotha@MAIL.SBIC.CO.ZA31_Fri, 22 Dec 2000 10:33:16 +0200306_iso-8859-1 I have been reading through the DB2 manuals (nothing else to do here at the moment) and stumbled onto the following:

From the DB2 Admin Guide ERASE. Allows you to specify whether the contents of a data set for the table space or partition are erased when the table space is dropped. [...] 525 75 30_Re: castout engine unavailable15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 22 Dec 2000 10:17:01 +0000309_us-ascii Hello Soo Lee,

My initial thought is that your GBP structures seem a bit large however, not knowing any of your statistics I can only give yoyu my 'gut feeling'. This puts you in the same position with the thresholds as the (V)DWTH issues that have been discussed to death on this forum. [...] 601 100 41_Re: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs10_Robert Ord21_robertord@HOTMAIL.COM31_Fri, 22 Dec 2000 09:26:07 -0000661_- Unfortunately we are archiving using Legato and their version of the userexit. They don't provide an equivalent of "db2adutl query".

Regards

Rob







>From: BILL_GALLAGHER@PHL.COM >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs >Date: Thu, 21 Dec 2000 13:32:18 -0500 > >Rob, > >If you are archiving the logs through the db2uext2 user exit to ADSM, then >you can use the "db2adutl query" command to see which logs are archived in >ADSM. Also, the exit will write a record of each log that it archives in >the ARCHIVE.LOG file, which [...] 702 129 56_Re: What if recovery fails after reorg/load with log no?0_18_mebert@AMADEUS.NET31_Fri, 22 Dec 2000 10:27:30 +0100566_us-ascii Hello J,

if REPAIR ... SET NORCVRPEND or -START .. ACCESS(FORCE) are no options (you didn't specify why the TS is in RECP), then my thoughts would be along the following lines (NOT tested - just running in free association mode): Procedure A: 1) RECOVER the TS TORBA X'...' where the RBA specifies the time of the REORG LOG NO (from SYSCOPY), or, if DB2 balks, the highest RBA just before that it will accept. 2) run REORG 3) take Full IC 4) DSN1COPY with OBIDXLAT to test TS with the same table structure for safety 5) apply Incremental copies [...] 832 138 40_Re: Erase YES on partitioned Tablespaces0_18_mebert@AMADEUS.NET31_Fri, 22 Dec 2000 10:45:32 +0100582_us-ascii Hi,

what does JHVZ stand for (I wonder)? ERASERULE is in SYSTABLESPACE so it probably predates partitioned TSs. It should be in SYSTABLEPART because you can specify the ERASE parm per partition. The true value for the ERASE parm can only be found in the VSAM Cluster info (e.g. using the CSI program I've put into the DB2-L-DOCUMENTS site). Indexes (partitions) can be ERASE YES as well, but I did not find an ERASERULE associated with them at all - again, it's in the VSAM definition. Note that the erase operation is done when the VSAM file is deleted... not [...] 971 45 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Dec 2000 07:50:28 -0500476_iso-8859-1 We had some mapping table learning to do. We found that to minimize lock situations our best option was one mapping table per tablespace. In the beginning we did a CREATE in each job. Once we hit about 8 reorgs at the same time we hit DBD lockouts on the CREATE/DROP. So now we have 'permanent' mapping tablespaces/indexes. If we run short of DASD space we can drop the larger ones then create them just before running the reorg (but this reduces concurrency. [...] 1017 24 12_WLM question27_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM31_Fri, 22 Dec 2000 09:49:47 -0500377_- We are changing WLM to goal mode from compatibility mode. Our OS/390 guy thought they were told in class something about making sure the DB2 buffers were big enough before changing to goal mode. I can't find anything in the DB2 books about WLM requiring bigger bufferpools or any other buffers. Does this sound familiar to anyone? We are running DB2 V5.1 on OS/390 2.6. [...] 1042 57 16_Re: WLM question16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Dec 2000 09:56:44 -0500424_iso-8859-1 I know a bit about WLM and a bit about bufferpools. The only way I can see any connection is that WLM goal mode might enable more Stored Procedure address spaces which might open things up for more workload.

However, buffer pool requirements are driven entirely by SQL (and certain utility) requirements. If the SQL and utility workload does not change, the bufferpool requirements should not change. [...] 1100 50 56_Re: What if recovery fails after reorg/load with log no?15_Murley, Michael22_Michael_Murley@BMC.COM31_Fri, 22 Dec 2000 09:55:22 -0600376_iso-8859-1 If there is any sort of full backup after the REORG (pack dump ?) you might be able to restore from it and apply the log with RECOVER LOGONLY. But you cannot apply log records through a REORG LOG NO, so recovery to current using a full image copy prior to the REORG is not possible. Incremental image copies are only valid if the prior full copy is available. [...] 1151 31 44_Re: Moving schema from dev to production env13_Faiz Siddiqui20_sabafaiz@HOTMAIL.COM31_Fri, 22 Dec 2000 15:48:13 -0000500_- Hello, Our environment: DB2UDB 7 on NT and AIX.

My problem is that we are trying to move the new updated schema from development to production and I'm not sure what's the best approroach to move the schema to production without losing any data. We have dropped and added columns certain tables recently and would like to move schema to production. What might be the best approach to handle this task? Any Ideas would be appreciated! Thanks. Faiz Siddiqui DB2 UDB DBA quantumStream Inc. [...] 1183 177 9_Re: Reorg15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 22 Dec 2000 10:11:57 -0600787_us-ascii 5 non-partitioned indexes 1 partitioned index 9398691 rows

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/22/2000 10:10 AM ---------------------------



Tim.Lowe@STPAUL.COM on 12/21/2000 06:54:41 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg





Nayeem, When reorganizing very large tablespaces, I generally use the SORTNUM 32. But, I think that your sort program can override this. For example, I know that even if I specify SORTNUM 32, it can use fewer sortworks based on the number of rows and the rowsize that reorg passes it. Obviously, I dynamically allocate my sortworks. And, if [...] 1361 180 9_Re: Reorg15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 22 Dec 2000 10:24:06 -0600501_us-ascii Thanks Tim for your responses. We have BMC here but I have not idea about its working procedure. Plz any wanna explain me about its features.

Thanks Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/22/2000 10:22 AM ---------------------------



Tim.Lowe@STPAUL.COM on 12/21/2000 06:54:41 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US) [...] 1542 26 55_-924 When Creating a Platinum Migrator Compare Strategy12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM31_Fri, 22 Dec 2000 11:36:12 -0500610_US-ASCII Success !!

Back on 12/14, I described a problem we were having with the Platinum Migrator when attempting to create a "Compare" type strategy which compared 2 objects in different subsystems. Basically, the tool would not create the strategy after a -924 was returned from DB2. In response to my listserv inquiry, Steen Rasmussen, Senior Consultant DB2/IMS for Computer Associates Scandinavia advised me of a DB2 APAR / PTF ( PQ36467 / UQ41798 ) which solved the problem. Thanks to Steen, we've solved a problem that has been outstanding with our CA/Platinum Tech Support for many months. [...] 1569 71 42_Re: Anyone using DSNUTILS Stored Procedure11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Fri, 22 Dec 2000 11:08:26 -0600629_us-ascii This PROC works for us:

//SSIDWLM PROC RGN=0K,APPLENV=WLMyours,DB2SSN=your SSID,NUMTCB=1 //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, // PARM='&DB2SSN,&NUMTCB,&APPLENV' //STEPLIB DD DISP=SHR,DSN=your.LOAD //SYSIN DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //SYSPRINT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //UTPRINT DD SYSOUT=* //CEEDUMP DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSMDUMP DD SYSOUT=*







Scott Goodell cc: Sent by: DB2 Subject: Anyone using DSNUTILS Stored Data Base Procedure Discussion List [...] 1641 145 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Fri, 22 Dec 2000 09:43:15 -0600399_iso-8859-1 Sounds like something that could be generated on the fly by IBM? How long did it take to work out all the kinks?

Thanks for the reply Eric. Anyone else?

-----Original Message----- From: Eric.Pearson@NSCORP.COM [mailto:Eric.Pearson@NSCORP.COM] Sent: Friday, December 22, 2000 6:50 AM To: DB2-L@RYCI.COM Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? [...] 1787 145 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Dec 2000 12:47:01 -0500484_iso-8859-1 John, Once we learned the syntax of Online Reorg things got simple. Each problem (the locking etc) was very easy to find. Within one day's testing we were ready for prime time.



regards,

eric pearson NS ITO Database Support

-----Original Message----- From: John Arbogast [mailto:john.arbogast.cquz@STATEFARM.COM] Sent: Friday, December 22, 2000 10:43 AM To: DB2-L@RYCI.COM Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? [...] 1933 69 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)10_Alan Smith25_alancsmith@BTINTERNET.COM31_Fri, 22 Dec 2000 18:07:27 +0000597_US-ASCII Don't know QMF or rexx/db2, but... I believe in any case that you can't call a stored procedure from a dynamically-prepared sql statement - unless it's an ODBC program. You can't call one from DSNTEP2, for instance. You could use UDFs though if you're V6.

Alan Smith







> ------------------------------ > > Date: Thu, 21 Dec 2000 08:57:43 -0600 > From: rick creech > Subject: Re: Paging :- QMF & Stored Proc guru (& guru-ettes) > > Hi, > > I know you can write rexx in qmf as a qmf proc...so maybe you can call a > stored proc [...] 2003 12 22_Testing. Please delete11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 22 Dec 2000 12:05:20 -0700303_iso-8859-1 Just being upgraded to Outlook 2000. Please Delete

================================================ 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. 2016 78 9_Re: Reorg19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 22 Dec 2000 14:22:55 -0600594_iso-8859-1 Tim, You should always use SORTDATA if the data does in fact need to be reorg'd *UNLESS*, see Utility Guide: "The data set is very large and there is not enough DASD available for sorting. The longest possible composite record size is greater than 32760."

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received [...] 2095 120 9_Re: Reorg0_19_Tim.Lowe@STPAUL.COM31_Fri, 22 Dec 2000 14:29:32 -0600375_us-ascii Rick, Yes, I know, but I was trying to quote the speaker that I had heard at the tech conference, and I did not think that I should "correct" something that I was trying to quote.

Thanks, Tim





"DAVIS, RICK (SBCSI)" To: DB2-L@RYCI.COM Subject: Re: Reorg Sent by: DB2 Data Base Discussion List [...] 2216 54 51_Re: Dynamic SQL from Business Objects, Brio, etc...22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Fri, 22 Dec 2000 15:55:01 -0500392_- I've read that DB2 for OS/390 V5 or V6 or V7, can't remember, has something called the Predictive Governor. It's supposed to predict the resources required to execute a query. If any thresholds that you set are exceeded, then the query is never executed. I haven't used it.





Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749 [...] 2271 36 18_Season's Greetings24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 22 Dec 2000 17:06:27 -0800789_us-ascii To all,

Merry Christmas Happy Hanukkah Prayerful Ramadan Blessed Kwanzaa Joyful Diwali and a healthy and prosperous 2001 CE

Peace, Frank



+-------------------------------------+--------------------------------------+ | Frank C. Fillmore, Jr. | Voice/Fax: 410.465.6335 | | The Fillmore Group, Inc. (TFG) | Class registration: 800-TFG-RDBMs | | 3213-A Corporate Court | E-mail: fillmore@tfg-rdbms.com | | Ellicott City, Maryland 21042-2247 | URL: http://www.thefillmoregroup.com | | USA | | +-------------------------------------+--------------------------------------+ | DB2 Family, Oracle, Client/Server, Distributed Database | | "Relational Database Solutions"(sm) | +----------------------------------------------------------------------------+ [...] 2308 42 15_Traces doubt...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Fri, 22 Dec 2000 15:50:29 -0800494_us-ascii Thank you very much James and Michael, I really appreciate the very basic information you provided me with regarding buffer pools. In my efforts to enhance familiarity with DB2, I have some doubts regarding traces. When I do a -DISPLAY TRACE(*), I see some traces which are active here in our shop. But how do I see the output of the traces? It shows DEST as SMF and OP2. What is SMF and OP2? Are these some kind off ddnames for some datasets which I can open and take a look at? [...] 2351 40 41_Happy Hoildays from the DB2-L list owners13_Morrill, John12_JohnM@VP.NET31_Fri, 22 Dec 2000 10:03:03 -0700519_iso-8859-1 Greetings!

With another year coming to a close to take this opportunity to thank all the people that have made DB2-L such a great success.

DB2-L has over 3000 subscribers in sends out over 82,000 messages a day. We are a recognized authority in DB2 community.

I would like to thank the Associate List Owners Janis Thomas, Philip Gunning and Lynne Flatley. This list would not be possible without their daily hard work to help our subscribers with the various problems that crop up. [...] 2392 69 19_Re: Traces doubt...12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Fri, 22 Dec 2000 21:21:03 -0600547_- Ritu:

When you do a -DISPLAY TRACE(*) command, It shows DEST for yours db2 traces. DEST SMF means that db2 trace records will be filled in the smf datasets and SMF OP2 means that your records will be filled in differents trace datasets rather than SMF.(most of the cases OPX , where x can be from 1 to 7 , is used by third party monitors like BMC Activity Monitor, CA Insight, Omegamon and that is why is preety handy read those small datasets to get a trace information rather read the SMF dataset where all the system trace record [...] 2462 14 47_Craig McGinnis/AO/USR/FTU is out of the office.14_Craig McGinnis29_craig.mcginnis@FIRSTUNION.COM31_Sat, 23 Dec 2000 02:08:55 -0500365_us-ascii I will be out of the office from 12/22/2000 until 12/27/2000.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2477 18 17_ADO & DB2 UDB V5.10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 23 Dec 1997 11:13:59 +0330368_US-ASCII Hi all,

Does anyone knows any references about ADO and DB2 UDB V5?

Any help is appreciated. Navid Khan.

================================================ 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. 2496 21 11_ADO Delete.10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 23 Dec 1997 16:08:07 +0330462_US-ASCII Hi all,

When I try to delete a recordset using ADO I receive the following error: Insufficient key column information for updating or refreshing. Who knows why?

Any help is appreciated. Navid Khan.

================================================ 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. 2518 26 21_REORG SHRLEVEL CHANGE12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Sun, 24 Dec 2000 14:42:46 +0200344_iso-8859-1 Hi,

For the mapping table you can have one small permanent segmented TS with many TBs in it. What's changes the size during the reorg is only the index on the table (one index per table). So you can define the TS as priqty 720 (1 cyl) and each index as priqty 720 secqty 72000 (or what ever you find is better for you ). [...] 2545 41 19_Re: Traces doubt...14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sat, 23 Dec 2000 14:17:12 -0500372_ISO-8859-1 The OPx destinations are not datasets. These are memory buffers used by the IFI interface, and the only way to get data from them is to to have a program running using IFI. When a program starts a trace, it is assigned an OPx buffer. I don't think you can read from a buffer that is not assigned to your IFI program (never tried to do this). Regards, Joel [...] 2587 67 19_Re: Traces doubt...12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Sat, 23 Dec 2000 17:27:54 -0600434_- Joel:



I know that opx are not datasets. They are just buffers that drive your trace records to be recorded in a partitular dataset depending on your IFI Program. Here we have Activity Monitor and it used DEST OP7 and it own trace datasets. It means we don't need to read SMF or any different datasets because all the db2 trace record will be filled in the Activity Monitor trace datasets. Thanks for your email, [...] 2655 44 19_Re: Traces doubt...14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sun, 24 Dec 2000 09:52:26 -0500447_ISO-8859-1 Harbry, Maybe we are just into semantics..... but the opx buffers do not "....drive trace records to be recorded......" It's up to the ifi program to specifically write the records out. In your specific case, AM retrieves the data from the opx buffer and writes the data to its own datasets. If an ifi program does not get dispatched quickly enough during a high trace period, there will be buffer over-runs and data will be lost. [...] 2700 81 19_Re: Traces doubt...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Sun, 24 Dec 2000 09:38:36 -0800428_us-ascii Thanks Joel and Harby,

But i still didn't get an answer to my original question...How do i 'read' trace records which have gone to SMF? Is SMF a dataset which I can browse? If yes, how do i know which dataset it is?

Also, my second question was that if Accounting trace class 2 is not up and running, how is my monitor showing me 'plan level details' (like elapsed time, CPU time etc. for the plan). [...] 2782 41 19_Re: Traces doubt...14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sun, 24 Dec 2000 16:58:55 -0500583_ISO-8859-1 SMF is an operating system recording medium for performance data. The data is written to datasets SYS1.MANx. These are dumped periodically, and usually accumulated... you'll have to talk to your MVS folks on where to get at the data. As far as the actual data. you need a program that can read the records you want, and format them.... like DB2PM, or the batch facilities of most of the other online monitors. You can't browse the data records,as this will be meaningless to you. Re: class 2 time... it depends. Most online monitors turn this on by default unles the [...] 2824 20 19_Re: DB2 Job Posting17_Esmaiel Nokhodian15_dbaen@TEXAS.NET31_Mon, 25 Dec 2000 07:17:12 -0600473_iso-8859-1 Hi Dougan ,

Attached is a copy of my resume. I am interested in DB2 DBA position. Please review my resume and let me know if you have any further questions.

Thanks-Esmaiel, 512-340-6974 wk. 512-335-0971 hm.

================================================ 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. 2845 14 49_Nasira Latef/O&S/Prudential is out of the office.12_Nasira Latef27_nasira.latef@PRUDENTIAL.COM31_Mon, 25 Dec 2000 03:10:20 -0500365_us-ascii I will be out of the office from 12/22/2000 until 12/28/2000.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2860 21 13_ADO & Update!10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 26 Dec 2000 11:49:53 +0330503_US-ASCII Hi all, Merry Christmas

When I try to run an update using an ADO recordset I get the following error: INSUFFICIENT KEY COLUMN INFORMATION FOR UPDATING OR REFRESHING I'm using db2 V5. Who can help me?

Best whishes for new year! Navid Khan.

================================================ 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. 2882 220 22_Re: MAD MAX goes again21_Salim, Arli (SG-SEMA)22_arli.salim@SEMA.COM.SG31_Tue, 26 Dec 2000 16:27:34 +0800574_iso-8859-1 Hi Isaac,

Just came back from leave. Thanks very much for taking the trouble to run the SQL.

Did anybody notice that Query 3 (Q3) is quite similar to the right hand side of the inner join of Q2, with the only major difference being the distinct clause?

I'm quite puzzled that Q3 didn't outperform Q2. I had estimated the result set of 'max(k_number) group by year' to be quite small (some hundred rows at most, I believe). In such a case, can I more or less say the following (of course, just wild guesses without access plans & stats): [...] 3103 116 22_Re: MAD MAX goes again16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 26 Dec 2000 14:33:19 +0530560_- Hi Salim, Thanks for summarizing the queries and CPU time. Sometimes it is very difficult to go back and check the things. To open some more discussion and giving some thoughts as per your details i would say i expected Q3 "not" to outperform Q2 because when i read the Venkat's mail and his explaination about the join performed in the case of Q2. Venkat said :- One scan of table and then making the result set smaller after joining...........I am adding that your group by on Number(in Q2) is on the smaller result set than the distinct on Number(in [...] 3220 85 59_Re: Thanks for the T-shirt Jennifer Moody, CDB Software Inc15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 26 Dec 2000 09:36:55 -0600555_iso-8859-1 Hi, you guys, It depends on how it was sent. If by air-mail (very $$$$) then you should have gotten them by now. If by surface mail (Still $$$ but not as much) then you might not see them for a few weeks. It also depends on the country that the package is being sent to. Some mail services are much slower then others. I send Christmas packages overseas every year and Germany, Austria and Switzerland have very prompt delivery. Another I send to Brazil takes much, much longer if someone hasn't stolen it first (A very real possibility). [...] 3306 29 22_Load in Partitioned TS15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 26 Dec 2000 11:22:40 -0600593_us-ascii Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE existing entire data using LOAD utility with REPLACE option. REPLACE option deletes existing data before reloading and during BUILD phase I am getting Unique key violations and these unique key violations are corrected in INDEXVAL phase. My question is why I am getting this with REPLACE option which actually deletes data and it seems it does not deletes index data. In such cases when it is required to REPLACE data , what is the best solution. Do I need to drop indexes and re-create/rebuild [...] 3336 62 26_Re: Load in Partitioned TS8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 26 Dec 2000 11:29:25 -0600447_us-ascii The REPLACE key word should be after the part number.

//DSNUPROC.SYSIN DD * LOAD DATA INDDN SYSREC00 LOG NO INTO TABLE creator.Your table PART 001 RESUME NO REPLACE

Thnaks Bejoy





From: Mohammed Nayeem @RYCI.COM> on 12/26/2000 11:22 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 3399 92 26_Re: Load in Partitioned TS15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 26 Dec 2000 11:35:06 -0600468_us-ascii Hi Bejoy

Thanks for early response. If I define REPLACE keyword according to your syggestion , then will I should not get index key violation (right) ???

Thanks Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/26/2000 11:29 AM ---------------------------



Kurian B on 12/26/2000 11:29:25 AM

Please respond to DB2 Data Base Discussion List [...] 3492 69 26_Re: Load in Partitioned TS16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Tue, 26 Dec 2000 12:44:01 -0500422_us-ascii Mohammed,

Have you verified that the input file does not contain duplicate key entries?

If duplicate unique key entries are present within the input file, then DB2 is performing as designed.

Also, if you want to replace the entire tablespace (pre-delete all existing rows) then the simplest load statement is:

LOAD DATA REPLACE LOG NO INDDN SYSREC00 INTO TABLE creator.table_name [...] 3562 24 26_Re: Load in Partitioned TS15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 26 Dec 2000 11:50:23 -0600440_us-ascii Hi Rick

Thanks for the response. Pre-deleting is taking hell of time when i try to do using qmf. So I thought to use REPLACE option in LOAD. And there are no dup's rec's in th input file and I am trying to replace existing data and why should I use PART Keyword any how data will go according to the definition of clustering index in corresponding partitions. But why i'am getting unique key violations in BUILD phase. [...] 3587 120 26_Re: Load in Partitioned TS8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 26 Dec 2000 12:00:29 -0600436_us-ascii Hi, I am sorry, i thought you are replacing the partition. Try dropping the index and load. Thanks





From: Mohammed Nayeem @RYCI.COM> on 12/26/2000 11:35 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Fax to: Subject: Re: Load in Partitioned TS [...] 3708 67 26_Re: Load in Partitioned TS16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Tue, 26 Dec 2000 13:08:56 -0500326_us-ascii Mohammed,

I agree with your approach, totally! Using the REPLACE keyword is the way to go if you want to pre-delete all the rows of the table ( regardless of whether the tablespace is partitioned or not - Just make sure a segmented or simple tablespace contains only ONE table before using this option ). [...] 3776 20 26_Re: Load in Partitioned TS15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 26 Dec 2000 12:27:01 -0600379_us-ascii Yep , only one table exist in partitioned TS and (1 partitioning index + 5 other indexes ). My question was with REPLACE option of LOAD , it look like it is not deleting index data but deleting only table data from the TS before RELOAD phase. Might be b'cos indexes are in diff TS's , so for REPLACing entire data how to empty index entries before Reload goes????? [...] 3797 51 26_Re: Load in Partitioned TS8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 26 Dec 2000 13:39:50 -0600452_us-ascii With REPLACE option it will delete the indexes and rebuild after the load, if you want to replace the entire table. It works fine several times with me. I did't understand "Might be b'cos indexes are in diff TS's... " I hope this may be diff. vsam datasets(index space). To me it does't matter, b'cos you created index against the table. Correct me if i am wrong. If you still have problem try dropping the indexes and load. Thanks Bejoy [...] 3849 96 26_Re: Load in Partitioned TS13_Basheer Shaik20_shaik123@HOTMAIL.COM31_Tue, 26 Dec 2000 19:34:42 -0000634_- HI There

I am getting Lots Of mail about DB2.. PLEASE Remove my e-mail Id from List.

Thanks Much.... Bye

>From: RICHARD E MOLERA >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Load in Partitioned TS >Date: Tue, 26 Dec 2000 13:08:56 -0500 > >Mohammed, > >I agree with your approach, totally! Using the REPLACE keyword is the way >to go >if you want to pre-delete all the rows of the table ( regardless of >whether the >tablespace is partitioned or not - Just make sure a segmented or simple >tablespace contains only ONE table before [...] 3946 16 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)11_Mark Harmon17_mhinnyc@YAHOO.COM31_Tue, 26 Dec 2000 14:51:24 -0600473_- About commit frequency: Do you have an DB2 Restart product? At least two of these products (maybe more) can force DB2 commits into programs that do not take them often enough. They can also provide automatic retry of some errors. Depending on the product, this might be done without source code changes. The product I am familiar with is Smart/Restart at www.relarc.com but there are others on the market. If you have a DB2 restart product peruse the manual for it. [...] 3963 57 56_Re: What if recovery fails after reorg/load with log no?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 27 Dec 2000 10:08:22 +1100306_iso-8859-1 I suppose you have only a single image copy (ie no DR IC)?

Both BMC and CA (don't know about others) have products that can read a log and generate equivalent SQL statements. Don't know about any requirements (eg DATA CAPTURE?). Combined with a recovery to just before the reorg ... [...] 4021 21 34_SQL Stored Pocedure Builder on AIX16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM31_Tue, 26 Dec 2000 18:35:30 -0500438_iso-8859-1 While trying to prepare an SQL stored procedure from the DB2 CLP, we get the following messages: SQL0060W The "C" precompiler is in progress. SQL0091W Precompilation or binding was ended with "0" errors... DBI1302E Invalid Parameter Detected

I know that the last message is from db2set saying "an invalid parameter was used". How can we tell what that "invalid parameter" is? Does anybody have any debugging hints? [...] 4043 51 51_Re: Dynamic SQL from Business Objects, Brio, etc...3_xyz19_dcreed@CABLEONE.NET31_Tue, 26 Dec 2000 21:00:23 -0600462_iso-8859-1 Sorry if this is a really old email, If you use DB2 Connect with TCPIP and catalog the database with ,,,interrupt_enabled then the user can cancel the query and not leave the thread active on DB2. Regards Danny x ----- Original Message ----- From: "Jessen Michael" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, December 21, 2000 9:15 AM Subject: Dynamic SQL from Business Objects, Brio, etc... [...] 4095 107 26_Re: Load in Partitioned TS16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 27 Dec 2000 10:22:00 +0530548_- Hi all, I think there is something which is missing when it is looked. Nayeem, try using the discard dataset and have a look at the discard dataset output to get which all rows are violating the uniqueness. I am sure there are lots of fixes are available for lots of problem but this looks silly to me that REPLACE will remove the data from tablespace and not from index. IBM is never famous for designing such an inconsistent thing i.e DB2, which is highly consistent and when it is not, it forces us to take some action. Theoritically and [...] 4203 65 12_ODBC problem10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 27 Dec 2000 17:15:56 +1000477_ISO-8859-1 Hi everyone,

Wish u all a merry New Year vacation.

I had posted this sometime back, but without a response. I hope I get one atleast this time.

We have got a problem with one of our applications which we have havent been able to figure out, yet.

This is a 16 bit Windows based application (it is pretty old) connecting to DB2 (V5) on the mainframe using DB2 Connect V5, with options which kick off other applications (again 16 bit). [...] 4269 18 59_Re: Thanks for the T-shirt Jennifer Moody, CDB Software Inc10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 27 Dec 2000 12:57:03 +0100383_us-ascii Hi Linda....

Ah, ok my T-shirt probably stopped in Switzerland, as Italy is not far from Brazil. ;-)

Cheerz Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 4288 86 51_Re: Dynamic SQL from Business Objects, Brio, etc...11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Wed, 27 Dec 2000 07:40:39 -0600467_us-ascii The Reactive Governor (See page 703 of DB2 6.1 Admin Guide) is what you can use. We can not get the Predictive Governor to work for all applications.

P.S. The Reactive Governor is available in ver 5 also... : )







"Piontkowski, Michael L" cc: Sent by: DB2 Data Base Subject: Re: Dynamic SQL from Business Discussion List Objects, Brio, etc... [...] 4375 72 56_Re: What if recovery fails after reorg/load with log no?16_SPARKS, SANDRA A17_SSPARKS@SCANA.COM31_Wed, 27 Dec 2000 10:26:03 -0500365_iso-8859-1 Do you have a copy made before the Load? Do you still have the input from the Load? To what point in time do you wish to return?

-----Original Message----- From: James Campbell [mailto:James.Campbell@HANCORP.COM.AU] Sent: Tuesday, December 26, 2000 6:08 PM To: DB2-L@RYCI.COM Subject: Re: What if recovery fails after reorg/load with log no? [...] 4448 71 34_Powerbuilder and Stored Procedures14_Kevin Eberhart21_Keberhart@BOSCOVS.COM31_Wed, 27 Dec 2000 13:51:06 -0500418_iso-8859-1 Hello All,

We are developing an application using powerbuilder that will access DB2 on AIX via a stored procedure. The developers are using the powerbuilder data window controls and have not been successful calling a stored procedure that updates the database.

Has anyone run into this? Did you find a solution?

TIA

Kevin Eberhart Database Administration Boscovs Dept Stores, LLC 4520 193 18_MAD MAX goes again12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 28 Dec 2000 21:46:36 +0200531_iso-8859-1 Hi,

I hope you can suffer the explain output ;-)

All measurments run were conducted separatly on a separate BP which was contracted and expanded (1000) for each run, few runs were done and the time are averages, the fluctations were not high. Use an editor like PFE which allows for large view and its ok. The answer set had 11 rows before the distinct and 3 after it. The price for sort is very high and for distinct/group by it was done twice for this query - this can explain the high time for it. [...] 4714 42 31_Bufferpools/hiperpools & memory12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 28 Dec 2000 21:59:52 +0200547_iso-8859-1 Hi,

I did an interesting "exercise" during the last weeks. VBPs were enlarge nearly up to the max allowed - 398500 pages all together. System paging to disk averaged 0.5 on the peak time as measured by SMF. No problems were encountered due to being near the edge.

Hiperpools for BP7 (DSNDB07) - I tended not to allocate HBP for DSNDB07 but during the last year I did many test with it and found out you may benefit even if sometimes it looks like a waste (which it is not). HBP is used by DSNDB07 when needed - so if [...] 4757 39 27_Insert into nullable column13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Wed, 27 Dec 2000 14:53:58 -0600334_iso-8859-1 Dear list.

We just upgraded to DB2 6.1 from 5.1

I have table A with 23 columns I have table B with the exact same 23 columns and one extra recently added nullable column at the very end.

I have a program (package A) that worked fine under 5.1 where I inserted into B selecting the columns from A. [...] 4797 94 16_Re: ODBC problem12_Susan Lawson24_susan_lawson@YLASSOC.COM31_Tue, 26 Dec 2000 15:08:31 -0600474_iso-8859-1 Kals,

What program are you using to connect (VB, VA, etc...)? And have you been able to capture the ODBC error codes?

Regards,

Susan Lawson

www.ylassoc.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of teldb2kals Sent: Wednesday, December 27, 2000 1:16 AM To: DB2-L@RYCI.COM Subject: ODBC problem



Hi everyone,

Wish u all a merry New Year vacation. [...] 4892 86 31_Re: Insert into nullable column0_19_Tim.Lowe@STPAUL.COM31_Wed, 27 Dec 2000 16:13:27 -0600332_us-ascii Scott, Typically, I see this problem anytime someone does inserts without specifying the column names that they are inserting, and a new column is added to the table. Therefore, I suspect that you are not specifying the column names in your insert statement. Am I correct?

I hope this helps.

Thanks, Tim [...] 4979 150 43_Buffer Pool: prefetch disabled/read engines16_Orndorff, Joseph24_Joseph.Orndorff@NIKE.COM31_Wed, 27 Dec 2000 15:43:47 -0800405_iso-8859-1 Recently, we have done following to the Buffer Pools in one of our production subsystems:

1. eliminated all buffers from all hiper pools (HP's) 2. added these buffers to the related virtual pools (VP's) 3. moved all ESTOR originally used by hiper pools to CSTOR

This was done to eliminate I/O between VP & HP. The various BP threshold settings were initially left unchanged. [...] 5130 160 47_Re: Computing percent of transactions that sort11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Wed, 27 Dec 2000 19:06:26 -0500402_us-ascii "SORT is a FOUR letter word"

Hi Barry!

Some of those formulas, in particular the first, look very familiar.

If a "Transaction" is a voluntary, intentionally completed, action, then COMMIT STATEMENTS ATTEMPTED + ROLLBACK STATEMENTS ATTEMPTED would accurately reflect the number of application intended transactions ("make it so" or "cancel" - commit/rollback). [...] 5291 209 47_Re: Buffer Pool: prefetch disabled/read engines14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 27 Dec 2000 19:57:32 -0500620_ISO-8859-1 Joseph, Read engines disabled is first a direct indication of a poorly performing DASD subsystem, because the I/Os cannot complete fast enough, and all the engines are busy waiting for reads to complete. Dataset placement is usually the culprit. Additonally, your vdwqt s/b set to ZERO.. for overall better performance, but this won't help the engine disabled problem. Some serious bufferpool tuning will probably help your performance, and will reduce the i/o load. You neglected to mention if your total number of buffers is the same now, as it was when you had HPs. What is your average elapsed times [...] 5501 67 35_Re: Bufferpools/hiperpools & memory14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 27 Dec 2000 20:13:25 -0500574_ISO-8859-1 Isaac, If your system is really paging to disk on a regular basis (not sure what your .5 number is... per/second?)... you need to look at your paging rate to expanded... which is probably much too high to have good performance. High MVS paging rates to expanded make performance worse, even when the I/O wait component drops, and the BP hit ratio goes up. You need to add all the page-ins across all your pools, and then calc a rate/sec. Also calc an MVS overhead factor for your transactions. (C2 Elapsed-C3 Wait)/C2 CPU. If this > 1.5 you are constrained, [...] 5569 13 56_Re: What if recovery fails after reorg/load with log no?9_J Sullens21_pleniumcorp@YAHOO.COM31_Wed, 27 Dec 2000 22:05:42 -0600508_- The question was regarding recovering to current when the most recent full imagecopy was bad and reorg or load log no was done prior to that. In the absence of pack dumps etc., Michael Eberts solution of applying incremental imagecopies seems to hold most promise.

================================================ 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. 5583 128 37_Re: VIEW Performance and VIEW EXPLAIN14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 28 Dec 2000 16:06:00 +1100566_iso-8859-1 Had a thought about this:

1) If each table has, say, 2 indexes that means there are 3 access paths to each table. That means DB2 has to evaluate 45 possible access initial table access paths (ie evaluate each of the 15 as the first table and for each its 3 access paths), 42 to the second table (with 3 possible join techniques), 39 to the third (with 3 ...) etc, giving: 45*42*3*39*3* .... 6*3*3 = 29E24 possible ways of joining the tables together. OK, perhaps the optimizer is going to discard some of these without evaluating the cost (eg [...] 5712 123 16_Re: ODBC problem10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 28 Dec 2000 16:16:55 +1000372_ISO-8859-1 Hi Susan,

We r using VC++. The SQLstate we get when we go back to PGM1 after closing PGM2 is 08003, which is basically "connection does not exist".

We r still working on the problem, trying to run some traces. One recent possible clue is the following infm from the CLI Guide and Reference under the description for SQLSetConnection function [...] 5836 35 47_Re: Buffer Pool: prefetch disabled/read engines10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 28 Dec 2000 08:59:40 +0100307_us-ascii What kind of DASDs do you have ? We had this problems some days ago (only few prefetch disabled, anyway), and we resolved it moving some datasets to another DASD (from HDS7700 to HDS7700E) with more cache (> 1 Gb) to achieve an higher cache hit ratio. Check if all cache features are enabled. [...] 5872 16 35_Re: Bufferpools/hiperpools & memory10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 28 Dec 2000 09:03:51 +0100324_us-ascii Hi Isaac, what's is your DB2 (MVS) UIC ?

Regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5889 167 22_Re: MAD MAX goes again16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 28 Dec 2000 15:15:08 +0530581_iso-8859-1 Hi Issac,

Thanks for the result. Despite of getting the difficult EXPLAIN Output, it was useful analysis.

If we look at the EXPLAIN output, i will always say query no. 999992(i.e. the last query below) is better. If someone would not have told me the CPU time taken, i would have gone by EXPLAIN and using correlated subquery looks the best choice. If i looked the explain output correctly then i can say there is no sort for query no. 999992 and that is the most important thing we can look at.Another difference i have seen is the COLUMN_FN_EVAL is [...] 6057 102 31_Re: Insert into nullable column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 28 Dec 2000 17:25:57 +0530442_- Hi, If i can recall correctly then the nullable columns need not to be mentioned in the INSERT INTO clause. DB2 will automatically insert NULL value for that column.Please correct me if i am wrong ! Scott, look at the VALUE clause of the insert statement. In your case it is INSERT into T1 SELECT * from T2. The error message says T2 should not have more number of columns than T1. You can also specify the column names in the SELECT. [...] 6160 130 31_Re: Insert into nullable column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 28 Dec 2000 17:37:14 +0530560_- As i said , DB2 will automatically insert NULL in the nullable column, but i realised what Tim wanted to mention that the column is not mentioned in the INSERT INTO clause but it is there in the SELECT clause. This can also give you sqlcode -117.

HTH Regards, Sanjeev

> -----Original Message----- > From: S, Sanjeev (CTS) > Sent: Thursday, December 28, 2000 5:24 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: Insert into nullable column > > Hi, > If i can recall correctly then the nullable columns need not to be > mentioned in [...] 6291 118 31_Re: Insert into nullable column13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Thu, 28 Dec 2000 07:02:35 -0600404_iso-8859-1 Actually, I am specifying the column names, but I really should not have to. Insert into table B select * from table A should work fine as. It is my understanding that as long as the source table has columns less than or equal to the number of columns in the target table (matching data types, of course) and that any additional target columns can allow nulls, the statement should work. [...] 6410 168 47_Re: Buffer Pool: prefetch disabled/read engines11_David Nance16_DWNance@FHSC.COM31_Thu, 28 Dec 2000 08:40:01 -0500394_US-ASCII Dave Nance First Health Services, Corp. (804)527-6841

>>> Joseph.Orndorff@NIKE.COM 12/27/00 06:43PM >>> Recently, we have done following to the Buffer Pools in one of our production subsystems:

1. eliminated all buffers from all hiper pools (HP's) 2. added these buffers to the related virtual pools (VP's) 3. moved all ESTOR originally used by hiper pools to CSTOR [...] 6579 150 26_Re: Load in Partitioned TS15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 28 Dec 2000 08:38:50 -0600389_us-ascii Thanks Sanjeev ,

Problem is solved.

One of the index's was not correct ,i .e for uniqueness one more column on that particular index was required and that was missing and that's why I was getting unique index key violations. Corrected that index and reloaded with replace option successfully.

Thanks and wish you all a happy and properous new year 2001. [...] 6730 12 31_Re: Insert into nullable column15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 28 Dec 2000 08:45:18 -0600279_us-ascii DBRM problem ?? double check

================================================ 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. 6743 14 31_Re: Insert into nullable column15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 28 Dec 2000 08:49:23 -0600405_us-ascii Double check INSERT stmt. The number of insert values in the value list of the INSERT statement is not the same as the number of object columns specified.

================================================ 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. 6758 206 31_Re: Insert into nullable column10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Thu, 28 Dec 2000 08:44:42 -0600355_- Sorry to contradict you and everyone else (except Tim), but the fact is:

The Insert into / Select from construct demands that the number of columns that you are inserting must match the number that you are selecting from. DB2 cannot 'automatically' do anything when they don't match.

I think the V5 to V6 transition is a red herring. [...] 6965 240 31_Re: Insert into nullable column13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Thu, 28 Dec 2000 09:48:19 -0600554_iso-8859-1 Thanks for the examples John.

The -117 is evidence that the rebind is demanding the extra value be specified.

I guess I'm asking why DB2 can't 'automatically' figure out that, hey, I'm giving you the first 23 values that match column for column. The target has a 24th column that allows nulls and I'm not sending a 24th value. It just seems to me that DB2 should be smart enough to just automatically insert the null. Some part of DB2 (stage 1?) is happy to insert the null until the rebind wakes up the optimizer, I guess. [...] 7206 20 31_Re: Insert into nullable column15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 28 Dec 2000 09:58:20 -0600289_us-ascii DB2 is not smart enough to just automatically insert the null value itself , but it allows you to insert null value. So I think you should send blank value to that 24th column. Remove '*' from your sql stmt and re-frame this sql stmt by selecting all columns rather than * . [...] 7227 20 37_DB2 7.1 -Authentication problem on NT19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Thu, 28 Dec 2000 18:03:33 +0200405_iso-8859-7 Hi all I have just installed DB2 7.1 on an NT workstation machine. Db2 processes starts using a local account. I log on using mydomain account and when I try to create a database I got message that my account has no priviledges to create database. What I have to do in order domain account obtain sysadm priviledges without add account to Administrators group of Primary domain Controler? [...] 7248 146 41_Re: DB2 7.1 -Authentication problem on NT12_David Booher22_David.Booher@QUEST.COM31_Thu, 28 Dec 2000 08:16:17 -0800563_ISO-8859-7 This may not be the perfect way, but:

I installed DB2 on my local machine and let it create the user DB2ADMIN. After the SAMPLE databases were created, I then added my normal domain login name as a user with all privileges to the database. There appears to be a restriction to eight characters for a user name. Since my domain login was david_booher, I had to create a new user on my machine as david_bo (8 characters). Once this was added as a user to the SAMPLE database (with privileges) I have no problem maintaining it when I'm logged [...] 7395 73 41_Fw: DB2 7.1 -Authentication problem on NT11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Thu, 28 Dec 2000 11:46:53 -0500496_us-ascii Dimitris,

From a DB2 command line window, you can issue the command: db2 get dbm cfg | more

You may find that SYSADM_GROUP, SYSCTRL_GROUP, and SYSMAINT_GROUPS are all blank, or unspecified.

It is a good practice to create operating system groups for these purposes (eg. PROD_SYSADM, TEST_SYSADM, PROD_SYSCTRL, ...)

Once you have created the OS groups, you can assign users to these groups. For instance, add your domain account to the PROD_SYSADM group. [...] 7469 29 31_Bufferpools/hiperpools & memory12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 29 Dec 2000 19:49:10 +0200553_iso-8859-1 Hi, Sorry the reply botton decided to go on vacation. Max - UIC 254, exp-sto migration age is 3,600+ at peak time(seconds) otherwise it's 150,000+ means pages reside there for enough time. Joel - system pages to disk on the peak time at 0.5 page/second (forgot to put it in my prev note) but only on very busy times, otherwise it's flat zero. Page rate to expanded at peak time fluctates between 0 - 200 per second (according to tmon/mvs). CPU goes 100% at the peak time - all the time. Page-in at VBP (all together) is less then 20,000 [...] 7499 16 31_Re: Insert into nullable column0_19_Tim.Lowe@STPAUL.COM31_Thu, 28 Dec 2000 11:49:30 -0600327_us-ascii Scott, Could you send us the insert/subselect statement ?

Thanks, Tim

================================================ 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. 7516 29 18_MAD MAX goes again12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 29 Dec 2000 20:09:03 +0200376_iso-8859-1 Hi, I'm really sorry for the explain format, will work on something better next time. I have no full answers for that case, what it teaches is that explain should be handeled with care, that queries can be written different ways and you are nevere sure which one is best till you really time it yourself. Which means that "it depends.." should be the motto... [...] 7546 16 33_enable on bind of a called module12_Alan Gredell28_agredell@KEMPERINSURANCE.COM31_Thu, 28 Dec 2000 12:57:29 -0600338_- I've read some posts on a topic close to this, but have a slight variation. We are using ENABLE(IMS) or ENABLE(CICS) for programs defined as transaction drivers. What about subprograms called by those transaction programs? Should they also have the same ENABLE parameter as the "parent" program?

Happy New Year to everyone! [...] 7563 14 19_SET TRANSACTION Sql0_20_bjnigh@HOUSEHOLD.COM31_Thu, 28 Dec 2000 11:02:20 -0800395_us-ascii Is there a SET TRANSACTION SQL Statement in DB2 OS/390? I was not able to find anything about it in the online books for Version 5 or Version 6.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7578 50 37_Re: enable on bind of a called module0_19_Tim.Lowe@STPAUL.COM31_Thu, 28 Dec 2000 13:25:20 -0600428_us-ascii Alan, In our case, we are using ENABLE(IMSMPP, CICS) on our online plans. Are you thinking of using this on the packages?

Thanks, Tim





Alan Gredell cc: Sent by: DB2 Data Subject: enable on bind of a called module Base Discussion List



12/28/2000 12:57 PM Please respond to DB2 Data Base Discussion List [...] 7629 20 39_udb DB2/aix upgrade v5.2 to version 7.111_Mike Fatula17_mfatula@PHEAA.ORG31_Thu, 28 Dec 2000 15:44:27 -0500439_us-ascii Currently we are looking to upgrade db2 v5.2 to v 7.1 " EEE'". Currently we are running microfocus cobol batch and tranaction server. Does anyone know of any out standing problems with DB2 or Microfocus COBOL working together? Also, will I need to re-compile and bind all my programs or just do a bind. The IBM book says do a rebind, but since we have to rebuild the runtime lib (MicroFocus) do I have to do a re-compile???? [...] 7650 35 35_Foreign Key indexes - when and how?15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 28 Dec 2000 16:47:00 -0500481_us-ascii Happy Holidays!

I searched the archives, but couldn't find what I'm looking for. Which is... What are the guidelines in defining foreign key indexes? I know they are needed when the rule is delete cascade, but what about restrict? Is one needed to make the check more efficient? What about the columns in the index? Do they need to match the foreign key exactly in number of columns and order or can they just be the first column(s) of a multi-columned index? [...] 7686 79 39_Re: Foreign Key indexes - when and how?0_19_Tim.Lowe@STPAUL.COM31_Thu, 28 Dec 2000 15:59:23 -0600327_us-ascii Jay, Regardless of whether the rule is cascade or restrict, the index is needed for performance to determine if children of the rule exist. The first columns of the index must match the referential integrity rule in exactly the same order. They can, and most often are, the first columns of a multi-column index. [...] 7766 49 43_Re: udb DB2/aix upgrade v5.2 to version 7.118_Gert van der Kooij15_geko@WANADOO.NL31_Thu, 28 Dec 2000 23:13:44 +0100402_iso-8859-1 Hi Mike,

I think the best way to go is to call Merant. We had an issue with MF Cobol /db2 v7 on NT because we use the SQL directive to specify a different user/password during precompile, but I don't know if that's also an issue on AIX. We didn't recompile our programs during the initial test. We only did a rebind and everything still worked but we didn't go live with V7 yet. [...] 7816 51 24_CA's PanAPT DB2 option ?10_John Katan18_JOHN.KATAN@ASU.EDU31_Thu, 28 Dec 2000 08:40:38 -0700146_- If anyone has had experience with Computer Associates PanAPT DB2 option I'd appreciate it if you'd take the time to render your opinion. Thanks. 7868 156 31_Re: Insert into nullable column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 29 Dec 2000 09:37:47 +0530298_- Hi , I think what i mentioned to Scott in my previous two mails is to specify something like this.

INSERT INTO B (23 out of 24 columns) SELECT * FORM A

23 out of 24 columns exclude the column in which NULL is allowed.This way DB2 will automatically insert 24th column as NULL. [...] 8025 66 31_Re: Insert into nullable column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 29 Dec 2000 09:46:01 +0530350_- Hi, Let us forget the question of Scott for one moment and i would say DB2 is "highly smart" to insert the null value but when we are writing the query it should make some sense to DB2 as well. So DB2 will insert the null value in the column in the cases mentioned below : Let's say T1 has 5 columns c1,c2,c3,c4 and c5 , where c5 is nullable. [...] 8092 44 31_Re: Insert into nullable column14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 29 Dec 2000 15:40:48 +1100425_iso-8859-1 If you read the SQL reference manual you will find the following things documented - SELECT * means all the columns in the from tables - at the time the statement is prepared - INSERT without a column list means all the columns in the table being inserted into - also at the time the statement is prepared - the number of columns in the INSERT column list must match the number of columns in the SELECT list. [...] 8137 72 39_Re: Foreign Key indexes - when and how?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 29 Dec 2000 10:38:45 +0530539_- Hi Jay, As we know idexes are used to locate the data for either selecting it, updating it or deleting it. This way we can say that indexes are required on the foreign key irrespective of the kind of RI rules implemented because for any rule DB2 has to locate the rows for either restricting or deleting it. Regarding the order of the foreign key index, it should exactly match the parent key. The first column should match to make it matching index scan for DB2. However , i think you can have the additional columns at the end in [...] 8210 101 39_Re: Transactions deadlocking on inserts13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 28 Dec 2000 23:09:34 -0600360_iso-8859-1 Tim & Andrew,

It is possible for an insert to timeout with a type-2 index. I've attached an extract from a DB2 listserv email from Chris Munson (IBM Santa Teresa) in August 1999. Sorry for the delay in replying but I had to retrieve the email from my outlook personal folder in another country, as it is no longer in the DB2-L archives. [...]