1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2001, week 3 2 101 42_Re: Can DB2 V7.1 on OS/390 be called UDB ?11_Rao Adiraju21_adiraju@ATTGLOBAL.NET31_Sat, 15 Sep 2001 18:10:56 +1000390_iso-8859-1 Felt like throwing few comments on Troy's optimism / confidence on IBM coming to the party.

I feel IBM is always late, coming to match the ever changing worlds needs.

These User defined objects support is their in other databases for quite some. How many more versions of DB2 or DB3 do we need to wait for, to get a proper searching engines for these objects. [...] 104 24 16_390 Load Utility14_Cheung, Teresa19_cheungt@TOYSRUS.COM31_Sat, 15 Sep 2001 09:07:48 -0400483_- Hi all,

We are trying ot migrate data from NT Oracle to 390 DB2 by extracting data from Oracle to a file and ftp the result file 390 where we run the load utility to load data to DB2. One of the issues reported by some programmers is 390 load utility does not handle data with sign fields. My question is do they need to write a separate routine to handle the insertion of signed filed data. Wonder if DB2 Connect provide any utilities that can resolved this scenario. [...] 129 83 37_Re: The World Trade Center Discussion10_Marc Baime14_mbaime@GTE.NET31_Sat, 15 Sep 2001 09:06:50 -0400570_us-ascii Attempting to have a guy removed from his job (by encouraging people to send nasty comments about him to his management) because he made comments you don't care for is just like the blacklist kind of thing McCarthy ran in the 50's to ensure people's attitudes were in line with his. If you don't care for what someone says, blow it off, don't try to control their speech through a mind screw like getting in touch with his management. Real Americans aren't afraid to debate the issues without calling names or feeling the need to respond to those who call [...] 213 68 20_Re: 390 Load Utility13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Sat, 15 Sep 2001 06:54:13 -0700574_us-ascii Teresa,

If Oracle generates a load file with a character string which is recognized as a "numeric constant" (see Chapter 3 in the SQL manual), (i.e. +15.27 or -4398.5793) then the load utility can definitely read that correctly.

Just specify in the load control file that the field in the input file is "DECIMAL EXTERNAL(length,scale)". This tells the loader to expect a "numeric constant" If the data is a proper "numeric constant", the load utility will convert that data to the format of the target column, be that Decimal, integer, or float. [...] 282 148 42_Re: Can DB2 V7.1 on OS/390 be called UDB ?12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Sat, 15 Sep 2001 21:36:15 -0500532_iso-8859-1 Well I don't know if I'm the best person to answer this. User Defined Data Types have been in DB2 on OS/2, NT, and UNIX for many years. This feature is now available for DB2 for OS/390. On most distributed platforms the database runs on a database server. The application runs on an application server. The front end in a web browser or client software. So the front end tool you use to scan in an image will invoke a database call to store it. The storage can be in DB2 on NT, UNIX, many others .... and OS/390. So [...] 431 202 60_Tolerance..and patience as we try to make sense of things...10_Marc Baime14_mbaime@GTE.NET31_Sun, 16 Sep 2001 01:14:38 -0400567_iso-8859-1 Let's not strike out at anyone or anyplace too hastily nor should we be quick to lay aside our rights and freedoms to ensure perfect safety lest we find ourselves back where we were in the 60's---just my opinion after hearing a number of people I thought were reasonable human beings dicussing the rounding up and expulsion of all muslims from the US. Hopefully we haven't forgotten the shameful episodes of 2nd and 3rd generation Japanese Americans being incarcerated during WWII, one of the most un-American things ever done in the name of America. [...] 634 32 26_Re: DB2 and SMP/E question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 16 Sep 2001 04:39:36 -0500418_- For the record: sorry Mark, it was only later that I realised I'ld not read your response carefully enough.

James

On Fri, 14 Sep 2001 06:00:53 -0500, James Campbell wrote:

>One other technique which no-one has mentioned is to used MVS aliased >datasets.



On Thu, 13 Sep 2001 09:45:09 -0700, Mark Turner wrote: [...] 667 32 63_Re: Question about DB2 Data sharing and Group naming convention14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 16 Sep 2001 04:53:09 -0500512_- But they'll get you in the end:

"I heard that the pundit who named it IGDZILLA got canned; don't know if it is true." from http://bama.ua.edu/cgi-bin/wa?A2=ind9805&L=ibm- main&P=R3914

James Campbell

On Fri, 14 Sep 2001 09:38:18 -0400, Pearson, Eric L, wrote:

>And some times the bureaurats let one slip by - >guts of DFSMS was: > >IGDZILLA > >(picture large beast munching on a DASD controller!) > > >Regards, >eric pearson >NS ITO Database Support > [...] 700 61 30_Re: Qualified accounting trace0_22_drdeath@OZEMAIL.COM.AU31_Sun, 16 Sep 2001 21:02:41 +1000423_ISO-8859-1 Shaun,

I see the set up at DEWRSB continues to produce problems. The guys here have been on V6 for some time and run lots of traces so I'll see what I can find out for you.

In the meantime how is everything else going.

My new email is Tony.Mitchell@rbs.co.uk

Please pass it on to Dave Elsey, Glenn Ford, Peter Heinemann and Christina, as I would like to know how they are going. [...] 762 131 61_Re: Two Questions excessive -911 errors and defrag of volumes16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Sun, 16 Sep 2001 07:16:27 -0400544_iso-8859-1 RE: Two Questions excessive -911 errors and defrag of volumesDaryl,

Since you didn't get a reply to question #2 -- yes, you can use ADRDSSU to copy your dataset to another volume and then copy it back. If you specify a CANCELERROR, DELETE, PURGE in the job, ADRDSSU will take care of the delete once it has successfully created the new dataset. Here is some JCL I have been using for years to move DB2 tablespaces and index spaces around. When you use this for VSAM, you only need to copy the CLUSTER (DSNDBC) component. [...] 894 20 18_DSNDB07 Allocation12_John Piccoli16_jpiccoli@LOOK.CA31_Sun, 16 Sep 2001 10:35:16 -0400470_us-ascii Is there an IFCID that reports on the physical amount of overflow to DSNDB07? I'm trying to determine the physical amount of sortwork space used by a particular query. We are on DB2 V5.1(OS/390).

John Piccoli CDS

================================================ 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. 915 52 64_Re: Tolerance..and patience as we try to make sense of things...14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sun, 16 Sep 2001 11:12:15 -0400395_ISO-8859-1 Very well said Marc. The perpetrators must be exterminated, and those who actively support and protect them as well. There is no place on this planet for those whose only goal in life to maime and kill civilians. This is intended not only towards those responsible for the WTC bombing, but towards all terrorists. They are sick people, rabid dogs, and should be treated as such. [...] 968 102 20_Re: 390 Load Utility14_Cheung, Teresa19_cheungt@TOYSRUS.COM31_Sun, 16 Sep 2001 13:22:37 -0400382_iso-8859-1 Hi Tink, Thanks for your promptly response.

Can you point out what's wrong with this data conversion process? Extract data from NT Oracle SQL> select to_char(qty,'s0999999') from item ( Let's say output is -0000002) 390 DB2 Load Control Statement LOAD DATA LOG NO RESUME YES INDDN SYSREC00 INTO TABLE ITEM_STORE ( : QTY POSITION( 28 ) INTEGER EXTERNAL(7) : ) [...] 1071 137 37_Re: The World Trade Center Discussion18_Elizabeth A. Pratt14_prattea@NU.COM31_Sun, 16 Sep 2001 16:23:46 -0400584_us-ascii To all -

my regrets and sympathies to all who had family or friends working at the WTC and the pentagon.... since we know that many foreign companies had office space at theWTC, this affects everyone whether living inside or outside of the US....

I received the following just before reading the e-mail that I am responding to.... I am sure that I am not sending something that is unknown to most people who look up statistics.... The following was sent to me by my finical advisor... interesting reading on how the market responds to world disasters..... [...] 1209 166 20_Re: 390 Load Utility13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Sun, 16 Sep 2001 13:43:19 -0700597_us-ascii Hi Teresa,

I believe it should look like the following:



LOAD DATA LOG NO RESUME YES INDDN SYSREC00 INTO TABLE ITEM_STORE ( : QTY POSITION( 28 ) DECIMAL EXTERNAL(8) : )

One thing to remember about the load control file. The column name (QTY) tells DB2 which column is the target column. DB2 then looks in the catalog to determine it's data type. The rest of the statement [POSITION( 28 ) DECIMAL EXTERNAL(8) ] describes to DB2 the input file. That is, this field starts at position 28, is 8 bytes long, and smells like a decimal looking character string. [...] 1376 49 14_FW: IT support18_Elizabeth A. Pratt14_prattea@NU.COM31_Sun, 16 Sep 2001 17:16:02 -0400447_us-ascii Hi everyone -

I received the following on the other list I belong to ... for those who stated that they would be willing to help out, check out the following web-site....

Liz Pratt Northeast Utilities SR Computer Analyst



----- Forwarded by Elizabeth A. Pratt/NUS on 09/16/2001 05:17 PM -----

"Smith, Ed " To: SAG-L@LISTSERV.UARK.EDU cc: (bcc: Elizabeth A. Pratt/NUS) Subject: FW: IT support [...] 1426 38 6_Re: V812_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Mon, 17 Sep 2001 08:47:19 +1000636_- Not released it's only out on Parole with good-behaviour bonds.

Cheers, Nick Cianci DB2 and ex-IMS DBA > -----Original Message----- > From: Susan Birgeles [SMTP:Susan.Birgeles@ALLTEL.COM] > > IMS has releases? > > _____________________________ Reply Separator > _________________________________ > Subject: Re: V8 > Author: DB2-L@RYCI.COM (DB2 Data Base Discussion List) at INTERNET > Date: 9/13/01 11:43 AM > > Sorry guys could not resist this one... it started with someone asking if > anyone had any idea when the next release (V8) of IMS might be out... > > grand one Andrew! > > Ciao! > > Aurora Emanuela Dell'Anno > [...] 1465 102 29_Re: Cut Off For Optimisation?22_S, Sanjeev (Cognizant)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 17 Sep 2001 09:30:21 +0530584_- Phil,

The looping SQLCODE is -901. But that's irrespective of any parameter. If at all that is the case it should have been issued. IBMLINK might give some idea.

Regards, Sanjeev

> -----Original Message----- > From: Grainger, Phil [SMTP:Phil.Grainger@CA.COM] > Sent: Friday, September 14, 2001 9:23 PM > To: DB2-L@RYCI.COM > Subject: Re: Cut Off For Optimisation? > > There is an SQLCODE (I forget which one) that effectively says "sorry, > this is too complex" > > I suspect what you have found is a loop in DB2 of some sort > > Have a look in IBMLINK or [...] 1568 16 37_Re: The World Trade Center Discussion7_ridhwan29_rizwan@CLOVERTECHNOLOGIES.COM31_Mon, 17 Sep 2001 11:44:55 +0530347_iso-8859-1 DISCLAIMER Those were my opinions entierly and had nothing to with CLOVER TECHNOLOGIES Regards

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 1585 97 29_Re: Cut Off For Optimisation?12_Simon George26_simon_x_george@HOTMAIL.COM31_Mon, 17 Sep 2001 07:49:45 +0100 1683 18 34_Re: Acessing SQL source from SPT0115_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 17 Sep 2001 02:51:21 -0500361_- Hi Walter

What do you mean with the statement in SYSPACKSTMT is incomplete. What are you missing?. I never knew, that the complete Statement isn't stored in that catalog table. The only thing I know, is that only explainable statements are stored in the catalog, if you do a remote bind. But I don't know, if those statements are stored in SPT01. [...] 1702 189 20_Re: 390 Load Utility18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Mon, 17 Sep 2001 13:36:40 +0530616_- Tink,

As a curious onlooker I would like to know what is 'scale' for in 'DECIMAL EXTERNAL(length,scale)'.

thanks, Rajendra.



>From: "BL.Tink Tysor" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: 390 Load Utility >Date: Sun, 16 Sep 2001 13:43:19 -0700 > >Hi Teresa, > >I believe it should look like the following: > > > LOAD DATA LOG NO RESUME YES INDDN SYSREC00 INTO >TABLE > ITEM_STORE > ( > : > QTY POSITION( 28 ) > DECIMAL EXTERNAL(8) > : > ) > >One thing to remember about the load control file. The >column name [...] 1892 25 0_10_praveen_kj19_praveen_kj@INFY.COM31_Mon, 17 Sep 2001 15:05:33 +0530591_iso-8859-1 Hi All,

We are going to do the whole development of our project in DB2 6.1(Infy Mainframe) whereas on the client side they are having DB2 6.1.1.

Do you see any possible issues with it.

can anybody pass me pointers regarding release notes of various versions DB2 6.1, DB2 6.1.1.

Regards,

Praveen Kumar Jain

===============================================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. 1918 44 6_Re: V814_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Mon, 17 Sep 2001 10:47:39 +0100646_iso-8859-1 -----Original Message----- From: Susan Birgeles [mailto:Susan.Birgeles@ALLTEL.COM] Sent: 14 September 2001 22:03 To: DB2-L@RYCI.COM Subject: Re: V8



> IMS has releases?

No, it has escapees.



----------------------------------------------------------------------------------------------------------------------- This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. [...] 1963 83 14_Re: code pages13_Cobbaert Marc19_marc.cobbaert@SD.BE31_Mon, 17 Sep 2001 11:51:18 +0100524_ISO-8859-1 Hi,

