1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 1999, week 3 2 42 19_Re: DSNDBM1 Storage10_Ron, Yoram12_rony@ANZ.COM31_Fri, 15 Oct 1999 15:19:05 +1000448_iso-8859-1 Generally speaking, DBM1 will have a page of real storage for every buffer-pool page. And a page in expanded storage for every hiper pool page. If your DB2 allocates about 100,000 buffers (50,000 in BP and 50,000 in HP) you will see about 400MB (200MB CS and 200MB ES) used by DBM1. On an active DB2 system, where there is some activity (and the data bases are larger then the buffers), the storage is nearly never released. Yoram. [...] 45 18 27_Re: Euro IDUG get-together?11_Popy Dimoka24_dimokap@INTERAMERICAN.GR31_Fri, 15 Oct 1999 09:32:23 +0300347_iso-8859-7 So, it looks like i won't be alone in Nice.....

AU REVOIR !!!

Popy Dimoka Athens

================================================ 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. 64 53 29_Re: Index vs Tablespace Scans12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 15 Oct 1999 11:24:57 +0200176_x-user-defined Hi, You may want to split the delete to groups by using < and > ( or between) and not do the whole bunch in one go. You may run then in parallel as well.

118 164 30_Re: Stored Procedures and PL/124_Murray, David (CAP, CMC)28_David_Murray@MORTGAGE.GE.COM31_Fri, 15 Oct 1999 07:51:13 -0400537_iso-8859-1 Do you have the LE environment setup in the SPAS address space. For example, do you have a library like SYSx.SCEERUN available at run time. Also, did you link with the DSNALI interface module, rather than the default.

David D. Murray, Technical Consultant GE Capital Mortgage, (919) 846-4262, Dial-com: *760-4262



-----Original Message----- From: Skelton, Terry (corp) USX [mailto:tskelton.inc@MOOG.COM] Sent: Thursday, October 14, 1999 1:55 PM To: DB2-L@RYCI.COM Subject: Stored Procedures and PL/1 [...] 283 38 41_Re: DB2 Connect to DB2/OS390 using TCP/IP13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 15 Oct 1999 07:54:12 -0400361_iso-8859-1 Check your target database name. This is not an OS/390 Database name but rather the location name for the subsystem. Look in the log for your DB2 master address space for the TCP/IP startup messages. Specifically, look for the DSNL004I message. This shows the location name for the subsystem. That message also confims the port you need to use. [...] 322 114 41_Re: DB2 Connect to DB2/OS390 using TCP/IP11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Fri, 15 Oct 1999 08:07:35 -0400508_iso-8859-1 Ed,

