1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2001, week 4 2 16 50_William D Trago/ISS/HQ/FHLMC is out of the office.10_Bill Trago33_William_D_Trago_Jr@FREDDIEMAC.COM31_Sun, 22 Apr 2001 01:02:50 -0400460_us-ascii I will be out of the office starting 04/20/2001 and will not return until 04/30/2001.

I will respond to your message when I return. If action needs to be taken immediately, Please contact Barbara Swart.

================================================ 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. 19 61 45_Re: Is Any Function for AutoNumber Generation19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Sun, 22 Apr 2001 18:26:17 -0500602_iso-8859-1 Hi, How to declare this IDENTITY column. I dont see any options while creating table or index to specify this in DB2 V5. Thanks for your help.

-----Original Message----- From: Aurora Dell'Anno [mailto:Aurora_E_DellAnno@CANDLE.COM] Sent: Friday, April 20, 2001 11:49 PM To: DB2-L@RYCI.COM Subject: Re: Is Any Function for AutoNumber Generation



Rabi,

IDENTITY columns may be the answer to your question, depending on what you have to do with your "counter" at a later date (not advisable to try and index those columns, for example, for a number of reasons). [...] 81 19 44_Convert 4k pages to 8k pages for tablespaces17_Andrew Robert Lim16_limar@SG.IBM.COM31_Sun, 22 Apr 2001 19:38:31 -0500324_- hi,

I'm trying to convert a tablespace from 4k to 8k pages becoz of the physical limits of DB2 5.2 (64 GB or 200 containers per tablespace). Was wondering if anyone out there has done this? I gather the way to do this is:-

1. Migrate from 5.2 to 6.1 2. Use db6convt to convert tablespace from 4k to 8k. [...] 101 80 45_Re: Is Any Function for AutoNumber Generation16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 23 Apr 2001 10:19:08 +0530633_- This is not available with V5(i think i am right !!). Look at the manual What's New in V6 for IDENTITY column.

Regards Sanjeev

> -----Original Message----- > From: K.Balaji (Exchange) [SMTP:K.Balaji@TARGET.COM] > Sent: Monday, April 23, 2001 4:56 AM > To: DB2-L@RYCI.COM > Subject: Re: Is Any Function for AutoNumber Generation > > Hi, > How to declare this IDENTITY column. I dont see any options while creating > table or index to specify this in DB2 V5. > Thanks for your help. > > -----Original Message----- > From: Aurora Dell'Anno [mailto:Aurora_E_DellAnno@CANDLE.COM] > Sent: Friday, April 20, 2001 11:49 [...] 182 25 50_C37Y - DB2 for OS/390 V6 Transition - Last Chance!15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 23 Apr 2001 17:12:26 +1000422_- Colleagues,

Obviously everyone here in Melbourne already knows all there is to know about DB2 UDB for OS/390 V6, 'cause myself and one other person are the only people currently enrolled for this 3-day course starting 7th May. It's not enough people for IBM to hold it. Help! If anyone is able to attend please contact Danielle Lofting at IBM Learning Services in Melbourne on (3) 9626 6123 or 1800 801 088. [...] 208 63 15_Re: DB2 and WLM18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Mon, 23 Apr 2001 02:14:26 -0500757_- Howzit Mike?

Have a look in the following redbook: "Getting Started with DB2 Stored Procedures" for a good intro to DB2 and WLM application environment setup.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia



On Wed, 18 Apr 2001 16:07:52 -0400, Clark, Kevin wrote:

>1) Check out the WLM site ..lots of related links and paper >http://www.s390.ibm.com/wlm/ >2) MVS planning: WORKLOAD MANAGEMENT AND (GC28-1761-10) FOR 2.8 >3) Red book : OS/390 WORKLOAD MANAGER IMPLEMENTATION AND EXPLOITATION >sg24-5326-00 > > > >Kevin Clark > > -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: [...] 272 29 30_Re: change db's instance owner0_23_JENNY@VMA.TABNSW.COM.AU31_Mon, 23 Apr 2001 17:36:57 +1000359_- Hi all, We have a requiremnt to create new instance , and move a large database from the current db2 instance to this new instance and it took 7 hours to backup the database, We would like to recatalog the database to the new instance.. but failed during testing. Is there a way to change the instance owner of a database without a backup and restore? [...] 302 201 45_Re: Is Any Function for AutoNumber Generation17_Rajkumar Gurusamy32_Rajkumar.G@CHENNAIMAIL.LTITL.COM31_Mon, 23 Apr 2001 12:56:54 +0530634_us-ascii Hi,

Hope the following information help on INDENTITY,

An identity column is a numeric column with ascending or descending values. For an identity column to be the most useful, those values should also be unique.

An identity column is defined in a CREATE TABLE or ALTER TABLE statement. The column has a SMALLINT, INTEGER, or DECIMAL(p,0) data type and is defined with the AS IDENTITY clause. The AS IDENTITY clause specifies that the column is an identity column. The column is also defined with the GENERATED ALWAYS or GENERATED BY DEFAULT clause. GENERATED ALWAYS means that DB2 generates a value [...] 504 95 25_Codepage & Backup Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Mon, 23 Apr 2001 17:08:03 +0700344_us-ascii Dear all,

We are having problem when restoring our backup DB into our AIX box. The problem that occured was about Codepage difference between the backup and the DB2 server. Our backup is in 1252 Codepage while our DB2 is in 819 Codepage. Is there any way that we can overcome this? Some options to change the DB2 codepage? [...] 600 27 34_UDB sysadm without NT Admin rights28_=?iso-8859-1?q?SPUFI=20Doo?=20_spufidoo@YAHOO.CO.UK31_Mon, 23 Apr 2001 12:01:19 +0100454_iso-8859-1 Dear all,

I have a friend who has UDB 7.1 installed on his PC, but is not allowed to use it because he is not allowed NT admin rights by the powers that be.

Is there a way around this?

Yours, Frustrated of Stevenage.

____________________________________________________________ Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie [...] 628 28 38_Re: DB2/RACF Install SYSADM RACF GROUP13_Fons Heirbaut27_Alfons.Heirbaut@AXA-BANK.BE31_Mon, 23 Apr 2001 06:03:03 -0500649_- On Thu, 19 Apr 2001 09:37:15 -0500, Kornblum, Kenneth wrote:

>Group IDs certainly work with both native DB2 security as well as with the >DSNX@XAC exit active. The ZPARM is honored ahead of other security checks, >so RACF (or internal auth tables) is not even referenced if you are the >Install SYSADM (or have one of the Install SYSADM IDs in your >list-of-RACF-groups that you are connected to). > That's my opinion too, but using RACTRACE during execution of QMF I found security-calls for EXECUTE PACKAGE (DSQABOR, DSQADB31, DSQADYSQ, DSQAFSQL, DSQAICVS, DSQALD, DSQARCTL, DSQARDBR, DSQASDB2, DSQASUSR). [...] 657 55 45_db2/os390 to db2/nt websphere commerce studio15_Pierre Dagenais19_pdagenai@UOTTAWA.CA31_Mon, 23 Apr 2001 08:13:56 -0400502_us-ascii Hi everyone,

I would like to setup the communication from db2/360 v6 to db2/nt of websphere commerce studio. I am trying to bind spufi from db2/os390 into db2/nt. At first I didn't have the proper database name of db2/nt. I fixed that.

Then I didn't have the proper password of the account on db2/nt . I fixed that. Now I have this: sqlcode=-30073 The book says there is a communication problem between the two db2 because of the characters. On db2/390 we have ccsid = 500. [...] 713 125 31_Re: DB2 connect & shadow tables11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Mon, 23 Apr 2001 07:16:53 -0500275_windows-1252 It's DB2 V6.1 on OS/390. After talking with my co-worker more, thought, this isn't the way we want to go. We want to be able to reload the data nightly from a batch job on the mainframe. He's just going to keep testing until he doesn't get any more errors. [...] 839 34 38_Re: DB2/RACF Install SYSADM RACF GROUP13_Fons Heirbaut27_Alfons.Heirbaut@AXA-BANK.BE31_Mon, 23 Apr 2001 07:01:01 -0500659_- On Mon, 23 Apr 2001 06:03:03 -0500, Fons Heirbaut wrote:

>On Thu, 19 Apr 2001 09:37:15 -0500, Kornblum, Kenneth > wrote: > >>Group IDs certainly work with both native DB2 security as well as with the >>DSNX@XAC exit active. The ZPARM is honored ahead of other security checks, >>so RACF (or internal auth tables) is not even referenced if you are the >>Install SYSADM (or have one of the Install SYSADM IDs in your >>list-of-RACF-groups that you are connected to). >> >That's my opinion too, but using RACTRACE during execution of QMF I found >security-calls for EXECUTE PACKAGE (DSQABOR, [...] 874 34 33_Re: DB2/RACF execute QMF-packages13_Fons Heirbaut27_Alfons.Heirbaut@AXA-BANK.BE31_Mon, 23 Apr 2001 07:20:11 -0500643_- On Thu, 19 Apr 2001 07:45:03 -0500, Billings, Linda wrote:

>When access is remote then the package is executed directly and is not >invoked by a plan. That is why execute authority much granted on the >package.

On Thu, 19 Apr 2001 07:31 Holmans Mike wrote: > >It depends what's trying to execute. If you are invoking QMF as a >directly-logged-on user of the OS/390 box, then the run-time authorisation >check is at the plan level, so the users need EXECUTE on the QMF plan. >However, if you are invoking QMF remotely, then the package-level >authorisation is checked [...] 909 18 28_Re: REORG UNLOAD/LOAD FORMAT13_Thomas Schulz20_thomas.schulz@SVI.DE31_Mon, 23 Apr 2001 07:56:38 -0500481_- smike

unload external is not supported in Version 5 just V6 and higher

Unless you specify UNLOAD EXTERNAL, data can be reloaded only into the same table and table space (as defined in the DB2 catalog) on the same subsystem.

================================================ 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. 928 72 24_Re: UDB Quick Reference?12_Susan Lawson24_susan_lawson@YLASSOC.COM31_Mon, 23 Apr 2001 08:45:00 -0500484_iso-8859-1 Hi all,

YL&A has written a Version 7 quick reference for both OS/390 and UDB (non-OS/390). It is about 400 pages and is being printed and distributed by BMC at IDUG.

Regards, Susan Lawson www.ylassoc.com DB2 Certified Soultions Expert

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Pav Kumar-Chatterjee Sent: Thursday, April 19, 2001 3:51 PM To: DB2-L@RYCI.COM Subject: Re: UDB Quick Reference? [...] 1001 91 24_Re: UDB Quick Reference?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 23 Apr 2001 09:53:12 -0400521_iso-8859-1 Any way those not in the wealthy elite able to attend IDUG can get one?

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Susan Lawson [mailto:susan_lawson@YLASSOC.COM] Sent: Monday, April 23, 2001 9:45 AM To: DB2-L@RYCI.COM Subject: Re: UDB Quick Reference?



Hi all,

YL&A has written a Version 7 quick reference for both OS/390 and UDB (non-OS/390). It is about 400 pages and is being printed and distributed by BMC at IDUG. [...] 1093 51 54_Re: C37Y - DB2 for OS/390 V6 Transition - Last Chance!15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Mon, 23 Apr 2001 09:54:18 -0400396_iso-8859-1 I'd love to go to Melbourne for the class, I like it there very much, but my company would have a hard time sending me down under from New Hampshire for a 3 day class.

Rich

Richard A Sharpe DBA Amherst Corporate Computer Sales and Solutions 40 Continental Blvd Merrimack, NH 03054 Phone: (603) 579-6180 / 800-431-8031 Fax: (603) 578-1072 Email: rsharpe@amherst1.com [...] 1145 55 28_Re: REORG UNLOAD/LOAD FORMAT0_19_Tim.Lowe@STPAUL.COM31_Mon, 23 Apr 2001 09:02:16 -0500402_us-ascii Thomas, I used REORG UNLOAD EXTERNAL when I was still running DB2 V5. See Apar PQ19897, PTF UQ23242, from 11/24/1998.







Thomas Schulz cc: Sent by: DB2 Subject: Re: REORG UNLOAD/LOAD FORMAT Data Base Discussion List



04/23/2001 07:56 AM Please respond to DB2 Data Base Discussion List [...] 1201 113 79_Re: The right procedure to change CCSID from 500 to 420 of existing user data .16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Mon, 23 Apr 2001 15:10:01 +0100410_us-ascii Islam,

the thread is interesting but long and a bit foreign to this list, so hope it's ok if we continue offline!

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK [...] 1315 25 38_oracle stored procedure via datajoiner11_David Ayers24_david.ayers@HIGHMARK.COM31_Mon, 23 Apr 2001 10:16:58 -0400326_us-ascii List, is anyone calling Oracle stored procedures that return a result set via datajoiner 2.1.1. The application runs on os/390 / cobol 370 / db2 v6.1. When we try to bind the remote package with the sp call, it abends with a -104 on the allocate cursor. Our sql looks like (extracted from the datajoiner manual) [...] 1341 20 15_debugger in db211_arvind heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET31_Mon, 23 Apr 2001 19:39:20 -0400483_us-ascii hi listmembers, I am a new user of DB2, migrating from oracle. can anybody help me regarding the db side configuration of the debugger for stored procedures , which can connect to the distributed debugger of IBM

thanks arvind

================================================ 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. 1362 126 24_Re: UDB Quick Reference?14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Mon, 23 Apr 2001 09:22:39 -0500689_iso-8859-1 Sure - check with us or BMC after IDUG, around May 21st.

Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Pearson, Eric L, > Sent: Monday, April 23, 2001 8:53 AM > To: DB2-L@RYCI.COM > Subject: Re: UDB Quick Reference? > > > Any way those not in the wealthy elite able to attend IDUG > can get one? > > regards, > > eric pearson > NS ITO Database Support [...] 1489 47 32_DB2 version 7 - OSS NOTE 039219412_Paul Packham26_paul.packham@CONSIGNIA.COM31_Mon, 23 Apr 2001 15:17:58 +0000382_us-ascii Hi,

One of my SAP colleagues passed the following to me regarding SAP & DB2 V7.1. Apparently SAP does not work with DB2 V7. 1 at the moment. The relevant bits are as follows : Symptom As with any new DB2 release, SAP intends to support DB2 V7 after a formal verification process which tests the release functionality and quality in regards to SAP environments. [...] 1537 17 28_Re: REORG UNLOAD/LOAD FORMAT10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 23 Apr 2001 16:34:58 +0200378_us-ascii You can use UNLOAD EXTERNAL with V5, it was implemented thru APARs. See DB2 home page at IBM site.

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 1555 14 11_Re: DSNTEP213_Thomas Schulz20_thomas.schulz@SVI.DE31_Mon, 23 Apr 2001 09:25:47 -0500366_- Hi John check the following URL may be it can Help you Should be DSNTEP2 in Cobol http://www.gseukdb2.org.uk/shareware.htm

================================================ 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. 1570 30 18_MSSQLserver to DB215_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Mon, 23 Apr 2001 10:50:30 -0400379_- Hi

I am going to be converting MSSQLserver 2000 databases over to DB2, has anyone done this and what have been the pitfalls, there are a number of methods to do the actual data conversion, DBArtisian from Embarcadero, and of course IBM's datajoiner and favorites, I am in a fact gathering state right so what ever tips you may want to share I thank you in advance. [...] 1601 15 43_Re: MSsqlserver migration to DB2 any tips ?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 23 Apr 2001 10:49:46 -0500389_- One of the best resources is probably the

DB2 UDB V7.1 Porting Guide, SG24-6128-00 Redbook, published December-11-2000

Roger Miller

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 1617 35 24_MTS - DB2 OS390 Contacts16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 23 Apr 2001 12:45:03 -0400406_us-ascii List serve participants:

I am interested in contacting knowledgeable individuals who possess strong hands-on, practical experience utilizing the following toolsets:

- MicroSoft Transaction Server (MTS) and Distributed Transaction Coordinator (DTC) - Visual Basic, C++, COBOL - DB2 Connect running on NT - DB2 CLI/ODBC, MS ActiveX Data Objects (ADO) - DB2 for OS390 V5 and/or V6 [...] 1653 120 24_Re: UDB Quick Reference?14_David Bargeron29_David.Bargeron@BTINTERNET.COM31_Mon, 23 Apr 2001 18:40:30 +0100660_iso-8859-1 "Ditto for those of us in the UK !"

Thanks

Dave Bargeron LloydsTSB



----- Original Message ----- From: Pearson, Eric L, Newsgroups: bit.listserv.db2-l To: Sent: Monday, April 23, 2001 2:53 PM Subject: Re: UDB Quick Reference?



> Any way those not in the wealthy elite able to attend IDUG > can get one? > > regards, > > eric pearson > NS ITO Database Support > > > -----Original Message----- > From: Susan Lawson [mailto:susan_lawson@YLASSOC.COM] > Sent: Monday, April 23, 2001 9:45 AM > To: DB2-L@RYCI.COM > Subject: Re: UDB Quick Reference? > > > Hi all, > [...] 1774 40 38_Re: UDB sysadm without NT Admin rights11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Mon, 23 Apr 2001 18:43:00 +0100392_us-ascii What you do is get someone who has NT admin rights to update the dbm cfg. Set the sysadm_group to be something that your user is in. Stopping and starting will pick up the change in configuration. They will then be able to use DB2.

One warning, you may need to change the id that some of the DB2 services run as. It's a while since I did this and can't remember exactly. [...] 1815 41 28_Re: REORG UNLOAD/LOAD FORMAT0_19_mike.holmans@BT.COM31_Mon, 23 Apr 2001 14:48:27 +0100624_- UNLOAD EXTERNAL *is* a V5 facility, as long as you have kept up with the maintenance.

Mike Holmans BT ISE Database Services mike.holmans@bt.com

This post represents the views of the author and does not necessarily accurately represent the views of BT

> -----Original Message----- > From: Thomas Schulz [SMTP:thomas.schulz@SVI.DE] > Sent: Monday, April 23, 2001 1:57 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] REORG UNLOAD/LOAD FORMAT > > smike > > unload external is not supported in Version 5 > just V6 and higher > > Unless you specify UNLOAD > EXTERNAL, data can be reloaded only into the [...] 1857 103 36_Re: Trigger calling Stored Procedure0_26_mark.walker@CUNAMUTUAL.COM31_Mon, 23 Apr 2001 12:56:30 -0500310_us-ascii Thanks to all that replied! The transition class manual gave me a hint and when I looked at the Redbook on stored procedures, that clarified it. We are going to go with a ROLLBACK in the stored procedure to trigger a bad sqlcode in the trigger. It looks like this should still work under V7 too. [...] 1961 35 43_Re: MSsqlserver migration to DB2 any tips ?15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Mon, 23 Apr 2001 14:19:59 -0400426_- Thanks, If I find other info I will pass it along too

Thanks again

Rich

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Monday, April 23, 2001 11:50 AM To: DB2-L@RYCI.COM Subject: Re: MSsqlserver migration to DB2 any tips ?



One of the best resources is probably the

DB2 UDB V7.1 Porting Guide, SG24-6128-00 Redbook, published December-11-2000 [...] 1997 230 45_Re: Is Any Function for AutoNumber Generation15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Mon, 23 Apr 2001 11:59:49 -0700595_us-ascii Hi Rajkumar.. I am working in UDB/AIX V6.1. I did not find document regarding Identity columns. Does it exist in V6.1? regards Rajesh

--- Rajkumar Gurusamy wrote: > Hi, > > Hope the following information help on INDENTITY, > > An identity column is a numeric column with > ascending or descending values. > For an identity column to be the most useful, those > values should also be > unique. > > An identity column is defined in a CREATE TABLE > or ALTER TABLE statement. > The column has a SMALLINT, INTEGER, or DECIMAL(p,0) > data type [...] 2228 28 22_DB2 v5 to v7 migration11_Brown, Bill28_sgjwb@TECHMAIL.ADMIN.TTU.EDU31_Mon, 23 Apr 2001 14:56:02 -0500539_iso-8859-1 I'd like to thank those that responded to my question regarding the v5 to v7 migration. I guess I don't have a compelling reason to skip v6 other than eliminating an upgrade, that, and that it's what my boss wants to do. I guess my thinking is that unless the v5 to v7 jump is a vicious, snarling beast why should I go through the joy of upgrading my DB2 environment twice instead of once? Not being familiar with DB2 migration I have no range of comparison, but everyone keeps telling me not to try it. My guess is absent [...] 2257 16 26_Re: DB2 v5 to v7 migration26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Mon, 23 Apr 2001 15:06:59 -0500436_- Thought that was the tornado research center? My dad visited Lubbock a few days after the original. Very impressive..... Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2274 33 36_CACHEDYN Vs MAXKEEPD parameter query5_Group17_db2list@YAHOO.COM31_Mon, 23 Apr 2001 13:02:05 -0700346_us-ascii Hi ,

If we go for CACHEDYN(dynamic sql cache) =NO but MAXKEEPD =300 (to save the dynamic sql stmt past a commit point For the plans bind with KEEPDYNAMIC =YES ) Do we still have to increase the size of EDMPOOL .

i think we should not increase the EDMPOOL size Because EDMPOOL caches dynamic sql only if CACHEDYN=YES . [...] 2308 34 26_Re: DB2 v5 to v7 migration11_Brown, Bill28_sgjwb@TECHMAIL.ADMIN.TTU.EDU31_Mon, 23 Apr 2001 15:14:54 -0500389_iso-8859-1 Yes, we do have a tornado research center here. And a cannon that shoots 2X4's through cinderblock walls at a hundred miles per hour! What could be more fun than that?

-Bill

-----Original Message----- From: Edward(Ed) J. Finnell, III [mailto:efinnell@SEEBECK.UA.EDU] Sent: Monday, April 23, 2001 3:07 PM To: DB2-L@RYCI.COM Subject: Re: DB2 v5 to v7 migration [...] 2343 42 56_Few Seats Left in Dallas - April 26-27 for Yevich/Lawson12_Jorge Rivera21_bpisols@BELLSOUTH.NET31_Mon, 23 Apr 2001 16:50:17 -0400360_Windows-1252 There are a few seats left for the special offering of two 1-days sessions given by Richard Yevich and Susan Lawson. The sessions are only 50$ each and are being held in cooperation with the Dallas DB2 Users Group.

April 26 - DB2 High Performance Design and Tuning April 27 - Advanced Programming: Stored Procedures, Triggers and UDFs [...] 2386 43 45_Re: Is Any Function for AutoNumber Generation12_Susan Lawson22_lawson_susan@YAHOO.COM31_Mon, 23 Apr 2001 14:15:08 -0700348_us-ascii > Rajkumar, > Rajkumar

Identity columns are introduced to DB2 UDB(non-OS/390) in Version 7.

Regards, Susan > > --- Rajesh Udandrao wrote: > > Hi Rajkumar.. > > I am working in UDB/AIX V6.1. I did not find > > document regarding Identity columns. Does it exist > > in > > V6.1? > > regards > > Rajesh [...] 2430 99 29_Re: DB2 Connect and MS Access17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Mon, 23 Apr 2001 15:18:00 -0700322_us-ascii Thanks Leon. It works great for the smaller tables but doesn't work for the larger tables where we really want to limit the rows returned. According to our monitors it keeps issuing getpages but returns no rows until we finally have to cancel it. I'll see what's different with these tables other than size. [...] 2530 56 29_Re: Codepage & Backup Problem14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Mon, 23 Apr 2001 19:09:43 -0500545_iso-8859-1 I've run into this a couple of times on NT when servers have been set up with different codepage defaults.

When you create a database you can specify which codepage it should use (either via the GUI or command line "USING CODESET codeset "). SO if you CREATE a database "FRED" with the codepage 1252 on your DB2 server (with a different codepage), then restore your backup to this database, you should get around the problem. It's a wonder IBM doesn't have a "RESTORE as CODESET nnn" option . HTH, [...] 2587 29 34_Coalesce replacing NULL processing12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Tue, 24 Apr 2001 10:26:23 +1000576_- Hi all, whilst doing some basic performance analysis on an application I've been coming across a lot of COALESCE functions to avoid having the Cobol program move default values to a field where a null exists. Particularly to cater for the NULLS on an outer join.

My initial thought was that no it should be done in the program to give DB2 a break, but on consideration began to think the processing over-head would be pretty much the same, as they are basically TEST & MOVE instructions that should not be significantly more expensive in one place than another. [...] 2617 155 37_Baltimore/Washington DB2 Users' Group17_Valerie J Traurig18_valeriet6@JUNO.COM31_Mon, 23 Apr 2001 22:02:08 -0400697_- Baltimore/Washington DB2 Users' Group

Meeting Date: Thursday, June 14, 2001 Location: Sheraton Columbia Inn 10207 Wincopin Circle Columbia, MD (410) 730-3900

Meeting Schedule: 8:30 - 9:00 Registration

9:00 - 10:15 Identifying and Tuning DB2 UDB (Windows, Unix, Linux) Configuration Parameters Philip Gunning, Quest Software

DB2 UDB is one of the premier databases used for OLTP and OLAP. It is loaded with functions and features. However, to ensure that your company is getting the best performance possible from DB2 UDB, an in-depth understanding of Database Manager and Database Configuration parameters is required. The old rule that you only make one change [...] 2773 39 0_20_Balaguru Paramasivan17_pbalaguru@USA.NET29_Mon, 23 Apr 2001 20:33:19 MDT403_US-ASCII Hi,

Can you tell me what is the significance of defining table fields within quotes. (eg)

CREATE TABLE table-name ( field1 INT NOT NULL , "TYPE" SMALLINT NOT NULL , field2 INT NOT NULL )



I feel, since TYPE is the reserveword we are defining within qoutes. Is it correct? But when I tried to create without quotes it works fine. Can anyone clear me about this. [...] 2813 75 54_Re: C37Y - DB2 for OS/390 V6 Transition - Last Chance!15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 24 Apr 2001 12:51:45 +1000558_- Aah, what's wrong with them? Melbourne is nice this time of year. If you're a duck, that is. My gut feel is that the course won't run due to lack of interest. Damn. Oh well, can't say I didn't try.



Raymond

> -----Original Message----- > From: Sharpe, Richard [SMTP:rsharpe@AMHERST1.COM] > Sent: Monday, 23 April 2001 11:54 pm > To: DB2-L@RYCI.COM > Subject: Re: C37Y - DB2 for OS/390 V6 Transition - Last Chance! > > I'd love to go to Melbourne for the class, I like it there very much, but > my > company would have a hard time [...] 2889 64 38_Re: Coalesce replacing NULL processing11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 23 Apr 2001 22:00:31 -0500585_- Nick,

My only concern is that programmers tend to get creative with the "default" value. For example - for a date column, someone can use 9999-12-31, someone else can use 0001-01-01 etc etc - variations that go against uniformity of code we strive for.

The other more serious concern is that it can (sometimes) disguise a true business condition of interest. For example, coalescing the sum of an amount to 0 may not allow me to distinguish between the cases: (a) no rows exist or (b) rows exist but the amounts add up to zero. The programmer writing the original [...] 2954 73 38_Re: Coalesce replacing NULL processing14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Mon, 23 Apr 2001 22:01:21 -0500448_iso-8859-1 No, they are not basically the same. You can equate the return of a value and a null indicator to a program as 2 cross memory transfers, as in 2 columns, instead of 1 -- twice the overhead there (a little less but 2 columns are populated in the program storage area - column and null indicator). Also, the optimization of the code in DB2, running at its priority is quite different that program code -- more overhead if in program. [...] 3028 65 34_Re: Reserved Word (formerly blank)13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 23 Apr 2001 23:25:05 -0500360_us-ascii Bala,

Off the top of my head, I'd say there are times when references to reserved words as column names are ambiguous and therefore need to be enclosed in quotes. And there are times when you can get away without enclosing the column name in quotes. I'd say you've found a time when the reference is unambiguous and thus works either way. [...] 3094 19 14_plan last used12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Tue, 24 Apr 2001 05:44:14 -0000486_- ALL, Is there a way to know when a plan is last used ? Thanx in advance. Regards Rakesh

_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3114 100 24_Re: UDB Quick Reference?14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Tue, 24 Apr 2001 07:59:19 +0200618_iso-8859-1 Hi everyone,

there's also "DB2 UDB for OS/390 Developer's quick reference guide' by Gary Joehlin in the MVS Training INC. Mainframe Series.