Same frustration. Try workaround by adding following parameter in DB2CLI.INI DISABLEUNICODE=1. It helped with me adding it in the COMMON section, but can be put in specific subsystem section also. Apparently this seems to be a non-documented parameter (at least I haven't found it documnted.)

Regards,

Marc



-----Oorspronkelijk bericht----- Van: Daniel Adam [mailto:dadam@GRATEX.COM] Verzonden: vrijdag 14 september 2001 17:01 Aan: DB2-L@RYCI.COM Onderwerp: code pages [...] 2047 76 37_Re: The World Trade Center Discussion7_ridhwan29_rizwan@CLOVERTECHNOLOGIES.COM31_Mon, 17 Sep 2001 15:45:49 +0530438_iso-8859-1 To all the members of the list,

My name is Shrikant and I am Ridwan's immediate superior. I am writing this message from his email account.

We at Clover Technologies apologize everybody for Ridwan's email message. Although it was his personal opinion, he had written it by using our corporate ID

We have taken a strong action against him and have terminated him from his job with an immediate effect. [...] 2124 202 32_STAR JOIN potential improvements19_Brouwer, R. - SPLXR21_Rinze.Brouwer@KLM.COM31_Mon, 17 Sep 2001 13:18:33 +0200385_iso-8859-1 Terry and others,

First I offer my sympathy to all of you in the USA, that have lost some one in the WTC tragedy. We here in the Netherlands feel also hurt and shocked by these evil people. We were 3 minutes silent last Friday and my flag was out all day halfway the flagstick as a sign of mourning. I hope that the people behind this can be brought to justice. [...] 2327 15 46_Finding last executed SQL-STMT in SYSUDUMP ???18_Siegfried Lindhoff44_Siegfried_Lindhoff@INFORMATIK-KOOPERATION.DE31_Mon, 17 Sep 2001 13:57:00 +0100419_us-ascii Is it possible - if yes, how - to locate the last executed SQL-Statement incl. Hostvar. and SQLCA in a SYSDUMP or need I always a SVC-DUMP ???

Siegfried Lindhoff

================================================ 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. 2343 29 23_CA/Platinum Rapid Reorg18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 17 Sep 2001 08:11:23 -0400442_iso-8859-1 If anyone out there is using this product I'd like to ask a question on what your criteria is for dertermining what needs to be reorg'd. Instead of boring the list please feel free to contact me off-line. We are currently at DB2 os/390 V6 using P97F (we had to fall back to P97F since we could not get P99D to work prior to upgrading to DB2 V6). Our current criteria is: less than 95% clustered and/or greater than 5 extents. [...] 2373 24 24_Informix vs UDB question13_Kathy Czepiel30_KathleenCzepiel@GROCERYBIZ.COM31_Mon, 17 Sep 2001 07:29:53 -0500515_us-ascii My company is taking a serious look at our Informix platform and attempting to decide whether or not an effort should be made to convert it to some flavor of DB2 either OS/390 V6 UDB or the AIX version. Has anyone else gone thru this exercise? Are there any good tools or documentation on this subject that anyone would recommend as a good starting point, especially for the barely Unix literate? (I can spell CAT and GREP, other than that, things are still hazy, I'm a mainframer, I love my tools! ) [...] 2398 66 27_Re: CA/Platinum Rapid Reorg16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 17 Sep 2001 08:39:03 -0400458_iso-8859-1 Steve,

We use < 97% clustered. We generally do not REORG based on extents. We have only one tablespace which goes into excessive extents when in need of REORG. Also, we keep hearing from IBM and from our DASD vendors that extents are not much of a performance impact these days. We have (mostly) switched from CA Rapid Reorg to IBM Online Reorg. For some of those we do conditional reorg based on OFFPOSLIMIT > 10 or INDREFLIMIT > 10. [...] 2465 47 28_Re: Informix vs UDB question16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 17 Sep 2001 08:41:40 -0400307_iso-8859-1 I have heard that a large orange hardware store went from Informix to UDB. They were OS/390 UDB on mainframe for a long time and Informix in the stores. Last I heard they were going OS/390 UDB for mainframe operational data store and data warehouse, and UDB (some *IX flavor) in the stores. [...] 2513 201 36_Re: STAR JOIN potential improvements19_Brouwer, R. - SPLXR21_Rinze.Brouwer@KLM.COM31_Mon, 17 Sep 2001 15:08:50 +0200369_iso-8859-1 Terry and others,

I hear from one of my colleagues on the list (thanks Marcel) that the layout of my previous mail gets a mess. So I repeat my text below again.

Thanks for your reaction. It helped me a lot. I have done a few investigations and I have a few questions left. I put these down below. Could you answer them? Thanks in advance. [...] 2715 83 60_Re: The World Trade Center Discussion - This is too much !!!20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Mon, 17 Sep 2001 15:13:23 +0200543_iso-8859-1 My condolonces, and my solidariety to all who is involved in this tragedy, and to all the orhers touched in any way by it.

Italy too stopped friday, three minutes of silence for such a big sorrow. Silence because there are no words to be spoken in such a situation.

Silence , I wish we had been in silence for the last three day on this list too. Now instead we are facing what for me it's a new tragedy, we're witness of the vicory of Fascism again, not only we were defeated by terrorism , but now by ourselves [...] 2799 106 60_Re: The World Trade Center Discussion - This is too much !!!16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 17 Sep 2001 09:30:42 -0400320_iso-8859-1 We too were nearly ruined by a sort of facism in this country. It happened in the 1950's and was called McCarthyism (after a rather vocal Senator). Many lost their careers. Many families were torn asunder. Not quite as bad as the facism in Italy, but that may be only because we stopped it early enough. [...] 2906 15 19_Undocumented ZPARMS20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Mon, 17 Sep 2001 14:07:27 +0100392_iso-8859-1 I am unable to find documentation for two of the ZPARMS new in V6; can anybody help me with OPTIUNQ and OPTNTJP?

Hugh Beighton-Dykes

================================================ 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. 2922 48 28_Re: Informix vs UDB question12_Carl Shinkle24_carl.shinkle@MANTECH.COM31_Mon, 17 Sep 2001 09:46:58 -0400414_US-ASCII Kathy Our company does this very thing as the primary focus of our business. Please contact me off-line and I'd be glad to provide details. Carl Shinkle ManTech Systems Solutions Corp.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Kathy Czepiel Sent: Monday, September 17, 2001 8:30 AM To: DB2-L@RYCI.COM Subject: Informix vs UDB question [...] 2971 68 37_Re: The World Trade Center Discussion12_McKown, John22_JMckown@HEALTHAXIS.COM31_Mon, 17 Sep 2001 08:49:50 -0500317_- Helen, If I sounded "heartless", I sincerely apologize. Have I become a different species? I don't think so. Different people react in different ways. I rarely cry (I did on and off while watching the news all week), but other cry freely. Does that make the the first type of person less a person? I hope not. [...] 3040 22 12_Tracker Site13_Harve Puckett25_hpuckett@DOAS.STATE.GA.US31_Mon, 17 Sep 2001 08:47:41 -0500561_- We are a DB2 Version 5 site at PUT0107 or so, OS/390 2R6.

After putting on a good deal of maintenance from PUT0108 and moving that maintenance to our install test subsystem all seemed to be fine until. One of our SysProgs reassembled the Zparms with SITETYP=RECOVERYSITE, and recycled the install test subsystem with those Zparms. From that day forward, this subsystem insists he is a "TRACKER SITE" ... even though TRKRSITE=NO and has never been anything other than NO. This renders the subsystem less than optimal, as an install test subsystem. [...] 3063 39 23_Re: Undocumented ZPARMS12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Mon, 17 Sep 2001 09:53:26 -0400537_us-ascii Hugh, there is some info on these in your SDSNMACS(DSN6SPRM) lib.







"Beighton-Dykes, Hugh" @RYCI.COM> on 09/17/2001 09:07:27 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Undocumented ZPARMS



I am unable to find documentation for two of the ZPARMS new in V6; can anybody help me with OPTIUNQ and OPTNTJP? [...] 3103 20 23_Re: Undocumented ZPARMS10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Sep 2001 16:02:10 +0200472_us-ascii Search in xxxxxx.SDSNMACS with search for utility (usually 3.14)

Briefly they activate (if on):

Optimizer for PQ39223

" " for PQ40573

Search these APARs/PTFs using IBM APARs database on the WEB

================================================ 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. 3124 44 23_Re: Undocumented ZPARMS9_Neal Cash16_nealc@US.IBM.COM31_Mon, 17 Sep 2001 10:07:07 -0400456_us-ascii These are documented in APARs PQ40573 for OPTIUNQ and PQ39223 for OPTNTJP. Hope this helps...

Neal

All of us cannot do great things, but we all can do little things in a great way.





"Beighton-Dykes, Hugh" @RYCI.COM> on 09/17/2001 09:07:27 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 3169 142 60_Re: The World Trade Center Discussion - This is too much !!!20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Mon, 17 Sep 2001 16:05:29 +0200300_iso-8859-1 Sorry Eric, to say that but it doesn't seem to me that you had stopped it at all... ridwan lost is career not in the 50's but nowadays, and maybe it only didn't explained well what he meant(just making a supposition) .

that's way I decided at last to reply to this discussion. [...] 3312 50 38_MQ Series help needed if someone knows15_Durga Vamsidhar22_durga.vamsidhar@DB.COM31_Mon, 17 Sep 2001 16:01:32 +0100551_iso-8859-1 Hello everybody,

Though this discussion list is for DB2, I am putting my doubt here regarding MQ as most of the people will be using MQ. Please help me if some body knows on the following situation.

I have a doubt on MQ-series. It is as follow. My queue is defined with Maximum message length of 100 MB. I want to process this message in my cobol program( please note that Cobol can have maximum buffer size of 16MB). Is mq supports that I can read the certain portion of the same message into one variable ( which I can [...] 3363 93 20_PLEDGE FOR AMERICANS0_20_John_Lendman@FPL.COM31_Mon, 17 Sep 2001 10:25:17 -0400469_iso-8859-1 PLEDGE FOR AMERICANS



I, ____________________, do solemnly pledge.



Although my country has suffered a horrible blow, I will not allow my spirit to be defeated.



I will go to work,



I will take my kids to school,



and my life, changed forever, will go on.









I pledge I will support my President and armed forces in whatever action must be taken. [...] 3457 34 37_Re: The World Trade Center Discussion12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Mon, 17 Sep 2001 09:30:45 -0500502_iso-8859-1 One Day Each

My daughter came and spoke to me of "half-mast flags", and "How could Old Glory be?" She quietly spoke of planes flying, and then they were not, Of "what would happen if daddy didn't come home, how would toys be bought?"

With a child's earnest heart, she continued to ask Why?" How could so many people die, and "Weren't planes just supposed to fly??" Her Grandpa worked at the Pentagon, so many years ago, Was his old office crushed? She simply had to know. [...] 3492 26 28_DB2 subsystem "health index"0_26_truman.g.brown@VERIZON.COM31_Mon, 17 Sep 2001 10:47:23 -0400633_us-ascii Our management wants periodic reporting with history on both applications and subsystems. IBM DB2 PM with "top" option for time in DB2 will satisfy the application issue. This will be for over 100 subsystems.

Since work for a subsystem is mainly I/O, i was thinking of creating some sort of "index" number based on GETPAGES, read I/O, updates, pages written, and log writes and I/O massaged by total CPU for a subsystem's address spaces. The numbers used to create the index would be displayed. This is certainly an oversimplification, but might be better than spreadsheets with numbers people probably wouldn't [...] 3519 58 49_World Trade Center Discussion -- From List Owners14_Philip Gunning24_philip.gunning@QUEST.COM31_Mon, 17 Sep 2001 07:50:41 -0700307_- Fellow Professionals, I have read some of the discussions this morning and urge all of you to keep your comments civil. I agree with John and support free discussion on this and believe it will come to a gradual end as time passes. So plese show restraint and compassion on the subject. Thanks, Phil [...] 3578 36 40_Between Perform much better than >= & <=11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Mon, 17 Sep 2001 09:57:37 -0500427_iso-8859-1 First my condolences to all effected by the tragedy & God bless the land of Oppurtunity America.

Back to Business :

If some one can help understand this. We are at DB2 6.1 (OS/390).

We have observed that whenever we have in the where clause >= & <= in the where clause the DB2 optimizer decides not to use the index & when we change the where clause to between it decides to use the index. [...] 3615 75 37_Re: The World Trade Center Discussion12_Dunn, Judith27_Judith.Dunn@INGRAMMICRO.COM31_Mon, 17 Sep 2001 11:15:45 -0400511_iso-8859-1 Word for word translations are difficult in any language. In French, after a simple 'hello,' it becomes more difficult with phrases like 'I have hunger' and 'where does the beach find itself.'

'The truly defeated/vanquished of war are the dead.'

Judy Dunn Ingram Micro



-----Original Message----- From: Billings, Linda [mailto:linda.billings@DOA.STATE.WI.US] Sent: Friday, September 14, 2001 12:20 PM To: DB2-L@RYCI.COM Subject: Re: The World Trade Center Discussion [...] 3691 110 37_Re: The World Trade Center Discussion15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Mon, 17 Sep 2001 10:29:33 -0500418_iso-8859-1 Thanks, I posted again with a correct translation similar to yours. I am finding that translating French is particularly difficult because the French phrasing is so different from our own. Our French student is a bit perplexed by it as well. He is only 16 and is still learning. His English is getting better as time passes though. I hope that my French does, too, or I'm going to sound really stupid. [...] 3802 48 25_Executing Script from CLP10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Mon, 17 Sep 2001 08:33:18 -0700354_us-ascii I have a db2 database in NT server and I am connected to server by DB2 client. I have executed the command from CLP. However,when I create the command in a file and try to execut from CLP, I am getting the following error.

D:\PROGRA~1\SQLLIB\BIN>db2 -tvf c:\db2look.txt

DB21007E End of file reached while reading the command. [...] 3851 16 29_Re: Cut Off For Optimisation?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 10:31:40 -0500329_- The SQLCODE for a statement that is too large or too complex is -101. The limits change with each new version. For statements that have more than 9 way joins, there were some very useful improvements in V7 documented in the V7 Performance Topics red book. If you are running V6, the limit for the number of tables is 225. [...] 3868 16 44_Re: comparison between DB2 6.1 and Db2 6.1.112_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 10:41:18 -0500374_- You need to specify the platform to get a solid answer. If this is DB2 for OS/390 V6, then there is much more granularity in the choices. There is a Redbook that documents the changes, SG24-6108 and an appendix which cross references the specific APARs to the function. If you don't have fairly current service for V6, then you are more likely to encounter problems. [...] 3885 18 61_Re: Two Questions excessive -911 errors and defrag of volumes12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 10:54:43 -0500459_- For the -911, there is a chapter in the Administration Guide called Improving Concurrency that has a number of other possibilities. The starting point is generally trying to understand the access pattern causing the problem. The IFCID 0172 and 0196 records have details for the deadlocks and timeouts. Row level locking should improve concurrency, but it often increases the number of deadlocks. Perhaps there is a table space scan or lock escalation. [...] 3904 72 29_Re: Executing Script from CLP18_Gert van der Kooij15_geko@WANADOO.NL31_Mon, 17 Sep 2001 17:54:51 +0200380_iso-8859-1 Hi, The utilities mentioned in chapter 2 of the Command Reference are seperate utilities. You don't need to prefix them with 'db2'. The commands from chapter 3 need to be run with 'db2'. If you run them with the -vtf options you need to end a single command with ';' which is the deault delimiter. Both types of commands need to be executed from a Command window. [...] 3977 35 29_Re: Executing Script from CLP16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Mon, 17 Sep 2001 16:59:05 +0100409_US-ASCII Mathai,

AT LAST - A question not about mainframe DB2!

This one is simple - if you have all the options on different lines of the file, as your example shows, you need to have a ";" semicolon after the last line, or on the last line.

If you were to put all the command on one line, and had only one line per command, you could them omit the "-t" option from the db2 command. [...] 4013 61 29_Re: Executing Script from CLP13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 17 Sep 2001 10:56:12 -0500352_US-ASCII Mathai, Try ending your statement with a ; HTH Kurt



>>> mathaijoydb2@YAHOO.COM 09/17/01 10:33AM >>> I have a db2 database in NT server and I am connected to server by DB2 client. I have executed the command from CLP. However,when I create the command in a file and try to execut from CLP, I am getting the following error. [...] 4075 75 29_Re: Executing Script from CLP20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Mon, 17 Sep 2001 18:02:39 +0200429_iso-8859-1 Hi there,

you simply forget the ";" at the end of your db2look.txt the -t option means you're going to use the default command separation character.

so it shoudl be: db2look -d briodev -u db2admin -c -e -t businessreview_tbl -o c:\bus.txt -i db2admin -w password ;



-----Original Message----- From: Mathai Joy To: DB2-L@RYCI.COM Sent: 17/09/01 17.33 Subject: Executing Script from CLP [...] 4151 56 29_Re: Cut Off For Optimisation?14_Adrian Collett27_adriancollett@TISCALINET.IT31_Mon, 17 Sep 2001 18:15:51 +020092_iso-8859-1 Simon, have you checked out PQ47644 ? Adrian Collett DB2 Consultant Milan, Italy. 4208 13 34_Re: Acessing SQL source from SPT0112_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 11:15:25 -0500423_- There is no source SQL in SPT01. You can get a little information from the Diagnosis Guide & Reference, but source is only in the catalog tables.

Roger MIller, DB2 for z/OS

================================================ 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. 4222 12 16_Re: Tracker Site12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 11:22:29 -0500344_- Please call the service line on this one 1-800-IBM-SERV in the US.

Roger Miller, DB2 for z/OS

================================================ 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. 4235 14 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 11:25:57 -0500416_- I talked with the conference manager this morning. The DB2 Technical Conference will be held. A more formal response will be coming later.

Roger Miller, DB2 for z/OS

================================================ 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. 4250 17 28_Re: Informix vs UDB question25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU31_Mon, 17 Sep 2001 11:25:21 -0500368_iso-8859-1 Sinc IBM purchased Informix a couple of months ago, I've been waiting for the yellow brick road(migration strategy) to be announced. So far it's been slow going. We've got a couple of small but very active Informix databases and we'd like to have a consolidated strategy. Even worse one is a complete turnkey operation and they don't have a clue...... [...] 4268 18 22_DB2 - Single User Mode23_Eamonn Downey/DUB/Lotus23_Eamonn_Downey@LOTUS.COM31_Mon, 17 Sep 2001 17:28:25 +0100457_us-ascii Hi,

Is it possible to place DB2 at an instance or database level in "single user" mode. One way would be to revoke "connect to" privs but this could get rather messy. Any suggestions ?

Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 4287 31 42_Re: MQ Series help needed if someone knows10_Tom Dunlap25_tsdunlap@WORLDNET.ATT.NET31_Mon, 17 Sep 2001 12:41:42 -0400436_us-ascii Vamsi,

The new Cobol for OS/390 can have 01 levels greater than 16MB, assuming you are AMODE(31) and RMODE(ANY). So you can read the entire message into a single area above the 16MB line.

This is not possible with VS Cobol II. Also, you are not able to segment the message once it is placed into the queue. Segmentation must be done at MQPUT time. However, OS/390 does not support segmentation at this time. [...] 4319 39 28_Buffer Pool Warning Messages16_Sharon Zitterman24_Sharon.Zitterman@TRW.COM31_Mon, 17 Sep 2001 12:25:08 -0400354_US-ASCII Hello,

I am looking for some direction on this issue. Research indicates that we have been getting DSNB536I or DSNB538I buffer pool warning messages since shortly after migrating (within minutes) to DB2 V6.

The first time we received it was when a former colleague in this shop ran what appears to be a buffer pool alter job. [...] 4359 12 26_Re: DB2 - Single User Mode15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Mon, 17 Sep 2001 11:32:43 -0500307_us-ascii Bring it up in maintenance mode. start db2 access(maint)

================================================ 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. 4372 33 44_Re: Between Perform much better than >= & <=22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Sun, 16 Sep 2001 13:42:20 -0300347_iso-8859-1 > We just bought a reporting tool which generates these sql's. Can anybody > point to a document which points to this observation > so that we can ask our vendor to generate only between Sql.

Amit,

Not always the DB2 optimizer can convert a "<= and >=" into a BETWEEN predicate. If you need some reference book, see: [...] 4406 31 26_Re: DB2 - Single User Mode13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 17 Sep 2001 11:53:47 -0500329_US-ASCII Eamonn, You can connect to the database in EXCLUSIVE MODE HTH Kurt

>>> Eamonn_Downey@LOTUS.COM 09/17/01 11:28AM >>> Hi,

Is it possible to place DB2 at an instance or database level in "single user" mode. One way would be to revoke "connect to" privs but this could get rather messy. Any suggestions ? [...] 4438 152 36_Re: STAR JOIN potential improvements13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 17 Sep 2001 11:54:48 -0500726_iso-8859-1 Rinze,

I've included my answers to your questions. My understanding is that most sites have disabled star join due to performance degration suffered with the initial implementation. IBM have radically changed the star join detection and have overcome the performance regressions that customers experienced.

2. Does the article in the Pipeline Newsletter (June 2001) give correct information? Answer: Unfortunately not. There are a number of misinterpretations of the May 2000 implementation that appear in this document. Also, since the May 2000 implementation has changed dramatically, the recommendations for index creation are incorrect, and the descriptions of processing and detection rules [...] 4591 25 37_Re: The World Trade Center Discussion13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Mon, 17 Sep 2001 12:00:16 -0500623_- I guess every language has its own way of looking at things. The most common beginner mistake I've come come across is translating "sa femme" as "her wife". Mohammad

On Mon, 17 Sep 2001 10:29:33 -0500, Billings, Linda wrote:

>Thanks, I posted again with a correct translation similar to yours. I am >finding that translating French is particularly difficult because the French >phrasing is so different from our own. Our French student is a bit >perplexed by it as well. He is only 16 and is still learning. His English >is getting better as time passes though. I hope [...] 4617 149 37_Re: The World Trade Center Discussion24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Mon, 17 Sep 2001 13:15:21 -0700619_us-ascii I'm sorry to continue an off-topic discussion, but there's a famous quote by someone I can't remember that "The first casualty of war is the truth". Is that what you're looking for?

Frank



On Mon, 17 Sep 2001 10:29:33 -0500, Billings, Linda wrote:

>Thanks, I posted again with a correct translation similar to yours. I am >finding that translating French is particularly difficult because the French >phrasing is so different from our own. Our French student is a bit >perplexed by it as well. He is only 16 and is still learning. His English >is getting better as time passes [...] 4767 175 37_Re: The World Trade Center Discussion18_Ricardo Lino Gomes20_ricardo.gomes@BCP.PT31_Mon, 17 Sep 2001 18:06:45 +0100397_iso-8859-1 Sorry folks but the sentence actually means 'The true winners of War are the Dead'

hope I've helped U

Best Regards, Cumprimentos,

Ricardo Gomes ricardo.gomes@bcp.pt DIS-Direcção Infraestruturas Sistemas DSC-Sistema Gestão Bases de Dados Tel.:(351) 21 421 92 74 Fax:(351) 21 421 92 79

Servibanca, TAGUSPARK Edifício 6, Piso 2, Ala A 2784-511 Porto Salvo [...] 4943 51 26_Re: DB2 - Single User Mode14_Shari A Thayne24_sathayne@DUKE-ENERGY.COM31_Mon, 17 Sep 2001 13:19:06 -0400569_us-ascii Do you mean something like 'Maint' mode?





Eamonn Downey/DUB/Lotu To: DB2-L@RYCI.COM s cc: Subject: DB2 - Single User Mode Sent by: DB2 Data Base Discussion List



09/17/2001 12:28 PM Please respond to DB2 Data Base Discussion List











Hi,

Is it possible to place DB2 at an instance or database level in "single user" mode. One way would be to revoke "connect to" privs but this could get rather messy. Any suggestions ? [...] 4995 192 37_Re: The World Trade Center Discussion14_Shaul Bergfeld18_sbergfeld@TACT.COM31_Mon, 17 Sep 2001 13:43:51 -0400589_iso-8859-1 Ricardo -

"vaincu" is "losers", not "winners" ...

-----Original Message----- From: Ricardo Lino Gomes [mailto:ricardo.gomes@BCP.PT] Sent: Monday, September 17, 2001 1:07 PM To: DB2-L@RYCI.COM Subject: Re: The World Trade Center Discussion



Sorry folks but the sentence actually means 'The true winners of War are the Dead'

hope I've helped U

Best Regards, Cumprimentos,

Ricardo Gomes ricardo.gomes@bcp.pt DIS-Direcção Infraestruturas Sistemas DSC-Sistema Gestão Bases de Dados Tel.:(351) 21 421 92 74 Fax:(351) 21 421 92 79 [...] 5188 19 24_Ver. 7 Features Synopsis16_Frank E. Giguere13_giguef@NU.COM31_Mon, 17 Sep 2001 13:50:44 -0400417_us-ascii Hi All,

If anyone has or can point me to a very condensed synopsis of Version 7 features I would greatly appreciate it......

Thanks

Frank Giguere

================================================ 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. 5208 12 28_Re: Ver. 7 Features Synopsis15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Mon, 17 Sep 2001 13:07:20 -0500287_us-ascii What's New? Version 7 (GC26-9946-00)

================================================ 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. 5221 50 28_Re: Ver. 7 Features Synopsis16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Mon, 17 Sep 2001 14:15:56 -0400383_us-ascii Listed below are some of the highlights of DB2 Version 7 :

The ability to change system parameters, without stopping DB2 Improved scrollable cursors for more flexible access to a result table A new UNLOAD utility for faster data unloading Enhancements to REORG to keep your data available Migration and fallback support from either Version 5 or Version 6 of DB2 [...] 5272 63 44_Re: Between Perform much better than >= & <=11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Mon, 17 Sep 2001 13:23:22 -0500468_iso-8859-1 Thanks Claudio for the response. I just checked the Craig Mullins Fourth edition book on Page 72 says The BETWEEN predicate is easier to understand and code than the equivalent combination of the less than or equal to predicate(<=) and the greater than or equal to predicate (>=). In past releases it was also more efficient, but now the optimizer recognizes the two formulations as equivalent and there is no performance benefit one way or the other. [...] 5336 90 44_Re: Between Perform much better than >= & <=13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 17 Sep 2001 13:44:08 -0500579_iso-8859-1 Amit,

This topic was covered extensively about 3 weeks ago on the list.

Unfortunately Craig is incorrect that the optimizer converts the range predicate to a BETWEEN. I have confirmed this with the IBM optimizer developers at the Silicon Valley Lab.

Many of us (me included) were under the impression that the filter factors were the same for these, although this is the 2nd time now that I have heard that V6 has produced a very different access path. The recommendation from IBM on this is to raise a PMR (ie. log a problem) on this issue. [...] 5427 93 44_Re: Between Perform much better than >= & <=14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Mon, 17 Sep 2001 13:52:54 -0500438_iso-8859-1 See previous message on this topic. They are the same for costing only in access path selection using the same filter factors. In only words, both assume less than 10% of the data will be considered (simply without details).

As to actual access path selection, the BETWEEN is 1 predicate, the other is 2 predicates meaning different physical access paths can be selected. The query is not rewritten to be the same. [...] 5521 23 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 13:55:35 -0500313_- I edited this note for DB2-L

I saw some notes where there questions about the DB2 Conference and if it will still be held on the planned date.

The answer is yes, the IBM DB2 Conference planned for 10/1 in Orlando will be held as planned. For enrollment information, please go to our website: [...] 5545 94 34_Re: Acessing SQL source from SPT0117_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Mon, 17 Sep 2001 16:08:10 -0300607_iso-8859-1 Roger,

Are you sure?? I´m looking at SPT01 COPY FULL output and I can see source SQL statements there.

This is a piece of a COPY FULL output generated yesterday:

Menu Utilities Compilers Help

BROWSE DSNPBS.DSNDB01.SPT01.A1.D010916.T1600 Line 00000358 Col 219 298 Command ===> Scroll ===> CSR DECLARE ROACMVT3 CURSOR FOR SELECT TBNAME , TBCREATOR FROM SYSIBM . SYSSYNONYMS SELECT TYPE INTO : H FROM SYSIBM . SYSTABLES WHERE NAME = : H AND CREATOR = : H SELECT BNAME , BCREATOR INTO : H , : H FROM SYSIBM . SYSVIEWDEP WHERE BNAME = : SELECT TBNAME , TBCREATOR INTO [...] 5640 145 32_Catalog Deadlock for dynamic SQL12_Mackey Glenn21_GMackey@GUIDEMAIL.COM31_Mon, 17 Sep 2001 14:43:06 -0500329_iso-8859-1 Hi,

We received a deadlock on DSNDB06.SYSDBASE. The two participants in the deadlock were executing dynamic SQL. One was a Java application from Websphere NT, the other a stored procedure (sp) executing Dynamic SQL. The SP was called from a CICS transaction. There was no DDL, just DML - SELECT statement. [...] 5786 33 27_DB2 Interface Update freeze13_Ray Briggs II12_ray@LSMP.COM31_Mon, 17 Sep 2001 14:38:45 -0500537_us-ascii Hello List,

I am a newbie to this list so go easy on me. I have run into a strange problem when updating some tables via the Command Center. I have table with 26 columns (mostly VARCHAR and INTEGER) and 1665 rows. When I do a simple update on the table ("update table set coulmn1='yoda') the command center sits and sits while attempting to update the table. I have waited in excess of 5 minutes for it to stop. Eventually I kill the command. If I go to view the contents of the table now the update WILL have taken [...] 5820 111 53_Central PA User Group - Reminder on Sept 27th meeting10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Mon, 17 Sep 2001 15:58:51 -0400587_iso-8859-1 Reminder, the next Central PA User Group Meeting is being held on Thur, Sept 27th. If you have NOT already registered & are planning to attend, let me know. Tks!! Seeya



Below is a copy of the agenda. Or you can access the website: HTTP://WWW.DB2PARUG.ORG.

Central Pennsylvania Regional DB2 User Group

Date: Thursday, September 27th, 2001 Place: Camp Hill Corporate Center, Building 300, Large Auditorium Cost: FREE Reservations: Email or call: Cathy.Peck@highmark.com (717) 975-7396 or For details and directions: See http://www.db2parug.org [...] 5932 191 109_Central PA DB2 Users Group - Sheryl Larsen, Sept 28th - $175 - DB2 for OS/390 V6/V7 Transition for Developers10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Mon, 17 Sep 2001 15:59:11 -0400670_iso-8859-1 Hi everyone! If you are interested in attending Sheryl's class on Sept 28th & have not already registered, please let me know. The price is $175 per person per class. Tks. Seeya

For more details & directions, see our Central PA DB2 Users Group website: http://www.db2parug.org













Sept 28th, 2001 DB2 for OS/390 V6 & V7 Transition for Developers Sheryl Larsen, Sheryl M. Larsen, Inc. Click here for class abstract Bio & Class Abstract can be found at: http://www.smlsql.com Cost: $175 Per Person - Class is held at Camp Hill Corporate Center Building 300 Click for directions To register for the [...] 6124 237 23_Re: ACCESS PATH Problem11_Howe, Steve15_SHowe@BCBSM.COM31_Mon, 17 Sep 2001 16:12:15 -0400521_iso-8859-1 > List, > We have a Access Path issue within our Warehouse that has baffled me and > my > fellow DBAs. Our warehouse is DB2 Version 6 on OS/390. Our primary > detail > tables have 48 Partitions using a SMALLINT Partition Key equal to 1 - 48. > > Each Part contains data for a rolling YEAR/MONTH. Each Part has about 5 > million rows and also have 10-12 NPIs. Each NPI begins with the > PARTITION_KEY_CD. > Each Part is a Month/Year. All Data is 100% Clustered. We have a > Control Table > that contains a [...] 6362 164 35_Re: Accessing SQL source from SPT0112_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 17 Sep 2001 15:20:15 -0500379_iso-8859-1 As a point of clarification, there is SQL in the DB2 Directory. It is there for diagnostic purposes and is not guaranteed for any other purpose. The DB2 Directory is not documented for use by ISVs or customers. The format of the Directory can change based on a PTF. If you want the complete SQL Statement outside of the program code itself, then keep your DBRMs. [...] 6527 23 40_Pre-compiler generated statement numbers8_Harish G24_hari_gangadhar@YAHOO.COM31_Mon, 17 Sep 2001 15:32:43 -0500552_- Hi,

The statement numbers generated by the DB2 pre-compiler is unique till the number 32767. The DB2 Admin. guide says that for numbers above 32767, the statement numbers are set to zero, and this can happen for a very long program. My question is what constitutes a very long program ? How many static SQLs can such a program contain ? Is this a constant at all times ? What factors, if any does the numbering depend on ? The reason I ask this is that the pre-compiler generated numbers does not start linearly from 1 through 32767 in a [...] 6551 70 46_need separate DB2 subsystem for Data Warehouse10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 17 Sep 2001 14:56:28 -0600332_iso-8859-1 Hi! Now we have a DB2 V7 running on OS/390 for WebSphere Application Server and we will have Data Warehouse running on the same OS/390 LPAR. I am wondering if we need to setup a separate DB2 subsystem for Data Warehouse? If it is recommended, should I separate all DB2 run time libraries for this new DB2 subsystem? [...] 6622 50 35_Re: Accessing SQL source from SPT0117_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Mon, 17 Sep 2001 18:10:36 -0300382_iso-8859-1 >As a point of clarification, there is SQL in the DB2 Directory. It is there for diagnostic purposes and is not guaranteed for any other purpose. The DB2 Directory >is not documented for use by ISVs or customers. The format of the Directory can change based on a PTF. If you want the complete SQL Statement outside of >the program code itself, then keep your DBRMs. [...] 6673 35 52_DB2 Crash Recovery with open cursors on V7.2 and AIX15_Dave Pettengill19_petti1955@YAHOO.COM31_Mon, 17 Sep 2001 14:04:09 -0700507_us-ascii We are running DB2 V7.2 (7.1 with fixpack 3) on a DB2 EEE instance on AIX 4.3.3.

We hit a case where we are getting a SQL -01 while trying to access an entry named usertemp.

According to IBM support we are are declaring this usertemp entry and opening a cursor against it. We then delete it while the cursor is still open. DB2 then goes into crash recovery, which works as it should. We do run into some log space problems and everything is held up for the recovery to complete. [...] 6709 239 69_RAI offers free use of recovery software to distressed organizat ions0_16_admin@RELARC.COM31_Mon, 17 Sep 2001 17:21:16 -0400826_iso-8859-1 Press Release

Relational Architects Intl Riverview Historic Plaza 33 Newark Street Hoboken NJ 07030 - 5604 Tel: 201 420-0400, Fax: 201 420-4080 www.relarc.com







For immediate release: Contact: Max Gartner 201 420-0400





RAI offers free use of recovery software

to distressed organizations





Hoboken, New Jersey, September 13, 2001: Relational Architects International (RAI) announced that it will offer distressed organizations use of its software products that can facilitate the recovery of mainframe computer operations until February 2003 without paying a license fee. The company emphasizes that its products are designed only for S/390 mainframes running z/OS or OS/390. Mainframes are commonly [...] 6949 55 44_Re: Pre-compiler generated statement numbers17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Mon, 17 Sep 2001 18:22:04 -0300510_us-ascii From SQL Reference (SYSIBM.SYSPACKSTMT):

STMTNO:

Statement in the package's source program. A statement number greater than 32767 will be displayed as zero (39) or a negative number (40) .

(39) Rows in which the value of SEQNO, STMTNO, and SECTNO are zero are for internal use.

(40) To convert a negative STMTNO to a meaningful statement number that corresponds to your precompile output, add 65536 to it. For example, -26472 is equivalent to +39064 (-26472 + 65536). [...] 7005 74 50_Re: need separate DB2 subsystem for Data Warehouse25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU31_Mon, 17 Sep 2001 16:22:56 -0500454_iso-8859-1 I would separate them for the following reasons:

1)Hours of availability 2)Tuning isolation 3)Maintenance window/batch window 4)DB/2 Catalog/LOG/Recovery/Utilities

