1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2000, week 3 2 45 12_Re: Net.data15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Sat, 15 Apr 2000 13:59:52 -0400664_iso-8859-1 Try this URL:

http://www-4.ibm.com/software/data/net.data/library.html

Additionally, I just attended the net.data IBM course here in Tampa and lots of the instructional material presented was from the manuals at this website location.

Hope this helps.

Susan Loria

-----Original Message----- From: Tim Miller [mailto:tmiller@TELCORDIA.COM] Sent: Friday, April 14, 2000 3:20 PM To: DB2-L@RYCI.COM Subject: Net.data



I was wondering if anyone knows of any good books explaining net.data. I have download all documentation/samples from IBM Net.data web site, but I was looking for more can anyone help. [...] 48 64 48_Re: Migrating DB in DB2/2 v 2.1 to UDB/NT v. 5.013_Umair Hussain25_umair_hussain@HOTMAIL.COM29_Sat, 15 Apr 2000 17:17:30 CDT504_- I believe cross platform migration is not possible. You also cannot backup on one platform (for example os2) and restore on to another platform (for example nt). You have to move the data via db2move or export/import in ixf file format.

Hope this helps... Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|************** [...] 113 21 29_Moving dbs between NT servers6_Jim C.17_Jimluxpax@AOL.COM29_Sat, 15 Apr 2000 19:24:05 EDT566_US-ASCII All, Is it possible to use backup/restore to move a database from one NT server to another? We tried (in UDB 6.1): a redirected restore over the LAN, restore with replace existing option, the 'create database from backup' option in the GUI - nothing worked. I'm guessing it has something to do with the 'database seed'? We ended up generating an empty db on the target server and using db2move to port the database from one to the other. Workable, but it seems like the long way around the barn - especially since we are looking to script the process. [...] 135 34 13_Sniffle error10_John Small17_jsmall@ISP800.COM31_Sat, 15 Apr 2000 21:21:21 -0500391_us-ascii I recently installed the Personal Developer's Edition for OS/2 v6.1 (with Fixpak3 applied). Whenever I boot up OS/2 one of the DB2 tasks started in my Startup folder causes and error dialog to display. The error message indicates that my "primary network adapter is not configured for TCP/IP". It goes on to tell me how to address the problem (which I have tried to no avail). [...] 170 146 29_Re: USING ENABLE PARM ON BIND12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Sat, 15 Apr 2000 23:12:22 -0500517_iso-8859-1 We used this feature back in 1993 on a system using packages. We used the enable to not only help in the area of security, but we also used it in test to ensure where we were executing the program from. We had two cics regions. In CICS region "A" we want to update tables for "A". In region "B" we want to update tables in "B". We had a problem early on when programmers would forget and run a transaction for PLAN "B" in CICS region "A". We bound the plan for region "A" and enabled CICS region "A". [...] 317 82 33_Re: Moving dbs between NT servers0_29_Greg.Palgrave@BANKWEST.COM.AU31_Mon, 17 Apr 2000 09:10:47 +0800521_us-ascii Jim,

Yes it is possible to do this. I suspect you hit problems due to trying "a redirected restore over the LAN" - to me that suggests you are trying to restore from a backup on server X while connected to server Y.

DB2 (V5.2 anyway) is very fussy, and if you want to restore from a backup, it has to be on the local drives of the server you are connected to (eg. C: D: etc, but not a network mapped drive). You can also get problems if the codepage is different from one server to the other. [...] 400 36 36_DB2 & Net.Data - a request for infos14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Apr 2000 09:43:23 +0200369_us-ascii We are thinking to activate Domino Go Webserver for S/390 to make possible to use some

DB2 tables via Web. For this reason we are studying (and we 'll install as soon as possible, I

hope) Net.Data, but our 'Dilbertian' boss (let me pass this neologism...) is afraid because he

believe it's very expensive in terms of CPU consumption. [...] 437 24 31_Re: Information on Deleted rows18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE31_Mon, 17 Apr 2000 03:24:45 -0500561_- Hello there,

I think your DBA might refer to Platinum Log Analyzer which is a very handy tool to extract records from DB2 Log files. Still, DB2 has its own program, DSN1LOGP, which enables to extract the same information, in a less friendly format for sure. This program only requires RACF authority to read the Log files. Even if your DBA does not have this authority, it should be too long to get. As I don't have authority to use PLA (Platinum Log Analyzer) in production neither to extract records, I still use it (option 4) to select the Log [...] 462 7310 40_Re: DB2 & Net.Data - a request for infos10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Mon, 17 Apr 2000 08:37:33 GMT574_us-ascii

Hi Max, I have one very interesting document which i found in internet(may be in IBM site only) which describes 2 Tier vs 3 Tier mesurment with lotus domino fo webserver.I do not have enough concept in this area but what u are talking about lotus domino go webserver on OS/390 is 2 Tier working enviornment.According to this docs. your number of virtual browser will increase(no. of concurrent users may be) but the CPU consumption will be high. You might have gone through this document but i am sending you in case you have not read that. You can go [...] 7773 26 18_SYNCPOINT ROLLBACK19_Pedro Correia (DSI)27_TE00102@MUNDIALCONFIANCA.PT31_Mon, 17 Apr 2000 09:31:38 +0200501_- Hi ALL

> We have a program COBOL/CICS/DB2 that executes 2 consecutive updates > 1 - with an access key, updates DB2 tables and a VSAM file > 2 - with a new access key, updates the same DB2 tables and the same VSAM > file. > In case of error it goes to an error paragraph where it executes an > SYNCPOINT ROLLBACK. > If an error ocurrs on the 1st update, everything is rolled back. > My doubt is: If an error occurs on the 2nd update, would it rollback the > 1st updates? > The COMMIT is [...] 7800 49 22_Re: SYNCPOINT ROLLBACK11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Mon, 17 Apr 2000 17:01:44 +0800684_us-ascii Yes, it will rollback all the updated records (DB2 tables and VSAM) to the last commit point.







"Pedro Correia (DSI)" on 17/04/2000 03:31:38 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) Subject: SYNCPOINT ROLLBACK







Hi ALL

> We have a program COBOL/CICS/DB2 that executes 2 consecutive updates > 1 - with an access key, updates DB2 tables and a VSAM file > 2 - with a new access key, updates the same DB2 tables and the same VSAM > file. > In case of error it goes to an [...] 7850 18 40_Re: DB2 & Net.Data - a request for infos14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Apr 2000 11:57:37 +0200425_us-ascii Hi Sanjeev, nice to meet you again in db2-l.

Thank you very much, I read your pdf about Net.Data and it I found it very useful !!

THX again & regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7869 23 33_Re: Moving dbs between NT servers15_Joel Zigelstein20_jzigel1@HALLMARK.COM31_Mon, 17 Apr 2000 07:16:18 -0500531_- Jim ...

Im in no way an expert .. Actually I'm quite new at DB2 UDB (experience is in DB2 fo VM and VSE) but i have just recently migrated a databse from one server to another. I did this using the DB2LOOK command and the DB2MOVE command. The DB2LOOK can create a script file (which will have to be modified) that will recreate all the SQL for defining the database on the new server. Then run DB2MOVE to create an export of the first database, use the output and use it to populate the new database. All these files [...] 7893 55 22_Re: DB2 control center19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 17 Apr 2000 07:38:15 -0500607_iso-8859-1 Hi Wayne, You're right. DSNACCMD is a SP that runs on DB2 UDB/OS390 and supplies one of the "hooks" needed by Control Center.

Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, [...] 7949 202 55_Repost: SAP R/3 and DB2 to be Supported on HP Platforms13_Morrill, John12_JohnM@VP.NET31_Mon, 17 Apr 2000 06:51:39 -0600563_iso-8859-1 Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner =================================================



Dear DB2 Interested Parties,

Below is a press release from earlier this week announcing the SAP R/3 will be available on HP platforms with DB2 V7 by the 4th quarter of this year. [...] 8152 68 22_Re: SYNCPOINT ROLLBACK19_Pedro Correia (DSI)27_TE00102@MUNDIALCONFIANCA.PT31_Mon, 17 Apr 2000 13:58:03 +0200655_- Thank you Roland Chua for your help. Regards

Pedro Correia





> -----Original Message----- > From: Roland Chua [SMTP:rolandchua@SINGAPOREEXCHANGE.COM] > Sent: Monday, April 17, 2000 11:02 AM > To: DB2-L@RYCI.COM > Subject: Re: SYNCPOINT ROLLBACK > > Yes, it will rollback all the updated records (DB2 tables and VSAM) to the > last commit point. > > > > > "Pedro Correia (DSI)" on 17/04/2000 03:31:38 > PM > > Please respond to DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) > Subject: SYNCPOINT ROLLBACK > > > > > Hi [...] 8221 26 25_Sysplan/Syspackage tables15_Morris, Deborah20_DMorris@UNCH.UNC.EDU31_Mon, 17 Apr 2000 09:29:31 -0400393_iso-8859-1 I need to remove entries from both the Syspackage & Sysplan tables. Is there some utility that I can run to do that?

Thanks in advance for help with this.



Deborah A. Morris Supervisor DataBase Administration UNC Health Care System 2nd Floor Carr Mill Mall Carrboro, NC 27510

(919) 966-2197 (voice) (919) 966-2110 (fax) mailto: dmorris@unch.unc.edu [...] 8248 47 29_Re: Sysplan/Syspackage tables14_Larry D Bolick24_ldbolick@DUKE-ENERGY.COM31_Mon, 17 Apr 2000 09:33:03 -0400486_us-ascii Yes. It is the FREE PACKAGE and FREE PLAN commands.







"Morris, Deborah" on 04/17/2000 09:29:31 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Larry D Bolick/Corp/DukePower) Subject: Sysplan/Syspackage tables







I need to remove entries from both the Syspackage & Sysplan tables. Is there some utility that I can run to do that? [...] 8296 55 29_Re: Sysplan/Syspackage tables15_Toine Michielse18_vndobtm@US.IBM.COM31_Mon, 17 Apr 2000 15:35:27 +0100534_us-ascii How about free plan/package......

Toine Michielse DB2 for OS/390 Technical Specialist Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



"Morris, Deborah" @RYCI.COM> on 04/17/2000 02:29:31 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Sysplan/Syspackage tables [...] 8352 72 29_Re: Sysplan/Syspackage tables15_Morris, Deborah20_DMorris@UNCH.UNC.EDU31_Mon, 17 Apr 2000 09:45:52 -0400492_iso-8859-1 thanks ..

Deborah A. Morris Supervisor DataBase Administration UNC Health Care System 2nd Floor Carr Mill Mall Carrboro, NC 27510

(919) 966-2197 (voice) (919) 966-2110 (fax) mailto: dmorris@unch.unc.edu



-----Original Message----- From: Larry D Bolick [mailto:ldbolick@DUKE-ENERGY.COM] Sent: Monday, April 17, 2000 9:33 AM To: DB2-L@RYCI.COM Subject: Re: Sysplan/Syspackage tables



Yes. It is the FREE PACKAGE and FREE PLAN commands. [...] 8425 23 16_CDB and CA Tools15_Brian Zamborski18_bzamborski@USS.COM31_Mon, 17 Apr 2000 09:51:04 -0400539_us-ascii I was hopeful someone could post information about a few products we are considering for partitioning:

CA' s Partition Expert CDB's Repart CDB's PartRoll

I appreciate any input that may be helpful in determining the usefulness of these tools!!

Thanks! Brian Zamborski

================================================ 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. 8449 51 29_Re: Sysplan/Syspackage tables12_Ranjay Sinha24_rsinha@LOT.TATASTEEL.COM29_Mon, 17 Apr 2000 14:07:10 GMT550_us-ascii THE DB2 COMMAND "FREE PLAN(PLAN NAME)" WILL REMOVE THE ENTRY FROM SYSPLANTABLES.







"Morris, Deborah" @RYCI.COM> on 04/17/2000 06:59:31 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Sysplan/Syspackage tables



I need to remove entries from both the Syspackage & Sysplan tables. Is there some utility that I can run to do that? [...] 8501 24 8_SYSLGRNG9_Sally Mir15_smir@BCBSGA.COM31_Mon, 17 Apr 2000 10:06:37 -0400457_us-ascii If nothing is running in a particular DB2 subsystem except for one job that quiesces and image copies the catalog and directory tablespaces, what would cause that quiesce to be unable to drain writers from DSNDB01.SYSLGRNG? This job always used to run successfully; now it goes down every time in the quiesce step. I even tried running it immediately after starting up the DB2 subsystem, before any other jobs are run, and got the same error. [...] 8526 225 42_Re: Is there a plausible explanation ?????23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Mon, 17 Apr 2000 10:12:33 -0400647_iso-8859-1 Thank you all for your quick suggestion/pointers...

Sanjay

> -----Original Message----- > From: Tim.Lowe@STPAUL.COM [SMTP:Tim.Lowe@STPAUL.COM] > Sent: Friday, April 14, 2000 5:36 PM > To: DB2-L@RYCI.COM > Subject: Re: Is there a plausible explanation ????? > > Sanjay, > If this is the problem, then I believe there be a compiler warning like > this: > IGYSC2025-W "LK-FIELD-NAME" or one of its subordinates was referenced, > but > "LK-FIELD-NAME" was a "LINKAGE SECTION" item that did not have > addressability. This reference will not be resolved successfully at > execution. > > Thanks, > Tim Lowe > > P.S. Larry, [...] 8752 44 29_Re: Sysplan/Syspackage tables16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Mon, 17 Apr 2000 10:17:34 -0400637_- DB2I -- option Bind/Rebind/Free -- Free removes plans or packages depending on which you select.

Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: Morris, Deborah [SMTP:DMorris@UNCH.UNC.EDU] > Sent: Monday, April 17, 2000 9:30 AM > To: DB2-L@RYCI.COM > Subject: Sysplan/Syspackage tables > > I need to remove entries from both the Syspackage & Sysplan tables. Is > there some utility that I can run to do that? > > Thanks in advance for help with this. > > > Deborah A. Morris > Supervisor DataBase Administration > UNC Health Care System > 2nd Floor Carr [...] 8797 42 12_Re: SYSLGRNG15_Murley, Michael22_Michael_Murley@BMC.COM31_Mon, 17 Apr 2000 09:29:04 -0500677_ISO-8859-1 Sally,

Try doing a DIS DB(DSNDB01) SPACE(*) RESTRICT to see if SYSLGRNX or one of its indexes is in a restricted status. That causes this symptom.

Michael Murley BMC

-----Original Message----- From: Sally Mir [mailto:smir@BCBSGA.COM] Sent: Monday, April 17, 2000 9:07 AM To: DB2-L@RYCI.COM Subject: SYSLGRNG



If nothing is running in a particular DB2 subsystem except for one job that quiesces and image copies the catalog and directory tablespaces, what would cause that quiesce to be unable to drain writers from DSNDB01.SYSLGRNG? This job always used to run successfully; now it goes down every time in the quiesce step. I [...] 8840 25 27_Online Reorg Space Problems12_Greg Popwell27_greg.popwell@MOTION-IND.COM31_Mon, 17 Apr 2000 09:55:18 -0500563_iso-8859-1 Hi List,

I'm having problems getting enough shadow space for NPI's using Online Reorg for OS/390 V5. We're Stogroup and SMS managed. I'm doing a partition level OLR of a tablespace which has 3 NPI's on it. My understanding is that OLR will create a shadow copy of just the Logical partition of the NPI's. But it seems to be trying to create a shadow for the entire set of NPI's, thus we're running out of space. Has anyone experienced similar problems? Let me throw in another wrinkle. We're using BMC's EasyPool to do dataset allocations. [...] 8866 81 22_Re: DB2 control center15_Philip, Sibimon27_Sibimon_Philip@CSXLINES.COM31_Mon, 17 Apr 2000 11:02:34 -0400307_iso-8859-1 Thanks to everyone who helped me. We do not have all these stored procedures set it up in mainframe. Can any one tell me, what all useful thing we can do easily from Control center. I thought this is like a SQL server enterprise manager. I can only see tables using some selection criteria. [...] 8948 125 31_Re: Online Reorg Space Problems0_18_mebert@AMADEUS.NET31_Mon, 17 Apr 2000 17:28:45 +0100578_us-ascii After some deep thinking, I have come up with this explanation:

DB2 cannot know in advance how much space a shadow NPI just for the keys in the Reorg'd partition would require (with N partitions, it's NOT necessarily 1/N of the total!) so it has to allocate the shadow using the PRIQTY/SECQTY for the original (if you have better knowledge, do an ALTER of the NPI parameters before, then change them back afterwards as the BUILD2 phase will NOT delete/define the NPI datasets - it does an SQL type update of the RIDs, as was discussed before on this list). [...] 9074 36 53_IDUG Conference - Looking for a few good moderators!!10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Mon, 17 Apr 2000 12:12:54 -0400313_us-ascii The IDUG North American Conference Planning Committee is looking for moderators for the upcoming May 15th-18th conference.

The sessions needing a moderator are: A - A4 B - B10 C - C7, C12, C13 D - D13, D14 E - E5, E12 F - F13 G - G10, G14 H - H2, H4, H6, H9, H14 I - I1, I7, I10 J - J10, J13 [...] 9111 45 18_AIX - 64-bit cpu ?11_Chris Hardy17_hhardy@SWBELL.NET31_Mon, 17 Apr 2000 11:21:18 -0500310_iso-8859-1 hello,

Anyone utilizing a mix of 32bit processor and 64-bit processor per RS/6000 under AIX.... this is ibm's s80 family of RISC boxes..... 2-way -- one cpu 32bit the other 64 bit... What are you using for / to monitor CPU utilization per both cpu's??? Also cpu % direct from kernel???? [...] 9157 23 12_Adding Users13_Wilson, Randy22_Randy_Wilson@BCBST.COM31_Mon, 17 Apr 2000 13:03:49 -0400558_- Hello list, The environment I'm working is a new one to us with UDB/EEE 6.1 running on AIX 4.3 with the control center installed on an NT workstation .... I'm having trouble trying to add users through the control center .... when I right click on the database and select authorities... there is a button to 'add' a user ... I press this button and nothing happens at all. I've also tried right clicking on the 'DB Users' table under 'User and Group objects' and selecting 'add', when I do this the progress indicator activates but never gives me the [...] 9181 18 34_Number of Months Between Two Dates13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM31_Mon, 17 Apr 2000 13:52:55 -0400436_us-ascii Has anyone tried to calculate the number of months between two dates in a SQL statement? If so, could you provide me with an example? TIA, Scott Lindsey Sr. DBA, Highlights for Children

================================================ 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. 9200 30 74_Lowering the Priority of a Distributed Query after it has begun e xecution12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Mon, 17 Apr 2000 14:08:44 -0400489_iso-8859-1 Hi,

If a DB2 thread running from a workstation (via DDF) is consuming large amounts of CPU, are there any alternatives to canceling the thread, specifically can you adjust the priority of the thread as it is running so that the thread's work can continue (just at the lowest priority) and without affecting the priority of other distributed threads. (My guess is that you could lower the MVS Dispatch priority of DDF, but that would affect all distributed threads.) [...] 9231 35 46_-101 sqlcode after conversion to DB2 V6 OS/39013_John Arbogast16_jfarbo@YAHOO.COM31_Mon, 17 Apr 2000 11:24:28 -0700298_us-ascii

Has anyone experienced unexplained -101 sqlcodes after conversion to DB2 V6?? We have a production application that ran fine before the conversion, but now gets -101s.

Any help would be great!

JA





--------------------------------- Do You Yahoo!? 9267 48 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39012_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 17 Apr 2000 11:29:16 -0700587_us-ascii From the IBM V6 Install manual: | 2.7.2.5.1 Adjust application programs

| You might need to adjust your application programs because of the following release incompatibilities.

| SQLCODE -101: After migrating to DB2 Version 6, it is possible to receive SQLCODE -101 on long or complicated SQL statements that previously executed successfully. This is possible because SQL statements and DB2 internal structures are buffered in the same local storage, and release changes in the internal structures can result in less storage available for the SQL statements. [...] 9316 53 78_Re: Lowering the Priority of a Distributed Query after it has begun e xecution12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 17 Apr 2000 11:30:41 -0700584_us-ascii If you're running V6 with WLM for the DDF address space, it's possible. Otherwise, you're stuck to the best of my knowledge.

--- "Hall, Robert" wrote: > Hi, > > If a DB2 thread running from a workstation (via DDF) > is consuming large > amounts of CPU, are there any alternatives to > canceling the thread, > specifically can you adjust the priority of the > thread as it is running so > that the thread's work can continue (just at the > lowest priority) and > without affecting the priority of other distributed > threads. (My guess [...] 9370 55 78_Re: Lowering the Priority of a Distributed Query after it has begun e xecution22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Mon, 17 Apr 2000 14:30:33 -0400659_- I suppose this would be possible if there was such a thing as Work Load Managed (WLM) DDF threads similar to WLM Stored Procedures? Is there such a thing?



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

> ---------- > From: Hall, Robert[SMTP:Robert.Hall@OCC.TREAS.GOV] > Sent: Monday, April 17, 2000 2:08 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Lowering the Priority of a Distributed Query after > it has begun e xecution > > Hi, > > If a DB2 thread running from a workstation (via DDF) is consuming large > amounts of CPU, are there any alternatives to canceling the thread, > specifically [...] 9426 37 16_Re: Adding Users11_Steve Mazer17_smazer@FMTUSA.COM31_Mon, 17 Apr 2000 14:22:07 -0400599_us-ascii You must get (or beg) the AIX System Administrator to add the user to the system before you can grant any authority/permissions to them.

Regards, Steve Mazer Fourth Millennium Technologies