Susan Lawson schrieb: > > Hi all, > > YL&A has written a Version 7 quick reference for both OS/390 and UDB > (non-OS/390). It is about 400 pages and is being printed and distributed by > BMC at IDUG. > > Regards, > Susan Lawson > www.ylassoc.com > DB2 Certified Soultions Expert > > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Pav Kumar-Chatterjee > Sent: Thursday, April 19, 2001 3:51 PM [...] 3215 29 27_change db 's instance owner0_23_JENNY@VMA.TABNSW.COM.AU31_Tue, 24 Apr 2001 16:55:31 +1000359_- Hi all, We have a requiremnt to create new instance , and move a large database from the current db2 instance to this new instance and it took 7 hours to backup the database, We would like to recatalog the database to the new instance.. but failed during testing. Is there a way to change the instance owner of a database without a backup and restore? [...] 3245 11 38_Re: UDB sysadm without NT Admin rights9_SPUFI Doo20_spufidoo@YAHOO.CO.UK31_Tue, 24 Apr 2001 03:39:46 -0500332_- Thanks, Jeremy, but what if no local users/groups are allowed, only server groups/users?

================================================ 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. 3257 41 18_Re: plan last used0_19_mike.holmans@BT.COM31_Tue, 24 Apr 2001 10:23:10 +0100376_- There isn't anything you can find in the catalog which will tell you that. You would need to use a log analysis product or such historical capabilities as your performance monitor may have.

Mike Holmans BT ISE Database Services mike.holmans@bt.com

This post represents the views of the author and does not necessarily accurately represent the views of BT [...] 3299 54 18_AW: plan last used12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Tue, 24 Apr 2001 11:36:53 +0200419_iso-8859-1 Hi Rakesh

1.) I don't know if you have a monitoring tool. As far as I know, Omegamon has the capability to deliver the information you want. I think so have the others. 2.) It's a wild guess because I am new to this: What info is the DB2 tracing able to give? You can specify up to 8 plan names with the START TRACE command. Well, I think we'll have to wait until one of the gurus jumps in here. [...] 3354 30 50_Pls tell me how to put a COM into DB2 database ???10_Do Duy Nam19_duynam@RIM-TECH.COM31_Tue, 24 Apr 2001 16:30:15 +0700300_us-ascii Hi all DB2 gurus,

I am a beginner of DB2 and I want to study this matter:

I have a business object represented by a DLL written in VB6. I am wondering whether can I store it in DB2 database and then how to retrieve it from DB (using Visual basic invironment for example). [...] 3385 143 45_Re: Is Any Function for AutoNumber Generation16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 24 Apr 2001 11:27:29 +0100346_us-ascii Sanjeev, Rabi,

yes of course you have to have DB2 UDB for OS/390 v. 6.1 - I somehow assumed this was the case, hope it wasn't clearly specified otherwise :-(

this option was not available even as a PTF to v 5 at my last check - sorry.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...] 3529 29 18_performance tuning13_manish raj kr26_krmanishraj@REDIFFMAIL.COM31_Tue, 24 Apr 2001 10:45:11 -0000482_- Hi List, I have been asked to look into the performance of a module consisting of twenty online application and 33 batch applications.The application are in cobol cics and db2 v5. I have never done this sort of activity. I would like to listen from the list how to start in this direction.I know that the performance can be improved by tuning the sql but not aware of the step involved.Is there any toll available to tune the sql or is it involves only try and error method. [...] 3559 98 38_Re: Coalesce replacing NULL processing16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 24 Apr 2001 12:23:26 +0100373_us-ascii Nick,

in my previous life as an application programmer, the argument was that DB2 code is well written, whereas application code sometimes is not - and therefore it would be better to let DB2 take care of these instances rather than risk application programmers (like myself), changing some parts of the program and not find what they wanted in there. [...] 3658 59 18_Re: plan last used12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Tue, 24 Apr 2001 06:34:23 -0500540_iso-8859-1 Rakesh:

If what you mean is when was the last time a plan with a specific name was executed then you can find this by reviewing the SMF data for the 101 records that reflect that plan name and look at the dates. This assumes you have kept DB2's SMF data and have either a performance data base or minimally DB2 PM's batch reporting or something that mimics those reports. I have found that outside of the US SMF data is not kept because the same legal requirements do not exist. Usually in the US, one can go back up [...] 3718 84 34_Re: Reserved Word (formerly blank)19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 24 Apr 2001 06:49:13 -0500589_iso-8859-1 Hi Terry, Bali, I would agree strongly with Terry NOT to use reserved words as column names, with or without quotes. I recently ran across a similar problem in DB2 for OS390 V6 and COBOL. I tried to use EDITABLE, VISIBLE, and REQUIRED as column names. I could not find these words on any reserved word list for DB2 or COBOL. The DB2 precompiler had no problems with them, however, the DBRM wasn't generated correctly yielding incorrect program execution (had fun finding this problem). I finally gave up and added a _X after each word, e.g. REQUIRED_X. Anyone had problems [...] 3803 61 22_Re: performance tuning14_Mohan Bezawada29_Mohan_Bezawada@CPR.SATYAM.COM31_Tue, 24 Apr 2001 16:42:05 +0530470_- Manish To my knowledge , I think You have to look into efficiency of the sql queries coded in your programs, the predicate conditions, usage of joins, usage of cursors in application programming etc. when u are using multiple tables in your queries make sure they use an appropriate matching conditions in search criteria. This is what I have done during my first job task. just wait for the gurus to come up with more clear information and expert ideas. Regards [...] 3865 60 33_problem calling stored procedures11_Arvind Heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET31_Tue, 24 Apr 2001 17:54:36 -0400338_iso-8859-1 hi list, I am trying to call a java stored procedure from another java store procedure, this gives an exception stating that call in that context is not allowed. "is it means that a java stored procedure cannot call another?" if yes then can UDF or any other way exists to call stored procedure from within the DB itself. [...] 3926 146 34_AW: Reserved Word (formerly blank)12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 24 Apr 2001 14:39:12 +0200331_iso-8859-1 Hi Rick,

NOT using "normal" SQL reserved words and NOT using IBM SQL reserved words is the best choice to reduce confusion and ambiguity.

I think it is important to mention a third kind of reserved words which should NOT be used: The ANSI SQL reserved words. More info please see http://www.ansi.org/ [...] 4073 130 34_Re: Reserved Word (formerly blank)15_Jackson Reavill18_damcon2@US.IBM.COM31_Tue, 24 Apr 2001 08:53:06 -0400596_us-ascii Hi Rick, Terry, Bali,

Just another little tidbit... I ran into a problem when SELECT was used as a column name (without quotes). Everything worked fine until a check constraint was put on that column. The constraint was created without any issues, but when DB2 UDB V6 on AIX attempted to enforce the constraint (during insertion of a row), the internal DB2 code that was generated to do the check failed with a syntax error. Also, there were columns named INSERT, UPDATE, and DELETE. However, these didn't cause any errors at all, even though they had the same setup. Bottom [...] 4204 73 22_Re: performance tuning19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 24 Apr 2001 07:52:30 -0500634_iso-8859-1 Manish, DB2 EXPLAIN is what you'll use to see if the SQL needs tuning/rewriting or indexes need to be built. But first make sure that the tables and indexes accessed by the Application don't need a reorg and that you have recent RUNSTATS. Another, often overlooked, area to study is overall Application design and program detail design. The overall design should have each program accessing the tables, as much as possible, in the same order -- this helps prevent deadlocks in Applications requiring concurrency. Repeatedly opening and closing cursors when not required is sometimes a big hitter. Commit frequency, or [...] 4278 83 22_Re: performance tuning15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 24 Apr 2001 07:55:02 -0500511_ISO-8859-1 Hi, Manish, I haven't done a lot of this and it has been a while but the first step is to gather information about your systems. There are probably a few programs having a problem and you need to find out which ones they are. I wouldn't assume that it is the SQL right away. Keep an open mind. If there are any SMF historical data and a reporting tool to go with it look there to see which programs seem to be taking the most time. If you have a product like Strobe then use that. If I remember [...] 4362 20 38_Another DB2 Connect/MS Access Question0_22_Tim.T.Dupree@LOWES.COM31_Tue, 24 Apr 2001 08:55:08 -0400323_- Is there a way in MS Access 97 or DB2 Connect V7.1 to limit or filter the list of tables returned to Access from DB2 for OS390 V6.1 when linking to external data? We have several PeopleSoft subsystems (37K tables) that we cannot link to, I presume due to the number of tables being returned from the system catalog. [...] 4383 155 17_IBM buys Informix14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 24 Apr 2001 08:00:10 -0500725_iso-8859-1 IBM bought Informix - announced this morning.



Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Peter, Georg > Sent: Tuesday, April 24, 2001 7:39 AM > To: DB2-L@RYCI.COM > Subject: AW: Reserved Word (formerly blank) > > > Hi Rick, > > NOT using "normal" SQL reserved words and NOT using IBM SQL reserved words > is the best choice to reduce confusion and ambiguity. > > I think it is [...] 4539 17 22_Re: performance tuning10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 24 Apr 2001 15:10:14 +0200374_us-ascii To have an idea about performance forecast don't forget DB2 XTIMATOR, it's useful and FREE....

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 4557 43 42_Re: Another DB2 Connect/MS Access Question15_Stacey L Waters23_WATERSS1@NATIONWIDE.COM31_Tue, 24 Apr 2001 08:21:03 -0500737_us-ascii Checkout the SCHEMALIST keyword that you can use in the db2cli.ini.









Tim.T.Dupree@LOWES.COM@RYCI.COM> on 04/24/2001 07:55:08 AM

From: Tim.T.Dupree@LOWES.COM@RYCI.COM on 04/24/2001 07:55 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Another DB2 Connect/MS Access Question

Is there a way in MS Access 97 or DB2 Connect V7.1 to limit or filter the list of tables returned to Access from DB2 for OS390 V6.1 when linking to external data? We have several PeopleSoft subsystems (37K tables) that we cannot link to, I presume due to the number of [...] 4601 31 63_DB2 V5, OS/390 - Strobe reports High Wait time in DSNVSR module11_Vijay Kumar26_vijay_kumar_33@HOTMAIL.COM31_Tue, 24 Apr 2001 13:13:06 -0000595_- Greetings,

We have a long running batch process using a C program accessing DB2 tables in partioned tablespaces. The program reads about 100,000 records in about 185 cpu secs with an elapsed time of about 2hrs. The program is bound using DEGREE(ANY) and the explain shows CPU parallelism for most of the queries (all of the SELECT SQLs only). So, I disabled parallelism by rebinding with DEGREE(1). This time the job retrieved 153,000 records using 227 CPU secs with an elapsed time of 2hr and 15 mins. Both the jobs were monitored using STROBE, and the strobe output shows DSNVSR [...] 4633 18 21_Re: IBM buys Informix13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 24 Apr 2001 09:24:20 -0400441_iso-8859-1 >IBM bought Informix - announced this morning.

For a billion $$ in cash -- American!

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.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. 4652 66 42_Re: Another DB2 Connect/MS Access Question9_Colin Fay13_cfay2@CSC.COM31_Tue, 24 Apr 2001 09:30:51 -0400480_us-ascii Hi,

Assuming you are on NT client machines.





Go into Setting>Control Panel>Data Sources

Tab to FILE DSN and pick out your data source and Configure Button

take Advanced Options and the Enterprise tab

Modify the SCHEMALIST which is a selection by tble Creator.

You can also filter by TableType for example Alias

The combination of these filters may help although you may have to set up aliases or views [...] 4719 29 22_Re: performance tuning15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT31_Tue, 24 Apr 2001 15:37:56 +0200540_iso-8859-1 I don't think to be able to suggest you somethings about DB2. But i would like to say you that we had, and we are having yet, some advantages by using the relatevly new compiler

COBOL FOR OS/390 & VM 2.2.0 (September 2000) because it introduced better performance in the the area of the TRUNC parameter. Only recompiling programs (that were not perfectly written respect to performance) we noticed that in several cases the use of CPU of executions is less than before. (My english is bad; I hope to be enaugh clear) [...] 4749 53 21_Re: IBM buys Informix13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Tue, 24 Apr 2001 14:41:20 +0100639_iso-8859-1 But why???

-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: 24 April 2001 14:24 To: DB2-L@RYCI.COM Subject: Re: IBM buys Informix



>IBM bought Informix - announced this morning.

For a billion $$ in cash -- American!

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.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. [...] 4803 38 37_Re: problem calling stored procedures14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 24 Apr 2001 09:48:16 -0400411_iso-8859-1 As of now (upto V7), DB2 does not support nested calls for Java stored procedures, though it supports multiple nested calls for SQL stored procs. I believe support for nested Java stored procs will come from V8. IBM DB2 people, please correct me if I am wrong here.

However, you can call a static method in a Java stored proc class from a static method in another Java stored proc class. [...] 4842 54 21_AW: IBM buys Informix12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 24 Apr 2001 15:52:01 +0200929_iso-8859-1 Dave,

if you are interested to know more about this deal take a look at http://www.informix.com/informix/blue/om/informix/blue/.

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Linux is like a wigwam: no gates, no windows, and an apache inside..... -----Ursprüngliche Nachricht----- Von: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Gesendet am: Dienstag, 24. April 2001 15:24 An: DB2-L@RYCI.COM Betreff: Re: IBM buys Informix [...] 4897 77 21_Re: IBM buys Informix16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 24 Apr 2001 10:04:57 -0400501_iso-8859-1 Perhaps to remove Informix from the marketplace? Roll Informix function into UDB then offer all Informix licencees free UDB then pull the plug on Informix? Maybe that will work then IBM can do the same (please! PLEASE!) to SYBASE(yucch!)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Adrian Savory [mailto:Adrian.Savory@WORKTHING.COM] Sent: Tuesday, April 24, 2001 9:41 AM To: DB2-L@RYCI.COM Subject: Re: IBM buys Informix [...] 4975 74 21_Re: IBM buys Informix16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 24 Apr 2001 19:41:51 +0530654_- I read the article from Informix site. Answer to "WHY ??" lies in Business i suppose. Informix too have large customer base and IBM definitely wants to gather the first rank in "Largest Database marker share".

My opinion !! May be wrong !!

Regards Sanjeev

> -----Original Message----- > From: Adrian Savory [SMTP:Adrian.Savory@WORKTHING.COM] > Sent: Tuesday, April 24, 2001 7:11 PM > To: DB2-L@RYCI.COM > Subject: Re: IBM buys Informix > > But why??? > > -----Original Message----- > From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] > Sent: 24 April 2001 14:24 > To: DB2-L@RYCI.COM > Subject: Re: IBM buys Informix > > [...] 5050 60 68_Re: DB2 V5, OS/390 - Strobe reports High Wait time in DSN VSR module0_19_mike.holmans@BT.COM31_Tue, 24 Apr 2001 15:02:51 +0100348_- Sounds like you're doing a lot of synchronous I/O. Do your tablespaces need reorging? Are you using a partitioning key consisting of "partition number" and nothing else to help DB2 with placing the rows, so that reorg doesn't really get things into any useful physical order?

Mike Holmans BT ISE Database Services mike.holmans@bt.com [...] 5111 95 21_AW: IBM buys Informix12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 24 Apr 2001 16:10:29 +0200634_iso-8859-1 Adrian, it seems that IBM intends to integrate selected Informix technology into future versions of DB2* Universal Database (which will hopefully remain IBM's flagship database product.....).

With kind regards - mit freundlichen Grüssen, Georg H. Peter ---------------------------------------------------------------------- Linux is like a wigwam: no gates, no windows, and an apache inside.....







-----Ursprüngliche Nachricht----- Von: Adrian Savory [mailto:Adrian.Savory@WORKTHING.COM] Gesendet am: Dienstag, 24. April 2001 15:41 An: DB2-L@RYCI.COM Betreff: Re: IBM buys Informix [...] 5207 150 22_Re: performance tuning0_19_mike.holmans@BT.COM31_Tue, 24 Apr 2001 15:12:07 +0100407_iso-8859-1 You could write whole books on the subject.

As a task for someone new to the field, it's pretty daunting, because there are so many things which you could look at.

There's a basic health check which is worth starting with.

Are the catalog statistics up to date, and is RUNSTATS being run regularly, and are packages/plans being rebound to take account of updated stats? [...] 5358 57 42_Re: Another DB2 Connect/MS Access Question0_22_Tim.T.Dupree@LOWES.COM31_Tue, 24 Apr 2001 10:21:11 -0400635_- Thank you! Exactly what I was looking for. I just leave out the PSoft creator ids and it works great.

> -----Original Message----- > From: Stacey L Waters [SMTP:WATERSS1@NATIONWIDE.COM] > Sent: Tuesday, April 24, 2001 9:21 AM > To: DB2-L@RYCI.COM > Subject: Re: Another DB2 Connect/MS Access Question > > Checkout the SCHEMALIST keyword that you can use in the db2cli.ini. > > > > > > Tim.T.Dupree@LOWES.COM@RYCI.COM> on 04/24/2001 07:55:08 AM > > From: Tim.T.Dupree@LOWES.COM@RYCI.COM on 04/24/2001 07:55 AM > > Please respond to DB2 Data Base Discussion List > > Sent by: DB2 Data Base Discussion List [...] 5416 83 42_Re: Another DB2 Connect/MS Access Question0_22_Tim.T.Dupree@LOWES.COM31_Tue, 24 Apr 2001 10:26:39 -0400576_- Thanks for the reply! I tested without the PSoft creator ids and it works great. Exactly what I was looking for.

Tim

> -----Original Message----- > From: Colin Fay [SMTP:cfay2@CSC.COM] > Sent: Tuesday, April 24, 2001 9:31 AM > To: DB2-L@RYCI.COM > Subject: Re: Another DB2 Connect/MS Access Question > > Hi, > > Assuming you are on NT client machines. > > > > Go into Setting>Control Panel>Data Sources > > Tab to FILE DSN and pick out your data source and Configure Button > > take Advanced Options and the Enterprise tab > > Modify the SCHEMALIST which [...] 5500 107 42_Re: Another DB2 Connect/MS Access Question12_Le BD Nguyen20_lnguyen@AHOLDUSA.COM31_Tue, 24 Apr 2001 10:33:18 -0400467_us-ascii I think Collin meant select the "SYSTEM DSN", not the "FILE DSN" tab.

The other thing you can do is to edit the DB2CLI.INI and add line SCHEMALIST="'Qualifier_name1', 'Qualifier_name2','....'" under the system data source name.

I hope this help.

Le.





Colin Fay cc: Sent by: DB2 Subject: Re: Another DB2 Connect/MS Access Question Data Base Discussion List [...] 5608 25 26_Cost of additional indexes0_39_Patrick_D_Poziwilko@EMAIL.WHIRLPOOL.COM31_Tue, 24 Apr 2001 10:46:52 -0400625_US-ASCII We have a number of developers that want indexes to fix there poor performing programs. Yes, one can write a poorly performing program in SAP's ABAP language. In an effort to educate the developers, we the DBAs, would like to provide performance cost of providing an index to fix specific program performance issue. Is there a good rule of thumb for estimating a second through n'th non-unique index? Most important would be the effect on performance and the locking associated with inserts, deletes, and updates? The rule-of-thumb value or percentage will be used to determine the cost effectiveness of adding [...] 5634 118 21_Re: IBM buys Informix14_Richard Yevich17_ryevich@YAHOO.COM31_Tue, 24 Apr 2001 07:59:21 -0700583_us-ascii 1 - Integrate Datablades with current extenders 2 - Integrate analytical functions from Redbrick into DB2 3 - maintain/enhance current Informix datbases 4 - new databases DB2 5 - Kick Oracle's butt in percentge of market

--- "S, Sanjeev (CTS)" wrote: > I read the article from Informix site. Answer to "WHY ??" lies in > Business i > suppose. Informix too have large customer base and IBM definitely > wants to > gather the first rank in "Largest Database marker share". > > My opinion !! May be wrong !! > > Regards > Sanjeev > > > [...] 5753 83 48_SQLJs db2profc UDB to DB2 OS/390 via DB2 CONNECT0_22_scott.goodell@WCOM.COM31_Tue, 24 Apr 2001 09:56:30 -0500553_us-ascii We are attempting to run the db2profc command on UDB V7 against a DB2 OS/390 V6 system. I don't think the version or UDB or DB2 makes any difference because we also had this problem with UDB V6 and DB2 OS/390 V5.

We code the SQLJ program without qualified table names so at bind time we can specify the QUALIFIER parameter to point to the correct tables. The OWNER keyword value is used for authority. The OWNER is a valid secondary authid where as the QUALIFIER is NOT. The bind works fine but we then receive an error that causes [...] 5837 220 22_Re: performance tuning0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 24 Apr 2001 10:03:30 -0500337_iso-8859-1 As other people have suggested, start from the outside and work your way in. Identify the longest running jobs and start with them, they provide the opportunity to have the most visible results. Then monitor these jobs and find out where they are using the most time, then identify what needs to be done to improve them. [...] 6058 57 26_Undocumented error message12_David Booher22_David.Booher@QUEST.COM31_Tue, 24 Apr 2001 08:16:15 -0700204_iso-8859-1 Has anyone on the list encountered this error? I can't find it in the code manuals.

DSNP016I ) DSNPCPS0 - CREATE FAILED FOR 091 GMERCER.LOB03.I0001. RC=00D70008

Thanks Dave Booher 6116 52 30_Re: Cost of additional indexes12_Le BD Nguyen20_lnguyen@AHOLDUSA.COM31_Tue, 24 Apr 2001 11:16:00 -0400747_us-ascii I think you should look at redbook SG24-2549 (QUBE - Quick Upperbound Estimate). I hope this will help.

Le.





Patrick_D_Poziwilko@EMAIL.WHI RLPOOL.COM To: DB2-L@RYCI.COM Sent by: DB2 Data Base cc: Discussion List Subject: Cost of additional indexes



04/24/2001 10:46 AM Please respond to DB2 Data Base Discussion List











We have a number of developers that want indexes to fix there poor performing programs. Yes, one can write a poorly performing program in SAP's ABAP language. In an effort to educate the developers, we the DBAs, would like to provide performance cost of providing an index to fix specific program performance [...] 6169 66 24_DB2 Complex SQL Question13_Keerthi Chegu18_lpbusobj@YAHOO.COM31_Tue, 24 Apr 2001 10:00:14 -0500332_ISO-8859-1 Hi All:

I am trying to create a report in Business Objects against DB2. Following is the report defintion:

I would like to know if there is a way to do this in plain SQL since I can't create procedureal code against DB2.

2. Q42000 ( bound_indicator = I)

Type A Countries

Ind = ‘Y’ [...] 6236 180 22_Re: performance tuning15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Tue, 24 Apr 2001 10:28:37 -0500369_iso-8859-1 Going back even further, don't even start to tune your apps until you are sure that everything above (or below) them is tuned.

Classic order is:

MVS (paging, IO balancing, CF structures etc) VTAM DB2 Subsystem (despatching priorities of address spaces, zparms, pools etc) Objects Apps (SQL)

Andy Lankester My opinions are just that [...] 6417 124 30_Re: Undocumented error message14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 24 Apr 2001 16:30:34 +0100452_iso-8859-1 DSNP016I CREATE FAILED FOR pageset-name. RC=rrrrrrrr CONNECTION-ID=xxxxxxxx, CORRELATION-ID=yyyyyyyyyyyy, LUW-ID=logical-unit-of-work-id =token

Explanation: Creation of a table space or index space failed in the data space manager (DSM) for the reason given in RC. (See "Section 4. DB2 Codes" ) The pageset-name format is 'dbname.tiname', where 'dbname' is the database name, and 'tiname' is the table space or index space name. [...] 6542 13 22_Re: performance tuning0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 24 Apr 2001 10:31:33 -0500362_us-ascii Also check the IBM Redbook (and Red Pieces), there is bound to be lots there about DB2 tuning, ibm.com/redbooks

================================================ 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. 6556 210 30_Re: Undocumented error message9_Cam White20_cwhite@REVEALNET.COM31_Tue, 24 Apr 2001 11:43:59 -0400375_iso-8859-1 Here's the description listed in Instant Messages for DB2. The new Spring, 2001 version of the product was released yesterday. Instant Messages is part of the Knowledge Base for DB2. Download a 30-day trial from www.revealnet.com

IBM DB2 Document Library (C) IBM Corporation

Manual: DB2 Universal Database for OS/390 IB6 Reference: GC26-9011-00 [...] 6767 114 30_Re: Undocumented error message12_Mark Granger22_the_grange@MBOX.COM.AU31_Tue, 24 Apr 2001 10:38:21 -0500638_us-ascii Dave, I'm looking at DB2 UDB for OS/390 and z/OS V7 ( http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves/DSNSHHA0 ) in the Messages and Codes and it has (for 00D70008) - | 00D70008 | Explanation: During allocation, DB2 detects that the attributes for the | data set were incorrect. This data set requires the extended | addressability attribute defined through DFSMS. | System Action: Error indication is returned to the end user and a dump is | not taken. | User Response: Notify the system programmer or storage administrator. | System Programmer Response: To be larger than 4 GB, this data set must | belong to [...] 6882 65 30_Re: Undocumented error message18_Michael J. O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV31_Tue, 24 Apr 2001 11:33:30 -0500531_US-ASCII From UDB for OS/390 V6.1 Messages & Codes: (I did a search on DSNPCPS0).

4.13.4 00D70004

-------------------------------------------------------------------------- 00D70004

Explanation: Certain data set(s) are pending deletion. Creation of a new data set is blocked by the presence of the name on a drop list. This is usually a user error. It usually occurs when the user has dropped an old object (table space, index, and so forth) and then attempted to redefine it without committing the drop. [...] 6948 71 30_Re: Undocumented error message13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 24 Apr 2001 10:42:36 -0500967_iso-8859-1 Have a look on the console/SDSF log for other messages from media services etc. I use the IBM site for the latest on manuals. They seem more current than my hard copies.

http://www-4.ibm.com/software/data/db2/os390/v6books.html



http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnmc0g2/4.13.8?A CTION=MATCHES &REQUEST=00D70008&TYPE=FUZZY&searchTopic=TOPIC&searchText=TEXT&searchIndex=I NDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT







# Explanation: During allocation, DB2 detects that the attributes for the # data set were incorrect. This data set requires the extended # addressability attribute defined through DFSMS. [...] 7020 98 30_Re: Undocumented error message15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 24 Apr 2001 10:43:10 -0500317_iso-8859-1 Hi, Dave, I don't know what it is either but you might try looking in the system log or the DB2 master JES log to see if there are any other error messages that can give you a clue.

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services [...] 7119 105 21_Re: IBM buys Informix19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 24 Apr 2001 10:47:14 -0500623_iso-8859-1 The news we really wanna here is that IBM buys CA!

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 7225 126 21_Re: IBM buys Informix16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 24 Apr 2001 12:11:24 -0400678_iso-8859-1 AMEN. I would rather deal with MicroSloth than with CA!

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:rd8246@SBC.COM] Sent: Tuesday, April 24, 2001 11:47 AM To: DB2-L@RYCI.COM Subject: Re: IBM buys Informix



The news we really wanna here is that IBM buys CA!

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe [...] 7352 23 38_regarding File-AID Related Data eXpert17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 24 Apr 2001 11:03:25 -0500433_- Hi Folks,

We are using DB2 V6 on os390. recently in our shope they istalled FILE-AID related Data eXpert, is any one using this???. i want to know more info on this??, what is use of this and when we will use this???. can someone help in this.

Thanks Kumar

_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. [...] 7376 21 15_Re: DB2 and WLM17_Panicker, Lethika26_LPanicker@FSSA.STATE.IN.US31_Tue, 24 Apr 2001 11:13:10 -0500287_iso-8859-1 Hi,