Technically not required it's just what I'd do in our environment.

The only thing that needs to be different is the DSNTIJUZ parm. It can be in the same DSNEXIT library with a different name. Again I'd make a separate library just to eliminate the confusion. 7080 46 44_Re: Pre-compiler generated statement numbers14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Mon, 17 Sep 2001 15:15:47 -0700358_us-ascii The statement numbers are the actual line number where the SQL statement starts in the pre-compiled output of the program. Thus if you put your SQL statements near the end of the program the statement numbers will be higher.

If you are using version 6 or above the QUERYNO clause will eliminate the occurrence of zero statement numbers. [...] 7127 38 28_Re: Ver. 7 Features Synopsis12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 17:26:26 -0500834_- One page synopsis of V7 improvements:

http://www.ibm.com/software/data/db2/os390/about.html

Brochures on specific features:

http://www.ibm.com/software/data/db2/os390/brochures.html

What's New and other books

http://www.ibm.com/software/data/db2/os390/v7books.html

What's New? the pdf Chapter 1, pages 1 - 8 provides an overview. Chapter 2 pages 9 - 29 provides the next level.

http://www.ibm.com/software/data/db2/os390/v7books.html

What's New? the BookManager version

http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnwnh11/CCONTENTS

There is another overview in the V7 Presentation Guide, SG24-6121

http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246121.html? Open

or start at ibm.com/redbooks and search for SG24-6121 [...] 7166 28 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 17 Sep 2001 17:29:06 -0500375_- IBM Technical Conferences Still A Go!

In light of the tragic events of last week, IBM Learning Services has received a number of inquiries as to whether our Technical Conferences will be held. This is to confirm that there have been no changes to the schedule of these conferences and they will be held as originally planned. The remaining Conferences include: [...] 7195 87 40_Information on Upcoming IDUG Conferences14_Philip Gunning24_philip.gunning@QUEST.COM31_Mon, 17 Sep 2001 15:46:48 -0700588_- Dear Fellow DB2 Professionals,





IDUG 2001 Events are Proceeding

The International DB2 Users Group is sensitive to the terrible tragedy that took place in New York City, Pennsylvania, and Washington, DC on September 11, and extends its deepest sympathies to those personally affected by these horrific events. In concert with efforts throughout the United States and the world, to return to our daily activities, IDUG's 2001 European and Asia Pacific conferences, and the Canadian Technical Symposium, will be held as planned this October and November. [...] 7283 172 36_Re: Catalog Deadlock for dynamic SQL15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Tue, 18 Sep 2001 10:00:07 +1000375_iso-8859-1 Glen,

I believe dynamic SQL locks the DBID during the PREPARE, exactly how I don't remember but if it's locking the page where the DBID is stored, that may explain your problem (but don't bet on it). We've had similar timeouts in the past where the object type has been 00000D01 or 00000D00, and not the tablespace so this may not be much help to you. [...] 7456 247 20_Re: 390 Load Utility13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Mon, 17 Sep 2001 17:05:45 -0700399_us-ascii Hi Rajendra,

Great question, and I am not sure that I know the answer. Let me make a stab at it, and if I am wrong, I am sure that we will hear about it.

If the input decimal looking input character string has no decimal point, and you wish to specify that the last 3 digits are to be considered to be to the right of the decimal point, then you would code a scale of 3. [...] 7704 98 23_Re: ACCESS PATH Problem15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Tue, 18 Sep 2001 10:33:52 +1000543_- Steve,

I'll just get in my 2cents worth before the more eminent members of this list ask more pertinent questions but could it be not so much that you're 'crossing years' but that the optimiser has determined the filter factors are such that you need such a large proportion of the data that a prefetch scan is more efficient? A bit of a longshot but also could the view be muddying the waters a bit by fooling DB2 into not selecting from only the discrete partitions it needs too? It may be an idea posting the SQL for the view [...] 7803 22 43_The World Trade Center discussion is closed13_Morrill, John12_JohnM@VP.NET31_Mon, 17 Sep 2001 19:20:17 -0600555_iso-8859-1 Dear Subscribers!

I would like to thank all of you for sharing during the aftermath of this world tragedy. However, as the rest of the world moves on, so must the DB2-L community.

The World Trade Center Discussion is now closed.

Thank You! J. Michael Morrill DB2-L Chief List Owner

================================================ 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. 7826 179 18_Re: SQL QUERY HELP17_Demetris Kasheris19_DKasheris@LAIKI.COM31_Tue, 18 Sep 2001 07:16:44 +0300473_us-ascii Terry, this is to thank you for your help. I tried what you recommended and their was major improvement. By setting the Query optimization class to 9 and changing the sequence of the index the cost dropped from 508 units to 204.

thanks

Content-Transfer-Encoding: 7bit Date: Wed, 12 Sep 2001 00:27:41 -0500 From: Terry Purcell Subject: Re: SQL QUERY HELP MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" [...] 8006 112 61_Re: Two Questions excessive -911 errors and defrag of volumes18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Tue, 18 Sep 2001 12:46:40 +0530630_- Hi Cory,

I do not have an answer for #1. Regarding defrag, i know of only the 'compress' utility which I am sure you must have already considered but the presence of DB2 user defined d/s should be a cause of concern.Just my 2 cents.

thanks, Rajendra.



>From: "Johnson, Daryl" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Two Questions excessive -911 errors and defrag of volumes >Date: Fri, 14 Sep 2001 12:59:29 -0700 > >Our RPROF table is defined so 1 row will fit on a page and we still >encounter -911's. > >It is [...] 8119 187 51_Disappointed about potential STAR JOIN improvements19_Brouwer, R. - SPLXR21_Rinze.Brouwer@KLM.COM31_Tue, 18 Sep 2001 09:52:42 +0200444_iso-8859-1 Terry,

Thanks for your detailed answers on my questions. I am impressed by your knowledge about this subject.

I feel disappointed about the STAR JOIN as it is now. It will not help us out of our problems (extract a lot of data from the central DWH to de-central Data Marts and cubes) I fear. The STAR JOIN feature seems to be most helpfull when you have a Data Mart on DB2 for OS/390 that is queried by end users. [...] 8307 85 32_Re: DB2 subsystem "health index"9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Tue, 18 Sep 2001 09:16:20 +0100428_iso-8859-1 George,

Below is the output from our Key Performance Indicators (KPIs). Our management are happy with it, hope it gives you a start.

ACTUAL TARGET KEY PERFORMANCE INDICATOR VALUE VALUE --------------------------------- ---------- -------------------- EDM POOL FAILURES COUNT 0.00 EQUAL TO ZERO

TABLES WITH NO RECOVERY 0.00 EQUAL TO ZERO

DATA MANAGER CRITICAL COUNT 0.00 EQUAL TO ZERO [...] 8393 21 12_SQL practice13_manish raj kr26_krmanishraj@REDIFFMAIL.COM31_Tue, 18 Sep 2001 08:43:38 -0000483_- Greetings, I have been asked to conduct some SQL session.I am currently looking for some books or article to practice various aspect of SQL in DB2. Any pointer or web address will be highly appreciated

Thanks in advance Manish



===============================================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. 8415 91 51_Antwort: Re: MQ Series help needed if someone knows15_Durga Vamsidhar22_durga.vamsidhar@DB.COM31_Tue, 18 Sep 2001 10:32:50 +0100568_iso-8859-1 Hi Thomas,

Thanks for your response. Can we talk offline regarding this problem.

Regards, Vamsi







Datum: 17.09.2001 18:38 An: DB2-L@ryci.com







Antwort an: DB2 Data Base Discussion List

Betreff: Re: MQ Series help needed if someone knows Nachrichtentext:

Vamsi,

The new Cobol for OS/390 can have 01 levels greater than 16MB, assuming you are AMODE(31) and RMODE(ANY). So you can read the entire message into a single area above the 16MB line. [...] 8507 39 16_AW: SQL practice12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 18 Sep 2001 10:48:30 +0200415_iso-8859-1 Manish,

just a few books where you can find a lot of SQL information:

1. SQL FOR SMARTIES - Advanced SQL Programming from Joe Celko



2. Understanding the New SQL - A complete Guide from Melton Simon

3. DB2 DEVELOPERS GUIDE Fourth Edition from Craig S. Mullins

4. An Introduction to DB2 for OS/390 Version 7 from Susan Graziano Sloan und Ann Kilty Hernandez [...] 8547 18 30_scope of check pending status.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Tue, 18 Sep 2001 04:05:30 -0500544_- Hi,

The admin guide for check data says 'CHECK DATA need not always examine an entire table space;a scope is recorded with each check pending status, and only rows within the scope must be checked.'

My question - is this ability,to find scope, built into check data utility.

TIA.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8566 47 25_One Warehouse, Many Marts14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 18 Sep 2001 10:11:00 +0100370_iso-8859-1 Dear Listers,

If anyone has implemented a Data Warehouse (any platform, not necessarily DB2) with feeds from many heterogeneous sources, feeding into multiple Data Marts (not just one Mart), please let me know if you had any performance, scheduling, ETL concerns? You may contact me offline if you like, unless everyone else is REALLY interested. [...] 8614 40 17_syncpoint manager23_Eamonn Downey/DUB/Lotus23_Eamonn_Downey@LOTUS.COM31_Tue, 18 Sep 2001 10:55:00 +0100691_us-ascii Hi,

What can you guys tell me about the syncpoint_manager. I have an AIX DB2 6.1 server that is getting errors in the db2diag.log file ...

syncpoint_manager sqlcspl_pgrlg Probe:145

DiagData

Dump File:/db2_src/db2/sqllib/db2dump/21630.000 Data:system_error(overflow)

and

syncpoint_manager sqlcspm_readlog Probe:10 SPM0438 The DB2 Syncpoint Manager recovery log is bad.

I have come across a tech note which says...

Start the Syncpoint Manager, erase the spmlog directory and start DB2. These actions cause DB2 to create new syncpoint log files and to establish cold start connections with all the DRDA application servers. [...] 8655 119 23_Re: ACCESS PATH Problem13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 18 Sep 2001 05:23:30 -0500507_iso-8859-1 Steve,

You may wish to send in the actual explain output from both queries also.

Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Mallett, Steven Sent: Monday, September 17, 2001 7:34 PM To: DB2-L@RYCI.COM Subject: Re: ACCESS PATH Problem [...] 8775 81 29_Re: One Warehouse, Many Marts19_Brouwer, R. - SPLXR21_Rinze.Brouwer@KLM.COM31_Tue, 18 Sep 2001 12:27:23 +0200351_iso-8859-1 Marcus,

We have the situation you describe (1 DWH, many DM's and cubes). DWH in DB2 on OS/390. Some of our concerns are:

1. Performance. See my recent STAR JOIN mails on the list. 2. Scheduling due to the performance problems. 3. ETL tool. The productivity of this tool is much less than we would like (expect) it to be. [...] 8857 32 38_Some OS/390 Stored Procedure Questions24_Philip Nelson (Team DBA)21_teamdba@ATTGLOBAL.NET31_Tue, 18 Sep 2001 11:45:11 +0100473_us-ascii We're dipping our toes into the Stored Procedure on OS/390 waters.

We're using a single SPAS, DB2 V6.1 and no WLM (not set up in our environment yet).

Here are some questions -

1) When an SP abends it leaves it lying in status STOPABN. Is there a way to get the SP restarted automatically ? I realise that this could be a problem if an SP was constantly abending - but on the other hand I can see situations where it would be beneficial. [...] 8890 33 42_Re: Some OS/390 Stored Procedure Questions13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 18 Sep 2001 07:12:25 -0400365_iso-8859-1 Hello Phil,

I can help you with 3) Abend-AID and I may be able to help with 2).

from one of the Abend-AID developers: I'm proud to say that Abend-AID has supported stored procedures since ETP of 4.1.

The set up details are in the install guide for Abend-AID.

And yes, we support ALL attach methods for stored procedures. [...] 8924 167 55_Re: Disappointed about potential STAR JOIN improvements13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 18 Sep 2001 06:10:06 -0500397_iso-8859-1 Rinze,

There are other improvements that have been delivered with the star join access path. Firstly, the join has been pushed down to Data Manager (Stage 1), rather than RDS (Stage 2). There is also an index key feedback loop to minimize unnecessary probes into the fact table, and also the ability to maintain index matching predicates even if a dimension key is missing. [...] 9092 193 55_Re: Disappointed about potential STAR JOIN improvements19_Brouwer, R. - SPLXR21_Rinze.Brouwer@KLM.COM31_Tue, 18 Sep 2001 13:57:21 +0200504_iso-8859-1 Terry,

Now it is crystal clear to me. For our situation (extract a large part of the fact rows in the central DWH, based on time periods only, to data marts and cubes) I expect only minor improvements.

Thanks for your help.

Kind Regards, Rinze Brouwer

-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Tuesday, September 18, 2001 1:10 PM To: DB2-L@RYCI.COM Subject: Re: Disappointed about potential STAR JOIN improvements [...] 9286 12 28_Re: Ver. 7 Features Synopsis13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Tue, 18 Sep 2001 07:35:01 -0500330_- Here is a good article on version 7 (OS/390):

http://www.dbazine.com/DB2v7.html

================================================ 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. 9299 118 58_Re: WLM & cobol stored procedure errors - 471 ----00E7900612_Hamid Alvani22_alvani@DPI2.DPI.NET.IR31_Tue, 18 Sep 2001 14:26:25 +0330476_US-ASCII Hello listener; My condolences to all those affected by attack to WTC. God Bless you all. I have same problem to run of sample UDF in DSN610.SDSNSAMP(DSNTEJ2U) has anyone else had this problem? Many thanks in advance; HAMID ______________________________ Forward Header __________________________________ Subject: Re: WLM & cobol stored procedure errors - 471 ----00E79006 Author: DB2 Data Base Discussion List at Internet Date: 9/11/01 9:31 AM [...] 9418 54 42_Re: Some OS/390 Stored Procedure Questions13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Tue, 18 Sep 2001 07:56:13 -0500660_iso-8859-1 For #1, you can just bump the MAX ABEND COUNT (STORMXAB). From the installation guide...

4. MAX ABEND COUNT Acceptable values: 0-225 Default: 0 Update: Option 28 on panel DSNTIPB DSNZPxxx: DSN6SYSP STORMXAB

The installation guide recommends leaving this to zero for production systems, meaning a stored procedure is stopped after the first abend. I've never quite understood this recomendation and don't really think it's practical in a high-volume production environment. Yes, you want to know if there was an error, but do you really want to shut down your production application every time a single error occurs? (Just because [...] 9473 69 42_Re: Some OS/390 Stored Procedure Questions0_26_JCameron@MSI-INSURANCE.COM31_Tue, 18 Sep 2001 08:01:48 -0500435_us-ascii On #1, you can change the zparm on DSNTIPX for maximum abend count. It defaults to 0, so the first abend will stop the procedure. I believe the juz parm is STORMXAB.

John Cameron JCameron@msi-insurance.com







"Seibert, Dave" cc: Sent by: DB2 Data Subject: Re: Some OS/390 Stored Procedure Questions Base Discussion List [...] 9543 13 63_Re: Is the DB2 Technical Conference in Orlando going to happen?11_John Kruger23_krugerj@ISP.STATE.IL.US31_Tue, 18 Sep 2001 08:47:18 -0500451_- I'm glad the DB2 Technical Conference is still a go. The airlines are saying they will not reach normalcy anytime soon. Will we get a full refund if we find out the last minute our flight has been cancelled?

================================================ 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. 9557 25 29_Online Reorg and Build2 Phase13_Moss, William15_MossW@AETNA.COM31_Tue, 18 Sep 2001 09:49:48 -0400546_- Our environment is DB2 V6 and OS/390 V2R10. We have been running online reorgs (share level change) of a single partition of a partitioned tablespace with several NPI's, and the Build2 phase has been as long as two hours. I know that the long Build2 phase is expected in DB2 V6 and that it supposed to be improved in V7 by using additional parallel processing. Does anyone have any experience with running online reorgs in V7 that have shown a significant improvement with the Build2 phase ? We will be migrating to V7 sometime next year. [...] 9583 70 42_Re: Some OS/390 Stored Procedure Questions14_Suparna Murthy19_msuparna@IN.IBM.COM31_Tue, 18 Sep 2001 19:23:14 +0530502_us-ascii David,

I am a first time Abend-aid user ,I need some tips as where I can look for info ,on this tool.

Thanks and Regards, suparna ------------------------------------------------------------------------------------------------------------------



Suparna Murthy Technical Manager, IBM Global Services India , S/390 group, Competency Center,Golden Towers Ground Floor, Airport Road, Bangalore - 560017 Tel.No : 5267117 ,ext : 3433 email-id : msuparna@in.ibm.com [...] 9654 44 44_Migration V5 to V6 and 255 character support10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 18 Sep 2001 15:55:56 +0200333_us-ascii Estimeed list

We are migrating DB2 from V5 to V6 but I've a (half) doubt :

I noted that there's a prerequisite - PTF 22051/APAR UQ28912 - for 255 character support for V5. This is a PE PTF

because the CATMAINT you must execute when you apply this PTF is no longer mandatory because there's another [...] 9699 31 19_Re: data conversion11_Daniel Adam16_dadam@GRATEX.COM31_Tue, 18 Sep 2001 09:11:52 -0500451_- On Fri, 14 Sep 2001 10:44:35 -0500, Roger Miller wrote:

>Are the Unicode services installed on OS/390? I think it's still an >optional component. > >Roger Miller, DB2 for z/OS > >================================================ >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. [...] 9731 97 48_Re: Migration V5 to V6 and 255 character support9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Tue, 18 Sep 2001 15:23:16 +0100422_us-ascii Max, Your understanding of this is the same as mine. I went down the route of steps '1' and '2'; therefore I cannot confirm that step '3' will work (although the hold docs say it should). I thought that by carrying out the steps in 2 stages (and then later migrate to V6), there was slightly less risk than making the change during the migration to V6. Kind Regards, Andy Hunt - Scottish And Southern Energy [...] 9829 27 34_Websphere and open cursor problem.12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Tue, 18 Sep 2001 09:47:24 -0500555_- We are using Websphere and JDBC on our V6.1 DB2 regions. We have 1 server running 5 connection pools. We are experienced some projects getting error messages indicating that there are no cursors available. This results in us having to recycle the server which puts all the projects on hold for that amount of time. We are using the default of 100 nohold and 100 hold cursors. We can increase this number but it would not be addressing the problem. The only thing we can see from our online monitor is that some threads show a larger number of open [...] 9857 70 29_Calling DSNTIAR in a SQL proc23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 18 Sep 2001 10:53:44 -0400397_ISO-8859-1 Hello List !

I would like to call DSNTIAR within a SQL procedure and then pass the formatted error text to the caller. This is a request from application team, but I am not able to get this done. Normally the SQL procedure passes the SQLCODE to the caller, but the application team wants a DSNTIAR type of output as they normally do in COBOL program. Has anyone done this ? [...] 9928 21 25_incremental backups on NT17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Tue, 18 Sep 2001 10:00:22 -0500481_us-ascii Hello,