At 01:03 PM 4/17/00 -0400, you wrote: >Hello list, >The environment I'm working is a new one to us with UDB/EEE 6.1 running on >AIX 4.3 with the control center installed on an NT workstation .... I'm >having trouble trying to add users through the control center .... when I >right click on the database and select authorities... there is a button to >'add' a user ... I press this [...] 9464 63 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39024_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 17 Apr 2000 11:46:29 -0700596_- We have some of the longest and most complicated SQL (from a vendor package, of course) that I've ever seen. Since we are planning on converting to version 6 soon, what is the remedy for these -101 SQL codes?

> -----Original Message----- > From: Myron Miller [SMTP:myronwmiller@YAHOO.COM] > Sent: Monday, April 17, 2000 11:29 AM > To: DB2-L@RYCI.COM > Subject: Re: -101 sqlcode after conversion to DB2 V6 OS/390 > > From the IBM V6 Install manual: > | 2.7.2.5.1 Adjust application programs > > | You might need to adjust your application programs > because of the following release [...] 9528 25 38_Re: Number of Months Between Two Dates13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 17 Apr 2000 14:53:31 -0400485_iso-8859-1 How about

select month(current date - '2000-01-25') from sysibm.sysdummy1

____________ 2

# Rows: 1 # Cols: 1



David Seibert Compuware Corporation File-AID product planner 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. 9554 103 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39012_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 17 Apr 2000 11:57:04 -0700470_us-ascii Only option I know (from IBM's recommendation in install manual) " Rewrite the unsuccessful SQL statements by using correlated references, breaking up UNIONs or using OUTER JOINs. "

Unfortunately, with vendor packages, that's easier said than done. Other than getting vendor to change SQL (yeah, right :) ) you might contact IBM and see if there is possibly anything else you can do to alleviate the memory problems that are the cause of the -101s. [...] 9658 56 79_Re: Lowering the Priority of a Distributed Query after it has beg un e xecution11_Jerry, Jeff24_Jeff.D.Jerry@NORWEST.COM31_Mon, 17 Apr 2000 14:13:45 -0500506_iso-8859-1 Yes you can. We are using Work Load Manager in goal mode. You must be MVS 5.2 and above and DB2 Version 4 and above.

We had a similar situation in that our distributed address space had a high dispatching priority and was consuming most of our production CPU. We set up a work load manager service classes for DDF threads that had 3 periods similar to TSO and it's periods. As a thread continued to process beyond thresholds set in WLM it would drop the priority of the DDF threads. [...] 9715 120 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39024_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Mon, 17 Apr 2000 14:39:39 -0500540_- Has anyone experienced the -101's with PeopleSoft software? If so, what PeopleSoft versions and components? Thx.

Dave

Dave Gendron dave.gendron@coastalcorp.com

-----Original Message----- From: Myron Miller Sent: Monday, April 17, 2000 12:57 PM To: DB2-L@RYCI.COM Subject: Re: -101 sqlcode after conversion to DB2 V6 OS/390

Only option I know (from IBM's recommendation in install manual) " Rewrite the unsuccessful SQL statements by using correlated references, breaking up UNIONs or using OUTER JOINs. " [...] 9836 39 19_Re: QMF batch query12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Mon, 17 Apr 2000 16:06:09 -0400599_iso-8859-1 Hey folks, I need to modify a QMF query that is executed from JCL. The query is currently in production as:

SELECT * FROM DB22SQL.TTRNSCRP A, DB22SQL.TTRNSCRP B WHERE A.C_UDOCNUM = B.C_UDOCNUM AND A.C_DOC_TYPE = B.C_DOC_TYPE AND NOT A.C_UDOCNUM = '00000000' AND NOT A.CPK_MRNO = B.CPK_MRNO

I need to add a table and extract one column from this table. I tried explicitly naming all of the columns instead of * in the select clause and I tried coding a sub-select. The problem is it uses alot of resources. From what I've read, a Union cannot be used. Would anyone have [...] 9876 59 19_Re: QMF batch query16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Mon, 17 Apr 2000 13:24:28 -0700619_- Have you tried

SELECT A.*, B.*, C.? FROM DB22SQL.TTRNSCRP A, DB22SQL.TTRNSCRP B, YOURNEWTABLE C ...



> -----Original Message----- > From: Kalman, Jeff [SMTP:JKalman@UNCH.UNC.EDU] > Sent: Monday, April 17, 2000 1:06 PM > To: DB2-L@RYCI.COM > Subject: Re: QMF batch query > > Hey folks, > I need to modify a QMF query that is executed from JCL. The query is > currently in production as: > > SELECT * FROM DB22SQL.TTRNSCRP A, DB22SQL.TTRNSCRP B > WHERE A.C_UDOCNUM = B.C_UDOCNUM > AND A.C_DOC_TYPE = B.C_DOC_TYPE > AND NOT A.C_UDOCNUM = '00000000' > AND NOT A.CPK_MRNO = B.CPK_MRNO > > I need [...] 9936 23 43_Alternatives to Erwin / Cayenne, anyone ???14_Shaul Bergfeld18_sbergfeld@TACT.COM31_Mon, 17 Apr 2000 16:40:07 -0400442_iso-8859-1 Hello, fellow Listers !

Can anyone suggest alternatives to the Erwin or Cayenne products (are these the "old" names, now that CA appears to have taken them over ???).

Specifically, I'm interested in the ability to create a logical database chart, and aslo to do so from the DDL definitions. More specifically, I'm interested in "financially" advantageous alternatives. Any and all help will be most appreciated. [...] 9960 82 38_Re: Number of Months Between Two Dates26_Marco Túlio de Vasconcelos28_marcotulio@SASSECAIXA.COM.BR31_Mon, 17 Apr 2000 17:41:08 -0300474_iso-8859-1

I did a test with the command below and i found problems:

"Select month(current date - '2000-01-25') from sysibm.sysdummy1" .

If the difference between dates is smaller than 12 months, the command result is ok. But, if the difference is equal 12 the command returns 0 (zero). And if the difference is equal 13 the command returns 1 (one). Why does that happen? This command is working correct when we have a period smaller than 1 year. [...] 10043 24 7_VARCHAR12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Mon, 17 Apr 2000 16:01:23 -0500436_iso-8859-1 We have never used varchar in indexes before but we seem to have a case for using them now.

Does any one have any opinions about why varchar in indexes would a bad idea aside from the 2-byte overhead and update with larger lengths ? One reason we don't want to have a fixed length column is we don't want to force the customer to pad their parameters when running ad hoc queries or force them to always use like. [...] 10068 13 50_Re: -101 sqlcode after conversion to DB2 V6 OS/3906_Jim C.17_Jimluxpax@AOL.COM29_Mon, 17 Apr 2000 17:05:14 EDT374_US-ASCII We had the same problem - although we're running 6.1 UDB on an NT platform not OS/390; maybe the solution is similar. There is a db config parameter called stmntheap in UDB which defines how much workspace is available for the SQL compiler. We doubled the value and the problem disappeared. Maybe there is a comparable config parameter for OS/390? HTH, Jim C. [...] 10082 56 47_Re: Alternatives to Erwin / Cayenne, anyone ???0_18_damcon2@US.IBM.COM31_Mon, 17 Apr 2000 17:10:04 -0400503_us-ascii Take a look at ER/Studio by Embarcadero. It's got some great functionality and costs about half what ERWin does.

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



Shaul Bergfeld @RYCI.COM> on 04/17/2000 04:40:07 PM [...] 10139 48 11_Re: VARCHAR20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 17 Apr 2000 17:12:06 -0400292_us-ascii The VARCHAR in the index are not kept as VARCHARS but as fixed length columns. That is why you can never get an index only access path when you have VARCHAR as indexed column unless you change your default ZPARM setting to reflect RETVLCFK=YES. How big is your VARCHAR column ? [...] 10188 67 38_Re: Number of Months Between Two Dates14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 17 Apr 2000 17:20:04 -0400697_iso-8859-1 The following should work:

SELECT DECIMAL(SUBSTR(PERIOD,1,4),5,0) * 12 + DECIMAL(SUBSTR(PERIOD,5,2),5,0) FROM ( SELECT DIGITS(CURRENT DATE - '1998-02-12') AS PERIOD FROM SYSIBM.SYSDUMMY1) AS A;



Manas.



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Marco Túlio de Vasconcelos > Sent: Monday, April 17, 2000 4:41 PM > To: DB2-L@RYCI.COM > Subject: Re: Number of Months Between Two Dates > > > > I did a test with the command below and i found problems: > > "Select month(current date - '2000-01-25') from sysibm.sysdummy1" . > > If the difference between dates is smaller than 12 months, [...] 10256 17 33_Re: Moving dbs between NT servers6_Jim C.17_Jimluxpax@AOL.COM29_Mon, 17 Apr 2000 17:20:05 EDT574_US-ASCII Greg & Joel,

Greg, Thanks for responing. Yes, we realized pretty early on that a redirected restore wasn't going to work and ended up going the DDL/db2move route. What I'm still wondering is whether it is possible to copy a backup image from one server to another (so it then qualifies as local, as you mentioned Greg) and then restore it on that target server. I've tried this a couple times and I get a message that says the timestamp on the backup image doesn't match anything in the recovery history file. Seems it's only possible to restore to the [...] 10274 90 79_Re: Lowering the Priority of a Distributed Query after it has beg un e xecution13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Mon, 17 Apr 2000 16:17:47 -0500397_iso-8859-1 Hello,

We are in the same boat, we'd like some protection from resource intensive distributed requests, and have considered WLM classes as Jeff describes. We have a situation however, where some of our distributed applications' threads always stay active (for hours!!) They are transactional in nature, and we don't want to degrade their performance as they accumulate CPU. [...] 10365 68 11_Re: VARCHAR12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Mon, 17 Apr 2000 16:30:25 -0500448_iso-8859-1 The varchar column is small and could be from 3 to 12 characters but the table will be huge. There will be other columns in the index which will be fixed length. The index will contain Fiscal Year, Fiscal Month, Account, Department and Fund and this other column, center. This table will be in our data warehouse and will contain all of the Accounting transactions for several years. I'm thinking there will be 10+ million of them. [...] 10434 59 38_Re: Number of Months Between Two Dates14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Tue, 18 Apr 2000 07:33:49 +1000483_us-ascii Scott, I think what your after is something referred to as a 'duration'. See the DB2 for OS/390 V5 SQL Reference section 3.14.8 . There are a number of types, but you may be after something called a 'date duration'. If you subtract two dates the result is a decimal(8,0) number in format yyyymmdd . For example, if I tried 'Select current date - '01.01.2000' from sysibm.sysdummy1' (from down in Oz today) what I would get is 317. This represents 3 months and 17 days. [...] 10494 39 33_Re: Moving dbs between NT servers13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Mon, 17 Apr 2000 16:39:36 -0500580_US-ASCII Hello Jim, I had the same problem as well, all you have to do is create the backup directory structure in UPPER CASE. Use a dos prompt and the mkdir command. HTH Kurt

>>> Jimluxpax@AOL.COM 04/17/00 04:20PM >>> Greg & Joel,

Greg, Thanks for responing. Yes, we realized pretty early on that a redirected restore wasn't going to work and ended up going the DDL/db2move route. What I'm still wondering is whether it is possible to copy a backup image from one server to another (so it then qualifies as local, as you mentioned Greg) and then restore it on [...] 10534 26 23_regarding last updation17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Mon, 17 Apr 2000 16:26:45 PDT296_- Hi Folks,

In our shop we are using DB2 V4. There are some of the records are updated in the table in Test region. i want to know when the table was last updated and who updated the data. like by catlog tables.... if so which catlog table.

can somebody help me in this issue. [...] 10561 82 33_Re: Moving dbs between NT servers0_29_Greg.Palgrave@BANKWEST.COM.AU31_Tue, 18 Apr 2000 08:30:32 +0800367_us-ascii Jim,

It *is* possible, by copying the full directory structure to the other server. BUT, having said that, I get the same problem as you 'no file matching timestamp found....' when I try the restore. The frustrating part is that the other DBA in our team can do the same copy of directory to another machine, and run the restore without problem! [...] 10644 52 33_Re: Moving dbs between NT servers11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Tue, 18 Apr 2000 08:28:13 +0800495_us-ascii Hi Jim, You must copy the backup image from the source server (important: include the directory where your backup image were.) to the target server. This will do the tricks.

Good Luck Roland









"Jim C." on 18/04/2000 05:20:05 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) Subject: Re: Moving dbs between NT servers [...] 10697 50 27_Re: regarding last updation14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 18 Apr 2000 10:55:10 +1000468_iso-8859-1 Ravi,

This question was raised in the last few weeks. Why don't you try searching the archives to see the suggestions that were raised then?

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au

-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Tuesday, April 18, 2000 9:27 AM To: DB2-L@RYCI.COM Subject: regarding last updation [...] 10748 95 11_Re: VARCHAR14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Tue, 18 Apr 2000 11:13:38 +1000481_us-ascii Louise,

Fixed length columns have no disadvantages over VARCHAR for short columns and only advantages, e.g. 1. Allows IX ONLY access. 2. Doesn't waste 2 bytes to hold the length in the data. 3. Allows you to choose fixed or compressed on a record level basis.

Customers will not be forced to pad their parameters. Trailing blanks are assumed. They don't have to use LIKE. They can use = for example. I probably don't understand what you are getting at. [...] 10844 36 32_Database/Instance mix in DB2/AIX12_Raymond Bell20_raymondb@NZ1.IBM.COM31_Tue, 18 Apr 2000 14:18:23 +1200465_us-ascii Colleagues,

A general question for those of you with experience in a DB2/AIX environment. Or any RDBMS/Unix environment, I suspect. We're drawing up plans to create a new environment that has several Development databases (for both unit and system testing), a QA-type database, a very-Production-like Education database and, of course, the real McCoy Production database. Complexity and cost are the main drivers, both being kept to a minimum. [...] 10881 39 3_UDT10_Feng Zhang23_zhang_feng@HOTVOICE.COM31_Mon, 17 Apr 2000 20:04:39 -0700514_- Hi, all:

Thanks in advance. Recently we account a problem in version 6 for OS390. In SYSIBM.SYSCOLUMNS catalog table, a colume called "TYPENAME" is used to store the User Defined Type name(distinct type). The type for this colume is VARCHAR18, but the dec len only 18 instead of 20(The manual did not count the two bytes length filed as the colume's length). No matter how long the real length of the UDT is, the two bytes length field always keep 18 and no change. Anybody know any reason for this? [...] 10921 114 33_Re: Moving dbs between NT servers18_Gert van der Kooij15_geko@WANADOO.NL31_Tue, 18 Apr 2000 08:51:54 +0200290_iso-8859-1 Hi, We always recover db's on other servers without any problem. What zip the entire directory structure, unzip it and no problems at all. Do you use the same database name on both servers, if so that may be the cause of your problem. Just drop the old database to restore. [...] 11036 45 50_Re: -101 sqlcode after conversion to DB2 V6 OS/3900_29_paul.packham@POSTOFFICE.CO.UK31_Tue, 18 Apr 2000 08:57:47 +0000444_us-ascii Jim,

As far as I can gather in OS/390 the equivalent is to turn off query parallelism.

Regards, Paul







Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Paul Packham/ITS/POSG/POSTOFFICE) Hard Copy To Hard Copy cc From "Jim C." Date 17/04/2000 21:05

Subject: Re: -101 sqlcode after conversion to DB2 V6 OS/390 [...] 11082 12 47_Re: Alternatives to Erwin / Cayenne, anyone ???19_Theo van Westrienen36_theo.van.westrienen@NL.MARTINAIR.COM31_Tue, 18 Apr 2000 02:46:26 -0500447_- ER/Studio from Embarcadero can be a very nice product for a very nice price, but it does not support DB2 for OS/390 at this moment. An announcement for DB2 for OS/390 support is made for later this year.

================================================ 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. 11095 38 4_none17_Kenneth J. Kripke22_kkripke@MINDSPRING.COM31_Tue, 18 Apr 2000 05:42:18 -050046_iso-8859-1 search DB2-L DB2 7.1 FROM TODAY-100 11134 22 15_Backup/Restore.9_Ted Pesta18_tpesta@AMQUEST.COM31_Tue, 18 Apr 2000 06:27:04 -0400427_iso-8859-1 We are DB2 V4, OS/390. My management wants to start using something called "snapshot" to backup the volumes. I'm concerned that DB2 won't be able to recover tablespaces from this product. Has anyone used this product and successfully recovered a tablespace from it?

I've always used the DB2 imagecopies for recovery. Are there any issues if I use datasets recovered outside of DB2 ? ( levelid and such ) [...] 11157 97 38_Re: Number of Months Between Two Dates24_Peter_Schwarcz (Bigpond)26_Peter_Schwarcz@BIGPOND.COM31_Tue, 18 Apr 2000 20:37:16 +1000607_us-ascii try this

select year(current date - date('1988-02-01'))*12 + month(current date - date('1988-02-01')) from sysibm.sysdummy1

which converts the labled duration returned from the subtraction of two dates to the dufference in years and and months

Regards Peter Schwarcz PO Box 426, Hawthorn Victoria Australia 3122 Peter_Schwarcz@bigpond.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Paul A Redhead Sent: Tuesday, 18 April 2000 7:34 AM To: DB2-L@RYCI.COM Subject: Re: Number of Months Between Two Dates [...] 11255 76 19_Re: QMF batch query12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 18 Apr 2000 07:49:59 -0400676_iso-8859-1 Duane, Thank you for taking the time to reply. I'll give it a try.

Jeff

-----Original Message----- From: Duane Lee - ATCX [mailto:DLee@MAIL.MARICOPA.GOV] Sent: Monday, April 17, 2000 4:24 PM To: DB2-L@RYCI.COM Subject: Re: QMF batch query



Have you tried

SELECT A.*, B.*, C.? FROM DB22SQL.TTRNSCRP A, DB22SQL.TTRNSCRP B, YOURNEWTABLE C ...



> -----Original Message----- > From: Kalman, Jeff [SMTP:JKalman@UNCH.UNC.EDU] > Sent: Monday, April 17, 2000 1:06 PM > To: DB2-L@RYCI.COM > Subject: Re: QMF batch query > > Hey folks, > I need to modify a QMF query that is executed from JCL. The query is > currently in [...] 11332 57 19_Re: Backup/Restore.15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 18 Apr 2000 12:38:22 +0100504_us-ascii Hello Ted,

I suggest you read the redbook called "Using RVS and SnapShot for Business Intelligence Applications with OS/390 and DB2' (SG245333).

You can get it on http://www.redbooks.ibm.com.

Regards.

Toine Michielse DB2 for OS/390 Technical Specialist Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



Ted Pesta @RYCI.COM> on 04/18/2000 11:27:04 AM [...] 11390 30 28_Stored Procedure Performance19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 18 Apr 2000 07:44:27 -0500476_iso-8859-1 Hi all and TIA, DB2 UDB/OS390 V6. Has anyone done any benchmarking of non-declarative RI contained in a SP vs application code? Am I right in thinking that there probably isn't much difference? I'm aware of the advantages of using SPs over application code for non-declarative RI; I'm trying to find any performance advantage(s). I've searched the Redbooks and didn't get any hits for V5 or V6. Does anyone know of any references to performance doc about SPs? [...] 11421 112 11_Re: VARCHAR12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Tue, 18 Apr 2000 08:27:38 -0500559_iso-8859-1 Thanks - I think I was having a blonde moment. I forgot about the assuming trailing blanks. :)

-----Original Message----- From: Michael Hannan [mailto:mhannan@C031.AONE.NET.AU] Sent: Monday, April 17, 2000 8:14 PM To: DB2-L@RYCI.COM Subject: Re: VARCHAR



Louise,

Fixed length columns have no disadvantages over VARCHAR for short columns and only advantages, e.g. 1. Allows IX ONLY access. 2. Doesn't waste 2 bytes to hold the length in the data. 3. Allows you to choose fixed or compressed on a record level basis. [...] 11534 62 50_Fwd:Re:Alternatives to Erwin / Cayenne, anyone ???11_Ken Liberty26_KLiberty@CONSULTEC-INC.COM31_Tue, 18 Apr 2000 09:21:41 -0400606_US-ASCII We have Cayenne products (Bachman & Terrain Map), both products are cumbersome and difficult to use, but in many cases they will do the job of providing a Data Model and a limited Data Dictionary for your environment. Currently, we are reviewing alternatives to Bachman also. There are a series of Data Modeling Tools out there, but your shop's requirements and the amount of money that you want to spend will of course drive which product works best for you. The primary need for our organization is for DB2 OS/390 support, with the flexibility of supporting other databases. I've evaluated [...] 11597 68 19_Re: Backup/Restore.14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Tue, 18 Apr 2000 22:17:36 -0400742_us-ascii You can also reference Implementing DFSMSdss SnapShot and Virtual Concurrent Copy , SG24-5268-00 - 1998-06-07 @ http://www.redbooks.ibm.com/pubs/pdfs/redbooks/sg245268.pdf

HTH

Bill G.



Toine Michielse wrote:

> Hello Ted, > > I suggest you read the redbook called "Using RVS and SnapShot for Business > Intelligence Applications with OS/390 and DB2' (SG245333). > > You can get it on http://www.redbooks.ibm.com. > > Regards. > > Toine Michielse > DB2 for OS/390 Technical Specialist > Santa Teresa Laboratory > Mobile: +31 6 537 23 256 > Email: vndobtm@us.ibm.com > Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS > > Ted Pesta @RYCI.COM> on 04/18/2000 11:27:04 AM > > [...] 11666 63 36_Re: Database/Instance mix in DB2/AIX14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Tue, 18 Apr 2000 07:27:07 -0700612_iso-8859-1 You may want to consider having multiple instances per box. The reason for this is two fold. First, certain memory areas in UDB are allocated at the instance level, then doled out to each database. This could allow a poorly performing database to monopolize the instance. Second, in UDB, SYSADM, SYSMAINT and SYSCTRL (the three most powerful authority levels) are defined, at the INSTANCE level, based on membership of a particular UNIX group. Because of this, if you have one instance with 2 databases, everyone that has one of these authorities will have it in both databases. Hope this helps. [...] 11730 49 33_Re: Moving dbs between NT servers0_22_rmadan@CYVEILLANCE.COM31_Tue, 18 Apr 2000 10:34:45 -0400333_iso-8859-1 This can be accomplished by making the backup drive the local drive on the server to be restored to. You can do this using the "at" and net use commands. Once the backup drive of the source server is local you can do your restore as normal. I do this all the time with a pretty good sized database..over a 150 + gig. [...] 11780 97 19_Re: Backup/Restore.9_Ted Pesta18_tpesta@AMQUEST.COM31_Tue, 18 Apr 2000 10:53:27 -0400544_iso-8859-1 Toine and William, Thank you very much. That was the information I needed.

Ted Pesta

-----Original Message----- From: William Gannon [mailto:wgannon@INET-SERVICES.PALMBEACH.K12.FL.US] Sent: Tuesday, April 18, 2000 10:18 PM To: DB2-L@RYCI.COM Subject: Re: Backup/Restore.

You can also reference Implementing DFSMSdss SnapShot and Virtual Concurrent Copy , SG24-5268-00 - 1998-06-07 @ http://www.redbooks.ibm.com/pubs/pdfs/redbooks/sg245268.pdf

HTH

Bill G.



Toine Michielse wrote: [...] 11878 56 16_DataJoiner 2.1.111_Ahl, Dana J19_AHLDANAJ@MATTEL.COM31_Tue, 18 Apr 2000 07:47:57 -0700393_iso-8859-1 I am in the process of setting this up on an NT Server to replicate Oracle to OS/390 DB2. I have followed the instructions in the manuals and have succeeded to map Oracle and the mainframe DB2. My problem comes when I attempt to select the Oracle as source. Can anyone help me. I seem to be stumped at this point. Maybe I just am missing a sequence or instruction. Thanks in advance 11935 31 13_Re: Batch QMF12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 18 Apr 2000 11:09:27 -0400530_iso-8859-1 Good morning everybody, I am running a job that executes a QMF proc that executes a QMF query and generates a report. When executing a -904 SQLCODE and a 00C900A5 for a reason code is returned. The explanation for this error is that the temporary file is full or the maximum number of extentions for a VSAM dataset(s) has been exceeded for all temporary files or the volume(s) has no space available in which to extend. Is this a site problem due to lack of disk space or is there something else I should look at? [...] 11967 96 31_Re: Online Reorg Space Problems12_Greg Popwell27_greg.popwell@MOTION-IND.COM31_Tue, 18 Apr 2000 10:14:17 -0500325_iso-8859-1 Dr. Ebert,

Thanks for the reply. You are correct. I posed the question to IBM. They said that for DB2 managed datasets, the shadow dataset allocation is the same size as the entire NPI. However, if your datasets are user-managed, you can pre-allocate them to be only as large as the logical partition. [...] 12064 56 13_Re: Batch QMF13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Tue, 18 Apr 2000 10:19:09 -0500491_iso-8859-1 Hello Jeffrey,

The work files (DSNDB07 in non-data sharing environ) have reached maximum capacity. This occurs when SQL requires sorts etc. The situation can be a result of the 'mix' of applications at the time, or by one big hog (maybe even the QMF query itself).

The answer is to either increase the workfile space, or tune the query(s), or both. Remember that some 'poor' SQL can have huge demands, and a failure of this type is not necessarily a bad thing. [...] 12121 73 13_Re: Batch QMF12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 18 Apr 2000 11:22:23 -0400527_iso-8859-1 Dave, Thank you for your reply. I will look into it.

-----Original Message----- From: Shapiro, Dave [mailto:Shapiro.Dave@PRINCIPAL.COM] Sent: Tuesday, April 18, 2000 11:19 AM To: DB2-L@RYCI.COM Subject: Re: Batch QMF



Hello Jeffrey,

The work files (DSNDB07 in non-data sharing environ) have reached maximum capacity. This occurs when SQL requires sorts etc. The situation can be a result of the 'mix' of applications at the time, or by one big hog (maybe even the QMF query itself). [...] 12195 61 7_Re: UDT17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Tue, 18 Apr 2000 10:31:09 -0500422_ISO-8859-1 Hi Feng,

Two things:

1) If you create a column as VARCHAR(18) the 'LENGTH' column of SYSIBM.SYSCOLUMNS will be populated with 18 ... not 20. If you create a VARCHAR(100) it will be populated with 100. Same thing with UDTs (but check out SYSDATATYPES too). That's not a reflection of the amount of storage used, it's the column definition. Take a look at DECIMAL columns for similar examples. [...] 12257 45 19_Re: Backup/Restore.0_20_John_Lendman@FPL.COM31_Tue, 18 Apr 2000 11:31:28 -0400664_- The short answer is it does work, but you have to do some things different and you have to plan on this type of recovery.