We are using OS390/V6 . First we created some stored procedures with out WLM . It is working fine, the same proc we created with WLM and it is not working . What are the additional things I needs to take care while creating and start/stop of a proc with WLM . [...] 7398 202 22_Re: performance tuning0_18_mebert@AMADEUS.NET31_Tue, 24 Apr 2001 18:23:26 +0200579_us-ascii I have to disagree strongly. In my experience & opinion, tuning the application SQL (or the general processing pattern on a more abstract level) is the most efficient way by far. Just last week, I improved a batch job from 30 mins to 20 secs CPU just by adding one column to a WHERE clause. Tiny blunders like that are rather common and can have enormous effects. Low-level tuning (listed as "MVS" below) is useful for squeezing the last millisecond out of an OLTP response time, but it is the last step (assuming the basic MVS parameters aren't completely screwed [...] 7601 123 21_Re: IBM buys Informix17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Tue, 24 Apr 2001 09:22:16 -0700444_US-ASCII Hi gang,

We think its a good deal IBM can use a bigger byte of the market!!!!!..... At least its good for Big Blue/Black shareholders.



______________________________ Reply Separator _________________________________ Subject: Re: IBM buys Informix Author: DB2 Data Base Discussion List at INTERNET-MAIL Date: 4/24/01 10:47 AM



The news we really wanna here is that IBM buys CA! [...] 7725 232 22_Re: performance tuning13_de Jonge, Jan20_Jan_De_Jonge@BMC.COM31_Tue, 24 Apr 2001 11:39:36 -0500554_iso-8859-1 Michael,

when it comes to effectiveness I totally agree with you. But when it comes to reality we all know that taking the classic order as Andy describes is the most real thing. It is easier to change systems parameters when it comes to OS/390, VTAM or DB2 then tuning and changing thousands of SQL embedded programs, even when you have tools like DB2 Estimator, Apptune or SQL Explorer. This takes a lot of time, a lot of people and a lot of budget which a lot of organisations don't seem to have. So you start in the area where [...] 7958 22 25_Re: AW: IBM buys Informix12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 24 Apr 2001 11:31:36 -0500469_- There is more out on the web, for instance ... http://www.ibm.com/news/2001/04/24.phtml

Here is a paste. "Informix customers also will benefit from IBM's open platform strategy and alliances with leading application developers such as SAP, Siebel Systems, PeopleSoft, Retek and many others worldwide. IBM intends to integrate selected Informix technology into future versions of DB2 Universal Database, which will remain IBM's flagship database product." [...] 7981 26 12_DB2OCAT Woes16_Maculsay, Gary R28_Maculsay.Gary@EMERYWORLD.COM31_Tue, 24 Apr 2001 16:53:38 -0000449_iso-8859-1 I had heard that this tool had a spotty history, but felt it deserved a fair shake.

Using DB2 Connect 7.1, and DB2OCAT v1.3.1, I specified the data source name that refers to a DB2/OS390 subsystem. Moving on to the next panel, it says to wait while it retrieves data. I see that it had issued dynamic sequel to suck down table information (2600 rows worth), and that's the last I've heard from the tool for over twelve hours. [...] 8008 43 15_Re: DB2 and WLM12_Clark, Kevin15_KClark@FDIC.GOV31_Tue, 24 Apr 2001 12:56:26 -0400465_- 1) Check out the WLM site ..lots of related links and paper http://www.s390.ibm.com/wlm/ 2) MVS planning: WORKLOAD MANAGEMENT AND (GC28-1761-10) FOR 2.8 3) Red book : OS/390 WORKLOAD MANAGER IMPLEMENTATION AND EXPLOITATION sg24-5326-00





-----Original Message----- From: Panicker, Lethika [SMTP:LPanicker@FSSA.STATE.IN.US] Sent: Tuesday, April 24, 2001 12:13 PM To: DB2-L@RYCI.COM Subject: Re: DB2 and WLM [...] 8052 62 42_Re: regarding File-AID Related Data eXpert16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Tue, 24 Apr 2001 13:25:11 -0400523_iso-8859-1 You can obtain information by either emailing myself with questions, or by going to www.compuware.com, or by contacting our hot line at 1-800-538,7833 (within North America), or your local Compuware office.



But in a nut shell, File-Aid RDX is a RELATED extractor/loader. Giving the ability to extract related information (within DB2 and/or MVS datasets (also IMS)), using selection criteria, and restoring the extracted data onto a target. It can also delete those records that you backup up [...] 8115 59 30_Re: Cost of additional indexes12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Tue, 24 Apr 2001 12:30:05 -0500360_us-ascii Patrick,

Be sure the developers are aware of OSS notes 185530, 187906 and 191492.

These notes give explicit examples of how to access certain tables WITHOUT adding indexes: VBAK, VBAP, VMVA, LIKP, LIPS, VBRK, VBRP, VBFA AFRU, AFKO, AUFK, CAUFV, RESB MSEG, LTAP, EBAN

I keep these notes handy and use them in my code reviews. [...] 8175 26 25_DB2 REORG/PURGE WAIT TIME16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Tue, 24 Apr 2001 10:37:14 -0700589_us-ascii We have created a process of performing a purge of a partitioned structure (32K) using the IBM DB2 REORG utility (DB2 V5), and are seeing a lot of slowdown during the discarding of rows being written to SYSDISC. A STROBE report indicates that the wait time within the utility execution (84%) is on SYSDISC which is being written to tape, and we have seen similar results writing to DASD. Has anyone had similar experiences, and if so, been able to apply any sort of performance technique to reduce this wait time? The DCB information for SYSDISC indicates an LRECL=32722 and [...] 8202 22 38_Re: UDB sysadm without NT Admin rights11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Tue, 24 Apr 2001 18:43:43 +0100479_us-ascii If your user ID and groups are granted on the domain I don't think it makes any difference.



SPUFI Doo wrote:

> Thanks, Jeremy, but what if no local users/groups are allowed, only server > groups/users? > > ================================================ > 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. [...] 8225 120 24_Re: UDB Quick Reference?15_Donna O'Connell26_donna.oconnell@SAFEWAY.COM31_Tue, 24 Apr 2001 10:50:13 -0700519_iso-8859-1 How can we obtain this product Is it on the IBM Web site? Is it on Bookmanager for DB2? Thank you.

Ruediger Kurtz wrote:

> Hi everyone, > > there's also "DB2 UDB for OS/390 Developer's quick reference guide' by > Gary Joehlin in the MVS Training INC. Mainframe Series. > > Susan Lawson schrieb: > > > > Hi all, > > > > YL&A has written a Version 7 quick reference for both OS/390 and UDB > > (non-OS/390). It is about 400 pages and is being printed and distributed by > > BMC at IDUG. > > [...] 8346 24 23_SOLARIS FIXPACK BACKOUT14_Timothy Miller17_njironman@USA.NET29_Tue, 24 Apr 2001 13:56:08 EDT665_US-ASCII HELLO,

How can I back out fixpack packages running on a solaris machine, then installallpatch failed, which will not allow me to use the backallpatch script. Any sugguestions?



Tim Please email me at njironman@usa.net, I receive a digest form email

____________________________________________________________________ Get free email and a permanent address at http://www.amexmail.com/?A=1

===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8371 202 24_Re: UDB Quick Reference?13_Zessin, Peter25_Peter.Zessin@ATCOITEK.COM31_Tue, 24 Apr 2001 12:18:31 -0600648_iso-8859-1 Donna, try this link : http://www.mvs-training.com/bookstore.htm

-----Original Message----- From: Donna O'Connell [SMTP:donna.oconnell@SAFEWAY.COM] Sent: 24 April, 2001 11:50 AM To: DB2-L@RYCI.COM Subject: Re: UDB Quick Reference?

How can we obtain this product Is it on the IBM Web site? Is it on Bookmanager for DB2? Thank you.

Ruediger Kurtz wrote:

> Hi everyone, > > there's also "DB2 UDB for OS/390 Developer's quick reference guide' by > Gary Joehlin in the MVS Training INC. Mainframe Series. > > Susan Lawson schrieb: > > > > Hi all, > > > > YL&A has written a Version 7 quick reference for both [...] 8574 64 22_Re: performance tuning5_chris22_hhardy1@HOUSTON.RR.COM31_Tue, 24 Apr 2001 13:33:08 -0500440_iso-8859-1 SQL statement tuning must happen in the "sand box" under conditions dictionary or catalog reflective of real environment numerous variations away from ISO, CMM or corporate (service level) policy will cause degradation to have an adverse impact on users community, CPU/MHZ high, I/O count high, wait time high, memory constraint, network impact, response shot to hell, user calling the help desk,,, this sounds familiar.... [...] 8639 99 22_FW: performance tuning19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 24 Apr 2001 13:31:41 -0500551_iso-8859-1 Jan, I hope we can agree to disagree! I strongly agree with Dr. Ebert, which implies I strongly disagree with your statement on this matter. I'm a DBA and been around DB2 since V1.0.0. Yes, there was a 1.0.0 and the install guide was a printout from an open reel, tape file! I was the MVS sysprog for the site when I installed DB2 V1.0.0. Its been my experience that most performance issues related to DB2 revolve around SQL or I/O subsystems. These are the two areas where you can get the biggest bang for your tuning bucks! And yes, [...] 8739 228 24_Re: UDB Quick Reference?14_Richard Yevich17_ryevich@YAHOO.COM31_Tue, 24 Apr 2001 11:48:54 -0700607_us-ascii That's OS/390 only, and not V7.

--- "Zessin, Peter" wrote: > Donna, try this link : http://www.mvs-training.com/bookstore.htm > > -----Original Message----- > From: Donna O'Connell [SMTP:donna.oconnell@SAFEWAY.COM] > Sent: 24 April, 2001 11:50 AM > To: DB2-L@RYCI.COM > Subject: Re: UDB Quick Reference? > > How can we obtain this product > Is it on the IBM Web site? > Is it on Bookmanager for DB2? > Thank you. > > Ruediger Kurtz wrote: > > > Hi everyone, > > > > there's also "DB2 UDB for OS/390 Developer's quick > reference > guide' by > > Gary Joehlin [...] 8968 96 29_Re: DB2 Connect and MS Access17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Tue, 24 Apr 2001 12:06:17 -0700414_us-ascii Is it safe to assume that MSAccess will read the entire table first and then display the number of rows you specified in the maxrows parameter?









leon@CA.IBM.C OM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: DB2 Connect and MS Access Discussion List



04/19/01 10:16 PM Please respond to DB2 Data Base Discussion List [...] 9065 72 30_Re: Cost of additional indexes0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 24 Apr 2001 15:15:56 -0400618_us-ascii Well, the rule of thumb is how often does the table needing the additional index have selects, deletes, inserts, and updates done?

Being an application programmer I've had to argue for additional indexes simply because the information supplied to the program, method, panel, etc. dictated the use of additional indexes.

If you have a table that only has a unique index on social security number, and the project specifications require that the user can do searches on any part of a name or any other unique index dependent column, then querying the table for someone with the first name of [...] 9138 92 28_REXX DB2 ABEND 878 Reason 1612_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 24 Apr 2001 15:16:24 -0400482_US-ASCII Hello List,

I've written a simple Rexx routine to read an input file and parse the input for a database, tablespace, and finally table name. I then build an SQLSTMT and open a cursor to bring back a row and finally close the cursor and write an output file before I go on to the next input record. This worked fine for several thousand lines of input. Now I am processing the full input file of around 40,000 lines. This will make about 1200 open cursor calls. [...] 9231 16 21_Re: IBM buys Informix26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Tue, 24 Apr 2001 14:21:59 -0500439_- Wonder what this portends? Well, they paid $3 billion for Lotus and we've survived that...sometimes you just has to wonder. Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9248 161 26_Re: FW: performance tuning12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 24 Apr 2001 12:25:28 -0700538_us-ascii I agree with Rick and Dr. Ebert. But it depends upon your objectives and your system. If your objective is high level quick major impact fixes, (old 80% rule still applies), then I must agree with Rick and Dr. Ebert. You can easily get the quickest and biggest bang here. On the other hand, if your SQL has been coded pretty well all along and you have a high volume system with tight response times from CICS/TM or IMS/TM, then you must consider all the other factors as well. You can have the best SQL in the world and if [...] 9410 49 32_Re: REXX DB2 ABEND 878 Reason 1614_Toppins, Smike21_smike.toppins@GWL.COM31_Tue, 24 Apr 2001 13:29:42 -0600603_- There's an APAR or two available to correct this.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Troy Coleman[SMTP:Colematr@MEIJER.COM] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, April 24, 2001 1:16 PM > To: DB2-L@RYCI.COM > Subject: REXX DB2 ABEND 878 Reason 16 > > Hello List, > > I've written a simple Rexx routine to read an input file and parse the > input for a database, tablespace, and finally table name. I then build an > SQLSTMT and open a cursor to bring back a row and finally close the cursor > [...] 9460 95 32_Re: REXX DB2 ABEND 878 Reason 1616_Sreedhar Bobbadi24_sbobbadi@DUKE-ENERGY.COM31_Tue, 24 Apr 2001 15:37:56 -0400704_iso-8859-1 Try increasing the region size......

HTH Sreedhar Bobbadi







Troy Coleman cc: Sent by: DB2 bcc: Data Base Subject: REXX DB2 ABEND 878 Reason 16 Discussion List



04/24/01 03:16 PM Please respond to DB2 Data Base Discussion List













Hello List,

I've written a simple Rexx routine to read an input file and parse the input for a database, tablespace, and finally table name. I then build an SQLSTMT and open a cursor to bring back a row and finally close the cursor and write an output file before I go on to the next input [...] 9556 78 32_Re: REXX DB2 ABEND 878 Reason 1613_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 24 Apr 2001 15:50:38 -0400345_iso-8859-1 Hi Troy,

This is a very common abend in Rexx, especially when you're tying up lots of memory with volumes of variables.

I suspect you're using Execio and storing the entire 40k records in the stem. The solution is to break up the input into chunks, process them and free the storage tied up in your stem variables. [...] 9635 130 32_Re: REXX DB2 ABEND 878 Reason 1612_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 24 Apr 2001 15:53:34 -0400639_US-ASCII Do you know what APAR is needed?

>>> smike.toppins@GWL.COM 04/24/01 03:29PM >>> There's an APAR or two available to correct this.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Troy Coleman[SMTP:Colematr@MEIJER.COM] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, April 24, 2001 1:16 PM > To: DB2-L@RYCI.COM > Subject: REXX DB2 ABEND 878 Reason 16 > > Hello List, > > I've written a simple Rexx routine to read an input file and parse the > input for a database, tablespace, and finally table name. I then build an > SQLSTMT and open a [...] 9766 47 47_IDUG Solutions Journal - Share your experiences12_David Beulke22_dbeulke@COMPUSERVE.COM31_Tue, 24 Apr 2001 15:56:02 -0400581_ISO-8859-1 All,

Share and detail your DB2 experience with an article for the IDUG Solutions Journal.

Your experience is valuable to the industry and the IDUG Solutions Journal is the place to highlight your design, programming or SQL that helped you solve the latest business or technical issues.

All you have to do is write an article for the next issue of the IDUG Solutions Journal. We are beginning to put together the next issue with Application Development and SQL as the themes and need your article detailing your experiences, issues and success. [...] 9814 21 21_Re: IBM buys Informix13_Morrill, John12_JohnM@VP.NET31_Tue, 24 Apr 2001 14:00:55 -0600557_- Greetings Friends in Califoria!

If you feel the earth shake today, don't worry. It's not an earth quake, it just Larry Ellison's reaction to the IBM-Informix deal. The man needs to spend a lot less time picking fights with Bill and a lot more time paying attention to his market.

Cheers!

John

================================================ 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. 9836 94 28_Re: DB2 Complex SQL Question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 24 Apr 2001 14:59:50 -0500454_iso-8859-1 Keerthi,

I would say yes.......but unfortunately I know nothing about Business Objects so this report definition does not mean anything to me.

Sounds like all you need is some clever CASE usage (especially required to perform table pivoting to get rate code 'C' and 'K' in the same expression).

Do you have anything more SQL-like (or even procedural logic) to allow anyone on DB2-L to attempt a SQL solution for you? [...] 9931 81 32_Re: REXX DB2 ABEND 878 Reason 1614_Toppins, Smike21_smike.toppins@GWL.COM31_Tue, 24 Apr 2001 14:04:55 -0600657_- I believe they are: PQ39176 and PQ41203.





SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Troy Coleman[SMTP:Colematr@MEIJER.COM] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, April 24, 2001 1:53 PM > To: DB2-L@RYCI.COM > Subject: Re: REXX DB2 ABEND 878 Reason 16 > > Do you know what APAR is needed? > > >>> smike.toppins@GWL.COM 04/24/01 03:29PM >>> > There's an APAR or two available to correct this. > > > SMike Toppins > Great-West Life > smike.toppins@gwl.com > (303) 737-5094 > > > > ---------- > > From: Troy Coleman[SMTP:Colematr@MEIJER.COM] > > Reply [...] 10013 22 31_SPECIAL REGISTER (Current Date)12_Raman Bharde21_rbharde@CMSENERGY.COM31_Tue, 24 Apr 2001 15:56:09 -0400439_us-ascii Normally, when an application uses this Register to get the Current date, it returns a date based on the time-of-day clock of the processor for the server executing the SQL statement and the MVS TIMEZONE parameter in SYS1.PARMLIB(CLOCKXX).

I want to find out ways for a COBOL-DB2 batch application to receive a Value other than it would normally get when it tries to access the CURRENT DATE register. Any suggestions? [...] 10036 64 24_Stogroup volume spanning16_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Tue, 24 Apr 2001 16:46:12 -0400376_iso-8859-1 Help..... I'm sure this has been covered before, but....... When creating a stogroup containing several volumes it appears as though the definition of the tablespace will fail if it cannot be allocated on a single volume. Is this normal? Must the tablespaces be SMS managed to span volumes? The environment is DB2 version 5 until OS390 2.7. Thanks in advance. [...] 10101 19 28_Bufferpool Tool FMID H00G61016_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 24 Apr 2001 23:03:22 +0200476_- Hi,

we are trying the Bufferpool Tool comming with DB2 V6 FMID H00G610. Unfortunally this tools didn't handle BP8K and BP16K.

Does this tool still exists in V7 because I can't find a FMID H00G710?

Regards Roland

================================================ 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. 10121 142 28_Re: Stogroup volume spanning16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 24 Apr 2001 17:13:15 -0400504_iso-8859-1 A tablespace consists of a VSAM linear dataset in one-to-n extents. Each (logical) extent can consist of 1-5 physical extents which must be on the samve volume. Your PRIQTY is a logical extent. It must be allocated on a volume which can supply that much space in 5 or fewer physical extents. The secondary (logical) extents can go on other volumes. These secondary (logical) extents follow the same rule - each logical extent must be satisfied in 1-5 physical extents on the same volume. [...] 10264 112 43_Large VSAM extended datasets/ SMS dataclass12_David Booher22_David.Booher@QUEST.COM31_Tue, 24 Apr 2001 14:16:32 -0700595_iso-8859-1 I am forwarding to this list also, in hopes that someone might have the answer to this problem.

Dave > -----Original Message----- > From: David Booher > Sent: Tuesday, April 24, 2001 2:35 PM > To: 'ibm-main@bama.ua.edu' > Subject: Large VSAM extended datasets/ SMS dataclass > > DB2 can create Large Objects in VSAM with sizes greater than 4Gb. This > requires that a DATACLASS be setup in SMS to enable the definition of an > EXTENDED ADDRESSIBILITY DATASET. > > When DB2 attempts to allocate one of these datasets, my DATACLAS ACS > routine is not setting the dataclass [...] 10377 35 35_Re: SPECIAL REGISTER (Current Date)0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 24 Apr 2001 16:32:15 -0500370_us-ascii What kind of value other than current date would you want from that register?

Or . . . do you want to see the date in a format other than what your system defaults to, ie yyyy-mm-dd vs dd/mm/yy ?









Raman Bharde @RYCI.COM> on 2001/04/24 02:56:09 PM Subject: SPECIAL REGISTER (Current Date) [...] 10413 73 30_Re: Cost of additional indexes12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Tue, 24 Apr 2001 16:44:42 -0500361_us-ascii Each added index means added cost: storage runstats reorgs.

Each added index means slower changes: 20 ms per row inserted 20 ms per row deleted 40 ms per row updates IF the update changes indexed column(s) 0 ms per row updated IF the update changes no indexed column.

Each added index would have NO EFFECT whatever for some queries. [...] 10487 16 27_Trigger to find the program15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 24 Apr 2001 17:57:21 -0400427_iso-8859-1 Can I use a trigger to find the program (Plan/Package) which update a field to null in a table. How do I get current package or plan in a trigger?



Thanks..sibi

================================================ 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. 10504 64 35_Re: SPECIAL REGISTER (Current Date)12_Raman Bharde21_rbharde@CMSENERGY.COM31_Tue, 24 Apr 2001 17:59:16 -0400448_us-ascii I want the REGISTER to have a date value that is different from today - i am trying to make the application pretend that it is running on a date that is different from today. For ex. If i run the application today, can i force the Register for CURRENT DATE to return a value, say '2000-01-20' instead of '2001-4-24' (which is the value CURRENT DATE will show normally), when any of the programs in the application uses this Register. [...] 10569 36 32_Re: Bufferpool Tool FMID H00G61014_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 24 Apr 2001 18:22:37 -0400357_ISO-8859-1 Roland, IBM re-markets a newer version of this software (V6.1.1) that was available as a maintenance release last October. It does support 8K & 16K pools, and also has newer PC graphic components.

You can obtain information about the V7 release of this software on the web at: www.responsivesystems.com, in the Announcements section. [...] 10606 89 35_Re: SPECIAL REGISTER (Current Date)12_Kirk Hampton16_khampto1@TXU.COM31_Tue, 24 Apr 2001 18:14:53 -0500301_us-ascii We use a software product called HourGlass that does exactly that... you can make DB2 (and CICS and TSO, etc.) return whatever date and time you like as the current date. It modifies one of the DB2 Loadlib members, so we only use it for testing. We originally bought it for Y2K testing. [...] 10696 107 35_Re: SPECIAL REGISTER (Current Date)0_19_Tim.Lowe@STPAUL.COM31_Tue, 24 Apr 2001 18:26:28 -0500402_us-ascii Raman, We used a product for Y2K testing, called Hourglass 2000 that did this. It used to be owned by a company called Mainware Incorporated, but it looks like it was bought by Princeton Softtech: http://www.princetonsoftech.com It was obviously designed for Y2K testing, and I am not sure that I would want it in a production environment, but I think that this does what you are asking. [...] 10804 93 35_Re: SPECIAL REGISTER (Current Date)16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Tue, 24 Apr 2001 20:17:48 -0400558_us-ascii Hi, this sounds like rerun logic. -It could be handled by appl program. ie Set a variable for current date rather than use the register. depending on i/p parm.(override) hth Tom F.

http://www.ibm.com/stretch/mindshare/ http://www.ibm.com/stretch/eos/index.phtml







Raman Bharde on 04/24/2001 05:59:16 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Re: SPECIAL REGISTER (Current Date) [...] 10898 17 35_Re: SPECIAL REGISTER (Current Date)26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Tue, 24 Apr 2001 19:40:08 -0500516_- Yeah that's sorta what I was thinking(for cheapo testing). Guess I'd just add(or subtract) a value returned by Special Register and make that the "shop standard". In production it must always be zero. Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10916 21 0_19_BALAJI Muthukishnan20_MBalaji@COVANSYS.COM31_Wed, 18 Apr 2001 05:49:18 -0400274_iso-8859-1 HI List, i want to know that if i am working on C/C++ having backend as Oracle/DB2 then how will i connect & access the backend using C. I have to include some system files or i have to do it using Pro*c? I am new to this area so i need your help in detail. [...] 10938 44 22_Re: performance tuning14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 24 Apr 2001 20:26:31 -0500414_iso-8859-1 Okay, I am going to strongly disagree with both Andy and Jan. We (YL&A) spend almost all of our time conducting performance audits and tuning systems for customers, and guess what we find -- almost without fail, it is the physical design and SQL together, especially the SQL.

Andy said: "don't even start to tune your apps until you are sure that everything above (or below) them is tuned." [...] 10983 121 30_Re: Cost of additional indexes16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 25 Apr 2001 11:01:04 +0530526_iso-8859-1 I think John has given the exact figures for the cost due to additional index. Still a good amount of Trade Off is to be done while adding the index. This trade off depends on the frequency of insert/update/load/reorg(especially for NPIs in partitions tablespace) and the frequency of select using the index on that table. The high cost of the application SELECTS, especially when it is online applications is never acceptable, even if the size of the BP is high, online applications can't do tablespace scan. [...] 11105 85 21_Re: IBM buys Informix23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 25 Apr 2001 06:29:42 +0100537_iso-8859-1 because they thought it was worth it.

Less comptetition ...

they had something they wanted ...

(see your still alive)

Les

-----Original Message----- From: Adrian Savory [mailto:Adrian.Savory@WORKTHING.COM] Sent: Tuesday, April 24, 2001 2:41 PM To: DB2-L@RYCI.COM Subject: Re: IBM buys Informix



But why???

-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: 24 April 2001 14:24 To: DB2-L@RYCI.COM Subject: Re: IBM buys Informix [...] 11191 63 38_Re: performance tuning/Cost of indexes10_john clark20_pak02449@PIXIE.CO.ZA31_Wed, 25 Apr 2001 08:10:56 +0530564_iso-8859-1 Given that it is early here in Africa, it was very nice to come in to work and see a lively discussion on Performance tuning and some notes on indexes.

While I think that some correspondents alluded to general applications tuning, I think that the discussion generally focussed on the two levels lower than that which I normally work on. Let me say however that while you can never assume that all the systems level tuning has been done and that the SQL is good, that our approach is to assume that this is so particularly on the OS/390 and [...] 11255 152 24_Re: UDB Quick Reference?14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 25 Apr 2001 08:28:01 +0200546_iso-8859-1 Donna,

a colleague of mine obtained it via bmc at this year's "DB2 and IMS Technical Conference in Hamburg", so I think it would be best you contact BMC.

Regards Ruediger Kurtz



Donna O'Connell schrieb: > > How can we obtain this product > Is it on the IBM Web site? > Is it on Bookmanager for DB2? > Thank you. > > Ruediger Kurtz wrote: > > > Hi everyone, > > > > there's also "DB2 UDB for OS/390 Developer's quick reference guide' by > > Gary Joehlin in the MVS Training INC. Mainframe Series. > > > [...] 11408 22 34_Re: Reserved Word (formerly blank)16_Sebastian Welton19_sebastian@WELTON.DE31_Wed, 25 Apr 2001 02:54:15 -0500408_- Agreed, do not use reserved words at all. Check the ANSI standard for all reserved words and also check the documentation for the product you are using. For example, I have had a problem interrogating data from a Gentran server (using MS SQL) which had the word FILE as a column heading. This was a reserved word in this particular case but if you look at DB2 UDB SQL guide it is not a reserved word. [...] 11431 65 22_Re: performance tuning15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Wed, 25 Apr 2001 03:47:35 -0500423_iso-8859-1 Well, I seem to have started something of a philosophical debate! A couple of points:

I absolutley agree that often the BIGGEST payback can be from tuning the worst SQL. But it is also the most work. In many cases the SQL is not alterable (packaged apps) or the DBA does not have the clout to force the application to make a change - so the only way to make things better is by system/object tuning. [...] 11497 24 22_Re: performance tuning11_Not telling20_spufidoo@YAHOO.CO.UK31_Wed, 25 Apr 2001 03:59:19 -0500473_- >I absolutley agree that often the BIGGEST payback can be from tuning the >worst SQL. But it is also the most work. >Best of all is to get the object design and SQL right _before_ they hit >production. You've obviously never worked with "****" (a leading insurance package) then! (Name withheld to protect the guilty.) Missing the leading column of an multiple key index, thus reducing a matching index scan to a tablespace scan is a particular favourite of theirs. [...] 11522 111 30_Re: Cost of additional indexes15_Toine Michielse18_vndobtm@US.IBM.COM31_Wed, 25 Apr 2001 11:38:26 +0200567_us-ascii Just a quick remark..... The numbers given, 20ms for each row inserted/deleted and 40 ms for updates affecting the index, are REALLY pessimistic. This assumes EACH row will generate a synch I/O and the I/O does take the full 20ms.

First of all I would be very surprised to see 20 ms added for each row inserted per index. Secondly, nowadays, in general we see better average response times than 20 ms for a sync I/O. Typically I see between 10 and 12 ms.... Of course your milage may vary... But if you would see 20ms/row/index.. it sure is time [...] 11634 118 22_Re: performance tuning0_19_mike.holmans@BT.COM31_Wed, 25 Apr 2001 11:32:19 +0100527_iso-8859-1 Well, from the original post, it seemed that they have a reasonably mature DB2 shop with one of its apps in a mess. It wasn't a cry for help along the lines of "my DB2 system doesn't work". If it was a shop entirely new to DB2, then checking all these system-level things might well be worthwhile - but then only if the DBA actually knew what it was he wanted. Someone asking for basic advice about how to start a performance audit is unlikely, I would submit, to have a complete grasp of how to tune MVS so as [...] 11753 37 30_Re: Cost of additional indexes0_19_mike.holmans@BT.COM31_Wed, 25 Apr 2001 12:18:33 +0100401_- Sanjeev wrote:

> Many times i have observed that indexes are created just to see ACCESSTYPE > "I" in the Explain output. This should not be the criteria. We should always > look at the cardinality and the filteration we are goind to get once the > index is there. For e.g. STATUS column having 3-4 distinct values doesn't > make sense to be in index if it is frequently updated as well. [...] 11791 81 38_Re: performance tuning/Cost of indexes22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Wed, 25 Apr 2001 09:06:38 -0300612_iso-8859-1 Dear John,

I have been following the discussion on the DB2-L list about performance tunning and I thought your point of view was very interesting. Here we have got several CICS applications which have their SQL constantly evaluated and everything works smoothly, with a nice response time. However, from time to time something happens and some CICS applications spent a lot of time in a locked state, increasing CICS queues and eventually leading to a system crash. We could not determine the cause, to me it seems to be I/O contention or other system misconfiguration. I would be glad if [...] 11873 102 52_Re: SQLJs db2profc UDB to DB2 OS/390 via DB2 CONNECT20_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE31_Wed, 25 Apr 2001 06:52:26 -0500337_- SQLSTATE 52503 means: The authorization ID specified in SET CURRENT SQLID is not one of the authorization IDs of the application process.

And really, CLI/ODBC-trace shows that DB2PROFC ist trying to execute "SET CURRENT SQLID" with the qualifier(!) as argument. So it will work only with a valid secondary id as qualifier. [...] 11976 24 31_Re: DB2 connect & shadow tables11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Wed, 25 Apr 2001 08:32:41 -0400152_us-ascii Just a suggestion, have you tried SCHEMALIST parameter?... It does a pretty decent job and I think is a little less maintenance oriented than a 12001 171 22_Re: performance tuning15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 25 Apr 2001 07:48:48 -0500309_ISO-8859-1 "....quite a few of whom are going to resent the apparent implication that it's their component which is to blame rather than the app itself."

Boy, is that true for me! Every time I just ask questions for my own understanding in that direction I get a lot of overly-defensive comments! [...] 12173 12 39_FYI: DB2 UDB 7.2 (non OS/390) announced14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 25 Apr 2001 07:58:46 -0500316_- http://www.ibmlink.ibm.com/usalets&parms=H_201-128

James Campbell

================================================ 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. 12186 17 35_max number of volumes in a stogroup21_Rechsteiner, Tom (TH)20_trechsteiner@DOW.COM31_Wed, 25 Apr 2001 09:27:34 -0400428_iso-8859-1 is there a limit from the DB2 point of view? what is it?

is there a limit from a MVS point of view? what is it?



(help me settle a small inter-office bet)

================================================ 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. 12204 47 16_CA/Platinim P99D18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 25 Apr 2001 09:32:28 -0400551_iso-8859-1 Has anyone out there recently upgraded to this release of CA DB2 tools? (P99D) We are currently running DB2 V5 and upgrading from P97F to P99D. We have run in to all sorts of problems. One problem we are having is with the Rapid Reorg utility and the 'space-defn' function. Seems if you use this feature now it takes the Pqty & Sqty for the 'tablespace' and applies that to ALL the indexes as well, even tho you haven't specified any indexes. Needless to say you can chew up alot of DASD if your indexes are being changed without your [...] 12252 24 56_WIN/UDB R610 connection with OS390/DB2 R610 using JDBC .24_Islam Abdelaleem Mohamed17_aleemi@EG.IBM.COM31_Wed, 25 Apr 2001 14:41:08 +0300546_us-ascii Hello ; We had a WIN 2000 with UDB R610 and we want to communicate with DB2 R610 or above at OS/390 using JDBC interface which supplied with DB2 R610 at OS/390 . after setting the connection (Token-Ring / TCPIP) layer in both OS/390 and WIN 2000 My Question What setting I needs in DB2 R610 at OS/390 to enable JDBC ? for successful WIN/UDB connection with OS390/DB2 . What layers of Software in OS390 I needs for successful connection ? for example (OS/390 , TCP/IP , DB2 and JDBC ........ etc . Are somebody know Where I can find [...] 12277 140 60_Re: SQLJs db2profc UDB to DB2 OS/390 via DB2 CONNECT (Reply)0_22_scott.goodell@WCOM.COM31_Wed, 25 Apr 2001 08:34:36 -0500393_us-ascii Thanks for the information. I agree it seems silly. I think I will open a problem with IBM on this since the only way we can get this to work is to run db2profc against a UDB database, setup with the same OS/390 table definitions, creating a BINDFILE and then binding that file to DB2 OS/390. I would like to run db2profc directly to DB2 OS/390 and eliminate the middle layer... [...] 12418 106 76_On Unix UDB V764 Bit ; getting a SQL0970N by a "create database from backup"12_Peter Horner27_peter.horner@JULIUSBAER.COM31_Wed, 25 Apr 2001 15:41:49 +0200386_us-ascii Hi List

I'm getting the following message while trying a "create database from backup"

SQL0970N The system attempted to write to a read-only file. SQLSTATE=55009

I copied the backup from another unix-box and was trying to create the database using the "create database from backup" in the DB2CC.

This is on SUN Solaris 8 with DB2 UDB V7 64 Bit [...] 12525 165 60_Re: SQLJs db2profc UDB to DB2 OS/390 via DB2 CONNECT (Reply)34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Wed, 25 Apr 2001 09:48:50 -0400603_iso-8859-1 I think I had done this few months ago. I vaguely remember using -online option & running db2profc on USS (OS/390). It worked for me that time.

-Venkat Pillay





> -----Original Message----- > From: scott.goodell@WCOM.COM [SMTP:scott.goodell@WCOM.COM] > Sent: Wednesday, April 25, 2001 9:35 AM > To: DB2-L@RYCI.COM > Subject: Re: SQLJs db2profc UDB to DB2 OS/390 via DB2 CONNECT > (Reply) > > Thanks for the information. I agree it seems silly. I think I will open > a > problem with IBM on this since the only way we can get this to work is to > run db2profc [...] 12691 43 39_Re: max number of volumes in a stogroup11_Moore, Tony15_TMoore@IKON.COM31_Wed, 25 Apr 2001 09:52:59 -0400576_iso-8859-1 Yo Tom, Note that this is from V4 SQL Reference manual, so it's a bit old but...

Number of volumes: There is no specific limit on the number of volumes that can be defined for a storage group. However, the maximum number of volumes used for allocation is 133. Thus, there is no point in creating a storage group with more than 133 volumes. For SMS-managed data sets, MVS has an even smaller limit on the number of volumes that can be defined: 59 at this writing. For the latest information on that restriction, see DFSMS/MVS: Access Method Services for [...] 12735 203 22_Re: performance tuning10_Raghu Kota21_raghukota@HOTMAIL.COM31_Wed, 25 Apr 2001 13:43:37 -0000673_- Well said Linda!! Good work!!!



>From: "Billings, Linda" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: performance tuning >Date: Wed, 25 Apr 2001 07:48:48 -0500 > >"....quite a few of whom are going to resent the apparent >implication that it's their component which is to blame rather than the app >itself." > >Boy, is that true for me! Every time I just ask questions for my own >understanding in that direction I get a lot of overly-defensive comments! > >In an established environment the problems with a poorly performing >application usually are found in poor design [...] 12939 60 39_AW: max number of volumes in a stogroup12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 25 Apr 2001 15:55:26 +0200713_iso-8859-1 Tom,

the limit of volume id's in a storage group is 133 (DB2 for OS/390 Version 6).

HTH.

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Linux is like a wigwam: no gates, no windows, and an apache inside..... [...] 13000 39 39_Re: max number of volumes in a stogroup16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 25 Apr 2001 09:58:24 -0400419_iso-8859-1 For non-SMS in DB2 V5.1 the limit is 133 vols in a STOGROUP. For SMS under OS/390 V2.6 there is a limit of 55 (a DFP limit, not a DB2 one)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Rechsteiner, Tom (TH) [mailto:trechsteiner@DOW.COM] Sent: Wednesday, April 25, 2001 9:28 AM To: DB2-L@RYCI.COM Subject: max number of volumes in a stogroup [...] 13040 60 39_Re: max number of volumes in a stogroup16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 25 Apr 2001 10:08:47 -0400533_iso-8859-1 Correction. Tony is right. For SMS it is 59 not 55. 133 is still correct for DB2 user-specified.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@nscorp.com] Sent: Wednesday, April 25, 2001 9:58 AM To: DB2-L@RYCI.COM Subject: Re: max number of volumes in a stogroup



For non-SMS in DB2 V5.1 the limit is 133 vols in a STOGROUP. For SMS under OS/390 V2.6 there is a limit of 55 (a DFP limit, not a DB2 one) [...] 13101 152 81_Re: On Unix UDB V764 Bit ; getting a SQL0970N by a "create datab ase from backup"13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Wed, 25 Apr 2001 15:08:25 +0100524_iso-8859-1 Peter,

Was the other Unix box also Solaris? We've just discovered that we can't restore a database from a backup taken on another Unix platform (in our case we were trying to restore to AIX from Solaris). IBM have confirmed that this can't be done.

Adrian Savory ACSIS Ltd

-----Original Message----- From: Peter Horner [mailto:peter.horner@JULIUSBAER.COM] Sent: 25 April 2001 14:42 To: DB2-L@RYCI.COM Subject: On Unix UDB V764 Bit ; getting a SQL0970N by a "create database from backup" [...] 13254 217 22_Re: performance tuning15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 25 Apr 2001 09:21:45 -0500734_ISO-8859-1 Thanks!

-----Original Message----- From: Raghu Kota [mailto:raghukota@HOTMAIL.COM] Sent: Wednesday, April 25, 2001 8:44 AM To: DB2-L@RYCI.COM Subject: Re: performance tuning



Well said Linda!! Good work!!!



>From: "Billings, Linda" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: performance tuning >Date: Wed, 25 Apr 2001 07:48:48 -0500 > >"....quite a few of whom are going to resent the apparent >implication that it's their component which is to blame rather than the app >itself." > >Boy, is that true for me! Every time I just ask questions for my own >understanding in that direction I get a lot of [...] 13472 52 22_Re: performance tuning14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 25 Apr 2001 09:30:36 -0500381_iso-8859-1 Can of worms...to say the least. Can we dicuss a different approach? I think all the thread participants have provided insightful and meaningful theories/ideas/techniques/concerns.

However, lets look at it from a different point of view. You are assigned this task...where do you start? Some have said the system, some more have said the application. I say: [...] 13525 206 20_Re: CA/Platinum P99D15_Zander, Barb J.19_bzander@STATE.ND.US31_Wed, 25 Apr 2001 09:46:55 -0500434_iso-8859-1 Steve, I haven't tried P99D but I do have P99C and it does not fully support V6 of DB2 yet(it is compatible but does not exploit the features and some of the features it has no idea what they are, so when doing alters you have to manually edit your process before they are run). I have open problems, that as of now, have no fix. I talked with support and I was told that they are not fixed in future releases either. [...] 13732 77 22_Re: performance tuning15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 25 Apr 2001 09:53:47 -0500516_iso-8859-1 Good point....there is always politics.

There is the ideal world and then there is the real world.

"Before you go running off to someone else to clean up their stuff, you'd better make sure that your own stuff is in respectable order..."

That will save some embarassment.

Linda





-----Original Message----- From: Scott Trometer [mailto:scott.trometer@RCI.COM] Sent: Wednesday, April 25, 2001 9:31 AM To: DB2-L@RYCI.COM Subject: Re: performance tuning [...] 13810 13 45_Re: Is Any Function for AutoNumber Generation12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 25 Apr 2001 09:37:56 -0500403_- The new V7.2 of DB2 for Unix & Windows includes a SEQUENCE, which provides automatic number generation that is not associated with a table.

Roger Miller

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 13824 80 20_Re: CA/Platinim P99D0_20_John_Lendman@FPL.COM31_Wed, 25 Apr 2001 11:17:22 -0400712_us-ascii YES I am having problems with 99D also.

My problems are in the PDA. JOhn





"Whittaker, Stephen" cc: Sent by: "DB2 Data Subject: CA/Platinim P99D Base Discussion List"



04/25/01 09:32 AM Please respond to "DB2 Data Base Discussion List"











Has anyone out there recently upgraded to this release of CA DB2 tools? (P99D) We are currently running DB2 V5 and upgrading from P97F to P99D. We have run in to all sorts of problems. One problem we are having is with the Rapid Reorg utility and the 'space-defn' function. Seems if you use this feature [...] 13905 29 22_Re: performance tuning0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 25 Apr 2001 10:21:27 -0500451_us-ascii Richard, I like your examples, but could you expand on them just a little more, they are rather cryptic. I find it's handy to have 'real-world' (vs salesman 'propaganda') examples when trying to fight the tuning war with management.

SQL Tuning 1: 400 hours per month to 15 hours - part time, while in production SQL Tuning 2: $14 million saved, deferred new MF 2 years SQL Tuning 3: weekly AR from 50 hours to 4 [...] 13935 26 34_Re: Reserved Word (formerly blank)12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 25 Apr 2001 10:08:33 -0500448_- Using quotes (or apostrophes in some situations) is called a delimited identifier, and it provides a technique to be much less sensitive to reserved words. Some products that generate SQL use delimited identifiers, for example.

Reserved words are added to most releases, so the precompiler and parser try to understand the context and allowed reserved words to be used, when it's possible. The technique does not work for every case. [...] 13962 27 41_web access to production operational data18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM31_Wed, 25 Apr 2001 09:47:48 -0400593_iso-8859-1 Do you have web access to your online production data? Any special setup needs, like data sharing?

We want to develop web inquiry to production data but are concerned about the impact on our current online system. Some people are saying we need to implement data sharing. Currently we have two subsystems for production, but they contain data about customers for two different companies. I am not sure exactly how the web will access the data, but I do not think we are using any new products like WebSphere. I believe the plan to execute CICS transactions, and the web [...] 13990 120 20_Re: CA/Platinum P99D5_chris22_hhardy1@HOUSTON.RR.COM31_Wed, 25 Apr 2001 10:43:10 -0500359_iso-8859-1 RE: CA/Platinum P99D CA has a matrix (outline) of DB2 V6 features (visit web site) which P99C embraced and which features are encompassed with P99D tape...

this should provide insight as to what is and what's not covered (P99C & P99D) per DB2 V6 features..... the SE or the CA tech rep should have explained this, about a year ago..... [...] 14111 61 45_Re: web access to production operational data16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 25 Apr 2001 11:42:29 -0400350_iso-8859-1 Why do you want data sharing for this? Are you out of capacity?

Probably more of concern is the SQL from the web stuff. If at all possible, have it be STATIC rather than DYNAMIC and put it in a Stored Procedure.

If you leave the SQL DYNAMIC in the client, you are faced with the same challenges as often found with QMF: [...] 14173 29 45_DDL generation from DB2 Administration panels11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Wed, 25 Apr 2001 16:38:56 +0100436_- All,

We have a number of columns defined as 'INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY' within DB2 v6 on OS/390. When I view the definition from the system catalog panels within the administration tool I see a value of 'J' against the Def column (DEFAULT from the SYSIBM.SYSCOLUMNS table), however when I use the line command GEN to generate the DDL for the table I only get 'INTEGER NOT NULL' against the column. [...] 14203 109 22_Re: performance tuning13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Wed, 25 Apr 2001 08:51:30 -0700522_iso-8859-1 I've been emailing this thread to a half dozen people in our shop. Saying "See I'm not just a raving lunatic.

For performance:

1. Logical Database design IS important. (you know: the normal forms) 2. Physical design IS important. (It does matter wether its a 100 or 100 Million rows) 3. SQL coding technics ARE important. (move a predicate from a WHERE clause to a HAVING clause can cut CPU usage in half. switch from a noncoorelated IN list to a coorelated EXISTS can cut it in half again.) [...] 14313 171 20_Re: CA/Platinum P99D0_19_csutfin@AMSOUTH.COM31_Wed, 25 Apr 2001 11:00:22 -0500507_iso-8859-1 Steve and Barb:

I have been trying to finish that install (P99D) now for about a two months. Yuck. I don't have those problems with Rapid Reorg though. Mine works just fine. Only the tablespace got updated with the new quantities. The index stayed the same.

Are either of you doing anything with Report Facility or Compile/PRF? I am getting abends in the CICS region when I try to use either function. I did get a fix for Report Facility in TSO. That fixed my problems in TSO. [...] 14485 65 20_Re: CA/Platinum P99D18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 25 Apr 2001 12:06:22 -0400461_iso-8859-1 I'm beginning to think that none of this P99x stuff works at all. When they start changing the 'defaults' to something else that doesn't say much for development. Why not leave the defaults as they were and make changes to the new enhancements. Seems like the tail wagging the dog here. Anyways, it don't work so we either fall back to P97F and install DB2 V6 with that or I pull the plug on CA and get another suite of tools in here that work. [...] 14551 90 22_Re: performance tuning16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 25 Apr 2001 11:46:31 +0530614_- Hi,

I thought about not replying this mail because of its generalized nature and requirements of some long writing which could be nothing but sharing some experience. After the mails from experts, i decided to involve myself in the discussion.

As Linda pointed out, it is more of an art than science. The science part of the performance tuning of SQL is understand the SQL processing, understand which all statistics affect it and how, which all utility is required to run to influence the optimizer(in a better way) and most importantly understanding the meaning of each and every columns of [...] 14642 57 22_Re: performance tuning14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 25 Apr 2001 11:07:10 -0500705_iso-8859-1 Will supply in day or so.

Regards, Richard

Richard Yevich, YL&A IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 Richard_Yevich@YLAssoc.com http://www.YLAssoc.com -- DB2 Performance Journal

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Rohn.Solecki@MTS.MB.CA > Sent: Wednesday, April 25, 2001 10:21 AM > To: DB2-L@RYCI.COM > Subject: Re: performance tuning > > > Richard, I like your examples, but could you expand on them just a little > more, they are rather cryptic. I find it's handy to have 'real-world' (vs > salesman 'propaganda') examples when trying to fight the tuning war [...] 14700 26 67_Re: DB2 V5, OS/390 - Strobe reports High Wait time in DSNVSR module12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 25 Apr 2001 11:04:14 -0500497_- You need to use the right tool for the job. I like to see a Strobe report to see how an application is running. It's good at identifying application modules and SQL statements. It is not a particularly good tool for a statement. Finding the DB2 module is often worthless information, as in this case. Discovering that waiting is done in a module that waits is not helpful. The DB2 accounting information splits out the wait time into a number of different categories. You need the details. [...] 14727 43 22_Re: performance tuning13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 25 Apr 2001 12:32:30 -0400604_iso-8859-1 Hey Scott, Good points. Your second though, >Second, what_are_you_good_at?? If you are an application person...< reminds me of an old joke, which provides a little caveat there.

There's this policeman who walks up to a little boy under a street light on his hands and knees in the street obviously looking for something. The policeman asks the boy what's up and the youngster says, "I lost some money. And I'm looking for it." So the friendly policeman gets down on his hands and knees and starts looking with the boy. After several minutes, the policeman says, "Are you sure this [...] 14771 27 31_Null Input to Stored Procedures14_Cannamela, Jim26_jcanname@IDCFS.STATE.IL.US31_Wed, 25 Apr 2001 11:42:55 -0500419_iso-8859-1 I have a stored procedure that performs an insert to a table with nullible columns.

Running the procedure via SPB or CLP as follows call myproc (123, 'ABCDEF',NULL) works fine.

Attempts to call the procedure through a VB application using ADO fails with a datatype mismatch.

Tried this with the IBM OLE DB Provider and with the MS OLE DB provider for ODBC databases without sucess. [...] 14799 49 35_Re: SPECIAL REGISTER (Current Date)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 25 Apr 2001 18:43:38 +0530405_- Raman,

The only way of getting it thru the special register is already mentioned by you. If at all you want to do this in the program, you have to add/subtract some fixed date/days/years/months from the date obtained from special register.

I believe SPECIAL REGISTER is meant to do that only.