My advise, if you are just getting into DB2 connect is to call IBM, they have an informational APAR (sorry I don't remember the number) which will list all the PTF's you need for DB2, MVS and TCP/IP. Without having all the PTF's applied you can get very unpredictable results, including: your DIST address space looping, not being able to stop DB2 (have to cancel/abend), hanging threads, etc.... Also, don't forget that you must define a receiving port as well as a sending port. [...] 437 20 32_Why have't I Received any Mails?15_Jeffery A Price17_jprice@IPALCO.COM31_Fri, 15 Oct 1999 07:24:33 -0500332_us-ascii Dear List Owner,

I have not received my much anticipated digested version of the DB2-L mail in two (2) days... I sent an email to listserv@ryci.com with the body containing the text: SUBSCRIBE DB2-L. I received a response stating that I am already subscribed. Is there some sort of problem with the listserver? [...] 458 29 30_Web access to DB2/CICS on MVS?14_Jackie Gribbon23_jgribbon@GSSEC.BT.CO.UK31_Fri, 15 Oct 1999 13:29:32 +0100308_iso-8859-1 I'm looking into options for allowing access via the web to both CICS transactions and directly to the underlying DB2 database on MVS. I don't need to know all the technical details at this stage because the study just requires me to say what is/isn't possible and, in outline, how to do it. [...] 488 91 19_Re: DSNDBM1 Storage22_KOA DAVID S. (DBM1DXK)15_dbm1dxk@UPS.COM31_Fri, 15 Oct 1999 08:56:12 -0400457_iso-8859-1 Hiperpool pages do not directly affect the storage utilization of the DBM1 address space. Hiperpools are managed by hiperspaces which are separate address spaces. The components which do use storage in the DBM1 address space include:

-virtual pool -virtual/hiperpool control blocks -EDM pool -RID pool -number of open datasets -db2 code -application threads -system threads(prefetch, castout...) -open compressed datasets(dictionary) [...] 580 111 19_Re: DSNDBM1 Storage22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM31_Fri, 15 Oct 1999 09:03:30 -0400687_- I believe the SORT pool could also be added to your list.



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

> ---------- > From: KOA DAVID S. (DBM1DXK)[SMTP:dbm1dxk@UPS.COM] > Sent: Friday, October 15, 1999 8:56 AM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DSNDBM1 Storage > Sensitivity: Personal > > Hiperpool pages do not directly affect the storage utilization of the > DBM1 address space. Hiperpools are managed by hiperspaces which are > separate address spaces. The components which do use storage in the > DBM1 address space include: > > -virtual pool > -virtual/hiperpool control blocks > -EDM pool > -RID pool [...] 692 23 20_Sequential Detection0_24_ssethi@LOT.TATASTEEL.COM31_Fri, 15 Oct 1999 18:29:52 +0530304_- Hi All, I have read that sequential detection is used if sequential/list prefetch cann't be decided at the bind time ,this is o.k but it also sa= ys that Sequential Detection is good for ?singleton? SQL operations, where= a single Select, Insert, Update or Delete statement is processed inside a= [...] 716 136 41_Re: DB2 Connect to DB2/OS390 using TCP/IP8_Long, Ed15_Ed.Long@FMR.COM31_Fri, 15 Oct 1999 09:37:51 -0400604_ISO-8859-1 Hi Anne. Thanks for the suggestions. At our shop, we aren't having a problem with DB2/Connect. We did have problems with each of the items I asked about. I am interested in finding out what Philip's eventual resolution is, however. Thanks again for being willing to help.

> -----Original Message----- > From: Stout, Anne [SMTP:Anne.Stout@DOWJONES.COM] > Sent: Friday, October 15, 1999 8:08 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Connect to DB2/OS390 using TCP/IP > > Ed, > > My advise, if you are just getting into DB2 connect is to call IBM, they > have an informational APAR [...] 853 259 35_Re: Performance Monitoring Question20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 15 Oct 1999 09:43:21 -0400321_iso-8859-1 Michael

I have observed this too that task switch overhead is higher in CICS than other attachment facilities and must be dependent on number of sql calls. Just to eloborate more on this, I think that this task switch overhead is under the attachment TCB. So looking at it diagrammatically we have [...] 1113 47 16_Re: SQL question4_vcei14_vcei@CEGEKA.BE31_Fri, 15 Oct 1999 15:46:52 +0200468_iso-8859-1 Hi! Simple comme bonjour [that `s French :-) ] :

INSERT INTO OWNER.TAB1 SELECT A.*, ` ` FROM OWNER.TAB2 A

You could even specify a value depending on the content of (one of ) the other attributes of each row: CASE WHEN ATTR1 = `XXX` THEN `Z` ELSE ` ` END

Bye PS: since I`m my own boss, I can`t be held responsible for myself. ---------------------------------------------------------------------------- --------------------------- [...] 1161 23 23_PeopleSoft 7.50 to 7.5116_Isenhour, Amy F.27_afisenhour@NOVANTHEALTH.ORG31_Fri, 15 Oct 1999 09:54:34 -0400559_- Requesting information from any PeopleSoft 7.5 OS/390 DB2 V5 DBAs.

This is our first upgrade of PeopleSoft since we did the initial install. The consultant has come in and asked for us to create a copy of the Production instance after the PayCalc portion of the batch cycle. They want me to use an image copy. The only way I know how to do this is to create the new instance using our Migrator tool which is fairly easy but time consuming, I can do this ahead of time. The problem I see is using DSN1COPY and translating 4,000+ DBIDs,PDID,ODIBs [...] 1185 54 34_Re: Web access to DB2/CICS on MVS?11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Fri, 15 Oct 1999 09:56:42 -0400495_iso-8859-1 Jackie,

Without knowing what you want the application to do it's a little hard to help you. If all you want is web access to your mainframe applications look into Host on Demand from IBM. We use it here, and can access any of our mainframe applications through the web.





-----Original Message----- From: Jackie Gribbon [mailto:jgribbon@GSSEC.BT.CO.UK] Sent: Friday, October 15, 1999 8:30 AM To: DB2-L@RYCI.COM Subject: Web access to DB2/CICS on MVS? [...] 1240 53 27_Re: PeopleSoft 7.50 to 7.5111_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Fri, 15 Oct 1999 10:04:53 -0400320_iso-8859-1 Amy,

When we had to create a complete copy we used unload/load instead of image copies. If you can copy your data to DASD the outage isn't that bad. I didn't want to set up the OBID translations either...

If anyone has a better idea, I'm all ears. We will be facing our first upgrade soon! [...] 1294 41 27_Re: PeopleSoft 7.50 to 7.5114_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 15 Oct 1999 10:17:24 -0400383_- Amy, Is this financials or HR? You should be able to do this with migrator. Phil

Phil Gunning DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Certified Solutions Expert -- CICS TS

-----Original Message----- From: Isenhour, Amy F. [SMTP:afisenhour@NOVANTHEALTH.ORG] Sent: Friday, October 15, 1999 9:55 AM To: DB2-L@RYCI.COM Subject: PeopleSoft 7.50 to 7.51 [...] 1336 44 19_multiple index scan4_vcei14_vcei@CEGEKA.BE31_Fri, 15 Oct 1999 16:12:04 +0200432_Windows-1252 Hello!

OS390 DB2 v5:

I wonder why the optimizer has decided to do a multiple index scan in the following situation: index 1 contains 6 fields, index 2 contains 4 fields. First two are the same (!!). Query specifies `=` for these two and doesn`t refer in any way to the other fields in index2 !!!! Both indexes: not clustered, not unique. Some statistics, maybe ? Although they seem irrelevant to me. [...] 1381 77 27_Re: PeopleSoft 7.50 to 7.5116_Isenhour, Amy F.27_afisenhour@NOVANTHEALTH.ORG31_Fri, 15 Oct 1999 10:21:54 -0400365_- I would use migrator for the entire copy except they want the copy during the production paycycle after the PayCalc is run. The unloads would take at least a few hours and that would keep the pay cycle on hold until I was done. We have a time crunch to get the tapes to the bank and they would not be willing for this long of an outage. Amy Isenhour - 47022 [...] 1459 72 27_Re: PeopleSoft 7.50 to 7.5114_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 15 Oct 1999 10:26:42 -0400357_- Anne, Thats the way to go. We used Migrator to do it. Phil

Phil Gunning DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Certified Solutions Expert -- CICS TS

-----Original Message----- From: Stout, Anne [SMTP:Anne.Stout@DOWJONES.COM] Sent: Friday, October 15, 1999 10:05 AM To: DB2-L@RYCI.COM Subject: Re: PeopleSoft 7.50 to 7.51 [...] 1532 1192 61_PeopleSoft 7.50 to 7.51 - How to "clone" PeopleSoft instances17_Anthony Ray Gould17_agould@UAKRON.EDU31_Fri, 15 Oct 1999 10:18:46 -0400622_us-ascii Amy and others,

We "clone" PeopleSoft instances on a pretty regular basis (more than I would like). We "clone" before upgrades and we also "clone" our production instance into a quality assurance and a training instance on a pretty regular basis. I have attached a Microsoft Word document that contains the steps necessary to "clone" a PeopleSoft instance. We use Compuware's DBA/XPERT to "clone" the structure of the PeopleSoft instance and use DSN1COPY with programs that I wrote to grab the DBID and PSID from the source and target instance and then merge the results together to build the SYSXLAT [...] 2725 41 27_Re: PeopleSoft 7.50 to 7.5116_Brian F. Patella25_patellab@WORLDNET.ATT.NET31_Fri, 15 Oct 1999 10:39:28 -0400686_iso-8859-1 Be aware of the 500 table limit using DSN1COPY. PeopleSoft delivers some of it's 7.5 tablespaces with more than 500 tables.

-----Original Message----- From: Isenhour, Amy F. Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Friday, October 15, 1999 9:54 AM Subject: PeopleSoft 7.50 to 7.51



>Requesting information from any PeopleSoft 7.5 OS/390 DB2 V5 DBAs. > >This is our first upgrade of PeopleSoft since we did the initial install. >The consultant has come in and asked for us to create a copy of the >Production instance after the PayCalc portion of the batch cycle. They want >me to use an [...] 2767 85 34_Re: Web access to DB2/CICS on MVS?14_Jackie Gribbon23_jgribbon@GSSEC.BT.CO.UK31_Fri, 15 Oct 1999 15:38:36 +0100564_iso-8859-1 Anne,

I know it may seem a bit back to front, but until I know what's possible and feasible I can't be specific about what I want to do. :)

Essentially, we want to give users access to data which is held in DB2 tables and where there are existing CICS transactions to provide most of the functionality. In many cases the requirement will be satisfied by simply making the existing transactions available via the web. In other cases the existing transactions may not be suitable and another means of getting at the data may be needed. [...] 2853 59 23_Re: multiple index scan20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 15 Oct 1999 10:43:54 -0400532_- Is there any non BT predicate (having OR etc.) ?

> -----Original Message----- > From: vcei [SMTP:vcei@CEGEKA.BE] > Sent: Friday, October 15, 1999 10:12 AM > To: DB2-L@RYCI.COM > Subject: multiple index scan > > Hello! > > OS390 DB2 v5: > > I wonder why the optimizer has decided to do a multiple index scan in the > following situation: > index 1 contains 6 fields, index 2 contains 4 fields. First two are the > same > (!!). > Query specifies `=` for these two and doesn`t refer in any way to the > other > fields in [...] 2913 26 14_Cool:Gen - MVS15_Michael Bancale21_mbancale@TXFB-INS.COM31_Fri, 15 Oct 1999 09:59:13 -0500594_iso-8859-1 We have purchased the Cool:Gen - MVS tool set. For those who are using this with the Repository on DB2/MVS(OS/390) where did you install it. In a production region, test region, standalone region?

TIA.

Michael



Michael Bancale mbancale@txfb-ins.com Texas Farm Bureau Insurance Company www.txfb-ins.com Waco, Texas

================================================ 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. 2940 27 17_SDSNEXIT - Thanks15_Michael Bancale21_mbancale@TXFB-INS.COM31_Fri, 15 Oct 1999 10:00:49 -0500397_iso-8859-1 Well, I would like to thank you for your responses to my question concerning SDSNEXIT for each subsystem. The results were almost 100% for seperate SDSNEXIT's for each DB2 subsystem. This convinced us to stay with what we had.

Thanks again.

Michael



Michael Bancale mbancale@txfb-ins.com Texas Farm Bureau Insurance Company www.txfb-ins.com Waco, Texas [...] 2968 63 31_Repost: PeopleSoft 7.50 to 7.5119_J. Michael Morrilll21_xavier@NETEXPRESS.NET31_Fri, 15 Oct 1999 10:12:33 -0500677_iso-8859-1 Greetings!

This message appeared in the DB2-L error mail box. DB2-L did not post this message because it found mail header data in the message body and assumed it was an error message.

The original poster was Robert Jans

Cheers!

J. Michael Morrill DB2-L Chief List Owner

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

Amy, I've had to do that for our Peoplesoft 5.12 often enough I've written a simple COBOL batch job that takes 2 input files and writes a flatfile in IEBUPDATE format to create a pds with a zillion members in it. This is still a work in progress but if you're interested, [...] 3032 100 48_Re: ODBC- catalog locks - OS/390 db2v4 - DB2OCAT0_17_rporco@UOTTAWA.CA31_Fri, 15 Oct 1999 11:26:19 +0000591_US-ASCII Thanks to all for your insight and suggestions..

Leon, The utility DB2OCAT may be just what I was looking for. I found the following behaviour and would appreciate your opinion .. I installed the DB2OCAT. . the intial 'apply all changes to the data source and the optimized catalog' ran for about 45 minutes.. The update tables used 90% of this time. This in a test evironment that represents about 10% of our production. We expected refreshing the optimized catalog to be quick., it took just as long. ( seems to drop and recreate all the tables followed by inserts..) [...] 3133 69 11_Re: coolgen16_Kittell, Chris J18_ckittell@AMFAM.COM31_Fri, 15 Oct 1999 10:29:37 -0500596_iso-8859-1 I'm guessing you'll save a lot of hassles just installing in test. Unfortunately, this makes the 'test' region more of a production region. Be sure to do the orphan cleanup and reorg combo as directed. Also, you probably want to do model level backups, which simple sql-select data into a dataset...note: model names will change a lot, so create some jcl that backup say fifty miniature models. The jcl should reference a dataset that someone can edit as models get added or deleted. The job will fail if somebody deletes a model out from under you---so... make the oncall person [...] 3203 52 27_Re: PeopleSoft 7.50 to 7.5110_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU31_Fri, 15 Oct 1999 08:29:12 -0700546_us-ascii >At 09:54 AM 10/15/99 -0400, you wrote: >Requesting information from any PeopleSoft 7.5 OS/390 DB2 V5 DBAs. > >This is our first upgrade of PeopleSoft since we did the initial install. >The consultant has come in and asked for us to create a copy of the >Production instance after the PayCalc portion of the batch cycle. They want >me to use an image copy. The only way I know how to do this is to create the >new instance using our Migrator tool which is fairly easy but time >consuming, I can do this ahead of time. The problem I [...] 3256 25 32_Giving Instructions to Optimizer0_24_ssethi@LOT.TATASTEEL.COM31_Fri, 15 Oct 1999 21:09:20 +0530536_- Hi all, I have come to know that in DB2 UDB V6 for OS/390 PLAN_TABLE can be used by user to insert rows and giving hints to the Optimizer to choose the access path.I have read that optimizer does some feasibility studyon the basis of the inserted records in PLAN_TABLE and then decides whether the same access path can be used or not. Is there anybody who has used this features ?. I am unable to believe this ,so i am thinking that my implications are wrong. Please explain me the the whole procedures. Is it not dangerous ? If [...] 3282 134 23_Re: multiple index scan4_vcei14_vcei@CEGEKA.BE31_Fri, 15 Oct 1999 17:41:12 +0200412_iso-8859-1 Well, I really hope I don`t give you a shock, but the query looks like this. My conclusion at first, second, ... glance is: nuts! Distinct x ; group by x,y,x !?!? Selecting only ILOT,DLOT,KLOT (incl. grouping by) resolves the problem. I think I`ll have a closer look at it with the programmer. index1 : DVERTREK,KRTSTP,RVERTREK,FPREPDOC,IACTOR,KSTBOOK index2 : DVERTREK,KRTSTP,PLAATEFF,ITPTPLAAT [...] 3417 211 16_Re: ARCHIVE LOGS18_Padmanabham, Paddy27_PPadmanabham@IDAHOPOWER.COM31_Fri, 15 Oct 1999 09:57:29 -0600328_iso-8859-1 I thank all of you for the info. We have Platinum Log Analyzer at our site. But, currently we did not want to do any test runs to recover the tablespaces. We defer this until any USER actually want to recover to that particular point in time. (From our past experience at this site the chances are almost zero). [...] 3629 153 23_Re: multiple index scan15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Fri, 15 Oct 1999 12:08:00 -0400722_iso-8859-1 Is this SQL correct?

Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: vcei [mailto:vcei@CEGEKA.BE] Sent: Friday, October 15, 1999 10:41 AM To: DB2-L@RYCI.COM Subject: Re: multiple index scan



Well, I really hope I don`t give you a shock, but the query looks like this. My conclusion at first, second, ... glance is: nuts! Distinct x ; group by x,y,x !?!? Selecting only ILOT,DLOT,KLOT (incl. grouping by) resolves the problem. I think I`ll have a closer look at it with the programmer. index1 : DVERTREK,KRTSTP,RVERTREK,FPREPDOC,IACTOR,KSTBOOK index2 : DVERTREK,KRTSTP,PLAATEFF,ITPTPLAAT [...] 3783 34 40_Re: Restoring from DB2 V2 to DB2 UDB 5.212_Janet Rolsma12_janet@NM.NET31_Fri, 15 Oct 1999 10:19:54 -0600452_us-ascii On Friday, October 08, 1999 12:24 AM, Karina Chong-Sing - Cesercomp(Jefe Dpto. Sistemas) [SMTP:kchong@GOLIAT.ESPOL.EDU.EC] wrote: > Hi all, > > Anybody knows which is the process to restore a DB2 V2 database into a > DB2 UDB system?. > > Karina Chong Sing

In my experience, you can just do a RESTORE command. You may have to create the database first, but I think we had to do that because we had two different code page defaults. [...] 3818 155 23_Re: multiple index scan20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 15 Oct 1999 12:23:34 -0400323_iso-8859-1 1. The DISTINCT applies to entire column list in the SELECT not to just one column. 2. DB2 cannot use simple index to match column by predicates having non boolean terms, in such cases multi-index access is considered. This means that ORing most likely result in multi index accesses.

Regards Pillay [...] 3974 30 53_Need DB2 architect & DBA | In Austin Texas | Offtopic18_Webber, William D.17_WWebber@ROMAC.COM31_Fri, 15 Oct 1999 11:48:17 -0400593_- Hello all-- I am looking for a database designer and DBA to hire on. DB2 was just purchased, and will be installed very soon. Environment is IBM OS & hardware. This person will be the first DBA hired in the company, and will have to do all initial architecture, then deployment. Person will be employee number 36 ( assuming we don't hire anymore Java people in the meanwhile.. we hired another Java developer Wednesday). Development atmosphere is Java/XML. Stock options are unreal because we are still in the first round of funding. Product is completely web centric, with no physical [...] 4005 73 36_Re: Giving Instructions to Optimizer12_Hylton Tom P23_Tom.P.Hylton@M1.IRS.GOV31_Fri, 15 Oct 1999 12:57:15 -0400295_iso-8859-1 Yes, it's true that you can "hint" to the optimizer what you want, meaning you can tell it what you want, but you still aren't guaranteed to get it.

No, I haven't used it. I attended a session on it at the Tech Conference. If you attended, look for Session 017 on the CD. [...] 4079 51 18_Re: Cool:Gen - MVS9_Ray Potts23_rpotts@MAIL.STATE.MO.US31_Fri, 15 Oct 1999 12:05:33 -0500661_us-ascii Micheal,

We have three Cool:Gen encyclopedias in a standalone environment. This allows us greater control and keeps the massive generations and transformations from impacting Test or development and vice versa. The three encyclopedias are for separate projects.

Michael Bancale wrote:

> We have purchased the Cool:Gen - MVS tool set. For those who are using this > with the Repository on DB2/MVS(OS/390) where did you install it. In a > production region, test region, standalone region? > > TIA. > > Michael > > Michael Bancale > mbancale@txfb-ins.com > Texas Farm Bureau Insurance Company > www.txfb-ins.com > Waco, Texas > [...] 4131 28 86_Message from the Listowners: RE: Need DB2 architect & DBA | In Austin Texas | Offtopic19_J. Michael Morrilll21_xavier@NETEXPRESS.NET31_Fri, 15 Oct 1999 12:25:07 -0500350_iso-8859-1 Greetings!

This post was on topic as specified in the FAQ.

However, the reason I am replying to this post is because it had the word "Offtopic" in the subject line.

I know in other lists, it is permissible to make an off topic post as long as it is identified in the subject line. It is not permissible in DB2-L! [...] 4160 75 29_Re: Index vs Tablespace Scans10_Leo Flores23_leoflores@EARTHLINK.NET31_Fri, 15 Oct 1999 10:44:54 -0700361_us-ascii Derez,

Is the tablespace scan being performed on the Parent or the Child? Make sure that the Child table has an index on the Foreign Key Column. I'm not sure if the index must contain only the one column or if it is sufficient to have the column as the 1st column of a multi-column index. Can anyone on the list comment on this for DB2 V5? [...] 4236 122 27_Re: PeopleSoft 7.50 to 7.5113_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 15 Oct 1999 11:43:24 -0500596_iso-8859-1 I just got done upgrading our Peoplesoft HRMS from 7.50 to 7.51 in our production environment last night. I don't know the capabilities of the "Migrator" tool, but I've found I can generally close a Peoplesoft environment in about a half a day using BMC change manager "migrate" worklists (since I have Peoplesoft split into almost 30 different databases, I can get some pretty good concurrency on the DDL and loads to cut the time down. Good dose of Coffee for the person performing the copy doesn't hurt either). A migrate worklist can migrate both structures and data, and has [...] 4359 26 22_Walker/DB2 performance16_Brennan, Michael18_BRENNANM@PKSIS.COM31_Fri, 15 Oct 1999 13:26:41 -0500567_iso-8859-1 Everyone, We have a customer who is converting their Walker Financials (AP, GL) from a VSAM to DB2 environment running on OS/390 2.4 and DB2 v5.1. Some of their batch jobs (LE COBOL/Assembler) have experienced a 100 - 200% increase in CPU time after converting them to DB2. The Walker sales personnel told the customer they'd see approximately a 10 to 20% increase in CPU. Yeah right! I have done everything I can think of to tune their DB2 system. The application seems to be working efficiently, except for the fact it eats up a lot of CPU. They do [...] 4386 84 29_Re: Updating using substring?13_Horacio Villa17_hvilla@TTI.COM.AR31_Fri, 15 Oct 1999 15:49:07 -0300826_iso-8859-1 Or better, try set lastname = 'XXXXX' || substr(lastname,6) Horacio Villa

-----Mensaje original----- De: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]En nombre de Michael Hannan Enviado el: Miércoles 13 de Octubre de 1999 19:29 Para: DB2-L@RYCI.COM Asunto: Re: Updating using substring?



Or try set lastname = 'XXXXX' || substr(lastname,5)

Michael Hannan

>>From owner-db2-l@RYCI.COM Wed Oct 13 00:17:32 1999 >Newsgroups: bit.listserv.db2-l >Date: Tue, 12 Oct 1999 15:12:17 +0100 >Reply-To: DB2 Data Base Discussion List >Sender: DB2 Data Base Discussion List >From: Marcus Davage >Subject: Re: Updating using substring? >To: DB2-L@RYCI.COM > >Try >set lastname = 'XXXXX' || substr(lastname,5,80) >(or [...] 4471 14 26_Re: Walker/DB2 performance0_17_JTonchick@AOL.COM29_Fri, 15 Oct 1999 14:38:21 EDT372_us-ascii Sounds like one of those "common I/O module" options that treat DB2 tables like VSAM files instead of optimizing the SQL.

================================================ 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. 4486 45 26_Re: Walker/DB2 performance13_Chie, Jim (C)17_chiej@CONNEXT.COM31_Fri, 15 Oct 1999 11:39:13 -0700391_iso-8859-1 I had experience with a similar VSAM to DB2 conversion. The biggest problem was the lack of set processing. i.e.. it replaced the VSAM reads with singleton selects. The next biggest problem was selecting rows by non key fields, especially in application joins. Check your transactions for table scans. It is obviously a target rich environment for performance opportunities. [...] 4532 24 19_Recover to Copy/POT0_23_Lockwood_Lyon@AMWAY.COM31_Fri, 15 Oct 1999 14:36:10 -0400321_us-ascii I want to recover a tablespace to a particular RBA (TORBA). However, I want it to begin from a particular Image Copy about five copies back ( some intermediate IC datasets were inadvertently deleted), and do log applies from that point forward. Which combinations of parameters and multiple loads do I use? [...] 4557 55 26_Displaying Dates as spaces20_BREIDENSTINE JOHN L.21_jlb@LEGIS.STATE.PA.US31_Fri, 15 Oct 1999 15:38:27 -0400415_us-ascii Hello, I have a question concerning DATE in DB2.

1) We cannot seem to store spaces in the columns we have defined as DATE. Is this absolutely the way it has to be or is there some other way of storing spaces in there? We do not want nulls.

2) Therefore, our default on all DATES is 0001/01/01. Is there an SQL statement that will extract the 0001/01/01 dates and display them as spaces? [...] 4613 57 30_Re: Displaying Dates as spaces14_Wayne Driscoll19_wdriscoll@QUEST.COM31_Fri, 15 Oct 1999 15:12:07 -0500354_iso-8859-1 You could try using a case statement (I did this on UDB for NT 6.1 so the syntax may be slightly different)

select charcol, case when date_notnull = date('0001-01-01') then ' ' else char(date_notnull,iso) end from datetest

I had to convert the date column to char so that all rows of the result set have the same data type. [...] 4671 68 15_Re: DB2 and HSM10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Fri, 15 Oct 1999 16:21:53 -0500521_iso-8859-1 This might be of interest:

The new IBM Redbook, "Storage Management with DB2 for OS/390" (SG24-5462-00, ISBN Number 0738414204) can now be downloaded as a PDF file from http://www.redbooks.ibm.com/abstracts/sg245462.html. (You can order hardcopy four to six weeks following the publish date of September 21, 1999.



______________________________ Reply Separator _________________________________ Subject: DB2 and HSM Author: owner-db2-l@RYCI.COM at INTERNET Date: 10/14/1999 11:13 AM [...] 4740 12 38_OS/390 DB2 to AS400 DB2 using DB2 DRDA37_Les Brown (Software Staff. Solutions)22_a-lesbro@MICROSOFT.COM31_Fri, 15 Oct 1999 13:32:53 -0700458_- Does any body have a sample of the definitions needed to establish an OS/390 DB2 with an OS/400 DB2 using DRDA? Including the parameters that need to be matched up. Both the VTAM definitions and the DB2 definitions

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 4753 31 32_Asynchronous wait time too high?14_Sandi Mitchell24_smitchell@AVISTACORP.COM31_Fri, 15 Oct 1999 14:03:06 -0700516_iso-8859-1 I'm looking at a Omegamon Accounting Time Summary report for a specific batch program. The numbers are as follows: I/O Wait count 365132 time 1:26:33.737520 Async Read Wait count 136361 time 1:11:47.267038 Async Write Wait count 2 time 2.027667

It looks to me like the average I/O wait is .014 while the average Async Read Wait is .031 and the Async Write average is .077 This is totally opposite of what I expected. Am I missing something? Where should I be looking to explain these numbers? [...] 4785 76 30_Re: Displaying Dates as spaces10_Deb Jenson17_DJenson@QUEST.COM31_Fri, 15 Oct 1999 14:29:22 -0700562_iso-8859-1 You're the man!

> -----Original Message----- > From: Wayne Driscoll [mailto:wdriscoll@QUEST.COM] > Sent: Friday, October 15, 1999 1:12 PM > To: DB2-L@RYCI.COM > Subject: Re: Displaying Dates as spaces > > > You could try using a case statement (I did this on UDB for > NT 6.1 so the > syntax may be slightly different) > > select charcol, > case when date_notnull = date('0001-01-01') > then ' ' > else char(date_notnull,iso) end > from datetest > > I had to convert the date column to char so that all rows of > the result set > have the [...] 4862 63 26_Re: Walker/DB2 performance0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 15 Oct 1999 16:47:49 -0500559_us-ascii When we were moving to DB2 the real CPU requirements were 3 to 4 times greater than the estimate (which forced some rather funny scrambling to fix, like getting a new CPU installed in about 3 months, don't ask how, ...).

Remember, DB2 uses VSAM 'under the covers' to handle the raw data files that make up DB2. So DB2 functionality is added on top of basic VSAM CPU requirements. You say they don't want to touch the application source code, it implies to me also that you are using an external I/O module that maps the native VSAM calls [...] 4926 112 29_Re: Index vs Tablespace Scans10_Leo Flores23_leoflores@EARTHLINK.NET31_Fri, 15 Oct 1999 16:23:13 -0700347_us-ascii Did you perform an EXPLAIN or REBIND after your re-orgs? Have you tried using the REOPT(VARS) BIND option on the Plan/Package? According to your previous post, you only have the one where predicate, right? So the Primary index and the Partitioning index are the same index. What is the column type and what are the limit key values? [...] 5039 49 11_DB2 and HSM0_19_bkilburn@US.IBM.COM31_Fri, 15 Oct 1999 18:33:09 -0600506_us-ascii Hi Jeffrey Price,

There is a redbook entitled Storage Management with DB2 for OS/390 (SG24-5462) which covers managing DB2 recovery data sets with SMS plus other topics. This book was published in September 1999, so the information is very current. I hope it helps you.

Barbara Kilburn IBM (STL)



>Date: Thu, 14 Oct 1999 09:07:31 -0500 >From: Jeffery A Price >Subject: DB2 and HSM >MIME-Version: 1.0 >Content-Type: text/plain; charset=us-ascii [...] 5089 49 36_Re: Asynchronous wait time too high?15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Fri, 15 Oct 1999 02:02:54 -0500310_us-ascii What were you expecting synch I/O, asynch read wait, and asynch write wait to be in milliseconds?

A prefetch (asynch read I/O) is usually longer than a synch read I/O because it can xfer up to 32 pages (non utility) which is 3+ track revolutions and lots of data xfer across the channels. [...] 5139 31 26_Re: Walker/DB2 performance15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Fri, 15 Oct 1999 02:06:07 -0500502_us-ascii Rohn.Solecki@MTS.MB.CA wrote:

> Remember, DB2 uses VSAM 'under the covers' to handle the raw data files that make up DB2. So DB2 functionality is added on top of basic VSAM CPU requirements.

DB2 does NOT use any VSAM access method code to access DB2 linear datasets. They are only VSAM in name (created via IDCAMS). DB2 uses something called Media Manager. Media Manager was a clone of VSAM's I/O/channel program code that was restricted to only 4K DASD block/CI/page size. [...] 5171 26 36_Re: Asynchronous wait time too high?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sat, 16 Oct 1999 05:21:00 -0400369_ISO-8859-1 Your avg asynch read time of .031 is quite good..... but - why are you waiting for them? If you are the only job in the machine, or the processor is not busy, this will explain the wait. However, you have an obvious I/O, access, and pool tuning problem simply by looking at the amount of I/O wait time for the job compared to the overall elapsed times. [...] 5198 191 23_Re: multiple index scan4_vcei14_vcei@CEGEKA.BE31_Sat, 16 Oct 1999 16:11:53 +0200590_iso-8859-1 First, to answer this question: Is this SQL correct? (by Sibimon Philip)

The result appears to be correct although I would have implemented this query a bit :-) different in the program... We`ve been stripping down the query, but still 2 indexes are used. Index1: matched 3 of 6 columns, Index2: matched 2 of 4 columns (those 2 are identical to the first 2 of index 1). The intention is to do some index screening within index1, and definitely not use any other index. We`ve got 10 indexes already, for a table that will be heavily updated (including the indexcols). [...] 5390 28 32_format/strategy for storing data17_Yusuf Batterywala23_yusuf@CSE.IITB.ERNET.IN31_Sat, 16 Oct 1999 22:31:58 +0530516_US-ASCII hi everybody i want to use db2 for backend in building a search engine. i want to store 3 fields: term-id, document-id and locations notice that last field may be of variable length. for a term or a keyword, there maybe many documents (here i store url in fixed length i.e. 64 bit hash and also term-id is of fixed length i.e. 32 bit hash). db2 stores only key/data pairs. in this case, term-id has to be the key but obviously it cannot be the primary key. how should i store data and manipulate them? [...] 5419 99 29_Re: Index vs Tablespace Scans16_Richard A Yevich15_ryevich@IBM.NET31_Sat, 16 Oct 1999 15:41:51 -0400359_iso-8859-1 If greater than 25% of the rows will be accessed, then you should be seeing a Tablespace Scan using List prefetch. That is the triggering area for a tablespace scan since at that point it would be faster than any kind of index scan.

But in your case, without any statistics and cardinality of the index columns, this is all guess work. [...] 5519 106 26_Re: Walker/DB2 performance11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Sat, 16 Oct 1999 19:46:00 -0400363_iso-8859-1 We have Walker AR installed, and it has been modified, including the 'FAMS' which has given us some performance benefits. Going to put level 9906 for DB2 V5 which includes many of the DB2 V6 retrofits has given us some very suprising benefits. One batch job went from 4 hours down to 30 minutes because of the missmatched key length enhancements. [...] 5626 56 23_Re: Recover to Copy/POT14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Sun, 17 Oct 1999 10:53:09 +0100535_- You can do this by reokying on the fact that DB2 will keep looking for a valid image copy to start from, working backwards, until it finds one.

So

You would just do a RECOVER TORBA to the RBA you wanted to recover TO and then make sure that DB2 cannot find ANY of the intervening copies (which shouldn't be hard as you said they'd been inadvertantly deleted!). In any case, when DB2 asks for the tapes (if they are on tape) just reply "NO" and it will look for the immediately prior copy. If you DO happen to have [...] 5683 16 11_DB2 and HSM0_22_kmulcahy@CHECKFREE.COM31_Sun, 17 Oct 1999 09:53:37 -0400381_us-ascii Consider the long running batch program with infrequent commits broken -- get it fixed!

Kevin Mulcahy CheckFree Corporation

================================================ 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. 5700 86 20_Sequential Detection14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Mon, 18 Oct 1999 02:06:01 +1000623_us-ascii Sanjeev,

Sequential dectection tracks pages accessed by each object in each SQL independently (and even maintains > 1 tracking of object if used by SQL more than once).

Tracking reset by COMMIT unless Release Deallocate.

Tracking marks an access 'Page Seql.' if within 16 forward pages of previous page (on same object). If 5 out of last 8 pages have been marked 'Page Seql.', then Sequential is Detected and Dynamic Prefetch of 32 pages is instigated (if just switched on), or if already Detected, may do another Dynamic Prefetch if appropriate trigger page (or pages) has been reached. [...] 5787 31 49_Unable to shut down MSTR and DBSTR address spaces14_Max 'Guderian'13_mocion@TIN.IT31_Sun, 17 Oct 1999 18:53:46 +0200336_iso-8859-1 We installed DB2 V.5 some months ago with migrate option.

All looks OK, but now when we stop DB2 with command -STOP DB2 only IRLM stops and

the others 2 (or 3, DDF only in test) MSTR AS & DBAS do not stop. They stop to be

highlighted and you have to CANCEL address spaces. At the restart all is ok. [...] 5819 72 26_Re: Walker/DB2 performance12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Sun, 17 Oct 1999 13:45:08 -0500565_iso-8859-1 I worked on the software conversion of another package used in the banking system. This application was built on top of VSAM record level processing with I/O modules that access at a record level. The program did all filtering of data at a record level. The DBA team told management that this thing would suck wind and needed to be redesigned to take advantage of relational set processing. Well a prototype went in and sure enough it was going to be a DOG. Your CPU is going to be high because your selecting every column and bringing all the data [...] 5892 68 53_Re: Unable to shut down MSTR and DBSTR address spaces14_Danny S. Creed19_dscreed@FULLNET.NET31_Sun, 17 Oct 1999 14:29:06 -0500526_iso-8859-1 Assumptions made, that no batch job is still running etc.. I have recently seen 2 causes for this, each with an IBM ptf (hipers) which may fix the problem. The first was caused by the monitoring packages. In our case it was Insight for DB2. The ptf available also describe the problem with DB2PM as well, look in IBMLINK for I do not remember the fix. The second was caused by a cancel or force of a TSO user. You would be able to see the thread (-dis thread) but not able to find the tso address space with a [...] 5961 54 53_Re: Unable to shut down MSTR and DBSTR address spaces11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Sun, 17 Oct 1999 19:23:33 -0400429_iso-8859-1 Max,

There seem to be many issues around DDF, TCPIP, and OS/390 depending what flavor of each you are on. As I stated in an earlier DB2 connect ? the best thing to do is call IBM and get the informational APAR (post it on the list server) and proceed from there. What you are describing is very similar to what we have encountered, and the best remedy is to make sure all 3 areas are in sync with fixes... [...] 6016 170 29_Re: Index vs Tablespace Scans14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Mon, 18 Oct 1999 12:07:46 +1000418_iso-8859-1 Derez,

Another possibility is that the datatype of the primary key column and the host variable do not match exactly on datatype and length (note: there is an exception for CHAR & VARCHAR of shorter length, dependant on V5 APAR applied).

If a datatype or length conversion is required, then this would render the predicate stage 2 (non-indexable) which would result in a tablespace scan. [...] 6187 117 24_Re: Sequential Detection0_24_ssethi@LOT.TATASTEEL.COM31_Mon, 18 Oct 1999 09:13:59 +0530536_- Hi Michael, Yes, i have got the enough new details but one more thing i need to ask that what makes DB2 to choose Sequential Detection(during runtime) instead of Sequential Prefetch(during Bind Time). As i understand if due to any of the reason due for which sequential prefetch couldn't be choosen at the bind time (like Catalog Statistics is not updated or SPTH for the corresponding Buffer Pool is reached) and access to the object during run time requires to be done as explained by you then Sequential Detection is choosen. [...] 6305 87 53_Re: Unable to shut down MSTR and DBSTR address spaces13_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK31_Mon, 18 Oct 1999 09:11:00 +0100452_- Danny,

if you use any packages such as FileAid for DB2, these can leave unwanted threads lying about which you have to cancel before DB2 will come down. We have had this problem with the aforementioned FileAid and end users who don't sign off properly.

Ian McDonald DB2 DBA Abbey National ---------- From: Danny S. Creed To: DB2-L@RYCI.COM Subject: Re: Unable to shut down MSTR and DBSTR address spaces Date: 17 October 1999 20:29 [...] 6393 21 28_Re: DB2 Connect cmallc rc=200_30_Franco_Oberto@NOTES.AMDAHL.COM31_Mon, 18 Oct 1999 11:12:02 +0100419_us-ascii Ciao ,

mi sai indicare con quale software ti collerghi al db2 ?

UDB NT V 5, o Microsoft OLE DB ?

Per questi potrei aiutarti .



Franco

================================================ 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. 6415 147 33_Re: Data Encryption in Peoplesoft11_Brown, Alan20_Alan_Brown@BCBST.COM31_Mon, 18 Oct 1999 07:02:58 -0400386_iso-8859-1 Phil:

We encrypt the data going over the network, but not within DB2 itself. We use standard DB2 security (i.e. GRANT) to control who can and cannot view, update, the DB2 tables. Outside of the DBA's, no one can get to the DB2 tables without a specific grant to that table (other than by using the database creator ID which is strictly controlled and monitored). [...] 6563 77 34_Re: Web access to DB2/CICS on MVS?26_Lindsay, William (SOM400A)24_WLindsay@EXCHANGE.ML.COM31_Mon, 18 Oct 1999 08:26:56 -0400454_iso-8859-1 We use NEON Shadow Direct to connect through TCP/IP to CICS, and DB2.

From a development standpoint the DB2 access is through an ODBC driver, which can invoke batch cobol remote stored procedures. As for CICS, communication is through the COMMAREA with no proprietary calls. A SHADOW CICS Transaction Server intercepts the request through Dynamic Program Link, and acts as a layer between your backend application and your client. [...] 6641 139 29_Re: Index vs Tablespace Scans0_20_ddlusk@HOUSEHOLD.COM31_Mon, 18 Oct 1999 09:01:36 -0500479_us-ascii we've determined that there is a problem, it seems that I'm not the only one with this situation... all explains were done... index type 2.. integer s9(9) comp.









Leo Flores 10/15/99 06:23 PM



Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM cc: (bcc: Derez D. Lusk/Household International) Subject: Re: Index vs Tablespace Scans [...] 6781 59 29_Re: Index vs Tablespace Scans0_20_ddlusk@HOUSEHOLD.COM31_Mon, 18 Oct 1999 09:04:02 -0500674_us-ascii this is an integer column s9(9) comp.

type 2 index..











"Purcell, Terry" 10/17/99 09:07 PM



Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM cc: (bcc: Derez D. Lusk/Household International) Subject: Re: Index vs Tablespace Scans





Derez,

Another possibility is that the datatype of the primary key column and the host variable do not match exactly on datatype and length (note: there is an exception for CHAR & VARCHAR of shorter length, dependant on V5 APAR applied). [...] 6841 59 30_Group Buffer Pools - Overhead?0_22_BILL_GALLAGHER@PHL.COM31_Mon, 18 Oct 1999 10:08:49 -0400599_us-ascii Hi. We have an application that does a great deal of I/O intensive processing in one of their nightly batch jobs. Because of a shrinking batch window about two years ago, the physical database design and the batch program in question were re-engineered to allow for two occurrences of the program to run concurrently, with job A running for account numbers 1 through 500, and job B running for accounts 501 through 999. As many tables as possible were partitioned by account number to assure that jobs A and B would be running against separate physical files. Unfortunately, there are [...] 6901 13 16_No subject given15_Sherri Sterling23_ssterling@BPD.TREAS.GOV31_Mon, 18 Oct 1999 11:14:40 -0400263_US-ASCII Test message

================================================ 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. 6915 69 36_Re: Asynchronous wait time too high?14_Sandi Mitchell24_smitchell@AVISTACORP.COM31_Mon, 18 Oct 1999 08:00:53 -0700508_US-ASCII Joel, I have been reading everything I can get my hands on but not much about tuning is obvious to me yet. How do I narrow down if this is I/O, access a pool tuning problem or a combination of the three?

Some additional buffer pool numbers - sync read I/O 365187 getpage /read I/O 16.62 page updates 1111583 seq prefetch requests 74961 dynamic prefetch req 32146 list prefetch req 435098 prefetch pg reads 580141 bp hit % 84% no hyperpool writes, no failed anything, no immediate writes [...] 6985 28 7_DB2/C++15_Benjamin Reches12_brech@MS.COM31_Mon, 18 Oct 1999 11:19:08 -0400418_us-ascii I have a C++ program which updates DB2 tables. When I run DCLGEN on the tables, the struct(s) generated have the decimal(p,s) type for the DB2 decimal fields.

I use the IBinaryCodeDecimal class to build the decimal value and then memcpy the data into the struct. The reason I am using the class is because I read a blurp somewhere that in c++ one should be using the class not the 'decimal' type. [...] 7014 38 42_Re: OS/390 DB2 to AS400 DB2 using DB2 DRDA0_14_dcreed@CSC.COM31_Mon, 18 Oct 1999 09:35:22 -0500588_us-ascii Manual that is good is Distributed Relational Database Architecture Connectivity Guide sc26-4783 Regards Danny







a-lesbro@microsoft.com on 10/15/99 03:32:53 PM

Please respond to DB2-L@ryci.com

To: DB2-L@ryci.com cc: (bcc: Daniel S Creed/HI/CSC) Subject: OS/390 DB2 to AS400 DB2 using DB2 DRDA





Does any body have a sample of the definitions needed to establish an OS/390 DB2 with an OS/400 DB2 using DRDA? Including the parameters that need to be matched up. Both the VTAM definitions and the DB2 definitions [...] 7053 88 34_Re: Group Buffer Pools - Overhead?13_Adrian Savory34_Adrian.Savory@CCMAIL.EU.SONY.CO.JP31_Mon, 18 Oct 1999 16:52:40 +0100337_ISO-8859-1 Bill,

The thing I'd be most concerned about is the size of your CF structures. How big are the tables that will be subject to inter-DB2 read-write interest? What locking regime is used - row or page? There may be a significant hit on your lock structure which could cause false contention if it's sized too small. [...] 7142 56 18_Re: Cool:Gen - MVS16_Galluzzo, Lori A21_lori.galluzzo@EDS.COM31_Mon, 18 Oct 1999 11:18:22 -0500333_- We have two Cool Gen encyclopedias in a production region with many other Cool Gen encyclopedias for our company. The production region was chosen for performance priority & application needs. We have a large number of Cool Gen programmers (50-100), major software releases & time constraints (International & US workgroups). [...] 7199 70 36_Re: Asynchronous wait time too high?13_Michalik, Ken19_kmichalik@KRAFT.COM31_Mon, 18 Oct 1999 11:56:14 -0500578_- I also heard this at the 98 DB2 Conference--- Asynchronous time should be nearly zero! DB2 is supposed to get the data for you before you need it.

My own impression of List Prefetch is try to not use it whenever possible. This is because (and real experts jump in here) I believe that the channel program runs while there is physical actuator movement/platter rotation and waits for everything to complete. DB2 sets a limit on how many pages will be read with one list prefetch...again this is just a vague memory...this was implemented later so that it would not [...] 7270 23 34_Data Warehouse and surrogate keys.15_Backes, Michael26_BackeM@MAIL.OA.STATE.MO.US31_Mon, 18 Oct 1999 12:06:42 -0500535_iso-8859-1 An issue has come up with our data warehouse project dealing with the use of surrogate keys. That is replacing the data on the fact table with a made up generated key. http://www.dbmsmag.com/9805d05.html has an article on it but im still not convinced that the use of them is as good an idea as they are made out to be. To me it seems like adding a bunch of generated data to the data you already have is just creates more of the problems this is supposed to solve. Maybe i am just not seeing the forest because of the [...] 7294 51 38_Re: Data Warehouse and surrogate keys.0_21_FNovak@SCHOLASTIC.COM31_Mon, 18 Oct 1999 13:39:32 -0400469_- You have to watch that you don't over use surrogate keys because it can cause performance problems not to note all those joins. In a data warehouse application where you have data requirements over a long time periods it does prevent major database reorg's when there is a change. Using the surrogate key will allow your fact tables (your largest tables) to stay stable and allow you to key changes in the key specific attributes over time in a snowflake table. [...] 7346 68 19_Re: DSNDBM1 Storage13_Michalik, Ken19_kmichalik@KRAFT.COM31_Mon, 18 Oct 1999 13:07:25 -0500471_- I agree with the seldom released idea. But, if this hasn't been said already, the total virtural storage usage of any address space is the sum of central+expanded+page dataset pages. RMF3 gives you all 3 average values simultaneously, so you can add them up. The location of all of the DBM1 virtual storage might change with time, depending on how much is present and what the demand is from other address spaces. The memory resources are managed globally by MVS. [...] 7415 30 38_Re: Data Warehouse and surrogate keys.0_29_Lisa_Westcott-Dryer@AMWAY.COM31_Mon, 18 Oct 1999 13:47:34 -0400330_us-ascii Good question. I am wary of surrogate keys and "data warehouse" too. It seems to me that surrogate key and data warehouse are oxymoronic. Since surrogate key(s) inheritantly have no business value.

Here's a couple of questions. If you can get answers to them, maybe this will help. I'd be interested to know. [...] 7446 32 46_DB2 5.1/OS390 -- Anyone have Timeout > 60sec??10_Mann, Tony19_tmann@UTILICORP.COM31_Mon, 18 Oct 1999 13:51:55 -0500504_- I'm finally getting into running On-Line Reorgs but - despite otherwise excellent results - have some user push back because of the occasional -904 unavailable resource errors they receive during the Switch phase. ((They want 24*7 and perfect performance.)). The (third party) application includes partitioned tables with wide rows and many NPIs (eg one table with 20 indexes): switch phase can take a while. I'm at put 9905, which brought some benefits but still results in timeouts on occasion. [...] 7479 24 9_DDF again11_Kenneth Lam14_klam@BTMNA.COM31_Mon, 18 Oct 1999 15:45:40 -0400588_us-ascii Does anyone encounter a problem with SQL0805 with package "DBWT.NULLID.SQLL18N5.4A41496C58654A4F" SQLSTATE=51002. I got this message when I used UDB 5.2 for NT (from Command Center) to issue SQL to UDB 6.1 for OS/390. Of course the background is using DDF with TCP/IP. Can someone gives me some insight?

In few days ago during the DDF discussion, Philip Gunning has suggested that I should check with the dataset in MVS for the TCPIP. This has resolved some of my problems, but still I can not issue SQL from UDB NT to UDB OS/390. Is it I need to bind some packages. [...] 7504 52 13_Re: DDF again12_Myron Miller24_myron_miller@HOTMAIL.COM29_Mon, 18 Oct 1999 13:14:35 PDT319_- As part of the install process for DB2 connect (or when you upgrade from one version to another), you must BIND all of the NULLID collection on the host. There is a panel in the CLIENT CONFIGURATION ASSISTANT that allows you to BIND all of the PACKAGES (assuming that you have SYSADM authority to do this BIND). [...] 7557 18 25_replication + data joiner11_David Ayers24_david.ayers@HIGHMARK.COM31_Mon, 18 Oct 1999 16:19:48 -0400378_us-ascii We are looking at replicating data from DB2 v5.1 OS/390 to DB2 v5.1 OS/390 using data joiner. We have the DPROP/R CAPTURE (MVS) running. Do we need the Apply for MVS if we have data joiner (e.g. target tables of DB2 v5.1 OS/390 defined in data joiner as nicknames). I know that this is a round about solution (os/390 - to aix - back to os/390), but is it possible [...] 7576 77 30_Re: Displaying Dates as spaces15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Mon, 18 Oct 1999 16:27:56 -0400503_iso-8859-1 You may be able is Value(nullif(date_col1,'0001-01-01'),' ').

Thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: Wayne Driscoll [mailto:wdriscoll@QUEST.COM] Sent: Friday, October 15, 1999 3:12 PM To: DB2-L@RYCI.COM Subject: Re: Displaying Dates as spaces



You could try using a case statement (I did this on UDB for NT 6.1 so the syntax may be slightly different) [...] 7654 66 27_Re: dsn1copy (small mayday)11_Kwan, James18_James_Kwan@BMC.COM31_Mon, 18 Oct 1999 16:46:44 -0500447_ISO-8859-1 -----Original Message----- From: Derek Lund [mailto:derek.lund@UTORONTO.CA] Sent: Wednesday, October 13, 1999 11:22 PM To: DB2-L@RYCI.COM Subject: dsn1copy (small mayday)



I am looking for advice from fellow digesters:

Problem: Users (and managment) have deleted a small amount of "very important" data on a large partitioned table 3 days ago. They REALLY would like to get the data back. (We are on DB2 V4.1) [...] 7721 37 36_Re: Asynchronous wait time too high?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 18 Oct 1999 17:44:50 -0400283_ISO-8859-1 Out of a total job elapsed time of 3 Hrs 57 mins, you have I/O wait time of 2 Hrs 37 Mins..

You have a job that is I/O constrained.

I don't believe your BP hit ratio is 84%, but you have not provided the necessary data to calculate this.... Formula is: [...] 7759 76 27_Re: dsn1copy (small mayday)11_Kwan, James18_James_Kwan@BMC.COM31_Mon, 18 Oct 1999 16:55:49 -0500478_ISO-8859-1 Derek

There were couple of good suggestions by others. Just like to remind you that the data could have been updated between day 5 and day 3. Therefore if you want to obtain the correct data, you might want to copy data from all 3 image copies tapes.



James Kwan

-----Original Message----- From: Derek Lund [mailto:derek.lund@UTORONTO.CA] Sent: Wednesday, October 13, 1999 11:22 PM To: DB2-L@RYCI.COM Subject: dsn1copy (small mayday) [...] 7836 75 27_Re: dsn1copy (small mayday)0_19_csutfin@AMSOUTH.COM31_Mon, 18 Oct 1999 16:56:18 -0500292_- Having done the same thing at one time: I can advise you that you don't need to do step 3.

Just apply the full image copy, then the incremental image copies (in the proper sequence). Once you finish that then recover your indexes. No need to recover them between each DSN1COPY. [...] 7912 59 33_Questions about Stored Procedures18_Centro de Computos21_iplc05@INFOVIA.COM.AR31_Mon, 18 Oct 1999 19:55:51 -0300388_us-ascii Hi all: I'm having problems to understand and implement stored procedures in DB2, where they are far more complicated than in the SYBASE environment I used in my previous employment. The point is performance improvement, not reusability of application logic. I will maybe repeat many questions that probably have been made to the list, I hope this does not annoise anybody. [...] 7972 30 22_DDF Connection options0_14_DPetro@AOL.COM29_Mon, 18 Oct 1999 21:12:09 EDT519_us-ascii We are considering DDF to replace a Shared Read-Only Database. The systems folks are testing the traditional Alias/3-part table name structure as well as using TCP/IP and the CONNECT facilities. They heard at one of the conferences that IBM may eventually be doing away with the 3-part table name construct so are leaning towards TCP/IP. As I understand, the difference is that using TCP/IP and IPNAMES table in the comm. database, will require explicit CONNECTS to the remote database. My questions are: [...] 8003 50 13_Re: DDF again13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 18 Oct 1999 15:44:02 -0500349_US-ASCII Kenneth, You need bindadd and create in collection NULLID authorization at the server. Then from the CCA or CLP connect to the database and bind the plans. The CCA has a radio button to do the binds for you or you can use the CLP and issue the bind command : BIND C:\SQLLIB\BND\@ddcsmvs.lst BLOCKING ALL SQLERROR CONTINUE GRANT PUBLIC [...] 8054 59 36_Re: Asynchronous wait time too high?15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Sun, 17 Oct 1999 22:43:32 -0500551_us-ascii Sandi Mitchell wrote: >

Are these numbers JUST for the batch job you are talking about? I remember Joel mentioning before that I/O parallelism can cause HUGE IOS queue times to a volume if you have related partitions on the same volser.

Regardless, in 2 Hr 36 Min, doing approx. 9400 seconds. If you do 40 I/Os per second (1 second / .025 second response time = 40 per sec), you can do about 375,000. I/O response time of .025 is fairly acceptable though not really, really good. This would cover your synch read I/O only. [...] 8114 84 36_Re: Asynchronous wait time too high?10_Ron, Yoram12_rony@ANZ.COM31_Tue, 19 Oct 1999 13:44:28 +1000577_iso-8859-1 From the figures (prefetch requests/prefetch page reads) it seems like the pages are being prefetched (32 page per request), but, not used. The application has a constant 'background' noise of sequential (/list) prefetch that does not make it any faster (all the pages needed are actually read by a sync read). One way to stop that noise is to switch the prefetch off (you can allocate the table in question to a separate buffer pool, and set the seq-threshold to 0), I remember doing it once in the past. Once the 'noise' is gone, the sync reads can go faster [...] 8199 83 26_Re: DDF Connection options14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Tue, 19 Oct 1999 13:54:50 +1000468_us-ascii Dave,

1) I doubt (but that's no guarantee) that IBM will be doing away with 3 part names. Up to V5 they were only available using private protocol and the IBM direction was/is to use DRDA for intersystem communication. However, in V6, DB2 allows use of 3 part names using DRDA. It is unlikely that this would have happened if 3 part names were going to be removed. (It is entirely possible that private protocol will be dropped from DB2 after V6) [...] 8283 44 24_Re: DDF again correction13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 18 Oct 1999 17:53:58 -0500491_US-ASCII Sorry that's bindadd and create in collection NULLID authorization at the HOST not the server. HTH Kurt



>>> Kenneth Lam 10/18/99 02:45PM >>> Does anyone encounter a problem with SQL0805 with package "DBWT.NULLID.SQLL18N5.4A41496C58654A4F" SQLSTATE=51002. I got this message when I used UDB 5.2 for NT (from Command Center) to issue SQL to UDB 6.1 for OS/390. Of course the background is using DDF with TCP/IP. Can someone gives me some insight? [...] 8328 91 30_Re: Displaying Dates as spaces0_24_ssethi@LOT.TATASTEEL.COM31_Tue, 19 Oct 1999 11:21:51 +0530291_us-ascii Yes, u can write the SQL statement which can display some date as spaces but the data type will be CHAR not the DATE

the sample query is

SELECT (CASE COL1 WHEN '0001-01-01' THEN ' ' ELSE COL1 END) AS DATE_SPACES FROM ( SELECT CHAR(T1.COL) AS COL1 FROM T1 ) AS V1 [...] 8420 36 25_Index vs Tablespace Scans13_Paul Fletcher28_Paul_L_Fletcher@BISCUITS.COM31_Tue, 19 Oct 1999 07:40:16 +0000358_us-ascii Derez,

If list prefetch was chosen as the access path, then DB2 may well start off using the index.

If it finds that it has accessed more than 25% of the rows then it stops using the index and switches to a tablespace scan instead.

It does this because it has decided that it would be too costly to continue with the sort. [...] 8457 61 42_Re: OS/390 DB2 to AS400 DB2 using DB2 DRDA12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Tue, 19 Oct 1999 11:33:35 +0200667_windows-1253 A very good manual also is the redbook: Distributed Relational Database Cross-Platform Connectivity and Applications (SG24-4311). It contains configuration examples (network, relational database directory, security e.t.c) for DRDA connectivity for OS/390, AS/400, OS/2 and AIX platforms. Chapters 8,14 are for Db2 for OS/400 platform and Chapter 10,16 for MVS platform. I believe you can find it online at IBMs site for redbooks. Gina Fertaki DBA EFG Eurobank Greece -----Original Message----- From: dcreed@CSC.COM [mailto:dcreed@CSC.COM] Sent: Monday, October 18, 1999 4:35 PM To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 to AS400 DB2 using DB2 DRDA [...] 8519 66 36_Re: Giving Instructions to Optimizer16_Richard A Yevich15_ryevich@IBM.NET31_Tue, 19 Oct 1999 07:25:05 -0400551_iso-8859-1 Sanjeev,

Number one, this is a feature that is only meant to be used in a very few extreme cases where absolutely needed to maintain an existing access path over changes to DB2 which would cause the access path to change. There are many rules involved such as if you define list prefetch, sorts, they would be ignored. The path listed in PLAN_TABLE is validated as to whether it can be used or not, since you would be able to put index names in there which in fact did not exist. If a valid basic access path is specified, that [...] 8586 25 13_Bind question11_Eric Robida22_Eric_Robida@BCBSME.COM31_Tue, 19 Oct 1999 08:31:05 -0400572_us-ascii I have a question about binds. We have DB2v5 on OS390. Sunday, a DBA dropped a table, changed a column definition and placed the changed table in production. Before the table was dropped there were about 27 programs that would have had their plans disabled by the drop. This occurred as expected. What was unexpected is that 6 programs appear to have rebound themselves when they began their normal scheduled run. The DBA's are at a loss as to why the programs ran with an initially disabled plan. Any ideas? Any help would be appreciated. Eric PS I'm not a [...] 8612 43 17_Re: Bind question14_Alan Dishowitz23_adishowitz@RURALINS.COM31_Tue, 19 Oct 1999 08:00:25 -0500616_- There is a Zparm (a DB2 install parameter) Auto Bind which instructs DB2 to rebind invalid plans/packages at execution time. The parameter defaults to YES.

Alan Dishowitz CSC, FSG

> -----Original Message----- > From: Eric Robida [SMTP:Eric_Robida@BCBSME.COM] > Sent: Tuesday, October 19, 1999 7:31 AM > To: DB2-L@RYCI.COM > Subject: Bind question > > I have a question about binds. We have DB2v5 on OS390. Sunday, a DBA > dropped a table, changed > a column definition and placed the changed table in production. Before the > table was dropped there were > about 27 programs that would have had [...] 8656 14 14_UDB Smartguide0_22_wjmartin@HOUSEHOLD.COM31_Tue, 19 Oct 1999 09:14:09 -0400433_us-ascii Does anyone know if there is a way to change the default path for the column list file (DB2COL.LST) so that it can be placed onto a common disk. The default is C:\SQLLIB\BIN. 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. 8671 45 17_Re: Bind question13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 19 Oct 1999 11:36:46 -0400413_iso-8859-1 Eric, Alan, Note there are 2 columns in SYSIBM.SYSPLAN/SYSPACKAGE tables pertinent here. A plan or package can be valid/invalid and operative/inoperative. (the columns in the tables are name Valid(containing Y,N, A, or H) and Operative(containing Y or N).

If a plan/package is merely invalid and if Autobind is yes, when the plan/package is next invoked, an attempt is made to autobind it. [...] 8717 34 18_Re: Cool:Gen - MVS12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Tue, 19 Oct 1999 10:44:00 -0400492_- We have the products in 2 DB2s. One is a test DB2 on a test LPAR and the other is in a Development DB2 on a development/production LPAR. We use the test one for testing fixes and new releases before moving it to the Development LPAR.

>>> DB2-L@RYCI.COM@inter2 10/15/99 10:01AM >>> We have purchased the Cool:Gen - MVS tool set. For those who are using this with the Repository on DB2/MVS(OS/390) where did you install it. In a production region, test region, standalone region? [...] 8752 149 23_Re: multiple index scan14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 20 Oct 1999 02:01:15 +1000700_us-ascii It is quite possible for Optimizer to make silly choices when using Multi-index Scans. Even more likely with non-optimal SQL.

From: Michael Hannan

>From: vcei >Subject: Re: multiple index scan >To: DB2-L@RYCI.COM > >Well, I really hope I don`t give you a shock, but the query looks like this. >My conclusion at first, second, ... glance is: nuts! Distinct x ; group by >x,y,x !?!? >Selecting only ILOT,DLOT,KLOT (incl. grouping by) resolves the problem. >I think I`ll have a closer look at it with the programmer. >index1 : DVERTREK,KRTSTP,RVERTREK,FPREPDOC,IACTOR,KSTBOOK >index2 : DVERTREK,KRTSTP,PLAATEFF,ITPTPLAAT > >The problem appears to be solved, [...] 8902 23 27_prefix.SDSNLOAD in LNKLSTxx15_Jeffery A Price17_jprice@IPALCO.COM31_Tue, 19 Oct 1999 11:11:19 -0500358_us-ascii Dear List,

I am considering putting our production DB2 loadlib in the linklist concatenation (or having LLA manage) for improved performance. I would assume that this is the ideal situation, due to the fact that this dataset has a very large number of I/O activities. Does anyone have any caveats or words of encouragement for doing so? [...] 8926 133 17_Re: Bind question13_Tierney, Mark20_mtierney@FIRSTAM.COM31_Tue, 19 Oct 1999 11:11:57 -0500611_- Hello Eric,

It probably AUTOBOUND since it was an invalid plan.

ABIND=YES in zparm

> -----Original Message----- > From: Eric Robida [SMTP:Eric_Robida@BCBSME.COM] > Sent: Tuesday, October 19, 1999 7:31 AM > To: DB2-L@RYCI.COM > Subject: Bind question > > I have a question about binds. We have DB2v5 on OS390. Sunday, a DBA > dropped a table, changed > a column definition and placed the changed table in production. Before the > table was dropped there were > about 27 programs that would have had their plans disabled by the drop. > This occurred as expected. > What was unexpected [...] 9060 27 9_NT vs AIX14_Philip Gunning20_pgunning@BOSCOVS.COM31_Tue, 19 Oct 1999 12:17:43 -0400561_- I am looking for some TPC-C benchmarks or user experiences on UDB on NT. I am doing a study to determine if NT can provide the transaction throughput and availability that we need for a receiving system that is being replaced. I know that DB2 UDB EEE on AIX can provide the reliability and performance that we need; I need to determine whether or not NT can. I am finding that there is concern with reliability/availability with NT. The receiving system runs our Distribution Center and must be very reliable. I would share the metrics with you but I am [...] 9088 41 31_Re: prefix.SDSNLOAD in LNKLSTxx14_Philip Gunning20_pgunning@BOSCOVS.COM31_Tue, 19 Oct 1999 12:19:43 -0400673_- We do that here and it is recommended.

Phil Gunning DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Certified Solutions Expert -- CICS TS

-----Original Message----- From: Jeffery A Price [SMTP:jprice@IPALCO.COM] Sent: Tuesday, October 19, 1999 12:11 PM To: DB2-L@RYCI.COM Subject: prefix.SDSNLOAD in LNKLSTxx

Dear List,

I am considering putting our production DB2 loadlib in the linklist concatenation (or having LLA manage) for improved performance. I would assume that this is the ideal situation, due to the fact that this dataset has a very large number of I/O activities. Does anyone have any caveats or words of encouragement [...] 9130 20 20_SQLCODES -904 & -91316_Santhana Lakshmi19_msl19037@ICIL.CO.IN31_Tue, 19 Oct 1999 21:47:33 +0530522_iso-8859-1 Hi Everybody,

I have a logic which will perform the same SQL Statement when the SQLCODE is -904 or -913. I would like to know when a Fetch operation is performed and we get above Sqlcodes, will the Cursor be still opened?

Thanks & Regards,

Lakshmi.

================================================ 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. 9151 73 31_Re: prefix.SDSNLOAD in LNKLSTxx10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU31_Tue, 19 Oct 1999 09:31:02 -0700682_us-ascii >At 11:11 AM 10/19/99 -0500, you wrote: >Dear List, > >I am considering putting our production DB2 loadlib in the linklist >concatenation (or having LLA manage) for improved performance. I would assume >that this is the ideal situation, due to the fact that this dataset has a very >large number of I/O activities. Does anyone have any caveats or words of >encouragement for doing so? > >Thanks In Advance, >Jeffery A. Price >Associate Engineer >Indianapolis Power and Light Co. > >================================================ >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 [...] 9225 41 24_Re: SQLCODES -904 & -91315_Philip, Sibimon19_PhilipS@SEALAND.COM31_Tue, 19 Oct 1999 12:43:55 -0400612_iso-8859-1 It will close the cursor for both this error.

Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: Santhana Lakshmi [mailto:msl19037@ICIL.CO.IN] Sent: Tuesday, October 19, 1999 11:18 AM To: DB2-L@RYCI.COM Subject: SQLCODES -904 & -913 Importance: High



Hi Everybody,

I have a logic which will perform the same SQL Statement when the SQLCODE is -904 or -913. I would like to know when a Fetch operation is performed and we get above Sqlcodes, will the Cursor be still opened? [...] 9267 111 31_Re: prefix.SDSNLOAD in LNKLSTxx0_22_kls_tech@IX.NETCOM.COM31_Tue, 19 Oct 1999 12:45:13 -0400325_iso-8859-1 Why not create a dataset alias to point to ****.SDSNLOAD and use that in your execution jcl (if you must). Then when you are testing DB2 maintenance or a new version, you can simply point to the new libraries explicitly. When you are ready to implement you just change the alias to point to your new dataset. [...] 9379 74 28_FW: Loading LARGE table NPIs17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Tue, 19 Oct 1999 13:19:30 -0400340_- Is there a way to get the DIAGNOSE to stop after a message id is found WITHOUT abending? It's an interesting suggestion to stop after the load phase is complete and then run concurrent jobs to rebuild the indexes, but having a scheduled production job that is supposed to ABEND will cause havoc with our scheduling and oncall folks. [...] 9454 22 47_Re: PLAN_TABLE query for cleaning up versions ?14_Matthew Miller23_Matthew.Miller@WCOM.COM31_Tue, 19 Oct 1999 11:04:18 -0600563_iso-8859-1 Hi -

Does anyone have a query that would clean up the PLAN_TABLE ? I want to retain the 2 most current versions only, deleting any others.

I haven't begun to crank out any SQL, I was hoping that someone has already thought of this and would share the query.

Thanks, Matthew.Miller@wcom.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9477 114 31_Re: prefix.SDSNLOAD in LNKLSTxx15_Mike Jakubowski16_mjakubow@CME.COM31_Tue, 19 Oct 1999 12:36:25 -0500590_- Jeffrey,

As long as your production DB2 subsystems are on different MVS images from your other DB2's, it should be OK to put the DB2 loadlib in the linklist. We have 3 DB2's ("system programmer playground", development, and QA) on one MVS system and a single production DB2 on another. Each MVS system's linklist has an entry for that system's DB2 loadlib (a standard name that we never change) . When we put on maintenance, it affects all three non-production databases at the same time. When we're confident that the maintenance is OK, we then migrate it to production. For [...] 9592 17 23_Re: loading and indexes16_Kittell, Chris J18_ckittell@AMFAM.COM31_Tue, 19 Oct 1999 12:47:56 -0500378_iso-8859-1 Drop whatever indexes are appropriate, load, create indexes defer yes, rebuild indexes in parallel Just an idea Chris Kittell

================================================ 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. 9610 52 14_Help from NEON11_David Brown15_brown@CS.UA.EDU31_Tue, 19 Oct 1999 12:52:40 -0500501_iso-8859-1 DB2-L Discussion Group:

I posted a message a couple weeks back with regard to some problems that we were having in making a conversion that involved Neon and several other new system components, both hardware and software.

We were very pleased with the response and help that we got from Neon. They were very helpful and identified several problem areas and made several suggestions, some that we have already implemented, and others that we are continuing to look into. [...] 9663 74 36_Re: Asynchronous wait time too high?14_Sandi Mitchell24_smitchell@AVISTACORP.COM31_Tue, 19 Oct 1999 09:44:15 -0700312_US-ASCII The numbers are from Omegamon reports run for that specific planname. So yes, the numbers are just for that one batch job. SYSPACKDEP shows 11 indexes and 11 tables, none of which are partitioned. I'm am going to look at dataset placement. That is an area that we haven't paid enough attention to. [...] 9738 91 31_Re: prefix.SDSNLOAD in LNKLSTxx11_Hilton Tina15_HiltonT@TCE.COM31_Tue, 19 Oct 1999 13:09:19 -0500694_iso-8859-1 I keep DB2 out of linklist for the same reason Bill does.

Tina Hilton Thomson Consumer Electronics -----Original Message----- From: Bill Welch [mailto:spgwdw@UCLINK4.BERKELEY.EDU] Sent: Tuesday, October 19, 1999 11:31 AM To: DB2-L@RYCI.COM Subject: Re: prefix.SDSNLOAD in LNKLSTxx



>At 11:11 AM 10/19/99 -0500, you wrote: >Dear List, > >I am considering putting our production DB2 loadlib in the linklist >concatenation (or having LLA manage) for improved performance. I would assume >that this is the ideal situation, due to the fact that this dataset has a very >large number of I/O activities. Does anyone have any caveats or words of >encouragement for [...] 9830 21 34_Re: Web access to DB2/CICS on MVS?12_Boe Franklin19_boe.franklin@DB.COM31_Tue, 19 Oct 1999 13:11:40 -0700491_- I am looking at the same thing. Currently we are looking at CICS Web interface of TransactionServer and Websphere for JAVA servlets (can access DB2v5 via CLI and JDBC). Also NetData for DB2 connectivity works (formally DB2WWW). IBM's e-business area has some interesting examples at http://www.s390.ibm.com/nc/download.html (and also click on Tech Info) and some links to various other vendors on the ADTools for S390 page http://www1.s390.ibm.com/products/adtools/toolkit/index.html [...] 9852 198 74_DB2 Restore DB problem (SQL0968C The file system is full. SQLST ATE=57011)11_Jakobs, Jef18_JJakobs@HARTIS.COM31_Tue, 19 Oct 1999 13:26:47 -0500339_iso-8859-1 Hi Everybody,

I've run into a strange problem restoring my DB2 5.2 (without any fixpaks) database on AIX.

I run into: SQL0968C The file system is full. SQLSTATE=57011 This happens when I do a redirected restore to a database of exactly the same size...

I'm trying to create a new database from backup. [...] 10051 26 45_DB2 UDB DBA Contract Opportunity - Boston, MA0_18_jtulman@MAAINC.COM31_Tue, 19 Oct 1999 14:42:07 -0400544_us-ascii Ladies and Gentlemen, A client of mine located in the Boston, Massachusetts area is looking for a highly qualified UDB DB2 DBA for a minimum of 6 months on a consulting basis. The client is looking for this person to install UDB ver 6.1 as well as Administer version 5.2 of UDB on the AIX/RS6000 platforms. Experience with UDB EE or EEE as well as IBM Certifications would be a huge plus but not required. Client is looking for this person to start no later than 2 weeks from interview. We can work with both Corp to Corp as well [...] 10078 15 13_Drop Database12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Tue, 19 Oct 1999 13:46:00 -0400468_- Is there any way to drop a database which has stogroup defined DSNs without the DSNs in existence? Someone here had a set of DASD packs initialized without dropping the DB2 Database/tablespaces/etc.

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. 10094 46 49_Re: DB2 UDB DBA Contract Opportunity - Boston, MA13_Lynne Flatley17_LFlatley@NEFN.COM31_Tue, 19 Oct 1999 15:06:29 -0400585_- Hi Justin, this is Lynne Flatley. How are you? My UDB experience (2+ years) was on OS/2. UDB was formerly known as Common Server. Let's talk. I'll be home after 5:30 or so tonight.

> -----Original Message----- > From: jtulman@MAAINC.COM [SMTP:jtulman@MAAINC.COM] > Sent: Tuesday, October 19, 1999 2:42 PM > To: DB2-L@RYCI.COM > Subject: DB2 UDB DBA Contract Opportunity - Boston, MA > > Ladies and Gentlemen, > A client of mine located in the Boston, Massachusetts area is looking > for a > highly qualified UDB DB2 DBA for a minimum of 6 months on a consulting > basis. [...] 10141 13 53_Recall: DB2 UDB DBA Contract Opportunity - Boston, MA13_Lynne Flatley17_LFlatley@NEFN.COM31_Tue, 19 Oct 1999 15:06:51 -0400340_- Lynne Flatley would like to recall the message, "DB2 UDB DBA Contract Opportunity - Boston, MA".

================================================ 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. 10155 34 0_11_Robert Jans21_Jans.Robert@AMSTR.COM31_Tue, 19 Oct 1999 12:07:00 -0600334_- Certainly is. Just use the DROP DATABASE command. This will work fine. We use SMS-managed storage and have our unused test database files migrate off after about 5 days. When we need to drop a database, we HDELETE the physical datasets and then issue the drop. This avoids waiting for the datasets to recall. It's also faster. [...] 10190 130 31_Re: prefix.SDSNLOAD in LNKLSTxx0_22_kls_tech@IX.NETCOM.COM31_Tue, 19 Oct 1999 15:08:04 -0400413_iso-8859-1 You are missing out on a BIG performance boost by doing this. I would think that this would be toward the top of the list in DB2 performance tuning. You do have at least SDSNLINK in your LNKLST don't you? See...2.4.3.2.2 Choosing Link List Options in http://www.s390.ibm.com/bookmgr-cgi/bookmgr.cmd/BOOKS/DSNIG0F5/2%2e4%2e3%2e2 %2e2?SHELF=DSNSH0F5#HDRDLNKLIT [Caution: URL may split] for details. [...] 10321 59 0_18_Sundaresan, Sundar33_Sundar.Sundaresan@SEA.SIEMENS.COM31_Tue, 19 Oct 1999 15:13:20 -0400575_- Ditto for the drop.

If you are on a RVA / SVA, the person who ran the init needs to release the back end space occupied by your DB2 datasets with DDSR. ( and to bring down the NCL )

Sundar Sundaresan Siemens

> -----Original Message----- > From: Robert Jans [SMTP:Jans.Robert@AMSTR.COM] > Sent: Tuesday, October 19, 1999 2:07 PM > To: DB2-L@RYCI.COM > Subject: > > Certainly is. Just use the DROP DATABASE command. This will work fine. We > use > SMS-managed storage and have our unused test database files migrate off > after > about 5 days. When [...] 10381 42 17_Re: Drop Database14_jack fernicola20_bowling730@YAHOO.COM31_Tue, 19 Oct 1999 12:28:59 -0700648_us-ascii If there isn't many, allocate dummy datasets through vsam defines or you can do an alter tablespace with the USING option, pointing to the VCAT instead of stogroup.

Hope this helps. Jack

--- BOB JEANDRON wrote: > Is there any way to drop a database which has > stogroup defined DSNs without > the DSNs in existence? > Someone here had a set of DASD packs initialized > without dropping the DB2 > Database/tablespaces/etc. > > Thanks in advance. > > ================================================ > To change your subscription options or to cancel > your subscription visit the DB2-L webpage at [...] 10424 24 45_index scans suddenly becomes tablespace scans15_Sherri Sterling23_ssterling@BPD.TREAS.GOV31_Tue, 19 Oct 1999 15:35:58 -0400297_US-ASCII After applying UQ31481 from PUT9907 maintenance, we have noticed that queries that do an outer join have suddenly changed access paths. They use to do an index scan and now they do a tablespace scan. We were fortunate enough to catch this before our 8 billion row table was scanned. [...] 10449 32 17_Re: Drop Database0_17_JTonchick@AOL.COM29_Tue, 19 Oct 1999 15:49:02 EDT459_us-ascii In a message dated 10/19/99 2:01:12 PM Central Daylight Time, BOB.JEANDRON@USDA.GOV writes:

<< Is there any way to drop a database which has stogroup defined DSNs without the DSNs in existence? Someone here had a set of DASD packs initialized without dropping the DB2 Database/tablespaces/etc.

Thanks in advance. >>

Bob,

Just use DSN1COPY from any full image copy and recreate the tablespace files. Then do the drop. [...] 10482 12 23_referential constraints0_27_Bud.Greenman@ONONDAGA.NY.US31_Tue, 19 Oct 1999 16:06:55 -0400435_- A coworker has a table with the 'on delete cascade' restraint on it pointing to the parent table. However when the parent row is deleted the child is not. Any ideas on why this isn't working?

================================================ 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. 10495 44 27_Re: referential constraints26_Lindsay, William (SOM400A)24_WLindsay@EXCHANGE.ML.COM31_Tue, 19 Oct 1999 16:11:17 -0400488_iso-8859-1 Does the child have a dependancy on any other parent ?

Bill Lindsay

Project Manager - Web Development Merrill Lynch Global Data Sourcing and Delivery 400 Atrium Drive - 5S51A Somerset NJ 08873-2861

1 (732) 764-3464 (Work) 1 (888) MERRIL-0 (Pager



-----Original Message----- From: Bud.Greenman@ONONDAGA.NY.US [mailto:Bud.Greenman@ONONDAGA.NY.US] Sent: Tuesday, October 19, 1999 4:07 PM To: DB2-L@RYCI.COM Subject: referential constraints [...] 10540 221 77_Re: DB2 Restore DB problem (SQL0968C The file system is full. SQLSTATE=57011)13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 19 Oct 1999 14:02:45 -0500395_ISO-8859-1 Did you alter the log file size and # of log primary and log secondary logs when you created the newtest db. Make sure you have sized the logs correctly. Just a thought. Kurt







>>> "Jakobs, Jef" 10/19/99 01:26PM >>> Hi Everybody,

I've run into a strange problem restoring my DB2 5.2 (without any fixpaks) database on AIX. [...] 10762 73 27_Re: referential constraints10_Nash Gupta19_nashgupt@US.IBM.COM31_Tue, 19 Oct 1999 16:35:11 -0400408_us-ascii If the child table has more than 2 parents try using the 'after delete' trigger... If table x has two parents table y and table z then use the trigger below. A trigger should be written for each parent in such a case.

create trigger TR_CLEAN_UP after delete on TABLE Y referencing old as oldrow for each row mode db2sql delete from TABLE X where field_of_child = oldrow.field_of_parent; [...] 10836 92 27_Re: referential constraints14_Stevens, Wayne21_Wayne_Stevens@BMC.COM31_Tue, 19 Oct 1999 15:49:44 -0500549_ISO-8859-1 Was this tablespace forced open at some time? You might run a check tablespace with scope all.

Wayne Stevens



-----Original Message----- From: Nash Gupta [mailto:nashgupt@US.IBM.COM] Sent: Tuesday, October 19, 1999 3:35 PM To: DB2-L@RYCI.COM Subject: Re: referential constraints



If the child table has more than 2 parents try using the 'after delete' trigger... If table x has two parents table y and table z then use the trigger below. A trigger should be written for each parent in such a case. [...] 10929 143 31_Re: prefix.SDSNLOAD in LNKLSTxx11_Hilton Tina15_HiltonT@TCE.COM31_Tue, 19 Oct 1999 15:56:13 -0500750_iso-8859-1 Can someone say why it makes a big difference? I discussed it with my MVS sysprogs, and they can't think of why. We do use LLA, by the way.

-----Original Message----- From: kls_tech@ix.netcom.com [mailto:kls_tech@ix.netcom.com] Sent: Tuesday, October 19, 1999 2:08 PM To: DB2-L@RYCI.COM Subject: Re: prefix.SDSNLOAD in LNKLSTxx



You are missing out on a BIG performance boost by doing this. I would think that this would be toward the top of the list in DB2 performance tuning. You do have at least SDSNLINK in your LNKLST don't you? See...2.4.3.2.2 Choosing Link List Options in http://www.s390.ibm.com/bookmgr-cgi/bookmgr.cmd/BOOKS/DSNIG0F5/2%2e4%2e3%2e2 %2e2?SHELF=DSNSH0F5#HDRDLNKLIT [Caution: URL may split] [...] 11073 15 27_Question on DB2 PPT Modules14_Medel Guevarra17_deng777@EMAIL.COM31_Tue, 19 Oct 1999 16:01:12 -0700474_- Hi, What is the significance of defining PPT entries for the modules DSNYASCP, DXRRLM00, DSNUTILB in the SCHEDxx member of parmlib? Our system only has DXRRLM00 defined. Does this have any effect on our overall DB2 system? 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. 11089 53 27_Re: referential constraints10_Leo Flores23_leoflores@EARTHLINK.NET31_Tue, 19 Oct 1999 15:02:28 -0700637_us-ascii Is the Child Table a Parent of another table that has an 'on delete RESTRICT' restraint on it?

HTH Leo Flores



Lindsay, William (SOM400A) wrote:

> Does the child have a dependancy on any other parent ? > > Bill Lindsay > > Project Manager - Web Development > Merrill Lynch > Global Data Sourcing and Delivery > 400 Atrium Drive - 5S51A > Somerset NJ 08873-2861 > > 1 (732) 764-3464 (Work) > 1 (888) MERRIL-0 (Pager > > -----Original Message----- > From: Bud.Greenman@ONONDAGA.NY.US > [mailto:Bud.Greenman@ONONDAGA.NY.US] > Sent: Tuesday, October 19, 1999 4:07 PM > To: DB2-L@RYCI.COM > Subject: [...] 11143 150 55_Repost: New Book: "DB2 Answers! Certified Tech Support"12_John Morrill12_johnm@VP.NET31_Tue, 19 Oct 1999 17:18:34 -0500591_iso-8859-1 Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner

=================================================

-----Original Message----- From: dbswift@us.ibm.com [mailto:dbswift@us.ibm.com] Sent: Tuesday, October 19, 1999 8:12 AM To: DB2_Interested_Parties@us.ibm.com Subject: New Book: "DB2 Answers! Certified Tech Support" [...] 11294 31 27_Re: referential constraints12_John Morrill12_johnm@VP.NET31_Tue, 19 Oct 1999 17:30:30 -0500372_iso-8859-1 Bud

There are several reason why this may have happen. But, if the parent row was successfully deleted and the child row is an orphan, I strongly suspect that the restraint (a.k.a foreign key) was dropped and the relation not longer exists between these tables. It is a common problem during development when tables are often dropped and recreated. [...] 11326 29 27_Stored Procedures Questions22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Tue, 19 Oct 1999 15:45:06 -0700604_- We are investigating Stored Procedures. The main plus to Stored procedures from my point of view is being able to call it from just about anywhere in the DB2 Cobol environment. The questions I have mainly concern distributed environments. Is anyone using Stored Procedures with VB, EZTRIEVE, or SQR with Cobol? Are you using PL/1 or C exclusively? If you are using Cobol, how are you building your parmlist? Since the parms are passed using the linkage section are you explicitly defining everything as an 01 level? Any insight on this would be appreciated.. So many questions, so little time.... [...] 11356 23 51_Automatic Number generator for a table - on DB2 UDB0_30_Milorad_Cerovac@TAC.VIC.GOV.AU31_Wed, 20 Oct 1999 12:29:15 +1000316_us-ascii Dear All,

We are a DB2 UDB ver 5 site running on an NT platform and have been asked if it is possible to replicate Microsoft Access' AutoNumber facility for a DB2 table - ie where a row added to a table has a value that is one larger than the largest entry that was ever entered in that column. [...] 11380 26 36_Re: Asynchronous wait time too high?15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Mon, 18 Oct 1999 21:21:04 -0500473_us-ascii Sandi Mitchell wrote:

Do you know what your DASD response times are for the tables/indexes in question?

Jim Lewandowski



> > The numbers are from Omegamon reports run for that specific planname. So yes, > the numbers are just for that one batch job. SYSPACKDEP shows 11 indexes and 11 > tables, none of which are partitioned. I'm am going to look at dataset > placement. That is an area that we haven't paid enough attention to. > [...] 11407 25 36_Re: Asynchronous wait time too high?15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Mon, 18 Oct 1999 21:43:38 -0500519_us-ascii Sandi Mitchell wrote:

Since a 32 page prefetch I/O is equivalent (very close) to a single page read synch I/O, seq. prefetch most likely is not your problem.

Jim Lewandowski

> > The numbers are from Omegamon reports run for that specific planname. So yes, > the numbers are just for that one batch job. SYSPACKDEP shows 11 indexes and 11 > tables, none of which are partitioned. I'm am going to look at dataset > placement. That is an area that we haven't paid enough attention to. > [...] 11433 40 13_Drop Database30_GroupIT - Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY31_Wed, 20 Oct 1999 12:51:16 -0700482_us-ascii Bob,

There is no problem dropping a database without the existence of the physical VSAM dataset. We are in DB2 Version 4.1 and had never encountered any problem. The steps undertaken by us were: 1) Issue STOP DATABASE where following appears in MSTR address space: DSNT736I + ASYNCHRONOUS STOP DATABASE COMMAND HAS COMPLETED 2) Issue DROP DATABASE DB2 merely removes the entries from catalog as if normal without checking for existence of the physical dataset. [...] 11474 41 24_Re: SQLCODES -904 & -91327_Parthasarathy, Sentil Kumar38_SentilKumar.Parthasarathy@SISCLEAR.COM31_Wed, 20 Oct 1999 08:50:54 +0100578_- Hai For both -904 and -913, DB2 will automatically close the Cursor.

With regards Senthil

> -----Original Message----- > From: Santhana Lakshmi [SMTP:msl19037@ICIL.CO.IN] > Sent: Dienstag, 19. Oktober 1999 18:18 > To: DB2-L@RYCI.COM > Subject: SQLCODES -904 & -913 > Importance: High > > Hi Everybody, > > I have a logic which will perform the same SQL Statement when the SQLCODE > is > -904 or -913. I would like to know when a Fetch operation is performed and > we get above Sqlcodes, will the Cursor be still opened? > > Thanks & Regards, > > Lakshmi. > [...] 11516 18 17_Re: Drop Database0_25_Clint.Taylor@AUTOZONE.COM31_Wed, 20 Oct 1999 07:14:09 -0500466_us-ascii The dsn's do not have to exist. We are sms managed db2 space in out shop. Tech Support has optioned to delete datasets in test that are not accessed in a specified period of time. This means that when a programmer accesses the table and the dataset has bee deleted we have to physically recreate and load the table from a image copy or production. We have not had any problem dropping and recreating these tables where there is physically no datasets. [...] 11535 53 27_Question on DB2 PPT Modules11_Sam Knutson21_sknutson@LANDMARK.COM31_Wed, 20 Oct 1999 08:52:57 -0400517_us-ascii >From: Medel Guevarra >What is the significance of defining PPT entries for the modules >DSNYASCP, DXRRLM00, DSNUTILB in the SCHEDxx member of parmlib? Our >system only has DXRRLM00 defined. Does this have any effect on our >overall DB2 system?

I believe that some or all of these are defaulted even if you don't have them coded in PARMLIB at the current OS390 levels. To see what you are really using for PPT you can use any of several free system information tools/programs [...] 11589 66 31_Re: Stored Procedures Questions13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 20 Oct 1999 08:14:26 -0500383_iso-8859-1 Hi, Incase you have not seen this read book, I recommend it. Look at http://www.redbooks.ibm.com/ and search of "stored procedures".

Getting Started with DB2 Stored Procedures: Give Them a Call through the Network , SG24-4693-01 is what I have used, I see a new book on the list added a few weeks ago, Cool ways to Develop DB2 Stored Procedures , SG24-5485-00 [...] 11656 92 32_Re: FW: Loading LARGE table NPIs10_Ven Ilagan18_vilagan@ONE.NET.AU31_Wed, 20 Oct 1999 07:47:02 +1000615_us-ascii Contact your friendly systems programmer guys and they can make you a routine to trap the abend code and issue out your own rc=0 .....

Regards, Ven



At 13:19 19/10/99 -0400, Scarcella, Gary V wrote: >Is there a way to get the DIAGNOSE to stop after a message id is found >WITHOUT abending? >It's an interesting suggestion to stop after the load phase is complete and >then run concurrent jobs to rebuild the indexes, but having a scheduled >production job that is supposed to ABEND will cause havoc with our >scheduling and oncall folks. > >Thanks, >Gary Scarcella >AFS/IRM/DBA - [...] 11749 167 31_Re: prefix.SDSNLOAD in LNKLSTxx11_rick creech18_ykcirc@HOTMAIL.COM29_Wed, 20 Oct 1999 06:44:12 PDT545_- I would like to put in an endorsement of the option used by William Welch to not put SDsNLOAD in linklist. I have been responsible for the DB2 systems admistration at companies where it was in the LINKLIST and where it was not. I much prefer not having it in the linklist for the reasons given by William, although it is true that things can be made to function either way. The more sub-systems that you have, and the more often that you change maintenance, the option of not having it in the linklist becomes more attractive in my view. [...] 11917 60 55_Re: Automatic Number generator for a table - on DB2 UDB0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 20 Oct 1999 09:03:03 -0500516_us-ascii Since the archives haven't been moved from American (well the last time I looked they hadn't yet) here are some responses on the list to similar questions that I have saved.

At the IBM Whitepaper website http://www.software.ibm.com/data/pubs/papers/ look for the doc titled "DB2 Universal Database V5 Implementing Sequences", it discusses techniques for implementing sequences in DB2 (and other DBMS's) -------------- SEQUENCE The technique I use is as follows, and working in Production: [...] 11978 25 25_DB2 / Top Secret security12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM31_Wed, 20 Oct 1999 10:08:12 -0400533_iso-8859-1 We are a new shop to DB2 and I am in the process of setting up security for the DB2 environment. We have Top Secret installed and I would like to use the TS security group capabilities to make the maintenance easier. It is my understanding that there is a user exit that can be used so that DB2 can pick up the group that an individual is assigned to and use it as the secondary ID in DB2. This secondary ID will than be used for access to tables, views, stored procedures, etc. Is anyone using this particular setup [...] 12004 33 25_UDB Training - UNIX vs NT0_22_BILL_GALLAGHER@PHL.COM31_Wed, 20 Oct 1999 10:16:18 -0400548_us-ascii Hi. Just a general question for anybody who has experience with UDB on both UNIX (AIX) and NT . . . .

We're about to embark on a couple of new projects which will involve UDB EE (not EEE) running on AIX. The two DBA's who will be supporting this environment (myself and a colleague) have little to no UNIX experience, so this will be a new adventure for us. About a year ago, we were invoved in supporting a small UDB application running on NT, and we were able to both take the CF231 (UDB Database Administration Workshop for [...] 12038 28 29_Re: DB2 / TOP SECRET SECURITY18_Ray.Turpen@WOODMEN19_rturpen@WOODMEN.COM29_Wed, 20 Oct 1999 10:20:37 EDT594_US-ASCII In-Reply-To: The letter of Wednesday, 20 October 1999 9:07am CT

We attempted to use Top Secret to handle DB2 security with no success. The problem was not in the group capabilities but in how Top Secret handled multiple DB2 regions ie(PROD-TEST-DEV). We were told by C.A. that we would have to purchase their Top Secret for DB2 add-on software to handle DB2 security. We are now converting to RACF but will still handle DB2 secondary ID processing outside RACF. There are to many open issues between DB2, CICS, and the handling of secondary IDs. We use the DB2 user exits, [...] 12067 30 29_identifying a locking problem0_18_Usregion10@AOL.COM29_Wed, 20 Oct 1999 10:25:02 EDT544_us-ascii Hi all!

I am trying to determine the cause of a locking problem between two applications. Now the problem is that the locking problems are not consistent - meaning the applications are running just fine and dandy and then all of a sudden on claims that the other is locking its users. We are informed about it a day later. So, my now mission is to identify whats going on behind the scenes. What would be ideal is to find out which are the two sql statements that cause the conflict. How do I proceed ? Any types of traces [...] 12098 49 29_Re: DB2 / TOP SECRET SECURITY14_Stevens, Wayne21_Wayne_Stevens@BMC.COM31_Wed, 20 Oct 1999 09:27:19 -0500469_ISO-8859-1 The sample DB2 exits for RACF are in pds &HLQ.SDSNSAMP. The members are DSN3SATH and DSN3SSGN.

Wayne Stevens BMC Software, Inc. wayne_stevens@bmc.com 512 340 6927





-----Original Message----- From: Ray.Turpen@WOODMEN [mailto:rturpen@WOODMEN.COM] Sent: Wednesday, October 20, 1999 9:21 AM To: DB2-L@RYCI.COM Subject: Re: DB2 / TOP SECRET SECURITY



In-Reply-To: The letter of Wednesday, 20 October 1999 9:07am CT [...] 12148 48 29_Re: DB2 / Top Secret security14_Murray Webster26_Murray.Webster@MS.RC.GC.CA31_Wed, 20 Oct 1999 10:22:53 -0400336_iso-8859-1 We are a TSS shop and have set up our security as you would like to. To do this run the installation job called DSNTIJEX which re-assembles the default RACF exits called DSN3@SGN and DSN3@ATH, supplied by IBM in the SDSNSAMP library. This works since TSS will interpret RACF calls and does not require any special mods. [...] 12197 54 17_Re: Bind question0_28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 20 Oct 1999 16:32:56 +0100367_us-ascii Eric,

were the six programs that rebound themselves among the 27 programs or were they added on to that number ? If so, then you might want to check your RI; DB2 invalidates programs that use tables that have relationships to dropped (or changed or ... or ....or ....) tables; if not, well, I would suggest your installation allowed auto-rebinds [...] 12252 61 29_Re: UDB Training - UNIX vs NT13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Wed, 20 Oct 1999 15:37:37 +0100488_us-ascii Bill,

I'd not recommend taking the course - you'd be bored to tears (certainly if it's the same as the ones they run in the UK under different codes).

They concentrate very much on adminstration through the GUI tools, which are the same for both platforms.

I don't reckon you'll have much difficulty moving from one platform to another - and you'd be better spending your training budget on some generic Unix training (and Perl scripting !!!) training. [...] 12314 60 29_Re: UDB Training - UNIX vs NT10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 11:09:46 -0400476_us-ascii Bill, around a month ago I moved from DB2 - NT and OS/2 to DB2/6000 (AIX)...I think the comfort level with DB2 will increase as you get more and more familiarised with unix... It also really depends on the quality of the teaching in these classes...If they use GUI tools to do all the functions then its definitey *not* worth (the gui is same throughout)...You might want to do consider a Unix class with teaches you some scripting language...\Hope this helps.. [...] 12375 39 36_Re: Asynchronous wait time too high?14_Sandi Mitchell24_smitchell@AVISTACORP.COM31_Wed, 20 Oct 1999 08:20:34 -0700694_US-ASCII No. I don't have that information.

____________________Reply Separator____________________ Subject: Re: Asynchronous wait time too high? Author: Jim Lewandowski Date: 10/18/99 9:21 PM

Sandi Mitchell wrote:

Do you know what your DASD response times are for the tables/indexes in question?

Jim Lewandowski



> > The numbers are from Omegamon reports run for that specific planname. So yes, > the numbers are just for that one batch job. SYSPACKDEP shows 11 indexes and 11 > tables, none of which are partitioned. I'm am going to look at dataset > placement. That is an area that we haven't paid enough attention to. [...] 12415 25 17_Trigger in UDB NT11_Kenneth Lam14_klam@BTMNA.COM31_Wed, 20 Oct 1999 11:23:22 -0400288_us-ascii Can anyone show me if I try to create a database trigger in UDB 5.2 that will check to see if a row already exists in the table before it is inserted. It looks like UDB doesn't allow the creation of such a trigger. Does anyone have any idea whether this can be done in UDB? [...] 12441 103 55_Re: Automatic Number generator for a table - on DB2 UDB10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 11:28:54 -0400394_us-ascii This can also be attained through a BEFORE INSERT trigger using COALESCE and MAX functions on the field which needs to be incremented. I am not sure how much does it differ in terms of performance from the solution mentioned below...

- Nash Gupta

Rohn.Solecki@MTS.MB.CA on 10/20/99 09:03:03 AM

Please respond to DB2 Data Base Discussion List [...] 12545 64 31_Re: Stored Procedures Questions13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 20 Oct 1999 10:28:40 -0500523_iso-8859-1 Marty, I misread your question ... try#2

We made each parameter a 01 level - that was the aim. This saved problems with data formats. An 01 level with different datatype below it caused problems when call by Powerbuilder (& vb etc) applications, because they had to build one string. Getting decimal format into a string was a problem from the Windows platform. By having separate 01 levels we did not have any problems with datatype. The translation was done via the system (DRDA, DB2 Connect etc). [...] 12610 81 17_Re: Drop Database12_Roy Brickley27_roy_brickley@CCGATE.APL.COM31_Wed, 20 Oct 1999 07:24:45 -0700606_US-ASCII Bob, Jenny, et al.,

There may be at least one variation where the lack of physical datasets from a DROP DATABASE could cause some conflicts. We have a Y2K clone of a Production DB2 subsystem. The "real" production DB2 has around 100 DASD volumes. All of the system catalog/directory objects reside on 10 volumes, which also have application data. The other 90 volumes have application data only, mostly for a vendor application package. The "clone" has only the 10 DASD volumes needed to start the DB2 subsystem. Each time this "clone" is refreshed with the most current backup of the [...] 12692 90 29_Re: UDB Training - UNIX vs NT14_Philip Gunning20_pgunning@BOSCOVS.COM31_Wed, 20 Oct 1999 11:41:43 -0400320_- Nash, What type of applications do you have running on AIX? Are you doing any OLTP? Did you do any OLTP on NT? I would appreciate your response as we are considering going from NT to AIX. Thanks, Phil

Phil Gunning DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Certified Solutions Expert -- CICS TS [...] 12783 37 55_Re: Automatic Number generator for a table - on DB2 UDB13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 20 Oct 1999 10:36:33 -0500440_US-ASCII Hello Milorad, Take a look at GENERATE UNIQUE Kurt



>>> 10/19/99 09:29PM >>> Dear All,

We are a DB2 UDB ver 5 site running on an NT platform and have been asked if it is possible to replicate Microsoft Access' AutoNumber facility for a DB2 table - ie where a row added to a table has a value that is one larger than the largest entry that was ever entered in that column. [...] 12821 56 55_Re: Automatic Number generator for a table - on DB2 UDB10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 11:54:23 -0400439_us-ascii Kurt, GENERATE_UNIQUE generates a unique id on the basis of timestamp..It does not generate one larger value than the largest entry that was ever entered in that column.

- Nash

Kurt Sahlberg on 10/20/99 10:36:33 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Automatic Number generator for a table - on DB2 UDB [...] 12878 73 29_Re: DB2 / Top Secret security15_David A. Zelmer25_david.zelmer@CITICORP.COM31_Wed, 20 Oct 1999 11:11:04 -0500312_us-ascii We use Top Secret for our security. Use the IBM supplied exit DSN3@ATH. It used to be in older versions of Top Secret that the code had to be modified. This restriction is no longer true and the code can be assembled and linked as supplied by IBM. We install this module into our SDSNEXIT library. [...] 12952 27 32_Re: Installing JDBC on DB2 OS39012_Hylton Tom P23_Tom.P.Hylton@M1.IRS.GOV31_Wed, 20 Oct 1999 14:30:40 -0400478_iso-8859-1 Yes, it comes with V5.

The install directions are in the Version 5 Program Directory. Member name is Q#DB5512





-----Original Message----- From: Michael Lim [mailto:TSMLIM@HKCLEARING.COM.HK] Sent: Wednesday, October 13, 1999 4:05 AM To: DB2-L@RYCI.COM Subject: Installing JDBC on DB2 OS390



Can someone tell me where I can find documentation on how to install JDBC on DB2 V5 OS390? Does this component come with DB2 V5? [...] 12980 48 37_DB2/IMS Database Administrator Needed21_C. Baker (Inter*Link)24_cbaker@INTERLINK-INC.NET31_Wed, 20 Oct 1999 15:02:47 -0400645_us-ascii I have a client looking for a DB2/IMS Database Administrator for a direct hire position in Jacksonville, Florida. This position requires you are a US Citizen or Green Card Holder. The salary range for this position is $60-70K plus benefits and bonuses. The following skills are required.

(Specify level of expertise needed by indicating: H, M, L, E-- Heavy, Medium, Light, Education) Software: Knowledge of the BMC or Platinum sweet of tools H Database: DB2 H; IMS H Hardware: IBM Mainframe, MVS H Education: BA/BS degree in Computer Science or Information Systems or equivalent experience. Experience: Minimum of 3-4 years [...] 13029 64 21_Re: Trigger in UDB NT0_19_Tim.Lowe@STPAUL.COM31_Wed, 20 Oct 1999 14:19:03 -0500770_us-ascii I thought that insert/update/delete was only allowed in AFTER triggers.













Kenneth Lam on 10/20/99 10:23:22 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Trigger in UDB NT















Can anyone show me if I try to create a database trigger in UDB 5.2 that will check to see if a row already exists in the table before it is inserted. It looks like UDB doesn't allow the creation of such a trigger. Does anyone have any idea whether this can be done in UDB? [...] 13094 34 55_Re: Automatic Number generator for a table - on DB2 UDB15_Steven Gertiser20_steveg@IDPC-INTL.COM31_Wed, 20 Oct 1999 13:21:35 -0600562_us-ascii Use the DB2 function GENERATE_UNIQUE.

Steve G. Stillwater

Milorad_Cerovac@TAC.VIC.GOV.AU wrote: > > Dear All, > > We are a DB2 UDB ver 5 site running on an NT platform and have been asked > if it is possible to replicate Microsoft Access' AutoNumber facility for a > DB2 table - ie where a row added to a table has a value that is one larger > than the largest entry that was ever entered in that column. > > We've been unable find anything suitable in the DB2 documentation. Does > DB2 have a similar ability? Or can someone suggest [...] 13129 18 42_Running UDB 6.1 Control Center under Linux15_Steven Gertiser20_steveg@IDPC-INTL.COM31_Wed, 20 Oct 1999 13:24:10 -0600364_us-ascii Hi folks,

Anyone know how to fire up the Java-based Control Center under Linux?

Steve G. Stillwater

================================================ 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. 13148 83 21_Re: Trigger in UDB NT10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 15:31:15 -0400552_us-ascii They can be used for both BEFORE and AFTER triggerng events.... - Nash

Tim.Lowe@STPAUL.COM on 10/20/99 02:19:03 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Trigger in UDB NT







I thought that insert/update/delete was only allowed in AFTER triggers.













Kenneth Lam on 10/20/99 10:23:22 AM

Please respond to DB2 Data Base Discussion List [...] 13232 22 23_data encryption program0_18_Usregion10@AOL.COM29_Wed, 20 Oct 1999 15:41:27 EDT360_us-ascii Hi all!

Does anybody know of any encryption code that can be used to encrypt a password before storing to a db2 table. The goal is to store an encrypted password and then compare it with the subsequent encryptions (password validation). Can we use anything from IBM such the RACFs encryption code or ACF2 encryption code etc. Thanks. Anil [...] 13255 30 21_Re: Trigger in UDB NT12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 20 Oct 1999 14:48:10 -0500375_us-ascii I sorely wish that SQL Insert, Update and Delete statements could be used in DB2 UDB for OS/390 V6, "before" triggers, but they cannot be according to:

"Table 19. Valid sql statements for triggers and trigger activation times" in section "3.5.2 Parts of a trigger" of manual: "DB2 UDB for OS/390 V6 Application Programming and SQL Guide" ( SC26-9004-00) [...] 13286 19 55_Re: Automatic Number generator for a table - on DB2 UDB12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 20 Oct 1999 14:51:35 -0500475_us-ascii Under DB2 UDB for OS/390 v6, this is apparantly handled with the ROWID data type.

See: 7.10.1 Providing a unique key for a table In: DB2 UDB for OS/390 V6 Application Programming and SQL Guide (SC26-9004-00)

Sg

================================================ 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. 13306 55 21_Re: Trigger in UDB NT10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 15:58:44 -0400326_us-ascii Steve, I know for sure the before and after triggers work in OS/2, NT and AIX env...You can also refer to the section on triggers in the book, A Complete Guide to DB2 Universal Database by Don Chamberlin. Don happen's to be a co-inventor of SQL language.

I am *not* sure if this works in the OS/390 env.. [...] 13362 115 29_Re: UDB Training - UNIX vs NT10_Nash Gupta19_nashgupt@US.IBM.COM31_Wed, 20 Oct 1999 16:05:52 -0400421_us-ascii Phil, unfortunately I haven't done any OLTP on NT. Currently, we are doing a ordering system which involves use of following technologes: Java Servelets, EJB, JSP, CORBA, Websphere and DB2/6000 (AIX). I am responsible only for DB2...Hope this helps...

- Nash

Philip Gunning on 10/20/99 10:41:43 AM

Please respond to DB2 Data Base Discussion List [...] 13478 24 21_Re: Trigger in UDB NT12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 20 Oct 1999 15:18:31 -0500398_us-ascii Ah - I'm sure you're right about that Nash. My ulterior motive for my post was the hope that some DB2 guru out there would say, "Oh -but here's the APAR that will allow before triggers to update under OS/390 V6..." etc.

Thanks for the information. I'm converting a design from MS SQL Server and this single issue has forced me to reconsider a number of trigger specifications. [...] 13503 66 21_Re: Trigger in UDB NT0_24_Ray.Price@DRESDNERKB.COM31_Wed, 20 Oct 1999 21:28:16 +0100568_iso-8859-1 According to the IBM web site, APAR means "Authorized Problem Analysis Reports"

Ray Price DBA Dresdner Kleinwort Benson London



-----Original Message----- From: Steve Grimes [SMTP:Steve_Grimes@AISMAIL.WUSTL.EDU] Sent: Wednesday, October 20, 1999 9:19 PM To: DB2-L@RYCI.COM Subject: Re: Trigger in UDB NT

Ah - I'm sure you're right about that Nash. My ulterior motive for my post was the hope that some DB2 guru out there would say, "Oh -but here's the APAR that will allow before triggers to update under OS/390 V6..." etc. [...] 13570 17 14_Old packages..10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM31_Wed, 20 Oct 1999 16:22:05 -0500446_- Hello List,

Is there a way to identify a list of packages in DB2/OS390 V5.1 that are not executed (accessed) for a longtime ( a year) ? I want to remove all the packages that are obsolete. 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. 13588 68 21_Re: Trigger in UDB NT0_19_Tim.Lowe@STPAUL.COM31_Wed, 20 Oct 1999 16:44:56 -0500482_us-ascii I had just thought that UDB was going to be consistent across platforms, and since OS/390 now supports triggers with a rule that insert/delete/update cannot be in before triggers, then logically this would follow to the other platforms.

Sorry for asking, Tim Lowe













Steve Grimes on 10/20/99 03:18:31 PM

Please respond to DB2 Data Base Discussion List [...] 13657 104 70_Repost: Check Out the New Issue of IDUG Solutions Journal (Summer1999)19_J. Michael Morrilll21_xavier@NETEXPRESS.NET31_Wed, 20 Oct 1999 16:51:07 -0500760_iso-8859-1 Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner

=================================================



Dear DB2 Interested Parties,

The new issue of the IDUG Solutions Journal is now available. With an editorial focus on Business Intelligence, there are several fine articles (see below) illuminating the challenges, opportunities, and solutions for BI applications. This issue also includes a new column, "The Intelligent Optimizer" by IBM Fellow, Dr. Patricia Selinger. [...] 13762 95 21_Re: Trigger in UDB NT15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 20 Oct 1999 17:54:06 -0400352_iso-8859-1 Based on what I read on V6 DB2 os/390, there is before and after triggers and is very much like SQL server triggers. We can do insert, update and delete before/after triggers. But we use DB2 v5.1. so I have not tested this.

Thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com [...] 13858 29 22_DB2 Connect to the Web15_Michael Bancale21_mbancale@TXFB-INS.COM31_Wed, 20 Oct 1999 17:08:10 -0500320_iso-8859-1 We have recently installed DB2 Connect EEE on a server. We have several clients which are now access data on the mainframe from the PC. (Big step here)....

Now I have been asked if anyone else has tried to set up the client (CAE) on a web server to enable web applications to access the DB2 data. [...] 13888 141 21_Re: Trigger in UDB NT0_19_Tim.Lowe@STPAUL.COM31_Wed, 20 Oct 1999 17:11:33 -0500437_us-ascii According to the presentation and the DB2 technical conference, "Trigger support in DB2 UDB for OS/390", and the DB2 version 6 SQL reference manual under "create trigger", table #39; insert/update/delete statements are not allowed in "before triggers" in DB2 UDB for Os/390 Thanks, Tim Lowe 651-310-2319













"Philip, Sibimon" on 10/20/99 04:54:06 PM [...] 14030 167 21_Re: Trigger in UDB NT15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 20 Oct 1999 18:27:14 -0400495_iso-8859-1 I looked at release guide 'http://www-4.ibm.com/software/data/db2/os390/pdf/relg6.pdf' under 'using triggers for active data' . Is this not correct? I am not able to cut and past this.

Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Wednesday, October 20, 1999 5:12 PM To: DB2-L@RYCI.COM Subject: Re: Trigger in UDB NT [...] 14198 114 21_Re: Trigger in UDB NT0_29_Greg.Palgrave@BANKWEST.COM.AU31_Thu, 21 Oct 1999 08:35:37 +0800578_us-ascii Hi Kenneth,

Just did a quick test (after fixing the syntax):

CREATE TRIGGER CHECK_DUP_CUST NO CASCADE BEFORE INSERT ON CUSTOMER REFERENCING NEW AS NEW_CUSTOMER FOR EACH ROW MODE DB2SQL DELETE FROM CUSTOMER WHERE CUST_ID = NEW_CUSTOMER.CUST_ID

Which gets error as follows:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0751N The trigger "XXXXX.CHECK_DUP_CUST" is defined with an unsupported triggered SQL statement. SQLSTATE=42987 [...] 14313 44 47_Re: PLAN_TABLE query for cleaning up versions ?9_jim.szabo22_jim.szabo@MCIWORLD.COM31_Wed, 20 Oct 1999 21:32:40 -0400380_iso-8859-1 Possibly better suggestion: only keep one generation of the explain information per package, moving the current information out to a historical table prior to the bind/explain. If you want to keep the n-most current versions, create n tables, and move the data "down a version" each. It's still easy enough to construct SQL that "joins" the information from both. [...] 14358 39 47_DB2 Connect 6 accessing DB2 V4.1 from MS Access15_Michael Bancale21_mbancale@TXFB-INS.COM31_Wed, 20 Oct 1999 22:36:15 -0500357_iso-8859-1 We are having some success with accessing DB2 OS/390 via a DB2 Connect server and MS Access. We are having some problems and have not been able to find any information on this problem. The problem is as described below.

In MS Access we attempt to link to a table which has a name which is longer than 15 characters without the owner. [...] 14398 50 55_Re: Automatic Number generator for a table - on DB2 UDB0_24_ssethi@LOT.TATASTEEL.COM31_Thu, 21 Oct 1999 09:00:30 +0530289_- Try using GENERATE_UNIQUE scalar function. It uses the system clock and node number to generate a unique value if the clock is not being reset. The output is CHAR(13) for bit data and there are no arguements.HEX and DEC function can be used to convert the output value in a number. [...] 14449 125 17_Re: Drop Database14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Thu, 21 Oct 1999 16:45:16 +1000418_us-ascii I suspect that the problem here was that the VSAM clusters _were not deleted_! That is, the process Jenny mentioned was _not_ followed.

So, when DB2 issued the delete against the VSAM clusters (which a DROP DATABASE would make happen), VSAM found a catalog entry and asked for the volume mount. If the VSAM clusters had been deleted, VSAM would have just told DB2 that each cluster did not exist. [...] 14575 57 11_Parallelism0_24_ssethi@LOT.TATASTEEL.COM31_Thu, 21 Oct 1999 15:13:01 +0530562_- Hi All, I was told by some of the senior people in our place that Parallelism (I/O,CPU or Sysplex) will be in effect only in the partitioned tablespaces but i am unable to agree on this point.As i have read in either Developer's Guide ,Admin Guide or any other article or FAQ's that Parallelism(I/O and CPU) can be in affect in non-partitioned tablespaces as well but it will be most efficient in Partitioned tablespaces.I was quite satisfied with Developer's Guide in this topic but after talking to the senior people i want to confirm my understanding. [...] 14633 80 38_DB2 UDB replication problem - SQL0180N12_Adam, Daniel15_dadam@GRATEX.SK31_Thu, 21 Oct 1999 13:00:05 +0200404_- Hi, this is DB2 V5.2 fixpack6 (wr9074) on Windows NT. I'm trying to replicate one database to another, both are in the same local instance.

Here's what I do : Define repl. source Run SQL file for the repl. source Define replic. subscription Run SQL file for repl. subscription Bind necessairy packages in both databases Create password file run "asnccp mydb1" run "asnapply myqualif mydb2" [...] 14714 25 21_Re: Trigger in UDB NT13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 21 Oct 1999 07:22:40 -0400314_iso-8859-1 Sibimon, What did you see in that manual that led you to believe you can do Delete(or insert or update) in a before trigger. If you look at that manual you reference at table 14(p.110) and the discussion following it, you'll see Insert Update & Delete are allowed in After triggers but not Before. [...] 14740 118 42_Re: DB2 UDB replication problem - SQL0180N10_Marc Baime23_mbaime1@TAMPABAY.RR.COM31_Thu, 21 Oct 1999 07:58:41 -0400598_iso-8859-1 I am not sure why you are getting the datetime value error (SQL0180N) but there are a couple of things you may or may not have forgotten and a couple of things you can do to track down the error. First on the capture/source side: Before you run capture (asnccp) are you sure you have set the DB2INSTANCE and DB2DBDFT variables? Have you examined the *.CCP file (splat replaced by userid:databasename combo) for error information? Have you queried the asn.ibmsnap_trace table on the source server side (there may be error information here)? In addition, on the capture side, you can [...] 14859 44 41_DB2 SAP - New sites or considering going?22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 21 Oct 1999 13:53:16 +0100557_ISO-8859-1 Dear All

I have just returned from the European IDUG and was impressed to see that many more sites around the world are either planning to or are in the process of going to SAP with DB2 on OS/390.

What I would be interested to know is if any of the people in the above bracket(including those that are already working with SAP & DB2 OS390) would be interested in communicating with me on the subject of SAP or with questions that they may have as they approach the implementation phase? I see this has being the start of maybe [...] 14904 61 45_Re: DB2 SAP - New sites or considering going?0_23_Mike_Levine@TEKHELP.NET31_Thu, 21 Oct 1999 09:15:32 -0400551_us-ascii Hi Leslie,

I don't think too many people would consider SAP topics relating to DB2 to be list 'noise'. It is certainly a valid and relevant topic that should stay on the list.

Regards,

Michael Levine Premier Data Services, Inc.



> Dear All > > I have just returned from the European IDUG and was impressed to see > that many more sites around the world are either planning to or are in > the process of going to SAP with DB2 on OS/390. > > What I would be interested to know is if any of the people in [...] 14966 99 45_Re: DB2 SAP - New sites or considering going?22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 21 Oct 1999 14:33:04 +0100544_ISO-8859-1 Michael (and everyone else)

before this gets all out of hand I would like to stress that at the moment trying to identify who is and who is not a SAP DB2 OS390 site is very difficult. We tend to have people contact us when IBM have suggested that we have cracked a particular problem or two, infact at the moment this is a one way process of us giving out solutions to problems. This can be frustrating fo us as we would like to strike up communication with people in a very similar position to ourselves and maybe learn [...] 15066 84 45_Re: DB2 SAP - New sites or considering going?0_20_John_Lendman@FPL.COM31_Thu, 21 Oct 1999 09:32:46 -0400553_us-ascii I would be glad to discuss SAP issues.

We are just installing 4.5B and I have a lot of questions. I have already talked to Adrian Savoy at your shop. Also did you know about the ASUG meeting that is coming up in New Orleans Nov 9,10,11. They have made the 11th solely for DB2 OS/390 and SAP. We have set some agenda topics like Backup and recovery. The new Application server running on OS/390 and Work Load Manager running in Goal mode for SAP, plus DB2 maintenance and utilities. finishing up with a round table on issues for DB2 [...] 15151 85 45_Re: DB2 SAP - New sites or considering going?11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Thu, 21 Oct 1999 09:56:23 -0400376_iso-8859-1 Leslie,

Platinum set up a monthly teleconference for PeopleSoft users which I found very helpful when we were going through implementation.. You may want to contact CA and see if they have something similar for SAP. The teleconferences were just people asking questions and hoping someone else had answers or suggestions, there was no sales involved... [...] 15237 52 21_Re: Trigger in UDB NT15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 21 Oct 1999 09:57:56 -0400392_iso-8859-1 DAVE,

What I thought about the question is totally different. I thought the question is 'A before trigger is not possible before a insert, update or delete SQL'. Not as 'Delete, update and insert is not possible in a before trigger'. Thanks for clarifying.

Thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com [...] 15290 124 45_Re: DB2 SAP - New sites or considering going?22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 21 Oct 1999 14:59:15 +0100479_ISO-8859-1 John thanks for the mail .. I did not even know that the ASUG was in Nov .. I am in the states on the 4th and 5th .. I fly back on the 7th .. which is sad as I would have loved to attend ..

What is ASUG?

Adrian has been meaning to get some form of communication going for a while .. I just beat him to it :-) sorry mate. Anyway, Adrian will be very much involved in what I plan ..... (we are like Batman and Robin anyway) so it would be hard not to [...] 15415 111 45_Re: DB2 SAP - New sites or considering going?22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 21 Oct 1999 15:09:05 +0100323_ISO-8859-1 Anne thanks for the mail. I just had a look at your presentation from the Advisory council ....

The thought has crossed my mind and I may have a crack at it sometime .. Will contact CA and see if they have anything to offer ..

Regards Leslie ps - we are a heavy user of PDA and PRA aswell ... [...] 15527 26 27_DB2 for MVS - Catalog Reorg12_Kapil Mathur25_kapil.mathur@WACHOVIA.COM31_Thu, 21 Oct 1999 10:14:56 -0400574_us-ascii hi, I am a DBA in a DB2 V5 for OS/390 environment with Peoplesoft HR 7.5 and wanted to know what are the criteria that most sites use to Reorg the tables in the DB2 System Catalog. Last week, I heard from a Peoplesoft consultant that dynamic sql (like Peoplesoft or SAP) uses significantly less CPU for a prepare if the db2 system catalog tables are in good shape (cluster ratio, leafdist etc?). However, the DB2 systems programmers have insisted that IBM told them that clusterratios etc. do not really matter for the DB2 system catalog tables since they are [...] 15554 139 45_Re: DB2 SAP - New sites or considering going?12_Janis Thomas20_thomasj@PLATINUM.COM31_Thu, 21 Oct 1999 09:33:47 -0500341_us-ascii I believe we (CA) will be starting back up with the monthly conference calls for SAP - I will find out the details and post the information to DB2-L. I know they started back up with the PeopleSoft conference calls last month (the former Platinum PeopleSoft User Group (PPUG) has been renamed the PeopleSoft Discussion Forum). [...] 15694 55 31_Re: DB2 for MVS - Catalog Reorg12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 21 Oct 1999 07:38:09 PDT516_- Kapil,

I use the SAME criteria for reorging the DB2 catalog as I use for User objects. I disagree that the catalog data organization has no impact on performance, especially when using an application such as Peoplesoft. Objects most likely will need to be resized as well as the data reorged. Pay attention to SYSKEYS and it's indexes as well as all of the indexes on the tables in SYSDBASE. Craig Mullin's book 'DB2 Developer's Guide' has good information as to when and HOW to REORG the DB2 catalog. [...] 15750 80 51_FW: DB2 Connect 6 accessing DB2 V4.1 from MS Access14_Metivier, Bill21_Bill.Metivier@FMR.COM31_Thu, 21 Oct 1999 10:32:13 -0400368_- We encountered this problem also using DB2 Connect V6 going against DB2 V5. The solution was to disable the CLI/ODBC advanced settings option for treating a "_" as a wildcard. This can be done either by manually editing the DB2CLI.INI file and adding "UNDERSCORE=0" or by using the client configuration assistant under CLI/ODBC advanced settings/ optimization. [...] 15831 17 12_sqlcode -80312_David Thomas21_davidthomas@CHUBB.COM31_Thu, 21 Oct 1999 10:46:35 -0400468_us-ascii My batch program received sqlcode -803 ( duplicate columns in the primary index ) while attempting to insert a row. In addition to the sqlcode I also received the SQLERRMC which displayed the index involved with the error. All of this is fine, however the SQLERRMC also includes two spaces after the index name, followed by a 10 byte field that I am unable to recognize. The 10 bytes are '0003256717'. How may I determine the meaning of these 10 bytes ? [...] 15849 24 16_Re: sqlcode -80313_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 21 Oct 1999 11:05:12 -0400447_iso-8859-1 David, You can look in the messages & codes manual to see the significance of the fields in SQLERRMC. Those fields are tokens which get inserted into the text of the error message you see in M&C manual or in the message text you would get back from DSNTIAR.

In the case of the -803 that 2nd field is the rid of the row whose uniqueness would be violated. To be anal about this, the name prior to that is the indexspacename. [...] 15874 155 45_Re: DB2 SAP - New sites or considering going?11_Hilton Tina15_HiltonT@TCE.COM31_Thu, 21 Oct 1999 10:07:36 -0500317_iso-8859-1 Is there a web site where I could get more information on ASUG? I might be able to convince my boss to send someone if I had some details.

We don't have have SAP yet, but it's coming eventually and we want to have all the ammo we can get to convince the higher-ups to use DB2 on OS/390 for it. [...] 16030 29 45_Re: DB2 SAP - New sites or considering going?16_Charles Stansell19_cfstansell@JUNO.COM31_Thu, 21 Oct 1999 10:20:07 -0500393_- Leslie,

I would like to see SAP info and questions continue to go to this email discussion list. Actually, I have my Outlook set up to automagically move any DB2-L mail into one folder, and any DB2-L mail containing 'SAP' in the text, to a separate folder. Currently I'm more interested in the 'SAP' info specific to DB2 systems programming info anyway so I appreciate the info. [...] 16060 183 21_Re: Trigger in UDB NT14_Wayne Driscoll19_WDriscoll@QUEST.COM31_Thu, 21 Oct 1999 08:38:19 -0700359_iso-8859-1 And, according to the UDB 6.1 for NT and Unix SQL Reference, the following applies to the Triggered SQL Statements:

triggered-SQL-statement If the trigger is a BEFORE trigger, then a triggered SQL statement must be one of the following (SQLSTATE 42987): a fullselect a SET transition-variable SQL statement. a SIGNAL SQLSTATE statement [...] 16244 175 45_Re: DB2 SAP - New sites or considering going?0_32_thomas.e.faglon@BELLATLANTIC.COM31_Thu, 21 Oct 1999 11:54:54 -0400484_us-ascii Tina IDUG has a SIG starting on sap www.software.ibm.com/data/partners/ae1partners/sap/newsletsummer99.html Also www.sapfaq.com/

Tom To: DB2-L@RYCI.COM cc: (bcc: Thomas E Faglon) From: Hilton Tina Date: 10/21/99 03:07:36 PM GMT Subject: Re: DB2 SAP - New sites or considering going?





Is there a web site where I could get more information on ASUG? I might be able to convince my boss to send someone if I had some details. [...] 16420 25 11_USA vs. ISO11_rick creech18_ykcirc@HOTMAIL.COM29_Thu, 21 Oct 1999 09:21:25 PDT411_- As a result of corporate mergers, we are merging DB2 applications from one DB2 sub-system to another. One of the corporations had ISO as their default DATE and TIME formats, the other has USA. At this point we have decided to make ISO as the default for everyone. Of course, there are some potential ramifications to the applications that did not use the CHAR function in selecting DATE or TIME columns. [...] 16446 210 45_Re: DB2 SAP - New sites or considering going?22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM31_Thu, 21 Oct 1999 12:21:02 -0400749_iso-8859-1 Check out http://www.asug.com



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

> ---------- > From: > thomas.e.faglon@BELLATLANTIC.COM[SMTP:thomas.e.faglon@BELLATLANTIC.COM] > Sent: Thursday, October 21, 1999 11:54 AM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DB2 SAP - New sites or considering going? > > Tina > IDUG has a SIG starting on sap > www.software.ibm.com/data/partners/ae1partners/sap/newsletsummer99.html > Also > www.sapfaq.com/ > > Tom > To: DB2-L@RYCI.COM > cc: (bcc: Thomas E Faglon) > From: Hilton Tina > Date: 10/21/99 03:07:36 PM GMT > Subject: Re: DB2 SAP - New sites or considering going? > > > > Is there a web site where I could [...] 16657 45 46_Re: Running UDB 6.1 Control Center under Linux25_Carlos Eduardo de Miranda25_carlos_e@CCUEC.UNICAMP.BR31_Thu, 21 Oct 1999 14:30:47 -0200439_us-ascii Steven:

This is what I done: 1. Install JDK 1.1.7 for linux 2. Append the JDK directory to the user PATH 3. Start a xterm session (use X11) From the xterm session: 4. Run db2profile (ksh) 5. Start JDBC (/home/db2inst1/sqllib/bin/db2jstrt) 6. Start Control Center (/home/db2inst1/sqllib/bin/db2cc)

I hope this help,

Carlos Eduardo de Miranda DB2 Database Administrator State University of Campinas Brazil [...] 16703 71 47_Re: PLAN_TABLE query for cleaning up versions ?0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 21 Oct 1999 11:30:15 -0500469_us-ascii Another guy in our group is playing with another option involving multi step bind jcl. We update the "REMARKS" column on the plan table to allow us to keep current bind and previous bind explain data in single plan_table.

- first delete where remark = 'old' and progname = 'prog' - update set remark = "old" where remark = "current" and programe = 'prog' - bind & explain - update set remark = 'current' where remark is blank and progname = 'prog' [...] 16775 70 43_Running UDB 5.2 Control Center under Win NT10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 12:37:14 -0400458_us-ascii Dear List:

Sometime ago I installed the DB2 SDK v5.2 on NT. I believe it installed the Java-based control center too. Does anybody know how to fire this one?? Help is appreciated.

- Nash

Carlos Eduardo de Miranda on 10/21/99 11:30:47 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Running UDB 6.1 Control Center under Linux [...] 16846 22 17_Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 12:41:54 -0400400_us-ascii Hello List! I believe row-level locking is a common feature of all major databases including DB2. Now here's the problem. I have this great app which grabs the entire table everytime it hits the database. It does this even if you insert just one row. Is it possible to restrict it that particular row than the entire table. I believe, I am missing few things here. Help is appreciated. [...] 16869 46 21_Re: Row level locking15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 21 Oct 1999 12:46:09 -0400715_iso-8859-1 Check the lockrule in sysibm.systablespace. It may be specified as table space lock or table lock.

thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: Nash Gupta [mailto:nashgupt@US.IBM.COM] Sent: Thursday, October 21, 1999 11:42 AM To: DB2-L@RYCI.COM Subject: Row level locking



Hello List! I believe row-level locking is a common feature of all major databases including DB2. Now here's the problem. I have this great app which grabs the entire table everytime it hits the database. It does this even if you insert just one row. Is it possible to restrict it that particular [...] 16916 13 21_Re: Row level locking0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Thu, 21 Oct 1999 12:54:38 -0400370_us-ascii Is it possible a "LOCK TABLE" sql statement is being issued within the program? what is the locksize of the tablespace?

================================================ 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. 16930 12 21_Re: Row level locking0_31_truman.g.brown@BELLATLANTIC.COM31_Thu, 21 Oct 1999 12:53:57 -0400326_us-ascii I believe table locks are acquired by SQL executed via an incremental bind.

================================================ 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. 16943 1262 45_Re: DB2 SAP - New sites or considering going?0_20_John_Lendman@FPL.COM31_Thu, 21 Oct 1999 13:06:30 -0400612_us-ascii Here is the information I have on the ASUG meeting.

SAP ASUG TECHNICAL INTEREST GROUP Location: New Orleans Date: November 9-11 (October 14 conference call to select/finalize topics specific to SAP R/3 on DB2 for OS/390.)s

The SAP R/3 on DB2 for OS/390 platform has been brought into ASUG participation under the "Technical Interest Group". The chairman for this group has offered as much time as needed on the agenda for technical platform specific topics at the upcoming November session. As you may know, ASUG is the "formal" SAP channel to submit and request design requirements. [...] 18206 74 15_Re: USA vs. ISO0_26_dawn.kastelic@HIGHMARK.COM31_Thu, 21 Oct 1999 13:09:44 -0400529_us-ascii We had the same issue due to the result of a merger. Our site had USA format as the default and the other site had ISO. We had to choose one or the other in order to implement a sysplex/data sharing environment so we chose ISO. The biggest issue we had was that all of our applications were written to handle the USA format. What we did for our COBOL programs was to add an override to the db2 precompiler to return the date in USA format, thus eliminating any coding changes in the programs (this would have be an [...] 18281 101 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 13:18:07 -0400538_us-ascii Sibimon, how do you figure out that whether it has been specified as table lock or tablespace lock from the data below which is from sysibm.systablespaces...

TBSPACE DEFINER CREATE_TIME TBSPACEID TBSPACETYPE DATATYPE EXTENTSIZE PREFETCHSIZE OVERHEAD TRANSFERRATE READONLY REMOVABLEMEDIA REMARKS



GNAME BUFFERPOOLID PAGESIZE ------------------ -------- -------------------------- ----------- ----------- --------

----------- ------------ ------------------------ ------------------------ -------- [...] 18383 110 45_Re: DB2 SAP - New sites or considering going?0_13_cpost@AEP.COM31_Thu, 21 Oct 1999 13:16:10 -0400693_us-ascii John

Could you send me any other info you have on the ASUG meetings in Nov.











John_Lendman@FPL.COM on 10/21/99 09:32:46 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Charles Post/BC1/AEPIN)

Subject: Re: DB2 SAP - New sites or considering going?







I would be glad to discuss SAP issues.

We are just installing 4.5B and I have a lot of questions. I have already talked to Adrian Savoy at your shop. Also did you know about the ASUG meeting that is coming up in New Orleans Nov 9,10,11. They have made the 11th solely for [...] 18494 30 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 13:21:35 -0400594_us-ascii Truman, how do you resolve this???

truman.g.brown@BELLATLANTIC.COM on 10/21/99 11:53:57 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Row level locking







I believe table locks are acquired by SQL executed via an incremental bind.

================================================ 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. [...] 18525 140 21_Re: Row level locking15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 21 Oct 1999 13:27:31 -0400300_iso-8859-1 I can't figure out from this. Just select lockrule from Sysibm.systablespace where name = 'The name your table's tablespace name' and dbname = 'the database name'

If lockrule = T - table lock = S - table space lock = P - Page lock = R - Row lock = A - Any, Db2 select the lock [...] 18666 25 25_REXX from within QMF PROC9_Krug, Ken17_Ken.Krug@NIKE.COM31_Thu, 21 Oct 1999 10:22:58 -0700440_iso-8859-1 I'm looking for some practical examples of using REXX from within a QMF PROC. A user asked for help in debugging a REXX EXEC he was executing from within a proc and that was the first time I'd seen REXX used that way. He was getting stuck trying to set up addressability to ISPEXEC (ADDRESS ISPEXEC). Unfortunately I couldn't help him, but it made me wonder how others are making use of this seemingly powerful combination. [...] 18692 167 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 13:38:26 -0400440_us-ascii Sibimon, the DATATYPE field in the systablspaces table shows

"T" for TEMPSPACE1 tablespace and "A" for ALL other tablespaces

Now, do I have to change "A" to "R" to achieve row locking??

THanks! - Nash

"Philip, Sibimon" on 10/21/99 12:27:31 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Row level locking [...] 18860 93 47_Re: Running UDB 5.2 Control Center under Win NT25_Carlos Eduardo de Miranda25_carlos_e@CCUEC.UNICAMP.BR31_Thu, 21 Oct 1999 15:41:28 -0200336_us-ascii Nash:

I think Java Control Center is not available in DB2 V5.2. In DB2 V 6 for NT I done this:

1. Start do DB2 --> Command Window: db2start 2. Start JDBC --> Command Window: db2jstrt 7000 (start jdbc at port 7000) 3. Start Control Center --> Command Window: db2cc 7000 (start control center using JDBC port) [...] 18954 205 21_Re: Row level locking0_21_kramps@NATIONWIDE.COM31_Thu, 21 Oct 1999 13:45:19 -0400433_us-ascii Nash,

Sibimon is talking about DB2 for OS/390, while you appear to be using DB2 on a different platform. Please state what DB2 you are using and what platform you are running on. Then perhaps someone will be able to help you.

Sue







Nash Gupta @RYCI.COM> on 10/21/99 01:38:26 PM

From: Nash Gupta @RYCI.COM> on 10/21/99 01:38 PM [...] 19160 43 29_Re: REXX from within QMF PROC13_Chie, Jim (C)17_chiej@CONNEXT.COM31_Thu, 21 Oct 1999 10:41:50 -0700410_iso-8859-1 Here's a simple example:

/*REXX */ ADDRESS ISPEXEC "DISPLAY PANEL (ISR@PRIM)"

You need to have it in your PROC window, and RUN it. You will also need a valid ISPF environment, including file concatenations.

-----Original Message----- From: Krug, Ken [mailto:Ken.Krug@NIKE.COM] Sent: Thursday, October 21, 1999 10:23 AM To: DB2-L@RYCI.COM Subject: REXX from within QMF PROC [...] 19204 227 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 13:48:35 -0400539_us-ascii Sue/Sibimon:

I am using DB2/NT and DB2/6000 (AIX)....

- Nash

kramps@NATIONWIDE.COM on 10/21/99 12:45:19 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Row level locking







Nash,

Sibimon is talking about DB2 for OS/390, while you appear to be using DB2 on a different platform. Please state what DB2 you are using and what platform you are running on. Then perhaps someone will be able to help you. [...] 19432 138 47_Re: PLAN_TABLE query for cleaning up versions ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 21 Oct 1999 13:51:44 -0400583_iso-8859-1 I use two options.

1. Create Plan_history just like PLAN_TABLE then run the following sql

Populate Plan_history:

INSERT INTO PLAN_TABLE_HIST SELECT * FROM PLAN_TABLE A WHERE BIND_TIME < (SELECT MAX(BIND_TIME) FROM PLAN_TABLE B WHERE B.APPLNAME = A.APPLNAME AND B.PROGNAME = A.PROGNAME AND B.COLLID = A.COLLID ) ;

Delete history from Plan_table:

DELETE FROM PLAN_TABLE A WHERE EXISTS (SELECT 1 FROM PLAN_TABLE_HIST B WHERE B.APPLNAME = A.APPLNAME AND B.PROGNAME = A.PROGNAME AND B.COLLID = A.COLLID AND B.BIND_TIME = A.BIND_TIME ) ; [...] 19571 39 29_Re: REXX from within QMF PROC13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 21 Oct 1999 13:55:46 -0400396_iso-8859-1 Here's another example with QMFish stuff:



/* Rexx */

address 'TSO' "alloc f(dsqprint) da(dsqprint) shr reuse" if rc = 0 then do otrc = outtrap(txmit,999) call MSG 'off' xmit= 1 end else xmit= 0 address 'QRW' "RUN QUERY DJS1.TBCOL (F=DJS1.FTBCOL" "PRINT REPORT" if xmit then do address "TSO" "XMIT MC10.DJS1 DA(DSQPRINT)" call msg 'on' call outtrap 'off' end [...] 19611 31 21_#Deleted in MS Access15_Michael Bancale21_mbancale@TXFB-INS.COM31_Thu, 21 Oct 1999 13:01:02 -0500527_iso-8859-1 First I would like to than Bill Metivier for his help in solving my long table name problem with MS Access to DB2 OS/390 via DB2 Connect 6.

Now I have another question. I remember seeing a thread on this but I can't locate it in the archives.

The table I am accessing using MS Access (97) via DB2 Connect EEE 6 has a primary key which is a timestamp. When I return rows from the table on DB2 into the MS Access report I get #Deleted in all of the fields. Has anyone had this problem? and What was [...] 19643 101 29_FW: REXX from within QMF PROC17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Thu, 21 Oct 1999 14:00:58 -0400729_- Here's an example of using REXX within a QMF proc.

/* REXX PROCEDURE TO FIND LIST ALL DB2 OBJECTS BASED ON DBID/OBID */ /* QMF COMMANDS MUST BE IN QUOTES WHEN INSIDE A REXX PROCEDURE */ /*-------------------------------------------------------------------*/ "RUN PROC GDBAT05D.SET_SSID" "GET GLOBAL (SSID=SSID" "GET GLOBAL (MODE=DSQAO_BATCH" /*ASSIGN QMF VARIABLE TO REXX VARIABLE*/ "SET GLOBAL (DATABASE=&DATABASE_NAME" "GET GLOBAL (DATABASE=DATABASE" IF MODE = '2' THEN /* 2=BATCH */ DO IF DATABASE = "ALL" THEN DO X = OUTTRAP("OUTPUT",0) /* INHIBIT MSG DISPLAY */ ADDRESS TSO "FREE FI(DSQPRINT)" MEMBER = SUBSTR(DATE('M'),1,3) 33 SSID DSQOUT = "'F4BN.DBA.DB2.OBID.REPORT(" 33 MEMBER 33 ")'" IF SYSDSN(DSQOUT) a= [...] 19745 98 25_Re: #Deleted in MS Access11_Steve Mazer17_smazer@FMTUSA.COM31_Thu, 21 Oct 1999 14:13:35 -0400601_us-ascii Michael, This is what I had archived from last year:

Hope this helps, Steve Mazer Forth Millennium Technologies

START OF PASTE >We ran into this problem on tables with Decimal columns. Couldn't change >the definitions, so we had to bypass Jet for the queries which, from the >rumblings I've heard lately, is not very popular with the developers. >Microsoft has a big writeup on it in their KB, but even after reading it >several times, I'm not sure i understand it. > > >*********** REPLY SEPARATOR *********** > > >On 10/30/98, at 9:11 AM, Steve Evans wrote: > > >>darin, [...] 19844 190 21_Re: Row level locking14_Wayne Driscoll19_WDriscoll@QUEST.COM31_Thu, 21 Oct 1999 11:16:47 -0700468_iso-8859-1 The LOCKRULE column only exists in DB2 UDB for OS/390. In UDB for Win/NT etc. DB2 will always take ROW locks, unless if the application issues a LOCK TABLE SQL statement, or if lock escalation occurs. It sounds like either the application is issuing a LOCK TABLE statement, or that it is taking so many row locks that the locks get escalated to a TABLE lock. Wayne Driscoll Product Developer Quest Software Inc. NOTE: All opinions are strictly my own. [...] 20035 209 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 14:26:20 -0400659_us-ascii Wayne, is the same true for DB2 for AIX too??



Wayne Driscoll on 10/21/99 01:16:47 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Re: Row level locking







The LOCKRULE column only exists in DB2 UDB for OS/390. In UDB for Win/NT etc. DB2 will always take ROW locks, unless if the application issues a LOCK TABLE SQL statement, or if lock escalation occurs. It sounds like either the application is issuing a LOCK TABLE statement, or that it is taking so many row locks that the locks get escalated to a TABLE lock. [...] 20245 40 18_Re: Old packages..11_rick creech18_ykcirc@HOTMAIL.COM29_Thu, 21 Oct 1999 11:37:05 PDT293_- If you know which plans are associated with which packages, and if you have used a method to capture plan summary info such as with Tmondb2 you can use the table landmark.plansumm, and if you have kept the plan history for the interval you need, then you could identify those not used. [...] 20286 100 15_Re: USA vs. ISO11_rick creech18_ykcirc@HOTMAIL.COM29_Thu, 21 Oct 1999 11:39:21 PDT601_- Thank you for your prompt and excellent response. Great minds must think alike. We are considering a very similar path to what you have used.

Rick

>From: dawn.kastelic@HIGHMARK.COM >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: USA vs. ISO >Date: Thu, 21 Oct 1999 13:09:44 -0400 > > We had the same issue due to the result of a merger. Our site had >USA >format as the default and the >other site had ISO. We had to choose one or the other in order to >implement a >sysplex/data sharing >environment so we chose ISO. The biggest issue [...] 20387 51 21_Re: Row level locking14_Wayne Driscoll19_WDriscoll@QUEST.COM31_Thu, 21 Oct 1999 11:41:44 -0700444_iso-8859-1 Yes, DB2 for Win/NT, AIX, OS/2, Linux and a number of other Unix platforms operate with the same code base, OS/390 (MVS) has a different code base. Wayne Driscoll Product Developer Quest Software Inc. NOTE: All opinions are strictly my own.



-----Original Message----- From: Nash Gupta [mailto:nashgupt@US.IBM.COM] Sent: Thursday, October 21, 1999 1:26 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Row level locking [...] 20439 40 21_Yahoo! Vacation Reply0_20_bowling730@YAHOO.COM31_Thu, 21 Oct 1999 11:54:01 -0700810_- testing vacation message

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



Original Message:



Return-Path: Received: from unknown (HELO ryci.com) (208.225.214.81) by mta123.mail.yahoo.com with SMTP; 21 Oct 1999 11:44:31 -0700 Received: from jupiter (jupiter.ryci.com) by ryci.com (LSMTP for Windows NT v1.1b) with SMTP id <0.0001BBB5@ryci.com>; Thu, 21 Oct 1999 13:35:31 -0700 Received: from RYCI.COM by RYCI.COM (LISTSERV-TCP/IP release 1.8d) with spool id 138810 for DB2-L@RYCI.COM; Thu, 21 Oct 1999 13:35:31 -0700 Received: from hotmail.com (f64.law4.HOTMAIL.com) by ryci.com (LSMTP for Windows NT v1.1b) with SMTP id <0.0001BBB2@ryci.com>; Thu, 21 Oct 1999 13:35:29 -0700 Received: (qmail 29415 invoked by uid 0); 21 Oct 1999 18:37:06 -0000 Received: from 206.2.56.100 [...] 20480 36 18_European IDUG 199911_Jerry Brown24_jerry_brown_fr@YAHOO.COM31_Thu, 21 Oct 1999 12:08:35 -0700375_us-ascii Well, that's European IDUG just about over for another year.

I was wondering what other peoples imnpressions were?

It's only the second time I have been able to attend, but I did think the exhibition area seemed smaller this year somehow, but I couldn't work out who was missing (except for the obvious substitution of PLATINUM by CA of course!). [...] 20517 34 13_Jan 1st posts10_Mark Labby16_mlabby@PHEAA.ORG31_Thu, 21 Oct 1999 15:18:58 -0400500_us-ascii Our organization was just conducting an internal survey of how we feel we will make out with the year 2000 turn over. Overall, our comfort level is very high - we've upgraded and tested everything that could be a problem. One thing that seems to have slipped the minds of a lot of us here in the US and Canada is that we will be near the last people to even hit the year 2000. Our comrades in Australia, Asia, Africa, and Europe will be discovering any problems hours before we should. [...] 20552 213 21_Re: Row level locking10_Nash Gupta19_nashgupt@US.IBM.COM31_Thu, 21 Oct 1999 14:46:10 -0400360_us-ascii I just checked out with the developers. It seems no body is using the LOCK TABLE statement in their apps. Also, is there any way to ensure that row locks do not get escalated to table locks??

- Nash

Wayne Driscoll on 10/21/99 01:16:47 PM

Please respond to DB2 Data Base Discussion List [...] 20766 26 23_EMC TimeFinder question0_21_kramps@NATIONWIDE.COM31_Thu, 21 Oct 1999 15:29:56 -0400435_us-ascii We are DB2 OS/390 v5.

One of our DB2 application areas has requested that we investigate using a product by EMC called TimeFinder on DB2 packs (we have the required dasd available). They would like to use this product to take snapshots of the packs that their data resides on. Then, if something bad happens, they would like to simply restore the packs from the snapshot, rather than going through a DB2 recovery. [...] 20793 11 8_Triggers0_27_Bud.Greenman@ONONDAGA.NY.US31_Thu, 21 Oct 1999 15:47:33 -0400327_- Can triggers be used with db2 for OS390/v5 or are they only available in version 6?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20805 53 12_Re: Triggers0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Oct 1999 14:53:43 -0500655_us-ascii At the DB2 tech conference, they said that triggers will not be retrofitted back to DB2 V5 for OS/390, they will only be available in DB2 UDB V6 for OS/390.













Bud.Greenman@ONONDAGA.NY.US on 10/21/99 02:47:33 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Triggers















Can triggers be used with db2 for OS390/v5 or are they only available in version 6? [...] 20859 37 46_REORG SHARELEVEL REFERENCE & SYSCOPY DDNAME !!14_Max 'Guderian'13_mocion@TIN.IT31_Thu, 21 Oct 1999 21:41:26 +0200495_iso-8859-1 I use reorg utility (DB2 V5) with SHARELEVEL REFERENCE and it' really nice and bullet proof (and, more important, now I can sleep during night time......).

But I have a question: is it possible to bypass the image copy built during reorg and execute an imagecopy after reorg sharelevel reference ended ? It'll be very useful in my shop (we have few tapes & DASD space....) there we execute a lot of partitions reorg in parallel and the use of the tape results impossible. [...] 20897 11 12_Re: Triggers0_27_Bud.Greenman@ONONDAGA.NY.US31_Thu, 21 Oct 1999 15:57:45 -0400340_- Thanks. I needed to know for a meeting tomorrow to justify going to v6 (we are currently on v4).

================================================ 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. 20909 48 27_Re: EMC TimeFinder question12_Myron Miller24_myron_miller@HOTMAIL.COM29_Thu, 21 Oct 1999 13:15:29 PDT438_- My client is using it and is extremely happy with it. Only problems they have is procedural. They normally bring DB2 down each morning, switch packs (disconnect via timefinder) and then bring DB2 back up. If this disconnect doesn't happen, the reconnect job in the afternoons will fail. They have to manually reconnect then. Other than that works great. Really simple to install as well. What other questions did you have in mind? [...] 20958 124 50_Re: REORG SHARELEVEL REFERENCE & SYSCOPY DDNAME !!0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Oct 1999 15:20:40 -0500378_us-ascii Max, I am glad to hear that someone else considers this a problem. I discussed this with Craig Friske from IBM at the 1998 DB2 technical conference, and afterward on 11/12/98 via email. I also discussed this briefly with him at the last DB2 technical conference. But, the last I heard, the inline image copy is still required for a sharelevel reference reorg. )-: [...] 21083 22 23_Stored procedure abends12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM31_Thu, 21 Oct 1999 17:23:06 -0400491_iso-8859-1 We are currently implementing stored procedures and have found that when a program abends while using the stored procedure, that stored procedure becomes unavailable until it is stopped and started again. This has become a pain in the butt for the application folks who are doing the testing, and I am very concerned how this will be handled when we get to production. How do other shops get around this problem without having someone manually stopping and starting the SPs. [...] 21106 49 27_Re: Stored procedure abends13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 21 Oct 1999 16:31:47 -0500477_iso-8859-1 We had the same situation to deal with. Short answer was to fix the programs and stop the abends. But to get the developers moving, and to save me time, the STOP/START ability was given to some programmers.

We planned to try and issue DISPLAYS and have our auto-console software restart procedures which had stopped/abended, but as time went on, the programs became stable and the programmers accepted that it was not a "DB2 problem". We never did that. [...] 21156 100 27_Re: Stored procedure abends0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Oct 1999 16:56:10 -0500597_us-ascii Andy, We created an automated process to issue a DB2 START PROCEDURE command after an abend of a DB2 STORED PROCEDURE. We also created an ISPF dialog for programmers to use to issue DB2 start commands if they changed the stored procedure parameters in SYSIBM.SYSPROCEDURES (using views on this table setup for each application per the redbook "Getting started with DB2 stored procedures", page 61). The dialog issues a message to automate which actually issues the db2 start procedure command. This avoided security issues with granting SYSOPR, SYSCTRL or SYSADM to too many people. [...] 21257 38 31_Unable to extend to next volume13_Peris, Ivon A23_IPeris@FSSA.STATE.IN.US31_Thu, 21 Oct 1999 17:04:09 -0500397_- Has anyone encountered this problem. Any help would be appreciated.

During the operation of reorg,load or even insert the Tablespace as well as the index does not go to the second volume after the first volume gets full, it fails with 00D70014 during Reorg/load and -904 during inserts. This takes place when it has taken few extents( less than 5, way less than 118 or 119 extents). [...] 21296 48 16_Re: sqlcode -8030_20_Troy.Coleman@UAL.COM31_Thu, 21 Oct 1999 12:42:06 -0500444_- The value your looking at is a RID id.

-803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'rid'

Troy Coleman

IBM Certified Solutions Expert - DB2 UDB V5 Database Administrator http://www.colemanconsulting.com/~troycci mailto:troycci@colemanconsulting.com [...] 21345 56 27_Question on DB2 PPT Modules14_Linda Hagedorn25_linda.hagedorn@EXCITE.COM29_Thu, 21 Oct 1999 16:38:49 PDT585_- >From: Medel Guevarra >What is the significance of defining PPT entries for the modules >DSNYASCP, DXRRLM00, DSNUTILB in the SCHEDxx member of parmlib? Our >system only has DXRRLM00 defined. Does this have any effect on our >overall DB2 system?

These entries are in the OS/390 shipped PPT. A table with the contents can be found in the 'OS/390 MVS Initialization and Tuning Reference, Document Number SC28-1752-05, Program Number 5647-A01'. See section 65.7 Program Properties Table (PPT), Figure 33 IBM-Supplied Program Properties Table (PPT) Values [...] 21402 52 21_Re: Row level locking0_18_rolchua@SES.COM.SG31_Fri, 22 Oct 1999 08:33:15 +0800754_us-ascii Hi Nash,

Check SYSIBM.SYSSTMT for possible "LOCK TABLE" sql statement in your program. Eg. Select TEXT from SYSIBM.SYSSTMT where NAME = dbrmname.













Nash Gupta on 22/10/99 00:41:54

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: CHUA CHONG KEE ROLAND/EDP/SES) Subject: Row level locking







Hello List! I believe row-level locking is a common feature of all major databases including DB2. Now here's the problem. I have this great app which grabs the entire table everytime it hits the database. It does this even if you insert just one row. Is it possible to restrict it [...] 21455 109 27_Re: EMC TimeFinder question0_29_Greg.Palgrave@BANKWEST.COM.AU31_Fri, 22 Oct 1999 08:52:50 +0800430_us-ascii Sue,

I don't know the product, but a couple of issues come to mind:

1. You will need to ensure DB2 is down, or the backups may be invalid due to the data in the buffers not being written to disk, files open etc.

2. What happens if a new DB2 object has been created (or dropped), or altered since the backup was taken? The physical structure on DASD will not match the catalog or be missing etc. [...] 21565 138 27_Re: EMC TimeFinder (errata)0_29_Greg.Palgrave@BANKWEST.COM.AU31_Fri, 22 Oct 1999 09:04:33 +0800427_us-ascii Oops! Forgot to mention that we exclude the CATALOG and our internal housekeeping datasets and some products (e.g. BMC) from this process. This is so we can keep the environment in synch with Prod as far as table definitions etc.

If you don't include the catalog, everything is internally consistent, but you would lose any changes you made since the backup and have to reapply them. [...] 21704 62 17_Re: Jan 1st posts11_Stout, Anne23_Anne.Stout@DOWJONES.COM31_Thu, 21 Oct 1999 21:46:37 -0400401_iso-8859-1 We have been testing our production system on a Y2K IPL system date two days a month every month this year... Every 'critical' application has been tested... I feel pretty comfortable... I must I volunteered to cover New Years eve! -----Original Message----- From: Mark Labby [mailto:mlabby@PHEAA.ORG] Sent: Thursday, October 21, 1999 3:19 PM To: DB2-L@RYCI.COM Subject: Jan 1st posts [...] 21767 148 27_Re: EMC TimeFinder question14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 22 Oct 1999 11:06:56 +1000422_us-ascii Sue, Just to add one more issue to those of Greg's below, the restored datasets will be 'down-level' and if you don't have the DLDFREQ ZPARM for that subsystem set to 0 you'll get (I think) -904's on access.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 22/10/99 10:28 ---------------------------



Greg.Palgrave@BANKWEST.COM.AU on 22/10/99 10:52:50 [...] 21916 55 27_Re: Stored procedure abends14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Fri, 22 Oct 1999 12:42:06 +1000373_- Andy,

There is a DSNZPARM parameter (STORMAX) which controls the number of allowable abends before the stored procedure is put in STOPABN status. The default is zero.

I would suggest that for test purposes you set it to a number greater than zero to avoid having to STOP/START the procedure each time it abends (I think we set it to 5 in development). [...] 21972 68 27_Re: EMC TimeFinder question0_18_rolchua@SES.COM.SG31_Fri, 22 Oct 1999 11:14:50 +0800348_us-ascii Hi Sue,

Before you used EMC TimeFinder to take snapshots of the DB2 tablespaces, you must first ensure that all updated data in the bufferpools are written to the dasd. (ie. by running Quiesce on all the affected tablespaces) Remember all these snapshots are not registered in DB2 Catalog so you must keep track them manually. [...]