John







"Ted Pesta" on 04/18/2000 06:27:04 AM

Please respond to "DB2 Data Base Discussion List"

To: DB2-L@RYCI.COM cc:

Subject: Backup/Restore.







We are DB2 V4, OS/390. My management wants to start using something called "snapshot" to backup the volumes. I'm concerned that DB2 won't be able to recover tablespaces from this product. Has anyone used this product and successfully recovered a tablespace from it? [...] 12303 16 17_Nullid collection12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Tue, 18 Apr 2000 10:53:00 -0400553_- How is your shop handling PC products with objects in DB2/OS390? For example: Business Objects. I have seen one entry in the archives where someone was planning to implement copying the packages to named collections. Is this worth doing and what advantages/problems will this create?

Thanks in advance.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 12320 19 47_Is there an easy way in SQL to get Julian date?9_Ken Paris17_KenParis@LVCM.COM31_Tue, 18 Apr 2000 09:12:29 -0700346_US-ASCII Is there an easy way, using SQL, to reformat the CURRENT DATE into a julian date (YYDDD). We'll be using version 6 of DB2/MVS (I don't have manuals) by the time we implement.

Thanks

----- Sent using MailStart.com ( http://MailStart.Com/welcome.html ) The FREE way to access your mailbox via any web browser, anywhere! [...] 12340 32 27_DB2 Version 7 announcements12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Apr 2000 11:01:37 -0500504_- Here are the primary URLs for the announcements. The Launch page has been out for a week: http://www.ibm.com/software/data/launch/

The announcement letters came out today. Preview: IBM DB2 Server for VSE & VM, Version 7 Release 1 (10.2KB) http://isource.ibm.com/cgi-bin/goto?it=usa_annred&on=200-085

IBM DB2 UDB Server for OS/390, Version 7 Delivers the Power for Your e-business and Data Warehouse Applications (115.3KB) http://isource.ibm.com/cgi-bin/goto?it=usa_annred&on=200-087 [...] 12373 36 51_Re: Is there an easy way in SQL to get Julian date?13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 18 Apr 2000 11:21:50 -0500535_US-ASCII Hello Ken, SELECT DAYS(CURRENT_TIMESTAMP) - DAYS(SUBSTR(CHAR(CURRENT_TIMESTAMP),1,4)||'-01-01') + 1 FROM SYSIBM.SYSDUMMY1 HTH Kurt



>>> KenParis@LVCM.COM 04/18/00 11:12AM >>> Is there an easy way, using SQL, to reformat the CURRENT DATE into a julian date (YYDDD). We'll be using version 6 of DB2/MVS (I don't have manuals) by the time we implement.

Thanks

----- Sent using MailStart.com ( http://MailStart.Com/welcome.html ) The FREE way to access your mailbox via any web browser, anywhere! [...] 12410 37 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Apr 2000 11:31:48 -0500553_- SQLCODE -101 was much more common for moving from V4 to V5, and we found a couple of changes that were possible in a V5 APAR PQ21786. It changed more than 100 modules and caused some other problems. APAR PQ21786 is applicable to V6 as well, but you should be much more current on DB2 V6 service to avoid problems. Right now the ESO 0003 (a.k.a. March PUT tape) is looking relatively good, and the additional APARs for the upcoming refresh are noted in Info APAR II12343. There are six additional APARs noted there now, with only PQ36206 ready to [...] 12448 54 51_Re: Is there an easy way in SQL to get Julian date?14_Arnold, Mark S20_Mark.Arnold@BNSF.COM31_Tue, 18 Apr 2000 11:47:16 -0500459_iso-8859-1 A little messy, but to get the 2 digit year also:

SELECT SUBSTR(CHAR(CURRENT DATE ) , 3 , 2 ) || SUBSTR(DIGITS( DAYS(CURRENT DATE ) - DAYS(SUBSTR(CHAR(CURRENT DATE),1,4)||'-01-01') + 1 ) , 8 , 3 ) FROM SYSIBM.SYSDUMMY1



-----Original Message----- From: Kurt Sahlberg [mailto:Kurt.Sahlberg@EXPERIAN.COM] Sent: Tuesday, April 18, 2000 11:22 AM To: DB2-L@RYCI.COM Subject: Re: Is there an easy way in SQL to get Julian date? [...] 12503 31 51_Re: Is there an easy way in SQL to get Julian date?12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Apr 2000 11:56:57 -0500423_- There is a DAYOFYEAR function built in to DB2 to provide the days. You might want to look at the ALTDATE sample user-defined function to provide your format.

I have two side comments. I'd push at least once more to get four digit years used. Otherwise the windowing techniques for two digit years need to match if there are any dates older than this year. This looks like an accident that is going to happen. [...] 12535 31 28_Unique vs Non Unique Indexes11_Ken Liberty26_KLiberty@CONSULTEC-INC.COM31_Tue, 18 Apr 2000 14:16:14 -0400351_US-ASCII I've done some research on this, but have not been able to find any conclusive information on favoring a Unique Index over an Non Unique Index. The scenario is this.... We have a series of indexes on a table, for example sake I'll say two. Field A is makes the record unique in itself. The indexes in which I have include the following: [...] 12567 73 19_Re: Backup/Restore.9_Ted Pesta18_tpesta@AMQUEST.COM31_Tue, 18 Apr 2000 14:25:19 -0400453_iso-8859-1 John, Are you able to do a point-in-time recovery with this ? Does it take longer than the DB2 recover utility ?

Ted Pesta

-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Tuesday, April 18, 2000 11:31 AM To: DB2-L@RYCI.COM Subject: Re: Backup/Restore.

The short answer is it does work, but you have to do some things different and you have to plan on this type of recovery. [...] 12641 23 21_information about ddf24_Helio Pimpinato da Rocha20_hrocha@TELESP.COM.BR31_Tue, 18 Apr 2000 15:19:42 -0300276_us-ascii Hi All

This is my first time here, and I need some help. I need to connect two DB2 V5.1 and they are in different CPUs. I think to use the DDF, but many people took me that it is not very good. What are you opinion about it? Is there another possibility? [...] 12665 29 62_Is this a valid way to consolidate UDB tablespace containers ?13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Tue, 18 Apr 2000 18:34:10 +0100459_us-ascii I've been thinking of various ways to redefine (downwards) the number of tablespace containers in a tablespace.

This approach came to me, I tried it and it seems to work. Is it valid ? (syntax squashed for briefness)

backup database dbname tablespace ts1; restore database dbname ... tablespace ts1 redefine; redefine container; restore database dbname continue; rollforward database dbname tablespace ts1 to end of logs and stop; [...] 12695 53 21_Re: Nullid collection14_McCombs, Terry20_tmccomb@SEDGWICK.GOV31_Tue, 18 Apr 2000 13:39:39 -0500562_iso-8859-1 I was hoping some others would answer this, as I'm also very interested in what people are doing. We started off using the default NULLID and it quickly got out of hand. We literally would have two developers with slightly different versions of something that would require the same package to be bound with different tokens (requiring one of the developers to upgrade to the same thing the other was using). In addition, I quickly lost track of what packages belonged to what version of CAE and who was using what and what was obsolete and .... [...] 12749 29 25_Re: information about ddf0_31_truman.g.brown@BELLATLANTIC.COM31_Tue, 18 Apr 2000 14:45:18 -0400589_us-ascii Helio,

I'm not sure what you mean by "connect two DB2s". DDF allows one DB2 to become a remote server to another DB2, and vice-versa. De- pending on activity rates and the amount of data transferred via DDF (a VTAM application), performance may (or may not) suffer. We use DDF in quite a few subsystems with no problems. Other applications have elected to extract data from one subsystem and reload it into tables in the "host" DB2, thus eliminating the DDF/VTAM piece; this seems to work better for us with very large tables where very high data transfer rates would [...] 12779 103 19_Re: Backup/Restore.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 18 Apr 2000 13:46:16 -0500585_iso-8859-1 John, If I may "piggy-back" on this question . . . Do you find that recovery with a concurrent copy is similar in elapsed time to using a Image Copy?

Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately [...] 12883 54 48_-904 sqlstate 57011 with degree(any) || db2 v5.110_Derek Lund22_derek.lund@UTORONTO.CA31_Tue, 18 Apr 2000 14:48:26 -0400357_US-ASCII We have recently migrated from DB2 V4.1 to V5.1 (in the last month) and we are getting some production programs abending with a -904 sqlstate 57011.

When we bind the program using DEGREE(1), the program runs for 1 minute elapsed and approx 10 seconds cpu. If we bind the program with DEGREE(ANY), the program abends almost immediately. [...] 12938 33 47_Central PA DB2 Users Group Meeting - April 27th10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 18 Apr 2000 15:26:31 -0400377_us-ascii April 27th is the next Central PA DB2 Users Group. We have a great line-up of IDUG presentors including: Willie Favero, BMC Software Joseph Fordham, United Parcel Service David Ayers, Highmark Phil Gunning, Boscov's Department Store

The meeting is FREE.

If you are interested in attending, please email Cathy.Peck@Highmark.Com or call 717-975-7396. [...] 12972 83 21_Re: Nullid collection13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Tue, 18 Apr 2000 14:48:58 -0500631_iso-8859-1 We are considering multiple collections as a way of 1) providing flexibility with the Resource Limit Facility (via collection names) and 2) allowing transactional type apps to have dynamic stmts cached, but not allowing others (through bind options) in order to conserve DBM1 memory requirements.

The down side: we are loathe to add a multitude of data sources (to accommodate various package sets) because it would introduce the need to manage/administer them from a developers point of view (i.e. which set should be used for which application, change management, service level thresholds, standards etc). [...] 13056 39 23_FW: PeopleSoft Recovery13_Dayna Brennan25_Dayna.Brennan@STATE.MN.US31_Tue, 18 Apr 2000 14:47:54 -0500599_iso-8859-1 > -----Original Message----- > From: Dayna Brennan > Sent: Tuesday, April 18, 2000 2:40 PM > To: 'Lockwood_Lyon@AMWAY.COM'; 'tim.lowe@stpaul.com'; 'db2l@ryci.com' > Subject: PeopleSoft Recovery > > Fellow PeopleSoft DBA's > > Have you ever done a full application recovery of PS HR 7.5? If so, how > long does it take? I would appreciate a quick answer, my manager wants > some backup of my estimates. We have about 50,000 employees and will be > doing a full restore with Production Data at our next Disaster Recovery. > We currently take 8 hours to recover a very old version with [...] 13096 76 52_Re: -904 sqlstate 57011 with degree(any) || db2 v5.19_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM31_Tue, 18 Apr 2000 13:57:29 -0600481_iso-8859-1 Derek,

As you know, when you specify DEGREE(ANY) you enable parallelism which, in most cases requires a significant increase in EDM Pool resources (50 to 70%). If you truly want to use parallelism you should enlarge your EDM Pool.

Good Luck, Don Alden

-----Original Message----- From: Derek Lund [mailto:derek.lund@UTORONTO.CA] Sent: Tuesday, April 18, 2000 12:48 PM To: DB2-L@RYCI.COM Subject: -904 sqlstate 57011 with degree(any) || db2 v5.1 [...] 13173 43 33_PeopleSoft Recovery - more detail13_Dayna Brennan25_Dayna.Brennan@STATE.MN.US31_Tue, 18 Apr 2000 15:09:07 -0500611_iso-8859-1 I forgot to include that we are running on DB2 for OS/390 Version 5 and using BMC Recovery Manager to generate the recover jobs. We have the tables split out 1 per tablespace, so we have roughly 3600 tablespaces including our custom tables. > -----Original Message----- > From: Dayna Brennan > Sent: Tuesday, April 18, 2000 2:40 PM > To: 'Lockwood_Lyon@AMWAY.COM'; 'tim.lowe@stpaul.com'; 'db2l@ryci.com' > Subject: PeopleSoft Recovery > > Fellow PeopleSoft DBA's > > Have you ever done a full application recovery of PS HR 7.5? If so, how > long does it take? I would appreciate a quick answer, [...] 13217 68 52_Re: -904 sqlstate 57011 with degree(any) || db2 v5.113_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 18 Apr 2000 15:19:08 -0500446_US-ASCII Derek, since you have no information about the -904 how about running logrec and look for software records under PROGRAM ID: 5740XYR00 at the time of the ABEND. This should give you a place to start when looking for a fix. Kurt

>>> derek.lund@UTORONTO.CA 04/18/00 01:48PM >>> We have recently migrated from DB2 V4.1 to V5.1 (in the last month) and we are getting some production programs abending with a -904 sqlstate 57011. [...] 13286 155 21_Re: Nullid collection15_DeMarco, Joseph18_DeMarcoJ@CONED.COM31_Tue, 18 Apr 2000 16:22:40 -0400490_windows-1252 Just a note on your comment “we are loathe to add a multitude of data sources (to accommodate various package sets)”. You can control the collection id of the package executed through the db2cli.ini file (thus the need for a multitude of data sources) or you can set the CURRENTPACKAGESET dynamically via the CURRENTPACKAGESET keyword in the connection string in your app (in other words, use the same data source but set this keyword dynamically in the calling program). [...] 13442 112 52_Re: -904 sqlstate 57011 with degree(any) || db2 v5.110_Derek Lund22_derek.lund@UTORONTO.CA31_Tue, 18 Apr 2000 16:38:09 -0400540_US-ASCII Thanks. We have bumped up EDM pool from 9342K to 24912K. But the fact that the -904 occurs almost immediately seems to indicate that it is not being used.....

btw, all of our packages (approx 3000) have been bound with DEGREE(ANY), but explain output from the plan table shows that only 300 of these would/could possibly use parallelism. I am considering rebinding everything with DEGREE(1) eventually and picking and chosing the exceptions for DEGREE(ANY), but that doesn't me understand why I get the -904 currently. [...] 13555 88 32_Re: Unique vs Non Unique Indexes10_Beck, Norm18_Norm.Beck@NIKE.COM31_Tue, 18 Apr 2000 13:34:28 -0700577_us-ascii Ken,

There are several things happening here that deserve discussion.

1. Index 1 is unnecessary. If it did not exist, index 2 would be the clustering index, by default, since it would be the only index. Be careful here, it is not always the lowest numbered index that is the default clustering index if none is specified, it is the first one created, or actually the one with the lowest identifier. This can be checked in the catalog. If index 2 were used as the clustering index, the order of the data would remain exactly the same since the first [...] 13644 77 32_Re: Unique vs Non Unique Indexes23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 18 Apr 2000 16:50:34 -0400534_- Ken with the setup you have, it is possible to insert following rows into the tables

Field A Field B

row 1 A1 B1 row 2 A1 B2

This would make Field A non-unique, thus violating the business rule (if there is one about the uniqueness of field A). If you want field A to stay unique, you must create a unique index on it. I have not seen any degradation in performance due to defining a 'could be' unique index as non-unique. DB2 would treat it as unique in some way, when it sees FIRSTKEYCARD = FULLKEYCARD. [...] 13722 83 32_Re: Unique vs Non Unique Indexes20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 18 Apr 2000 16:52:39 -0400370_us-ascii The catalog statistics are more important. In this case, which you have mentioned. Index 1 will have FirstkeycardF = CardF of tables

Consider the following SQLs :-

SELECT * FROM TABLE WHERE Field_A = ?

The filter factor = 1/FirstkeycardF ----> if only index 1 is present. The filter factor = 1/ColcardF ---> when index 2 is evaluated. [...] 13806 35 29_V7 Formal Announcements Today16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Tue, 18 Apr 2000 15:51:55 -0400665_iso-8859-1 All,

The official announcement letters went out today.

200-085 Preview: IBM DB2 Server for VSE & VM, Version 7 Release 1 (10.2KB) 200-086 IBM DB2 OLAP Server Version 7.1 Extends Scalability, Usability, and Performance to Your Enterprise (66.2KB) 200-087 IBM DB2 UDB Server for OS/390, Version 7 Delivers the Power for Your e-business and Data Warehouse Applications (115.3KB) 200-089 IBM DB2 Universal Database Version 7.1 with DB2 Warehouse Manager, DB2 Net Search Extender, and DB2 OLAP Starter Kit Delivers Enhanced Function for All DB2 Users (134.1KB) 200-090 IBM QMF for Windows for DB2 Workstation Databases Version 7 -- for All [...] 13842 97 19_Re: Backup/Restore.0_20_John_Lendman@FPL.COM31_Tue, 18 Apr 2000 16:40:42 -0400374_us-ascii You can only do a point in time when you took the SNAP backup. You can SNAP back then do a LOGONLY roll forward to current. AS for the time the SNAP is much quicker.







"Ted Pesta" on 04/18/2000 02:25:19 PM