The processor date was changed by most of the installation during Y2K testing time. [...] 14849 232 22_Re: performance tuning12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 25 Apr 2001 20:38:46 +0200415_iso-8859-1 Hi, Many times I find out that on top of "not so well written SQL" you find a worse "not so well written application logic" which is much harder to fix. (not including 3rd party software that can't be touched). I've seen loops with singleton selects instead of cursors .... I've seen repetitive calls to the same row ... I've seen manually coded joins done by calling X programs (one per table) ... [...] 15082 49 29_Re: DB2 REORG/PURGE WAIT TIME12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 25 Apr 2001 20:58:46 +0200658_iso-8859-1 Hi, Add bufno to your JCL.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Jeff A L'Italien" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, April 24, 2001 7:37 PM Subject: DB2 REORG/PURGE WAIT TIME



> We have created a process of performing a purge of a partitioned structure > (32K) using the IBM DB2 REORG utility (DB2 V5), and are seeing a lot of > slowdown during the discarding of rows being written to SYSDISC. A STROBE > report indicates that the wait time within the utility execution (84%) is on > SYSDISC which is being [...] 15132 81 28_Re: Rexx/DB2 Memory Problems12_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 25 Apr 2001 14:26:37 -0400482_US-ASCII Wow! Thank you. I moved the DECLARE out of the loop and everything is working fine. It has also improved the performance.

Cheers, Troy Coleman Coleman Consulting, Inc.

>>> "Maculsay, Gary R" 04/25/01 01:59PM >>> One other problem I ran into was the seemingly inocuous placement of DECLARE CURSOR within a major looping construct. We watched this in SDSF, slowing chunking away more heap, until it ran into system maximums. [...] 15214 102 22_Re: performance tuning14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 25 Apr 2001 19:31:05 +0100595_iso-8859-1 OK,

I've lurked all day and now I have something to add.

Most people need to approach tuning very simplistically. Also, there is the question of whether you know which programs need tuning and which ones do not.

Assuming you DON'T know what is bad (most of the time), then you really have to use software to help you. Apptune from BMC or Detector from CA will TELL you where the most heavily resource consuming SQL is in your application/subsystem. The biggest "bang for your buck" will come from tuning the worst stuff first. These tools will also help you [...] 15317 139 22_Re: performance tuning16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 25 Apr 2001 14:46:27 -0400324_iso-8859-1 Phil, I don't know about Apptune but we use Detector almost every day just as you described. It has saved us many thousands of dollars of CPU cost (or maybe it has saved us many thousands of dollars in labor costs to find the CPU cost???).



regards,

eric pearson NS ITO Database Support [...] 15457 27 78_Stored procedure builder, changing the external name for SQL Stored procedures0_19_Tim.Lowe@STPAUL.COM31_Wed, 25 Apr 2001 13:49:08 -0500469_us-ascii I am starting to experiment with Stored Procedure Builder creating SQL Stored procedures on DB2 on OS/390 version 6. When it builds the SQL procedure, I understand that it builds a C program and compiles it, essentially making it an "external procedure". Right now, it appears to name this itself. (For example, SQL97834.) My question is, how do I get it to use a different external name? (for example, use the name of the procedure as the external name) [...] 15485 62 35_Re: Null Input to Stored Procedures9_Colin Fay13_cfay2@CSC.COM31_Wed, 25 Apr 2001 14:54:20 -0400673_us-ascii Hi Jim ,

I think I just say something related to that, Look at :

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

see ' 8.4 CLI and ODBC Applications'

Colin











"Cannamela, Jim" @RYCI.COM> on 04/25/2001 12:42:55 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Null Input to Stored Procedures



I have a stored procedure that performs an insert to a table with nullible columns. [...] 15548 37 81_Re: Stored procedure builder,changing the external name for SQL Stored procedures15_Benjamin Reches33_Benjamin.Reches@MORGANSTANLEY.COM31_Wed, 25 Apr 2001 15:08:18 -0400299_us-ascii you still reference the sproc by the name you gave it in the create statement. the reason for that SQLnnnnn name is to allow you to specify names greater than 8 bytes and to allow the reuse of the sproc name by any other schema name. the SQLnnnnn is only used in the loadlib and dbrm. [...] 15586 44 49_Application Development for Major Software Vendor12_Geoff Greene13_geoff@WLK.NET31_Wed, 25 Apr 2001 14:16:44 -0500741_iso-8859-1 An Industry Leader in the field of Enterprise software development is looking for Senior DB2 professionals for implementation and support of R&D business applications. Responsible for analyzing, supporting and delivering automated business solutions that enable business clients to be highly productive. This position requires an understanding of the strategies, goals and objectives defined for the assigned business area(s) and applying the appropriate use of technologies to translate business needs into automated business solutions. Analyst duties will consist of approximately 60% support and maintenance and 40% application development projects. This position reports to the manager of Support and Maintenance for R&D [...] 15631 29 46_Calling a Java Stored procedure from a Trigger14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Wed, 25 Apr 2001 15:36:00 -0400590_iso-8859-1 Hello List,

Does DB2 V7.1 (NT & Unix) support calling a Java (JDBC or SQLJ) stored procedure from inside a trigger ?

I tried creating a "after delete" trigger and putting a CALL statement but get a syntax error in CREATE TRIGGER. The V7.1 SQL reference for CREATE TRIGGER does not mention anything about calls to Stored procs or UDFs, but I gathered from previous discussions on this list and the DB2-L archive that SQL stored procs can indeed be called from within a trigger (or is that feature available only for DB2 for OS/390 ?) . What about [...] 15661 52 34_Re: Reserved Word (formerly blank)19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 25 Apr 2001 14:47:07 -0500597_iso-8859-1 Hi Roger, Thanks for your reply. When a couple people get back from their travels we'll recreate the problems we faced and get some copies to you or level 1. Again, we had trouble with EDITABLE, VISIBLE and REQUIRED when used as UNDELIMITED column names.

Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received [...] 15714 65 22_Re: performance tuning14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 25 Apr 2001 16:11:36 -0500509_iso-8859-1 Issac,

Which is still part of SQL tuning: which is not only fixing the SQL, but collapsing application logic into SQL, moving cursors within cursors to joins (inners and outers), removing SQL I/O layers, etc. It is all the same thing. SQL is application logic and becoming more so.

Regards, Richard

Richard Yevich, YL&A IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 Richard_Yevich@YLAssoc.com http://www.YLAssoc.com -- DB2 Performance Journal [...] 15780 56 22_Re: performance tuning13_Olson, Carlos14_COlson@QRS.COM31_Wed, 25 Apr 2001 14:26:23 -0700483_iso-8859-1 We had a number of batch programs processing sequential files that did millions of get pages on 10,000 page tables. Sorting input files first and then adding control break logic to programs made incredible improvements. We also denormalized a few related tables allowing us to change a cursor with a 3 table join into a 1 table select. That change reduced a weekly job stream from 40 to 10 CPU hours. A tool like Detector will help identify opportunities like these. [...] 15837 19 24_UNDOCUMENTED REASON CODE16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 25 Apr 2001 14:58:55 -0700513_us-ascii Anyone have an idea as to what reason code 00C20110 is??? I've seen this in the DB2 Master address space, but can't find any supporting documention in V5 or V6 manuals, Book Manager, Quickref, or on IBM's Web Site?

Regards, Jeff L'Italien American Express

================================================ 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. 15857 44 28_Re: UNDOCUMENTED REASON CODE15_Murley, Michael22_Michael_Murley@BMC.COM31_Wed, 25 Apr 2001 17:36:37 -0500438_iso-8859-1 Jeff,

This reason code was introduced with PTF UQ39685 for DB2 V5 and UQ39686 for DB2 V6. It indicates that the HURBA for the space is not consistent with the high-preformatted page number recorded in the header page. You can look at APAR PQ33002 for more information.

If you're using any ISV utilities, they may require fixes to support this change (I know some of our utility products had fixes for this). [...] 15902 62 28_Re: UNDOCUMENTED REASON CODE13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Wed, 25 Apr 2001 17:41:45 -0500383_iso-8859-1 I found four hits from an IBMLINK search on RC00C20110

APAR Identifier ...... PQ33002 Last Changed ........ 00/11/24 PREVENT APPLS. FROM ACCESSING TABLESPACES ON WHICH ICF CATALOG INFORMATIONON HURBA DOESN'T MATCH DB2 INFORMATION FROM SPACEMAP.

PTF List: Release 510 : UQ39685 available 00/02/16 (F002 ) Release 610 : UQ39686 available 00/02/14 (F002 ) [...] 15965 49 28_Re: UNDOCUMENTED REASON CODE18_Carpenter, Stephen25_Stephen_Carpenter@BMC.COM31_Wed, 25 Apr 2001 17:40:05 -0500584_iso-8859-1 Jeff,

V6.1 APAR PTF UQ39686 adds a new field to the HEADER PAGE. Excerpt of text from UQ39686: DB2 has been modified to maintain a field in the header page of a pageset or partition which keeps track of the high-used page number. This field will be updated at the same points in time as the level-ID (physical close, pseudo-close, and at the DLD checkpoint interval). When the pageset or partition is physically opened, this value will be compared with the high- used RBA returned by VSAM. If the high-used page number derived from the VSAM hurba is lower than [...] 16015 27 20_DSN1COPY Performance17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Wed, 25 Apr 2001 17:06:24 -0700561_us-ascii We are copying a DB2 full image copy from one DB2 to a DB2 vsam dataset on another DB2. The image copy is of multiple datasets and the tablespace is segmented. Each dataset (2 g) takes about 35 minutes to copy while the load takes only 42 minutes for all 4 datasets (8 g). I was under the impression that DSN1COPY would be much quicker than this. Is there something we're missing? I know there are other solutions like image copy the datasets using dsnum and then dsn1copy concurrently all these copies. Also I know there are third party products [...] 16043 132 38_Re: Coalesce replacing NULL processing12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 26 Apr 2001 10:10:04 +1000414_iso-8859-1 Dear Dudes, I would have done this yesterday, but it was ANZAC day ~ a public Holiday!

As per Richard's comments, which I agree with, I began to think that yes COALESCE should be more efficient (less info to pass across and the like). Then a former colleague chimed in and said why don't you test it out! Simple piece of SQL a tight loop for say 50,000 iterations and compare the CPU times. [...] 16176 50 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group13_Olson, Carlos14_COlson@QRS.COM31_Wed, 25 Apr 2001 17:24:42 -0700576_iso-8859-1 Our environment is DB2 V6.1 under OS/390 V2.6. Are you saying that if I define a DB2 STOGROUP with VOLUMES("*"), then the ACS routines used to assign DB2 datasets can only use the first 59 volumes defined to an SMS Storage Group? Is it the VSAM alter ADDVOLUMES during dataset extend failure that is limited to 59 volumes? I'm asking for clarification on this 59 volume limit because our largest SMS Storage Group currently has 75 volumes defined and all contain data although no DB2 datasets. I am planning to create a 150 volume SMS Storage group to be used [...] 16227 62 30_Re: Undocumented error message15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 26 Apr 2001 11:14:52 +1000293_- You've probably got it by now - yesterday was ANZAC day so I'm still catching up with all my mails - but it's to do with Extended Addressability. It seems DB2 is expecting the DS to be EA-enabled and it isn't. Or the other way 'round. Here's the message text from somewhere on the web: [...] 16290 182 38_Re: Coalesce replacing NULL processing13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 25 Apr 2001 20:27:30 -0500480_iso-8859-1 Nick,

Did that former collegue also say that he wouldn't be surprised if the COALESCE performed worse than the NULL indicator? Since previous testing of his own had shown scalar functions such as SUBSTR had added up to 10% CPU overhead (for an extremely simple example like yours) as opposed to writing this in application code.........CASE is another one (Michael Hannan and I did some thorough testing on this last year before our IDUG AP presentations). [...] 16473 83 29_Offline Backup on AIX Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Thu, 26 Apr 2001 10:40:15 +0700346_us-ascii Dear all,

We are facing a weird problem when we're trying to do an offline backup on AIX. The estimated size of the DB is 2,3 GB. And we've already set a directory (a JFS named /xtrabackup) with 8 GB in size. But everytime we run the backup process, a message saying 'A device full warning encountered on device /xtrabackup' [...] 16557 70 30_Re: Cost of additional indexes16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 26 Apr 2001 09:32:54 +0530527_- Thanks Mike, For changing my generalized comment to a specific one. As per your example, the records with lesser %age of a particular value will be containing the operational data and if most of the queries are on that, accesspath will be real better because of the better filtering.

You have also mentioned REPOT(VARS), but if this is not feasible SYSIBM.SYSCOLDIST can also be maintained for getting the better performance. In that case the 19M rows with STATUS=COMPLETE will not get the default filter factor. [...] 16628 103 22_Re: performance tuning16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 26 Apr 2001 10:08:10 +0530425_iso-8859-1 It makes me feel always that DB2-L is a great thing made in this world. As mentioned by John, right in the morning, looking at nice replies from all experts on such a good topic is great.

This thread really gave some good insight on the approach different people think regarding the one of the interesting topic i.e. performance tuning. And everyone is correct. Some are generic and some are specific. [...] 16732 25 18_Performance Tuning12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Thu, 26 Apr 2001 05:55:07 -0000396_- All, The last two days was so interesting to read and it was a toast to get to know a lot from so many experts and so many viewpoints but everything so finely tuned. I also have a query regarding indexes with multiple columns say 5 columns .Is it good having a high value for the firstkeycard or vice versa ? Or what should be a good value for the Firstkeycard comparitive to Fullkeycard. [...] 16758 90 24_DB2 Control Center Bug ?11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Thu, 26 Apr 2001 15:57:39 +0700560_us-ascii Dear all,

Have you guys ever tried loading data from textfile to a table with IDENTITY Column and Generated Always Column with Control Center ? In the Column Page, there's a "Generated Column Behaviour" section with several options. In the SQL Reference, the correct command for all those options are GENERATEDIGNORE, GENERATEDOVERRIDE, GENERATEDMISSING. But in the Control Center, they are GENERATEIGNORE, GENERATEOVERRIDE, and GENERATEDMISSING. Only the GENERATEDMISSING is in the correct command, the other two will always cause error. [...] 16849 139 33_Re: Offline Backup on AIX Problem19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 26 Apr 2001 10:06:39 +0100412_us-ascii The default maximum file size on AIX is 2 Gbytes.

You can "large file enable" the file system, which will allow up to 4 Gbytes.

Or you can split the backup over multiple files -

db2 "backup database dbname to /file1, /file2, ..."

HTH

Phil Nelson Senior DBA Scottish Widows

--Original Message Text--- From: Endy Lambey Date: Thu, 26 Apr 2001 10:40:15 +0700 [...] 16989 59 24_Re: DSN1COPY Performance15_Toine Michielse18_vndobtm@US.IBM.COM31_Thu, 26 Apr 2001 11:08:06 +0200780_us-ascii Hello Charles,

Worth a shot.... Try adding AMP=('BUFND=180')...

//SYSUT1 DD DISP=SHR,AMP=('BUFND=180'), // DSN=

Regards,

Toine Michielse





Charles Jambrosic @RYCI.COM> on 04/26/2001 02:06:24 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: DSN1COPY Performance





We are copying a DB2 full image copy from one DB2 to a DB2 vsam dataset on another DB2. The image copy is of multiple datasets and the tablespace is segmented. Each dataset (2 g) takes about 35 minutes to copy while the load takes only 42 minutes for all 4 datasets (8 g). [...] 17049 138 29_Offline Backup on AIX Problem11_John Pilbin25_John.Pilbin@GRATTAN.CO.UK31_Thu, 26 Apr 2001 10:36:26 +0100348_iso-8859-1 Eddy

This worried us when we first backed up our 16Gb Address database. DB2 is trying to write out three 1Gb files because of AIX limits. It has come to the end of the first. Either reply to the prompt, or use the 'WITHOUT PROMPTING' clause on the BACKUP command.

John Pilbin Database & TP Support Manager Grattan PLC [...] 17188 91 24_Re: DSN1COPY Performance25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Thu, 26 Apr 2001 17:43:20 +0800488_us-ascii Hi,

You can add the BUFNO for the input if your input is an imagecopy dataset. Make sure that you add region parameter in your jobcard.

Regards







(Embedded image moved DB2 Data Base Discussion List to file: (Tel: ) pic19413.pcx) 26/04/2001 05:08 PM







Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: bcc: Subject: Re: DSN1COPY Performance [...] 17280 39 22_Re: performance tuning10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 26 Apr 2001 12:01:02 +0200483_us-ascii I agrre with Dr M. Ebert, in most cases and in a consolidated environment the most part of problems derive from SQL

side, with new appz (poor SQL, no RUNSTATS, no frequent BIND/REBIND to 'tune' the access paths) or modified appz.

OR with new users executing dynamic SQL to select ALL rows in huge tables. And it's impossible to trace all SQL running in a medium DB2 subsystem every day, you can capture only the heavy hitters, as Horacio Terrizzano said. [...] 17320 20 15_DB2 resource???10_Do Duy Nam19_duynam@RIM-TECH.COM31_Thu, 26 Apr 2001 17:11:35 +0700535_us-ascii Dear all,

I am a DB2 beginer and now I woud like to take the certification exam: "DB2 UDB V7.1 Family Application Development". If anyone happens to has any resource relating to this field, please share them to me.

Thank you very much for your consideration.

Nam.

================================================ 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. 17341 25 24_Fork error using DB2LOOK11_John Clarke23_johnwclarke@HOTMAIL.COM31_Thu, 26 Apr 2001 11:23:43 -0000445_- Hi all, Can anyone tell me how to resolve the error listed below? Whenever I try to run a DB2 look command this error occurs.

AIX DB2 7.0 wdc2031a /cpfddb/cpfadmin $db2look help

db2look: 0403-030 The fork function failed. Too many processes already exist.

Cheers, John _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. [...] 17367 50 12_SQLCODE -41912_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 26 Apr 2001 14:25:23 +0200524_iso-8859-1 Hi DB2 for OS/390 collegues.

For converting column-contents from concurrency Deutsche Mark to Euro one of our programmers tried to test the following SQL:

UPDATE myowner.mytable SET BETRAG = BETRAG / 1.95583 WHERE ..

These BETRAG-column-values are really existing as positive and negative values and sometimes the value is plus null. We thought an UPDATE like shown above should not be a problem. But the programmer receives a SQLCODE -419 (SQLSTATE 42911) - an invalid decimal divison. [...] 17418 66 20_Re: CA/Platinum P99D5_chris22_hhardy1@HOUSTON.RR.COM31_Thu, 26 Apr 2001 07:40:43 -0500539_iso-8859-1 Well, as one option call your CA rep and fill him/her in and let them (CA) know what they may have to eat... the resources to have someone come on-site to get this "stuff" installed and functional.

folks don't buy "stuff" that is complex, broke, and difficult to get resolutions.... Call them and "TELL" them what they need to do, your paying the bucks to keep their lights on and feed them..... they have to fix this.... Or as you stated below, pull the plug on CA..... you should not be going through this........ [...] 17485 67 22_Re: Performance Tuning13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 26 Apr 2001 07:35:54 -0500487_iso-8859-1 Rakesh,

I would apply the following basic priorities to index design:

1. Create the index columns in the order which best exploits matching of index predicates, rather than index screening in queries. Index matching predicates can limit the number of index pages read, index screening predicates do not.

2. Then consider the index column sequence to support any ORDER BY and GROUP BY requirements of your queries. Thus allowing a sort to be avoided. [...] 17553 195 16_Re: SQLCODE -41914_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 26 Apr 2001 13:45:23 +0100470_iso-8859-1 If it's the NULL values that are causing the problem, then you need and

AND WHERE BETRAG NOT NULL adding to the WHERE clause

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



-----Original Message----- From: Peter, Georg [mailto:G.Peter@DZBW.DE] Sent: 26 April 2001 13:25 To: DB2-L@RYCI.COM Subject: [DB2-L] SQLCODE -419 [...] 17749 76 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 08:00:37 -0500345_iso-8859-1 Hi, Carlos,

I can't believe that there is that kind of restriction for SMS. I don't know the details but we have much more than 59 volumes in our SMS storage group for our DB2 underlying VSAM data sets.

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services [...] 17826 65 40_Copying data from Version 4 to Version 516_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Thu, 26 Apr 2001 09:01:12 -0400428_iso-8859-1 I am attempting to copy DB2 data from v4 to v5. It looked as though DSN1COPY with OBIDXLAT was the way to go, however, I am not sure. The process ran well until I attempted to open a table and received a levelid error. I ran a REPAIR levelid but still cannot open the table. Am I approaching this the correct way? I cannot seem to find any definitive doc on this process. Any help or thoughts? Thanks in advance [...] 17892 107 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 26 Apr 2001 09:15:50 -0400517_iso-8859-1 Actually I was told that the SMS storage group can have more. The restriction is on the total number of candidate volumes for a dataset managed by DFSMS. We have had as many as 80 volumes in the SMS storage group for our test DB2. I have never seen > 59 in the candidate volume list used by DEFINE. If you have Platinum Fast Load or Rapid Reorg you can see the list of volumes in the DEFINE. I never saw > 59. I guess DFSMS has some sort of logic which says 'build teh best 59 volume candidate volume [...] 18000 112 44_Re: Copying data from Version 4 to Version 512_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Thu, 26 Apr 2001 09:17:03 -0400412_iso-8859-1 Have you possibly tried to run an Unload-DSNTIAUL in V4 followed by a Reload into V5 with the Replace option - maybe that would work better for you?

Just a thought.

Susan Loria

-----Original Message----- From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] Sent: Thursday, April 26, 2001 9:01 AM To: DB2-L@RYCI.COM Subject: Copying data from Version 4 to Version 5 [...] 18113 33 44_Re: Copying data from Version 4 to Version 512_Grayson, Ken27_ken.grayson@CAPGEMINI.CO.UK31_Thu, 26 Apr 2001 14:19:26 +0100616_- Why not UNLOAD the data in V4 subsystem and LOAD into the V5 subsystem (works for me)

Ken Grayson

-----Original Message----- From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] Sent: 26 April 2001 14:01 To: DB2-L@RYCI.COM Subject: Copying data from Version 4 to Version 5



I am attempting to copy DB2 data from v4 to v5. It looked as though DSN1COPY with OBIDXLAT was the way to go, however, I am not sure. The process ran well until I attempted to open a table and received a levelid error. I ran a REPAIR levelid but still cannot open the table. Am I approaching this [...] 18147 46 44_Re: Copying data from Version 4 to Version 519_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 26 Apr 2001 08:40:33 -0500635_iso-8859-1 Mike, DSN1COPY can be difficult sometimes. If you're pressed for time why not just unload and load?

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of [...] 18194 98 16_Re: SQLCODE -41911_John Clarke23_johnwclarke@HOTMAIL.COM31_Thu, 26 Apr 2001 13:47:54 -0000403_- Georg, listed below are a couple of examples of case. Both show the example of dividing by 0. You could probably apply the same logic for nulls.

SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE WHERE (CASE WHEN SALARY=0 THEN 0 ELSE COMM/(SALARY+COMM) END) > 0.25;

SELECT EMPNO, WORKDEPT, (CASE WHEN SALARY=0 THEN NULL ELSE COMM/SALARY END) AS "COMMISSION/SALARY" FROM DSN8610.EMP; [...] 18293 68 44_Re: Copying data from Version 4 to Version 511_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Thu, 26 Apr 2001 14:47:11 +0100535_us-ascii Mike

Have you included the RESET parameter on your job? If not, that may be the cause of the problem. HTH

Dave Hayes

Mike Friedlander wrote:

> I am attempting to copy DB2 data from v4 to v5. It looked as though > DSN1COPY with OBIDXLAT was the way to go, however, I am not sure. The > process ran well until I attempted to open a table and received a > levelid error. I ran a REPAIR levelid but still cannot open the > table. Am I approaching this the correct way? I cannot seem to find > any [...] 18362 96 34_System Specification/Configuration14_Scott, Matthew18_MScott@ALFAINS.COM31_Thu, 26 Apr 2001 09:05:15 -0500383_iso-8859-1 I work for an Insurance Company that does business primarily in the southeast. We have roughly 2500 employees and do $450M in written premium annually. We are in the process of upgrading our legacy systems to third party applications which include Peoplesoft HR 7.51 and FIN 7.53, CSC/MYND/S3+, and SOLCORP. We will be running on OS/390 v2.9 with a DB2 v6.1 backend. [...] 18459 71 18_DB2 Benchmark Data13_Kenski, David25_David.Kenski@MARRIOTT.COM31_Thu, 26 Apr 2001 10:09:41 -0400502_iso-8859-1 Hello all!

I've been asked by management to question the user's of DB2 for benchmark data. Does anyone have web addresses, publications, personal experiences they would like to share, etc. that would be a good source to show DB2 scalability and throughput? For that matter, if you are using DB2 and you experience high transaction volumes and maintain large quantities data, what kind of performance numbers are you getting? What TP monitor (CICS, IMS, WAS, etc.) are you using? [...] 18531 122 44_Re: Copying data from Version 4 to Version 510_Bill Disch23_bill.disch@GENMILLS.COM31_Thu, 26 Apr 2001 09:10:33 -0500442_iso-8859-1 How current is your maintenance. I had problems with DSN1COPY after migration to V5 in summer 1999. I continued to use the V4 DSN1COPY until it was fixed in V5 sometime in summer of 2000. Sorry, I don't have the PTF#. My problems related to broken pages on 32K tablespaces. Also, a recent problem using OBIDXLAT, INCRCOPY on compressed segmented tablespaces. Check IBMLINK, the list of APARS for DSN1COPY is relatively short. [...] 18654 42 16_AW: SQLCODE -41915_Martin Ketterer31_Martin.Ketterer@DE.KAERCHER.COM31_Thu, 26 Apr 2001 16:04:54 +0200449_ISO-8859-1 Hello Georg Peter, there is a chapter in SQL Reference V5 page 92 - 94 called 'Arithmetic with Two Decimal Operands' , maybe the answer to -419 SQLCode. If DEC15 is in effect, the result of Precision(BETRAG) - Scale(BETRAG) + 5 (=Scale(EUROFAKTOR) ) must not exceed 15 ,that is if BETRAG is defined as DECIMAL(13,2) there will be a -419 SQL-Code which says that the scale of the resulting number would be negative. I hope this helps [...] 18697 160 24_Re: DSN1COPY Performance10_Bill Disch23_bill.disch@GENMILLS.COM31_Thu, 26 Apr 2001 09:22:39 -0500536_- Charles, I noticed the same thing when using a FULL Imagecopy as input. Each tape cartridge was read in about 3 minutes until the second dataset was being written and the time for each tape from then on was 8-10 minutes. A sysprog looked into it and noticed that there was still an I/O to the A001 dataset even when writing to A002, A003, etc. Our thought was that DSN1COPY needed to look at the spacemap in A001 to determine that the page to be written was in another dataset. Our solution was to do the FULL Imagecopy by DSNUM [...] 18858 26 24_DB2 on various platforms15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Thu, 26 Apr 2001 10:24:28 -0400541_- Hi

We are currently going through a fact finding project for our migration from MSSQLserver V7 and V2000 to DB2 and was wondering what in your honest opinions is the best platform and OS for the database ? I know there is allot of bias out there and loyalties, but really all that aside would it be NT, AIX, OS390, Solaris, HP, Linux and hardware Intel, RS6000, SUN, Mainframe etc ? We are very open to the hardware and OS, one thing is we are trying to stay away from Intel based servers as that is what our MSSQLserver's run [...] 18885 59 44_Re: Copying data from Version 4 to Version 516_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Thu, 26 Apr 2001 10:30:51 -0400504_iso-8859-1 Ken, Thanks to you and everyone else who responded. One question, the receiving table has two additional fields. The LOAD seems to have trouble with this. Any tricks or suggestions?

Mike Friedlander mike.friedlander@firstinvestors.com 732-855-2529

----- Original Message ----- From: "Grayson, Ken" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, April 26, 2001 09:19 AM Subject: Re: Copying data from Version 4 to Version 5 [...] 18945 16 4_DB2I8_Rush Yan14_db2-l@21CN.COM31_Thu, 26 Apr 2001 22:35:02 +0800338_us-ascii Hello,

Would you mind sharing your CLIST/REXX which start DB2I ?

Rush Yan

================================================ 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. 18962 20 8_RI Issue12_Rakesh Singh14_rasun@JUNO.COM31_Thu, 26 Apr 2001 07:28:30 -0700474_- Hi folks, Can we define two parent table for one child table. for Example The parent row can exist in ( Table A or Table B) for a child row in (Table C). Is this possible or I am getting things mixed up.



Cheers, -Me

================================================ 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. 18983 136 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group11_Rusty Lewis15_rlewis@TSYS.COM31_Thu, 26 Apr 2001 10:28:49 -0400433_us-ascii 59 volumes is a MVS VSAM limit. Currently the limit on the number of volumes defined and allocated to a VSAM data set is 59. See DFSMS/MVS Access Method Services for the Integrated Catalog or DB2 V6 SQL reference under CREATE STOGROUP







"Pearson, Eric L," @RYCI.COM> on 04/26/2001 09:15:50 AM

Please respond to DB2 Data Base Discussion List [...] 19120 108 47_Re: Large VSAM extended datasets/ SMS dataclass15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 09:41:07 -0500515_ISO-8859-1



-----Original Message----- From: David Booher [mailto:David.Booher@QUEST.COM] Sent: Tuesday, April 24, 2001 4:17 PM To: DB2-L@RYCI.COM Subject: Large VSAM extended datasets/ SMS dataclass





I am forwarding to this list also, in hopes that someone might have the answer to this problem.

Dave -----Original Message----- From: David Booher Sent: Tuesday, April 24, 2001 2:35 PM To: 'ibm-main@bama.ua.edu' Subject: Large VSAM extended datasets/ SMS dataclass [...] 19229 40 53_[ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT0_24_ujjwal@LOT.TATASTEEL.COM31_Thu, 26 Apr 2001 20:11:49 +0530530_us-ascii ALL, In our environment , we had to bring 27 tablespace's data from production environment to test enviroment to a particular day's imagecopy which was in production.Both the environment was in different machine ES-9121 , Es-9672(PROD) and DB2 V5.1 . The following steps were taken. DSN1COPY was run in TEST ENV. with the PARM='FULLCOPY,OBIDXLAT,RESET' .DSN1COPY job was succesfully completed.The data was succesfully migrated to TEST .But when the correspondong job was run for PAYROLL SYSTEM the error message was [...] 19270 144 47_Re: Large VSAM extended datasets/ SMS dataclass15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 09:45:14 -0500509_iso-8859-1 Oooops! I hit "send" accidently again.....

No one has answered this one and I can't really help a lot myself. I started kicking the tires on VSAM extended data sets a while back but haven't gotten back to them. If I remember correctly once we defined the special data class for this type of data set all of our DB2 underlying VSAM files would be assigned to it. It didn't matter is they actually allocated over 4G of space or not. I don't recall any variables that differentiated them. [...] 19415 91 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group11_Lynda Stone29_Lynda.Stone@LIBERTYMUTUAL.COM31_Thu, 26 Apr 2001 10:51:56 -0400381_- Carlos,

From my experience, I know that we have 136 volumes in our application production SMS pool currently and two DB2 stogroups (one for tablespaces, one for indexes, for reporting purposes). They have been defined using VOLUMES ('*'). Our table and index data is striped across all 136 volumes and is handling a 25+million SQL / day online application very well. [...] 19507 27 29_Hung thread problem on UDB...16_Sreedhar Bobbadi24_sbobbadi@DUKE-ENERGY.COM31_Thu, 26 Apr 2001 10:54:35 -0400539_us-ascii Hi Folks

We are running UDB Ver. 5.2 (Fix pack 13) on NT 4.

The problem is : The application starts a transaction does a few updates and for some reason hangs (we are in the process of finding out why the app. hangs) the operator of the hung workstation gets fed up and hits the power switch and reboots. The locks that were taken by the app seem to persist, they are not released until I force the application off. I want to know if there is a configurable DB or DBM cfg parm which will roll back transactions [...] 19535 228 44_Re: Copying data from Version 4 to Version 514_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 26 Apr 2001 15:55:11 +0100333_iso-8859-1 If you are really saying that the target table that you are DSN1COPYing TO has extra columns that do NOT exist in the source table that you are DSN1COPYing FROM, then DSN1COPY will NEVER work.

To use DSN1COPY the source and target tables must be EXACTLY the same (as must the tablespaces they are defined in). [...] 19764 55 28_Re: DB2 on various platforms16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 26 Apr 2001 10:59:17 -0400607_iso-8859-1 DB2/UDB EEE on AIX and DB2/UDB OS/390 have consistently been shown to be about the most scalable DBMS around. In particular, OS/390 DB2/UDB has the most linear cost of scalability when used with Parallel Sysplex and CMS processors. The new Z/OS may surpass this. As for reliability, DB2/UDB OS/390 using Data Sharing in a Parallel Sysplex environment is about the highest-availability DBMS/OS combination you will find. Again, the Z/OS may soon surpass this. UDB/EEE on AIX clusters is probably next most reliable. See numerous studies including those from Gartner for details on this info. [...] 19820 50 28_Re: DB2 on various platforms19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 26 Apr 2001 10:02:19 -0500347_iso-8859-1 Hi Rich, Highest scalability, reliability, and availability would be with DB2 for OS390/zOS. One drawback is that some of the newest DB2 features show up on other flavors of DB2 first then retro-fitted back to DB2 for OS390/zOS. Sometimes the newest features show up on DB2 for OS390/zOS first. See www.tpc.org for benchmark info. [...] 19871 72 44_Re: Copying data from Version 4 to Version 512_Grayson, Ken27_ken.grayson@CAPGEMINI.CO.UK31_Thu, 26 Apr 2001 16:02:47 +0100368_- If they are NNWD then just don't map them on the LOAD, if they are mandatory then map them to some other data in the unloaded dataset, LOAD should then complete OK.

-----Original Message----- From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] Sent: 26 April 2001 15:31 To: DB2-L@RYCI.COM Subject: Re: Copying data from Version 4 to Version 5 [...] 19944 50 12_Re: RI Issue16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 26 Apr 2001 11:04:56 -0400562_iso-8859-1 The RI relationship is from one child table to one parent table. You can have several RI relationships from one child table to different parent tables. However, the restrictions are 'ANDed' rather than 'ORed'. i.e. the parent would need to exist in both A and B not just (A or B). You could do what you want if you put all your update/delete/insert logic into a Stored Procedure, did the update/delete/insert *ONLY* via the Stored Procedure, and coded your RI logic in the Stored Procedure rather than using DB2 RI. Sounds ugly, but should work. [...] 19995 178 47_Re: Large VSAM extended datasets/ SMS dataclass12_David Booher22_David.Booher@QUEST.COM31_Thu, 26 Apr 2001 08:10:39 -0700599_iso-8859-1 That's exactly what we ended up doing. I was hesitant to assign EA to ALL DB2 datasets, but there appears to be no "penalty" for doing so. I did not want to impose a naming standard on anyone creating LOB Tablespaces, even though the &DSN variable worked fine in the ACS routines to define the correct dataclass. So now, every DB2 database gets the same dataclass, thus they all get defined with Extended Addressibility. There was absolutely no combination of variables that would allow us to distinguish [at DB2 allocation time] between datasets requiring EA vs those that did not. [...] 20174 68 28_Re: DB2 on various platforms0_22_BILL_GALLAGHER@PHL.COM31_Thu, 26 Apr 2001 11:10:58 -0400626_us-ascii Rich,

My best answer to your question would be "it depends!". It really does depend on which environment (NT, AIX, OS/390, etc.) that you feel that your shop can best support, in terms of hardware, software, and scalability.