We are running DB2 7.2 on NT. Does anyone know if DB2 support incremental backups on this platform? I don't see anything in the command reference.

Thanks

Jeremy Schleicher May Department Stores Company.

================================================ 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. 9950 18 34_Re: Acessing SQL source from SPT0112_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 10:08:10 -0500525_- I don't know for sure. The information is proprietary. If you do reverse engineer the result, then a PTF or release might let you do the work again. What I do know is that the text should be complete in the catalog, and that is the place to find the information more easily. If something is broken, then the second source would be to look at the log, copies and recovery choices. Another option would be the DBRMs in a pds. Then there is the source code. If none of those work, then the choices become more difficult. [...] 9969 22 24_Database management tool12_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM31_Tue, 18 Sep 2001 11:08:48 -0400374_- Hi Everyone,

I am working on a requirement to gather information on DB2 database management tool for our environment ( DB2 V 7.1 OS/390 ). I am aware of DBA-XPERT from Compuware. Does anyone have an opinion on this product ? What are your experiences and likes/dislikes. Are there other comparable products out there ? Thanks in advance for all the responses. [...] 9992 21 20_Re: 390 Load Utility12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 10:12:28 -0500567_- This type of question is easy to answer from the SQL Reference. The scale can be implicit, but more often it's explicit, as in CREATE TABLE or a decimal constant. The scale of 789. is 0. The scale of 123.456 is 3.

Decimal (DECIMAL or NUMERIC) A decimal number is a System/390 packed decimal number with an implicit decimal point. The position of the decimal point is determined by the precision and the scale of the number. The scale, which is the number of digits in the fractional part of the number, cannot be negative or greater than the precision. [...] 10014 24 33_Re: Calling DSNTIAR in a SQL proc12_Jim Harrison17_jharrison@GMX.NET31_Tue, 18 Sep 2001 11:23:45 -0400571_us-ascii Yes. I posted a COBOL routine which should work for you. Check the document archives of the list and search on the keyword error. (see link at the bottom of this post for directions to the documents page).

At 10:53 AM 09/18/2001 -0400, Jain, Sanjay (Exchange) said: >Hello List ! > >I would like to call DSNTIAR within a SQL procedure and then pass the >formatted error text to the caller. This is a request from application team, >but I am not able to get this done. Normally the SQL procedure passes the >SQLCODE to the caller, but the application [...] 10039 24 33_Release levels and service levels12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 10:28:47 -0500445_- If you are in development now, I'd tend to start with more current levels fo the software, especially if this is a large project. That can save you a migration later, when the cost is higher, while you have more facilities to use in the application now.

You will want to check on the end of service date for the client platform. The DB2 for OS/390 V6 end of service isn't announced, but it's likely to be two year earlier than V7. [...] 10064 56 29_Re: incremental backups on NT9_Chris Tee23_chris.tee@UK.ZURICH.COM31_Tue, 18 Sep 2001 16:30:31 +0100422_us-ascii Jeremy,

Incremental backups were added in v7.2 (fixpak3), I'm not sure if the updated manuals are available yet but the new syntax is in the Fixpak 3 Release Notes in section 10.12.

HTH

Chris





Jeremy Schleicher cc: Sent by: DB2 Data Subject: [DB2-L] incremental backups on NT Base Discussion List [...] 10121 12 28_Re: Database management tool15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Tue, 18 Sep 2001 10:28:04 -0500305_us-ascii Change Manager from BMC, Administration Tool from IBM.

================================================ 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. 10134 25 28_Re: Informix vs UDB question12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 10:40:09 -0500699_- We did announce the our strategy, and there is a white paper available from your account representative or data management specialist. Here is the web page to start:

http://www.ibm.com/software/data/informix/

Then click on updated strategy paper. Here is a paste:

"There will be no forced migration to DB2," said Janet Perna during her keynote speech at Giga Information Group Inc.'s GigaWorld IT Forum in Las Vegas. An IBM white paper discussing IBM's future direction for Informix products confirms IBM's intention to protect Informix's customers investments, not forcing anyone to migrate to DB2 and reiterates support for multi-hardware platforms and open standards. [...] 10160 77 29_Re: incremental backups on NT18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Tue, 18 Sep 2001 11:49:18 -0400410_- You can also issue the db2 ? command-name in a db2 command window to get the syntax.

Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2



-----Original Message----- From: Chris Tee [mailto:chris.tee@UK.ZURICH.COM] Sent: Tuesday, September 18, 2001 11:31 AM To: DB2-L@RYCI.COM Subject: Re: incremental backups on NT [...] 10238 63 38_Re: Websphere and open cursor problem.12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 18 Sep 2001 08:50:57 -0700608_us-ascii Couple of questions: What release of Websphere and JDBC drivers are you using? Are you using DB2 Connect JDBC drivers and if so, what fixpack?

And are your JAVA programmers closing the result sets and cursors after they have finished their application and implicitly closed the connection? If not, this will account for the growth in cursors and will cause various types of problems, including locks being held on DB2 where you don't expect them. --- Richard Pack wrote: > We are using Websphere and JDBC on our V6.1 DB2 regions. We have 1 > server > running 5 [...] 10302 77 29_Re: incremental backups on NT18_Gert van der Kooij15_geko@WANADOO.NL31_Tue, 18 Sep 2001 17:53:48 +0200713_iso-8859-1 Hi, Some updated PDF files are already available on ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter



----- Original Message ----- From: "Chris Tee" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, September 18, 2001 5:30 PM Subject: Re: incremental backups on NT



> Jeremy, > > Incremental backups were added in v7.2 (fixpak3), I'm not sure if the > updated manuals are available yet but the new syntax is in the Fixpak 3 > Release Notes in section 10.12. > > HTH > > Chris > > > > Jeremy Schleicher > AY-CO.COM> cc: > Sent by: DB2 Data Subject: [DB2-L] incremental backups [...] 10380 58 33_Re: Calling DSNTIAR in a SQL proc0_19_Tim.Lowe@STPAUL.COM31_Tue, 18 Sep 2001 11:03:37 -0500587_us-ascii Isn't that what the DSNACCMG stored proc is for?







Jim Harrison cc: Sent by: DB2 Subject: Re: Calling DSNTIAR in a SQL proc Data Base Discussion List



09/18/2001 10:23 AM Please respond to DB2 Data Base Discussion List











Yes. I posted a COBOL routine which should work for you. Check the document archives of the list and search on the keyword error. (see link at the bottom of this post for directions to the documents page). [...] 10439 20 32_Re: Buffer Pool Warning Messages12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 11:13:03 -0500410_- It sounds as though you need to look at virtual and real storage ASAP. Have you mapped out the usage? In Version 7 we added some instrumentation to make it easier, with IFCIDs 0217 and 0225. There is a USERMOD for Version 6. The best resources are the IDUG white paper (Spring 2000 IDUG Solutions Journal www.idug.org, the Solutions Journal) and John Campbell's Virtual Storage Management presentation. [...] 10460 16 38_Re: Websphere and open cursor problem.12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Tue, 18 Sep 2001 11:14:14 -0500391_- Websphere is 3.02. The drivers I believe are from our DB2 for OS/390 maintenance, the OSS group runs the process that updates the drivers and creates the DBRM's for the DSNJDBC packages I bind. We are at 0107 level on DB2. We are asking the same thing from the programmers and some have had to changed their code to make sure they close them. That continues to be work on their part. [...] 10477 30 28_Re: Database management tool12_Rumyee, Paul19_paul.rumyee@HBC.COM31_Tue, 18 Sep 2001 12:21:57 -0400597_iso-8859-1 Dan..

On your own opinion, which is better BMC/IBM?





-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHNA.COM] Sent: Tuesday, September 18, 2001 11:28 AM To: DB2-L@RYCI.COM Subject: Re: Database management tool



Change Manager from BMC, Administration Tool from IBM.

================================================ 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. [...] 10508 156 50_Re: need separate DB2 subsystem for Data Warehouse12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 11:42:43 -0500390_- I have an opinion on this one, and it looks pretty close to the earlier response, but I found an old article I wrote on the topic, so here are a couple of twists.

Roger Miller, DB2 for z/OS

Why can't we share?

Data Warehousing adds a new twist to several old questions. As we install data sharing and new applications, we often need to answer questions like ... [...] 10665 135 21_Re: Buffer pool usage14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 18 Sep 2001 12:55:21 -0400549_ISO-8859-1 Tom, Seems I missed this one in the turmoil of trying to get home from Santa Cruz last week. Just how would you propose to make a pool smaller and not drive the reclaim manager?

Now - how do you explain the facts that the I/O rate goes through the roof when you make a pool bigger, or reduce the pool size by only a small amount? From your statement below, there would be almost no impact on I/O by making it larger, and minimal by making it smaller. However, these are not the observed cases in real production environments. [...] 10801 18 50_Re: Finding last executed SQL-STMT in SYSUDUMP ???12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 11:55:07 -0500364_- There is no procedure that's simple or works in every situation. Here's a quick guess, since I don't do this much any more.

If you are lucky the save areas are still chained together. Follow the save areas back to see the call to DSNHLI, DSNALI, ... and look at the area pointed to by R1 when DSN?LI was called. Map that area with the DSNXRDI macro. [...] 10820 45 29_More SQL Procedures Questions14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 18 Sep 2001 12:28:53 -0500614_iso-8859-1 Listers,

DB2 V6, Os/390 V2.7 -

We have some SPL stored procedures that will be inserting to non-declarative RI child tables. We have insert triggers set up to handle the RI. If there is a violation, we signal SQLSTATE and return to the stored procedure.

The problem: We can interrogate the SQLSTATE and SQLCODE from the insert statement (and trigger)within the SQL procedure...not a problem. But, we'd like to also interrogate SQLERRMC as set and returned from the trigger. This will be used to determine which FK we had a violation on. I'm told that that this is not possible [...] 10866 49 33_Re: Calling DSNTIAR in a SQL proc23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 18 Sep 2001 13:31:59 -0400493_ISO-8859-1 Jim, Thank you. I will take a look.

Sanjay Jain





-----Original Message----- From: Jim Harrison [mailto:jharrison@GMX.NET] Sent: Tuesday, September 18, 2001 11:24 AM To: DB2-L@RYCI.COM Subject: Re: Calling DSNTIAR in a SQL proc



Yes. I posted a COBOL routine which should work for you. Check the document archives of the list and search on the keyword error. (see link at the bottom of this post for directions to the documents page). [...] 10916 28 33_Re: More SQL Procedures Questions24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Tue, 18 Sep 2001 13:04:15 -0700540_us-ascii Hi,

We are introducing a new Java front end and we have a DB2 OS/390 server with a sysplex architecture, in which we have 3 datasharing members. We use DB2 connect and DDF to connect to DB2 (OS/390) from Java.

In the Java client , at present we are specifying an IP address and port number to connect to the mainframe DB2 which allows it to always come to only one DB2 member and in effect it is not using the other DB2 members in the sysplex. If anybody has already faced this problem please let me know how to [...] 10945 43 52_Language Environment enablement for DB2 for OS/390 ?12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Tue, 18 Sep 2001 16:25:15 -0400369_iso-8859-1 To DB2 list group:

We are in the midst of readying all our products for Language Environment enablement using the (CEE.xxxx) libraries running on OS/390 V2.10 with DB2 V6.1 and QMF V6.1.

Is there any quick reference documentation to help in what all would need to be reconfigured to allow for LE enablement within our DB2 and QMF setups ? [...] 10989 29 37_DB2/OS390 V6 - CICS Transaction Abend11_Moore, Tony15_TMoore@IKON.COM31_Tue, 18 Sep 2001 16:26:55 -0400453_iso-8859-1 Yo Listers, I have just installed DB2 Version 6 in an OS/390 V2.10 environment and I'm running into a nasty little abend with a CICS (Transaction Server) transaction trying to access DB2. DB2 is creating a dump with the following title:

DUMP TITLE=DB2P,ABND=0C4-00000010,U=ZSSH01 ,C=XYR00.610.SCC -C OMMIT1 ,M=DSNTFRCV,LOC=DSNXPMGR.DSNXPDST+0A2E

The transaction is bombing out with an AD2R abend followed by an ASPR abend: [...] 11019 55 41_Re: DB2/OS390 V6 - CICS Transaction Abend12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Tue, 18 Sep 2001 16:49:21 -0400402_us-ascii Hello Tony, i Recently applied PQ48479(UQ54564) that is an exact match for the csect and offset. We were also getting and S0C4 in that csect and offset from our DDF transactions. Have not had one since putting it on.







"Moore, Tony" @RYCI.COM> on 09/18/2001 04:26:55 PM

Please respond to DB2 Data Base Discussion List [...] 11075 27 38_Looking for quick help on create index8_K.Balaji19_K.Balaji@TARGET.COM31_Tue, 18 Sep 2001 15:57:15 -0500486_iso-8859-1 Hi all,

I am trying to create an alternate index on a test table. But SQL is returning following error.

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E70081, TYPE OF RESOURCE 00000A00, AND RESOURCE NAME .

From the reason code it looks like this table is being accessed by some other plans. But I couldnt find any. Could anybody throw some more light on this and how to over come this problem ? [...] 11103 13 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 17:09:07 -0500394_- For specific questions like this one, I suggest that you call the conference hot line at 1-800-IBM-TEAC (implied h).

Roger Miller, DB2 for z/OS

================================================ 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. 11117 29 48_Re: Migration V5 to V6 and 255 character support12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 17:57:15 -0500415_- That looks like my understanding too. If you do wait until migration to V6 or V7 for the CATMAINT, remember to drop the views on two catalog tables then.

Here are my migration notes:

When you migrate, you may need to remove all views on catalog tables SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTSTATS before running DSNTIJTC or catalog migration will fail. Then redefine the views after migration. [...] 11147 15 33_Re: Online Reorg and Build2 Phase12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Sep 2001 18:04:55 -0500508_- You probably know about the lab measurements in the V7 Performance Topics red book, SG24-6129, section 6.5.2, page 134 in hard copy. There are 8 examples of improvements, ranging up to about five times faster for the BUILD2 phase.

Roger Miller, DB2 for z/OS

================================================ 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. 11163 47 64_Sorry Ricardo but 'vaincu' mean 'defeated' winner is 'vainqueur'16_Norbert Thiebaud23_norbert@INFOTELCORP.COM31_Wed, 19 Sep 2001 01:19:38 +0200358_iso-8859-1 Sorry Ricardo but 'vaincu' mean 'defeated' winner is 'vainqueur'

so: Les vrais vaincus de la guerre, ce sont les morts.

translate pretty much as it was mentionned before in this list:

The truly defeated/vanquished of war are the dead.'

I might have my doubts about my english, but I'm pretty sure of my French :-) [...] 11211 59 42_Re: Looking for quick help on create index15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Wed, 19 Sep 2001 09:50:35 +1000403_- Balaji,

Object type 00000A00 equates to RD.CR.TB where RD is a DB2 Release dependency mark, CR the object creator and TB the Table name Could there be something in your DDL which is release dependant.



regards, Steve Mallett Billing Technical Services

I 5/484 St Kilda Rd, Melbourne

' (03) 9865 7647 7 (03) 9866 1753 * [...] 11271 208 55_Re: Disappointed about potential STAR JOIN improvements15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Wed, 19 Sep 2001 10:09:14 +1000365_iso-8859-1 Terry,

I was just able to let it pass the first time but now that you've mentioned it again I wonder if you could expand on the 'index feedback loop' process a little. I'm imagining it to be a small memory or work table containing matching index keys and RIDs for the last 'x' probes. Are there any other circumstances where it might be used [...] 11480 11 18_DB2 and Windows XP12_Roland Holup19_rholup@MEGSINET.NET31_Tue, 18 Sep 2001 19:19:05 -0500384_- What is IBM's stategic direction on supporting DB2, VA Java and other developer tools for running under the new Windows XP Operating System.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11492 67 55_Re: Disappointed about potential STAR JOIN improvements13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 18 Sep 2001 21:42:14 -0500490_iso-8859-1 Steve,

This process is briefly mentioned in the V6 Technical Update Redbook in section 6.1.3.3., but I will expand on this further.

Basically the index key feedback will return the next highest key value when a probe into the fact table index returns a "not-found" condition. This allows data manager to reposition itself within the dimension workfiles so that unnecessary fact table key combinations are skipped and do not result in unnecessary index probes. [...] 11560 30 24_benefit of compression ?12_Hend Dwiyono16_hdwi@HOTMAIL.COM31_Tue, 18 Sep 2001 22:51:01 -0600522_- Hi folks...

I need help with expert opinion on this issue. Our system has a 64 partitions table with 200 million rows. Only one partition currently compressed and since we are CPU-constrained, we really want to test the CPU overhead vs. savings from I/O, more space in bufferpool with compressed data. Is there really a good way to test the performance between one compressed partition and the other uncompressed partition ? This way we can have legitimate proof for our client if compression is beneficial. [...] 11591 37 28_Re: Database management tool14_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Wed, 19 Sep 2001 16:56:46 +1000438_us-ascii To get close to what BMC change manager requires you will also need DB2 Object compare with the IBM admin tool. If you use BMC change manager full range of functions and have it integrated in your change process then you would probably find that the admin tool is a not yet a viable replacement. I say this on functionaility only. The BMC change process is tightly controlled and integrated with the range of DB2 utilities. [...] 11629 158 28_Re: Database management tool16_Bednarz, Michael22_Michael.Bednarz@CA.COM31_Wed, 19 Sep 2001 07:58:53 +0100685_iso-8859-1 CA!!



Michael Bednarz Computer Associates FSG Consultant tel: +49 (0)211 5306 295 fax: +49 (0)211 5306 200 mobile: +49 (0)170 85 38 576 mailto:michael.bednarzr@ca.com







-----Original Message----- From: Rumyee, Paul [mailto:paul.rumyee@HBC.COM] Sent: Dienstag, 18. September 2001 18:22 To: DB2-L@RYCI.COM Subject: Re: Database management tool



Dan..

On your own opinion, which is better BMC/IBM?





-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHNA.COM] Sent: Tuesday, September 18, 2001 11:28 AM To: DB2-L@RYCI.COM Subject: Re: Database management tool [...] 11788 55 42_Re: MQ Series help needed if someone knows14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Sep 2001 02:08:21 -0500447_- 1) use an area in linkage section to be based on the pointer

linkage-section.

01 lk1-big-buffer. 05 lk-data-area. 10 filler pic x occurs 1 to 999999 times depending on ws5-current-data-size. 05 lk-next-data-area pic x.

2) call CEEGTST to acquire a 100Mb buffer

call ceegtst( ws1-pic-s9(9)-comp-value-zero, ws2-pic-s9(9)-comp-value- 100mb, ws3-pointer, ws4-fc)

set address of lk1-big-buffer to ws3-pointer [...] 11844 26 57_Re: Migration V5 to V6 and 255 character support - thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 19 Sep 2001 09:16:12 +0200367_us-ascii Hi Andy & Roger.

Thank you for your replies. I've read carefully the *** VERY IMPORTANT **** notes on the APAR documentation.

I discovered we defined 2 views on the SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTSTATS tabs when

we installed DB2PM for evaluation purpose, but the don't need them so I dropped these views as COLVALUE is 254. [...] 11871 28 28_Re: Database management tool16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Wed, 19 Sep 2001 08:17:40 +0100457_iso-8859-1 >> On your own opinion, which is better BMC/IBM?

> CA!! >Michael Bednarz >Computer Associates >FSG

Said without a hint of a smile or any bias at all. I'm vey impressed at your ability to keep a straight face there, Michael.

Adrian Challinor e-mail: adrian.challinor@osiris.co.uk phone: 020-8852-1605 This e-mail is environment fax: 0870-0349-134 friendly and was made only mobile: 07860-290-883 from recycled electrons. [...] 11900 97 33_Re: Calling DSNTIAR in a SQL proc18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 19 Sep 2001 13:45:35 +0530303_- Sanjay,