Please respond to "DB2 Data Base Discussion List"

To: DB2-L@RYCI.COM cc: [...] 13940 132 52_Re: -904 sqlstate 57011 with degree(any) || db2 v5.113_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 18 Apr 2000 16:40:40 -0500342_US-ASCII Derek, you do not need to rebind just run SET CURRENT DEGREE='ANY' in QMF before running your query. Kurt



<<< derek.lund@UTORONTO.CA 04/18/00 03:38PM >>> Thanks. We have bumped up EDM pool from 9342K to 24912K. But the fact that the -904 occurs almost immediately seems to indicate that it is not being used..... [...] 14073 29 36_Variables in SYSIN Select statements12_Jim Harrison12_jimh@QIS.NET31_Tue, 18 Apr 2000 18:17:35 -0400347_us-ascii A contractor came to me today asking if there was a way to use variables in a select statement in a batch jobstream via a SYSIN DD using something like DSNTIAUL. My first thought was to create a proc and simply use the JCL variables. But noooo, the interpreter doesn't touch instream data. At least according to my dated JCL manual. [...] 14103 55 40_Re: Variables in SYSIN Select statements0_19_csutfin@AMSOUTH.COM31_Tue, 18 Apr 2000 17:32:28 -0500468_- Jim, If you have QMF (or some other query tool ) available, you could use that with a varaible in the query.

Carol Sutfin Corporate DBA Amsouth Bank NA (205)326-5214







Jim Harrison on 04/18/2000 05:17:35 PM



Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Carol Sutfin/BIRMINGHAM/AL/AMSOUTH BANK) Subject: Variables in SYSIN Select statements [...] 14159 55 40_Re: Variables in SYSIN Select statements11_Suresh Sane21_data_arch@HOTMAIL.COM29_Tue, 18 Apr 2000 17:40:48 CDT664_- Jim,

SYSIN can contain concatenated DD's. For example, you could use:

SYSIN DD dataset1 (contains constant info) DD dataset2 (you variable info) DD dataset3 (more constant info) etc.

You will then have a step prior to DSNTIAUL that builds this dataset. Not much easier than a program perhaps ...

Suresh

>From: Jim Harrison >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Variables in SYSIN Select statements >Date: Tue, 18 Apr 2000 18:17:35 -0400 > >A contractor came to me today asking if there was a way to use variables in >a select statement in a batch jobstream [...] 14215 80 40_Re: Variables in SYSIN Select statements0_19_Tim.Lowe@STPAUL.COM31_Tue, 18 Apr 2000 17:44:08 -0500539_us-ascii Jim, This is not really a "DB2 answer" to your question, but I had a similar problem many years ago at a different shop where I needed variables in instream sysin statements in procs. Since instream sysin is not allowed in a proc, I created a very simple little program that was input a parm, and wrote that parm out to a temporary dataset, which was then input to the step that needed it. If your substitution is very simple, then perhaps this could work for you? I can't find this right now, but it was very easy to write. [...] 14296 48 36_Variables in SYSIN Select statements14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 19 Apr 2000 10:17:56 +1000333_us-ascii I'm too lazy to write programs, so I use batch ISPF skeleton substitution facility. It requires a REXX (3 lines of code) to request the substitution. Allows several symbolics to be substituted based on PARM string in the JCL or in another file. The only disadvantage is quite a few JCL cards needed for batch TSO/ISPF. [...] 14345 114 32_Re: Unique vs Non Unique Indexes14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 19 Apr 2000 10:18:01 +1000520_us-ascii Several worrying things here.

Firstly ALWAYS define a Cluster index. So called default cluster does not apply in all circumstances. I have seen Reorgs that do not recluster the data because no Cluster index defined.

Cluster indexes don't have to be unique but are more effective if not too many duplicates. e.g. If CARDF = 1,000,000 and clusterindex has FULLKEYCARDF = 10 then each key value has on average 100,000 duplicates that are essentially in random sequence. So clustering is wasted. [...] 14460 125 32_Re: Unique vs Non Unique Indexes14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 19 Apr 2000 10:37:20 +1000656_us-ascii Venkat,

The Catalog Stats. are important but now that we have changed the topic ...

I find the Optimizer seems to use 1/COLCARDF in preference to 1/FIRSTKEYCARDF for equals predicate on first column of a composite index, seemingly ignoring FIRSTKEYCARDF for most things. Normally we didn't notice since COLCARDF set to same value as FIRSTKEYCARDF. I begin to think FIRSTKEYCARDF is just a historical thing, similar to CLUSTERED not being very relevant any more. Anyway most people should have FIRSTKEYCARDF and COLCARDF matching anyway. For a one column index DB2 should use 1/FULLKEYCARDF. Perhaps I missed what you are trying [...] 14586 55 43_Implementing a DB2 OS/390 V6 Data Warehouse13_Olson, Carlos14_COlson@QRS.COM31_Tue, 18 Apr 2000 17:42:49 -0700593_iso-8859-1 Hello Everyone,

> We plan to replace our current multi-dimensional OLAP database on NT with > a star schema data warehouse on DB2 OS/390 V6 consisting of a central > weekly SALES fact table containing 4+ billion rows and 3 dimension tables > - PRODUCT, LOCATION, and TIME containing 1.5 million, 10000, and 3650 rows > respectively. Another DB2 OS/390 transactional database will feed the > SALES fact table with 40+ million new rows weekly. The SALES fact table > will contain a rolling 108 weeks worth of data. > > For the SALES fact table, I'm considering creating a [...] 14642 26 22_Re: DB2 v6 parallelism12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 18 Apr 2000 21:05:10 -0500399_- PeopleSoft 7.0 and 7.5 have changed, and the current recommendation is to use dynamic statement caching, as long as you are reasonably current on service. You do need more space in EDMPOOL, but there is substantial savings in cpu time.

Do you have the red book? This one is for 7.5. Planning to Install PeopleSoft with DB2 for OS/390, SG24-5156-01 1999-08-31 Download it from the web. [...] 14669 21 33_Re: Moving dbs between NT servers7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Wed, 19 Apr 2000 00:45:16 -0500506_- Sorry if I am not really clear on the discussion.

We are using UDB 6.1 on NT running on server A. I install the DB2 and create the database on drive F in server A. Now we have another spare server, server B.

If I copy the whole directory structure of F: and restore them in server B (note that I am not using DB2 image copy). Does that mean I can use the database in server B already? Do I have to do anything to server B beforehand? Do I have to install UDB on server B and configure [...] 14691 52 33_Re: Moving dbs between NT servers11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Wed, 19 Apr 2000 14:06:48 +0800432_us-ascii Hi Eric, Yes, you need to install UDB 6.1 in Server B and you must make sure that the code page is the same for both UDB . After that you need to create database using the "Database from backup" option.

Good Luck.

Roland Chua











Eric Ng on 19/04/2000 01:45:16 PM

Please respond to DB2 Data Base Discussion List [...] 14744 31 37_Questions in regarding sparse indexes9_Paul Yuen16_pyuen@GIO.COM.AU31_Wed, 19 Apr 2000 16:07:58 +1000518_us-ascii Dear List, It's been requested by our users to tag on a new column to two of our large tables (DB2 V5 OS/390), initially there'll be a lot of unknown values in the new column but it'll be updated with new values in the next few months. Most of the searching criteria will probably be on this new column alone. I've kicked around different approaches, 1) make the column nullable, and create a unique where not null index 2) tag on the new column onto a high cardinality column and make it into an index. [...] 14776 15 33_Re: Moving dbs between NT servers7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Wed, 19 Apr 2000 03:24:11 -0500492_- Thank you, Roland. But like I said in my previous email, if I backup the whole directory (the place where I install UDB plus create database) using eg Seagate backup. Would I be able to restore to another server, bypassing the DB2 control totally ?

================================================ 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. 14792 95 41_Re: Questions in regarding sparse indexes10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 19 Apr 2000 09:02:44 GMT584_us-ascii Hi Paul, There are number of things to be taken care in these type of cases. First of all defining the index on the updated columns is a big tradeoff and you have to look how many indexes currently exists on these tables and how many applications are inserting in this table and updating the indexeable columns.Whether these applications are online or batch.If most of the applications are batch then i think there is no problem creating the index.If most of the applications are online then you should try and identify the impact on those applications (if it goes slow [...] 14888 88 32_Re: Unique vs Non Unique Indexes10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 19 Apr 2000 09:14:08 GMT487_us-ascii Hi all, All the answers are quite explainatory but i think what Sanjay said is very useful in this case. "Addition or removal of column(s) from unique index can destroy the uniqueness." But for few statements i want to clarify my doubts : 1) How FIRSTKEYCARD and CLUSTERED are becoming historical things ? . 2) How does it matter to optimizer looking at either firstkeycard or colcard ? , if both are same.Runstat is current and in sync for both the table and its indexes. [...] 14977 101 22_Re: Extents question ?0_18_mebert@AMADEUS.NET31_Wed, 19 Apr 2000 12:05:30 +0100437_us-ascii I don't remember seeing a response to this one so... I'm virtually certain that table-/indexspace statistics are NOT affected by VSAM extents.

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany







From: "Trivedi, Kaushal J, BGM" on 13/04/2000 16:11 GMT

Please respond to DB2 Data Base Discussion List [...] 15079 67 40_Re: Variables in SYSIN Select statements17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Wed, 19 Apr 2000 12:11:54 +0100692_us-ascii If you are using CA scheduler,then this is quite easy. Use DPROC paramters in you SYSIN, instead of variables. We do it at our site most of the time.

Thanks







______________________________ Reply Separator _________________________________ Subject: Re: Variables in SYSIN Select statements Author: jimh (jimh@QIS.NET) at unix,mime Date: 18/04/00 23:17



A contractor came to me today asking if there was a way to use variables in a select statement in a batch jobstream via a SYSIN DD using something like DSNTIAUL. My first thought was to create a proc and simply use the JCL variables. But noooo, the interpreter doesn't touch [...] 15147 38 25_DB2 V6 Upgrade Evaulation21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Wed, 19 Apr 2000 19:31:05 +0800302_us-ascii Dear All,

I am working in a DBA team of a bank. We are using DB2 V5 for OS/390 and we plan to evaluate if it is worth to upgrade to DB2 V6 in this year.

For our evaluation, would the people who have experience in DB2 V6 please provide the following input for our reference: [...] 15186 28 22_Re: DB2 v6 parallelism0_19_mike.holmans@BT.COM31_Wed, 19 Apr 2000 12:29:55 +0100545_- Roger Miller said:

"We did not put in the global default to set DEGREE=ANY except when a > customer demanded it. I would not set everything to use parallel unless > there are more than enough resources (cpu, memory, IO). For most > customers, > using the parameter to speed up the queries that need it most is much more > efficient and less trouble. You might also want to limit the degree of > parallelism in some cases." > How can we "limit the degree of parallelism"? As far as I can see, we only have DEGREE(1) and DEGREE(ANY) [...] 15215 65 25_Re: information about ddf0_19_mike.holmans@BT.COM31_Wed, 19 Apr 2000 12:52:33 +0100599_- For linking two OS/390 DB2 subsystems, I think there are three basic approaches: data sharing, DDF, and MQSeries.

Data sharing is the fastest. In effect, you are merging your subsystems into a single entity running on two or more machines at once. Going to data sharing means implementing parallel sysplex, which, as we are finding, is a huge and complex operation which requires a lot of commitment across the IT organisation rather than just within the DB2 community. It may be better, but it may also be impractical (or too expensive, which comes to the same thing) to implement in [...] 15281 118 13_strange query17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Wed, 19 Apr 2000 13:13:28 +0100619_US-ASCII Hi all

I have the following query (say QUERY1) which returns about 1.1 million rows :

SELECT DISTINCT(S.SETTLE_TRACT_ID) FROM VSS0IE.SETTLE_TRACT S ,VSS0IE.POSTING P WHERE P.SETRA_SET_TRACT_ID = S.SETTLE_TRACT_ID AND S.SET_STS_IND = 'C' AND S.UPDATE_DATE_CLOSED <= '2000-01-10' AND S.ALLOCATION_IND = 'N' AND S.SETTLE_TRACT_TYPE = 'SBG' AND S.SETTLE_TRACT_ID LIKE 'F%' AND P.SETT_POSTING_IND = 'Y' AND NOT EXISTS (SELECT 1 FROM VSS0IE.SETR_LINK SL WHERE SL.SETTLE_TRACT_ID = S.SETTLE_TRACT_ID) AND NOT EXISTS (SELECT 1 FROM VSS0IE.CERT_TRACT CT WHERE CT.SETTLE_TRACT_ID = S.SETTLE_TRACT_ID) [...] 15400 63 22_Re: DB2 v6 parallelism15_Toine Michielse18_vndobtm@US.IBM.COM31_Wed, 19 Apr 2000 14:15:29 +0100468_us-ascii Hello Mike,

There's a ZPARM that controls the maximum parallel degree. It's called PARAMDEG and was introduced with APAR PQ28414. PTF UQ33442 for DB2 V5, UQ33443 for DB2 V6.

Regards,

Toine Michielse DB2 for OS/390 Technical Specialist Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



mike.holmans@BT.COM@RYCI.COM> on 04/19/2000 12:29:55 PM [...] 15464 63 22_Re: DB2 v6 parallelism10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 19 Apr 2000 12:18:35 GMT312_us-ascii Hello Mike, I have not tried this but just a quick guess is that from DB2 V6 for OS/390 hints to the optimizer can be given and all the new columns in the PLAN_TABLE applicable to parallelism can be given any value which can limit the degree of parallelism.

It may be wrong , it is a guess. [...] 15528 69 54_=?iso-8859-1?q?R=E9f=2E_=3A_Re=3A_DB2_v6_parallelism?=14_Denzil Coalter26_denzil.coalter@UNICIBLE.CH31_Wed, 19 Apr 2000 14:18:39 +0200381_iso-8859-1 Perhaps he was referring to the new zparm PARAMDEG which allows you to specify a max degree of parallelism. A value of 3 would ensure that even if 10 processors were available only a max of 3 would be used.







mike.holmans@BT.COM@RYCI.COM> le 19.04.2000 13:29:55

Veuillez répondre ŕ DB2 Data Base Discussion List [...] 15598 115 32_Re: Unique vs Non Unique Indexes0_19_mike.holmans@BT.COM31_Wed, 19 Apr 2000 13:23:29 +0100576_- In the earliest releases of DB2, CLUSTERRATIO wasn't recorded. RUNSTATS simply decided whether or not it the table was >=95% clustered, and put 'Y' in CLUSTERED if it was. Later releases recorded the more accurate CLUSTERRATIO, and the optimizer would make its decisions using that figure instead. Since CLUSTERRATIO is user-modifiable, it is possible for users to give it a value which isn't in the range 0-100, in which case the optimizer takes the view that statistics have not been collected and uses the default, for which it looks at CLUSTERED, since it's there. [...] 15714 85 22_Re: DB2 v6 parallelism0_19_mike.holmans@BT.COM31_Wed, 19 Apr 2000 13:27:43 +0100652_- Thanks, Toine.

But, being a ZPARM, it presumably doesn't allow you to limit some queries but not others where that might actually be beneficial. In context, Roger's remark was perhaps a little unclear about that.

Mike Holmans BT ISE Technical Design mike.holmans@bt.com

> -----Original Message----- > From: Toine Michielse [SMTP:vndobtm@US.IBM.COM] > Sent: Wednesday, April 19, 2000 2:15 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 v6 parallelism > > Hello Mike, > > There's a ZPARM that controls the maximum parallel degree. It's called > PARAMDEG and was introduced with APAR PQ28414. PTF UQ33442 for DB2 V5, > UQ33443 for [...] 15800 140 22_Re: Extents question ?12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Wed, 19 Apr 2000 13:37:00 +0100409_- Although I haven't got around to proving it yet, I suspect that LEAFDIST is influenced by extents as I had an index on a history table of 8 million rows with LEAFDIST in the thousands the day after a reorg.

Colin Allen, DB2 DBA Abbey National

Colin.Allen@AbbeyNational.Co.Uk ---------- From: mebert@AMADEUS.NET To: DB2-L@RYCI.COM Subject: Re: Extents question ? Date: 19 April 2000 12:05 [...] 15941 162 22_Re: Extents question ?14_Thomas, Steven21_Steven_Thomas@BMC.COM31_Wed, 19 Apr 2000 07:42:04 -0500382_ISO-8859-1 Hi Colin,

I don't think extents has any relation to LEAFDIST. If this is a history table and FREEPAGE=0, the the first page split will set LEAFDIST to 200, and it goes up exponentially from there. If FREEPAGE>0, then LEAFDIST should stay a lot lower, but if FREEPAGE=0, then adding enough rows to generate a page split will make LEAFDIST go through the roof. [...] 16104 36 23_Stored Proc. & Auth. ID32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 19 Apr 2000 15:47:29 +0300388_- Hello List,

I've been testing Stored Procedures for a while and have some strange problem.

For Internal Reasons I must use Secondary Auth. ID for running SP. I registered SP to the Catalog as YKBSP01 (SP Name), GGDV (RACF Group of User who runs the CALL'er pgm and SP) and LUNAME and the other columns. I put those columns especially because they uniquely define SP. [...] 16141 174 17_Re: strange query10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 19 Apr 2000 12:59:40 GMT726_us-ascii Hi Nagaraj, What i can see from your query is the problem is with predicates S.SETTLE_TRACT_ID LIKE 'F%' In your query1 you are searching for S.SETTLE_TRACT_ID LIKE 'F%' and getting approx. 1 million rows and in your query3 i.e

(SELECT DISTINCT(POST.SETRA_SET_TRACT_ID) FROM VSS0IE.POSTING POST WHERE POST.VALUE_DATE <='2000-01-10' GROUP BY POST.SETRA_SET_TRACT_ID,POST.CREATE_USER HAVING POST.CREATE_USER >0) you have not given this condition i.e you should be searching for POST.SETRA_SET_TRACT_ID not like 'F%' ,means other than starting from 'F'. I am sure your query3 is returning all the values including all POST.SETRA_SET_TRACT_ID like 'F%' i.e started with 'F' which gets excluded because of NOT [...] 16316 25 40_Check Constraint vs Appl. code Benchmark19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 19 Apr 2000 07:59:18 -0500604_iso-8859-1 Hi all and TIA, Has anyone done or know where to find benchmarking info on using a column constraint vs application code for simple edit checks?

Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any [...] 16342 80 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39013_John Arbogast16_jfarbo@YAHOO.COM31_Wed, 19 Apr 2000 06:03:00 -0700545_us-ascii Thanks, but does this statement look too long or complex to anyone?

EXEC SQL SELECT ALERT_REASON, GLOBAL_SERIAL_IND INTO :ALERT-REASON-ALTYP, :GLOBAL-SERIAL-IND-SW FROM TIDALTYP WHERE (ROUTE_LIST_TYPE = :ROUTE-TYPE-RT) AND (ALERT_REASON_CODE = :ALERT-REASON-CODE-ALARD) END-EXEC.



Myron Miller wrote: From the IBM V6 Install manual: | 2.7.2.5.1 Adjust application programs

| You might need to adjust your application programs because of the following release incompatibilities. [...] 16423 101 50_Re: -101 sqlcode after conversion to DB2 V6 OS/3909_Chris Tee18_TEECHRI@UK.IBM.COM31_Wed, 19 Apr 2000 14:09:04 +0100646_us-ascii Check to see if TIDALTYP is a view







John Arbogast on 19/04/2000 14:03:00

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Chris Tee/UK/Contr/IBM) Subject: Re: -101 sqlcode after conversion to DB2 V6 OS/390







Thanks, but does this statement look too long or complex to anyone?

EXEC SQL SELECT ALERT_REASON, GLOBAL_SERIAL_IND INTO :ALERT-REASON-ALTYP, :GLOBAL-SERIAL-IND-SW FROM TIDALTYP WHERE (ROUTE_LIST_TYPE = :ROUTE-TYPE-RT) AND (ALERT_REASON_CODE = :ALERT-REASON-CODE-ALARD) END-EXEC. [...] 16525 216 22_Re: Extents question ?10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Wed, 19 Apr 2000 13:22:31 GMT404_us-ascii I also do not think extent has to do anything with any of these LEAFDIST,FAROFFPOS,NEAROFFPOS because even if your primary quantity is much higher and secondary quantity is 0 these values can go up. I think these all are internal relations with index and data pages and it doesn't have to do anything with the gap between primary space allocation and subsequent secondary space allocation. [...] 16742 16 49_Recommended Maintenace Level for DB2 V5 for OS39014_Patrick Scholl23_Patrick.Scholl@DEXIA.BE31_Wed, 19 Apr 2000 15:23:35 +0200411_- I would like to know what is the last Recommended Maintenance Level for DB2 V5.

Many thanks in advance for your help.

Best regards,

Patrick Scholl

================================================ 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. 16759 217 17_Re: strange query17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Wed, 19 Apr 2000 14:29:01 +0100371_us-ascii Hi Sanjeev

QUERY3 results only 350,000 rows, so it is not a superset of the result set of QUERY1, which gives me 1.1 million rows. If I search for POST.SERTRA_SET_TRACT_ID NOT LIKE 'F%' in QUERY3 then the result set from QUERY3 will not have anything in common with the result set from QUERY1 (which has all the S.SETTLE_TRACT_ID beginning with 'F') [...] 16977 125 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39013_John Arbogast16_jfarbo@YAHOO.COM31_Wed, 19 Apr 2000 06:30:43 -0700582_us-ascii Nope, it is a table.