Case in point: we're in the process of implementing a software package from a vendor, it's currently the highest profile project within our shop. The vendors said that their DB2 database could reside on either UNIX of OS/390, though they were really more comfortable with the UNIX environment. However, we felt that we were much better prepared to support this database on [...] 20243 70 57_Re: [ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 26 Apr 2001 10:15:37 -0500425_iso-8859-1 Ujjal,

Before you take any corrective action, I recommend that you try to understand what happened. In particular, you should re-check the target and source OBIDs you specified in the SYSXLAT data set when you copied the table space. If they were not correct, I think this symptom could result. If this is so and you empty the index, the index and table will be consistent, but they may both be wrong. [...] 20314 109 44_Re: Copying data from Version 4 to Version 515_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 26 Apr 2001 10:20:51 -0500406_iso-8859-1 Phil,

But if the two new columns in the target space were added with SQL ALTER ADD, DSN1COPY could still work since DB2 will be expecting variable-length rows.

Michael Murley BMC

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Thursday, April 26, 2001 9:55 AM To: DB2-L@RYCI.COM Subject: Re: Copying data from Version 4 to Version 5 [...] 20424 100 44_Re: Copying data from Version 4 to Version 516_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Thu, 26 Apr 2001 11:18:30 -0400730_iso-8859-1 Ken, Does this means I cannot use FORMAT UNLOAD?

Mike Friedlander mike.friedlander@firstinvestors.com 732-855-2529

----- Original Message ----- From: "Grayson, Ken" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, April 26, 2001 11:02 AM Subject: Re: Copying data from Version 4 to Version 5



> If they are NNWD then just don't map them on the LOAD, if they are mandatory > then map them > to some other data in the unloaded dataset, LOAD should then complete OK. > > -----Original Message----- > From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] > Sent: 26 April 2001 15:31 > To: DB2-L@RYCI.COM > Subject: Re: Copying data [...] 20525 51 28_Re: DB2 on various platforms10_Raghu Kota21_raghukota@HOTMAIL.COM31_Thu, 26 Apr 2001 15:27:45 -0000638_- Hi Rich

For given choice I will go DB2 with AIX. I heard performance is good on AIX than os/390. Another factor is there is heavy maintenance cost for s/390 compare to AIX.



Good luck Raghu.



>From: "Sharpe, Richard" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: DB2 on various platforms >Date: Thu, 26 Apr 2001 10:24:28 -0400 > >Hi > > We are currently going through a fact finding project for our >migration from MSSQLserver V7 and V2000 to DB2 and was wondering what in >your honest opinions is the best platform and OS for the [...] 20577 244 47_Re: Large VSAM extended datasets/ SMS dataclass17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Thu, 26 Apr 2001 10:47:11 -0500313_iso-8859-1 Howdy all,

You folks might want to check APAR OW41955. If I read this correctly, the associated PTF WILL REQUIRE DSSIZE >4G for EA datasets.

The problem is that for EA datasets Media Manager uses CI access, but if the DSSIZE is < 4G then RBA access should be used and errors result. [...] 20822 111 44_Re: Copying data from Version 4 to Version 512_Grayson, Ken27_ken.grayson@CAPGEMINI.CO.UK31_Thu, 26 Apr 2001 16:52:51 +0100577_- Yes

-----Original Message----- From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] Sent: 26 April 2001 16:19 To: DB2-L@RYCI.COM Subject: Re: Copying data from Version 4 to Version 5



Ken, Does this means I cannot use FORMAT UNLOAD?

Mike Friedlander mike.friedlander@firstinvestors.com 732-855-2529