Why not handle this in your cobol prog by calling DSNTIAR after executing the SQL that calls your proc. Although Appl prog & sql guide says that you can have manipulative or logical processing in a host language inside a proc,i am not sure about calling a prog from inside a proc. [...] 11998 148 28_Re: Database management tool14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Sep 2001 10:32:22 +0100572_iso-8859-1 Just to put a little perspective on the BMC/CA/Candle/etc/IBM comparison, don't forget that CA (with it's PLATINUM legacy), BMC and some of the others have been developing tools to help users manage their DB2 environments for almost as long as IBM have been developing DB2 (15 years in our case). It doesn't take a genius to work out what innovations we have brought to the market in that time. OK, so it's not going to take IBM 15 years to catch up (after all, 'copying' others is much easier than being an innovator!!), but neither is it going to happen [...] 12147 48 41_Fast copy to prod debug including indexes0_24_Mike_Jarrett@MANDG.CO.UK31_Wed, 19 Sep 2001 10:40:54 +0000547_us-ascii Hi list, I am creating a fairly big new production database (in a brand new subsystem), and I have a requirement to take a very fast daily copy of it into a debug database (ether in it's own subsystem or in the production subsystem, whichever turns out to be easier). I hope to find some way of doing the copy-over using the RVA DASD snapshot utility. I'm going to create the tables with chosen OBIDs, so that the table OBIDs will match between prod and debug, so I ought to be able to copy the tablespaces across using an 'instant' [...] 12196 48 57_Re: Migration V5 to V6 and 255 character support - thanks16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Wed, 19 Sep 2001 06:18:36 -0400551_iso-8859-1 Incidentally, when you migrate to V6, there is a new installation job, DSNTIJPM, which will generate a report of any objects (such as views on SYSCOLDIST and SYSCOLDISTATS) which would cause the CATMAINT to fail. Very useful!!! I wish it had been around earlier...

Jean E. Crichton Logicon ----- Original Message ----- From: "Max Scarpa" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, September 19, 2001 3:16 AM Subject: Re: [DB2-L] Migration V5 to V6 and 255 character support - thanks [...] 12245 170 45_Re: Fast copy to prod debug including indexes14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Sep 2001 12:07:06 +0100519_iso-8859-1 Mike,

I must be missing something.

WHAT is stopping you snapping the indexes in the same way as the tablespaces?

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com



-----Original Message----- From: Mike_Jarrett@MANDG.CO.UK [mailto:Mike_Jarrett@MANDG.CO.UK] Sent: 19 September 2001 11:41 To: DB2-L@RYCI.COM Subject: [DB2-L] Fast copy to prod debug including indexes [...] 12416 14 16_error code -805.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 19 Sep 2001 07:00:07 -0500450_- Hi friends,

We are facing a rather queer problem while executing a CICS transaction. CEDF execution of transaction abends at a particular point in prog, say PROG1. This prog is bound with plan, PLAN5

================================================ 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. 12431 51 42_Re: Looking for quick help on create index12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 19 Sep 2001 14:52:08 +0200498_iso-8859-1 Hi, According to Msgs&Codes (V7) - you have a dynamic SQL in the cache that references this object. Get rid of it and it should be ok.

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "K.Balaji" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, September 18, 2001 10:57 PM Subject: Looking for quick help on create index [...] 12483 34 31_error -805 in CICS / DB2 appln.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Wed, 19 Sep 2001 07:11:19 -0500406_- Hi friends,

We are facing a rather queer problem while executing a CICS transaction. CEDF execution of a transaction abends at a particular point in prog, say PROG5. This prog is bound with plan,say PLAN5, and abends with a -805 and the msg displays..

PLAN=PLAN1, DBRM=PROG5, STMT=..., SECT=... SQL COMMUNICATION AREA: SQLCABC = 136 SQLCODE = -805 SQLERRML = 041 " " SQLSTATE = 51002. [...] 12518 32 34_QMf V6 for OS/390-End of Support ?12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Wed, 19 Sep 2001 08:22:59 -0400528_iso-8859-1 Group,

I recently read a Software Withdrawal dated September 11, 2001 notice from IBM indicating they are withdrawing from Marketing QMF V6 and this document indicates that migrationg should be to implement to QMF V7. I need to schedule the purchase of this release and this work to be done that is involved and was wondering does anyone know where I can find the end-of-support date so that I can push for this to start up soon? I've looked in this announcement and also on IBM's website information at: [...] 12551 58 38_Re: QMf V6 for OS/390-End of Support ?18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 19 Sep 2001 08:34:35 -0400547_iso-8859-1 I would be interested in this info too. Since we just upgraded to DB2 v6 and we have QMF v6 r1 installed, if they are pulling QMF V6 that would be note worthy.

Thanks: --Steve....

Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:PEB 14 - Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267



-----Original Message----- From: Loria, Susan [mailto:Susan_C_Loria@TVRATINGS.COM] Sent: Wednesday, September 19, 2001 8:23 AM To: DB2-L@RYCI.COM Subject: QMf V6 for OS/390-End of Support ? [...] 12610 30 42_Help needed ASAP. Buffer Pools for Dummies11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Wed, 19 Sep 2001 19:39:38 +0700602_- Dear all,

I have read many things about Buffer Pool Usage from this list, and also from DB2 Administration Guide, but still I have some problem with our DB2 Data Warehouse on AIX box. We have 4 bufferpools, IBMDEFAULTBP, BP_RAND, BP_SEQ, and BP_TEMP. De defined all of our fact tables to BP_RAND, all Dimensional Table to BP_SEQ, and all Temporary Tablespace to BP_TEMP. During loading process to T2 tables, error 'SQL3011C There is not enough storage to process the command.' keep occur. What should we do? And other thing than keep bugging us is that everytime we started the database, [...] 12641 67 35_Re: error -805 in CICS / DB2 appln.15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 19 Sep 2001 07:46:12 -0500468_iso-8859-1 Hi, Rajendra, Have you checked your RCT definition for the transaction? That is where programs executed by a particular transaction in CICS are defined to use a particular plan.



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

(soon-to-be full-time project manager. The state government moves at a slow rate of speed. Mountains have been crushed faster by glaciers.) [...] 12709 57 35_Re: error -805 in CICS / DB2 appln.12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Wed, 19 Sep 2001 08:48:27 -0400621_US-ASCII Does your RCT for the transaction point to plan1 or plan5? Also, look to see if the transaction is using the pool thread and what plan is identified there.

HTH!

Mark Anzmann Tech Services DHRIS Program - Team Accenture Phone: 410-238-1213 email: manzmann@dhr.state.md.us

>>> rajendra_deshpande@HOTMAIL.COM 09/19/01 08:11AM >>> Hi friends,

We are facing a rather queer problem while executing a CICS transaction. CEDF execution of a transaction abends at a particular point in prog, say PROG5. This prog is bound with plan,say PLAN5, and abends with a -805 and the msg displays.. [...] 12767 74 35_Re: error -805 in CICS / DB2 appln.13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 19 Sep 2001 07:47:21 -0500465_us-ascii Rajendra,

Check in you define of your CICS entry (transaction definition) pay close attention to your RCT-Entry. If what you are saying is true then you will find that entry will say PLAN1 instead of PLAN5.

Bob Tilkes









Rajendra Deshpande To: DB2-L@RYCI.COM cc: Sent by: DB2 Data Base Discussion bcc: List Subject: error -805 in CICS / DB2 appln. [...] 12842 70 46_Re: Help needed ASAP. Buffer Pools for Dummies24_Philip Nelson (Team DBA)21_teamdba@ATTGLOBAL.NET31_Wed, 19 Sep 2001 14:26:40 +0100651_iso-8859-1 Endy,

Hopefully some of the following should be a help.

9/19/01 1:39:38 PM, Endy Lambey wrote:

>Dear all, > >I have read many things about Buffer Pool Usage from this list, and also from DB2 Administration Guide, but still I have some problem with our DB2 Data Warehouse on AIX box. >We have 4 bufferpools, IBMDEFAULTBP, BP_RAND, BP_SEQ, and BP_TEMP. >De defined all of our fact tables to BP_RAND, all Dimensional Table to BP_SEQ, and all Temporary Tablespace to BP_TEMP. >During loading process to T2 tables, error 'SQL3011C There is not enough storage to process the command.' keep occur. [...] 12913 122 43_' =?iso-8859-1?q?=A3'_pound_sign_problems?=12_Pete Rybacki32_peter.rybacki@BRISTOL-WEST.CO.UK31_Wed, 19 Sep 2001 14:19:37 +0100 13036 15 45_Re: Fast copy to prod debug including indexes14_Donald A Smith18_dsmithj@US.IBM.COM31_Wed, 19 Sep 2001 09:46:33 -0400505_us-ascii You might consider recreating your production indices withe the RECOVERY YES option that allows you to take image copies of indices. I do not know for a fact that SNAPSHOT would work on indices with this option but it would be work a try. good luck , Don

================================================ 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. 13052 94 33_Re: Calling DSNTIAR in a SQL proc23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 19 Sep 2001 10:05:36 -0400515_ISO-8859-1 Tim,

DSNACCMG might be what I need. However I can not find the source code. The load module is in DSNLOAD. What if IBM abandons this in future ? I would have preferred, to have the source code. Also, DSNACCMG is defined as a non WLM proc in DB2 ver 6 and as WLM proc in ver 7. Our application development environment is ver 6 and every SP is defined to a WLM environment, thus I can not call DSNACCMG from a SQL proc. But I will try this in DB2 ver 7 (sysprog subsystem) and see how it goes. [...] 13147 138 33_Re: Calling DSNTIAR in a SQL proc23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 19 Sep 2001 10:40:34 -0400592_ISO-8859-1 Rajendra,

If a stor proc during its execution, receives -ve SQLCODE and programmer wants to get additional information, DSNTIAR or anything similar must be executed in the proc itself before any other SQL. When the control comes back to the calling program, SQLCA is changed to reflect the result of CALL statement to the proc (which may very well be a '0'), not the SQL that failed in the stor proc. So invoking DSNTIAR in calling program will not give the information, I want. Without DSNTIAR or similar prog/proc , the called stor proc can return the -ve SQLCODE as [...] 13286 91 45_Re: Fast copy to prod debug including indexes15_Murley, Michael22_Michael_Murley@BMC.COM31_Wed, 19 Sep 2001 10:04:52 -0500312_iso-8859-1 Mike,

If you use BMC COPY PLUS for DB2* and BMC RECOVER PLUS for DB2*, you can copy and recover table spaces and indexes using RVA Snapshot with the data set snap feature. Use COPY PLUS to create Instant Snapshot copies and use the RECOVER PLUS INCOPY feature to perform instant restores. [...] 13378 16 45_Re: Fast copy to prod debug including indexes14_Donald A Smith18_dsmithj@US.IBM.COM31_Wed, 19 Sep 2001 11:22:01 -0400311_us-ascii I made a mistake in my last note regarding this situation, I meant to say that the production indices could be recreated with the COPY YES attribute and copied using SNAPSHOT the same way you plan on copying the tables. Sorry for my first note and any confusion it may have caused. sincerely, Don [...] 13395 23 57_Re: Migration V5 to V6 and 255 character support - thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 19 Sep 2001 17:26:29 +0200392_us-ascii Hi.

DSNTIJPM may be useful of course but unfortunatelyt it executes only the simple queries described in installation guide to detect the unsupported object in V6 and prints a report. It cannot detect other catalog inconsistencies or problems requiring PTFs + ACTION (duplicate DBD for instance) before migration.

Regards

Max Scarpa DB2 plumber/electrician [...] 13419 26 40_Non-matching index scan and OFFPOS leafs15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL31_Wed, 19 Sep 2001 17:36:46 +0200406_us-ascii Halo List,

I've just read that Oracle has so called Fast Full Index Scan which scans index in page order (not leaf order).

When I have an index with bad quality (a lot of pages OFFPOS possibly FAROFFPOS) but this index can give me index-only access and I'm not interested in order of result scanning whole indexspace could be faster (sequential) than following leaf page order. [...] 13446 35 28_Re: Database management tool13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Wed, 19 Sep 2001 10:38:25 -0500395_us-ascii I have used DBA-Xpert. It is an easy to use product. My mine use for it was to do complex migrations. I didn't use it for catalog queries because I had written my own in QMF to provide easy catalog information long before we acquired DBA-Xpert. One of DBA-Xpert's biggest advantages is that it is an all-in-one product and I knew our company wasn't going to buy multiple products. [...] 13482 31 37_Moving UDB (AIX) DB between instances24_Philip Nelson (Team DBA)21_teamdba@ATTGLOBAL.NET31_Wed, 19 Sep 2001 17:31:18 +0100429_us-ascii UDB V6.1 on AIX.

I want to move a database from db2inst2 to db2inst1 (same machine)

I know I can use db2move (which basically is export / db2look / import), but are there any easier methods.

I've tried -

Uncataloging on db2inst2; recataloging on db2inst1 : doesn't work

Backing up on db2inst2; restoring on db2inst1 : can't find the backup because it has the instance in the name [...] 13514 82 41_Re: Moving UDB (AIX) DB between instances18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 19 Sep 2001 13:23:44 -0400327_- I'm not sure what options you tried when recovering your backup to the other instance. If you use a redirected restore (documented in the admin guide), it will do the trick. We do this all the time to create a development database from the production backup. It should be an offline backup, it's the easiest to restore. [...] 13597 23 33_db2 pm v7 error message dgovo565e10_Tom Taylor17_ttaylor@CHUBB.COM31_Wed, 19 Sep 2001 13:44:28 -0400493_us-ascii Anyone

I'm recieving from db2pm v7

DGOV0565E DB2V DB2 MEMBER DB2V REPORTED AN UNEXPECTED CONDITION (RS 00E60816) CURRENT HISTORY SNAPSHOT DOES NOT CONTAIN ALL DATA FOR THIS MEMBER

can't find the message



Tom

================================================ 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. 13621 29 42_Nested table expression and TABLE function15_Saffer, Michele26_Michele.Saffer@ACS-INC.COM31_Wed, 19 Sep 2001 13:55:40 -0400569_iso-8859-1 I am trying to join two tables using a LEFT OUTER JOIN and a nested table expression, like the following:

SELECT TABLEA.COL1, TABLEA.COL2, ... BNTE.SUM1 FROM TABLEA LEFT OUTER JOIN TABLE (SELECT COL1, SUM(COL3) AS SUM1 FROM TABLEB WHERE TABLEB.COL1=TABLEA.COL1) AS BNTE ON TABLEA.COL1 = BNTE.COL1

There is an index on COL1 in both TABLEA and TABLEB. When I look at the explain, the accesspath is a tablespace scan of both tables. My question is, why isn't the index on COL1 in TABLEB being used to materialize the nested table expression? [...] 13651 209 23_FW: ACCESS PATH Problem11_Howe, Steve15_SHowe@BCBSM.COM31_Wed, 19 Sep 2001 14:31:33 -0400701_iso-8859-1 Here are the Explains as requested by Terry Purcell.

> SELECT C.PROCEDURE_CD, C.BCBSM_PROV_CD, C.PROV_SPECIALTY_CD, > C.CONTRACT_NUM,SUBSTR(C.PATIENT_FIRST_NAME,1,1) AS FIRSTINIT, > C.SEX_CD > FROM XDUD0000.XVUD0100_REG_CLMS AS C, > (SELECT DISTINCT A.CONTRACT_NUM, > SUBSTR(A.PATIENT_FIRST_NAME,1,1) AS FIRSTINIT, A.SEX_CD > FROM XDUD0000.XVUD0100_REG_CLMS AS A > WHERE A.FILE_SOURCE_CODE BETWEEN 'A' AND 'C' > AND A.YR_MTH BETWEEN '200101' AND '200107' > AND A.PROV_SPECIALTY_CD='47') AS AA > WHERE C.YR_MTH BETWEEN '200101' AND '200107' > AND C.CONTRACT_NUM = AA.CONTRACT_NUM > AND C.SEX_CD = AA.SEX_CD > AND SUBSTR(C.PATIENT_FIRST_NAME,1,1) = AA.FIRSTINIT > FOR FETCH ONLY; [...] 13861 51 22_Re: DB2 and Windows XP12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Sep 2001 13:30:18 -0500295_ISO-8859-1 I think you'll get a better response from people who spend more of their time on DB2 for Windows, but here are some pointers that may help. You may want to clarify a bit. Do you mean for the clients or for the servers? The servers are concentrating on the Windows 2000 line. See [...] 13913 26 43_Difference between EXISTS and IN predicates22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Tue, 18 Sep 2001 15:42:03 -0300341_iso-8859-1 Is there any difference in performance between an EXISTS and an equivalent IN predicate when used with a correlated subquery? DB2 manuals say both are non-indexable and stage 2. It seems to me that the EXISTS predicate would have better performance because it would not need to materialise all rows selected in the subquery. [...] 13940 30 58_Running processes as a result of DB2 triggers being fired.10_Mike Wiles14_mwiles@UCG.COM31_Wed, 19 Sep 2001 14:48:14 -0400438_iso-8859-1 Hi Listers!

I apologize if this is a basic question, or one that has been asked recently, or a million times before. I've been having trouble finding anything regarding my problem.

Basically, I would like to create a file somewhere, or kick off a process as a result of a DB2 trigger being fired. More specifically, if there is an insert to table X, I want to kick off process Y. I am running DB2 V6 on AIX. [...] 13971 53 38_Re: QMf V6 for OS/390-End of Support ?11_Mark Ediger22_MEDIGER@TRANSUNION.COM31_Wed, 19 Sep 2001 13:46:45 -0500385_US-ASCII Hi Susan,

If you go to the IBMLINK Sales Manual and enter the product number the first part of the product description will give the product life cycle dates. The link is http://www.ibmlink.ibm.com/ussman. You do not need an IBMLINK account to see this. Hope this helps

Mark Ediger



>>> Susan_C_Loria@TVRATINGS.COM 09/19/01 07:22AM >>> Group, [...] 14025 62 46_Re: Nested table expression and TABLE function11_David Nance16_DWNance@FHSC.COM31_Wed, 19 Sep 2001 15:07:43 -0400597_US-ASCII Michele, The reason for two tablespace scans is you are really running two separate queries. I imagine your explain probably shows two separate query blocks. The initial one being on your nested table expression with a sort for the group by and the second being on tablea for the main query. Without a more meaning ful WHERE clause I don't see much chance of this getting any better. Except maybe with this(it should give the same results and only give a TS scan on tablea): SELECT TABLEA.COL1, TABLEA.COL2, ... SUM(B.COL3) FROM TABLEA LEFT OUTER JOIN TABLEB ON TABLEA.COL1 = B.COL1 [...] 14088 28 19_Java & DB2 Question13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 19 Sep 2001 14:14:11 -0500503_iso-8859-1 Hi, Just thought I would try this list first with this Java/Db2 question.

Does anyone have any Java examples of setting the SQL_ATTR_INFO_USERID or the SQL_ATTR_INFO_WRKSTNNAME data. We want to put in an eye-catcher when displaying remote threads which are authenticated with the same DB2 userid under the covers.



Thanks Glenn Mackey GuideOne Insurance Data Base Administrator Phone: (515) 267 5767 Pager: (515) 241 1627 Mail Stop: AB1 mailto:gmackey@guidemail.com [...] 14117 28 19_Performance Monitor18_Manching, Fernando28_FManching@COOPERLIGHTING.COM31_Wed, 19 Sep 2001 15:51:50 -0400307_iso-8859-1 I am trying to monitor SQL statements on a UDB 5.2 using Performance Monitor.

If I add up the select sql statments + update/insert/delete sql statements + failed SQL + DDL statements + commits and compare it to the total of dynamic + static sql statements they don't match up at all. [...] 14146 29 25_-904 Resource Unavailable16_McDonnell, James23_JMcDonnell@PROMENIX.COM31_Wed, 19 Sep 2001 16:00:34 -0400430_- We have a test DB2 system (there is no production system yet). The active log archive process was not set up correctly when DB2 was installed. As a result, the active logs became full and they could not be offloaded to the archive files. To begin correcting the problem, I tried to stop DB2. Because this is also a logging event and the active logs were already full, DB2 hung. I forced DSN1MSTR to stop to bring DB2 down. [...] 14176 80 32_CA symbol substitute in JOB card20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM31_Wed, 19 Sep 2001 16:05:22 -0400427_ISO-8859-1 All,

I am just starting to use the CA/Platinum products - specifically Migrator. One requirement I have is to customize the JOB card. I want to do something like this and cannot find which manual to read to help me out. Anyone know?

//%USERID.%UTNAME JOB (9999-T-999T,9999,,80),'DB2-%SYSID-%UTNAME-%DATE' // CLASS=%CLASS,MSGCLASS=%MSGCLASS,NOTIFY=%USERID TYPRUN=HOLD //* SARDEVLID CA%SYSID%UTNAME [...] 14257 26 28_Re: benefit of compression ?12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Sep 2001 15:13:09 -0500434_- You will probably want to start with some comparisons that have been done before, such as those in the red books:

DB2 for OS/390 and Data Compression, SG24-5261

DB2 Version 7 Performance Topics, SG24-6129, chapter 10

There are substantial differences depending upon the compression ratio, the specific machine, SELECT versus INSERT, scan versus index access, and buffer pool choices and various utilities. [...] 14284 40 15_BMC Load Resume10_Chris Kern23_Chris_Kern@VANGUARD.COM31_Wed, 19 Sep 2001 16:19:11 -0400453_us-ascii Listers, I'm attempting to execute a BMC load resume utility and I'm having some trouble. I do not have much experience with BMC utilities.

When I load replace with the following card I do fine. However when I change REPLACE to RESUME I start having problems. It is as if the load utility doens't recognized the commands after RESUME. How does my load card need to be adjusted to perform the LOAD RESUME. Thanks for the assistance. [...] 14325 76 56_Tuning tablespace scan (inner table) in nested loop join12_Brian Picard26_brian_picard@ALTAVISTA.COM31_Wed, 19 Sep 2001 15:25:26 -0500646_- Hi, Here is a strange query that we have some difficulty in tuning in DB2 V6.0 /OS-390. SELECT DISTINCT 0000011944 , A.SETID , A.TREE_EFFDT , A.PROCESS_GROUP , A.CHARTFIELD , B.LOCATION_C , A.EFFDT_FROM , A.EFFDT_TO FROM PS_COMB_SEL_TMP A , PS_C_LOC_TBL B WHERE A.PROCESS_INSTANCE = 0000011944 AND A.SETID = 'NY' AND A.PROCESS_GROUP = 'PW_C1XO_Q' AND A.CHARTFIELD = 'LOCATION_C' AND B.SETID = 'NABOR' AND B.LOCATION_C BETWEEN A.RANGE_FROM_30 AND A.RANGE_TO_30 AND ( ( B.EFFDT <= ( SELECT MAX ( EFFDT ) FROM PS_C_LOC_TBL C WHERE C.SETID = B.SETID AND C.LOCATION_C = B.LOCATION_C AND C.EFFDT <= '2000-08-30' ) ) ) AND B.EFF_STATUS = 'A' AND [...] 14402 79 46_Re: Nested table expression and TABLE function13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Sep 2001 15:19:29 -0500494_us-ascii Michele,

The reason is because there is currently a bug with respect to correlated nested table expressions. The correlated where clause predicate WHERE TABLEB.COL1=TABLEA.COL1 is a stage 2 predicate, so the best you can hope for is a tablespace scan or non-matching index scan. It should be an indexable predicate.

The problem has been logged with IBM. Offline I can give you the PMR number if you would like to encourage IBM to raise the priority of this problem. [...] 14482 61 19_Re: BMC Load Resume13_Walter Davies26_wdavies@CO.EL-DORADO.CA.US31_Wed, 19 Sep 2001 13:43:25 -0700642_iso-8859-1 You need resume yes. replace does not require a yes.

Walter ----- Original Message ----- From: "Chris Kern" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, September 19, 2001 1:19 PM Subject: BMC Load Resume



> Listers, > I'm attempting to execute a BMC load resume utility and I'm having some > trouble. I do not have much experience with BMC utilities. > > When I load replace with the following card I do fine. However when I change > REPLACE to RESUME I start having problems. It is as if the load utility doens't > recognized the commands after RESUME. [...] 14544 75 46_Re: Nested table expression and TABLE function12_Susan Lawson22_lawson_susan@YAHOO.COM31_Wed, 19 Sep 2001 12:03:19 -0700340_us-ascii Michele,

TABLEB.COL1 - on the inside of the table expression will not be used for index access at this time - this unfortunately is currently a known problem with this feature.

Index access on a correlated nested table expression is not considered when there is an aggregation.

IBM is aware of the issue. [...] 14620 17 23_Re: Performance Monitor13_Debra Roberts16_DEBRA171@AOL.COM29_Wed, 19 Sep 2001 16:53:02 EDT451_ISO-8859-1 Hello- I am a technical recriter in the DC Metro area. I am currently trying to fill some UDB DBA positions at a client site in Virginia. Do you possibly know anyone looking?

Thank You- Debra

================================================ 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. 14638 64 28_Re: benefit of compression ?11_Bill Happel28_Bill.Happel@HARTFORDLIFE.COM31_Wed, 19 Sep 2001 16:10:24 -0500469_US-ASCII It depends how much wool you can pull over your client's eyes :--) To get a true comparison, you need to copy the data to a parallel test system and execute several different processes, several different times against the data in non-compressed mode, then in compressed mode.

Another thought is to find the Cost Justification for having only one partition compressed and 63 partitions non-compressed? What were the statistics behind that decision? [...] 14703 65 19_Re: BMC Load Resume10_Dale Smock20_Dale.Smock@BMSUS.COM31_Wed, 19 Sep 2001 15:56:58 -0500548_iso-8859-1 Instead of "REPLACE PRELOAD CONTINUE" specify "RESUME YES" to Load Resume and Rebuild the Indexes. It is also recommended to provide estimated rows with "ENUMROWS (,nnnn)" where "nnnn" are the estimated number of new rows being added.

An alternative is "RESUME YES INDEX UPDATE UNIQUECHECK NO" to Load Resume and update Index RIDS. This may be faster if loading a small percentage of new rows into a very large table, however, this option has restart implications and may require recovery for failures (like duplicate rows). [...] 14769 22 46_Production problem, can i retrieve data by RID0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 19 Sep 2001 16:23:59 -0500341_us-ascii Ran into a little problem in production, loaded a old image copy to a table (oops!, long story ...) which is now causing RI problems on 2 other tables. When we do a CHECK on the other tables we get the RID. If we force the tables from access check pending to access read only, is there any way we can retrieve the rows by RID? [...] 14792 45 58_Re: Production problem, can i retrieve data by RID - fixed0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 19 Sep 2001 16:37:24 -0500700_us-ascii Fixed problem with a ficticious RBA for recovery, tables check ok.









Rohn.Solecki@MTS.MB.CA@RYCI.COM> on 2001/09/19 04:23:59 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Production problem, can i retrieve data by RID



Ran into a little problem in production, loaded a old image copy to a table (oops!, long story ...) which is now causing RI problems on 2 other tables. When we do a CHECK on the other tables we get the RID. If we force the tables from access check pending to access read only, [...] 14838 64 50_Re: Production problem, can i retrieve data by RID0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Sep 2001 16:41:02 -0500432_us-ascii Rohn, Have you tried running the REPAIR utility with LOCATE to find the row by RID?

And, have you run the REPORT RECOVERY utility to see what possible recovery points you might have?

I hope this helps.

Thanks, Tim





Rohn.Solecki@ MTS.MB.CA To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Production problem, can i retrieve Discussion data by RID List [...] 14903 121 60_Re: Tuning tablespace scan (inner table) in nested loop join13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Sep 2001 16:51:42 -0500670_iso-8859-1 Brian,

The BETWEEN predicate involving columns rather than literals for the low and high values is stage 2, hence why you do not see matching index access.

So the predicate:

AND B.LOCATION_C BETWEEN A.RANGE_FROM_30 AND A.RANGE_TO_30

should be changed to

AND B.LOCATION_C >= A.RANGE_FROM_30 AND B.LOCATION_C <= A.RANGE_TO_30

In early V6, and V5 via APAR, DB2 was able to provide an index match on character columns of differing lengths for equals predicates only. But this changed somewhere in V6, and now you should get matching index access on >, >=, <, <= predicates. Depends on your maintenance level though. [...] 15025 55 29_Re: -904 Resource Unavailable12_Sue Janowitz22_sjanowitz@MEDIAONE.NET31_Wed, 19 Sep 2001 19:05:41 -0400310_iso-8859-1 Do you have any information on what resource is unavailable? In order to update the BSDS, did you stop any of the catalog tables? I suspect that one of them is the resource that is unavailable.

(I think if you had DB2 up as ACCESS MAINTENANCE that you would get a different error code.) [...] 15081 96 46_Re: Nested table expression and TABLE function15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 19 Sep 2001 19:19:11 -0400425_- I too had this problem. Since this nested table expression is new, I thought there may be a bug like outer joins had in V4 or V5.

Is this nest table expression is ANSI standard SQL?

Thanks..sibi

-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Wednesday, September 19, 2001 03:19 PM To: DB2-L@RYCI.COM Subject: Re: Nested table expression and TABLE function [...] 15178 67 47_Re: Difference between EXISTS and IN predicates13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Sep 2001 19:01:05 -0500412_iso-8859-1 Claudio,

What result did you get when you ran some tests to compare these?

I cannot recall when I last coded a correlated IN list subquery, for this very reason. My understanding is that DB2 will sort and remove duplicates for the IN list comparison for each execution of the subquery (ie. for every unique outer qualifying, even more if the cache cannot avoid repeat exectutions). [...] 15246 26 9_batch qmf19_Chilakamarri, Shyam36_Shyam.Chilakamarri@BLR.HPSGLOBAL.COM31_Thu, 20 Sep 2001 06:12:18 +0500614_- Hi all,

Does anyone have the syntax to run a BATCHQMF? Also could you kindly explain the syntax of the same if you have one

Thanks in advance for your help

Shyam Chilakamarri WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender [...] 15273 62 36_Re: CA symbol substitute in JOB card14_Basiaco, Lucio20_Lucio.Basiaco@CA.COM31_Thu, 20 Sep 2001 11:36:14 +1000377_iso-8859-1 Hi Lisa,

This is a function of the Batch Processor as RCM and all the other products submit jobs via batch processor.

The place to look is the Batch Processor Reference Guide which you can download from esupport.ca.com if your registered and you don't have it already.

Section : Model JCL/Symbolic Variables/User Defined Symbolic Variables [...] 15336 86 35_Re: error -805 in CICS / DB2 appln.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Wed, 19 Sep 2001 14:37:10 -0500347_ISO-8859-1 One thing that helps to remember is that the RCT links TRANID->PLAN, not PROGRAM->PLAN. A common error I see is TRN1 executes PROGRAM1 (which is in PLAN1) and TRN2 executes PROGRAM2 (which is in PLAN2), but then during the transaction PROGRAM1 XTCL's to PROGRAM2 (so now PROGRAM2 is executing under TRN1, and trying to use PLAN1). [...] 15423 25 16_Select statement8_K.Balaji19_K.Balaji@TARGET.COM31_Wed, 19 Sep 2001 21:21:38 -0500565_iso-8859-1 Hi all,

Can we get a column from a sub-query like...

Select A.Col1, A.Col2, B.Col3 From Table1 A Where exists (Select Col3 From Table2 B Where A.Col1 = B.Col1);

...in any version of DB2? I find this type of SQL very useful in many of my applications.

Thanks Balaji Ph # (510) 727-3259

================================================ 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. 15449 21 40_How to fetch sql code error description?10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 20 Sep 2001 10:01:26 +0530555_iso-8859-1 Hi All,

1.)How to catch the SQL code description for an sql error occured.(We know that sql code comes in the field sqlcode of sqlca) 2.)which system programme of DB2 fetches the sql code description and how to use this programme to get the sql code description?