--- Chris Tee wrote: > Check to see if TIDALTYP is a view > > > > > John Arbogast on 19/04/2000 > 14:03:00 > > Please respond to DB2 Data Base Discussion List > > > To: DB2-L@RYCI.COM > cc: (bcc: Chris Tee/UK/Contr/IBM) > Subject: Re: -101 sqlcode after conversion to DB2 > V6 OS/390 > > > > > Thanks, but does this statement look too long or > complex to anyone? > > EXEC SQL > SELECT > ALERT_REASON, > GLOBAL_SERIAL_IND > INTO > :ALERT-REASON-ALTYP, > :GLOBAL-SERIAL-IND-SW > FROM [...] 17103 18 22_Re: Extents question ?23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM31_Wed, 19 Apr 2000 09:40:30 -0400364_iso-8859-1 Thanks for bringing this up Dr Ebert.

regards

Kaushal Trivedi DBA Universal Biller (732)-457-2858

================================================ 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. 17122 211 32_Re: Unique vs Non Unique Indexes20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 19 Apr 2000 09:38:35 -0400633_iso-8859-1 Michael

What I meant was for a single column index the filter factor would be 1/fullkeycardf ( by mistake, I typed 1/firstkeycardf). Sorry about that.

Regarding clustering, I emphasized the need for analysis of data access. Which ever index brings larger amount of data that index should be preferable clustering index. Index used for Inner table in a favorite join, which could qualify many rows from outer table is also a good candidate. I never said a single column, unique index should not be clustering index. Index with timestamp is also one common example of clustering index with single column. [...] 17334 159 64_Repost: Winter Corp Announces: Database Scalability Program 200013_Morrill, John12_JohnM@VP.NET31_Wed, 19 Apr 2000 07:58:40 -0600640_iso-8859-1 Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner =================================================



Dear DB2 Interested Parties,

Below is a bulletin requesting participation in the Winter Corporation's "Database Scalability Program 2000" (formerly the VLDB Survey Program), a survey-based research effort that identifies databases with over 100GB of data. [...] 17494 692 42_Repost: DB2 Version 7 Announcement Summary13_Morrill, John12_JohnM@VP.NET31_Wed, 19 Apr 2000 07:58:41 -0600674_iso-8859-1 Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner =================================================



Dear DB2 Interested Parties,

Today, IBM released the following product announcements in association with the launch of DB2 Universal Database Version 7. Below are the announcement letters numbers and web site URLs for your reference. Also find below a summary of the product announcements. [...] 18187 19 14_DB2 V% PUT991013_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM31_Wed, 19 Apr 2000 09:55:36 -0400437_us-ascii Hi, Listers! We are getting ready to apply maintenance to get us to PUT level 9910. Has anyone had any problems going to this level??? TIA, Scott Lindsey Sr. DBA, Highlights for Children

================================================ 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. 18207 45 22_Re: Extents question ?11_Stefan Geus25_stefan.geus@HUK-COBURG.DE31_Wed, 19 Apr 2000 15:58:43 +0100577_us-ascii Hi Sanjeev, Admin Guide says that after you run REORG, Leafdist might be greater then zero, because of emty pages for FREEPAGE. So, Reorg of Index may not reduce LEAFDIST if FREEPAGE > 0.

Stefan



Sanjeev .. schrieb: > > I also do not think extent has to do anything with any of these > LEAFDIST,FAROFFPOS,NEAROFFPOS because even if your primary quantity is much > higher and secondary quantity is 0 these values can go up. > I think these all are internal relations with index and data pages and it > doesn't have to do anything with the gap [...] 18253 147 77_Re: Lowering the Priority of a Distributed Query after it has begun execution0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Wed, 19 Apr 2000 09:11:17 -0500562_us-ascii Hi Dave, In your question " how threads can stay active without holding locks"? Interesting situation.

My understanding is that :

A thread is considered ACTIVE if it is holding cursors or DB2 resources. Its usually doing some sort of work in DB2 e.g. sending requests or performing fetches.

An IDLE ACTIVE thread is one where DB2 is wating for that thread to either issue another request or release its resources via a commit and go inactive.. Normally they are usually holding some sort of DB2 resource (usually a share lock) [...] 18401 61 40_Re: Variables in SYSIN Select statements13_Brendan Friel18_b.friel@PECORP.COM31_Wed, 19 Apr 2000 09:05:40 -0500381_iso-8859-1 I've done this in the past many times using REXX. The dinosaur way before REXX was to run the assembler with a SYSPARM variable to PUNCH statements.

There may also be some possibilities with third party software or your job scheduling system. Not sure of any other possibilities, but you have identified a weakness in MVS that's been around for a long time. [...] 18463 45 18_Re: DB2 V% PUT991019_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 19 Apr 2000 09:24:56 -0500609_iso-8859-1 Hi Scott, My secret decoder ring says that '%' = 5! ;~> 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." [...] 18509 53 18_Re: DB2 V% PUT99100_29_STANLEY_GOODWIN@MECH.DISA.MIL31_Wed, 19 Apr 2000 10:23:41 -0400672_us-ascii I would come current with 5.1 or at least 9912+



Stan Goodwin DAC MBG DB2 Support

Stanley_goodwin@mech.disa.mil DSN: 430-4335 Outside:- 717-605-4335





Scott Lindsey cc: Sent by: DB2 Data Subject: DB2 V% PUT9910 Base Discussion List



04/19/00 09:55 AM Please respond to DB2 Data Base Discussion List











Hi, Listers! We are getting ready to apply maintenance to get us to PUT level 9910. Has anyone had any problems going to this level??? TIA, Scott Lindsey Sr. DBA, Highlights for Children [...] 18563 142 32_Re: Unique vs Non Unique Indexes20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 19 Apr 2000 10:27:43 -0400634_iso-8859-1 Sanjeev

I typed wrongly in my previous mail about firstkeycardf. It should have been fullkeycardf. So far we have observed that optimizer does not use firstkeycardf for filter factor calculation in any case. That is why Michael has said firstkeycardf is historical.

There many places including us who do not run runstats in production (rather run it rarely or update the catalog) but keep a close eye on production catalog stats to make the access path consistent for different application releases. In such cases, it is important that we know the right columns which optimizer uses. Since we not always [...] 18706 25 7_userids17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US31_Wed, 19 Apr 2000 10:42:58 -0400554_iso-8859-1 Hello Mike and DB list serve,

DB2 UDB V6.1 on AIX Question.

If you have a test and production instance on the same AIX node, do you have seperate Database Administrator userids for each instance?

Thank You,

John L. Breidenstine Programmer Legislative Data Processing Center

================================================ 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. 18732 49 33_Re: Moving dbs between NT servers23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 19 Apr 2000 10:49:49 -0400336_- Eric,

I would have to say NO. You do have to install UDB, because the installation process, updates Registry files, services, PATH etc which will not be updated by restoring the whole directory structure. I am not sure if you can manually update everything ( IF you knew every file that UDB installation process updates). [...] 18782 53 18_Re: DB2 V% PUT99100_17_sjvagnier@AEP.COM31_Wed, 19 Apr 2000 10:48:20 -0400366_us-ascii Hello, Scott

We are currently running DB2 V5.1 Put level 9911 in 25 different DB2 environments (includes 6 separate data sharing environments) with no problems. We are currently in the process of upgrading one of our test DB2 V5 environments (non- data sharing) to V6 from V5 Putlevel 9911.

Regards, Steve Vagnier American Electric Power [...] 18836 141 19_Re: Backup/Restore.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 19 Apr 2000 09:53:59 -0500576_iso-8859-1 Hi Ted, See IBM Redbook SG245333, "Using RVA and Snapshot...". Based on my reading, (haven't done it yet, so can't compare), time to recover using a concurrent copy or a Image Copy is about the same. One thing does concern me and maybe it shouldn't, but I can't help it. If I understand correctly from the Redbook, a Concurrent Copy via Snapshot is done by the RVA, RAID 6 Controller internally by saving the track tables. This obviously takes a very short period of time vs copying the entire tablespace and releases access to the tablespace back to DB2 very [...] 18978 32 52_Re: -904 sqlstate 57011 with degree(any) || db2 v5.110_Derek Lund22_derek.lund@UTORONTO.CA31_Wed, 19 Apr 2000 11:08:52 -0400401_US-ASCII By setting SET CURRENT DEGREE = 'ANY' in qmf and running the problem query, I can recreate the error. The message is: "Unsuccessful execution caused by an unavailable resource. Reason code 00E30100, type of resource 00000906, and resource name ."

We have 2 cpu's (I believe:-)) here but am not sure how they are coupled and whether something has to be done to enable parallelism. [...] 19011 41 11_Re: userids11_Steve Mazer17_smazer@FMTUSA.COM31_Wed, 19 Apr 2000 11:01:13 -0400394_us-ascii Yes, since the instance is also an AIX userid, they would be separate. If the question would be to have separate sysadm groups, I would also have that if you give this priveledge to non-DBAs in Test. If at all possible, I would recommend against having both test and production running on the same AIX machine - How would you test AIX level upgrades without impacting Production? [...] 19053 24 47_Re: Alternatives to Erwin / Cayenne, anyone ???11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET31_Wed, 19 Apr 2000 08:14:39 -0700364_US-ASCII I have been evaluating the ERwin against a RDM product from SILVERRUN. I have found the Silverrun product to be very robust. It includes software that will publish your models as web pages and you have to pay extra to get this with ERwin. Check them out at www.silverrun.com. I was surprised to see all of the companies that are using their product. [...] 19078 149 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39012_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 19 Apr 2000 08:23:31 -0700536_us-ascii It looks like a good query to me. Its from PASSPORT, right? Think you need to open a PMR with IBM and give them this query. Assume you're reasonably current with maintenance on V5 (See Roger Miller's comments). --- John Arbogast wrote: > Nope, it is a table. > > --- Chris Tee wrote: > > Check to see if TIDALTYP is a view > > > > > > > > > > John Arbogast on 19/04/2000 > > 14:03:00 > > > > Please respond to DB2 Data Base Discussion List > > > [...] 19228 170 19_Re: Backup/Restore.18_Erringer, Keith M.24_Keith.Erringer@ALCOA.COM31_Wed, 19 Apr 2000 11:14:34 -0400631_iso-8859-1 Whenever DB2 changes data after the Snapshot takes place it gets re-written to a new location leaving the original track undisturbed. We are talking virtual DASD here. The old tracks won't be re-used or freed until the Snapshot copy is deleted.

Keith.

> ---------- > From: DAVIS, RICK (SBCSI)[SMTP:RD8246@MOMAIL.SBC.COM] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, April 19, 2000 10:53 AM > To: DB2-L@RYCI.COM > Subject: Re: Backup/Restore. > > Hi Ted, > See IBM Redbook SG245333, "Using RVA and Snapshot...". > Based on my reading, (haven't done it yet, so can't compare), time to > [...] 19399 10 18_db2 install on vse0_27_Bud.Greenman@ONONDAGA.NY.US31_Wed, 19 Apr 2000 11:22:56 -0400293_- We need some help installing db2 on a vse system.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19410 140 17_Re: strange query13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 19 Apr 2000 10:36:40 -0500422_US-ASCII Hello Nagaraj, could you please change the HAVING POST.CREATE_USER >0) in QUERY2 to HAVING POST.CREATE_USER <>0) and post the results. I recently open an ETR with IBM regarding these strange results. TIA Kurt













>>> Nagaraj.Pudukotai@WDR.COM 04/19/00 07:13AM >>> Hi all

I have the following query (say QUERY1) which returns about 1.1 million rows : [...] 19551 202 19_Re: Backup/Restore.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 19 Apr 2000 10:37:28 -0500361_iso-8859-1 Hi Keith, Yup, you restate my point exactly and better! One is dependent on the controller doing its job right by not wiping out the old tracks needed for the copy, and not failing while the copy is in progress. A "goof" during the copy process could impact data integrity. Snapshot adds a layer of complexity when relying on a concurrent copy. [...] 19754 59 47_Re: Alternatives to Erwin / Cayenne, anyone ???14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Wed, 19 Apr 2000 10:54:33 -0500592_ISO-8859-1 For a list of ER and data modeling tools and vendors, check out TDAN.com, the Data Administration Newsletter. This site has a fairly comprehensive list of vendors at the following URL

http://www.tdan.com/companies.htm#Datamodel

Though not 100 per cent up to date and not all of the tools are the "same", you can get a good representative sample of data modeling vendors. The ones that jump out at me from that list as the most viable, independent solutions are Silverrun, Popkin, and Embarcadero. I'm not sure of the DB2 support provided by any of them, though. [...] 19814 17 25_order of binds important?15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM31_Wed, 19 Apr 2000 09:14:25 -0700285_iso-8859-1 I keep hearing that plan binds must precede package binds but I'm not seeing why. It seems that as long as the DBRM exists it doesn't matter what order the binds are done in. Anyone know why plan binds would have to be done first? (DB2 V4 MVS soon to be DB2 V5 OS/390) [...] 19832 206 32_Re: Unique vs Non Unique Indexes13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 19 Apr 2000 09:11:55 -0700552_iso-8859-1 Hi!

I agree with Norm and Michael...but just a few more thoughts before a decision is made to make an index clustering.

In the absence of an explicit clustering index , DB2 only 'attempts' to make the first index defined on the table as the clustering index. This means that when a new record is inserted into the table, DB2 tries to insert the record into the tablespace page as close as is feasible so that the clusterratio remains 100% (this happens even if there is an explicit clustering index defined on the table). [...] 20039 36 14_-430 in V6 IVP11_Lori Bewley21_lori_bewley@SABRE.COM31_Wed, 19 Apr 2000 11:13:04 -0500544_- Has anyone encountered this problem running job DSNTEJ65 in the V6 IVP?

ERROR: Call to DSNTPSMP failed -----> Processing halted DSNT408I SQLCODE = -430, ERROR: PROCEDURE SYSPROC .DSNTPSMP (SPECIFIC NAME SYSPROC .DSNTPSMP) HAS ABNORMALLY TERMINATED DSNT418I SQLSTATE = 38503 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNX9CAC SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 20076 39 29_Re: order of binds important?17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Wed, 19 Apr 2000 09:41:31 -0700415_US-ASCII TIA,

I agree with you, however the question that I have in this matter is when a PLAN is new. In this instance I think the PLAN should be added first.



az



______________________________ Reply Separator _________________________________ Subject: order of binds important? Author: DB2 Data Base Discussion List at INTERNET-MAIL Date: 4/19/00 9:14 AM [...] 20116 41 29_Re: order of binds important?0_19_mike.holmans@BT.COM31_Wed, 19 Apr 2000 17:20:26 +0100633_- 1 From whom do you keep hearing this?

2 Do they know anything about DB2?

Whatever the answer to question 1, my guess at the answer to the second question is "Nothing reliable."



Mike Holmans BT ISE Technical Design mike.holmans@bt.com

> -----Original Message----- > From: Mitchell, Sandi [SMTP:Sandi.Mitchell@AVISTACORP.COM] > Sent: Wednesday, April 19, 2000 5:14 PM > To: DB2-L@RYCI.COM > Subject: order of binds important? > > I keep hearing that plan binds must precede package binds but I'm not > seeing > why. It seems that as long as the DBRM exists it doesn't matter what order > the [...] 20158 184 77_Re: Lowering the Priority of a Distributed Query after it has begun execution13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 19 Apr 2000 11:52:53 -0500354_iso-8859-1 Hi Shan,

Thanks for the feedback! I must say my confidence in the Omegamon monitor is dwindling. Our active threads (the ones we believe should be going inactive) just sit and accumulate Elapsed time for long periods. The idle thread timeout is set at 2 hours, but these threads have just enough sporadic activity to not hit this. [...] 20343 32 29_Re: order of binds important?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 19 Apr 2000 13:11:57 -0400625_iso-8859-1 In my experience, the order of binds of plans consisting of packages is of no consequence. This seems to me to certainly be the case when you use Version(Auto) on the pre-compile and when your PKList for the plan consists of wildcard specification.

For example, I have packages named D610.FAXPED.CALLSPI.(2000-04-19-12.53.53.249852) D610.FAXPED.SP1.(2000-04-17-12.13.45.212385) D610.FAXPED.SP2.(2000-04-14-12.23.33.002311) As long as I bind a plan with the parameter PKList(FAXPED.*) all 3 packages are included in the plan as well as any created subsequently. So the order definitely does NOT matter. [...] 20376 287 19_Re: Backup/Restore.9_Ted Pesta18_tpesta@AMQUEST.COM31_Wed, 19 Apr 2000 13:16:48 -0400422_iso-8859-1 Rick and Keith, Thanks for the help. The redbook you and Toine recommended was extremely useful. We are going to try a snap and recover as soon as the MVS team tells me snapshot is available.

Thank you again, Ted Pesta

-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Wednesday, April 19, 2000 11:37 AM To: DB2-L@RYCI.COM Subject: Re: Backup/Restore. [...] 20664 283 19_Re: Backup/Restore.9_Ted Pesta18_tpesta@AMQUEST.COM31_Wed, 19 Apr 2000 13:46:59 -0400647_iso-8859-1 Another useful redbook is Implementing SnapShot, SG24-2241-01. It has several good DB2 examples.

I -----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Wednesday, April 19, 2000 11:37 AM To: DB2-L@RYCI.COM Subject: Re: Backup/Restore.

Hi Keith, Yup, you restate my point exactly and better! One is dependent on the controller doing its job right by not wiping out the old tracks needed for the copy, and not failing while the copy is in progress. A "goof" during the copy process could impact data integrity. Snapshot adds a layer of complexity when relying on a concurrent copy. [...] 20948 15 13_Revoke SYSADM16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Wed, 19 Apr 2000 10:56:00 -0700369_- We are on DB2 for OS390, V5. We have a standard to use secondary authids, including SYSADM authority. At some point in time I had SYSADM granted to my primary authid(maybe for testing). I now want to revoke the SYSADM from my primary id(one of my secondary authids has SYSADM authority). What specifically should I check for that may get revoked or invalidated? [...] 20964 46 17_Re: Revoke SYSADM13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 19 Apr 2000 11:09:34 -0700344_us-ascii Hi!

The safest way to do it is :

1. Create DSNZPARM member specifying SYSADM userid to be revoked as an Install SYSADM (in addition to the existing Install SYSADM userid). 2. Revoke the SYSADM authority from the user. 3. Modify the DSNZPARM member to remove the userid added in the first step as the Install SYSADM. [...] 21011 36 29_Re: order of binds important?9_Rob Crane22_racrane@CONCENTRIC.NET31_Wed, 19 Apr 2000 12:27:33 -0600309_us-ascii Probably tied to someone that set standards Prior to version 2.3 of DB2 when there were only plans and no packages, once 2.3 came out packages were introduced and lot of people just added it as a step to there existing procedures, after the plan bind. There is no requirement from DB2 for this. [...] 21048 91 17_Re: Revoke SYSADM13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Wed, 19 Apr 2000 13:28:35 -0500455_ISO-8859-1 If you cannot stand the system outage, or determine if you need to have an outage, you can do a select from the SYSIBM.SYS*AUTH tables to determine if your primary authid is the GRANTOR of other authorizations.

SELECT * FROM SYSIBM.SYS*AUTH WHERE GRANTOR = 'primary id';

These tables are: SYSCOLAUTH SYSDBAUTH SYSPACKAUTH SYSPLANAUTH SYSRESAUTH SYSTABAUTH SYSUSERAUTH SYSROUTINEAUTH (Version 6.1) SYSSCHEMAAUTH (Version 6.1) [...] 21140 45 22_Re: DB2 v6 parallelism12_Hayes, Chris19_CHRIS.HAYES@SSA.GOV31_Wed, 19 Apr 2000 14:54:51 -0400413_iso-8859-1 Is there any way to characterize applications that can benefit from parallelism? A large amount of getpages per sql statement comes to mind. Maybe a large amount of cpu per sql statement would be another. Any other thoughts?

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Tuesday, April 18, 2000 10:05 PM To: DB2-L@RYCI.COM Subject: Re: DB2 v6 parallelism [...] 21186 234 17_Re: strange query13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 19 Apr 2000 13:57:15 -0500507_US-ASCII Nagaraj, If your running parallel degree=any see PQ21063 its still open. Kurt

>>> Nagaraj.Pudukotai@WDR.COM 04/19/00 08:29AM >>> Hi Sanjeev

QUERY3 results only 350,000 rows, so it is not a superset of the result set of QUERY1, which gives me 1.1 million rows. If I search for POST.SERTRA_SET_TRACT_ID NOT LIKE 'F%' in QUERY3 then the result set from QUERY3 will not have anything in common with the result set from QUERY1 (which has all the S.SETTLE_TRACT_ID beginning with 'F') [...] 21421 51 17_Re: Revoke SYSADM11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM31_Wed, 19 Apr 2000 14:34:03 -0400448_us-ascii The trick is to include the ID that you plan to revoke as an Install SYSADM in the ZPARM. After you re-cycle DB2 you can then revoke the ID without any cascading problems.







"JOHN G. MATTHEWS" on 04/19/2000 01:56:00 PM

Please respond to DB2 Data Base Discussion List









To: DB2-L@RYCI.COM cc: Subject: Revoke SYSADM [...] 21473 179 50_Re: -101 sqlcode after conversion to DB2 V6 OS/39013_John Arbogast16_jfarbo@YAHOO.COM31_Wed, 19 Apr 2000 12:15:39 -0700564_us-ascii Yep, Passport. The -101 happens on a V6 subsystem, works fine on V5. We are very current on our maintenance.



--- Myron Miller wrote: > It looks like a good query to me. Its from > PASSPORT, > right? Think you need to open a PMR with IBM and > give > them this query. Assume you're reasonably current > with maintenance on V5 (See Roger Miller's > comments). > --- John Arbogast wrote: > > Nope, it is a table. > > > > --- Chris Tee wrote: > > > Check to see if TIDALTYP [...] 21653 219 50_Re: -101 sqlcode after conversion to DB2 V6 OS/3900_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Apr 2000 14:18:48 -0500648_us-ascii Do you know what the access path was previously? Are you using parallelism?