----- Original Message ----- From: "Grayson, Ken" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, April 26, 2001 11:02 AM Subject: Re: Copying data from Version 4 to Version 5 [...] 20934 16 57_Re: [ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 26 Apr 2001 11:50:21 -0400482_us-ascii Did you say that the tablespaces and indexes were stopped before doing the DSN1COPY? If not, pages in the bufferpool may not have been externalized and this could account for the mismatch between table and index.

Rick Creech

================================================ 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. 20951 128 44_Re: Copying data from Version 4 to Version 513_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 26 Apr 2001 10:56:22 -0500505_iso-8859-1 Michael,

It's been a while, but I remember being bit by dsn1copy when columns had been added with ALTER. It was my understanding as well that the structures must match identically, and if the target or source had columns that were added via ALTER (as opposed to DROP/CREATE), the other (target or source) must have the columns added the same way (as INSERT/UPDATE activity leads to some rows having the "new" length, while untouched rows remain the "old" length if ALTER was used). [...] 21080 58 26_v6 pre-compiler OS/390v2.910_Joan Green22_Joan.Green@BOIMAIL.COM31_Thu, 26 Apr 2001 17:08:57 +0100339_us-ascii Hi List,

Does anyone know if there was any change to the pre-compiler from v5 to v6? I'm getting COBOL errors on scope delimiters (END-IFs) that I don't believe we had before.

Let me elucidate: We have SQL ON WARNING /ON ERROR code at the top of our programs which the precompiler replaces with the following : [...] 21139 82 30_Re: v6 pre-compiler OS/390v2.915_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 11:21:49 -0500496_ISO-8859-1 Hi, Joan, END-IF is COBOL II syntax. I doubt that a conversion from DB2 V5 to V6 would affect it.

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

"We have met the enemy and he is us." - Walt Kelly "Pogo Comic Strip"



-----Original Message----- From: Joan Green [mailto:Joan.Green@BOIMAIL.COM] Sent: Thursday, April 26, 2001 11:09 AM To: DB2-L@RYCI.COM Subject: v6 pre-compiler OS/390v2.9 [...] 21222 78 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM31_Thu, 26 Apr 2001 17:39:49 +0100436_iso-8859-1 Carlos,

In SMS you can also stack many storage groups if you reached the limit

I don't remember the actual amount of volume that you can define in SMS storage group but I know that it has been increased regularly and this wouldn't be an issue anymore.

Philippe Herman Computer Associates Technical Consultant Tel: +32 (2) 773 28 43 Fax: +32 (2) 762 73 59 Mobile: +32 (496) 59 72 79 philippe.herman@ca.com 21301 49 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group13_Olson, Carlos14_COlson@QRS.COM31_Thu, 26 Apr 2001 09:42:52 -0700752_iso-8859-1 Lynda,

That's what I was hoping to hear. Thanks for the feedback.

Carlos Olson Database Administrator QRS Corporation email: colson@qrs.com http://www.qrs.com





-----Original Message----- From: Lynda Stone [mailto:Lynda.Stone@LIBERTYMUTUAL.COM] Sent: Thursday, April 26, 2001 7:52 AM To: DB2-L@RYCI.COM Subject: Re: max number of volumes in a DB2 stogroup vs SMS storage group



Carlos,

From my experience, I know that we have 136 volumes in our application production SMS pool currently and two DB2 stogroups (one for tablespaces, one for indexes, for reporting purposes). They have been defined using VOLUMES ('*'). Our table and index data is striped across all 136 volumes and [...] 21351 91 30_Re: v6 pre-compiler OS/390v2.912_Mark Granger22_the_grange@MBOX.COM.AU31_Thu, 26 Apr 2001 11:46:44 -0500526_us-ascii I don't know about the COBOL options, but the pre-compiler DEFINITELY changed from DB2 V5 to V6. I had problems with Assembler addressability due to some of the changes.

Mark Granger Independent Systems Integrators P/L Senior Technical Specialist - 2BDB2 Email: mgranger@isi.com.au Phone: +61 0416-012-262 (Australia) OR +1 617-306-7794 (USA)



----- Original Message ----- From: Joan Green Date: Thursday, April 26, 2001 11:08 am Subject: v6 pre-compiler OS/390v2.9 [...] 21443 37 51_Large VSAM extended datasets/ SMS dataclass - Reply15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 11:49:44 -0500546_ISO-8859-1 Hi, Ken, I have read APAR OW41955. We encountered a problem when we didn't have the PTF for this APAR applied. At least, I think it was this PTF. It has been a while since I worked on it. I can't remember the exact error code but I do remember that I couldn't allocate any DB2 tablespaces including any with DSSIZE greater than 4G. We were waiting for this PTF to be applied with our regular maintenance cycle so that we could continue. I haven't revisited the problem yet. When I was searching for this APAR I also found PQ46367 [...] 21481 46 12_Re: RI Issue12_Mckay, Wayne24_Wayne.Mckay@ATCOITEK.COM31_Thu, 26 Apr 2001 10:52:08 -0600291_- If your design is truly relational you will have multiple parents (often more than 2) for one child table. Sometimes the parent row must exist in both A and B parents such as in Associative type of relationships (in this case there usually are only 2 parents and both are mandatory). [...] 21528 44 19_Re: DB2 resource???10_Moden, Jim17_jim.moden@EDS.COM31_Thu, 26 Apr 2001 11:45:40 -0500332_iso-8859-1 Nam.

I would recommend you review the fourth of fifth edition of The DB2 Developer's GUIDE by Craig Mullins. The book is published by SAMS Publishing and is available from Amazon.com or BarnesandNoble.com at a discount of 10 to 20% off the retail price. The book also includes a CD you can load onto your PC. [...] 21573 24 26_RACF and Stored Procedures13_Davis, Ronald17_DavisRo@CONED.COM31_Thu, 26 Apr 2001 13:36:19 -0400575_iso-8859-1 We have a security scheme for DB2 Connect where the application (ASP) passes the userid and password to db2 connect. That userid is connected to a Secondary RACF Group that has execute on the packages. The Bind-id has table privileges. It works fine. When I try to set up stored procedures to follow that scheme, it doesn't work. The ASP passes the Userid and PW but never gets the association to the Secondary RACF group. I've tried placing the Secondary Racf group in the Authid column of sysprocedures. It still doesn't work. To get it to work I've had to [...] 21598 75 28_Re: DB2 on various platforms15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Thu, 26 Apr 2001 13:39:53 -0400358_iso-8859-1 Thanks

Raghu

I think we are leaning in that direction (AIX), I started off with mainframes back with the IBM S360's so they are no strangers to me and I have worked with UNIX for years and am comfortable with UNIX and the maintence cost like you say is very high on mainframes compared with AIX/UNIX, thanks for the suggestion. [...] 21674 92 28_Re: DB2 on various platforms15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Thu, 26 Apr 2001 13:42:15 -0400492_- Thanks Bill

I think we are more comfortable with AIX/UNIX, I have allot of mainframe, plus UNIX experience and feel that the AIX path is best for this company, they are a medium sized company and I think a mainframe would be too much for them.

Thanks very much again

Rich

-----Original Message----- From: BILL_GALLAGHER@PHL.COM [mailto:BILL_GALLAGHER@PHL.COM] Sent: Thursday, April 26, 2001 11:11 AM To: DB2-L@RYCI.COM Subject: Re: DB2 on various platforms [...] 21767 68 12_Re: RI Issue12_Kline, Wayne27_wayne.kline@INGRAMMICRO.COM31_Thu, 26 Apr 2001 11:01:29 -0700409_iso-8859-1 I think what Rakesh has in mind is the situation where a single FK domain contains some values that are in one parent table PK domain, and some in a different parent table PK domain. The FK is essentially the UNION of the two independent PK domains. This can work, I suppose, if you define separate FK's on the same column, but this could be very confusing from a number of different aspects. [...] 21836 43 19_Re: DB2 resource???21_Devineni, KishoreBabu29_KishoreBabu.Devineni@NIKE.COM31_Thu, 26 Apr 2001 11:08:55 -0700391_us-ascii There is a certification guide available and you can order from any one of the online book stores. For application development certification you need to pass two exams(I think 512 and 513). One is family fundamentals and the other is application development. There are sample tests available on the IBM website and also you can download a free self study course. Best of luck. [...] 21880 63 65_Creating a linked server with SQL Server 7 to access DB2/OS390 V512_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Thu, 26 Apr 2001 14:14:45 -0400562_iso-8859-1 Hello list,

I am trying to access DB2 on the mainframe from SQL Server using DB2 Connect V7 as a linked server, but I keep getting the following error:

Server: Msg 7302, Level 16, State 1, Line 1 Could not create an instance of OLE DB provider 'IBMDADB2'.

I have tried using the IBM OLE provider (but I have NO idea what to put in the Product Name, Data Source, and Provider string fields). I also tried using the regular Microsoft ODBC driver and specifying a DNS name, but I had no luck with that either. I assuming I have [...] 21944 19 23_Dynamic Statement Cache9_Birk, Tim17_BirkT@DIEBOLD.COM31_Thu, 26 Apr 2001 14:15:50 -0400543_iso-8859-1 DB2 5.1, OS/390 2.9

We have a CICS application that makes good use of dynamic SQL. The developer, probably correctly, feels that his application would benefit from dynamic statement cache. We have not turned on cache because we are a heavy QMF shop and were told that all of the QMF queries would flood the cache. The developer pointed out that one of the rules for cache is that you cannot use REOPT(VARS). He suggested that we rebind the QMF plan and packages with REOPT(VARS) to prevent them from flooding the cache. [...] 21964 19 58_Accessing VSAM from Stored procedures in WLM address space22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Thu, 26 Apr 2001 11:18:50 -0700332_iso-8859-1 Hello Gurus, I apologize for not being able to search the archives on this topic before asking this question . Has anybody attempted to access a VSAM via a Stored procedure (for read only) when it is already allocated to CICS? Can somebody enlighten me on the nuances and pit falls of doing this? Thanks Ramesh Bala [...] 21984 82 19_Re: DB2 resource???14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 26 Apr 2001 13:16:26 -0500353_iso-8859-1 Nam,

You are talking about the UNIX, Windows, etc. version of DB2 and there is a Certification Guide published specifically for that platform and V7, called "DB2 UDB V7.1 Certification Guide" for Unix, Linux and Windows by Baklarz and Wong.

The certification guide for DB2 V7 for OS/390 and z/OS will be published in June. [...] 22067 88 12_Re: RI Issue15_Steven Morrison33_steven.morrison@FIRSTDATACORP.COM31_Thu, 26 Apr 2001 13:51:03 -0500393_us-ascii You could have multiple primary keys one for each parent table. With 2 foreign keys on the child table. All but one of the foreign keys would have to be defined as delete set null. To say it another way if a foreign key exists for table A and table B (different fields A's foreign key field1 and b's foreign key field2) one of the foreign key needs to be define delete set null. [...] 22156 61 36_Performance of DB2/CICS applications22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 26 Apr 2001 16:07:47 -0300608_iso-8859-1 Dear list members,

We have got several CICS applications whose response time has been very stable during for many days. The average execution time is quite low, usually less than one second or even miliseconds. There are rare peaks when the execution time of some applications goes up to several seconds. Some contention happens but it is resolved by DB2. Our limit time for CICS applications is 30 seconds, that means if a CICS thread spends more than 30 seconds the user's request is lost. However, on some occasions something - still undetermined - happens and a CICS threads lasts [...] 22218 109 20_Re: CA/Platinum P99D9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 26 Apr 2001 13:21:56 -0600455_us-ascii I have installed P99D in 40+ subsystems both V5 and V6. If you are are having specific issues with the P99 SDM SMP/E install or any product tech support should be able to assist. I can also chat with you off line on the install. Getting hooked up to hiper notification and eSupport is critical. You will also find out about existing fixes and current release information. The next tape in the P99 series (P99E) should be coming out shortly. [...] 22328 77 27_Re: Dynamic Statement Cache34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Thu, 26 Apr 2001 15:33:30 -0400324_us-ascii I think they are confused with two separate things.

REOPT(VARS) is for static SQLs but access path is decided run time. Dynamic statement cache is chuck of memory in EDM pool and is used for the dynamic SQLs so that SQL with similar syntax need not be passed to the optimizer for access path selection. [...] 22406 40 62_Re: Accessing VSAM from Stored procedures in WLM address space13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Thu, 26 Apr 2001 14:40:41 -0500392_iso-8859-1 We aren't WLM, so I don't know what differences may apply....

We access VSAM data for read all the time on files that are also defined to CICS. We also have a recent case where we needed to update the data in a VSAM file, and we are accomplishing that by using a stored procedure an EXCI call. The stored procedure links to a CICS routine which does the actual update. [...] 22447 19 48_Should SAP be in a separate subsystem on OS/390?13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Thu, 26 Apr 2001 14:44:51 -0500544_iso-8859-1 I would like some information on the pros and cons of putting SAP R/3 data in separate DB2 subsystems on OS/390. We currently have a test and a production DB2 subsystem, and our consultant is suggesting that we need to add 3 more DB2 subsystems for 3 SAP environments (test, quality assurance, and production). However, he has never done an SAP-OS/390 installation before. (We will be DB2 V6 prior to the installation.) Since we are new in this SAP business (haven't even been to a class yes), any comments will be appreciated. [...] 22467 107 40_Re: Performance of DB2/CICS applications12_Clark, Kevin15_KClark@FDIC.GOV31_Thu, 26 Apr 2001 15:56:02 -0400701_- Claudio,

By execution time . You mean total elapsed time. How about CPU time.

Is the extra time WAIT or CPU ?







-----Original Message----- From: Claudio Meluzzi Mendes [SMTP:p000002@BEG.COM.BR] Sent: Thursday, April 26, 2001 3:08 PM To: DB2-L@RYCI.COM Subject: Performance of DB2/CICS applications

Dear list members,

We have got several CICS applications whose response time has been very stable during for many days. The average execution time is quite low, usually less than one second or even miliseconds. There are rare peaks when the execution time of some applications goes up to several seconds. Some contention happens but it is [...] 22575 91 40_Re: Performance of DB2/CICS applications15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 26 Apr 2001 15:02:20 -0500400_ISO-8859-1 Hi, Claudio,

This is just a shot in the dark but is there a possibility that an insert is being performed and the page DB2 wants to insert on is full and DB2 must format another page at the end of the tablespace? Or, if an update is being performed, the page must be relocated to a different page that must be formated? Do you have enough freespace defined in the tablespaces? [...] 22667 28 52_Re: Should SAP be in a separate subsystem on OS/390?10_Bob Riehle19_bob.riehle@WAMU.NET31_Thu, 26 Apr 2001 13:03:35 -0700577_us-ascii sound familiar?

Terry McCombs wrote:

> I would like some information on the pros and cons of putting SAP R/3 data > in separate DB2 subsystems on OS/390. We currently have a test and a > production DB2 subsystem, and our consultant is suggesting that we need to > add 3 more DB2 subsystems for 3 SAP environments (test, quality assurance, > and production). However, he has never done an SAP-OS/390 installation > before. (We will be DB2 V6 prior to the installation.) Since we are new in > this SAP business (haven't even been to a class yes), any [...] 22696 19 40_Re: Performance of DB2/CICS applications0_26_truman.g.brown@VERIZON.COM31_Thu, 26 Apr 2001 16:06:01 -0400521_us-ascii Claudio,

Many of your questions may be answered in the IBM Redbook, "CICS/ESA-DB2 Interface Guide", SG24-4536. You may have to tailor your RCT and other CICS resources to match the necessary levels of concurrency. Also, check any current CICS APARS.

George

================================================ 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. 22716 74 52_Re: Should SAP be in a separate subsystem on OS/390?0_20_John_Lendman@FPL.COM31_Thu, 26 Apr 2001 16:07:03 -0400578_us-ascii Terrry, it is SAP recommendation that you put each SAP system into it's own DB2 subsystem.

There are many reasons for this, I will list just a few here, I am sure you will get the own list

One, the number of dataset that you will have in SAP 4.5B release and more that likely getting large with newer releases is 27,000. Two, the names for all the database, tablespaces, tables, stogroups, and indexes are identical in every SAP system. Three. If you wish to do some type of "SNAPPING" and restoring you want everything including the DB2 Catalog and [...] 22791 14 42_the key things for monitoring DB2 on AS40013_Mike Goodrich23_mgoodrich28@HOTMAIL.COM31_Thu, 26 Apr 2001 14:57:25 -0500435_- Hi,

I am trying to find the documents about monitoring DB2 database on AS/400, I want to find the key things that I should monitor for it. Can anybody provide any information about it?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22806 83 52_Re: Should SAP be in a separate subsystem on OS/390?10_Bill Disch23_bill.disch@GENMILLS.COM31_Thu, 26 Apr 2001 15:23:43 -0500571_iso-8859-1 Terry,

I was the DB2 DBA for our SAP R/2 system using CICS and DB2 V3, V4, & V5 on OS/390 which were just migrated to SAP R/3 using oracle on HP_UX. From a database viewpoint, the structures are very similar from SAP's view - 15,000 tables + indexes. The difference is more pronounced at the database level where in R/2 10,000 SAP tables are just 'redefines' of a DB2 table defined with 2 colums, KEY CHAR(xx), and DATA CHAR(4000). The R/3 database creates the 10,000 SAP tables as unique database tables. Either way, you still have a large number [...] 22890 55 52_Re: Should SAP be in a separate subsystem on OS/390?9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Thu, 26 Apr 2001 14:22:34 -0600350_iso-8859-1 There are no options of sharing a DB2 subsystem with SAP. SAP => MUST <= run by itself in a DB2 subsystem because it has it's own operating system known as BASIS. SAP transactions have their own types of commit points blended in with the standard DB2 ones. SAP is a "stange beastie" that requires it's own standalone world to run in. [...] 22946 45 52_Re: Should SAP be in a separate subsystem on OS/390?14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Thu, 26 Apr 2001 15:17:11 -0500465_iso-8859-1 That's exactly what I did. I have a test and production legecy environment and when I put SAP in 2 years ago I created 3 separate DB2's (dev,tst, and prd). It works. I think that's the recommentation but some people have done it a little different.

-----Original Message----- From: Bob Riehle [mailto:bob.riehle@WAMU.NET] Sent: Thursday, April 26, 2001 3:04 PM To: DB2-L@RYCI.COM Subject: Re: Should SAP be in a separate subsystem on OS/390? [...] 22992 123 27_Re: Dynamic Statement Cache14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 26 Apr 2001 15:31:26 -0500523_iso-8859-1 FYI --

REOPT(VARS) can be used for dynamic SQL using host variables, parameter markers, or special registers, and reevaluates the access path based on the contents of the those variables.

Also, when REPORT(VARS) is used with dynamic SQL, it prevents the use of the SQL cache if turned on.

Regards, Richard

Richard Yevich, YL&A IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 Richard_Yevich@YLAssoc.com http://www.YLAssoc.com -- DB2 Performance Journal [...] 23116 28 52_Re: Should SAP be in a separate subsystem on OS/390?10_Bill Disch23_bill.disch@GENMILLS.COM31_Thu, 26 Apr 2001 15:42:55 -0500437_iso-8859-1 Terry,

I stand corrected. Based on what John and Don replied, I guess you have no choice. I was more familiar with the R/2 side which also uses the same DB/TS/TB/IX name everywhere, but we changed the CREATOR on the DB2 side and this was also a parameter in the SAP startup module so we could have multiple SAP systems in one DB2 subsystem. However, I bow to Don who is very right that SAP is a "strange beastie". [...] 23145 90 52_Re: Should SAP be in a separate subsystem on OS/390?0_20_John_Lendman@FPL.COM31_Thu, 26 Apr 2001 16:43:50 -0400482_us-ascii I agree with Don here, I think the main reason why you MUST is because SAP expects the owner of all the objects to SAPR3, and since all the database, tablespaces, tables and indexs have the same name, YOU CAN PUT THEM IN THE SAME SUBSYSTEM/

John







"Don Alden" cc: Sent by: "DB2 Subject: Re: Should SAP be in a separate Data Base subsystem on OS/390? Discussion List" [...] 23236 96 27_Re: Dynamic Statement Cache13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Thu, 26 Apr 2001 16:43:03 -0400613_US-ASCII Tim,

There are several considerations here. I think I've got this all right, but (of course!) you should refer to the appropriate manuals for confirmation. Of course, I welcome comments from the list to set me straight if I've missed or misstated something.

First, there are TWO caches. (1) The Local Dynamic Statement Cache is a separate area of memory in the DBM1 address space, not in the EDM Pool. It is allocated/used if ZParm CACHEDYN=YES and your plans/packages are bound with KEEPDYNAMIC(YES). If so, a 'user copy' of a prepared statement is stored here. Max size of this area [...] 23333 99 70_Re: Creating a linked server with SQL Server 7 to access DB2/OS39 0 V519_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Thu, 26 Apr 2001 13:56:59 -0700381_- We stumbled through this one a few days ago.

I don't have SQL Server in front of me to look at the panel, but from what I can remember: 1. We used the Microsoft OLE to ODBC driver 2. Configured the ODBC connection to the Database Alias used in DB2 Connect for all the fields which seemed to be required (location, database, ?). I remember filling it in three places. [...] 23433 173 44_Re: Copying data from Version 4 to Version 515_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 26 Apr 2001 16:10:51 -0500476_iso-8859-1 Dave,

First of let me say that I agree wholeheartedly that if you can do unload/reload, it's usually safer.

But if that's not an option - because of volume or whatever - a dsn1copy to a target that has had columns added with Alter should work, I think, if the source and target table spaces are otherwise defined the same. Dsn1copy after adding columns to the *source* space with Alter might not work if the target space was not also so altered. [...] 23607 26 27_Re: Dynamic Statement Cache0_17_JTonchick@AOL.COM29_Thu, 26 Apr 2001 17:10:49 EDT411_US-ASCII My dynamic SQL environment is almost entirely QMF users. There has been no measurable impact on production (static SQL) with this implementation. My stats show that even with QMF as the primary dynamic workload, 60% of the queries come from cache. It would probably be higher, but the weekly REORG & RUNSTATS of the QMF tablespaces forces a refresh of queries in the cache that use those objects. [...] 23634 19 52_Re: Should SAP be in a separate subsystem on OS/390?15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Thu, 26 Apr 2001 15:55:33 -0500350_us-ascii We run 6 seperate sub systems for SAP - - sandbox 1, sandbox 2, quality , developement, production and quality 2. We have the first for on one LPAR and Production and quality 2 on a seperate LPAR.

We have about 36 3390 mod 3 in dev, about 25 in sandbox 2 20 in sandbox 1 and about 135 in production and another 135 in quality 2. [...] 23654 162 29_Re: DB2 Connect and MS Access0_15_leon@CA.IBM.COM31_Thu, 26 Apr 2001 18:16:09 -0400542_us-ascii Sorry I am a bit slow on the reply.

I don't think I completely understand what the problem really is. If what you re doing is attaching a DB2 table to an MS Access database and then just double-clicking to open the table then what you are asking MS Access to do is return all rows in the table. There is no way to limit this because you specifically asked to return all rows. If, on the other hand, you created a MS Access query that references your attached DB2 table then using the instructions I provided you can tell [...] 23817 23 39_Table id from History (UDB/AIX)..URGENT8_bharat r20_bharat_321@YAHOO.COM31_Thu, 26 Apr 2001 15:24:30 -0700549_us-ascii Hi all I am not able to find the table id. for the dropped table in the HISTORY file. Pls let me know where I can get the same

Bharat

__________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 23841 87 44_Re: Copying data from Version 4 to Version 515_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 27 Apr 2001 08:49:05 +1000534_- Mike,

If the extra columns on the receiving table allow nulls (or have a default value), then you're in the clear; ignore them and they'll get either a null value or their default. If not, you could use BMC's LoadPlus utility to load a literal into those columns. I've done it myself to create part of the partitioning key, splitting the data across arbitrarily assigned partition numbers when the number wasn't part of the data being loaded. Alternatively, you might have to massage the input files to provide a value. [...] 23929 116 44_Re: Copying data from Version 4 to Version 516_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Thu, 26 Apr 2001 18:56:27 -0400507_iso-8859-1 Raymond, Thanks for the info. I've now successfully populated several tables with the methods specified in all the helpful posts. Thanks to you and all those who also responded.

Mike Friedlander mike.friedlander@firstinvestors.com 732-855-2529

----- Original Message ----- From: "Bell, Raymond W" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, April 26, 2001 06:49 PM Subject: Re: Copying data from Version 4 to Version 5 [...] 24046 94 47_Re: Large VSAM extended datasets/ SMS dataclass15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 27 Apr 2001 09:00:34 +1000455_- Just as well you did hit 'send' again; you've pricked my conscience about not answering the original.

Not that I can help much, Dave, but the only EA-enabled TSs we have are controlled by their names. We have separate SMS dataclass specs for these suckers that look for objects of a certain name in a certain database. Works for us as we've only got, um, three TSs across three subsystems. I can see this getting really messy with any more. [...] 24141 42 12_Re: RI Issue15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 27 Apr 2001 09:18:11 +1000352_- Dear -Me,

Yes, you can have two parent tables for one child table. But, unlike real life when the existence of two parents is simply recommended, not the rule (Warning! Warning! Morality Alert!) the child row must have an owning parent row in each parent table.

Hope this helps,



-Me (man, this is getting confusing) [...] 24184 77 5_SPT0110_Wang.James24_Wang.James@AAA-CALIF.COM31_Thu, 26 Apr 2001 16:44:40 -0700373_us-ascii Have anyone experienced this before?

After upgraded DB2 from V5.1 to V6, we see a tremendous growth in size for DSNDB01.SPT01 during rebind of DB2 packages.

Any information is greatly appreciated.

James Wang ESD-Technical Services Automobile Club of Southern California (714) 850-2851 Wang.James@aaa-calif.com 24262 51 57_Re: [ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT0_24_ujjwal@LOT.TATASTEEL.COM31_Fri, 27 Apr 2001 08:25:45 +0530507_us-ascii Hi Charles, The Tablespaces was in read only mode before running DSNICOPY and I have also run Quiesce Utility with WRITE(YES) option so that all data and index pages get Externalized before running DSN1COPY. I have recovered all the indexes also but still it is showing the error in CHECK INDEX that the row does not have a matching index and key does not have a matching data. I think the alternative would be to run DSNICOPY on the indexes too. Lemme try that and I will let u know. Thanx . [...] 24314 21 8_RI Issue12_Rakesh Singh14_rasun@JUNO.COM31_Thu, 26 Apr 2001 21:54:05 -0700302_- Hi Folks, Thanks for the reply I received and some of them make sense :-0 ) May be i did not phrase my question right I was intersted in knowing whether DB2 RI allows a OR condition. For example . The foreign key in Table C can exist as primary key in either Table A or Table B but not in both. [...] 24336 108 30_Re: Cost of additional indexes23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 27 Apr 2001 07:17:13 +0100576_iso-8859-1 Sanjeev (et al)

some excellent food for thought (liked the numbers from John and Toine).

one other thing though...modifying catalog tables is an whole other ball game ... surely.

I have done it, do it and would do it again but I have seen things go wrong when somebody ran a runstats and they should'nt .. i.e humans make mistakes and you can't bolt down the catalog once you have changed it. Anyway I was just wanting the junior members amoungst us to BEWARE of modifying the catalog .. and while I'm here .. make sure you think long and [...] 24445 51 43_creating threads in java stored procedures.11_Arvind Heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET31_Fri, 27 Apr 2001 13:04:41 -0400175_iso-8859-1 hi list, can somebody help me in creating threads thru stored procedures and to keep them alive even after stored procedure terminates.

thanks arvind

24497 73 52_Re: Should SAP be in a separate subsystem on OS/390?0_18_mebert@AMADEUS.NET31_Fri, 27 Apr 2001 09:41:23 +0200549_us-ascii For goodness' sake DON'T skip the imagecopies. You don't want to restore the entire SAP subsystem to a prior point in time just because one TS has an I/O error. For recovery to current, ICs are the way. Run several job streams in parallel, then they complete in a couple of hours; use SHRLEVEL CHANGE so you don't need to restrict access. Most of the TSs will be tiny anyway. We do it daily for 4 SAP subsystems. No problem (we also take weekly full volume dumps). Note that DB2 RECOVER is designed to protect you from HW failures. It [...] 24571 27 45_MS Access olny moving 510 characters - Urgent11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Fri, 27 Apr 2001 08:43:35 +0100388_- All,

We have a problem moving data from SQL Server to DB2 V6 on OS/390 and are desperately looking for some assistance. We have a column on SQL Server V7 defined as IMAGE size 16 going to a column in DB2 of BLOB(1Mb). We import the data into MS Access from SQL Server and then do a link table to DB2 through DB2 Connect and perform a SELECT * FROM ..... into the DB2 table. [...] 24599 133 20_Re: CA/Platinum P99D18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Fri, 27 Apr 2001 06:31:07 -0400710_iso-8859-1 Been there - done that....

thanks....

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



-----Original Message----- From: Rob Crane [mailto:racrane@CONCENTRIC.NET] Sent: Thursday, April 26, 2001 3:22 PM To: DB2-L@RYCI.COM Subject: Re: CA/Platinum P99D



I have installed P99D in 40+ subsystems both V5 and V6. If you are are having specific issues with the P99 SDM SMP/E install or any product tech support should be able to assist. I can also chat with you off line on the install. Getting hooked up to hiper notification and eSupport is critical. You will also find out [...] 24733 68 15_from IMS to DB217_Gupta, Ajay Kumar24_Ajay.Gupta@HPSGLOBAL.COM31_Fri, 27 Apr 2001 15:30:34 +0500314_- Hi List !

We have programs which are using IMS database which we are planning to change to DB2 . Can you pls suggest as to what extent the performance of these programs may improve in moving from IMS to DB2 databse. warm regards,ajay ajay k gupta , 91-118-4588604 (direct) 91-118-4581236..41 : extn : 3270 24802 128 19_Re: from IMS to DB216_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 27 Apr 2001 11:52:47 +0100321_us-ascii





Ehm ouch arghhhhh,

Ajay,

I guess without knowing more details about the data, i.e. what kind of use you have for it, what kind of DS you use in DL/I, if the stuff's still going to be used by IMS TS, etc., it's basically not possible to give an answer to that question. [...] 24931 196 20_Re: CA/Platinum P99D16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 27 Apr 2001 11:59:42 +0100311_us-ascii oh no please we're not off on the "SLAG CA UNTIL ALL SENIOR VICE-PRESIDENTS WRITE TO DB2-L AND TO ALL WHO WERE MENTIONED IN THE THREAD ONE-BY-ONE" wagon again, are we....

:-8 last time I got 4 phone calls from CA people throughout the world :-(

(just a friday whinge, by the way)... [...] 25128 35 40_Re: Performance of DB2/CICS applications22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Fri, 27 Apr 2001 08:10:27 -0300427_iso-8859-1 Kevin,

By execution time I mean total elapsed time. Our CPU times are always low. I usually associate high CPU time with bad SQL but this is not the case. The extra time is spent on locking. Unfortunately, I only have got accounting data to analyse. Capuring detailed locking information could help us but the problems occur so rarely that the amount of data I would need to keep and analyse is too big. [...] 25164 215 19_Re: from IMS to DB216_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 27 Apr 2001 12:18:55 +0100389_us-ascii





Forwarding the responses from IMS-L, where lots of people work with both IMS and DB" and btw are much more knowledgeable than little ol' me...

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...] 25380 232 20_Re: CA/Platinum P99D18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Fri, 27 Apr 2001 07:24:16 -0400329_iso-8859-1 Nope, I ain't bashin anyone, just stating the problems we are having with P99D and would like to get them resolved sometime before I plan on retiring. I really don't want to make a career out of trying to upgrade to P99D. Frustration levels are high but I guess that's to be expected with any of these DB2 tools. [...] 25613 33 24_Re: DSN1COPY Performance14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 27 Apr 2001 06:13:51 -0500560_- http://bama.ua.edu/cgi-bin/wa?A2=ind0012&L=ibm-main&P=R52215 suggests that anything much more than BUFND=72 won't provide much performance inprovement. However, as that reference makes plain the appropriate number does depend on the caching available on your disk system.

James Campbell



On Thu, 26 Apr 2001 11:08:06 +0200, Toine Michielse wrote:

>Hello Charles, > >Worth a shot.... Try adding AMP=('BUFND=180')... > >//SYSUT1 DD DISP=SHR,AMP=('BUFND=180'), >// DSN= > >Regards, > >Toine Michielse > > > [...] 25647 129 30_Re: v6 pre-compiler OS/390v2.916_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 27 Apr 2001 12:29:28 +0100347_us-ascii Joan,

the scope delimiters are part of the COBOL language structure, nothing to do with DB2 and/or the precompiler. if you need further info, look in the COBOL for MSV & VM books, the COBOL LANGUAGE REFERENCE first of all (I assume this is your current flavour of COBOL, since COBOL II went out of maintenance in April 1998). [...] 25777 225 16_FW: SQLCODE -41914_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 27 Apr 2001 12:33:57 +0100445_iso-8859-1 -----Original Message----- From: Tim.T.Dupree@Lowes.Com [mailto:Tim.T.Dupree@Lowes.Com] Sent: 27 April 2001 11:32 To: Grainger, Phil Subject: RE: SQLCODE -419



Georg,

The are a couple of PTFs made available on 4/5/01 that are suppose to resolve numerous -419 errors. Check out UQ52503 and UQ52502 (don't forget the MINDVSCL=6 ZPARM update). I had to apply these to support our DEC31 PeopleSoft environments. [...] 26003 94 19_Re: from IMS to DB216_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 27 Apr 2001 12:34:52 +0100326_us-ascii Dougie,

it was my post on IMS-L of something which was sent by someone on DB2-L, where I am in turn re-posting the stuff I get on this list. just being a busybody, basically (btw I am forwarding this on DB2-L as well) ;-)

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...] 26098 27 10_IFCID 006213_Tim K. Larsen23_tim-kent.larsen@UBS.COM31_Fri, 27 Apr 2001 13:47:42 +0200598_US-ASCII Hi there! Can anyone give me a ballpark-figure on the overhead of running a permanent trace on IFCID 0062 (for DDL audit and documentation purposes) ?

Thanks in advance Tim

_________________ IFCID 0062 ____________________________ (RMID 22) DB2 PERFORMANCE RECORD. THIS IFCID RECORDS THE START OF EXECUTION OF A STATEMENT WHEN THIS EXECUTION ENDS, IFCID 0058 IS WRITTEN. THIS RECORD IS ONLY WRITTEN AT THE APPLICATION SERVER WHEN THE DRDA PROTOCOL IS USED. THIS RECORD IS WRITTEN WHEN PERFORMANCE TRACE CLASS 3 IS ON. MONITOR1 PRIVILEGE IS REQUIRED FOR READING VIA IFI. [...] 26126 115 47_Re: Large VSAM extended datasets/ SMS dataclass15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 06:49:53 -0500403_ISO-8859-1 Well, we haven't actually allocated any EA-enabled data sets yet but that was what it was looking like. I do have to experiment with this though because I know that we will need to allocate some in the near future. Thanks for the warning. I am gathering information about these things as I see it come up so that when I finally get a chance to work on it I have something to start with. [...] 26242 211 19_Re: from IMS to DB213_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 27 Apr 2001 06:47:45 -0500598_us-ascii Two completely different mindsets:

One record processing, one set processing. Unfortunately many convert VSAM or IMS calls straight to DB2, and benefit is none or reverse performance.

DB2 can join tables, summarize, group, order and manipulate data.........and is responsive to adhoc user access.......

Terry Purcell Yevich Lawson & Assoc www.ylassoc.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Aurora Dell'Anno Sent: Friday, April 27, 2001 6:19 AM To: DB2-L@RYCI.COM Subject: Re: from IMS to DB2 [...] 26454 37 12_Re: RI Issue14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 27 Apr 2001 06:31:49 -0500372_- DB2 RI will not do what you want.

In DB2 V6, you could write a pair of BEFORE INSERT/UPDATE triggers on Table C that CALL a stored procedure that verifies your requirement and issues a failing SQLSTATE if it is violated.

You would also need INSERT/DELETE/UPDATE triggers on Tables A and B to ensure that those actions don't violate your requirements. [...] 26492 64 40_Re: Performance of DB2/CICS applications13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 27 Apr 2001 06:58:36 -0500468_iso-8859-1 Claudio,

If I recall, you said the problem related to insert and update lock waits (I have no knowledge of the CICS RCT tuning so will just talk SQL).

Are inserts using an application coded next number key generation system? Inserts should not wait for locks, unless there is a duplicate in a unique index which is currently uncommitted and the process must wait to see if it is committed or rolled back, unless there is lock escalation? [...] 26557 72 19_Re: from IMS to DB214_Richard Arnold24_Richard.Arnold@CHASE.COM31_Fri, 27 Apr 2001 07:05:25 -0500401_iso-8859-1 Gupta,

A well tuned and well designed IMS application will out perform just about anything.

If you application is reasonably well tuned, you probably won't see any performance improvement with DB2, and you can very possibly see a performance degradation.

Don't get me wrong; there are reasons to convert from IMS to DB2. Performance just isn't really one of them. [...] 26630 72 47_Re: Large VSAM extended datasets/ SMS dataclass14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 27 Apr 2001 06:53:25 -0500441_- Why can't you code the ACS routine to base the EA/non-EA decision on either - the HLQ, ie EA spaces use a different STOGROUP which has an appropriate VCAT name; or - (and this is probably unworkable in practice) reserve a particular character (eg "$") or characters as the eight character of the space name. Now, when some "unfortunate" uses a space name longer than 8 characters, which is mangled into a name which ends in "$" ... ? [...] 26703 40 27_host vars missing the colon10_Joan Green22_Joan.Green@BOIMAIL.COM31_Fri, 27 Apr 2001 13:18:26 +0100701_us-ascii Hi List,

Sorry to re-hash this subject, but maybe I missed a piece of the conversation.

If we don't fix our DBRMs, migrate to version 6 and the DBRM is subsequently rebound (and is missing colons) will this cause a problem?

Many thanks,

Joan Green





******************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately at MailManager@psir.ie and delete this E-mail from your system. Thank you. It is possible for data [...] 26744 178 47_Re: Large VSAM extended datasets/ SMS dataclass12_David Booher22_David.Booher@QUEST.COM31_Fri, 27 Apr 2001 05:16:28 -0700578_iso-8859-1 Thanks to all you took the time to answer this post.

In reviewing what Kenneth said about the APAR, I was reluctant to continue assigning EA to all DB2 datasets, which leaves me to resort to using some kind of naming convention using &DSN(4). Since we are a development shop, I have several Application Developers that will attempt to create these LOB Tablespaces in their testing. They want the flexibility to name it what ever they want. I fail to see how I can make this a win-win situation, because I don't want to have to manipulate the ACS routines [...] 26923 103 47_Re: Large VSAM extended datasets/ SMS dataclass15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 07:26:04 -0500473_iso-8859-1 You can do that but I will need to throw chocolate and doughnuts at the storage administrators because they don't like to add any more to the ACS routines than they have to. Also, some of our tablespaces that will need to be EA-enabled belong to OAM so I don't have a choice with names. I could change the HLQ to something specific to these but they are already established and it would be a big upheaval for all parties involved such as the billing folks. [...] 27027 39 19_Re: from IMS to DB215_Lankester, Andy22_Andy_Lankester@BMC.COM31_Fri, 27 Apr 2001 07:27:17 -0500526_iso-8859-1 Well - think of DB2 as implementing each IMS segment type as a root only HIDAM database with all pointers (physical and logical) as symbolic keys (except, obviously, for the index to data pointers). And with field level sensitivity turned on for EVERY field (not just those defined in the DBD). Looked at from this point of view it's amazing that DB2 performs at all! But the buffer manager in DB2 is the star and helps reduce the costs. As others have said, don't make the conversion for performance reasons. [...] 27067 69 31_Re: host vars missing the colon0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Fri, 27 Apr 2001 08:32:46 -0400441_us-ascii Joan,

If you rebind without making any change, your bind will fail with SQLCODE -104. Your plan will be invalidated and you will not be able to execute the plan until you fix the program (happened to us on a couple of occasions).

R/...Ed







Joan Green @RYCI.COM> on 04/27/2001 08:18:26 AM

Please respond to DB2 Data Base Discussion List [...] 27137 118 14_text extenders8_ian.long22_IAN.LONG@GRATTAN.CO.UK31_Fri, 27 Apr 2001 13:17:07 +0100324_iso-8859-1 Hi I'm having problems with text extenders the sql below is a modified version of the sample supplied but when I sub it I get



SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818 but only if I run it on AIX . If run from command centre it works ok. [...] 27256 58 31_Re: host vars missing the colon34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Fri, 27 Apr 2001 08:44:00 -0400594_us-ascii Yes they will. If there is missing colon (except for Descriptor) then the REBIND will fail.

-Venkat Pillay





> -----Original Message----- > From: Joan Green [SMTP:Joan.Green@BOIMAIL.COM] > Sent: Friday, April 27, 2001 8:18 AM > To: DB2-L@RYCI.COM > Subject: host vars missing the colon > > Hi List, > > Sorry to re-hash this subject, but maybe I missed a piece of the > conversation. > > If we don't fix our DBRMs, migrate to version 6 and the DBRM is > subsequently rebound (and is missing colons) will this cause a problem? > > Many thanks, > > Joan [...] 27315 94 52_Re: Should SAP be in a separate subsystem on OS/390?14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Fri, 27 Apr 2001 07:48:11 -0500423_iso-8859-1 This is a good point. I use the BMC copyplus and Recovery Manager and these products to a lot automatically to copy/recover the SAP system. Having each SAP system in it's own DB2 is the only way to go.

-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: Friday, April 27, 2001 2:41 AM To: DB2-L@RYCI.COM Subject: Re: Should SAP be in a separate subsystem on OS/390? [...] 27410 86 47_Re: Large VSAM extended datasets/ SMS dataclass12_David Booher22_David.Booher@QUEST.COM31_Fri, 27 Apr 2001 05:51:08 -0700399_iso-8859-1 James, We must have thought of that at the same time! It works, I just tested it. A new usercat and an alias will save me from having to adjust the ACS routines all the time. Dave

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Friday, April 27, 2001 6:53 AM To: DB2-L@RYCI.COM Subject: Re: Large VSAM extended datasets/ SMS dataclass [...] 27497 30 43_DSN3@SGN setup for use of secondary authids15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 08:03:49 -0500536_iso-8859-1 We have run into a problem here where we need to modify the DSN3@SGN exit so that our users of stored procedure builder can use the owner option in their binds to specify a secondary authid. See APAR II12801. My colleagues must put on their Assembler Helmets to do this. This is a frightening prospect for them and for the rest of the world. Does anyone out there have any code that they are willing to share? We need something that we can modify as opposed to coding from scratch. The world will be a much safer place. [...] 27528 33 48_Re: CA/Platinum P99D - No Bashing - reality/fact5_chris22_hhardy1@HOUSTON.RR.COM31_Fri, 27 Apr 2001 08:13:54 -0500588_iso-8859-1 Every vendor has issues and problems.... What's of concern is when its the exact same process procedure that has repeated errors.... Simple upgrade P99C-P99D this is not rocket space ship stuff.... Very simple process, but its not. Why is that????

Most folks prior to feeding this list (thread) for support actually do seek assistance /help from the perspective vendor they are contracted under.... When response, support and issues from the vendor jeopardize their performance for delivery most of us feel some level of comfort coming to this arena for "HELP".... [...] 27562 55 52_Re: Should SAP be in a separate subsystem on OS/390?13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Fri, 27 Apr 2001 08:23:38 -0500551_iso-8859-1 Thanks to all who replied to my original question.

I understand that each SAP environment needs to have its own DB2 subsystem. If I could be a little more specific, I am more interested in knowing the pros and cons of putting SAP R/3 data in the same subsystem as non-SAP R/3 data, although from the answers I got, it sounds like that may not be a good idea. Is anyone doing it? If so, would you do it again? I had searched the archives first, and didn't find anything specific to SAP, although the concensus seemed to be about [...] 27618 84 19_Re: from IMS to DB215_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 27 Apr 2001 09:31:52 -0400541_iso-8859-1 I know the general rule of thumb is that it will take longer in DB2, but I can say from experience that, if the time is taken to properly design the application for DB2, the fruits of your labor will be well worth it. Take a fresh look at your data and normalize/denormalize where it makes sense. Don't just turn IMS tables into DB2 tables. Don't just turn IMS calls into DB2 calls, make use of DB2's relational power and let the SQL do the work for you. If you're doing massive inserts, consider using a load. If reading an [...] 27703 169 18_Re: text extenders10_Mark Savin31_Mark.Savin@SCOOT-TECHNOLOGY.COM31_Fri, 27 Apr 2001 14:31:02 +0100318_iso-8859-1 Hiya,

It seems odd that it works via the GUI but not a text file. However, it could be due to using a 'stemmed form of' predicate in the search result function - it's the only thing in the query that you don't in advance know the datatype of. You could use a cast function in your where clause. [...] 27873 59 48_Re: CA/Platinum P99D - No Bashing - reality/fact18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Fri, 27 Apr 2001 09:36:34 -0400589_iso-8859-1 Couldn't have said it better myself...I think that pretty much nips the bashing right in the bud... Anyways, to everyone that responded to my original question(s) on P99D I 'thank-you' for your time and information.

--Steve....

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



-----Original Message----- From: chris [mailto:hhardy1@HOUSTON.RR.COM] Sent: Friday, April 27, 2001 9:14 AM To: DB2-L@RYCI.COM Subject: Re: CA/Platinum P99D - No Bashing - reality/fact [...] 27933 83 48_Re: CA/Platinum P99D - No Bashing - reality/fact16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 27 Apr 2001 09:48:14 -0400472_iso-8859-1 Steve, Chris: Exactly. This is why we are seeking to remove CA/Platinum utilities from our shop before the next contract renewal. Unfortunately, the renewal is quite far away.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Whittaker, Stephen [mailto:stephen.whittaker@PGNMAIL.COM] Sent: Friday, April 27, 2001 9:37 AM To: DB2-L@RYCI.COM Subject: Re: CA/Platinum P99D - No Bashing - reality/fact [...] 28017 27 28_problems with Authorizations20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Fri, 27 Apr 2001 15:44:23 +0200559_- Hi everyone, I know it could be quite simple and not interesting question, but I've a problem understanding the right authorization to give to some user, and so any advice is really welcome. I'm working on a AIX 4.3.3 system with UDB 7.1 . There is a user who sohuld be able to drop tables and tablespaces in a database, I gave him full DBADM authorization but still he got the ont authorized user during drop tablespaces. I'm trying to study the admin guide but I couldn't really found a point where it's stated the right authorization nedeed to drop [...] 28045 73 40_Re: Performance of DB2/CICS applications22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Fri, 27 Apr 2001 11:00:35 -0300603_iso-8859-1 Hi Linda,

It seems that your shot in the dark might have hit something. The tables involved in locking are from the same database and they have got default values for both freespace and pctfree (0 and 5, respectively). Those tables contain cheque information and their size is less than 4 million rows. Up to 50 cheques can be inserted in a single unit of work. There is a sequential unique identifier for each row and tables are segmented and clustered by that identifier. In theory, this would imply some contention in a concurrent environment with applications consisting of a [...] 28119 55 30_Re: Cost of additional indexes0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 27 Apr 2001 09:16:19 -0500465_us-ascii and while I'm here .. make sure you think long and hard when using OPTIMIZER HINTs.

OK, I'll bite, why are you concerned about using Optimizer Hints? Do you have a horror story, or an 'oops!' story, about using Hints? I haven't had an opportunity to use them yet, but there have been occassions when I would have loved to be able to hint, rather than jumping thru hoops trying to beat and trick the optimizer into submission. [...] 28175 106 40_Re: Performance of DB2/CICS applications15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 09:17:15 -0500566_iso-8859-1 Hi, Claudio, I don't have any numbers but I would expect that the impact is directly related to the number of pages that need to be formated. There is a tip from the former Platinum web site that I have passed on quite often to anyone having these problems. You can find it at www.platinum.com/dbtips/tps_os05.htm. The amount of freespace that you allocate in a tablespace must factor in the size of each record and the growth rate. If the records are very small then I use PCTFREE to allocate the freespace. If the records are very large (only 1 or [...] 28282 114 28_Re: DB2 on various platforms11_John Curran20_John.Curran@DRKW.COM31_Fri, 27 Apr 2001 12:45:22 +0100494_iso-8859-1 Hi

To say that AIX is cheaper than OS/390 is not always strictly true.

As usual it depends !!!

On how large and complex your system is going to be.

There is an interesting white paper which looks at costs of Unix vs. OS/390.



http://www-1.ibm.com/servers/eserver/zseries/library/whitepapers/gemini.html

If you have the time is may be worth reading it may be of some interest to you. If you have no time just read the conclusions [...] 28397 125 19_Re: from IMS to DB216_Peddycoart, Judy22_peddycoa@UILLINOIS.EDU31_Fri, 27 Apr 2001 09:21:49 -0500566_iso-8859-1 Hi Ajay,

Having converted three legacy systems from IMS to DB2 a few years ago, I think I can safely say "it depends". To give you a little background, we did not do any retooling of the systems; it was a straight segment to table transformation. I know; I've heard all the warnings that that is one of the worst things you can do! We would have liked to redesign, but the budget didn't allow for that. Two of the systems were written inhouse utilizing COBOL and CICS during the eighties, and the third was a heavily customized vendor package [...] 28523 44 47_Re: DSN3@SGN setup for use of secondary authids15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 09:22:01 -0500561_ISO-8859-1 Thanks to Bill Disch for his exits. I forgot to mention that we are in the process of migrating from ACF2 to RACF though. If anyone has exits that use RACF and wants to share, please do.

Linda

> -----Original Message----- > From: Billings, Linda > Sent: Friday, April 27, 2001 8:04 AM > To: 'DB2-L@RYCI.COM' > Subject: DSN3@SGN setup for use of secondary authids > > We have run into a problem here where we need to modify the DSN3@SGN exit > so that our users of stored procedure builder can use the owner option in > their binds [...] 28568 121 17_FW: Response time13_Morrill, John12_JohnM@VP.NET31_Fri, 27 Apr 2001 08:41:06 -0600622_- -----Original Message----- From: john king [mailto:john_king@rediffmail.com] Sent: Friday, April 27, 2001 7:10 AM To: DB2-L-REQUEST@RYCI.COM. Subject: Response time



Hi All I m new to db2 dba. I m giving thanks in advance to every mebers of the list to for their replies. Kindly tell the areas to be more stressed.

My client has asked me to make the system performance time to 2 seconds. The application is in OS390 db2. The application is having 35 tablespaces and even billions of records in few tables. Please tell me what are the factors i should first consider for doing analysis. From my [...] 28690 41 32_Re: problems with Authorizations11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Fri, 27 Apr 2001 15:38:36 +0100337_us-ascii Fabrizio

What error message is being generated? The user will not be able to drop either temporary or system tablespaces, nor will they be able to drop a tablespace if it contains a table that has part of its structure held in a different tablespace. In this case, the table will need dropping first. HTH Dave Hayes [...] 28732 67 22_Load with DATE problem16_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM31_Fri, 27 Apr 2001 10:45:22 -0400425_iso-8859-1 OK folks, me again. I thought I was done with this, should have known better. I am loading tables in v5 with unloaded data from v4 and have a problem with DATE data. The unload produces a 4 byte field containing the date info. The load seems to be expecting a formatted date (10 bytes) for input. I cannot set the DATE EXTERNAL(4) as the value is too small. Any help of thoughts would be appreciated. Thanks, [...] 28800 45 32_Re: problems with Authorizations15_Christopher Tee24_chris.tee@TOTALISE.CO.UK31_Fri, 27 Apr 2001 15:58:07 +0100623_ISO-8859-1 Fabrizio,

To drop a tablespace, SYSADM or SYSCTRL is required.

Chris

>===== Original Message From DB2 Data Base Discussion List ===== >Hi everyone, >I know it could be quite simple and not interesting question, but I've a >problem understanding the right authorization to give to some user, and so >any advice is really welcome. >I'm working on a AIX 4.3.3 system with UDB 7.1 . >There is a user who sohuld be able to drop tables and tablespaces in a >database, I gave him full DBADM authorization but still he got the ont >authorized user during drop tablespaces. I'm [...] 28846 140 48_Re: CA/Platinum P99D - No Bashing - reality/fact24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Fri, 27 Apr 2001 08:08:57 -0700496_iso-8859-1 Hi Guys!

Given that you've already purchased your current suite of tools, and the under DB2 version 7, you will have to purchase an alternative suite of tools, how do you plan of justifying the cost of the new tools? At the present time, your annual expense consists of maintenance an upgrade fees. If you switch vendors, you're likely to have an additional significant one-time purchase fee for tools which may not provide any significant benefit over your current suite. [...] 28987 25 46_Re: problems with Authorizations -missing info20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Fri, 27 Apr 2001 17:09:31 +0200557_- Hello and sorry, I think I missed to explain some fundamental part of the problems. Unfortunaely I don't have the error code because the user explined me tihs by telephone. The problem is he can't drop REGULAR tablespace. These tablespaces were first created by me using the Instance owner user id (I didn't have any other user id on the system), then once this user finished is fast training on UDB I gave him the DBADM auth hinking it could be enough for administering the DB. But when he tried to drop the regular tablespaces I had created he got [...] 29013 25 33_Looking for a Datacomm listserver11_Carl Nelson22_Carl.Nelson@ANTHEM.COM31_Fri, 27 Apr 2001 10:21:41 -0500614_us-ascii Anyone know of Datacomm listserve equivalent to this listserve. We have a possible Datacomm to DB2 conversion comming up but we need to ask some Datacomm capacity questions first.

Thanks Carl Nelson



CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. [...] 29039 62 37_Re: Looking for a Datacomm listserver12_Mark Granger22_the_grange@MBOX.COM.AU31_Fri, 27 Apr 2001 10:44:14 -0500530_us-ascii Carl, The list is called CADRE-L . LISTSERV@LISTSERV.ALBANY.EDU to signup (usual thing SUBSCRIBE CADRE- L Carl Nelson) , messages go to CADRE-L@LISTSERV.ALBANY.EDU .For a list of all (?) public listservers http://www.lsoft.com/lists/listref.html . The 2BDB2 product I work on is a Datacom to DB2 transparency tool, if your interested !

Mark Granger Independent Systems Integrators P/L Senior Technical Specialist - 2BDB2 Email: mgranger@isi.com.au Phone: +61 0416-012-262 (Australia) OR +1 617-306-7794 (USA) [...] 29102 90 22_Re: DB2 Benchmark Data12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 27 Apr 2001 10:27:51 -0500710_- Here are some pointers I put together yesterday for another question. redbooks http://www.redbooks.ibm.com./ Put DB2 or book number in search criteria, click Go. There are about 230 listed there now for DB2. Here are some on performance & availability DB2 UDB for OS/390 Version 6 Performance Topics, SG24-5351-00 DB2 UDB for OS/390 and Continuous Availability, SG24-5486-00 DB2 for OS/390 and Data Compression, SG24-5261-00 Storage Management with DB2 for OS/390, SG24-5462-00 DB2 for OS/390 Capacity Planning, SG24-2244-00 DB2 for OS/390 Application Design Guidelines for High Performance, SG24-2233-00 DB2 for OS/390 Version 5 Performance Topics, SG24-2213-00 Locking in DB2 for MVS/ESA Environment, [...] 29193 55 26_Re: Load with DATE problem10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Fri, 27 Apr 2001 10:44:18 -0500311_us-ascii Mike,

In the install procedures there is a DATE format exit that must be loaded....check your DB2.LOADLIB for members in V4 & V5 and the manual to get the correct name of the date exit that should be there (as I have forgotten the name of the exit - I just know it's supposed to be there). [...] 29249 23 40_What V7 features are needed to match V5?0_17_JTonchick@AOL.COM29_Fri, 27 Apr 2001 12:24:20 EDT483_US-ASCII We've made the technical decision to jump from DB2 V5 to V7, but now the administrative confusion starts.

With all the repackaging of DB2, QMF, the DB2 utilities, and the "no charge options" like the DB2 Control Center on the PC, what V7 features do I need to tell my boss to order to guarantee that we receive all we currently have in DB2 v5 and QMF v3.3? IBMLink's program descriptions and multiple features for each pricing option don't make this any easier. [...] 29273 47 48_Re: CA/Platinum P99D - No Bashing - reality/fact5_chris22_hhardy1@HOUSTON.RR.COM31_Fri, 27 Apr 2001 11:34:50 -0500579_iso-8859-1 Your 100% correct and that's (all you stated below) is taken into or accounted for within the process of vendor selection.... Make it quick and not so dirty, just find a shop using tools set you would like to incorportae within your environment.... Granted there will be some adjustments but we deal with this daily.... And right about now and for the next couple of years its a buys market.... the new vendor of choice will be glad to come in and dis-lodge someone else;s software.... Incorporated within the new deal they would even bring their own team in and [...] 29321 190 48_Re: CA/Platinum P99D - No Bashing - reality/fact18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Fri, 27 Apr 2001 12:35:27 -0400511_iso-8859-1 Fritz: Your note is well founded. And I agree that 'cost' is an issue. But at times you do reach a point where you have to say 'hey look can I continue to operate with software that is not performing as advertised and I'm paying out $$$'s for it'. The key is 'not to throw the baby out with the bathwater' as they say. As for me, as we approach moving to DB2 V7 sometime within the next 1 1/2 to 2 years if we feel we cannot totaly rely on our vendor tools then we will start looking elsewhere. [...] 29512 55 44_Re: What V7 features are needed to match V5?16_Robert H. Tilkes22_TILKESR@NATIONWIDE.COM31_Fri, 27 Apr 2001 12:46:24 -0400302_us-ascii With V 7 the utilities are offered in 3 packages. The first two options are not valid if you are trying to mirror what you have in V5. You will need to request the IBM DB2 Utility Suite which includes all of the utilities that you have currently with V5 and some additional new features. [...] 29568 21 32_CPU Utilization on FETCH Command11_Ted Flowers25_TedFlowers@GROCERYBIZ.COM31_Fri, 27 Apr 2001 11:36:48 -0500424_us-ascii While attempting to improve the response time of a DB2/CICS program, I added an index to one of the tables being used in a three-table join. This dramatically reduced the cost of the OPEN for the cursor, but now the CPU time required for the FETCH has increased beyond reason. The total CPU time to execute 6 fetches is 4.3136 seconds. I'm stumped as to the cause. Any assistance would be greatly appreciated. [...] 29590 56 36_Re: CPU Utilization on FETCH Command15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Fri, 27 Apr 2001 12:06:02 -0500471_ISO-8859-1 Hi, Ted, According to my notes from a Bonnie Baker class that I attended a while ago:

1) Declare Cursor does nothing at run-time. 2) Open Cursor makes sure that the right segment is in the EDMPOOL, obtains high-level locks, execute authority on the plan is checked, and the result set is NOT created here. 3) Fetch actually finds the qualified rows. The cursor is positioned here. If a tablespace scan is the access path then it is performed here. [...] 29647 18 80_Anyway to tell when a tablespace was last updated/used outside of using the Log?18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM31_Fri, 27 Apr 2001 13:09:08 -0400 29666 52 36_Re: CPU Utilization on FETCH Command0_26_truman.g.brown@VERIZON.COM31_Fri, 27 Apr 2001 13:13:13 -0400370_us-ascii Well, this may sound off the wall - In its "previous life" was the cursor list-prefetch and the pages were usually already in the bufferpool when FETCH executed? And in its "improved form", is it creating a results set and then doing read I/O against a 4K work dataset?

That amount of CPU for 6 FETCHES does sound a little ominous in either case... [...] 29719 117 52_Re: Should SAP be in a separate subsystem on OS/390?12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Fri, 27 Apr 2001 12:15:53 -0500342_iso-8859-1 Well, one size does not fit all; your mileage may vary, ....

In our case, we believe we will need 4-way active data sharing (plus additional fail-over member(s)) to scale up to the number of mips we will need for our single enterprise-level SAP system. Why would we want to mix other data into this data sharing group? [...] 29837 17 34_DB2 SP WLM application environment17_Lidstrom, Barbara31_Barbara.Lidstrom@PSS.BOEING.COM31_Fri, 27 Apr 2001 10:12:01 -0700625_iso-8859-1 I am working on standards for our WLM application environments. We had originally thought that we would have one for applications, system, ... and one for any APF (DSNUTILS). Then if we had unique requirements, we could add more. So we have ssidWLM1, ssidWLMU, and add ssidWLMn as necessary. Another possibility is to have one for each application - ssidWap1, ssidWap2, ... Then I had a monkey wrench thrown into my thinking when I saw in the V7 presentation guide red book (SG24-6121) "It has always been a recommendation to configure different stored procedure workloads into different WLM stored procedure [...] 29855 97 36_Re: CPU Utilization on FETCH Command16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Fri, 27 Apr 2001 13:27:48 -0400517_us-ascii An important disctintion needs to be made here. It depends on if there is a sort or not. If there is a sort of any kind, including list prefetch, then all qualifying rows are read and sorted at OPEN cursor time, and then each FETCH gets a row from the sorted set of rows. If there is no sort, then no data is read at OPEN cursor time. At each FETCH data is read to satisfy the request, one FETCH at a time. This is one of the reasons that sorts are so undesirebale, especially in screens that list rows [...] 29953 93 8_Re: DB2I11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 27 Apr 2001 13:37:21 -0400411_iso-8859-1 Here is one. This one allocates different db2 libraries on different lpars.



/* REXX */ /*********************************************************************/ /* DB2I BOOTSTRAP FOR DB2 V6 (SPUFI) */ /* */



"SUBCOM ISPEXEC" IF RC \= 0 THEN DO SAY "YOU ARE NOT IN AN ISPF ENVIRONMENT" EXIT END

/* SET LIBRARY(S) FOR THIS LPAR */

ISPEXEC "VGET (ZSYSID) " [...] 30047 23 65_OS/390 SP Question - Calling Program Invalidation after SP Change13_Brian Charles20_bcharles@RITEAID.COM31_Fri, 27 Apr 2001 13:39:56 -0400364_iso-8859-1 I wrote a V6 Cobol stored procedure and I'm testing it by calling it from another cobol program. Whenever I recompile and bind the stored procedure, it invalidates the calling program's plan! Am I doing something wrong or is this just fact of life?

Please respond directly to bcharles@riteaid.com as I get listserv responses in digest form. [...] 30071 86 44_Re: What V7 features are needed to match V5?0_17_sjvagnier@AEP.COM31_Fri, 27 Apr 2001 13:57:52 -0400549_us-ascii Good timing Jim. I just went through the same issues with IBM. Here's what I was told:

1). Since we needed utilities from both the DB2 Operational Utilities product set and DB2 Diagnostic and Recovery Utilities product set, we decided on the DB2 Utilities Suite which combines the utilities from both product sets and it's cheaper than purchasing each individual product set. 2). For the Utilities Suite, you will also need to purchase the "Annual Subscription fee (Utilities Suite) which covers yearly maintenance and support. [...] 30158 79 36_Re: CPU Utilization on FETCH Command11_Ted Flowers25_TedFlowers@GROCERYBIZ.COM31_Fri, 27 Apr 2001 13:08:41 -0500603_us-ascii Thanks for your input.