Regards, Praveen Kumar Jain

===============================================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. 15471 49 44_Re: How to fetch sql code error description?18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 20 Sep 2001 11:17:30 +0530419_- Praveen,

I know of DSNTIAR which gives a formatted error message.

Syntax - Call 'DSNTIAR' using SQLCA err-msg err-txt-len where err-msg is a varchar group variable whose first half contains the length of the error msg area and the second half holds the error msg text. err-txt-len is the length of the text per line of msg displayed. Detailed info is available in Appln prog & SQL guide, page 196. [...] 15521 16 44_Rue V. Pham/IRM/DST/US is out of the office.0_21_RVPham@DSTSYSTEMS.COM31_Thu, 20 Sep 2001 01:04:18 -0500439_us-ascii I will be out of the office starting 09/18/2001 and will not return until 09/24/2001.

Please contact Todd Carmichael 435-4925 or Tracy Hutton while I'm away for immediate attentions

================================================ 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. 15538 15 77_Re: DB2-L Digest - 19 Sep 2001 to 20 Sep 2001 (#2001-267) (Out of the Office)11_Jerry Boehm25_GBOEHM@GW.DOT.STATE.NY.US31_Thu, 20 Sep 2001 02:11:25 -0400329_US-ASCII I am out of the office until Monday, October 1st, and will read your email then.

===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 15554 45 18_unknown sense code13_Ben ten Thije25_Ben.ten.Thije@MAIL.ING.NL31_Thu, 20 Sep 2001 08:15:48 +0200797_US-ASCII Hi;



During a (sna) connect from DB2 (v6)/TSO to DATAJOINER (v2)/AIX we receive this (unknown) sense-code on the tso log ;

DSNL500I ... RTNCD=00 FDBK2=0B RCPRI=005C RCSEC=0000 SENSE=07000000



Does anybody has a clue what this sense-code means ?



Thanks !

Ben ten Thije



----------------------------------------------------------------- ATTENTION: The information in this electronic mail message is private and confidential, and only intended for the addressee. Should you receive this message by mistake, you are hereby notified that any disclosure, reproduction, distribution or use of this message is strictly prohibited. Please inform the sender by reply transmission and delete the message without copying or [...] 15600 22 30_OLE dB : Access to legecy data13_manish raj kr26_krmanishraj@REDIFFMAIL.COM31_Thu, 20 Sep 2001 07:40:21 -0000505_- Hi Gurus,

I recently came to know that with the help od OLE DB(possibly from microsoft) it is possible to acceess legacy data from open system. I do not have any document regarding this. Any pointer will be highly appreciated.

TIA Kr.Manish.



===============================================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. 15623 50 29_Re: -904 Resource Unavailable14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 20 Sep 2001 03:13:37 -0500539_- Jim,

At the time of you crashed db2 there might have been an update in progress that affected two pages - with the update for one page externalised to disk and the other still in memory. What you have done might forever prevent DB2 getting back into sync.

Quite honestly what I would do now is drop the test system and rebuild it from scratch (well, not quite - you should be able to get all the DDL, data, DBRMs out of the cureent system). Unpleasant? Yes. But at least it will then be in a known situation. There is [...] 15674 28 62_How to select index for better performance in synch with space8_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 20 Sep 2001 08:39:35 +0000527_- Hello Listers. here is basic doubt i do have and can any body shed light to it. This is for index selection for tables. My doubts are in three phase which I m listing below. As per my knowledge we know that except clustering index and primary index we need to check how many times a column is referenced in sql statements. If the columns are more are referenced in more than three sql satements then we need to create a index on that whether unique or non unique. Here can any body clear my doubts. Second doubt is when [...] 15703 48 20_Re: Select statement18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 20 Sep 2001 14:18:28 +0530694_- Balaji,

No. From clause does not mention table2.

thanks, Rajendra.



>From: "K.Balaji" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Select statement >Date: Wed, 19 Sep 2001 21:21:38 -0500 > >Hi all, > >Can we get a column from a sub-query like... > >Select A.Col1, A.Col2, B.Col3 >From Table1 A >Where exists (Select Col3 From Table2 B Where A.Col1 = B.Col1); > >...in any version of DB2? I find this type of SQL very useful in many of my >applications. > >Thanks >Balaji >Ph # (510) 727-3259 > >================================================ >To change your subscription options or to cancel [...] 15752 226 20_Re: Select statement0_24_Bruce.Williamson@PSIR.IE31_Thu, 20 Sep 2001 09:41:33 +0100640_iso-8859-1 Howzit Balaji?

Instead of coding a sub-query, join the tables. This will give you the same result:

SELECT A.COL1, A.COL2, B.COL3 FROM TABLE1 A INNER JOIN TABLE2 B ON A.COL1 = B.COL1;

Cheers Bruce

__________________________________________________

Bruce Williamson DB2 Database Administrator

perotsystems information resource(tm)

TSG DBA Computer Centre Cabinteely Dublin 18 Ireland

Bruce.Williamson@psir.ie



-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: 20 September 2001 03:22 To: DB2-L@RYCI.COM Subject: Select statement [...] 15979 226 44_Re: How to fetch sql code error description?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 20 Sep 2001 10:06:35 +0100429_iso-8859-1 I love it when even simple questions have a sting in the tail.

Some time ago, I discovered that the SQLCODES and all the other information that DSNTIAR provides is actually held in the DSNTIAR module itself.

So, if you STATICALLY link DSNTIAR into your program, you have a fixed set of error messages. If, a few years down the road, you hit a 'new' SQLCODE, DSNTIAR will not know anything about it. [...] 16206 51 44_Re: How to fetch sql code error description?15_Toine Michielse18_vndobtm@US.IBM.COM31_Thu, 20 Sep 2001 11:08:39 +0200523_us-ascii Hello,

Call dsntiar. You can find the parameters to pass to DSNTIAR in the manuals.

Regards,

Toine Michielse DB2 S/390 Advocate Silicon Valley Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: How to fetch sql code error description? [...] 16258 126 36_UK DB2 GUIDE and user group meetings14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 20 Sep 2001 10:15:12 +0100314_iso-8859-1 If you are NOT based in the UK, then please delete this message NOW.

OK, if you are still with me, let me introduce myself.

As you can see below, I work for Computer Associates as a Product Manager for their range of (formerly PLATINUM technology) tools for DB2 on OS/390 (and z/OS). [...] 16385 67 66_Re: How to select index for better performance in synch with space18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 20 Sep 2001 14:58:01 +0530424_- Let me endevor to answer your queries....btw, do not count this as an expert's view....:)

Well, why do we need indexes at all. The answer is - to reduce DB I/O and hence improve performance.

If you are going to frequently access a set of columns then it certainly pays to have an index built on those columns. Index on primary key columns have to be Unique. Pay attention to how you use the predicates. [...] 16453 37 31_Accessing Stored Procedure Name19_Thorsten Tovacovsky33_thorsten.tovacovsky@KARLSRUHER.DE31_Thu, 20 Sep 2001 04:17:01 -0500382_- Hello List,

how can I get the name of the DB2-stored-procedure ?

Explanation:

DB2-WLM (DSNX9WLM) -> LE (CEEPIPI) -> STP (PLSYxxxx) -> Load PLSYzzzz.

How can PLSYzzzz(assembler), which was dynamic loaded, get the NAME of the stored procedure (STP: PLSYxxxx) ?

Some one at IBM mentioned that the enclave-name ist the same as the stored-proc. [...] 16491 43 31_Accessing Stored Procedure Name19_Thorsten Tovacovsky33_thorsten.tovacovsky@KARLSRUHER.DE31_Thu, 20 Sep 2001 04:17:01 -0500391_iso-8859-1 Hello List,

how can I get the name of the DB2-stored-procedure ?

Explanation:

DB2-WLM (DSNX9WLM) -> LE (CEEPIPI) -> STP (PLSYxxxx) -> Load PLSYzzzz.

How can PLSYzzzz(assembler), which was dynamic loaded, get the NAME of the stored procedure (STP: PLSYxxxx) ?

Some one at IBM mentioned that the enclave-name ist the same as the stored-proc. [...] 16535 68 47_Re: Difference between EXISTS and IN predicates22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Wed, 19 Sep 2001 08:18:34 -0300500_iso-8859-1 Terry,

> What result did you get when you ran some tests to compare these?

I did not make any test to compare EXISTS and IN predicates, I've just searched DB2 manuals and books looking for the difference. I'm giving an SQL course in my company and one of my students asked me about the difference in performance. He said that Oracle would perform an EXISTS predicate faster than an IN predicate because it does not need to materialise the results of the internal query. [...] 16604 94 46_Re: Nested table expression and TABLE function15_Saffer, Michele26_Michele.Saffer@ACS-INC.COM31_Thu, 20 Sep 2001 08:20:22 -0400556_iso-8859-1 I thought about that, but the real query is, naturally, much more complex than my example, with multiple LEFT OUTER JOINs. This SQL is being used in an unload for a data warehouse application. I'm afraid my best advice to the applcation programmer is to put it in a program, and select what they need from TABLEA and then go get the data they need from the other tables, including TABLEB. Another reason I think this is a good idea is that they can avoid looking at many of the other tables that they are currently joining to by evaluating [...] 16699 15 14_need load jcl.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 20 Sep 2001 07:28:42 -0500406_- Hi friends,

I need load jcl for IBM utility DSNUTILB to be run on OS/390 DB2 ver 6.1. Is there any other utility from renowned software vendor.

TIA.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16715 145 46_Re: Nested table expression and TABLE function16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Thu, 20 Sep 2001 13:39:00 +0100391_iso-8859-1 Michelle,

My compnay may actually have a product that does EXACTLY what you want of the shelf. It handles unloads and reloads, allowing you to specify how the data is selected - ie, you dont have to unload a complet table, and it can unload the referential parts of child (or parent!) tables. So it does all the out joins for you, but as separate dynamic SQL queries. [...] 16861 107 50_Re: need separate DB2 subsystem for Data Warehouse10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 20 Sep 2001 06:34:14 -0600421_iso-8859-1 Ed, Thank you for your reply. Do we need separate IRLM for this separate DB2 subsystem? How do you handle TSO session since DSNEXIT is specified in the TSO logon procedure?

Grace

-----Original Message----- From: Edward(Ed) J. Finnell,III [mailto:efinnell@SEEBECK.UA.EDU] Sent: Monday, September 17, 2001 3:23 PM To: DB2-L@RYCI.COM Subject: Re: need separate DB2 subsystem for Data Warehouse [...] 16969 112 46_Re: Nested table expression and TABLE function15_Saffer, Michele26_Michele.Saffer@ACS-INC.COM31_Thu, 20 Sep 2001 08:36:43 -0400420_iso-8859-1 I just thought of another question (well, actually two). You are right, there are two query blocks, one for the nested table expression and another with a join of TABLEA and the result set of the NTE. First of all, is the result set of the NTE only those rows where COL1 is the same for both tables? Second, is the tablespace scan of TABLEB occurring one time or is it happening for every row of TABLEA? [...] 17082 82 35_Re: Accessing Stored Procedure Name0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Thu, 20 Sep 2001 07:46:59 -0500346_us-ascii I'm not sure if this is what your asking. If you want the name of the DB2 stored procedure, go to the DB2 Catalog table Sysibm.Sysroutines (if you're v6) and do a find of the external_name that you know and it will show you the stored procedure name.

hth Shan

Disclaimer; All thoughts are mine and not of my company . [...] 17165 128 13_Re: batch qmf13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 20 Sep 2001 07:55:31 -0500330_us-ascii Shyman,

Here is one I wrote to document indexes for the programmers. The DQSPRINT can also go to SYSOUT=A to print it, but here I am printing it to a file. The input must be a proc name (owner.proc). It cannot be a query. APPLID here is a parm in the proc. In the parm list in the jcl, it must start with &&. [...] 17294 16 59_Derez D. Lusk/Household International is out of the office.13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Thu, 20 Sep 2001 08:05:26 -0500440_us-ascii I will be out of the office starting 09/20/2001 and will not return until 09/24/2001.

any issues... please call 847-824-2871 and leave detailed message or via HOUSEMAIL - page EMPZ59

================================================ 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. 17311 109 19_Re: BMC Load Resume0_19_csutfin@AMSOUTH.COM31_Thu, 20 Sep 2001 08:33:43 -0500458_us-ascii You probably want to remove the REDEFINE YES or change it to REDEFINE NO. I don't remember if it will be ignored for the RESUME YES.

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









Dale Smock cc: (bcc: Carol Sutfin/BIRMINGHAM/AL/AMSOUTH BANK) Sent by: DB2 Subject: Re: BMC Load Resume Data Base Discussion List [...] 17421 20 35_Re: Accessing Stored Procedure Name19_Thorsten Tovacovsky33_thorsten.Tovacovsky@KARLSRUHER.DE31_Thu, 20 Sep 2001 08:49:08 -0500499_ISO-8859-1 Sorry,

the module which need the STP-Name dont have any informations who is the caller...it is also not sure that the module is called directly from the STP...it´s possible that a dynamic loaded programm call my routine...

...Thorsten

===============================================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. 17442 81 18_Re: need load jcl.17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT31_Thu, 20 Sep 2001 15:51:39 +0100697_us-ascii I hope it's enough

//LOADMU1 EXEC PGM=DSNUTILB, // PARM='D21Z' //STEPLIB DD DSN=YCSDB2.V610.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1 DD DSN=&&TEM,DISP=(,DELETE,DELETE), // SPACE=(CYL,(10,10)),UNIT=SYSDA //SORTIN DD DSN=&&SIN,DISP=(,DELETE,DELETE), // SPACE=(CYL,(20,10)),UNIT=SYSDA //SORTOUT DD DSN=&&SOU,DISP=(,DELETE,DELETE), // SPACE=(CYL,(20,10)),UNIT=SYSDA //SYSMAP DD DSN=&&MAP,DISP=(,DELETE,DELETE), // SPACE=(CYL,(20,10)),UNIT=SYSDA //SYSERR DD DSN=&&ERR,DISP=(,DELETE,DELETE), // SPACE=(CYL,(20,10)),UNIT=SYSDA //* //SYSREC00 DD DUMMY //SYSIN DD * LOAD DATA LOG YES INDDN SYSREC00 REPLACE INTO TABLE owner.tbname [...] 17524 19 20_Re: Stuck with a SQL14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 20 Sep 2001 09:52:04 -0400481_iso-8859-1 Hi,

Thanks to all for your solutions and suggestions. I came back to work a couple of days back and it took a while to go thru a ton of e-mails. As you can probably guess, I work in lower Manhattan.

Thanks, Abhijit

================================================ 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. 17544 18 37_Boston MA DB2 Systems position wanted12_Sue Janowitz22_sjanowitz@MEDIAONE.NET31_Thu, 20 Sep 2001 09:53:42 -0400519_iso-8859-1 I am currently looking for a job in the Boston, Massachusetts (USA) area as a mainframe DB2 systems programmer (with other mainframe systems and programming experience). If you know of anything, please contact me off-list at SJanowitz@mediaone.net.

Thank you.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17563 143 50_Re: need separate DB2 subsystem for Data Warehouse50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Thu, 20 Sep 2001 09:56:43 -0400549_iso-8859-1

Separate IRLM's Have them use a separate TSO logon proc to point to the subsystem libraries for your warehouse DB2.





-----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Thursday, September 20, 2001 8:34 AM To: DB2-L@RYCI.COM Subject: Re: need separate DB2 subsystem for Data Warehouse



Ed, Thank you for your reply. Do we need separate IRLM for this separate DB2 subsystem? How do you handle TSO session since DSNEXIT is specified in the TSO logon procedure? [...] 17707 17 29_Re: -904 Resource Unavailable13_Jim McDonnell23_jmcdonnell@PROMENIX.COM31_Thu, 20 Sep 2001 08:54:45 -0500480_- Sue -

On the 904 error, the reason code I am getting is C200B8, the resource type is 300 and the resource name is DSNDB04 .BMSORDER.X'00000002'. DSNDB04 is the database and the table I am trying to update is BMSORDERS.

Jim

================================================ 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. 17725 82 50_Re: need separate DB2 subsystem for Data Warehouse0_19_Tim.Lowe@STPAUL.COM31_Thu, 20 Sep 2001 09:01:54 -0500606_us-ascii Grace, If at all possible, I would not separate these subsystems. I have seen 2 different companies create separate subsystems for this type of workload, only to wish they could put them back together again. In one case, the data warehouse was eventually used in an operational system, driving the merger. In another case, the 2 different subsystems caused more problems getting data back than the split originally solved, but it became impossible to merge them. And, DB2 has made so many improvements over the years to allow varied workloads to run on the same system. Before I would create [...] 17808 103 18_Re: need load jcl.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 20 Sep 2001 19:36:58 +0530976_- thanks massimo. I guess thats ur first name. Sorry if I got it wrong.