John Arbogast on 04/19/2000 02:15:39 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Re: -101 sqlcode after conversion to DB2 V6 OS/390















Yep, Passport. The -101 happens on a V6 subsystem, works fine on V5. We are very current on our maintenance. [...] 21873 72 17_Re: Revoke SYSADM11_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Wed, 19 Apr 2000 15:41:04 -0400549_us-ascii Another approach to this problem may 'kill two birds with one stone."

1) Convert your DB2 internal security to RACF, wherever possible. 2) Revoke SYSADM.

By converting to RACF, you'll have a cleaner, leaner set of security definitions. And when you can demonstrate that your objects are protected by RACF, many cascading problems will be averted. I wish I could tell you that you can avoid all of them, but I don't have time to research any further. But I wanted to advise this as the 'road less travelled'. -joe luthman [...] 21946 20 28_Used Index space information8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Wed, 19 Apr 2000 14:36:03 -0500371_us-ascii Hi, Is it possible to find the actual index space usage from catalog tables?. I am able to get for Tablespaces by running STOSPACE and RUNSTATS. and querying the PERCACTIVE and SPACE. But could not find one for Indexes. Appreciate if some one could used any kind of query or any other method to find the size of actual space used by indexes. TIA Rgds Bejoy [...] 21967 90 47_Re: Implementing a DB2 OS/390 V6 Data Warehouse8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Wed, 19 Apr 2000 14:51:26 -0500383_us-ascii Carlos Olson, Good Design for this kind of warehouse. Need to know, how is your partition key looks like? If it is ascending value you may not be able to alter the key after 2.5 years. Check the SQL Ref. Manual about ALTER INDEX. You can see,

"The highest value of the key in any partition must be lower than the highest value of the key in the next partition." [...] 22058 235 77_Re: Lowering the Priority of a Distributed Query after it has begun execution22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 19 Apr 2000 15:54:19 -0400688_iso-8859-1 Dave -

At the risk of stating the obvious... Make sure ZPARM CMTSTAT=INACTIVE



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

> ---------- > From: Shapiro, Dave[SMTP:Shapiro.Dave@PRINCIPAL.COM] > Sent: Wednesday, April 19, 2000 12:52 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] Lowering the Priority of a Distributed Query > after it has begun execution > > Hi Shan, > > Thanks for the feedback! I must say my confidence in the Omegamon monitor > is > dwindling. Our active threads (the ones we believe should be going > inactive) > just sit and accumulate Elapsed time for long periods. The idle thread [...] 22294 74 29_Re: order of binds important?22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 19 Apr 2000 16:00:45 -0400528_- I believe that binding a plan before binding a package is not important. You must bind a package (COLL1.PACK1) before binding a plan that has a PKLIST entry specifying a fully qualified package name (e.g. PKLIST(COLL1.PACK1)). That's why most plans with packages specify PKLIST(COLL1.*)

If a plan consists of DBRMs and packages, then execution time performance depends on whether SQL statements are executed using DBRMs more than packages or packages more than DBRMs and DB2's search order for DBRMs and packages. [...] 22369 20 58_Changing High Level Qualifier for all Tables in a Database19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM31_Wed, 19 Apr 2000 16:43:22 -0400511_us-ascii In DB2 for MVS V 5 I need to change the high level qualifier for all of the tables in a database. for example SQLUSER.PURCH_ORD to SQLORD.PURCH_ORD.

This has to be done to support a vendor package.

Do you know a simple way to do this?

Lisa

================================================ 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. 22390 47 62_Re: Changing High Level Qualifier for all Tables in a Database12_craig patton21_prgpatton@HOTMAIL.COM29_Wed, 19 Apr 2000 13:59:54 PDT388_- Lisa, Depending on your version of db2, just RENAME the tables. Pay attention to the views and synonyms that will be lost as well as rebinding plans/packages. If you are running less than V5 DB2 for OS/390, this is not available and you might create the new tables, insert * from OLD table then Drop OLD tables. If you are running UDB on NT or Unix, etc, I don't know the process. [...] 22438 258 77_Re: Lowering the Priority of a Distributed Query after it has begun execution13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 19 Apr 2000 15:59:09 -0500491_iso-8859-1 Thanks Mike,

I double checked and CMTSTAT is set to INACTIVE. At this point I'd have loved for it to be that obvious!

Any other ideas? (Robert, sorry for introducing scope creep on this Subject)

Dave

-----Original Message----- From: Piontkowski Michael ML [mailto:michael.piontkowski@ASTRAZENECA.COM] Sent: Wednesday, April 19, 2000 2:54 PM To: DB2-L@RYCI.COM Subject: Re: Lowering the Priority of a Distributed Query after it has begun execution [...] 22697 43 62_Re: Changing High Level Qualifier for all Tables in a Database14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 19 Apr 2000 17:00:47 -0400600_iso-8859-1 I do not believe you can do that other than dropping and re-creating the tables. However you can build synonyms or alias (whichever suits your purpose) with SQLORD pointing to SQLUSER.

Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Lisa Westcott-Dryer > Sent: Wednesday, April 19, 2000 4:43 PM > To: DB2-L@RYCI.COM > Subject: Changing High Level Qualifier for all Tables in a Database > > > In DB2 for MVS V 5 I need to change the high level qualifier for all of > the tables in a database. > for example [...] 22741 17 21_db2 TCP/IP and resync10_Tom Taylor17_ttaylor@CHUBB.COM31_Wed, 19 Apr 2000 17:17:19 -0400435_us-ascii Hi all

Can I run 2 members of a DS group on the same Lpar and use TCP/IP with success? What happens if a member fails? what is the idea behind the resync port number?

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22759 18 16_SAP DBA GOOD/BAD11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Wed, 19 Apr 2000 14:18:50 -0700288_iso-8859-1 I'd like to toss this out, A co-worker of mine is thinking of leaving a low stress lower pay DBA job (I know low stress DBA is an oxymoron) for a high paying DBA job with a company that is going to move their SAP from Oracle to DB2. Is this a good idea or not??? cliff:-) [...] 22778 92 62_Re: Changing High Level Qualifier for all Tables in a Database0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Apr 2000 16:16:43 -0500853_us-ascii Craig, The RENAME TABLE command in DB2 V5 for OS/390 cannot rename the table owner, only the table name.

Thanks, Tim













craig patton on 04/19/2000 03:59:54 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Re: Changing High Level Qualifier for all Tables in a Database















Lisa, Depending on your version of db2, just RENAME the tables. Pay attention to the views and synonyms that will be lost as well as rebinding plans/packages. If you are running less than V5 DB2 for OS/390, this is not available and you might create [...] 22871 307 77_Re: Lowering the Priority of a Distributed Query after it has begun execution22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 19 Apr 2000 17:31:18 -0400658_iso-8859-1 Dave -

Do they show up on a -display thread(*) type(*) location(*) detail.



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

> ---------- > From: Shapiro, Dave[SMTP:Shapiro.Dave@PRINCIPAL.COM] > Sent: Wednesday, April 19, 2000 4:59 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] Lowering the Priority of a Distributed Query > after it has begun execution > > Thanks Mike, > > I double checked and CMTSTAT is set to INACTIVE. At this point I'd have > loved > for it to be that obvious! > > Any other ideas? (Robert, sorry for introducing scope creep on this > Subject) > > Dave > [...] 23179 34 20_Re: SAP DBA GOOD/BAD9_Rob Crane22_racrane@CONCENTRIC.NET31_Wed, 19 Apr 2000 15:49:36 -0600487_us-ascii Fairly subjective question, but dealing with SAP or any other ERP vendor on the OS390 UDB platform should be a great opportunity with lots of opportunities to "save the day" for the DBA.

Money is good, more money is even better, but doing what you like and enjoying your job is priceless. Company politics, career growth and support from your management even various within the same company. As the saying goes, "unless you are the lead dog the view never changes". [...] 23214 294 76_Re: Lowering the Priority of a Distributed Query after it hasbegun execution14_Linda Claussen18_lindafc@NETINS.NET31_Wed, 19 Apr 2000 16:33:41 -0500492_ISO-8859-1 If the zparm RELCURHL=YES is specified, no locks will be held for Cursors declared WITH HOLD, but the thread will stay on the active chain. It might be worth checking.

Linda F. Claussen Claussen & Assoc. lindafc@netins.net http://www.netins.net/showcase/lclaussen ---------- From: Shapiro, Dave To: DB2-L@RYCI.COM Subject: Re: Lowering the Priority of a Distributed Query after it hasbegun execution Date: Wednesday, April 19, 2000 3:59 PM [...] 23509 47 62_Re: Changing High Level Qualifier for all Tables in a Database15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Wed, 19 Apr 2000 17:47:43 -0400494_us-ascii If you have 3rd party tools for altering DB2 objects you should be able to do it by using them to change the owner id of the tables. Do not forget any authorizations and owner / qualifier parameters in plan / package binds. Also for QUALIFIED table / view names in imbeded SQL in programs and DCLLIB members these will have to be changed and programs recompiled to pick up new owner id. There will probably be more that I have not thought of but I am sure others will and answer. [...] 23557 16 36_Re: Database/Instance mix in DB2/AIX12_Raymond Bell20_raymondb@NZ1.IBM.COM31_Thu, 20 Apr 2000 09:34:20 +1200353_us-ascii Thanks, Wayne. It did help, actually. Just the kind of thing I was looking for.



Raymond

================================================ 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. 23574 53 29_Re: DB2 V6 Upgrade Evaulation13_Olson, Carlos14_COlson@QRS.COM31_Wed, 19 Apr 2000 16:13:29 -0700462_iso-8859-1 Go to link:

http://www-4.ibm.com/software/data/db2/os390/whatsnew/whatv602.htm

-----Original Message----- From: Li, Chak Lung Dominic [mailto:seddba@HK.SUPER.NET] Sent: Wednesday, April 19, 2000 4:31 AM To: DB2-L@RYCI.COM Subject: DB2 V6 Upgrade Evaulation



Dear All,

I am working in a DBA team of a bank. We are using DB2 V5 for OS/390 and we plan to evaluate if it is worth to upgrade to DB2 V6 in this year. [...] 23628 130 17_Re: Revoke SYSADM15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU31_Thu, 20 Apr 2000 09:07:44 +1000451_us-ascii You also have to watch out for cases where views can be dropped. This happens when the SELECT access used to create the view is revoked by the cascading revokes.

Shaun







"McDonald, Ken" on 20/04/2000 04:28:35

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Shaun Z Lombard/CorpServ/qdot/au) Subject: Re: Revoke SYSADM [...] 23759 35 30_DB2UDB EEE vs DB2UDB EE on SMP13_Umair Hussain25_umair_hussain@HOTMAIL.COM29_Wed, 19 Apr 2000 19:05:59 CDT320_- Hi all,

What performance gains will we have in an OLTP environment (very high inserts and updates) if we install DB2UDB EEE on a single 8 or 12 way SMP machine (AIX 4.3.3) as opposed to installing DB2UDB EE on the same box. Also, will the db2 utilities perform any better with DB2UDB EEE in a SMP machine? [...] 23795 252 32_Re: Unique vs Non Unique Indexes14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 20 Apr 2000 11:13:36 +1000614_us-ascii Venkat,

"The reason I said there is no big advantage of having single column unique index is following (of-course assuming there are other indexes also) - Since index is unique, the matching index access would bring only 1 row (not considering join with other tables)"

No. In batch processing may have no matching columns, 1 column match with range predicates, or even have process with equals match driven off sorted flat file on the column in question. In all 3 cases, processing the table sequentially (and clustered) by this column nay be the best strategy to avoid death by random [...] 24048 107 56_[Fwd: [Fwd: IBM DB2 Connect (Personal Edition) Problem]]21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Thu, 20 Apr 2000 12:50:19 +080063_us-ascii Sorry, I have mis-typed again.

Regards, A Cheng 24156 19 32_DB2 Reporting Tools - UDB 6.1 NT6_Jim C.17_Jimluxpax@AOL.COM29_Thu, 20 Apr 2000 01:53:23 EDT359_US-ASCII Hello All,

We're looking for a DB2 tool that does ad hoc and canned reports. QMF from IBM, Crystal Reports, Viador, Accrue, and NetGenesis have been suggested. (Also, we're a Net.Commerce facilitator - we're looking for web capable products). Any thoughts as to the alternatives and relative merits would be much appreciated. Thanks, Jim [...] 24176 124 41_Re: Questions in regarding sparse indexes9_Paul Yuen16_pyuen@GIO.COM.AU31_Thu, 20 Apr 2000 17:12:54 +1000317_us-ascii Sanjeev, Thanks for your detailed suggestions, the column will be updated thorough the year and is business dependent. The column will be used mostly by online queries and a clustering index is already defined in the table. We still haven't decided on what to do about the new index. Thanks again, Paul [...] 24301 36 22_Re: db2 install on vse4_IBM116_IBM1@UBIL.COM.TR31_Thu, 20 Apr 2000 10:12:33 +0300735_- What kind of help are you looking for, exactly? and most important question: is it VSE native or rather hosted on VM?

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: Bud.Greenman@ONONDAGA.NY.US [SMTP:Bud.Greenman@ONONDAGA.NY.US] > Sent: Wednesday, April 19, 2000 6:23 PM > To: DB2-L@RYCI.COM > Subject: db2 install on vse > > We need some help installing db2 on a vse system. > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can > be reached at [...] 24338 77 32_[Fwd: DB2 V6 Upgrade Evaulation]21_Li, Chak Lung Dominic19_seddba@HK.SUPER.NET31_Thu, 20 Apr 2000 15:43:56 +0800255_us-ascii Dear All,

Further to my mail of 19 April 2000, would you please also let me know the following:

1. Upward compatibility of DB2 V6.

2. Your general impression for this upgrade.

Thanks and regards,

A Cheng

24416 28 36_Re: DB2 Reporting Tools - UDB 6.1 NT32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Thu, 20 Apr 2000 11:23:35 +0300582_- Business Objects is also an alternative.

-----Original Message----- From: Jim C. [mailto:Jimluxpax@AOL.COM] Sent: Thursday, April 20, 2000 8:53 AM To: DB2-L@RYCI.COM Subject: DB2 Reporting Tools - UDB 6.1 NT



Hello All,

We're looking for a DB2 tool that does ad hoc and canned reports. QMF from IBM, Crystal Reports, Viador, Accrue, and NetGenesis have been suggested. (Also, we're a Net.Commerce facilitator - we're looking for web capable products). Any thoughts as to the alternatives and relative merits would be much appreciated. Thanks, Jim [...] 24445 41 30_Removing Entries from SYSLGRNX17_Andreas Constanti24_andreas.constanti@AGI.CH31_Thu, 20 Apr 2000 10:30:52 +0100601_us-ascii The DB2 for OS/390 V5 SQL Reference Manual states on page 361: "Deleting SYSLGRNG records for dropped table spaces: After dropping a table space, you cannot delete the associated records. If you want to remove the records, you must quiesce the table space, and then run the MODIFY RECOVERY utility before dropping the table space. If you delete the SYSLGRNG records and drop the table space, you cannot reclaim the table space." It seems that this hasn't always been observed at our site (especially in the test environment). I found quite a few old entries in the SYSLGRNX. Does anyone [...] 24487 154 17_Re: strange query10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Thu, 20 Apr 2000 09:07:47 GMT325_us-ascii Hello Nagaraj, You are correct .I didn't see this. Result of QUERY3 is not the superset of QUERY1 , so minimum 750,000 rows should be returned ideally. I tried all possible effort to find out the problem but i could not come to any conclusion. Please pass on the solution if you are able to solve this problem. [...] 24642 61 20_Re: SAP DBA GOOD/BAD13_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 20 Apr 2000 10:17:31 +0100275_us-ascii Speaking as a DB2 DBA who has worked with SAP for 2 years I would recommend it, purely for the reason that you will learn new and valuable skills, which is always a good idea. Managing an environment like this makes you look at DB2 in a slightly different way. [...] 24704 269 22_Re: Extents question ?12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK31_Thu, 20 Apr 2000 11:18:00 +0100546_iso-8859-1 Before checking on the impact of extents on leaf distance I need to understand how it is calculated in the first place.

I don't understand why the first leaf split would give a leafdist of 200 as Steve says. I am looking at the V4 Admin Guide page X - 181 which suggests to me that Leafdist is more likely to approach 0 immediately after a reorg if Freepage IS 0. The Admin Guide talks at one point about the average number of GAPS between leaf pages and then talks about the average number of PAGES between leaf pages, so [...] 24974 379 56_=?iso-8859-1?q?R=E9f=2E_=3A_Re=3A_Extents_question_=3F?=14_Denzil Coalter26_denzil.coalter@UNICIBLE.CH31_Thu, 20 Apr 2000 12:56:09 +0200363_iso-8859-1 Hi Colin,

My guess is the following

2 pgs between 1 and 2 (5,4) 0 pgs between 2 and 3 2 pgs between 3 and 4 (1,5) 3 pgs between 4 and 5 (2,3,1)

7 total

7 / 5 leaf pgs = avg 1.4 pgs 1.4 * 100 = leafdist 140







"Allen, Colin" @RYCI.COM> le 20.04.2000 12:18:00 [...] 25354 313 22_Re: Extents question ?14_Thomas, Steven21_Steven_Thomas@BMC.COM31_Thu, 20 Apr 2000 06:22:55 -0500369_ISO-8859-1 Hi Colin,

My figure of 200 was a rough estimate, depending where in the index the first page split happens. The actual number could be anywhere between 100 and 200. The message I was trying to get across was that the first page split in an index with FREEPAGE=0 can cause LEAFDIST to go quite high, which is a shame as FREEPAGE 0 is the default. [...] 25668 66 11_Re: userids17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US31_Thu, 20 Apr 2000 09:04:45 -0400434_iso-8859-1 Hello steve,

Thanks for your answer, but I must clearify.

We do have 2 seperate userids for the instances, because a seperate userid is needed as the sysadm to create the instances.

But we wanted to create a single dbadmin (database administer) that could be used for both instances. Example: PROD INST: SYSADMIN = prodinst1 DBADMIN = db2admin1 TEST INST: SYSADMIN = testinst1 DBADMIN = db2admin1 [...] 25735 50 11_Re: userids11_Steve Mazer17_smazer@FMTUSA.COM31_Thu, 20 Apr 2000 09:16:24 -0400594_us-ascii At 09:04 AM 4/20/00 -0400, you wrote: >Hello steve, > >Thanks for your answer, but I must clearify. > >We do have 2 seperate userids for the instances, because a seperate userid >is needed as the sysadm to create the instances. > >But we wanted to create a single dbadmin (database administer) that could be >used for both instances. >Example: >PROD INST: SYSADMIN = prodinst1 > DBADMIN = db2admin1 >TEST INST: SYSADMIN = testinst1 > DBADMIN = db2admin1 > >Thanks John B. > You could do this, but it would complicate things somewhat. For instance, which db2profile do you execute [...] 25786 386 32_Re: Unique vs Non Unique Indexes20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 20 Apr 2000 09:47:02 -0400416_iso-8859-1 I guess you mostly disagree with the words "No big advantage". But you should see this in the context of the very first mail then probably you would agree why I used that word (to justify that rules of clustering is not as simple)

>>"> > Additionally, I've always preferred that the Clustering >Index is > >Unique also, > > but I have not found anything yet to say that using a Non >Unique"<< [...] 26173 26 65_PLEASE HELP - Need to change the Password for a SCHEDULED BACKUP!15_Joel Zigelstein20_jzigel1@HALLMARK.COM31_Thu, 20 Apr 2000 09:04:53 -0500561_- Hi All ....

The Network people did something wierd on me. They changed the administration password on me for some reason or another but regardless of that fact ... my backups last night (they changed it yesterday afternoon) failed because of error - SQL1403 - Invalid user name and/or password. I created these backups using the backup with smart guide and they ran great up until now. How do I change the passwords for this backup OR how do I deleted a pending job(it shows in the journal) ... and that way I can create another scheduled backup! [...] 26200 97 15_keepdynamic=yes15_Pierre Dagenais19_pdagenai@UOTTAWA.CA31_Thu, 20 Apr 2000 10:22:09 -0400536_us-ascii Hi everyone

I am trying to solve a mystery. We are building powerbuilder application PB6.5 with db2 connect personal edition 5.2 fixpack11, and db2 v5.1

I want to avoid to prepare dynamic sql statement. But it doesn't work. DYNCACHE is set to yes in db2 v5.

Powerbuilder app are running with sqlcache=100 . This parameter avoids the execution of the prepare instruction if the sql statement is found in the PB app cache( do not confuse this with db2 dyn cache).It works I verified with an odbc trace. [...] 26298 47 69_Re: PLEASE HELP - Need to change the Password for a SCHEDULED BACKUP!13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 20 Apr 2000 09:36:51 -0500622_US-ASCII Hello Joel, Go into the journal and select the system name where the job(s) are you want to change. Right click on pendind job(s) and select reschedule. You can change you userid and password here. HTH Kurt

>>> jzigel1@HALLMARK.COM 04/20/00 09:04AM >>> Hi All ....