truman.g.brown@V ERIZON.COM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: CPU Utilization on FETCH Command Discussion List



04/27/01 12:13 PM Please respond to DB2 Data Base Discussion List









Well, this may sound off the wall - In its "previous life" was the cursor list-prefetch and the pages were usually already in the bufferpool when FETCH executed? And in its "improved form", is it creating a results set and then doing read I/O against a 4K work dataset? [...] 30238 126 36_Re: CPU Utilization on FETCH Command11_Ted Flowers25_TedFlowers@GROCERYBIZ.COM31_Fri, 27 Apr 2001 13:10:46 -0500657_us-ascii Thanks for your input, Rick.





Charles F Creech cc: Sent by: DB2 Subject: Re: CPU Utilization on FETCH Command Data Base Discussion List



04/27/01 12:27 PM Please respond to DB2 Data Base Discussion List









An important disctintion needs to be made here. It depends on if there is a sort or not. If there is a sort of any kind, including list prefetch, then all qualifying rows are read and sorted at OPEN cursor time, and then each FETCH gets a row from the sorted set of rows. If there is no sort, then no [...] 30365 89 36_Re: CPU Utilization on FETCH Command11_Ted Flowers25_TedFlowers@GROCERYBIZ.COM31_Fri, 27 Apr 2001 13:22:59 -0500424_us-ascii Linda,

Thanks for shedding some light on the situation. I'll review whatever accounting data is available to make a further determination and proceed from there. Have a great weekend.

Ted Flowers





"Billings, Linda" cc: Sent by: DB2 Data Base Subject: Re: CPU Utilization on FETCH Command Discussion List [...] 30455 44 25_DB2 Overview information?10_Mark Labby16_mlabby@PHEAA.ORG31_Fri, 27 Apr 2001 14:35:47 -0400373_us-ascii I've been asked to prepare a high-level overview of DB2 and relational databases in general for a new project team that will be redesigning some of our old VSAM based applications. The users who will be there have no experience at all with relational databases and are definitely used to thinking in terms of specific files relation to specific CICS screens. [...] 30500 26 20_partition key update11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 27 Apr 2001 13:40:23 -0500352_iso-8859-1 Apar PQ16946 in V5 allows update of the partitioning key, but it says only for tables created after the apar is applied. I tried doing an explain on an update and got the -151 so I guess it also applies to V6. This update will NOT cause the row to be moved to another partition. I have PARTKEYU=SAME specified in ZPARM to prevent that. [...] 30527 89 32_Access Path Selection Statistics14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Fri, 27 Apr 2001 11:59:11 -0700388_iso-8859-1 I am striving to put together procedures that copy statistics from our production environment and update/insert them into our integration testing environment. My questions to all are:

1. What is the best way to accomplish this task with the least impact on production and test db2's? 2. Are there vendor products that can do this? 3. What problems may I encounter? [...] 30617 52 24_Re: partition key update34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Fri, 27 Apr 2001 14:58:44 -0400324_us-ascii That is correct. It has caused enormous pain to us in the past, where program works (bind) in the test but fails in production. We have been telling programmers not use this feature without letting DBAs know about it so we can check if table in production can tolerate to be dropped & recreated to allow this. [...] 30670 81 29_Re: DB2 Overview information?12_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Fri, 27 Apr 2001 14:56:05 -0400371_us-ascii Mark, If this requirement is for Db2 OS/390, then the book entitled An introduction to Db2 for OS/390 version 7 by Susan Graziano and Ann Kilty Hernendez might be useful to you. That is the first thing that came to my mind after I flipped through the pages.

Amit







Mark Labby on 04/27/2001 02:35:47 PM [...] 30752 19 40_Re: Performance of DB2/CICS applications12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 27 Apr 2001 13:39:42 -0500432_- The deadlock and timeout traces are inexpensive in STATS class 3, since they only are driven when there are timeouts and deadlocks. The IFCID 172 and 196 records provide lots of help for the typical issues. The traces have more information than the messages.

Are the locking resource wait times smaller than the 30 seconds? You don't want to have the resource wait at 60 seconds with something else timing out faster. [...] 30772 19 52_Re: Should SAP be in a separate subsystem on OS/390?12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 27 Apr 2001 13:48:02 -0500441_- Quite a few customers have some non-SAP applications in the same subsystem with SAP, judging by some comments I've heard. Treat that as what's it's worth, rumors.

The primary cost for a separate subsystem is the memory, disk space and time to create it. The cpu time is usually very close to the same for doing the same work. If there are some tuning advantages (and there usually are), then you might even save some cpu time. [...] 30792 74 29_Re: DB2 Overview information?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 27 Apr 2001 14:14:17 -0500590_iso-8859-1 Mark, maybe some required reading before class? The IBM manual, DB2 UDB for OS390 V7, An Introduction to DB2 for OS390, Section 1.2 DB2 Concepts would be a great start. Simple enough for a DB2 101 Class.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender [...] 30867 48 24_Re: partition key update11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Fri, 27 Apr 2001 14:12:09 -0500657_iso-8859-1 Yeah, it only works after the table is dropped and recreated. After you recreate it the sysibm.syscolumns will have a 'Y' in the updates field for that column.

Thanks.

Amit Sinha





-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: Friday, April 27, 2001 1:40 PM To: DB2-L@RYCI.COM Subject: partition key update



Apar PQ16946 in V5 allows update of the partitioning key, but it says only for tables created after the apar is applied. I tried doing an explain on an update and got the -151 so I guess it also applies to V6. This update will NOT cause the row to [...] 30916 46 85_Re: Anyway to tell when a tablespace was last updated/used outsid e of using the Log?13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Fri, 27 Apr 2001 12:37:38 -0700510_iso-8859-1 Running REPORT RECOVERY against that tablespace should give you that info. as it brings info. from SYSLGRNX and hence shows when that tablespace was open for update. But it does not record if the tablespace was only 'read' and not updated.

Thanks. Ashish.

-----Original Message----- From: Anyanso Agwu-Okeke [SMTP:pagwu@HOTMAIL.COM] Sent: Friday, April 27, 2001 10:09 AM To: DB2-L@RYCI.COM Subject: Anyway to tell when a tablespace was last updated/used outside of using the Log? [...] 30963 75 38_Re: DB2 SP WLM application environment12_Clark, Kevin15_KClark@FDIC.GOV31_Fri, 27 Apr 2001 15:49:47 -0400328_iso-8859-1 Barbara,

I was working on a draft to present to my client. It consists of four Environments, and has concerns for WLM Sysplex management. So info like NUMTCB is required. (we're going with 1 for all exec the last which will be 8 as under DB2 SPAS) . Another key is to used the SSID override for DB2SSN. . [...] 31039 122 24_Re: DSN1COPY Performance17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Fri, 27 Apr 2001 12:49:42 -0700521_us-ascii Bill, Thanks for the info but I still have a couple of questions for you. You mentioned that it only takes 3-4 minutes for each tape cartridge to be read. I am assuming that you are copying a 2 gig dataset containing the imagecopy to a DB2 vsam file. At best I can only get a copy completed in about 25 minutes for a 2 gig dataset from an image copy. Although if I reverse the process and dsn1copy the DB2 vsam file (index) to a sequential file then it takes only 13 minutes. I have used various values for [...] 31162 66 24_Re: partition key update11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 27 Apr 2001 15:27:41 -0500521_iso-8859-1 Thanks Venkat and Amit. That doesn't make it very useful, but I guess we can't have everything. I just wanted confirmation that I understood it correctly.

Tina

-----Original Message----- From: Sinha, Amit [mailto:Sinha.Amit@MBCO.COM] Sent: April 27, 2001 2:12 PM To: DB2-L@RYCI.COM Subject: Re: partition key update



Yeah, it only works after the table is dropped and recreated. After you recreate it the sysibm.syscolumns will have a 'Y' in the updates field for that column. [...] 31229 49 14_Declarative RI17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR31_Fri, 27 Apr 2001 17:39:47 -0300374_us-ascii Hi Folks,



We are working on defining declarative referential integrity on our system, and would like to know which sites are actually using it, and which ones are not using. We have some RI defined, but still have a lot of application controlled RI around that we want to get rid of. The information provided here will be for internal use only. [...] 31279 109 18_Re: Declarative RI12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 27 Apr 2001 16:10:11 -0500607_us-ascii



Hello,

If by "Declaritive RI" you mean foreign key references, then yes, I would say we're using it quite successfully. The neatest thing is that the Check utility tells me that everything is "ok" after I added the keys via "alter", and DELETE CASCADE keeps us feeling warm and fuzzy in the event our application SQL doesn't clean things up well enough. We also wanted to migrate some application controlled "RI", where we denormalized certain tables into "before" triggers, but they were too lame to get the values from another table (without UDF's which require WLM.) [...] 31389 78 22_Re: performance tuning11_Pedro Ramos18_ramos@MAINFRAME.PT31_Fri, 27 Apr 2001 23:52:21 +0100397_iso-8859-1 Hi Kr. Manish. You should check the product Database System Analysis (DSA) from HLS Technologies at www.hlstechnologies.com/dsa/dsa.htm

HLS Technologies offers a unique set of DB2 performance tools that focus on the specific needs of DB2 DBA's and Managers charged with the responsibility of providing and maintaining satisfactory service levels to mainframe DB2 end users. [...] 31468 194 19_Re: from IMS to DB211_Pedro Ramos18_ramos@MAINFRAME.PT31_Fri, 27 Apr 2001 23:59:43 +0100427_iso-8859-1 from IMS to DB2Hi Ajay. You should check the DL/2 product from Circle at http://www.circle-group.com/Software/Dl2Intro.htm DL/2 enables you to migrate your IMS legacy programs to DB2, and the new release will also support VSAM.

Com os nossos melhores cumprimentos, Best regards, Pedro Ramos

PS: Have a great weekend.



MAINFRAME, Lda Rua António Nobre 10 R/C 2800-259 Almada Portugal [...] 31663 44 28_Re: Fork error using DB2LOOK15_Lynette Roberts21_llroberts@KSCABLE.COM31_Fri, 27 Apr 2001 18:38:24 -0500768_- On Thu, 26 Apr 2001 11:23:43 -0000, John Clarke wrote:

>Hi all, >Can anyone tell me how to resolve the error listed below? Whenever I try to >run a DB2 look command this error occurs. > >AIX DB2 7.0 >wdc2031a /cpfddb/cpfadmin $db2look help > >db2look: 0403-030 The fork function failed. Too many processes already >exist. > >Cheers, >John >_________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 31708 177 70_Re: Creating a linked server with SQL Server 7 to access DB2/OS39 0 V58_Jo Young22_Jo_Young@HAM.HONDA.COM31_Fri, 27 Apr 2001 19:54:31 -0400602_us-ascii Robert,

We have been using the linked server approach from SQL Server to DB2 (OS/390 v5) for about a year now.

Here's what we did to get it to work:

1) Load DB2 Connect (client install, not gateway) on the server hosting SQL Server. This loads the DB2 driver and creates the DSN in the ODBC Manager. 2) On the "General" tab of the create-new-linked-server dialog click the "other data source" radio button and use the following: -Provider Name: select Microsoft OLEDB for ODBC from the drop-down menu (still can't get DB2 v7 OLEDB driver to work). -Product Name: IBM [...] 31886 14 57_Re: [ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT15_Lynette Roberts21_llroberts@KSCABLE.COM31_Fri, 27 Apr 2001 18:46:35 -0500343_- We saw this same error messages working with DSN1COPY from prod to test on DB2V4. For us, it turned out that the segsize on the test tablespace was different than the production tablespace, causing our error. We ended up dropping the tablespace in test and recreating it to match the production definition, and then rerunning DSN1COPY. [...] 31901 15 31_Re: DB2 connect & shadow tables16_Marcelo Silveira22_mardb2ques@HOTMAIL.COM31_Fri, 27 Apr 2001 21:50:19 -0500432_- I had the same problem. I couldn't click on "Advanced Options" because it was greyed out. So the shadow tables got created on default database DSNDB04 on mainframe.

Thanks, Marcelo.

================================================ 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. 31917 19 57_Re: [ EMPTYING INDEX WITHOUT DROPPING THE INDEX ]- URGENT0_24_ujjwal@LOT.TATASTEEL.COM31_Sat, 28 Apr 2001 09:29:07 +0530492_us-ascii Hi Roberts, In fact it was the same case with us yerterday and we also did the same ie dropping the tablespace and recreating it in test as in prod due to segsize difference in both the environments . Thanx.

Regards Ujjal Bhattacharya

================================================ 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. 31937 205 19_Re: from IMS to DB217_Gupta, Ajay Kumar24_Ajay.Gupta@HPSGLOBAL.COM31_Sat, 28 Apr 2001 12:12:25 +0500319_- Hi Judy, Thanx alot for the mail and sharing your experience. I would like to know lil more about the tool which converts DL/I calls to SQL and the extent of changes that are required in the code. cheers,ajay

ajay k gupta , HCL Perot System, India 91-118-4588604 (direct) 91-118-4581236..41 : extn : 3270 [...] 32143 84 16_DB2 on 2 Servers11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Sat, 28 Apr 2001 20:44:44 +0700359_us-ascii Dear all

I like to know wheter DB2 can be installed on a Primary Server and Backup Server, both are using 1 external storage as the storage for DB2 data. In normal operation, all Transaction will be handled by the Primary Server, but if suddenly the Primary Server is off/crash, the operation will be move to and handle by Backup Server. [...] 32228 114 36_Re: CPU Utilization on FETCH Command13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 28 Apr 2001 10:01:45 -0500366_us-ascii Ted,

Actually, RID sort occurs at first fetch, not at open cursor (provided there is no data sort also). Each subsequent fetch accesses the RID pool to retrieve the next row.

Obviously you had a data sort (for ORDER BY or GROUP BY?) beforehand, which occurs at open cursor. Now you have an access path which does not require a data sort. [...] 32343 57 20_Re: DB2 on 2 Servers20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM31_Sat, 28 Apr 2001 15:11:03 -0000640_- Hi Endy

What you will need to look at is "High Availability" for DB2. This will be HACMP on AIX, Sun Cluster on Sun boxes and MSCS on Windows.

This is all explained in the admin guide.

HTH

Pav



>From: Endy Lambey >Reply-To: endyl@mitrainfosarana.co.id >To: DB2-L@RYCI.COM >Subject: [DB2-L] DB2 on 2 Servers >Date: Sat, 28 Apr 2001 20:44:44 +0700 > >Dear all > >I like to know wheter DB2 can be installed on a Primary Server and Backup >Server, both are using 1 external storage as the storage for DB2 data. >In normal operation, all Transaction will be handled [...] 32401 122 19_Re: from IMS to DB215_Toine Michielse18_vndobtm@US.IBM.COM31_Sat, 28 Apr 2001 17:14:56 +0200641_iso-8859-1 Hello Ajay,

Take a look at DL/2 from Circle.

http://www.circle-group.com/Software/SoftwareOpeningpage.htm

Regards,

Toine Michielse





Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: from IMS to DB2









Hi Judy, Thanx alot for the mail and sharing your experience. I would like to know lil more about the tool which converts DL/I calls to SQL and the extent of changes that are required in the code. cheers,ajay [...] 32524 37 31_JCL for running SPUFI in Batch.13_Harry William14_NRv893@AOL.COM29_Sat, 28 Apr 2001 11:21:13 EDT217_US-ASCII Hi All, I have to run SPUFI to retrieve data over a period of 3 years.If i have to execute it takes considerable amount of time.Is there any JCL to execute the SQL in the back ground.

many thanks harry 32562 35 35_Image Copy Registration in syscopy.10_Lee Mandel16_DBMSUser@AOL.COM29_Sat, 28 Apr 2001 12:14:05 EDT161_US-ASCII Hi All, Can a person registered a Image Copy entry in syscopy other than an Image Copy Utility completion? If so what book should I read.

TIA Lee 32598 169 20_Re: DB2 on 2 Servers19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Sat, 28 Apr 2001 18:04:22 +0100488_us-ascii Endy,

Check out the chapters in the Administration Guide on High Availability. You can read the manuals on the web at -

http://www.software.ibm.com/data/db2/udb

And the follow the Library link and then the Product Technical Library link.

The chapters in question are -

Chapter 11 : General High Availability Planning Chapter 33 : AIX (using HACMP) Chapter 34 : Windows (using Microsoft Clustering) Chapter 35 : Solaris (using Sun Cluster) [...] 32768 88 35_Re: JCL for running SPUFI in Batch.16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Sat, 28 Apr 2001 13:31:15 -0400566_us-ascii





Harry,

Here you go (just remember, every site tailors their TEP2 routine differently; so, you'll need to modify this JCL stream in accordance with your shop standards, and etc.):

//* //STEP010 EXEC PGM=IKJEFT01 //STEPLIB DD DISP=SHR,DSN=SITE.SPECIFIC.DSNEXIT // DD DISP=SHR,DSN=SITE.SPECIFIC.DSNLOAD //SYSTSPRT DD SYSOUT=* //* //SYSPRINT DD SYSOUT=* //SYSPUNCH DD DUMMY //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DSN) RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) /* //SYSIN DD * SELECT * FROM CREATOR.TABLE_NAME ; //* [...] 32857 15 47_Re: DSN3@SGN setup for use of secondary authids12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 28 Apr 2001 14:46:48 -0500329_- For RACF, I think you will want to start with the ones we provide as DSN3SATH and DSN3SSGN in SDSNSAMP. The documentation in the Administration Guide appendix on writing exit routines notes a possible change or two, but these routines have been the same for quite a few years.

Roger Miller, DB2 for z/OS and OS/390 [...] 32873 19 36_Re: Access Path Selection Statistics12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 28 Apr 2001 15:04:14 -0500422_- I hope you started with the information from the Administration Guide. Each version has some differences. In V7 the chapter is 32, Maintaining Statistics in the Catalog.

There is a section titled Modeling your production system. It has SQL statements that generate the needed update SQL statements and several cautions. There are other cautions under the title, Updating the Catalog, earlier in the chapter. [...] 32893 68 31_DB2 Utilities Evaluation Advice5_Larry19_larry1260@YAHOO.COM31_Sat, 28 Apr 2001 16:54:05 -0700350_us-ascii Hello all

We are looking at DB2 Utilities from various vendors. We are looking at REORG, COPY, UNLOAD, LOAD, RECOVER, and RUNSTATS plus online versions of these utilities. I am hoping someone else has roughly the same type of shop and has already been thru an evaluation of DB2 utilities and can provide some insight and advice. [...]