Rajendra.





>From: Massimo Biancucci >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: need load jcl. >Date: Thu, 20 Sep 2001 15:51:39 +0100 > >I hope it's enough > >//LOADMU1 EXEC PGM=DSNUTILB, >// PARM='D21Z' >//STEPLIB DD DSN=YCSDB2.V610.SDSNLOAD,DISP=SHR >//SYSPRINT DD SYSOUT=* >//UTPRINT DD SYSOUT=* >//SYSUDUMP DD SYSOUT=* >//SYSUT1 DD DSN=&&TEM,DISP=(,DELETE,DELETE), >// SPACE=(CYL,(10,10)),UNIT=SYSDA >//SORTIN DD DSN=&&SIN,DISP=(,DELETE,DELETE), >// SPACE=(CYL,(20,10)),UNIT=SYSDA >//SORTOUT DD DSN=&&SOU,DISP=(,DELETE,DELETE), >// SPACE=(CYL,(20,10)),UNIT=SYSDA >//SYSMAP DD DSN=&&MAP,DISP=(,DELETE,DELETE), >// SPACE=(CYL,(20,10)),UNIT=SYSDA >//SYSERR DD DSN=&&ERR,DISP=(,DELETE,DELETE), >// SPACE=(CYL,(20,10)),UNIT=SYSDA >//* >//SYSREC00 DD DUMMY >//SYSIN DD * > LOAD DATA LOG YES [...] 17912 21 25_DB2 & DBCS usage question12_Fred Collins20_fred.collins@EDS.COM31_Thu, 20 Sep 2001 09:00:21 -0500563_- Currently have MIXED=NO as parameter in DSNHDECP data-only module for DB2 subsystem. Now Application DBA has a need to be able to retrieve Arabic & Kanji characters. I know I have to change the MIXED=NO parm to MIXED=YES. Page 488 of the DB2 Installation Guide (V6) states that I must "specify a double-byte" CCSID from one of the CCSIDs listed in Table 115 for EBCDIC data." (We are using enscheme of EBCDIC.) Am very confused on which one to pick and will the selection of this CCSID have any ramifications on the data in the existing tables in this DB2 [...] 17934 17 41_Re: Moving UDB (AIX) DB between instances13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Thu, 20 Sep 2001 09:07:08 -0500428_- Hi Phil I don't know if you are in the market for a product to provide this migration capability but Quest Central for DB2's database administration module will allow you to simply DRAG/DROP the database from one instance to another and automatically generate all the necessary scripting to accomplish the task. If you want to take a look at the capability you can download a free 30-day trial copy at WWW.QUEST.COM/DB2. [...] 17952 13 50_Re: Production problem, can i retrieve data by RID15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 20 Sep 2001 09:16:11 -0500376_- Rohn

Did you try a CHECK with the DELETE-option. Then you are able to store all row violating the RI into an exception 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. 17966 25 46_Running DB2 Scripts from a 3rd Party Scheduler0_25_DBird@KEMPERINSURANCE.COM31_Thu, 20 Sep 2001 09:05:20 -0500331_us-ascii I am trying run Windows NT DB2 command Scripts from a 3rd Party Scheduler ESPX Client. This tool allows us to schedule jobs on the mainframe to create files that are loaded into tables located on windows NT servers.

Is there a way to run DB2 Command Scripts outside the DB2 Command Window and the UDB Journal. [...] 17992 46 29_Re: -904 Resource Unavailable12_Sue Janowitz22_sjanowitz@MEDIAONE.NET31_Thu, 20 Sep 2001 10:43:04 -0400602_iso-8859-1 The reason code and error message say that there is an internal error, not able to update SYSLGRNG. And it's pointing to the table you are trying to update. I wonder if the table/tablespace could have an indicator (possibly internal and invisible) saying that logging needs to be done.

Before trying James Campbell's suggestion of dropping and rebuilding the test subsystem, you may want to try deleting the table/tablespace and recreating it. Or, do you by any chance have an image copy from prior to this time to recover to? (Point-in-time recovery since you don't have logs.) [...] 18039 66 50_Re: Running DB2 Scripts from a 3rd Party Scheduler16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Thu, 20 Sep 2001 15:52:19 +0100406_us-ascii I agree. This is a very real and major failing of DB2 on NT. There are ways in which you can do this, but as you say, its is difficult.

I managed to do it by having a batch procedure run the commands in the db2 command window by executing db2cmd, which calls db2cmd.bat, whcih I made call a second batch file of my own with SQL command in. To say that its a mess is putting to simply. [...] 18106 33 19_Re: OLE DB Provider11_Anne Fanous25_anne.fanous@CONSIGNIA.COM31_Thu, 20 Sep 2001 16:05:52 +0000543_us-ascii Hi Manesh, I believe OLE DB provider comes with Microsoft's Host Integration Server. A colleague of mine found some information on OLE DB on MS TechNet August 2001 (CD version). This provided us with details of how to configure the connection to DB2 and some background information on the product. It was pretty straightforward to set up (you will also need to configure DB2 for remote communications if you have not already done so). If you would like me to email you a copy of this information let me know. Regards Anne Fanous [...] 18140 12 29_Re: -904 Resource Unavailable10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 20 Sep 2001 17:12:12 +0200315_us-ascii I've found this error rebuilding our test DB2. I I remember well

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 18153 43 32_Re: Buffer Pool Warning Messages12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 20 Sep 2001 10:48:50 -0500345_iso-8859-1 I've been gone for a week and just got back to the office, I would second this recommendation from Roger. This is a great resource for storage management information. John also did a presentation on this subject at the DB2 Technical Conference last year.

Tom Moulder (BMC Software, Inc.) my opinions, not my employers ... [...] 18197 156 21_Re: Buffer pool usage12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 20 Sep 2001 11:20:39 -0500573_iso-8859-1 Joel

I'm not against driving the reclaim manager. I'll use other means to drive requirements for how the reclaim manager might be made smarter.

I'm not saying that at times, depending upon many circumstances, there may not be I/O spikes as a result of pool changes. If you want to make these changes to improve DB2 performance then you have to be smart about how and when you do it and not simply do it on a whim. What I am saying is that there are DB2 shops that are making these changes and experiencing improved performance without the I/O [...] 18354 146 46_Re: Nested table expression and TABLE function11_David Nance16_DWNance@FHSC.COM31_Thu, 20 Sep 2001 12:38:28 -0400327_US-ASCII First question, Yes. According to your original query it would only be the rows that met your where criteria. Second the TS scan on B only happened the one time, but now you are doing a TS scan on the materialized result set that you retrieved from B.

Dave Nance First Health Services, Corp. (804)527-6841 [...] 18501 55 19_Re: OLE DB Provider13_Cobbaert Marc19_marc.cobbaert@SD.BE31_Thu, 20 Sep 2001 18:43:59 +0100706_ISO-8859-1 There is also an OLE DB provider available from IBM which doesn't rely on Microsoft's Host Integration Server, but which uses the standard DB2 connect.

-----Oorspronkelijk bericht----- Van: Anne Fanous [mailto:anne.fanous@CONSIGNIA.COM] Verzonden: donderdag 20 september 2001 17:06 Aan: DB2-L@RYCI.COM Onderwerp: Re: OLE DB Provider



Hi Manesh, I believe OLE DB provider comes with Microsoft's Host Integration Server. A colleague of mine found some information on OLE DB on MS TechNet August 2001 (CD version). This provided us with details of how to configure the connection to DB2 and some background information on the product. It was pretty straightforward to set [...] 18557 52 9_IDMS/DB-212_Marc Sennett23_msennett@TEKSYSTEMS.COM31_Thu, 20 Sep 2001 14:37:44 -0500567_us-ascii All,

If anybody in Ohio or surrounding states is considering a career change.. A fortune 500 company in Greater Cincinnati has a need for a DB2 with some IDMS experience to handle various performance issues and system optimization. The data will eventually move to Oracle databases. The client will then provide training on Oracle. This is a fantastic opportunity to acquire new skills. Please, no third party recruiters or agencies. For more information, please e-mail me at msennett@teksystems.com. You can also call toll free at 877-854-1159. [...] 18610 45 33_Re: More SQL Procedures Questions13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 20 Sep 2001 13:49:33 -0500465_iso-8859-1 Mahadevan,

Try creating a DNS entry which contains all the IP addresses of your MVS images. Point your clients at this entry. It will round-robin balance your workload. You can add and remove IP addresses from this entry if an MVS is going to be IPLed or if one crashes. The adding and removing can be automated by in-house front ends, monitoring, etc. We tried the WLM route and could not get any intelligent balancing, it was unacceptable. [...] 18656 20 15_DB2 Column type14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Thu, 20 Sep 2001 14:13:43 -0500345_iso-8859-1 In SQL Server there is a column type [text] that will hold the format of that data. This is sometimes used for data like emails or paragraph data. When this column of [text] can be brought back and it will have the same carriage control and line feeds that the original input had. This data can be updated and then stored again. [...] 18677 102 28_Re: benefit of compression ?12_Hend Dwiyono16_hdwi@HOTMAIL.COM31_Thu, 20 Sep 2001 13:20:25 -0600451_- Jeff, you're right. I should have written "more space for more rows in the buffer pool". Our team is still trying to weigh in the additional CPU cycle to uncompress rows from buffer pool vs. the benefit of fewer getpage/ I/Os to get compressed rows into the buffer pool. Would fewer getpage have any effect on CPU cycles ? I thought it's only affecting I/Os. And we already know that the one compresed partition gives around 54% space savings. [...] 18780 162 13_Re: batch qmf19_Chilakamarri, Shyam36_Shyam.Chilakamarri@BLR.HPSGLOBAL.COM31_Fri, 21 Sep 2001 00:44:58 +0500571_iso-8859-1 Thanks Helen

That was helpful

Shyam Chilakamarri

> -----Original Message----- > From: Helen Johnson [SMTP:helen_johnson@RAC.RAY.COM] > Sent: Thursday, September 20, 2001 6:26 PM > To: DB2-L@RYCI.COM > Subject: Re: batch qmf > > Shyman, > > Here is one I wrote to document indexes for the programmers. The DQSPRINT > can also go to SYSOUT=A to print it, but here I am printing it to a file. > The input must be a proc name > (owner.proc). It cannot be a query. APPLID here is a parm in the proc. > In the parm list in the jcl, it must [...] 18943 24 32_Comparison of Oracle and DB2 SQL0_26_Steve.Westfall@EQUIFAX.COM31_Thu, 20 Sep 2001 14:45:35 -0500384_us-ascii We have some new users of DB2 (UDB EEE 7.2 on AIX) who are familiar with Oracle (8i) SQL. Is there anything reasonably up to date they can read, preferably on the web, that might give them an overview of differences between SQL syntax in the two different dbms's? I didn't run across anything in the list archives. The only thing I found on IBM's web site was outdated. [...] 18968 59 28_Re: benefit of compression ?11_Jose Moreno30_jose.moreno@FR.CDBSOFTWARE.COM31_Thu, 20 Sep 2001 21:47:35 +0200554_iso-8859-1 You'll have also to evaluate the benefit/cost of compression on Utilities. Compressed TS are faster to COPY, RECOVER and REORG (w/ KEEPDICTIONARY of course) but slower to LOAD. You also have less Bufferpool usage, so you also save CPU cycles in DB2 in managing BPs, the same applies to the Logging. I have always seen benefits in compression. However the risk you have is to get more Timeouts/Deadlocks, because more rows are placed in a single page unless U use row locking ... but then there is CPU overhead on DBM1. Nothing is simple. [...] 19028 53 36_Re: Comparison of Oracle and DB2 SQL18_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM31_Thu, 20 Sep 2001 16:00:16 -0400818_us-ascii Not exactly what you may be looking for, but I have found this webpage to be helpful



http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM





Rich.







Steve.Westfall@EQUIFAX.COM@RYCI.COM> on 09/20/2001 03:45:35 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: (bcc: Richard McCutcheon/HCM/AM/HONDA) Subject: Comparison of Oracle and DB2 SQL



We have some new users of DB2 (UDB EEE 7.2 on AIX) who are familiar with Oracle (8i) SQL. Is there anything reasonably up to date they can read, preferably on the web, that might give them an overview of differences between SQL syntax in the [...] 19082 86 21_Re: Buffer pool usage14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 20 Sep 2001 16:17:21 -0400588_ISO-8859-1 Hi Tom, Perhaps I'm missing something here. I don't believe we have any control or impact on how the reclaim manager is doing anything. Perhaps having the vdwqt set low or to zero when avg pages per write is consistently low may reduce it's impact.

Regarding the effect of pool size changes on I/O we have an opposing experience and philosphy. First, anytime you change a pool size you will have an I/O spike, based on observed performance at many sites.... the fact that some short term subsequent performance (after the world is re-read) may be better is not the [...] 19169 15 18_Scrollable Cursors12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 20 Sep 2001 15:24:00 -0400494_- We are writing WEB apps. using Microsoft's ADO. Is there a way to do client side scrollable cursors using DB2 connect to access DB2 OS390 V6.1 data? What are the components, software and versions, that it will take to do it?

Thanks in advance.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19185 32 36_Re: Comparison of Oracle and DB2 SQL12_Roy Reynolds15_royr@INCYTE.COM31_Thu, 20 Sep 2001 13:38:42 -0700630_us-ascii Have a look at http://www-4.ibm.com/software/data/pubs/papers/orac91vsdb272/orac91vsdb272.pdf It is fairly recent.

Steve.Westfall@EQUIFAX.COM wrote:

> We have some new users of DB2 (UDB EEE 7.2 on AIX) who are familiar with > Oracle (8i) SQL. Is there anything reasonably up to date they can read, > preferably on the web, that might give them an overview of differences > between SQL syntax in the two different dbms's? I didn't run across > anything in the list archives. The only thing I found on IBM's web site > was outdated. > > Thanks. > > Steve Westfall > Equifax, Inc. > Lombard, Illinois USA [...] 19218 52 63_Re: Is the DB2 Technical Conference in Orlando going to happen?14_Gary Bernhardt27_garybernhardt@EARTHLINK.NET31_Thu, 20 Sep 2001 15:49:30 -0500488_Windows-1252 Is there anyone out there that was planning to go, but have since cancelled their plans?

I have heard of other recent conferences that eventually were cancelled because of the volume of participants canceling.



----- Original Message ----- From: "Roger Miller" Newsgroups: bit.listserv.db2-l To: Sent: Monday, September 17, 2001 5:29 PM Subject: Re: Is the DB2 Technical Conference in Orlando going to happen? [...] 19271 38 22_Insert with Sub-select12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 20 Sep 2001 17:21:59 -0500447_us-ascii Hello,

I'm trying to load a 4 column "blah-2" table with

INSERT blah2 select 0, col_b, col_c, col_d from blah1 where blah-blah;

The problem is, the first column in blah2 is an integer IDENTITY column. Table blah1 has no IDENTITY columns.

In the first position, I've tried 0, NULL and DEFAULT but to no avail. I then tried 0 again, with an OVERRIDING USER VALUE clause prior to the select. Again, failure. [...] 19310 37 38_Re: QMf V6 for OS/390-End of Support ?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 20 Sep 2001 17:20:17 -0500326_ISO-8859-1 In my words, rather than the more accurate ones, we are putting QMF V7 into the box with both DB2 V6 and with V7. My understanding of the reams of pricing is that V6 and V7 are the same in the US, with variation for the world like currency and other factors. (Software Announcement 201-256 September 11, 2001) [...] 19348 19 28_Re: Database management tool13_Judy Brownlow18_judysaxe@M3NET.NET31_Thu, 20 Sep 2001 17:21:27 -0500363_- If you are seeking a tool for data migration or manipulation, then SoftBase Systems has one called Data Generation Facility (DGF).

It will maintain ref integrity, and has reuseable routines. Also can be used for changing data to comply with HIPAA and other govt regs where certain info may be confidential and need masking, etc. Lots of good stuff. [...] 19368 80 26_Re: Insert with Sub-select0_19_Tim.Lowe@STPAUL.COM31_Thu, 20 Sep 2001 17:33:22 -0500430_us-ascii Steve, Why not leave out the column and let DB2 assign a value to the identity column? For example: INSERT blah2 (col_b, col_c, col_d) select col_b, col_c, col_d from blah1 where blah-blah;

I hope this helps.

Thanks, Tim





Steve Grimes cc: Sent by: DB2 Data Base Subject: Insert with Sub-select Discussion List [...] 19449 42 26_Re: Insert with Sub-select16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Thu, 20 Sep 2001 23:37:56 +0100398_us-ascii Hi,

In order to insert into Blah1 with an indentity column, or indeed, any other generated always colum, you must not specify the column on the insert statement. So, instead of:

> INSERT blah2 > select 0, col_b, col_c, col_d > from blah1 > where blah-blah;

Try:

INSERT INTO BLAH2(col_b, col_c, col_d) SELECT col_b, col_c, col_d from BLAH1 where blah-blah; [...] 19492 42 28_Re: Database management tool13_Judy Brownlow18_judysaxe@M3NET.NET31_Thu, 20 Sep 2001 17:21:27 -0500372_iso-8859-1 If you are seeking a tool for data migration or manipulation, then SoftBase Systems has one called Data Generation Facility (DGF).

It will maintain ref integrity, and has reuseable routines. Also can be used for changing data to comply with HIPAA and other govt regs where certain info may be confidential and need masking, etc. Lots of good stuff. [...] 19535 38 19_Re: DB2 Column type15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 20 Sep 2001 19:35:19 -0400349_iso-8859-1 I think in DB2 you use CLOB data type in V6 for this. I never used it, but it can used in the same way as text data type in SQLserver.

Thanks..sibi



-----Original Message----- From: Zobjeck, A. J. [mailto:Al_Zobjeck@TTX.COM] Sent: Thursday, September 20, 2001 2:14 PM To: DB2-L@RYCI.COM Subject: DB2 Column type [...] 19574 96 66_Re: How to select index for better performance in synch with space8_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 20 Sep 2001 23:54:51 +0000351_- Hello Rajendra , I was waiting for experts to pitch in.I m still waiting for that. My doubts remain unanswered. Again I m requesting experts like Susan, Terry, Issac,David, DAVIS,TROD and all others to pitch in. Actually it is very difficult to write all name on mail list but I m requesting them as all.Eagerly waiting Thanks in advance. Duam [...] 19671 44 22_Re: Scrollable Cursors12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Fri, 21 Sep 2001 09:51:21 +1000291_- Bob, scrollable cursors are in V7.1 (refer to SQL Reference guide for details. Manuals can be downloaded from the IBM web-site). Otherwise, at least with OS/390, you are forced to a programatic solution, I can think of a couple of ways of tackling it, but it all involves extra code. [...] 19716 34 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Thu, 20 Sep 2001 19:12:02 -0500413_Windows-1252 Gary:

I'll be in Orlando and then go on to Florence. I am looking forward to each event.

Gerald Hodge HLS Technologies, Inc.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Gary Bernhardt Sent: Thursday, September 20, 2001 3:50 PM To: DB2-L@RYCI.COM Subject: Re: Is the DB2 Technical Conference in Orlando going to happen? [...] 19751 58 28_Difference in statement cost10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 21 Sep 2001 10:56:11 +1000436_us-ascii Hi,

Is there any diff. between coding a predicate as "A.COL = B.COL" and coding it as "B.COL = A.COL". Logic tells me there shdn't be. But.....

I have the foll. query, whose access path is fairly satisfactory to me. While testing, I somehow happened to change the last three predicates to read D.colnames = B.colnames instead of B.colnames = D.colnames. The accesspath remains EXACTLY the same, as expected. [...] 19810 54 30_Left Outer Join and null value11_Mike Dupuis23_Mike.Dupuis@CLARICA.COM31_Thu, 20 Sep 2001 22:05:10 -0500318_us-ascii I have a Customer with a problem using left outer join. When the following query is run in SPUFI with hard coded values for the host variables the desired results are returned. When the query is run in PL1 batch (using cursor select) with host variables and a null indicator variable the results differ. [...] 19865 49 30_Left Outer Join and null value11_Mike Dupuis23_Mike.Dupuis@CLARICA.COM31_Thu, 20 Sep 2001 21:56:47 -0500550_us-ascii I have a Customer with a problem using left outer join. When the following query is run in SPUFI with hard coded values for the host variables the desired results are returned. When the query is run in PL1 batch (using cursor select) with host variables and a null indicator variable the results differ. The process is open the cursor, get a row (1st row returned) and close the cursor. The cause seems to be once the fetch returns a row with a null from the left outer join table ( CX@ADDR C ) then the subsequent fetch (after closing [...] 19915 144 46_Re: Nested table expression and TABLE function13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 20 Sep 2001 21:03:13 -0500374_us-ascii David (and Michele),

I'm afraid you are mistaken on this one. The answer you have given relates to a (materialized) nested table expression. This however is a correlated nested table expression or correlated table reference, indicated by the TABLE keyword and correlation predicate within the table expression. These were delivered in V6 of OS/390 DB2. [...] 20060 128 66_Re: How to select index for better performance in synch with space13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 20 Sep 2001 22:59:09 -0500435_iso-8859-1 Duam,

A collegue of mine, Dan Luksetich, has a nice quote on his website "If anyone tells you they're a DB2 expert...they're lying". Although I do not know everything (even though I am a certified expert?), it was nice to receive the complement.

For starters, I would suggest you take a look at a presentation given by Michael Hannan at IDUG NA 2000 titled: "Are Your Indexes Well Designed, Well Tuned?". [...] 20189 116 26_Re: Insert with Sub-select18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 21 Sep 2001 10:32:49 +0530489_- Steve,

Tom is right. I was also trying the same thing yesterday...strange coincidence..:). below is the SQL....

INSERT INTO TESTTBL1 (NAME,CITY,TIME1) (SELECT NAME, CITY, CURRENT TIMESTAMP FROM TESTTBL2)

TESTTBL1 has 'Identity' as its first col (defined as IDENTITY) and Time1 is a timestamp col. Col 'Identity' takes proper values. I noticed that Time1 values remains the same for all the rows inserted above....guess this is the time when rows got committed. [...] 20306 26 33_Buffer Pool for Dummies Part Deux11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Fri, 21 Sep 2001 13:42:38 +0700365_- Dear All,

When I try to connect to our DB, this error message sometime occur: 'SQL1478W The database has been started but only one buffer pool has been activated. SQLSTATE=06126" We have 4 BP's in that DB. Although this message occur, when I try to do Load or other process to other table that use different BP's, the process seem to be working fine. [...] 20333 68 37_Re: Buffer Pool for Dummies Part Deux20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Fri, 21 Sep 2001 11:10:29 +0200591_iso-8859-1 Hi Endy,

-----Original Message----- From: Endy Lambey [mailto:endyl@CENTRIN.NET.ID] Sent: Friday, September 21, 2001 8:43 AM To: DB2-L@RYCI.COM Subject: Buffer Pool for Dummies Part Deux