The Network people did something wierd on me. They changed the administration password on me for some reason or another but regardless of that fact ... my backups last night (they changed it yesterday afternoon) failed because of error - SQL1403 - Invalid user name and/or password. I created these backups using the backup with [...] 26346 89 22_Re: DB2 v6 parallelism19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 20 Apr 2000 09:42:52 -0500633_iso-8859-1 Chris, DB2/OS390 V6. This topic was discussed recently (see the archives). The consensus indicated that parallelism should only be used when the cost of DB2 setting up the parallelism at run-time (which can be considerable) is offset by a reduction to the total elapsed "wall time" compared to DEGREE(1). This would indicate that your stated indicators would be a good place to start but that testing would then be required. Someone may have provided a way to isolate DB2 parallelism setup time from DB2PM reports, can't remember for sure. See DB2 Admin. Gde., Chapter 5-11, Parallel Operations and Query Performance [...] 26436 126 19_Re: keepdynamic=yes13_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 20 Apr 2000 15:40:30 +0100541_us-ascii



Pierre,

Have you set the MAXKEEPD ZPARM parameter? This determines how many dynamic SQL statements you want to keep cached. Also keep an eye on your EDM pool - it will probably need to be increased if you use a lot of dynamic SQL caching.

Adrian Savory ACSIS Ltd





|--------+-----------------------> | | Pierre | | | Dagenais | | | | | | | | | 20/04/00 | | | 15:22 | | | Please | | | respond to | | | DB2 Data Base| | | Discussion | | | List | | | [...] 26563 89 32_Re: Unique vs Non Unique Indexes0_19_Tim.Lowe@STPAUL.COM31_Thu, 20 Apr 2000 09:07:34 -0500502_us-ascii Ken, This thread seems to have created a life of it's own, but I am still curious about your basic question. I apologize if this has already been asked.

You said that "Field A is makes the record unique in itself", and then noted that index 1 was non unique, and index 2 was Unique. The question that you then asked started with: Since Index 2 is always going to be Unique anyway, due to the existence of Field A..... It's obvious that Index 2 should be defined as a Unique Index. [...] 26653 27 31_Constraints: Dynamic or Static?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 20 Apr 2000 10:08:21 -0500318_iso-8859-1 Hi all and TIA, Done research on this and can't find a definitive answer yet. Given some table has a check constraint on a column, during the bind process, is constraint checking (a look at SYSCHECKS) bound statically along with the SQL statement? If not, how is it done? Dynamically at INSERT/UPDATE? [...] 26681 29 38_Lucent Technologies TotalView Database23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Thu, 20 Apr 2000 11:17:11 -0400451_iso-8859-1 Fellow DB2'ers

Has anyone undertaken the construction of a relational database (DB2) that reflects the content of the Lucent Technologies; TotalView Customer Service Center data store. Lucent, currently stores the call information in a proprietary data store using DISAM and circular data files.

I recognize the uniqueness of this request, for I am looking to save significant time in the keying of the table create DDL. [...] 26711 53 35_Re: Constraints: Dynamic or Static?14_John P Shipley24_jpshiple@DUKE-ENERGY.COM31_Thu, 20 Apr 2000 11:29:00 -0400276_us-ascii No it should be done using the DBD or dsndb01. It is not tied to the SQL statement. You can add and drop the constraints and the packages associted with the table will not be invalidated and no auto rebinds will occur to my knowledge. It is checked at run time. [...] 26765 64 40_Re: Variables in SYSIN Select statements12_Jim Harrison12_jimh@QIS.NET31_Thu, 20 Apr 2000 11:10:05 -0400424_us-ascii Thanks to all who replied to this subject. I gave QMF a quick look, but it appears the only way to do what I want is to invoke ISPF file tailoring services which I can just do on my own, without QMF (and the pain!). In fact, that's what I've done for the short term anyway. I would have liked to write a wrapper or "pre-compiler" but I just don't have the time. Maybe I'll get to it during the next recession. [...] 26830 91 40_Re: Variables in SYSIN Select statements15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Thu, 20 Apr 2000 12:18:55 -0400379_us-ascii You may want to ask this question on IBM-MAIN@BAMA.UA.EDU. There should be several utilities these members have to do this. I use something I got a long time ago called PARMSWTR which I do not havve the source available any longer. This routine writes data from the PARM operand on exec pgm line to an 80 byte record file. HTH Bob Lawrence DBA Boscov's Dept Stores [...] 26922 43 47_Determining DB2 Version from within ESQL/C code11_Jim Keohane19_jimkeo@LOCKSTAR.COM31_Thu, 20 Apr 2000 13:14:11 -0400462_us-ascii Can someone suggest an easy way to determine if DB2 V4, V5 or V6? In fact, I can probably live with knowing whether at V5 or not.

I could do a prepare of an SQL statement with a scalar function that's not supported until V6 embedded sql. Maybe there's another scalar function at V5 that V4 doesn't support via embedded sql. One drawback might be that I do not know what tables exists that I have access to when a user first invokes my code. [...] 26966 33 69_Is there an esql/c equivalent of CLI/ODBC scalar function DATABASE()?11_Jim Keohane19_jimkeo@LOCKSTAR.COM31_Thu, 20 Apr 2000 13:16:07 -0400127_us-ascii What does CLI/ODBC scalar function DATABASE() return and how can it be mimicked via embedded sql only? Thanks!

27000 42 51_Re: Determining DB2 Version from within ESQL/C code14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Thu, 20 Apr 2000 10:19:58 -0700432_iso-8859-1 Jim, When you use CAF and do an EXPLICIT connect call, there is a control block returned called the RIB. This block contains the version of the DB2 instance you just connected to. You can also issue an SQL CONNECT statement and the db2 version (in form DSNnnnnn) is returned in one of the SQLCA fields. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 27043 25 40_Phoenix, Arizona DB2 Tech Day Invitation11_James Drewe20_James.Drewe@AEXP.COM31_Thu, 20 Apr 2000 10:38:37 -0700417_- For those of you that may be in the Phoenix, Arizona area next week, the Southwest Area Relational Users Group will be hosting a DB2 "Tech Day" conference. The theme will be DB2 and e-commerce. The keynote speaker will be Richard Yevich, and other technical presentations will be made by speakers from IBM, BMC, Computer Associates, and others. It will be held on Wednesday, April 26. The cost will be US$115. [...] 27069 82 35_Re: Constraints: Dynamic or Static?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 20 Apr 2000 12:51:22 -0500626_iso-8859-1 Hi John, Thanks for the sanity check. Someone almost had me convinced otherwise. Best Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is [...] 27152 26 38_How do you to clear hung archive task?0_17_JTonchick@AOL.COM29_Thu, 20 Apr 2000 14:24:45 EDT411_US-ASCII We had a situation where a hardware error on a tape drive caused the DB2 archive task to hang. The operations staff could not swap the mount request to a different drive, so in there finate wisdom they canceled the DB2PMSTR task to clear the drive! They never bothered to contact anyone. I heard about the incident when I arrived at work the next morning. Fortunately, DB2 restarted successfully. [...] 27179 58 42_Re: How do you to clear hung archive task?13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Thu, 20 Apr 2000 11:30:18 -0700518_iso-8859-1 Hi!

Depending on the version of DB2 you are on, you could try -ARCHIVE LOG CANCEL(OFFLOAD)

command (I think it has come only in Ver.5). By this, any suspended offload operations are started and by disabling the 'bad' drive now, archiving will occur on a 'good' drive.

Thanks.

Ashish.

-----Original Message----- From: JTonchick@AOL.COM [SMTP:JTonchick@AOL.COM] Sent: Thursday, April 20, 2000 11:25 AM To: DB2-L@RYCI.COM Subject: How do you to clear hung archive task? [...] 27238 80 34_Re: Removing Entries from SYSLGRNX19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 20 Apr 2000 13:30:15 -0500593_iso-8859-1 Andreas, If you have tablespaces in SYSLRGNX that go back to their creation they probably have never been image copied, or they've been image copied but no MODIFY has been done to delete old copies. MODIFYing SYSCOPY also cleans up SYSLGRNX. BTW, I probably have SYSLGRNX wrong somewhere in this email! 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 [...] 27319 54 42_Re: How do you to clear hung archive task?13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Thu, 20 Apr 2000 11:33:43 -0700655_us-ascii Oops,

Double checked it. The ARCHIVE LOG command is present in V4 too. So, I believe you could have used that.

Thanks.

Ashish.

-----Original Message----- From: JTonchick@AOL.COM [SMTP:JTonchick@AOL.COM] Sent: Thursday, April 20, 2000 11:25 AM To: DB2-L@RYCI.COM Subject: How do you to clear hung archive task?

We had a situation where a hardware error on a tape drive caused the DB2 archive task to hang. The operations staff could not swap the mount request to a different drive, so in there finate wisdom they canceled the DB2PMSTR task to clear the drive! They never bothered to contact anyone. I heard [...] 27374 42 42_Re: How do you to clear hung archive task?13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 20 Apr 2000 13:33:22 -0500508_US-ASCII Jim, How about ARCHIVE LOG CANCEL OFFLOAD Kurt

>>> JTonchick@AOL.COM 04/20/00 01:24PM >>> We had a situation where a hardware error on a tape drive caused the DB2 archive task to hang. The operations staff could not swap the mount request to a different drive, so in there finate wisdom they canceled the DB2PMSTR task to clear the drive! They never bothered to contact anyone. I heard about the incident when I arrived at work the next morning. Fortunately, DB2 restarted successfully. [...] 27417 56 40_Re: Variables in SYSIN Select statements11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 20 Apr 2000 14:46:12 -0400456_iso-8859-1 Here is a simple rexx to provide some of this feature..