>Dear All,

>When I try to connect to our DB, this error message sometime occur: >'SQL1478W The database has been started but only one buffer pool has >been activated. SQLSTATE=06126" >We have 4 BP's in that DB. >Although this message occur, when I try to do Load or other process to >other table that use different BP's, the process seem to be working >fine. [...] 20402 50 41_Fast copy to prod debug including indexes0_24_Mike_Jarrett@MANDG.CO.UK31_Fri, 21 Sep 2001 11:19:54 +0000547_us-ascii Hi list, I am creating a fairly big new production database (in a brand new subsystem), and I have a requirement to take a very fast daily copy of it into a debug database (ether in it's own subsystem or in the production subsystem, whichever turns out to be easier). I hope to find some way of doing the copy-over using the RVA DASD snapshot utility. I'm going to create the tables with chosen OBIDs, so that the table OBIDs will match between prod and debug, so I ought to be able to copy the tablespaces across using an 'instant' [...] 20453 185 50_Re: need separate DB2 subsystem for Data Warehouse11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 21 Sep 2001 08:37:00 -0500390_iso-8859-1 You shouldn't need separate logon procs. We have multiple DB2 systems on our LPARs and one set of DB2 libraries (with the exception of SDSNEXIT). We use a clist/rexx to allocate the libraries so when we upgrade to a new release, we just point to the correct set of libraries for that SSID. My company has been using this technique for over 5 years, with no problems so far. [...] 20639 98 21_Re: Buffer pool usage12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Fri, 21 Sep 2001 09:03:25 -0500597_iso-8859-1 Joel

You are right, we have had different experiences. It appears that each data center's value may vary, thus why there are so many times when we use the phrase "It depends!". I'm confident that those on the list and those not on the list will use every means possible to tune DB2 for optimal performance. I'm also convinced that I would rather examine the performance opportunities of DB2 on OS/390 than any other Relational Data Base Management system anywhere in the world. No other platform provides the data for analysis and the metrics to tune for performance. And I [...] 20738 50 23_Re: Performance Monitor12_Kirk Hampton16_khampto1@TXU.COM31_Fri, 21 Sep 2001 08:59:16 -0500684_us-ascii Just a guess, you forgot to include dynamic FETCH and/or PREPARE statements.









"Manching, Fernando" on 09/19/2001 02:51:50 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Kirk Hampton/BIZSRV/TXU) Subject: Performance Monitor





I am trying to monitor SQL statements on a UDB 5.2 using Performance Monitor.

If I add up the select sql statments + update/insert/delete sql statements + failed SQL + DDL statements + commits and compare it to the total of dynamic + static sql statements they don't match up at all. [...] 20789 26 34_DB2 replication tools and monitors11_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Fri, 21 Sep 2001 09:07:15 -0500396_- Hi List,

We're trying to build data replication between AIX UDB DATABASE and 500 occasionally connected laptops. About 200 tables need to be replicated for each laptop.

We're looking for ANY replication tools (other than Control center and DJRA) which can faciliate replication setup (define source, subscription),maintenance (table change for replication, etc) and monitor. [...] 20816 76 34_Re: Left Outer Join and null value0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 21 Sep 2001 09:26:01 -0500405_us-ascii when you move the new host values, do you re-initialize the dclgen. I suspect the problem may be that after the null indicator is set to '-1' in the prior retrieval to indicate null, it is not reset by the next retrieval. I remember a similar problem from back when I was programming where I was not checking the null flag and seeing old values in the field when the null indicator was set. [...] 20893 18 26_Re: Insert with Sub-select12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 21 Sep 2001 09:32:02 -0500391_us-ascii It works! Thank you!

I didn't realize I could specify a column list on the table being inserted into.

Thanks again.

Stg

================================================ 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. 20912 38 38_Re: DB2 replication tools and monitors16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Fri, 21 Sep 2001 15:36:19 +0100398_iso-8859-1 > We're trying to build data replication between AIX UDB DATABASE and 500 > occasionally connected laptops. About 200 tables need to be > replicated for > each laptop. > > We're looking for ANY replication tools (other than Control center and > DJRA) which can faciliate replication setup (define source, > subscription),maintenance (table change for replication, etc) and monitor. [...] 20951 87 50_Re: need separate DB2 subsystem for Data Warehouse14_Allison, Edith23_edith.allison@UCONN.EDU31_Fri, 21 Sep 2001 10:37:58 -0400647_iso-8859-1 Tina ... are you saying you do dynamic allocation of DB2 libraries? Could you share a sample of your clist?

-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: Friday, September 21, 2001 9:37 AM To: DB2-L@RYCI.COM Subject: Re: need separate DB2 subsystem for Data Warehouse



You shouldn't need separate logon procs. We have multiple DB2 systems on our LPARs and one set of DB2 libraries (with the exception of SDSNEXIT). We use a clist/rexx to allocate the libraries so when we upgrade to a new release, we just point to the correct set of libraries for that SSID. My company has [...] 21039 17 29_Re: -904 Resource Unavailable13_Jim McDonnell23_jmcdonnell@PROMENIX.COM31_Fri, 21 Sep 2001 09:47:00 -0500450_- Sue -

The reason code I get is C200B8, the reason type is 300 and RESOURCE NAME DSNDB04 .BMSORDER.X'00000002'.

DSNDB04 is the database. BMSORDERS is the table I am trying to update.

Jim

================================================ 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. 21057 27 38_Re: DB2 replication tools and monitors11_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Fri, 21 Sep 2001 09:49:11 -0500303_- Adrian,

It's two way replication. Data will be changed on both sites (aix and laptops) and each laptop will only get its own subset of data.

The estimate size of each latop table will vary from 200 rows to 10000 rows. The centrl database is on AIX, about 40 GB.( Backup file size). [...] 21085 18 29_Re: -904 Resource Unavailable13_Jim McDonnell23_jmcdonnell@PROMENIX.COM31_Fri, 21 Sep 2001 10:01:46 -0500261_- Sue -

I tried to drop the table and get a -904 error trying to do this. Reason code C90081, type 201.

We do not have an image copy that I am aware of. I am not a DBA and trying to do this on the fly. I am trying to avoid re-installing DB2. [...] 21104 17 19_Re: DB2 Column type12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 21 Sep 2001 10:05:55 -0500409_- I'm not sure on this one, but here are a couple of points. Character strings can include new line and carriage control characters. Translations of EBCDIC to and from ASCII may lose some of the characters. FOR BIT DATA will stop the translations, like BLOB. CLOB should be translated too. Unicode is the direction for improving translations, Java, and machines using Windows, UNIX, iSeries and zSeries. [...] 21122 82 35_Re: Accessing Stored Procedure Name14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Sep 2001 10:11:34 -0500505_- Thorsten

You have just discovered the !@#$%^&-up that is PARAMETER STYLE DB2SQL. I think this was defined by people who could only spell the name of a programming language if it had a single letter. In which case, if you use PROGRAM TYPE MAIN you get an explicit count of the number of parameters passed to the program. (At one time I checked for COBOL, and found that LE did not provide a high-bit in the last word of the parameter list. I don't know if it would for an assembler program.) [...] 21205 14 29_Re: -904 Resource Unavailable10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Sep 2001 17:26:40 +0200305_us-ascii Some index stopped on that table ???

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. 21220 50 29_Re: -904 Resource Unavailable15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 21 Sep 2001 10:28:02 -0500301_iso-8859-1 Jim, According to that reason code and type you have an index that is stopped for all access. Try starting that index and then dropping your table. Did you actually delete your active logs and BSDS or did you copy the data somewhere and copy it back in when the new logs were created? [...] 21271 26 38_Re: DB2 replication tools and monitors16_Adrian Challinor29_adrian.challinor@OSIRIS.CO.UK31_Fri, 21 Sep 2001 16:35:36 +0100418_iso-8859-1 Richard,

I take it you have looked at Data Propogator? Its some years since I dealt with it, but I thoight it would do just what you want?



Adrian Challinor R&D Director - Osiris Consultants Ltd e-mail: adrian.challinor@osiris.co.uk

phone: 020-8852-1605 This e-mail is environment fax: 0870-0349-134 friendly and was made only mobile: 07860-290-883 from recycled electrons. [...] 21298 21 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 21 Sep 2001 10:30:34 -0500318_- This is my personal opinion. There will be a few who cannot come. I'm sure that some individuals are involved or concerned and will need to adjust their plans. My prayers and thoughts are with those who have been affected by the terror. I work with a number of customers and colleagues who were in WTC 2 and 5. [...] 21320 33 44_How to get rid of a runaway stored procedure24_Philip Nelson (Team DBA)21_teamdba@ATTGLOBAL.NET31_Fri, 21 Sep 2001 16:37:20 +0100345_us-ascii In our ongoing testing of stored procedures (on our development system), we've just had a poorly coded COBOL SP in an infinite loop (updating the same row over and over again). The SP was being called from a trigger fired by updating a row in SPUFI.

We -

1) Cancelled the thread in DB2 2) Cancelled the user from TSO [...] 21354 41 63_Re: Is the DB2 Technical Conference in Orlando going to happen?12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Fri, 21 Sep 2001 10:43:51 -0500403_iso-8859-1 I just want to add my agreement to what Roger has said. In my opinion, you are absolutely right.

Tom Moulder (BMC Software, Inc.) my opinions, not my employers ...

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Friday, September 21, 2001 10:31 AM To: DB2-L@RYCI.COM Subject: Re: Is the DB2 Technical Conference in Orlando going to happen? [...] 21396 12 34_Re: Left Outer Join and null value12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 21 Sep 2001 10:38:00 -0500312_- Where are the host variables on the OPEN cursor?

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. 21409 138 48_Re: How to get rid of a runaway stored procedure12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV31_Fri, 21 Sep 2001 12:28:11 -0400620_iso-8859-1 I had the same user problem (looping) earlier this week. Canceling the thread didn't free it, nor did Canceling the user. What I had to do was Force a termination of the user's address space from the system, which foregoes the clean up procedures a CANcel does and goes straight to removal. It was very effective at freeing up everything, but unfortunately, doing so caused a "DSNV086E -SSID DB2 ABNORMAL TERMINATION REASON=00E50054", which promptly abended the subsystem due to synch errors. We were able to immediately restart, but I would like to explore less debilitating solutions if anyone has any [...] 21548 79 50_Re: need separate DB2 subsystem for Data Warehouse11_Mark Turner29_mark.turner@UCDMC.UCDAVIS.EDU31_Fri, 21 Sep 2001 09:59:23 -0700377_us-ascii There are various products on the market that allow for dynamic STEPLIB (or tasklib) allocation from TSO/ISPF - see Computer Associates or Tone Software. These virtually eliminate the need for multiple TSO logon procs with different STEPLIB concatenations. You can also use the TSOLIB command, but I think you need to execute this command before you invoke ISPF. [...] 21628 32 29_Re: DB2 & DBCS usage question12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 21 Sep 2001 12:10:06 -0500460_- There is no simple answer for the question, other than "It depends." You'll want to start with reading the Installation Guide appendix on Character Conversion (8 or 10 pages). There are quite a few options for DBCS in various countries, and this question needs to be answered correctly by those who implement. You need to look at the CCSID you use now and the CCSID set you will be using. Are any characters used today that are not in the new set SBCS? [...] 21661 23 27_dynamic allocation of files10_Chris Kern23_Chris_Kern@VANGUARD.COM31_Fri, 21 Sep 2001 14:21:30 -0400342_us-ascii Listers

This is a bit off subject, however I'm looking for a dataset dynamic allocation routine(s) which I can invoke/call from a COBOL program in an OS390 environment. I have used these in the past, however in my current shop they do not have any facility to do this.

Any assistance would be greatly appreciated. [...] 21685 46 33_DB2 Connect V7.2 to DB2 OS/390 V611_Moore, Tony15_TMoore@IKON.COM31_Fri, 21 Sep 2001 14:24:47 -0400342_iso-8859-1 Yo Listers, I was wondering if anyone has run across the following. We have just installed DB2 EE V7.2 on AIX and are trying to connect to DB2 for OS/390 V6. The connect is failing with the following error:

SQL5048N The release level of the database client is not supported by the release level of the database server. [...] 21732 50 31_Re: dynamic allocation of files12_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 21 Sep 2001 13:31:45 -0500710_- Replying off-line.

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

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to upset my management.

Where does bad light end up? In a prism! (Gary Lisica)

> -----Original Message----- > From: Chris Kern [SMTP:Chris_Kern@VANGUARD.COM] > Sent: Friday, September 21, 2001 1:22 PM > To: DB2-L@RYCI.COM > Subject: dynamic allocation of files > > Listers > > This is a bit off subject, however I'm looking for a dataset dynamic > allocation > routine(s) which I can invoke/call from a COBOL program in [...] 21783 90 41_Re: DB2/OS390 V6 - CICS Transaction Abend11_Moore, Tony15_TMoore@IKON.COM31_Fri, 21 Sep 2001 14:37:19 -0400320_iso-8859-1 Jeff, Thanks for the info. I had previously seen this APAR in searches on IBMLINK, but blew it off since it said it was dealing with DDF transactions and our abend was triggered by a CICS transaction. However, IBM support suggested trying it before we starting shipping dumps and it worked like a charm. [...] 21874 35 31_Re: dynamic allocation of files12_Jim Harrison12_jimh@QIS.NET31_Fri, 21 Sep 2001 14:46:45 -0400615_us-ascii Check the document archives. I posted an assembler routine to do this in a stored procedure.

http://jupiter.ryci.com/cgi/wa.exe?A2=ind0003&L=DB2-L-DOCUMENTS&P=R880

At 02:21 PM 9/21/01 -0400, Chris Kern said: >files >To: DB2-L@RYCI.COM > >Listers > >This is a bit off subject, however I'm looking for a dataset dynamic >allocation >routine(s) which I can invoke/call from a COBOL program in an OS390 >environment. >I have used these in the past, however in my current shop they do not have any >facility to do this. > >Any assistance would be greatly appreciated. > >Thanks > >chris kern [...] 21910 116 41_Re: DB2/OS390 V6 - CICS Transaction Abend12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Fri, 21 Sep 2001 14:53:45 -0400728_us-ascii Not a problem Tony. Thats one of the best things about this list. People helping people.







"Moore, Tony" @RYCI.COM> on 09/21/2001 02:37:19 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: DB2/OS390 V6 - CICS Transaction Abend



Jeff, Thanks for the info. I had previously seen this APAR in searches on IBMLINK, but blew it off since it said it was dealing with DDF transactions and our abend was triggered by a CICS transaction. However, IBM support suggested trying it before we starting shipping dumps and [...] 22027 72 31_Re: dynamic allocation of files11_Jim Keohane19_jimkeo@LOCKSTAR.COM31_Fri, 21 Sep 2001 14:56:41 -0400426_us-ascii Chris,

You might have hit the motherlode here. There was a recent colloquy on www.search390.com that gave numerous approaches and example code. You can visit www.search390.com (you may have to do a quick free registration) and then look for "Developers Tips" in lower right. Once there search for DYNA or SVC 99 or ISPLINK or BPXWDYN. Some interesting info in posts # 151, 156, 159 and 178. - Jim Keohane [...] 22100 22 37_Re: DB2 Connect V7.2 to DB2 OS/390 V613_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 21 Sep 2001 14:56:28 -0400331_iso-8859-1 Hi Tony, I can't help you with the SQL5048N.

But I can tell you that I have the same erroneous Database release level 9.00 on a Windows 2000 client. I've seen that since V7.1 or earlier. It's wrong, but I'd say it's probably unrelated to your problem, since I see that version number and am able to connect. [...] 22123 35 15_Table Partition13_Harry William27_harry_williamtk@HOTMAIL.COM31_Fri, 21 Sep 2001 19:00:42 +0000386_- Hi Listers, We have table which has 6 partitons.But the data is not split evenly througout the partition.Out of 18 million records, partition 6 alone has 13 million record and the rest is spread across the other partition.

This particular table is being used by a number of online and batch programs.We are facing poor performance on the online as well as batch programs. [...] 22159 29 26_Load data by Script Center10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Fri, 21 Sep 2001 12:21:32 -0700310_us-ascii I have data reside in my NT directory and I am able to load data into DB2 table which is on AIX server by CLP or through control center of my NT/db2 client. However, when I try to schedule and run through SCRIPT CENTER I am receving Path error. Is any way I can avoid FTP the file to aix server. [...] 22189 93 19_Re: Table Partition64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Fri, 21 Sep 2001 14:35:26 -0600451_ISO-8859-1 Hi Harry.

In VERY quick summary,

1) determine the new limitkeys 2) unload the data 3) drop the tablespace 4) recreate the tablespace 5) load the tablespace 6) rebind everything 7) regrant everything

If you are DB2 V6 or above, using IBM's provided method, you could:

1) determine the new limitkeys 2) ALTER the limitkeys with your new keys 3) Using IBM's REORG, REORG the tablespace (if you can, obviously) [...] 22283 85 33_DB2 for Systems Programmers Class13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Fri, 21 Sep 2001 15:24:01 -0400395_US-ASCII List,

I was just reviewing the course description for CF850 (IBM Educ), theoretically DB2 for Systems Programmers. In it there was mention of several things, to be taught over 4.5 days. However, I felt that some were in the realm of DBA Training rather than systems programming.

So, two general questions:

Where do you go for DB2 systems programming training? [...] 22369 135 37_Re: DB2 for Systems Programmers Class16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 21 Sep 2001 15:40:47 -0400382_iso-8859-1 Where do you go for DB2 systems programming training?

==The manuals. Other Sysprogs. Conferences (well.... a LONG time ago)

How do you distinguish between training for Systems Programmers and training for DBAs?

==Which parts of which manuals to read. Other DBAs instead of Sysprogs. Different sessions in conferences (well.... a LONG time ago). [...] 22505 112 50_Re: need separate DB2 subsystem for Data Warehouse16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Fri, 21 Sep 2001 15:47:16 -0400525_iso-8859-1 The DSN command is the same between DB2 V5 and V6, so you can attach to multiple releases of DB2 with your existing TSO or CAF logon procedures, but...

If you use the DB2 V6 library in the logon procs for your V5 TSO DB2 users, some of the DB2I program preparation functions won't work correctly in V5. This is not a problem unless your users use DB2I for program preparation. I kept the DB2 load library at V5 in the TSO logon procs for each LPAR when I was rolling out DB2 V6 until all the DB2s on a [...] 22618 140 37_Re: DB2 for Systems Programmers Class15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 21 Sep 2001 15:00:00 -0500350_iso-8859-1 Hi, Lockwood, My experience has been that there is a very blurry line between DB2 sysprogs and DB2 DBAs. It is useful to learn some of both. In the beginning I ended up going to all of the training that IBM had to offer. I believe that at the time the Systems Administration class was geared more toward systems rather than DBA work. [...] 22759 201 37_Re: DB2 for Systems Programmers Class16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Fri, 21 Sep 2001 16:11:40 -0400644_iso-8859-1 I have been a DB2 Systems Programmer (and sometimes a DBA as well) for a long time, and here's the very condensed version of what I have seen.

As a general rule, DB2 Systems Programmers install and maintain DB2 and DB2 program products, including maintaining the system databases (the Catalog, Directory, Program Product databases). In this respect, every DB2 Systems Programmer is also a DBA, since system tablespaces need to be image copied, recovered, checked for referential integrity on occasion, and reorganized just the same as any other tablespaces in a DB2 subsystem. If the DB2 Sysprog doesn't have familiarity [...] 22961 17 34_DB2 Row Archive Manager for OS/39014_Donald A Smith18_dsmithj@US.IBM.COM31_Fri, 21 Sep 2001 16:24:13 -0400489_us-ascii Hi List, Anyone using this product? It seems like it may be a nice tool for applications that run periodic purges. Does this product reclaim DASD or does a REORG have to be performed in addition to archival activity.

sincerely, Don

================================================ 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. 22979 200 37_Re: DB2 for Systems Programmers Class12_Rumyee, Paul19_paul.rumyee@HBC.COM31_Fri, 21 Sep 2001 16:28:39 -0400299_iso-8859-1 I am also a DB2 Sysprog/DBA too and I am working now in a DB group. I agree with Jean's advise and in addition to 'How to install DB2', I suggest you should also attend SMP/E class since a lot of the S/390 products use SMP/E to install and maintain. Paul Rumyee Hudson's Bay Company [...] 23180 122 37_Re: DB2 for Systems Programmers Class24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 21 Sep 2001 16:36:53 -0700454_us-ascii

Lockwood, Go to http://www.ibm.com/software/data/db2/skills In the lower right hand corner, there is a selection entitled "For Gold Consultant Skills and Educational offerings". Selecting this will download a list of DB2 Gold Consultants, many of whom offer customized DB2 education, and their areas of specialization.

Frank



--Original Message Text--- From: Lockwood Lyon Date: Fri, 21 Sep 2001 15:24:01 -0400 [...] 23303 172 19_Re: Table Partition14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Fri, 21 Sep 2001 13:35:31 -0700625_iso-8859-1 Why IBM Reorg?

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



-----Original Message----- From: Todd Burch ***************************************************** [mailto:tburch@CDBSOFTWARE.COM] Sent: Friday, September 21, 2001 1:35 PM To: DB2-L@RYCI.COM Subject: Re: Table Partition

Hi Harry.

In VERY quick summary,

1) determine the new limitkeys 2) unload the data 3) drop the tablespace 4) recreate the tablespace 5) load the tablespace 6) rebind everything 7) regrant everything [...] 23476 95 19_Re: Table Partition0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 21 Sep 2001 15:44:49 -0500348_us-ascii If your are OS390 V6 or higher, then DB2 parallel processing can come into play also. By running parallel processes (if your platform has more than one CPU available) both for utilities and for SQL DB2 can (in theory) reduce elapsed processing time in exchange for more CPU Cycles. Check the "DEGREE" the package has been bound with. [...] 23572 150 19_Re: Table Partition64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Fri, 21 Sep 2001 16:26:23 -0600439_ISO-8859-1 Hello Daryl!!

Why IBM Reorg? I've been asking that question for 11 years!! (I'm going to jump on this one like a hobo on a bologna sandwich...)

When you issue the SQL "ALTER LIMITKEY", DB2 puts that object into REORGP, or REORG PENDING status. Is a reorg actually pending? Well, maybe just a repartitioning is pending, but the flag says REORG pending. IBM REORG removes this status flag as part of the REORG. [...] 23723 228 37_Re: DB2 for Systems Programmers Class16_Srinivasan Kumar31_srinivasan.kumar@PRUDENTIAL.COM31_Fri, 21 Sep 2001 17:44:55 -0400344_us-ascii



Thanks for the useful information regarding DB2 sysprogs/DBAs.

I am only a DB2 applications programmer and I am pretty confident of my DB2 skills. I would want to move to DBA class.. Basically, I am looking for one course package which would help me move to be a DBA. (or try to be a DBA !!) Any advises... [...]