/* REXX NAME: PARMLINE USE REXX TO WRITE SYSIN PARM RECORD FROM PROGRAM PARM INPUT USEFUL TO AVOID HAVING TO CODE PARMLIB INPUT FOR ALL POSSIBLE CASES. THIS CREATED SYSIN CAN BE "WRAPPED" WITH OTHER SYSIN I.E. //SYSIN DD DSN=MY.PARM(PARMS001) // DD DSN=&&PARMLINE (FROM REXX) // DD DSN=MY.PARM(PARMS002) NOTES. 1. PARMLINE WILL BE UPPER CASE 2. PARM MUST BE ENCLOSED IN QUOTES [...] 27474 48 24_Parallelism & plan_table13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM31_Thu, 20 Apr 2000 14:50:49 -0400310_us-ascii The scenario is as follows ;

There are two tables , T1 and T2 .

T1 is partitioned , having 2 partitions , and 5 million records . The partitioning key is Part_nbr and Call_date . Call dates from 02-01-2000 to 02-15-2000 are in part 1 , from 02-16-2000 to 02-28-2000 are in part 2 . [...] 27523 17 39_Who's doing Dynamic sequential prefetch10_Tom Taylor17_ttaylor@CHUBB.COM31_Thu, 20 Apr 2000 15:06:41 -0400372_us-ascii Hi all

Is there an easy way to determine which package or plan is invoking dynamic sequential prefetch.

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 27541 77 28_Re: Parallelism & plan_table20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 20 Apr 2000 15:01:51 -0400382_us-ascii If you are running explain under spufi then it is possible that your spufi plan may have bound with DEGREE=1

You could try SET CURRENT DEGREE = 'ANY' before explain and see if there is any difference.

Why are you not mentioning part_no for T1, which is the first column of the table. Are you getting PAGE_RANGE column of the PLAN_TABLE as 'Y' or blank ? [...] 27619 127 21_DB2 Connect V6.1 P.E.17_Alessandro Brezzi28_brezzi.isac@INTERBUSINESS.IT31_Thu, 20 Apr 2000 21:05:14 +0200447_us-ascii Hi all, I just installed a brand new MP3000, configured DB2 v5.1 and DDF, installed a client DB2 Connect v5.1 for Win NT and tested: all work just fine. Then I installed a DB2 Connect v6.1 for Win 98 and found: the DataBase type in the protocol tab of the configuration wizard is missed: where I specify DB2 for OS/390? the connection don't work ... I receive an SQL code from the local machine: nothing displayed in the OS/390 side [...] 27747 42 43_Re: Who's doing Dynamic sequential prefetch20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 20 Apr 2000 15:13:26 -0400276_us-ascii There are no traces, which I could think of except IFCID 6 & 7. But even that 1. Indicates ACE, which will not fully ensure that you hook on to the right transaction. 2. If you specify plan or auth while using IFCID 6 & 7 then Sequential prefetch is not traced. [...] 27790 60 42_Re: How do you to clear hung archive task?11_Jerry, Jeff24_Jeff.D.Jerry@NORWEST.COM31_Thu, 20 Apr 2000 14:13:59 -0500394_- DB2 Version 4? and above:

ARCHIVE LOG CANCEL OFFLOAD

We have had to do this a few times in the past and it has saved us.

-----Original Message----- From: Kurt Sahlberg [SMTP:Kurt.Sahlberg@EXPERIAN.COM] Sent: Thursday, April 20, 2000 1:33 PM To: DB2-L@RYCI.COM Subject: Re: How do you to clear hung archive task?

Jim, How about ARCHIVE LOG CANCEL OFFLOAD Kurt [...] 27851 33 38_Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Thu, 20 Apr 2000 14:22:38 -0500577_iso-8859-1 Hi, I have a performance problem in the following query. I am running a strobe and that shows it is taking lot of time searching the index. SELECT RETL_A FROM ITEM_STORE WHERE DEPT_I = :WS-DEPT AND CLASS_I = :WS-CLASS AND ITEM_I = :WS-ITEM AND STORE_I = :WS-STORE

ITEM_STORE is partioned table and it has partioned index (10 partitions on full key). Primary key is composite and they are Dept, Class, Item and Store. From the explain output I could see that it uses the index which is build on primary key only. That is the only index available for this [...] 27885 77 29_Re: DB2 for OS/390 and SAP BW12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 20 Apr 2000 14:13:25 -0500678_- On Wed, 12 Apr 2000 03:54:14 -0500, adriancollett wrote:

>Does anybody know where I can get info on SAP BW and DB2 OS/390 ?? Web sites, redbooks etc. > >Also, is anybody ACTUALLY using DB2 OS/390 (V6) and SAP BW(V1.2B) ? > >Any help would be greatly appreciated - and as usual - will be remunerated with copius quantities of your favourite liquid. > >TIA, > >Adrian Collett >DB2 Consultant, >Milan, Italy. > > > >-- >TiscaliFreeNet, libero accesso ad Internet. >http://www.tiscalinet.it > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at [...] 27963 82 28_Re: Parallelism & plan_table14_John P Shipley24_jpshiple@DUKE-ENERGY.COM31_Thu, 20 Apr 2000 15:31:47 -0400563_us-ascii We down loaded Visual Explain (free) from IBM and use it. You can easly flip back and forth between degree 1 and any and see if the path changes. If the optimizer thinks the dimentional table can be accessed and eliminate many of the rows then go into the larger table on an index he will sometimes choose to not scan the larger table. Only having 2 partitions is the most likely cause as he knows 2 parallel task will not perform adequately. You should consider having many more parts on the larger table and also consider partitioning the smaller [...] 28046 56 42_Re: Performance on using partitioned Index20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 20 Apr 2000 15:36:19 -0400470_us-ascii What is the matchcol (from plan_table) ? Is there any prefetch used ? Since you are using entire primary key and that also happens to be partitioned key, explain should not choose any prefetch and matchcol should be 4. If not then there may be data type mis-match.

-----Original Message----- From: Sekar.S (Exchange) [SMTP:Sekar.S@TARGET.COM] Sent: Thursday, April 20, 2000 3:23 PM To: DB2-L@RYCI.COM Subject: Performance on using partitioned Index [...] 28103 23 19_mapping table sizes20_Johnson, Bill (GLIC)18_bjo23@ALLSTATE.COM31_Thu, 20 Apr 2000 14:30:28 -0500419_- All, We are in the process of changing all of our reorgs to shrlevel change in order to facilitate 24x7 availability. We run these during the weekend when there is a minimum of activity. Can anybody provide some guidance as to the size of the mapping table for a table that contains 1 million rows? Or is this simply a function of the amount of activity on the table during the reorg? Our environment is DB2 V6. [...] 28127 80 42_Re: Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Thu, 20 Apr 2000 14:43:42 -0500519_iso-8859-1 I am just doing the explain in Platinum and I am not having any PLAN_TABLE. Can you please explain how do I get a PLAN_TABLE table in my ID and to get the result. I was thinking that what we are getting thru Platinum "E" is the explain output.

All datatypes what we have used is correct. Dept is small int and S9(4) comp. Like this all the values are matching, I have checked all the variables. RETL_A is Decimal(9,2) and the host variable is s9(7)v9(2). Also there are no field procedures used. [...] 28208 35 36_How to manage the first application?21_=?gb2312?B?19693A==?=20_zoujie@MAIL.DCAC.COM31_Thu, 20 Apr 2000 15:56:07 +0800609_gb2312 Dear list: We are going to develop our first application on DB2 platform,but we have some some problems since we have no too many experience. Could anybody tell me some rules on developing application such as: 1.Is it necessary to build alias for each application? Or use the DB2 system alias(we use DSNCAT)? What the advantage and disadvantage of it? 2.How to constraint the user privileges to build each table in each tablespace? 3.How to grant user privileges and let it is easy to develop program and easy to migrate to production platform? (We have programmers,several application DBAs and a [...] 28244 184 60_Repost: Pre-registration for DB2 Certification Exams at IDUG13_Morrill, John12_JohnM@VP.NET31_Thu, 20 Apr 2000 18:04:50 -0600705_- Greetings!

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

Cheers!

J. Michael Morrill Chief DB2-L List Owner =================================================



Dear DB2 Interested Parties,

IBM is offering free DB2 Certification again this year at the IDUG North American conference (May 14-18 in Dallas, see http://www.idug.org ). A preparation class for DB2 Certification is being offered on Sunday, May 14, making it easy to get ready for certification while at the conference that week. [...] 28429 70 42_Re: Performance on using partitioned Index10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 03:43:48 GMT570_us-ascii Hi Sekar, Ideally, your query should use only one partition/select during run time as all the 4 columns of the primary index should be used (matchcols = 4) . Which index is used in the explain output ?.Which is the partition index ?. I get a feeling that if primary index and partition index are different ,and partition index include columns from promary index then query might follow the partition index as it might try to access particular partition(My guess). If primary index is used and it is slow then one solution which i can think of is bind your [...] 28500 115 28_Re: Parallelism & plan_table10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 03:59:50 GMT407_us-ascii Hi Jaydeep, I get a feeling that it is a tablespace scan for the partitioned tablespace. By the way, Venkat can you explain how the DEGREE of spufi plan can affect the explain output ?. Does DB2 has to use parallelism to decide parallelism ?

Regards

Sanjeev









"Pillay, Venkat (PCA)" @RYCI.COM> on 04/21/2000 01:31:51 AM [...] 28616 44 39_Who's doing Dynamic sequential prefetch14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 21 Apr 2000 14:07:39 +1000624_us-ascii Tom,

Its 'dynamic'. So you can't tell in advance. You can see it when its actually happening or afterwards.

Use DB2PM Accounting Detail report, or Display Bufferpools, or I often use Omegamon Object Analysis (while its happening) at our site since we have that product.

Of course if you know that a table or index is being scanned sequentially by the one SQL, then you can predict a reasonable level of dynamic prefetch if its not Sequential or List Prefetch. There are rules for Dynamic that must be obeyed of course. Anyway runtime monitoring to check expected dynamic prefetch actually [...] 28661 77 41_Re: Questions in regarding sparse indexes14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 21 Apr 2000 14:08:00 +1000423_us-ascii Paul,

You certainly can index by this column and make it NULLable. Of course updates to the column will have to delete and reinsert the index entry so reorg the index frequently.

Due to the large number of entries that are Null, DB2 Optimizer will not understand the filtering for equals predicate so you will need to adjust the COLCARDF for this column, to improve the Optimizer understanding. [...] 28739 28 42_Re: How do you to clear hung archive task?18_Rajesh Kumar Sinha24_rksinx@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 04:02:46 GMT602_us-ascii Hi, We have faced this problem 3-4 times, and solution is -ARCHIVE LOG CANCEL(OFFLOAD). We are keeping dual copy of archive logs.

Suppose cartridge drive 1 & 2 are selected for log offload. After mount of cartridge, if it identifies error in one of the cartridge drive then issue ARCHIVE LOG command. By accepting this command drive in error would be rejected and single copy log offload will take place. If both the cartridge drive are in error then system would look for drive available for single copy log offload. You should put cartridge drive off-line by giving vary command [...] 28768 44 40_Re: How to manage the first application?18_Rajesh Kumar Sinha24_rksinx@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 04:59:09 GMT359_us-ascii Hi ZOE,

1. First I should know whether your DB2 datasets are SMS managed or DB2 managed. If it is SMS managed, no point going for separate aliases for different application system. If it is DB2 managed than you will certainly have to create alias for each application system. Application system I mean to say group of programs or module. [...] 28813 20 19_DB2 data on the net12_Rakesh Kumar21_rakesh457@HOTMAIL.COM29_Fri, 21 Apr 2000 05:44:48 GMT389_- Hi ALL, Could anyone tell how to access Db2 data on the net and what are the hardware and software support required and about the web server ,Net.data . We are running DB2 V5 and are a banking division . Thanx in Advance . RAKESH KUMAR ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com [...] 28834 38 40_Re: Variables in SYSIN Select statements14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Apr 2000 08:49:08 +0200315_us-ascii Sorry for the question, but do you want to use batch or QMF ? There's a way to build an

entire variable query, i.e. a PUBLIC query like:

&var1 &var2 & var3 ........... ........... &varn

You can store it in QMF and then using DSQCIX instruction to pass strings or query via REXX o [...] 28873 31 23_Re: mapping table sizes18_Rajesh Kumar Sinha24_rksinx@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 06:55:43 GMT389_us-ascii Hi Bill, The number of rows in the mapping table should not exceed 110% of the number of rows in the table space or partition to be reorganised. But you are right it depends on activity, because LRSN of mapping table is related to no of change during REORG . Reclength of the mapping table is 21, going by 110% logic, space should be 21 * (1000000 + 1000000*.10) = 23100 KB. [...] 28905 20 51_Re: Determining DB2 Version from within ESQL/C code14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Apr 2000 09:27:14 +0200418_us-ascii I observed that calling CAF via REXX you can see your current DB2 version in RIBREL

field. I think it may be useful to you.

HTH

Regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 28926 75 19_Re: keepdynamic=yes11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Fri, 21 Apr 2000 04:38:10 -0400363_ISO-8859-1 Hi Pierre

Two possible things come to mind.

1. PB is not issuing Commits. The Local Cache activated with KEEPDYNAMIC YES only benefits you if a Prepare is avoided after a Commit has destroyed the Previous prepare result.

or

2. The PB cache is not doing what you expect and PB is always re-issuing Prepare after Commit. [...] 29002 141 42_Re: Performance on using partitioned Index17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Fri, 21 Apr 2000 13:13:18 +0100437_us-ascii Hi Sanjeev



Sekar says that the primary index is the only index on the table. So it must be the partitioning index as well. If that is not the case, and if the partitioning index is different from primary index, and if he wants the optimizer to use partitioning index instead of primary index, but at the same time wants to specify the primary key columns in the predicate then he can modify the predicate as [...] 29144 52 25_Re: DB2 Connect V6.1 P.E.13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Fri, 21 Apr 2000 08:14:00 -0400364_US-ASCII Hi Alessandro,

Did you install DB2 Connect v6.1 PE or EE?

I had the same results you did when trying to install DB2 Connect V6.1 PE.

It has been my experience that the PE (Personal Edition) does not allow connection to OS/390 but the EE (Enterprise Edition) will allow connection to OS/390.

Hope this helps, Burgess Evans [...] 29197 11 25_Re: DB2 Connect V6.1 P.E.12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Fri, 21 Apr 2000 07:27:38 -0500385_- I am not sure what your problem is but I wanted to say we have multiple customers using PE and connecting to DB2 for OS/390 without problems.

================================================ 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. 29209 48 23_Re: mapping table sizes12_Greg Popwell27_greg.popwell@MOTION-IND.COM31_Fri, 21 Apr 2000 08:06:00 -0500656_iso-8859-1 This has been discussed before. The tablespace for the table can be very small. It's the indexspace that must be sized properly.

Greg Popwell - DBA, Motion Industries

-----Original Message----- From: Rajesh Kumar Sinha [mailto:rksinx@LOT.TATASTEEL.COM] Sent: Friday, April 21, 2000 1:56 AM To: DB2-L@RYCI.COM Subject: Re: mapping table sizes



Hi Bill, The number of rows in the mapping table should not exceed 110% of the number of rows in the table space or partition to be reorganised. But you are right it depends on activity, because LRSN of mapping table is related to no of change during REORG . Reclength [...] 29258 146 42_Re: Performance on using partitioned Index10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Fri, 21 Apr 2000 13:19:47 GMT545_us-ascii Hi , Yes , As table has one index only so partition one is primary .I am sorrry to overlook that. If this the situation i think Explain output and some monitor's report can help to analyse what is happening because a query with all the matching columns with the primary index returns only one row and it should never take more time. I get a feeling that PAGE_RANGE in the explain should be 'Y' and if it is 'Y' then otimizer has decided that only particular partition to be scanned during bind time, and which one ,will be decided [...] 29405 74 25_Re: DB2 Connect V6.1 P.E.14_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 21 Apr 2000 09:39:08 -0400360_iso-8859-1 DB2 Connect PE certainly can be used to connect to DB2 on the host via SNA or TCP/IP. Follow the Quick Beginnings Guide for PE and you shouldn't have any problems as long as everything on the host is setup right. Phil

Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS [...] 29480 23 34_IEX TotalView Call Center Database23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Fri, 21 Apr 2000 09:41:03 -0400466_iso-8859-1 Fellow DB2'ers

Has anyone undertaken the construction of a relational database (DB2) that reflects the content of the IEX (Innovation Excellence) TotalView Call Center Workload data store. IEX currently stores the call center information in a proprietary data store using DISAM and circular data files.



I recognize the uniqueness of this request, for I am looking to save significant time in the keying of the table create DDL. [...] 29504 13 46_Recall: Lucent Technologies TotalView Database23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Fri, 21 Apr 2000 09:41:21 -0400343_- Sercombe, George W A133 would like to recall the message, "Lucent Technologies TotalView Database".

================================================ 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. 29518 217 42_Re: Performance on using partitioned Index0_19_Tim.Lowe@STPAUL.COM31_Fri, 21 Apr 2000 08:47:27 -0500557_us-ascii Sanjeev, I used to work at Target, and I have asked Sekar a few questions offline about his post. (and a few questions about the people I used to work with). Below are his answers (excluding the questions about the people I worked with): I am not DBA. I am application programmer and I am doing some performance tuning in my system. Now lot of stores and lot of items, so we have partioned table and partitioned index. Now the table has about 34887456 rows. Order of the index is Dept_i, Class_i, Item_i and Store_i. I am seeing thru platinum [...] 29736 12 22_Re: db2 install on vse0_27_Bud.Greenman@ONONDAGA.NY.US31_Fri, 21 Apr 2000 09:55:43 -0400411_- I wrote this for someone else. I forwarded on the request for specific information to him and will resend as soon as I get the response. Thanks for you quick response.

================================================ 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. 29749 303 42_Re: Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Fri, 21 Apr 2000 09:10:27 -0500385_iso-8859-1 Hi All, It is a simple select in the program. It is not the cursor. Can I still use the order by and will it do that trick for me? I have only one index as Tim mentioned that is partitioned. I want to access all the partitions in the program. I could see the ' ' in PAGE_RANGE field. I am not sure how you can make that one Y. Can you please tell me how can I do that? [...] 30053 32 25_Re: DB2 Connect V6.1 P.E.13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 21 Apr 2000 10:25:28 -0400461_iso-8859-1 I also am unable to configure OS/390 connections using DB2 P.E. v6.1 on Win98 or Win95. I have no problems with Win NT or 2000 with PE or EE. I have configured dozens on NT. Also PE for 5.2 works just fine.

Using the Client Config Assistant, when I select protocol TCP/IP, the other 4 radio buttons do not appear. These are the buttons for OS/390, VM, 400 or Lan based. If I continue the config, the test connection fails with SQL10007N. [...] 30086 343 42_Re: Performance on using partitioned Index17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Fri, 21 Apr 2000 15:23:38 +0100671_iso-8859-1 Hi Sekar

Can you tell me what the cluster ratio is on the index IPITM141?. And are you reading your values for host vars from an input file?.

Thanks and regards.

Nagaraj



______________________________ Reply Separator _________________________________ Subject: Re: Performance on using partitioned Index Author: Sekar.S (Sekar.S@TARGET.COM) at unix,mime Date: 21/04/00 15:10



Hi All, It is a simple select in the program. It is not the cursor. Can I still use the order by and will it do that trick for me? I have only one index as Tim mentioned that is partitioned. I want to access all the partitions in [...] 30430 62 25_Re: DB2 Connect V6.1 P.E.17_Alessandro Brezzi28_brezzi.isac@INTERBUSINESS.IT31_Fri, 21 Apr 2000 16:32:46 +0200551_us-ascii Thanks all, I'm using 6.1 PE for WIN 98, not shure the version is, but obtained in mid-98 from IBM ... as already staded, the 5.1 PE version for Win NT is workink fine ... I ask IBM for a copy of 6.1 EE.

Thanks

Alessandro

>DB2 Connect PE certainly can be used to connect to DB2 on the host via SNA >or TCP/IP. Follow the Quick Beginnings Guide for PE and you shouldn't have >any problems as long as everything on the host is setup right. Phil > >Philip K. Gunning >DB2 DBA >IBM Certified Advanced Technical Expert - [...] 30493 29 22_Re: db2 install on vse0_27_Bud.Greenman@ONONDAGA.NY.US31_Fri, 21 Apr 2000 10:42:49 -0400743_- {\rtf1\ansi\ansicpg1252\deff0\deftab720{\fonttbl{\f0\fswiss MS Sans Serif;}{\f1\froman\fcharset2 Symbol;}{\f2\froman Times New Roman;}} {\colortbl\red0\green0\blue0;} \deflang1033\pard\plain\f2\fs20 \par } ------------------( Forwarded letter 1 follows )-------------------- Date: Fri Apr 21 10:15:56 2000 To: Bud.Greenman Sender: Fred.Folmsbee@orps.state.ny.us Subject: Re:FW:Re: db2 install on vse

we have a pc330 running in our shop. this is the model that has the p390(vse/esa) running on it. when it was installed db2 was put up in partition 8, but our software at time was vsam oriented so time has elapsed and we are now developing the equivalent application to run in db2. we have no system staff, so we are at a loss as [...] 30523 33 40_Re: How to manage the first application?14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Fri, 21 Apr 2000 07:52:52 -0700429_iso-8859-1 Rajesh, I agree with everything but the first comment. I feel that with SMS it makes even more sense to use separate aliases. That will make it easier for the SMS admins to code the SMS storage group ACS routines. In addition, when you have separate VCAT's defined, storage useage is simplified, regardless of SMS or non-SMS. Having everything in one alias makes using ISPF 3.4 and/or IDCAMS jobs more difficult. [...] 30557 30 23_Re: mapping table sizes14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Fri, 21 Apr 2000 07:56:46 -0700343_iso-8859-1 I seem to recall that the mapping tablespace really isn't used, all the data is put in the required index, so the space in the index is whats really important. You may want to search the archives for more on this. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 30588 68 25_Re: DB2 Connect V6.1 P.E.12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 21 Apr 2000 08:20:35 -0700292_us-ascii That's surprising. I've configured a number of PE v6.1 sessions on Win98 and Win95 with no problems whatsoever. In fact that's what I use on my own machine. Just tried the CCA and it brings up the buttons just fine. Can use TCPIP not only to OS/390 but to AS/400 as well on v6. [...] 30657 92 25_Re: DB2 Connect V6.1 P.E.14_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 21 Apr 2000 11:34:44 -0400281_iso-8859-1 Myron, I have also seen the problem Dave is describing when going from 5.2 to 6.1 and back and forth, etc during testing. Seems to happen when the install does not complete successfully but appears as if it has. This is very rare and I have only seen it once. Phil [...] 30750 16 25_Re: DB2 Connect V6.1 P.E.13_Noreen Prenty22_Noreen_Prenty@HESC.COM31_Fri, 21 Apr 2000 11:19:57 -0400349_us-ascii A longshot but a similar thing happened to me once - I thought DB2 Connect Personal Edition was on the same install CD as the UDB Personal Edition. It wasn't and was I confused when I didn't see the second set of radio buttons doing a client configuration . When I installed PE Connect, everything started working OK. Hope this helps. [...] 30767 49 40_Re: How to manage the first application?13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Fri, 21 Apr 2000 10:36:30 -0500551_us-ascii As always there is more than one way to do things. I believe that there should be one VCAT per subsystem. That way you can user ISPF 3.4 to look at all the data sets in a particular subsystem. You don't have to use SMS to use STOGROUPS. Just define one STOGROUP per pack (including the pack name in the STOGROUP name) with one VCAT per subsystem. Then you have the advantage of pack placement without the hassle of IDCAMS jobs. Have your application id somewhere in your database name. Then if you want to look at only your applications [...] 30817 363 42_Re: Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Fri, 21 Apr 2000 10:47:50 -0500523_iso-8859-1 Hi, I could see cluster ratio 99 in production and 0 in test environment where I am running the program.



Thanks and Regards, Sekar.S. -----Original Message----- From: Nagaraj Pudukotai [mailto:Nagaraj.Pudukotai@WDR.COM] Sent: Friday, April 21, 2000 9:24 AM To: DB2-L@RYCI.COM Subject: Re: Performance on using partitioned Index



Hi Sekar

Can you tell me what the cluster ratio is on the index IPITM141?. And are you reading your values for host vars from an input file?. [...] 31181 47 23_Re: DB2 data on the net17_Linda F. Claussen18_lindafc@NETINS.NET31_Fri, 21 Apr 2000 10:53:39 -0500864_ISO-8859-1 Rakesh,

Try the following 2 library links. They contain all the documentation you will need.

http://www-4.ibm.com/software/data/net.data/library.html http://www-4.ibm.com/software/webservers/library.html

Linda F. Claussen Claussen & Associates, Inc DB2 Consulting and Training lindafc@netins.net http://www.netins.net/showcase/lclaussen/



---------- From: Rakesh Kumar To: DB2-L@RYCI.COM Subject: DB2 data on the net Date: Friday, April 21, 2000 12:44 AM

Hi ALL, Could anyone tell how to access Db2 data on the net and what are the hardware and software support required and about the web server ,Net.data . We are running DB2 V5 and are a banking division . Thanx in Advance . RAKESH KUMAR ________________________________________________________________________ Get Your Private, [...] 31229 114 32_compression dictionary- dsn1copy12_HEPP SHERY C17_schepp@SRPNET.COM31_Fri, 21 Apr 2000 08:55:15 -0700515_ISO-8859-1 I currently support a peoplesoft financials application and am working on a data migration from one db2 subsystem to another. Due to the number of tables in this application we've set up a process that uses the production image copies as input and migrates the data using dsn1copy. The problem I'm having with this process is some of the tablespaces that are compressed encounter an -904 on the compression dictionary in the recover index step. The error message in the dsnmaster log is as follows: [...] 31344 22 34_When to create a new DB2 Subsystem0_19_RTurpen@WOODMEN.COM31_Fri, 21 Apr 2000 11:00:18 -0500338_us-ascii We are running DB2 for OS/390 Version 5.1 with 3 subsystems for PROD, TEST, and DEVL. We are trying to decide at what point it is necessary to build another PROD subsystem before the current subsystem is overloaded. Do you go by number of objects(TS,TB,IX), the size of the catalog and directory, or some other measurement? [...] 31367 407 42_Re: Performance on using partitioned Index17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM31_Fri, 21 Apr 2000 17:08:23 +0100547_iso-8859-1 Hi Sekar

Thanks for the info. Are you reading your input values in the predicate you are using, from an input file. If so this means that you are doing synchronous I/o for most of the time ( becuase cluster ratio is 0). If you read your input values (for the host vars) in cluster sequence (i.e your PK sequence), and if your tablespace is reorged then you might benifit from asynchronous I/O. But the assumption for this is that it is possible for you to read get the host vars in the cluster sequence?. Is that the case?. [...] 31775 451 42_Re: Performance on using partitioned Index0_19_Tim.Lowe@STPAUL.COM31_Fri, 21 Apr 2000 11:50:41 -0500412_us-ascii Sekar, If the clusterratio is 0, then I would also wonder how many levels the index is in. And, since this is a test system, I would also like to know if the runstats are current.

Thanks, Tim













Nagaraj Pudukotai on 04/21/2000 11:08:23 AM

Please respond to DB2 Data Base Discussion List [...] 32227 125 36_Re: compression dictionary- dsn1copy12_HEPP SHERY C17_schepp@SRPNET.COM31_Fri, 21 Apr 2000 10:35:04 -0700396_ISO-8859-1 This has been resolved. I rechecked the obids and found a discrepancy in the translation- even though the error was on the compression dictionary- it had nothing to do with compression.

-----Original Message----- From: HEPP SHERY C Sent: Friday, April 21, 2000 8:55 AM To: 'DB2 Data Base Discussion List'; 'DBeulke@Compuserve.com' Subject: compression dictionary- dsn1copy [...] 32353 502 42_Re: Performance on using partitioned Index0_18_damcon2@US.IBM.COM31_Fri, 21 Apr 2000 14:18:11 -0400534_us-ascii If clusterratio is 0 then runstats probably hasn't been run.

Cheers, Jay



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



Tim.Lowe@STPAUL.COM@RYCI.COM> on 04/21/2000 12:50:41 PM

Please respond to DB2 Data Base Discussion List [...] 32856 68 38_Re: When to create a new DB2 Subsystem19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 21 Apr 2000 13:39:42 -0500627_iso-8859-1 Ray, In my perhaps limited experience, I have never seen a production OS390 image need, because of DB2 capacity reasons (given a decently tuned, adequate I/O subsystem), more than one DB2 subsystem. I have seen more than one DB2 subsystem on a single OS390 image simply because that particular site, for reasons of their own, wanted to separate different kinds of workloads, e.g. OLTP from batch, or SAP from Data Warehousing. However, with the "tuning knobs" and hand-shaking available between OS390 and DB2, making these wholly different workloads "play well with each other" is much easier than in the past. [...] 32925 21 33_DB2 connect version compatibility12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Fri, 21 Apr 2000 13:42:45 -0500401_- I have asked this question to IBM but want to get an answer from the users.

When I use DB2 connect PE V6, that we get when we order DB2 for OS/390 V6, to bind the packages on DB2 for OS/390, will only V6 DB2 connect software, PE and EE, be able to connect to OS/390. Will this require all users of V5.1 and earlier to upgrade to V6 to be able to connect with the V6 pacakages on OS/390? [...] 32947 31 37_Re: DB2 connect version compatibility12_Jim Harrison12_jimh@QIS.NET31_Fri, 21 Apr 2000 15:08:47 -0400487_us-ascii IIRC, each new version of DB2 connect comes with a new set of packages so theoretically it would seem like different versions could co-exist, however I believe we ran into problems when someone bound 5.2 to our system which had previously been using only 5.0. Sorry, I don't remember exactly what happened. But now that you've got me thinking about it, that happened right around the time we started experiencing hung DDF threads that never timed out (and we *still* are). [...] 32979 274 76_Re: Lowering the Priority of a Distributed Query after it hasbegun execution13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 21 Apr 2000 14:20:34 -0500465_iso-8859-1 Linda, thanks, interesting point to keep in mind. Our RELCURHL is NO. I have a feeling it must be something like this though. I'm wondering if it is a DB2 Connect package bind option possibly?

Dave





-----Original Message----- From: Linda Claussen [mailto:lindafc@netins.net] Sent: Wednesday, April 19, 2000 4:34 PM To: DB2-L@RYCI.COM Subject: Re: Lowering the Priority of a Distributed Query after it hasbegun execution [...] 33254 117 38_Re: When to create a new DB2 Subsystem22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM31_Fri, 21 Apr 2000 15:26:00 -0400397_iso-8859-1 Ray -

In addition to Rick's comments...

There is the 2GB address space region size upper limit. If your workload demands more than a 2GB address space region size (usually for xxxxDBM1), then you might want to consider multi-subsystem and possibly data sharing. My understanding is that future versions of OS/390 will raise the address space region size upper limit. [...] 33372 303 75_Re: Lowering the Priority of a Distributed Query after ithasbegun execution17_Linda F. Claussen18_lindafc@NETINS.NET31_Fri, 21 Apr 2000 14:31:59 -0500523_ISO-8859-1 Dave,

If the packages are bound with KEEPDYNAMIC(YES) this could cause the thread to stay on the active chain.

Might be worth checking.

Linda F. Claussen Claussen & Associates, Inc DB2 Consulting and Training lindafc@netins.net http://www.netins.net/showcase/lclaussen/



---------- From: Shapiro, Dave To: DB2-L@RYCI.COM Subject: Re: Lowering the Priority of a Distributed Query after ithasbegun execution Date: Friday, April 21, 2000 2:20 PM [...] 33676 447 42_Re: Performance on using partitioned Index10_Leo Flores23_leoflores@EARTHLINK.NET31_Fri, 21 Apr 2000 14:07:50 -0700327_us-ascii Or runstats was run when the table was empty and probably are not currently representative of the data that the table currently has.

Sekar, Tim Lowe had previously asked if the host variables were of the same length and type as the partitioning index columns. Have you had a chance to determined this yet? [...] 34124 475 42_Re: Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Fri, 21 Apr 2000 16:11:40 -0500482_iso-8859-1 All host variables are of the same length and same as partitioning columns. Absolutely no problem with that..

sekar.s.

-----Original Message----- From: Leo Flores [mailto:leoflores@EARTHLINK.NET] Sent: Friday, April 21, 2000 4:08 PM To: DB2-L@RYCI.COM Subject: Re: Performance on using partitioned Index



Or runstats was run when the table was empty and probably are not currently representative of the data that the table currently has. [...] 34600 511 42_Re: Performance on using partitioned Index0_19_Tim.Lowe@STPAUL.COM31_Fri, 21 Apr 2000 16:36:04 -0500573_us-ascii Leo, On of Sekar's more recent emails showed MATCHCOLS=4. Therefore, the host variables are defined correctly. (a previous email from him had indicated an "index scan", and therefore I had (wrongly) assumed matchcols was 0.)

It now appears that either runstats was run when it was empty or runstats was not run. In either case, runstats should be run now. He may need to talk to his DBA to get this done. After that, then we may find out the correct clusterratio, and the number of index levels. Then, we might find that he needs to have his DBA reorg [...] 35112 544 42_Re: Performance on using partitioned Index18_Sekar.S (Exchange)18_Sekar.S@TARGET.COM31_Fri, 21 Apr 2000 16:44:43 -0500423_iso-8859-1 Hi I have contacted one of our DBA and He is currently looking into that. I will let you know guys if something happens on this query. I am sorry if I have disturbed anyone.

Thanks and Regards, Sekar.S.

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Friday, April 21, 2000 4:36 PM To: DB2-L@RYCI.COM Subject: Re: Performance on using partitioned Index [...] 35657 10 38_Re: When to create a new DB2 Subsystem10_Bill Brown25_bill_brown@SPRINGMAIL.COM31_Fri, 21 Apr 2000 18:54:16 -0400274_- How about DB2 Data Sharing????

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