1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2000, week 1 2 53 34_UNDER CICS RUN DB2 ABEND CODE APCH19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW30_Tue, 2 May 2000 05:00:03 +0800213_big5 HI: I run cics v4.1 and db2 v4.1 program abend APCH, I think 1. precompiler jcl error 2. cics start procedure something error ANY ONE CAN PROVIDE PRECOMPILER JCL AND CICS START PROCEDURE THANK TANG SHENN TUAN 56 35 38_Re: UNDER CICS RUN DB2 ABEND CODE APCH10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Mon, 1 May 2000 10:04:23 GMT394_UTF-8

The discription of APCH in CMAC says that :

EXPLANATION: A request for a VS COBOL II program could not be executed because a problem has occurred during system initialization for VS COBOL II. This is probably due to the absence of VS COBOL II or Language Environment support.

SYSTEM ACTION: The transaction is abnormally terminated and the program is disabled. [...] 92 27 43_Can anyone recommend a good DB2 data editor12_Sams, Debbie13_dsams@UFL.EDU30_Mon, 1 May 2000 07:27:36 -0400536_iso-8859-1 Jim,

Since you alrelady have a BMC product you might consider another one. We use their Voyager product for browsing and editing data on the mainframe. In the last year or so they rebundled their products so you cannot still buy only Voyager. Also we have had some problems with them - we are actually paying for maintenance that we cannot receive. They also have trouble editing varchar columns. I have also been looking at Princeton Softech's Relational Tools for DB2 - sounds good but haven't seen a demo yet. [...] 120 32 12_Explaination10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Mon, 1 May 2000 12:06:03 GMT601_us-ascii Hi all, Can somebody help me to understand this statement in the Utility guide for DB2 UDB V6 for OS/390 (Chapter 2-11 ,Page no.195, Modify utility)

Improving REORG performance after adding a column If you add a column to a table space and the record was previously fixed-length, DB2 treats the record as variable-length. As a result, the REORG utility decompresses the rows of the table space during the UNLOAD phase and then compresses them again during the RELOAD phase. To remedy this, use the following procedure: 1. Run the REORG utility on the table space. 2. Run the COPY [...] 153 64 47_Re: Can anyone recommend a good DB2 data editor7_Jim Lee25_JLee@PRINCETONSOFTECH.COM30_Mon, 1 May 2000 08:42:57 -0400365_- Jim

I work for a software vendor, Princeton Softech, that sells a DB2 relational table editor, Access for DB2. It provides editing capabilities on a single table or multiple tables. You can dynamically join related tables into a single view and synchronously scroll through the related data. In addition, there is an interface to BMC Catalog Manager. [...] 218 48 20_Visual Explain Error19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Mon, 1 May 2000 09:42:30 -0400558_us-ascii The good news - I have installed Visual Explain for DB2 on my Windows 95 PC (using DB2 for Windows 95 to connect) it runs fine and I can point it to packages and do some analysis work.

The bad news - a colleague of my has installed in the same environment but is getting errors that are put in a "walkback.log" file. He is getting the error upon trying to connect but can connect via DB2 for Windows 95 - Here is a sample from that file. Walkback at 8:19:51 AM on 5/1/00 Primitive failed in: PlatformFunction>>#callWithArguments: due to [...] 267 31 21_Bufferpool thresholds9_Chu, Pius14_ChuP@CONED.COM30_Mon, 1 May 2000 09:56:02 -0400512_- I have a bufferpool that has 1000 buffers defined strictly for use of DSNDB07. I have 6 DASD workfiles total about 2800 cyls for sort work to support this bufferpool. Two of the workfiles have secondary extents that can be expandable. We use TMONDB2 to monitor. During a 15 minutes interval, each minute hit the Data Manager threshold (95%) and the Immediate Write threshold (97.5). The highest one is 890 for DM and 92 for IW, and yet the DASD workfiles never expanded. Can someone explain this? Thanks. [...] 299 68 16_Re: Explaination16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Mon, 1 May 2000 07:05:51 -0700304_iso-8859-1 When you add a column to an existing table, none of the existing rows are physically changed, so DB2 has to treat it as variable-length, since it consists of rows with the old length (where the new column doesn't yet exist), but needs to accept rows with the new column (and new length). [...] 368 86 25_Re: Bufferpool thresholds15_Toine Michielse18_vndobtm@US.IBM.COM30_Mon, 1 May 2000 16:11:56 +0100440_us-ascii Hello Pius....

Sounds like you are in deep trouble (performance wise). Here's some immediate thoughts.....

What you are experiencing is NOT a shortage of sort space but a shortage of buffers in the bufferpool you are using. The deferred write engines that are kicked of when you hit the deferred write thresholds are having a really hard time keeping up with the speed at which you are generating changed pages. [...] 455 100 47_Re: Can anyone recommend a good DB2 data editor14_Bistricer, Avi20_BistricerA@CONED.COM30_Mon, 1 May 2000 10:17:22 -0400282_- We are using ProEdit by CA. It is somewhat basic, but does the job. Does not have a good way to see multiple tables or a related set. Avi Bistricer, IR-DBA 212.460.6695

Yesterday is history, tomorrow is a mystery and today is a gift, that is why it is called present. [...] 556 59 25_Re: Bufferpool thresholds14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Mon, 1 May 2000 10:25:44 -0400301_ISO-8859-1 Pius, First, your pool is much too small if this is a production system. If you have memory available, increase the pool in 1000 buffer increments and monitor performance. A reasonable point may be 4000 - 5000 buffers. Some systems need a lot more, depending on the size of the sorts. [...] 616 27 28_DB2 Enforced RI Benchmarking19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Mon, 1 May 2000 09:41:59 -0500550_ISO-8859-1 Hi all and TIA, I'm currently searching for benchmark data on DB2 enforced RI vs Application enforced. I know that DB2 enforced RI has won the "battle", but I need to get my hands on any published results. I know this info is getting somewhat old, but do you know where I can find any results? 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 [...] 644 56 47_Re: Can anyone recommend a good DB2 data editor14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Mon, 1 May 2000 10:17:05 -0500419_ISO-8859-1 Jim:

BMC Software has a DB2 table editor in the development process. The table editor will integrated with CATALOG MANAGER. The table editor will be available in the Version 6.1.03 maintenance release due sometime in September 2000. Functions in the first release will be basic, but should have most of the features/function found in RC/Edit (the component of RC/Update that does table editing). [...] 701 89 38_Re: UNDER CICS RUN DB2 ABEND CODE APCH22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Mon, 1 May 2000 11:26:23 -0400341_- It might not be your compile, link edit. Ensure the COBOL II run time environment or the LE run time environment is initialized during CICS start up.

In the CICS JES2 job log the LE initialization message is something similar to

"DFHAP1203I applid Environment/370 is being initialized." where applid = CICS VTAM applid. [...] 791 25 55_UDB NT 6.1 and JDBC driver -- scrollable cursor problem13_Lynne Flatley17_LFlatley@NEFN.COM30_Mon, 1 May 2000 11:36:19 -0400366_us-ascii A customer of mine has hit a roadblock with IBM's JDBC driver for UDB NT 6.1 (fixpack 3). The driver does not support scrollable cursors which contain blobs. Does anyone know of a work-around for this, especially another JDBC/UDB driver from another vendor?!



"A foolish consistency is the hobgoblin of small minds" - Ralph Waldo Emerson [...] 817 65 24_Re: Visual Explain Error12_Rompot, Kurt26_kurt.rompot@INGRAMBOOK.COM30_Mon, 1 May 2000 10:51:57 -0500540_iso-8859-1 Make sure c:\sqllib\bin is in your PATH statement. Make sure your PATH statement isn't too long and inadvertently getting truncated.

-----Original Message----- From: Lisa Westcott-Dryer [mailto:Lisa_Westcott-Dryer@AMWAY.COM] Sent: Monday, May 01, 2000 8:42 AM To: DB2-L@RYCI.COM Subject: Visual Explain Error





The good news - I have installed Visual Explain for DB2 on my Windows 95 PC (using DB2 for Windows 95 to connect) it runs fine and I can point it to packages and do some analysis work. [...] 883 56 40_Re: Duplicate on table with unique index19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Mon, 1 May 2000 11:03:15 -0500602_ISO-8859-1 Sibimon, Don't waste time -- run the CHECK. HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 940 166 56_Re: SYSLGRNX and data integrity of dropped tablespaces??13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 1 May 2000 11:08:41 -0700381_iso-8859-1 Martin ,

:.. risks of re-initializing the SYSLGRNX tablespace and then FULL image coping all of the tablespaces in the subsystem???...'.

Well I can think of only one...if you do this operation 'now' you will not be able to recover any tablespace to any point in time before 'now'. If you are confident that you will not need that it is OK , I guess . [...] 1107 31 56_Re: SYSLGRNX and data integrity of dropped tablespaces??0_19_Tim.Lowe@STPAUL.COM30_Mon, 1 May 2000 13:37:12 -0500292_us-ascii Sanjeev, You said that : >> What if a tablespace is accidently dropped and u have an image copy , >> u can run DSN1COPY and get it back to the point in time with the help of >> logs.You do not need SYSCOPY records but you do need SYSLGRNX records for >> the logs to be applied. [...] 1139 71 17_Re: DB2 interface0_15_leon@CA.IBM.COM30_Mon, 1 May 2000 14:57:59 -0400631_us-ascii



Dmitriy, can you elaborate on what you mean by "good interface(connection) between UNIX RS6000 box and DB2 database"? IBM DB2 Connect has been around the longest (since 1992 when it was called DDCS) and it is as standard as it gets with most DB2 mainframe shops using it.

DB2 Connect will allow you to run applications on RS/6000 as well as SUN, HP, Numa-Q, Linux, Windows (of all flavours) and have these applications work with DB2 data on the mainframe and AS/400 machines. You can find more information including new version 7.1 features at http://www.ibm.com/software/data/db2/db2connect/. [...] 1211 61 56_Re: SYSLGRNX and data integrity of dropped tablespaces??20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 1 May 2000 17:20:17 -0400350_us-ascii That will not work. Once the tablespace is dropped, there is no question of using "RECOVER TABLESPACE .. LOGONLY". I can't think of reason why IBM does not remove SYSLGRNX records when the tablespace is dropped, While all the catalog entries are removed so why not SYSLGRNX ? Good question, lets see if some one from IBM replies to it. [...] 1273 177 24_DataJoiner Connect Error11_Ahl, Dana J19_AHLDANAJ@MATTEL.COM30_Mon, 1 May 2000 15:08:41 -0700593_iso-8859-1 I am having a problem with DataJoiner when attempting to start a replication. I receive this error on the mainframe: 11.27.23 STC12263 DSNL511I DB2S DSNLIENO TCP/IP CONVERSATION FAILED

TO LOCATION 156.20.27.156

IPADDR=156.20.27.156 PORT=4723

SOCKET=READ RETURN CODE=1121 REASON CODE=00000000 14.22.31 STC12263 DSNL511I DB2S DSNLIENO TCP/IP CONVERSATION FAILED

TO LOCATION 156.20.27.156

IPADDR=156.20.27.156 PORT=4795

SOCKET=READ RETURN CODE=1121 REASON CODE=00000000 14.31.00 STC12263 DSNL511I DB2S DSNLIENO TCP/IP CONVERSATION FAILED [...] 1451 110 33_Re: UDB Version 5 SAMPLE Database11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM30_Tue, 2 May 2000 08:06:53 +0800485_us-ascii If you had problem starting up DB2-DB2 services, my guess is that your UDB was not successfully installed. Closed all your window applications and try to install your UDB again.









Don Alden on 29/04/2000 04:56:44 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: UDB Version 5 SAMPLE Database [...] 1562 145 33_Re: UDB Version 5 SAMPLE Database9_Paul Yuen16_pyuen@GIO.COM.AU30_Tue, 2 May 2000 10:22:00 +1000567_us-ascii Hi all, I also have some problems creating the sample database, I have uninstall and reinstall UDB 6.1 on win98. When trying to execute the db2sampl, I always get an SQL1005N alias "sample" is already define in either the local or system database. However the sample database does not show up in the control centerl or anywhere in db2set variables. Trying to drop the sample database wouldn't work either as DB2 couldn't find it. Other than creating the sample database in another instance, is there any other ways to work around this problem? TIA Paul [...] 1708 40 27_Catalog stats manipulation.11_Linda Smith22_linda_home@HOTMAIL.COM30_Tue, 2 May 2000 01:11:36 -0500432_- DB2- OS/390 Version 4

Dear List,

I would like to Know, how i can find out what plans were bound, after catalog manipulation. This was done by the earlier DBA'S in my production system.

Recently, Two times it has happened for two different programs. Changes were made and package bind was done by the development staff. These two programs were clocking in production for several hours, on different days. [...] 1749 35 59_FW: UDB NT 6.1 and JDBC driver -- scrollable cursor problem14_Richard Bourke25_Richard.Bourke@MERANT.COM30_Tue, 2 May 2000 09:39:32 +0200486_- Lynne, SequeLink Java Edition 5.0 can handle non-updateable, scroll-insensitive cursors, with blobs. Unfortunately the db2 support is currently only for os/390(alongside oracle, sql server). The next maintenance release adds db2 nt and aix support, plus sybase, informix and so on. Richard, Merant

-----Original Message----- From: Lynne Flatley [mailto:LFlatley@NEFN.COM] Sent: Monday, May 01, 2000 5:36 PM Subject: UDB NT 6.1 and JDBC driver -- scrollable cursor problem [...] 1785 16 43_Antwort: Re: Removing Entries from SYSLGRNX17_Andreas Constanti24_andreas.constanti@AGI.CH30_Tue, 2 May 2000 10:27:03 +0100343_us-ascii Kirk

I used DSN1PRNT to look at the DSNDB01.SYSLGRNX tablespace.

Regards, Andy

================================================ 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. 1802 84 31_Re: Catalog stats manipulation.15_Toine Michielse18_vndobtm@US.IBM.COM30_Tue, 2 May 2000 10:44:45 +0100600_us-ascii Hello Linda,

There's no way to find out if packages/plans have a certain access path because the catalog statistics have been manipulated prior to the bind (I assume that's what you are after). Unless your DBA's have been extremely good in documenting all their actions.....

DB2 OS/390 V6 has functionality (Optimizer hints) that may help you in the sense that a lot of cases where you'd have to manipulate the stats can be avoided. Once you have a good access path in a plan_table, you can point the optimizer to that that access path on a subsequent bind. The optimizer [...] 1887 118 25_Re: Bufferpool thresholds9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Tue, 2 May 2000 10:02:09 +0100495_iso-8859-1 Hi,

Just to add to Toine's excellent suggestions, consider increasing VPSEQT to 98% or higher as the accesses through the DSNDB07 buffer pool are nearly all sequential.

Jim.

-----Original Message----- From: Toine Michielse [mailto:vndobtm@US.IBM.COM] Sent: 01 May 2000 16:12 To: DB2-L@RYCI.COM Subject: Re: Bufferpool thresholds



Hello Pius....

Sounds like you are in deep trouble (performance wise). Here's some immediate thoughts..... [...] 2006 83 17_Re: REORG PROBLEM20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Tue, 2 May 2000 10:50:11 +0100501_iso-8859-1 The answer to your question is 'yes'.

Incorrect reorg or load restarts, and the mismatched data and indexes which can result, is a topic mentioned from time to time on this list. I believe it happened at my site once (though I never proved it).

Your particular circumstance is noted in the Utilities Guide, where it warns: "...if you used the NOSYSREC option, terminate REORG using TERM UTILITY. Then recover the table space, using RECOVER, and run the REORG job again." [...] 2090 54 59_Re: UDB NT 6.1 and JDBC driver -- scrollable cursor problem13_Lynne Flatley17_LFlatley@NEFN.COM30_Tue, 2 May 2000 07:17:16 -0400665_us-ascii Yes! We're anxiously awaiting the next release of Merant's DB2 JDBC driver. Thanks for confirming that this driver will solve our needs.

> -----Original Message----- > From: Richard Bourke [SMTP:Richard.Bourke@MERANT.COM] > Sent: Tuesday, May 02, 2000 3:40 AM > To: DB2-L@RYCI.COM > Subject: FW: UDB NT 6.1 and JDBC driver -- scrollable cursor problem > > Lynne, > SequeLink Java Edition 5.0 can handle non-updateable, scroll-insensitive > cursors, with blobs. Unfortunately the db2 support is currently only for > os/390(alongside oracle, sql server). The next maintenance release adds > db2 > nt and aix support, plus sybase, informix and so [...] 2145 54 20_Re: Which Member ???23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 2 May 2000 12:27:32 +0100334_iso-8859-1 ...search is performed of the subsystem names file in parmlib, the first one found is connected to and then db2 moves onto the next found etc. Well documented in the Datasharing guide.

this is a gotcha when going to DS from non-DS .. if you do not IPL but go for a dynamic add of the subsystem names entries .. [...] 2200 13 33_Re: UDB Version 5 SAMPLE Database13_Noreen Prenty22_Noreen_Prenty@HESC.COM30_Tue, 2 May 2000 07:55:23 -0400347_us-ascii The sample database is not gone. Go to command center, enter the command catalog database sample

================================================ 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. 2214 10 32_Re: DB2 Enforced RI Benchmarking10_Bill Brown25_bill_brown@SPRINGMAIL.COM30_Tue, 2 May 2000 08:02:15 -0400569_- Although defining referential integrity will add cycles to DB2 processing, oh by the way application defined ri will also add cycles, I don't think the issue of which method is more efficient should really be the issue. I think the real issue should be data integrity. Using DB2 ri will ensure that your data stays pure. Of course you must consider the load utility that fails and is not properly cleaned up type problems, but you will not insert a child row without a parent and the delete trigger will do the job. You can't make these claims using application [...] 2225 29 33_Business validation before Update8_madhavan16_madhavan@INF.COM30_Tue, 2 May 2000 17:40:25 +0530254_- Hi,

In many of our programs we will be doing a business validation ( in few tables - let us say table b, table c ) before making an update in a table (let us say table A). These are online programs and the bind option is expected to be CS. [...] 2255 83 54_Help for S978 error in PLATINUM QUICKCOPY + more infos14_Massimo Scarpa16_mscarpa@CESVE.IT30_Tue, 2 May 2000 14:42:45 +0200460_us-ascii Hi Kurt & all db2-lers, thanks for the post in reply.

After a while I obtained the full JCL of PLATINUM QUICKcopy and error codes below:

10.58.39 JOB11740 IEF403I PUTUTIL - STARTED - TIME=10.58.39 11.06.47 JOB11740 *ICH409I 978-004 ABEND DURING EXTRACT PROCESSING 11.07.18 JOB11740 IEA794I SVC DUMP HAS CAPTURED: DUMPID=125 REQUESTED BY JOB (PUTUTIL ) DUMP TITLE=COMPON=DLF,COMPID=SC164,ISSUER=COFMEST2,ABEND=S0978, REASON=00000004 [...] 2339 48 33_Re: UDB Version 5 SAMPLE Database21_Karthikeyan Vinayagam30_vkarthikeyan@MD.IN.DSQSOFT.COM30_Tue, 2 May 2000 07:32:12 -0500612_- think u did check ur userid belongs to administrator group and the it follows naming rules?

/Karthik



On Thu, 27 Apr 2000 17:14:31 -0600, Don Alden wrote:

> Upon completion of installation of UDB 5 Personal Edition on my NT >workstation, I >bring-up the First Steps Panel and select "Create SAMPLE Database". I then >get a >Warning message telling me that selecting this option will take several >minutes. > > When I OK this option I don't receive any kind of message that it is >running etc. When I >try to launch the create again I get a message [...] 2388 136 58_Re: Help for S978 error in PLATINUM QUICKCOPY + more infos11_David Ayers24_david.ayers@HIGHMARK.COM30_Tue, 2 May 2000 09:03:53 -0400441_us-ascii Max, have you looked at the allocation errors in your jeslog. Some things that may cause this are dsn masks, dsn units... There should be a member in your parmlib that has this information in it for dynamic file allocaiton.

hth -dfa













Massimo Scarpa on 05/02/2000 08:42:45 AM

Please respond to DB2 Data Base Discussion List [...] 2525 32 62_Re: Changing High Level Qualifier for all Tables in a Database10_Robert Ord21_robertord@HOTMAIL.COM28_Tue, 2 May 2000 13:08:33 GMT696_- I have recently been through this process on DB2 OS/390 by doing the following;

Create new stogroup with new HLQ Image copy tablespace Alter tablespace to use new stogroup Delete cluster Recover tablespace Image copy tablespace

Cheers

Rob











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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2558 27 57_Re: MVS/OS390 Generating unique row identifiers - how to?10_Robert Ord21_robertord@HOTMAIL.COM28_Tue, 2 May 2000 13:26:46 GMT461_- This SQL returns an ascending sequence for each value of SHOP_KEY, you should be able to adapt it to generate the next sequence.

SELECT COUNT (*) as sequence, T2.SHOP_KEY FROM (SELECT SHOP_KEY FROM ADQD0.TBLOCN) AS T1 , ADQD0.TBLOCN T2 WHERE T1.SHOP_KEY <= T2.SHOP_KEY GROUP BY T2.SHOP_KEY ORDER BY 1 ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com [...] 2586 57 19_MOVING TABLE SPACES7_Gustavo26_lozano@BANDEIRANTES.COM.BR30_Tue, 2 May 2000 10:42:34 -0300265_iso-8859-1 Hi folks.

How can I move a table space from one physical file to another in UDB/NT (something similar to ALTER/REORG in DB2/OS390)? Thank you very much.

Gustavo Lozano (lozano@bandeirantes.com.br) - DBA Banco Bandeirantes Sao Paulo - Brazil 2644 121 20_Re: Which Member ???10_Tom Taylor17_ttaylor@CHUBB.COM30_Tue, 2 May 2000 09:57:47 -0400632_us-ascii



leslie

Which db2 is choosen (with 2 members on the same lpar) when connecting via tcpip? via DDF. Is there any balancing ?

Tom

















"Pendlebury-Bowe, Leslie" on 05/02/2000 07:27:32 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Thomas G Taylor/ChubbMail)





Subject: Re: Which Member ???













2766 56 20_Re: Which Member ???32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR30_Tue, 2 May 2000 16:54:17 +0300391_- With IP Connection Port number of the DB2 is given.



-----Original Message----- From: Tom Taylor [mailto:ttaylor@CHUBB.COM] Sent: Tuesday, May 02, 2000 4:58 PM To: DB2-L@RYCI.COM Subject: Re: Which Member ???







leslie

Which db2 is choosen (with 2 members on the same lpar) when connecting via tcpip? via DDF. Is there any balancing ? [...] 2823 60 37_Re: Business validation before Update10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Tue, 2 May 2000 13:07:39 GMT454_us-ascii Hi Madhavan, What about considering WITH RR in validation queries in table b and table c.WITH RR for those 2 queries will override the plan's CS .

I hope this helps

Regards

Sanjeev









madhavan @RYCI.COM> on 05/02/2000 06:40:25 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 2884 19 48_DB2 for OS/390 Version 6 Refresh announced today12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 2 May 2000 08:50:23 -0500398_- The announcement is out on IBMLINK, and I expect to get a pointer to the announcement on the DB2 for OS/390 web page later today. The title is

Refresh of IBM DB2 UDB for OS/390, Version 6 Delivers New Functions and Recommended Maintenance Level

Current Version 6 customers can apply service to match this level. Please be sure to read the info APAR II12343 and the PSP bucket. [...] 2904 102 20_Re: Which Member ???10_Tom Taylor17_ttaylor@CHUBB.COM30_Tue, 2 May 2000 10:12:00 -0400565_us-ascii IP only allows one server to connct to a given port. The db2's SHARE the same port number on the same lpar using the tcpip parm 'shareport"

Tom















"Cuneyt Goksu (Garanti Teknoloji)" on 05/02/2000 09:54:17 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Thomas G Taylor/ChubbMail)





Subject: Re: Which Member ??? [...] 3007 99 62_Re: Changing High Level Qualifier for all Tables in a Database0_19_Tim.Lowe@STPAUL.COM30_Tue, 2 May 2000 09:01:48 -0500756_us-ascii Rob, There is a much master method that I have used successfully on DB2 OS/390:

Create new stogroup with new high-level qualifier Stop tablespace and indexes Alter tablespace and indexes to use the new stogroup Use IDCAMS to rename the tablespaces and indexes (make sure to do both the cluster and data portions of each): ALTER OLDHLQ.DSNDBC.DBNAME.TSNAME.I0001.A001 - NEWNAME(NEWHLQ.DSNDBC.DBNAME.TSNAME.I0001.A001) ALTER OLDHLQ.DSNDBD.DBNAME.TSNAME.I0001.A001 - NEWNAME(NEWHLQ.DSNDBD.DBNAME.TSNAME.I0001.A001) ALTER OLDHLQ.DSNDBC.DBNAME.IXNAME.I0001.A001 - NEWNAME(NEWHLQ.DSNDBC.DBNAME.IXNAME.I0001.A001) ALTER OLDHLQ.DSNDBD.DBNAME.IXNAME.I0001.A001 - NEWNAME(NEWHLQ.DSNDBD.DBNAME.IXNAME.I0001.A001) Start the tablespace and indexes [...] 3107 47 50_Re: How do I find the IDENTITY value of a new row?13_John Hamilton22_John_Hamilton@MAIL.COM30_Tue, 2 May 2000 08:45:34 -0500337_- Has anyone found a solution to the problem of finding the value of the unique ID in a newly created row (whether it is generated by IDENTITY, GENERATE_UNIQUE or the MAX(id_fld)+1 trigger methods)?

Can a second trigger be added to signal the new value back to the application program as an SQL warning message? For example: [...] 3155 30 23_Re: MOVING TABLE SPACES0_22_rmadan@CYVEILLANCE.COM30_Tue, 2 May 2000 10:11:15 -0400511_iso-8859-1 redirected restore

-----Original Message----- From: Gustavo [mailto:lozano@BANDEIRANTES.COM.BR] Sent: Tuesday, May 02, 2000 9:43 AM To: DB2-L@RYCI.COM Subject: MOVING TABLE SPACES



Hi folks.

How can I move a table space from one physical file to another in UDB/NT (something similar to ALTER/REORG in DB2/OS390)? Thank you very much.

Gustavo Lozano ( lozano@bandeirantes.com.br ) - DBA Banco Bandeirantes Sao Paulo - Brazil [...] 3186 134 63_Re: Changing High Level Qualifier for all Tables in a Dat abase22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Tue, 2 May 2000 10:31:36 -0400378_- If the new ICF HLQ is not in the same ICF usercat as the old ICF HLQ and IDCAMS ALTER does not move the ICF catalog entries from the "old" usercat to the "new" usercat, then you can IDCAMS ALTER from the old ICF HLQ to the new ICF HLQ and then execute an IDCAMS REPRO MERGECAT LVL(new ICF HLQ) to move the ICF catalog entries from the "old" usercat to the "new" usercat. [...] 3321 93 50_Re: How do I find the IDENTITY value of a new row?0_19_Tim.Lowe@STPAUL.COM30_Tue, 2 May 2000 09:12:10 -0500539_us-ascii John, One idea that I have heard so far is to use a trigger to return the value through a global temporary table.

Thanks, Tim













John Hamilton on 05/02/2000 08:45:34 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Re: How do I find the IDENTITY value of a new row? [...] 3415 126 33_Re: UDB Version 5 SAMPLE Database12_Kline, Wayne27_wayne.kline@INGRAMMICRO.COM30_Tue, 2 May 2000 08:09:50 -0700464_iso-8859-1 Was this a real copy of UDB or an evaluation copy?

Wayne

-----Original Message----- From: Roland Chua [mailto:rolandchua@SINGAPOREEXCHANGE.COM] Sent: Monday, May 01, 2000 5:07 PM To: DB2-L@RYCI.COM Subject: Re: UDB Version 5 SAMPLE Database



If you had problem starting up DB2-DB2 services, my guess is that your UDB was not successfully installed. Closed all your window applications and try to install your UDB again. [...] 3542 27 42_Search a String In Case Insensitive Manner17_Rabindra Senapati21_rsenapati@HOTMAIL.COM28_Tue, 2 May 2000 08:13:10 PDT449_- Hello Everyone in the Group:

I need to know Whether DB2 has capability to store "Strings as Case Sensitive and Compare Strings as Case Insensitive manner". As I know first part is possible in DB2 but not sure about the second part "Search a String as Case Insensitive" specially on DB2-UDB(my current platform). Please let me know quickly if there is a way to do this in DB2-UDB.I found out SQL-Server does that not sure about ORACLE. [...] 3570 32 50_Re: How do I find the IDENTITY value of a new row?13_John Hamilton22_John_Hamilton@MAIL.COM30_Tue, 2 May 2000 10:06:03 -0500479_- Thanks for your reply Tim -

I guess this would work by updating a global file, eg keyed by filename and containing the field "Last used ID value", with the new ID value allocated by the trigger, then reading this row in the application program immediately after the INSERT statement.

However what is the guarrantee that in a multi-user environment it would not be possible for another session to already have updated the global file with a new last used ID? [...] 3603 91 50_Re: How do I find the IDENTITY value of a new row?0_19_Tim.Lowe@STPAUL.COM30_Tue, 2 May 2000 10:34:02 -0500358_us-ascii Hello John, A Global Temporary Table is "instanciated" for each unit of work, appearing as though each user had their own copy of a table. So, you would not need to worry about other users with a GTT, that is already being handled. You will just need to select the value from the GTT immediately after the insert, and before any commit occurs. [...] 3695 45 19_Hybrid Join Failure13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM30_Tue, 2 May 2000 11:33:24 -0400570_us-ascii Hi, all! We are experiencing a problem with a program getting a hybrid join failure. When we run the program with a certain number of criteria, it runs very quickly. When we add one more to the list of criteria, we we receive a hybrid join failure. We found the hybrid join failure by putting a trace on the program. The following is a print screen from the trace that we ran: BOOLE AND BABBAGE---------- USER DETAIL TRACE ------------- RX AVAILABLE SERV ==> UTRAC INPUT 09:04:42 INTVL=> 3 LOG=> N TGT==> DB2P PARM ==> X133981I,LEVEL=3 ROW 1 OF 6 SCROLL=> [...] 3741 71 32_Re: DB2 Enforced RI Benchmarking14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Wed, 3 May 2000 01:43:24 +1000634_us-ascii Rick,

I don't have a benchmark as I didn't need to do one. However a few comments that may be useful. DB2 R.I. checks, provided appropriate indexes available, will outperform application checks via additional SQLs by a very significant margin.

The reason is simple and comes back to one of the largest overheads in DB2 applications, particularly CICS Online. The overhead is simply the CPU cost of a SQL call (SELECT, OPEN, FETCH, etc.) is generally quoted as around 0.1 millisecs (3090-180J) before the SQL does anything at all (just API overheads) plus an additional 0.2 millisecs additional task switch [...] 3813 24 74_Differences in Reported Access paths Platinum Plan Analyzer and V ersion 68_Long, Ed15_Ed.Long@FMR.COM30_Tue, 2 May 2000 11:49:13 -0400483_- Hi all. As part of our validation process prior to converting to Version 6 we are comparing access paths between releases.

At present Platinum Plan Analyzer (2.1.5) future explain is reporting different results from what we see in the Plan table after a bind.

Specifically its seeing tablespace scans when the plan table says there aren't any. The 'current' explain, the report writer, accurately reports the contents of the plan table, ie, no tablespace scans. [...] 3838 78 23_Re: Hybrid Join Failure14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Wed, 3 May 2000 01:54:45 +1000541_us-ascii Is the additional criteria ANDed or ORed?

It would be a good idea to see the before and after SQL and Access Paths and no. of rows in each table.

What happens with your 'failure'? Does the SQL complete successfully and get the results?

While List Prefetch degrades to TS Scan under certain conditions, Hybrid does not. It proceeds in a stepwise fashion of getting some RIDs and then getting some data rows. There may be some exception for Multi-index processing on a Hybrid. We don't see these very often. [...] 3917 45 8_SQL Quiz19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 2 May 2000 11:13:57 -0500544_ISO-8859-1 Hi all and TIA, I wish to do something like the following. And please don't chew me out for the design of the tables -- I'm not the designer! Three tables contain columns I need. One table has two nullable columns, one of which is populated the other is null and enforced by application code. I want join to this table based on one or the other column not being null. The following SQL works somewhat but does a Cartesian product. How can I avoid the Cartesian product? I'm trying to avoid existence checks and do it all in one [...] 3963 80 12_Re: SQL Quiz20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 2 May 2000 12:27:46 -0400333_us-ascii I didn't get it from the SQL you have sent, you have B.CUST_UNIQUE_ID = A.CUST.. OR C.IDENT = A.SPP_IDENT.

This means TABLE A can have nulls in either CUST_UNIQUE_ID or SPP_IDENT and not TABLE B as you said in your mail. Is that correct ? What is the join condition between B and A when CUST_UNIQUE_ID is null ? [...] 4044 54 79_Re: Differences in Reported Access paths Platinum Plan Analyzer a nd V ersion 69_Mark Ruhe19_Mark.Ruhe@QUEST.COM30_Tue, 2 May 2000 09:29:43 -0700648_iso-8859-1 Ed, Just a thought but have you verified that the rows being populated with the "Future" explain are indeed not showing the tablespace scans. Perhaps the difference lies in inaccurate host variable substitutions when the "Future" explain is run. This can easily be verfied by specifying commit plan table rows on the "Future" explain and examining the plan table. Plan Analyzer first attempts to perform the "Future" explain with parameter markers and if errors are encountered (any error) then all host variables are substituted with literals. Since these literals are hard coded within PPA code numerous problems can occur. These [...] 4099 55 78_Re: Differences in Reported Access paths Platinum Plan Analyzer a nd Version 616_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Tue, 2 May 2000 09:32:13 -0700486_iso-8859-1 For clarification, "Current" reports what's in the plan table, i.e. the path that is currently being used; "Future" reports the access path based on current stats, i.e. the path that would be used if you rebound.

Are you saying that the two are just different, which is not unusual if things have changed since the last bind; or are you saying that Platinum and DB2 don't come up with the same access path, so "Future" doesn't match what you get when you rebind? [...] 4155 26 41_VERTICAL DEFERRED WRITE THRESHOLD SETTING16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Tue, 2 May 2000 09:35:27 -0700574_- Esteemed list,

Recently a performance analysis was done by IBM for all aspects of one of our larger systems at our company to determine where possible improvements might be made to reduce run times and . One of the recommendations they made was to set our vertical deferred write threshold to 0%. We currently use the default value of 10% for this, along with 50% for deferred write threshold. I'm somewhat confused as to why one would want to set the deferred write threshold to 10% as this would imply more write activity, and possibly have an impact on the [...] 4182 168 33_Re: UDB Version 5 SAMPLE Database9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Tue, 2 May 2000 10:34:56 -0600613_iso-8859-1 Howdy All,

I discovered my problem with the SAMPLE database installation. I have a book written by Susan Visser (IBM DB2 UDB developer) called Teach Yourself DB2 UDB in 21 days. This book came with a CD that contained various flavors of UDB V5.

When I installed the DB2 UDB for Windows NT Personal Edition the installation went just fine (no warning messages of any kind). After the installation completed a window pops-up and prompts you to install the SAMPLE database. When you select YES a message pops-up and says the SAMPLE database is already being installed. The real problem [...] 4351 196 24_Stored Procedure Problem12_Keister, Jim16_JKeister@QRS.COM30_Tue, 2 May 2000 09:36:04 -0700548_iso-8859-1 We are now testing DB2 V6 using stored procedures. This is our first venture into stored procedures and we encountered the following condition early on in our testing.



Any comments would be appreciated!!





I have spent several hours experimenting and have determined that even though I have an open cursor for temp table ACT_CT_TEMP001 when I return from stored procedure CTS2010, the EXEC SQL CALL that invokes CTS2010 terminates with SQLCODE = 0 --whereas a +466 is returned with a result set. [...] 4548 191 33_Re: UDB Version 5 SAMPLE Database11_Jakobs, Jef18_JJakobs@HARTIS.COM30_Tue, 2 May 2000 11:45:38 -0500416_iso-8859-1 Hi,

If I were you I'd just download DB2 UDB 6.1. The personal edition is free as far as I know. (That is unless you'd like to try the Beta Version of UDB Version-7)

Just my 2 cents,



Jef

-----Original Message----- From: Don Alden [mailto:Don.Alden@PKSIS.LEVEL3.COM] Sent: Tuesday, May 02, 2000 11:35 AM To: DB2-L@RYCI.COM Subject: Re: UDB Version 5 SAMPLE Database [...] 4740 85 78_Re: Differences in Reported Access paths Platinum Plan Analyzer a nd Version 68_Long, Ed15_Ed.Long@FMR.COM30_Tue, 2 May 2000 13:03:24 -0400530_- Hi Tony. Thanks for thinking about my problem. "Current", is, as you correctly noted, just a report writer for what is in the plan table. We confirmed that PPA/Current accurately reflects what DB2 put in the plan table after the bind explain(YES). Its "Future" that is creating the problem. Here are the steps we executed in order for one of the 100 or so packages. 1: Runstats affected table Index(ALl) col(All). 2: Rebind explain(Yes). 3: PPA Current explain 4: Rc/query*Rc/Edit Plan table listing 5: PPA Future Explain. [...] 4826 54 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Tue, 2 May 2000 22:44:46 +0530379_us-ascii In your current scenario can you explain what is the frequency of updates and is the data that is fetched reused ?? are we fetching new data all the time. All this will effect the deffered write threshold ( I mean all this will make us review the deffered write threshold.) . Can u explain your scenario. I can then give you reasons as to why it was reduced to 10% [...] 4881 37 23_Re: Hybrid Join Failure13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM30_Tue, 2 May 2000 13:28:36 -0400540_us-ascii Michael, Thx for the help. It seems that the programmer has found out that if she asks for recent data the program runs forever. If she asks for older data, then the program runs forever. DB2 is turning the list prefetch into a tablespace scan because the amount of recent data is much greater that the older data. I've asked that she break the cursor apart and try to run each part separately (it is a cursor with three unions) to see if she still has problems. If she does not, then she can process the data in this fashion [...] 4919 62 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 2 May 2000 13:34:44 -0400364_us-ascii Jeff,

If you are on non-data sharing environment then vertical deferred write threshold closer to 40 pages is ideal. But if you are not on V6 yet then it is not possible to specify threshold in term of pages. If you have a big buffer pool e.g. 40,000 buffers then even 1% would be 400 buffers. That is why the recommendation is to make it 0%. [...] 4982 29 21_Stored Procedure Info12_Keister, Jim16_JKeister@QRS.COM30_Tue, 2 May 2000 10:41:19 -0700580_iso-8859-1 Is it possible to access MQ Series directly from within a DB2 stored procedure? How about a file access(VSAM, flat, etc.)from within a DB2 stored procedure??



Thanks,

Jim Keister Sr. DB2 DBA

QRS, Corp. 1400 Marina Way South Richmond, CA. 94804

(510)231-6654 (510)621-3873 Fax jkeister@qrs.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. 5012 88 12_Re: SQL Quiz20_Reddy, Vishnu Prasad27_VishnuPrasad.Reddy@NIKE.COM30_Tue, 2 May 2000 10:48:49 -0700446_us-ascii Hi, A basic q! I have a table tbl1 with columns a,b,c as primary key.column a is CHAR,it can have values between 0 and 9 (including both)

Then,is

Select a,b,c from db1.tbl1 efficient or Select a,b,c from db1.tbl1 where a in ('0','1','2','3','4','5','6','7','8','9') Thanks, Vishnu







Vishnu Prasad P Reddy Ph# 503-5326439/6909161 Nike Sales Order Processing - Europe For Wipro Technologies [...] 5101 213 33_Re: UDB Version 5 SAMPLE Database9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Tue, 2 May 2000 11:44:17 -0600334_iso-8859-1 When I went out to the IBM website the only UDB downloads I could find was Version 7. I would prefer to go with UDB 6.1 since I am still a "UDB newbie" and the Version 7 seems to have a "major boatload" more functionality than 6.1. I would appreciate any information on how to get a UDB 6.1 Personal Edition download. [...] 5315 236 28_Re: Stored Procedure Problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Tue, 2 May 2000 13:52:03 -0400631_iso-8859-1 Jim:

You should receive +466 if RESULT SETS are returned. You may have already done it but since you did not mention, did you code 'RESULT SET n' in your CREATE PROCEDURE statement (where n=no. of result sets returned)?

Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Keister, Jim > Sent: Tuesday, May 02, 2000 12:36 PM > To: DB2-L@RYCI.COM > Subject: Stored Procedure Problem > > > We are now testing DB2 V6 using stored procedures. This is our > first venture into stored procedures > and we encountered the following condition [...] 5552 37 33_Re: UDB Version 5 SAMPLE Database11_Jakobs, Jef18_JJakobs@HARTIS.COM30_Tue, 2 May 2000 12:56:25 -0500410_iso-8859-1 The site is not very clear, but a free DB2 UDB 6.1 is there at:

http://www6.software.ibm.com/dl/db2pde/db2pde-p

It's sort of hidden by all the news on DB2 UDB 7 :o)

Good luck!



Jef -----Original Message----- From: Don Alden [mailto:Don.Alden@PKSIS.LEVEL3.COM] Sent: Tuesday, May 02, 2000 12:44 PM To: DB2-L@RYCI.COM Subject: Re: UDB Version 5 SAMPLE Database [...] 5590 272 28_Re: Stored Procedure Problem12_John Cameron26_JCameron@MSI-INSURANCE.COM30_Tue, 2 May 2000 13:02:13 -0500471_us-ascii We're still in Version 5, but I ran into this same problem. We needed to change the LINK to include DSNRLI rather than DSNALI. Hope this helps ?

John Cameron jcameron@msi-insurance.com





Manas Dasgupta cc: Sent by: DB2 Data Subject: Re: Stored Procedure Problem Base Discussion List



05/02/00 12:52 PM Please respond to DB2 Data Base Discussion List [...] 5863 98 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Toine Michielse18_vndobtm@US.IBM.COM30_Tue, 2 May 2000 20:02:11 +0100589_us-ascii Hello Jeff,

Not knowing all the ins and outs of your environment I can only make an educated guess as to why one would want to set the VDWQT to 0%.

In the past few years the average bufferpool has increased tremendously in size. Therefor the 10% default of changed pages per pageset or the 50% changed pages would accumulate to a large number. (consider 10% of 100.000 pages is 10.000 changed pages....). This results in a potentially large number of pages to be written during checkpoint. This in turn can cause spikes in I/O subsystem activity and reduce the [...] 5962 17 21_Test mail - pl ignore20_Reddy, Vishnu Prasad27_VishnuPrasad.Reddy@NIKE.COM30_Tue, 2 May 2000 11:17:11 -0700356_us-ascii Vishnu Prasad P Reddy Ph# 503-5326439/6909161 Nike Sales Order Processing - Europe For Wipro Technologies

================================================ 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. 5980 48 32_Re: DB2 Enforced RI Benchmarking19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 2 May 2000 13:17:31 -0500589_ISO-8859-1 Hi Bill, Agree completely. 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 strictly prohibited." [...] 6029 54 25_Re: Stored Procedure Info0_15_leon@CA.IBM.COM30_Tue, 2 May 2000 14:52:04 -0400405_us-ascii Yes it is possible to make MQ calls as well as access VSAM, CICS, IMS etc. There is a red book on DB2 for OS/390 Stored Procedures that has a chapter on this stuff.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



"Keister, Jim" on 05/02/2000 01:41:19 PM

Please respond to DB2 Data Base Discussion List [...] 6084 102 32_Re: DB2 Enforced RI Benchmarking19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 2 May 2000 14:13:11 -0500605_ISO-8859-1 Hi Michael, Listers, We plan to benchmark Triggers and Constraints. I'll report back to DB2-L with the results should it actually happen. What would be your ideal test of a Trigger vs application code?

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 [...] 6187 112 12_Re: SQL Quiz19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 2 May 2000 14:27:33 -0500390_ISO-8859-1 Hi Venkat, Listers, Venkat, you state my problem exactly. However, there was a flaw in the original SQL. Should a join between C and A be successful, there's no way to get info from B. We found a way to determine ahead of the select whether or not it was a CUST_UNIQUE_ID or a IDENT. So, problem solved. I appreciate your help and regret any wasted effort on anyone's part. [...] 6300 53 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Tue, 2 May 2000 16:10:17 -0400542_ISO-8859-1 Whenever the average pages/write is a single digit like 1.9, 3.7, 4.5, etc, etc, VDWQT should be lowered to zero. This will have little to no effect on the number of actual writes that takes place. It WILL avoid flooding the system with writes when DB2 takes a checkpoint, or even hitting DWQT if it's still at 50%. Keeping a trickle write going, and avoiding a huge burst of writes at checkpoint will maintain the consistency of application run time, and online response times. Regards, Joel Goldstein ++++++++++++++++++++++ [...] 6354 47 23_Nonmatching index scans0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 2 May 2000 15:26:59 -0500307_us-ascii A developer came to me with an EXPLAIN that has a non matching index scan on it (Access type = I, Matchcols = 0). I assume that the "OR" clause is causing the 'nonmatch'. Can anyone suggest a way to eliminate the nonmatch index scan?

DB2 V5, OS/390, TableA, Index = parent, child, Col3 [...] 6402 80 27_Re: Nonmatching index scans16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM30_Tue, 2 May 2000 13:45:06 -0700478_- Rohn,

This could be rewritten as the following to achieve your results:

SELECT A.TOTCOUNT + B.TOTCOUNT FROM (SELECT COUNT(*) as TOTCOUNT from TableA WHERE parent = :value1) AS A, (SELECT COUNT(*) as TOTCOUNT from TableA WHERE child = :value1) AS B

Granted, you will get an additional query blocks executed, but this at least this will eliminate the non-matching index scan you are currently getting from the use of the OR predicate. Hope this helps. [...] 6483 16 32_Re: DB2 Enforced RI Benchmarking12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 2 May 2000 15:35:26 -0500289_- We are working on a red book called a Version 6 Technical Update. It does have some performance information about triggers in it. It will probably be out in a month or two, and it's mentioned in today's V6 refresh announcement, but with the wrong number, which should be SG24-6108. [...] 6500 110 27_Re: Nonmatching index scans14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Tue, 2 May 2000 16:53:02 -0400285_iso-8859-1 I believe the second block (B) would still do a non-matching index scan. One solution would be to add an index on child - that should give you an access type of MU which should be way better than what you're getting now. (assuming default or not too bad cardinalities) [...] 6611 103 27_Re: Nonmatching index scans9_Mark Ruhe19_Mark.Ruhe@QUEST.COM30_Tue, 2 May 2000 13:56:47 -0700526_iso-8859-1 You would still get a non-matching index scan on the second inline view or worse a tablespace scan depending on catalog statistcs. Adding a second index on child might give you multiple index access which would eliminate the problem (assume DB2 would perform a multiple index access). Depending on the size of the index and the frequency it is executed a non-matching index only access may not be bad. You'll need to consider the overhead of adding a second index to updates, deletes, inserts, reorgs,...,etc. [...] 6715 46 36_Problems creating tablespaces on AIX10_Mike Wiles14_mwiles@UCG.COM30_Tue, 2 May 2000 17:37:32 -0400472_iso-8859-1 Hi list!

I'm running UDB 6.1 on AIX. I've been trying to create a tablespace with this command:

CREATE REGULAR TABLESPACE SRTL_REG_DATA PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/d15/crtlregdata01.001' 17920, FILE '/d16/crtlregdata01.002' 17920, FILE '/d17/crtlregdata01.003' 17920 ) EXTENTSIZE 16 OVERHEAD 12.17 PREFETCHSIZE 48 TRANSFERRATE 0.62 BUFFERPOOL PCSTRTLREG;

what I get is SQLSTATE=55009 (writing to a read-only file.) [...] 6762 28 52_Re: DB2 for OS/390 Version 6 Refresh announced today12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Tue, 2 May 2000 16:35:00 -0400469_- Barbera,



>>> DB2-L@RYCI.COM@inter2 05/02/00 08:50AM >>> The announcement is out on IBMLINK, and I expect to get a pointer to the announcement on the DB2 for OS/390 web page later today. The title is

Refresh of IBM DB2 UDB for OS/390, Version 6 Delivers New Functions and Recommended Maintenance Level

Current Version 6 customers can apply service to match this level. Please be sure to read the info APAR II12343 and the PSP bucket. [...] 6791 27 14_DB2 PE Connect17_Charles Jambrosic29_Charles_Jambrosic@CSIHOME.COM30_Tue, 2 May 2000 15:04:23 -0700541_us-ascii Does anyone have any experience with DB2 Connect PE V5 on NT?

We are attempting to add a database via the CCA but receive a SQL10007N ' Add Database operation failed' message. This only occurrs on NT as we have no problems on the other windows platforms as we have the connections communicating with the host successfully. We heard that apparently there is some known problem with V5 of DB2 Connect but there is a workaround to get you through. Does anyone know or have experienced this problem and have the workaround? [...] 6819 170 52_Re: DB2 for OS/390 Version 6 Refresh announced today10_Wang.James24_Wang.James@AAA-CALIF.COM30_Tue, 2 May 2000 15:27:51 -0700662_us-ascii Is there any difference between V6 Refresh and V7?

James Wang Sr. Systems Programmer Automobile Club of Southern California (714) 850-2851 Wang.James@aaa-calif.com

-----Original Message----- From: BOB JEANDRON [SMTP:BOB.JEANDRON@USDA.GOV] Sent: Tuesday, May 02, 2000 01:35 PM To: DB2-L@RYCI.COM Subject: Re: DB2 for OS/390 Version 6 Refresh announced today Sensitivity: Personal

Barbera,



>>> DB2-L@RYCI.COM@inter2 05/02/00 08:50AM >>> The announcement is out on IBMLINK, and I expect to get a pointer to the announcement on the DB2 for OS/390 web page later today. The title is [...] 6990 21 42_DB2 Connect for DB2 V 5 and NT (SQL10007N)11_Tim Kearney22_tkearney@ACCESS360.COM30_Tue, 2 May 2000 17:43:52 -0500371_- I have seen several posted questions and message about the SLQ10007N problem. I have an NT workstation and have administrative rights, but it must not be the right kind because I cannot create a database. Always get the SQL10007N.

Does anyone have any more details on what I have to do to my NT workstation to make DB2 Connect allow me to create databases? [...] 7012 117 27_Re: Nonmatching index scans25_Robinson, Peter [IBM GSA]33_Peter.J.Robinson@TEAM.TELSTRA.COM30_Wed, 3 May 2000 08:38:05 +1000342_- Hmmm, looks like this may not always provide the desired result. If the values of Parent and Child can ever be the same, a record could be counted twice, given a result one higher than the original query.

PJR

Peter Robinson Senior Performance DBA Measurement & Performance IBM-GSA Enterprise Services Brisbane, Australia [...] 7130 108 12_Re: SQL Quiz14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Wed, 3 May 2000 09:33:31 +1000590_us-ascii Vishnu,

As you coded the q,

where a in ('0','1','2','3','4','5','6','7','8','9')

does not help or add any value. However if you added an additional predicate: AND b = value the IN predicate on a may suddenly become very useful to allow 2 matching columns on the primary index.

From: Michael Hannan

At 10:48 AM 2/5/00 -0700, you wrote: >Hi, > A basic q! > I have a table tbl1 with columns a,b,c as primary key.column a is >CHAR,it can have values between 0 and 9 (including both) > > Then,is > > Select a,b,c from db1.tbl1 > efficient > or > [...] 7239 101 27_Re: Nonmatching index scans14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Wed, 3 May 2000 09:33:43 +1000352_us-ascii A slight ammendment to Jeff's query, which may not be necessary depending on business rules. Add the child index as others suggested.

SELECT A.TOTCOUNT + B.TOTCOUNT FROM (SELECT COUNT(*) as TOTCOUNT from TableA WHERE parent = :value1) AS A, (SELECT COUNT(*) as TOTCOUNT from TableA WHERE child = :value1 AND parent <> :value) AS B [...] 7341 21 18_Data Modeling Tool10_Ravi Verma17_rverma@ANNAMS.COM30_Tue, 2 May 2000 16:43:26 -0700482_iso-8859-1 Dear Friends,

I was ERWin to do data model for a DB2 UDB EEE database. But ERWin supports only 18 characters wide table names. Do you know of any other popular tool for DB2 database?

Best regards.

Ravi Verma.

================================================ 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. 7363 81 52_Re: DB2 for OS/390 Version 6 Refresh announced today14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 3 May 2000 10:14:35 +1000555_iso-8859-1 1) A link to the announcement letter is http://www.ibmlink.ibm.com/usalets &parms=H_200-104

2) There is a difference. If you read the V7 What's New http://www-4.ibm.com/software/data/db2/os390/ pdf/whatv7.pdf you will see that there are serveral things not available in V6 (eg UNIONs in subselects and views; FETCH FIRST ROW ONLY to mention two long awaited things). You'll also notice that you can upgrade directly from V5 to V7. [...] 7445 28 27_Migrating from DB2 V4 to V511_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM30_Wed, 3 May 2000 08:35:55 +0800388_us-ascii Hi,

We are in the process of migrating our development DB2 subsystem from V4 to V5. We had followed the steps listed in Installation Guide Chapter 2-5 (Installing, Migrating, and Updating System Parameters) and generated the JCLs required for the migration. We will then follow the steps listed in Chapter 2-7 (Migrating the DB2 Subsystem) and perform the migration. [...] 7474 52 22_Re: Data Modeling Tool16_Mandar Ghosalkar21_mghosalkar@TANDON.COM30_Tue, 2 May 2000 18:16:28 -0700556_us-ascii Try PowerDesigner 7 from Sybase

Mandar



|--------+-----------------------> | | Ravi Verma | | | | | | | | | 05/02/00 | | | 04:43 PM | | | Please | | | respond to | | | DB2 Data Base| | | Discussion | | | List | | | | |--------+-----------------------> >-----------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Mandar Ghosalkar/TIS) | | Subject: Data Modeling Tool | >-----------------------------------------------------------------------| [...] 7527 98 37_Re: Business validation before Update14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 3 May 2000 11:30:43 +1000360_iso-8859-1 Madhavan

- If the validation and update happen in one transaction, use WITH RR

- if they can happen in two transactions (eg there is a client interaction) , then you will need to 'mark' the rows used for validation and then, in the update transaction, verify that the mark is still valid. There are a number of ways of doing this [...] 7626 20 13_Prerequisites20_Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY30_Wed, 3 May 2000 09:53:45 -0700438_us-ascii Hi, Does anyone know of an easy way of determining the software/hardware prerequisites for running DB2 Version 5 and DB2 Version 6 on OS/390? Any reference sites ?

rgds, Jenny Kuan

================================================ 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. 7647 102 31_Re: Catalog stats manipulation.12_Marcus Green18_mgreen@AAPT.COM.AU30_Wed, 3 May 2000 13:37:38 +1000590_US-ASCII Linda,

IF the DBA's use the IBM Load utility, and IF you are prepared for an answer that might give a rough idea only, you could try something like the following:

SELECT * FROM SYSIBM.SYSPACKAGE WHERE NAME = 'your program';

The above will tell you when the program was last bound.....(bindtime).

Below will give you a list of the tables that have had a load replace done on them (using the IBM load utility) in load date and time order. Browse through the results and make a note of tables that were loaded twice, one after the other in relatively [...] 7750 68 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Tue, 2 May 2000 06:22:43 -0500384_us-ascii Pillay, Venkat (PCA) wrote: > > By making it 0% as threshold, your applications are not affected because the > write activity is always asynchronous.

Write activity is NOT asyncrhonous to thread TCBs that want to update in "in-flight" page (latch taken by DBM1 for duration of write). Just a small technicality. This shows up as "wait for asynch write I/O" time. [...] 7819 148 45_Questions about implementing website database12_Steve Parlin17_sparlin@4CTEK.COM30_Wed, 3 May 2000 00:00:26 -0400379_iso-8859-1 Hello! I own a small website which is growing in popularity. I am looking for some help to design a web based database, and was told that I may receive help from this mailing list.

What I would like to do is add 4 capabilities to the site.

1. I would like the users to be able to add and modify their information (via a secure registration process) [...] 7968 100 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Tue, 2 May 2000 06:42:39 -0500584_us-ascii Joel Goldstein wrote: > > Whenever the average pages/write is a single digit like 1.9, 3.7, 4.5, etc, > etc, > VDWQT should be lowered to zero. This will have little to no effect on the > number of > actual writes that takes place.

Joel, can you explain why lowering would be appropriate here? Personally, I want as many pages as possible for the write since it will increase the likelihood that succesive pages (2 thru nth) within that write channel program will get a write hit (DASD NVS cache). For STK/compression DASD, this can be critical. Channel programs [...] 8069 31 12_Re: SQL Quiz10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 05:14:57 GMT570_us-ascii Vishu, Any way if you are selecting all the primary keys columns from the table you are going to get Index only access.The only thing is that with Select a,b,c from db1.tbl1 , you will get Index access with matchcols=0 i.e accesstype = 'I' and Matchcols = 0 But if you code Select a,b,c from db1.tbl1 where a in ('0','1','2','3','4','5','6','7','8','9') You are going to get accesstype = 'N' and matchcols = 1 but this will not add any difference, as all the index pages are to be scanned in both the methods. However ,if you can add b= value(depending on [...] 8101 41 46_Re: Search a String In Case Insensitive Manner14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 3 May 2000 15:24:59 +1000518_iso-8859-1 Rabi

In both DB2 V6 and Oracle you can use the UPPER function to force case insensitivity. Or you could write a UDF to do the comparision, and do whatever comparison you like.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Rabindra Senapati [mailto:rsenapati@HOTMAIL.COM] Sent: Wednesday, May 03, 2000 1:13 AM To: DB2-L@RYCI.COM Subject: Search a String In Case Insensitive Manner [...] 8143 70 46_Re: Search a String In Case Insensitive Manner10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 05:27:41 GMT531_us-ascii Hi Rabi, If you are talking till DB2 UDB V5 for OS/390 then i am sure it is possible because you made me test it right now .However ,earlier i had used it in one of my CICS -COBOL application because of the requirement (good looking description fields, with the first letter upper case and next lower) but i didn't check the comparison.As of my test now ,String Comparison also works in DB2 for OS/390. You have not mentioned your current platform in the question because now DB2 UDB can be in many of the platforms. [...] 8214 328 32_Re: DB2 Enforced RI Benchmarking11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Wed, 3 May 2000 16:26:40 +1000582_iso-8859-1 Michael,

just to reinforce your comments - two things are probably worth expanding:

* The same indexes which are appropriate for DB2 RI are usually also needed for Application RI. If a program deletes a parent (without DB2 RI), it will normally access all children, either to delete them, or to verify that none exist (there are exceptions to this, of course). In either case the child tables will need indexes on the foreign keys - same as DB2 RI. On the other hand, if parents never get deleted, FK indexes are not needed for either type of RI. (And [...] 8543 60 11_unsub db2-l14_Cuneyt Toraman25_ctoraman@ARTEMIS.EFES.NET30_Wed, 3 May 2000 09:20:34 +0300625_- unsub db2-l Automatic digest processor yazmis:

> Date: Sat, 29 Apr 2000 01:00:02 -0500 > Reply-To: DB2 Data Base Discussion List > Sender: DB2 Data Base Discussion List > From: Automatic digest processor > Subject: DB2-L Digest - 28 Apr 2000 to 29 Apr 2000 (#2000-126) > To: Recipients of DB2-L digests > MIME-Version: 1.0 > > There are 54 messages totalling 3602 lines in this issue. > > Topics of the day: > > 1. S978 error in PLATINUM QUICKCOPY (2) > 2. REORG PROBLEM > 3. standby DB2 using real production data sets [...] 8604 66 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 3 May 2000 09:21:06 +0200325_us-ascii Hi Jim and Hi all.

I'm wondering (like you) why IBM experts recommend a VDWQT = 0 when an IBM DASD

expert said in during a convention that a cache size less than 64Mb is not good for a DB2

subsystem (mainly for sequential prefetch) in general, due to the fact it's very small and it cannot [...] 8671 26 34_OS/390 2.6 Toleration for DB2 4.1?11_Tim Blewitt22_timblewitt@HOTMAIL.COM28_Wed, 3 May 2000 08:26:41 GMT655_- Fellow DB2 Professionals,

Do you know of any issues for DB2 4.1 when upgrading the MVS 5.2.2 operating system to OS/390 2.6. Is there any toleration maintenance required?

Much obliged,

Tim

(tim.blewitt@bigfoot.com)





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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8698 97 56_Re: SYSLGRNX and data integrity of dropped tablespaces??17_William R. Miller32_william.miller@DRESDNER-BANK.COM30_Wed, 3 May 2000 03:08:32 -0500562_- Hello all,

DB2s strategy of keeping SYSLGRNX records is tied together with DB2 handling of the DBD. DB2 does not automatically pare the DBD when objects in the database are dropped. In talks with IBMers, I have been told that the reason for this is "Recovery Implications". One is the following: Suppose you have a tablespace with many tables in it. You regularly make image copies. At time B, you drop a table. Later on, you want to do a point-in-time recovery to A (before you dropped a table). The image copy from A has the dropped table in it. [...] 8796 17 36_Control Center SQLSTATE 2800 Problem4_huxl22_huxl@TRUCK.CSRU.EDU.CN30_Wed, 3 May 2000 16:46:57 +0800274_gb2312 background: Windows NT,DB2 V6.1 Problem: I have started the CC JDBC Applet server and DAS.but when starting control center,it repored some like this: "[IBM][JDBC Driver] CLI0618E USERID OR PASSWD INVALID.SQLSTATE=2800". I'm a new db2 user,please help me,thinks a lot. 8814 65 46_Re: Search a String In Case Insensitive Manner10_Robert Ord21_robertord@HOTMAIL.COM28_Wed, 3 May 2000 09:43:38 GMT351_- Beware that in Oracle 7 the use of the UPPER function in the WHERE clause prevents the use of an index. You can get around this in Oracle 8 by creating a function based index such as;

Create index ix1 on contacts (UPPER(surname));

Convaluted I know, but Oracle seem to add features to overcome the limitations of their optimiser. [...] 8880 103 56_Re: SYSLGRNX and data integrity of dropped tablespaces??10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 09:56:53 GMT532_us-ascii Hi all,

What i said was my misinterpration of LOGONLY and REPAIR option.I am very sorry fot that.However ,it made me try some new things and at last i was unsuccessful.I am sharing what i have tried and then at i have some more questions to ask Steps tried : 1) Create a tablespace 2) Create a Table 3) Insert 5 rows in the table 4) Take full Image copy 5) Insert 2 more records 6) Drop the tablespace 7) Create the tablespace and table with same name 8) run DSN1COPY ......It copied only 5 rows as i had image [...] 8984 168 26_Re: compression dictionary14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE30_Wed, 3 May 2000 12:32:29 +0100610_us-ascii Although this is not a question I thought I'd throw it out to you;

recently I tried to partition a compressed tablespace; I unloaded the data using n-unload-jobs, for each future partition one job; I then dropped and re-created the tablespace and ran the load-jobs, one for each partition; I thought as the partitions are empty anyway I might use 'load resume'; each of the load-jobs abended indicating that an attempt has been made to load a row into a partition whilst the row actually belongs to the next partition. Some investigation into the problem revealed the following: LOAD DATA [...] 9153 24 56_Re: SYSLGRNX and data integrity of dropped tablespaces??14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 3 May 2000 12:50:08 +0200500_us-ascii Hi Sanjeev,

did you run DSN1COPY with or without OBIDXLAT ? Sometimes OBIDs changes after a

DROP.

I ask this because some years ago I did the same thing with the same results but without

OBIDXLAT

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. 9178 59 56_Re: SYSLGRNX and data integrity of dropped tablespaces??10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 11:00:08 GMT318_us-ascii Hi Max, I did it with OBIDXLAT option . However ,luckily my OBID and PSID of the table and tablespace was same as the previous one ,it didn't change. Did you try the same thing ?. Means point in time recovery is not possible when a tablespace is dropped and we are using DSN1COPY methods to get it back. [...] 9238 64 36_Control Center Incomplete structures12_Mike O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV30_Wed, 3 May 2000 07:14:42 -0400514_US-ASCII I have set up two definitions for DB2 instances on the same LPAR. The first is called DVAB, and the structure (all component objects) appears to be complete. The second, called SAAB has an incpmp0lete structure (see below). I got no errors during definition (using CCA), testing the connection, or binding utilities in CCA, yet the structure is incomplete, and the SQL associated with retrieving it gets errors indicating that objects do not exist. The structures in Control Center window look like: [...] 9303 50 18_Re: DB2 PE Connect14_Philip Gunning20_pgunning@BOSCOVS.COM30_Wed, 3 May 2000 07:56:43 -0400506_iso-8859-1 Charles, To catalog a database you have to be a local or domain admin. There is a workaround in V6.1 by setting the db2catalog.noauth variable to yes. HTH Phil

Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS

----- Original Message ----- From: Charles Jambrosic Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 02, 2000 6:04 PM Subject: DB2 PE Connect [...] 9354 93 46_Re: Search a String In Case Insensitive Manner17_Rabindra Senapati21_rsenapati@HOTMAIL.COM28_Wed, 3 May 2000 04:55:34 PDT603_- Yes, i am sorry for that. My platfrm is DB2 V6.1 ON WINDOWS NT. Could you please let me know how you compaire and get the required string results in case insensative way.

Thanks A LOT.

Rabi.



>From: "Sanjeev .." >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Search a String In Case Insensitive Manner >Date: Wed, 3 May 2000 05:27:41 GMT > >Hi Rabi, > If you are talking till DB2 UDB V5 for OS/390 then i am sure it is >possible because you made me test it right now .However ,earlier i had used [...] 9448 115 46_Re: Search a String In Case Insensitive Manner13_Lynne Flatley17_LFlatley@NEFN.COM30_Wed, 3 May 2000 08:17:20 -0400600_us-ascii I believe there's a function called UCASE that makes a string uppercase.

> -----Original Message----- > From: Rabindra Senapati [SMTP:rsenapati@HOTMAIL.COM] > Sent: Wednesday, May 03, 2000 7:56 AM > To: DB2-L@RYCI.COM > Subject: Re: Search a String In Case Insensitive Manner > > Yes, i am sorry for that. My platfrm is DB2 V6.1 ON WINDOWS NT. Could you > please let me know how you compaire and get the required string results in > case insensative way. > > Thanks A LOT. > > Rabi. > > > >From: "Sanjeev .." > >Reply-To: DB2 Data Base Discussion List [...] 9564 196 46_Re: Search a String In Case Insensitive Manner10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 13:00:44 GMT627_iso-8859-1 Rabi, I searched through the Information Center in my PC for DB2 UDB V5.1 for Win NT and found these things :

>>-UCASE--(--expression--)------------------------------------->< The schema is SYSFUN. Returns a string in which all the characters have been converted to upper case characters. All characters in the expression are monocased. That is a-z are translated to A-Z, and characters with diacritical marks are translated to their upper case if they exist. For example, in code page 850, é maps to É, but ÿ is not mapped since code page 850 does not include &Ye.. The argument can be CHAR or VARCHAR. [...] 9761 144 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 3 May 2000 07:59:09 -0500656_ISO-8859-1 Hi all, I guess I see this issue in a somewhat different way. Given: the ultimate end-user is concerned with response time and its consistency, transaction-to-transaction. And that transaction "A" always runs 2.1 secs. Or that batch job "B" always runs 2 hrs. Consider an OS390, CICS, DB2 transaction. The tuning of OS390, CICS, DB2, and the I/O subsystem all come into play. They must all "play well together". Optimizing any one resource can penalize one or more of the others. Wouldn't it therefore follow that setting VDWQT should probably be some compromise between exploiting I/O subsystem capabilities and transaction-to-transaction [...] 9906 26 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 3 May 2000 09:10:07 -0400513_ISO-8859-1 The number of times you will see this kind of wait is miniscule, 99% of all write is asynch to the thread, and usually occurs after the thread has committes and is gone. Regards, Joel +++++++++++++++++++++++++++++++++++++

Message text written by DB2 Data Base Discussion List >Write activity is NOT asyncrhonous to thread TCBs that want to update in "in-flight" page (latch taken by DBM1 for duration of write). Just a small technicality. This shows up as "wait for asynch write I/O" time. [...] 9933 97 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 3 May 2000 09:25:21 -0400607_ISO-8859-1 Jim, You are getting too bogged down in detail. Write thresholds should not have anything to the hardware device. The number of pages/write is managed by an internal threshold of 150 CIs. So having a high write threshold has no significant impact on the number of pages/write.... Lowering the threshold keeps the trickle write going and avoids huge bursts of write activity when ckpt occurs. Zero uses an internal threshold of 40 pages to trigger the writes. Let's assume you will average 2 pages/write.... Trigger at 40 pages, trigger at 1000 pages... 20 I/Os vs 500 I/Os.... Regards, Joel [...] 10031 77 40_Re: Problems creating tablespaces on AIX8_Dan Tuck18_dan_tuck@YAHOO.COM30_Wed, 3 May 2000 06:17:11 -0700338_us-ascii You did not say if the command was issued via Command Line Process (CLP) or Command Center. I have also received this error when using Command Center. I was told, by the UNIX gurus, that Command Center did not have the authority to create the file. The Command Center ID was captured(?) and included in the DB2 admin group. [...] 10109 173 56_Re: SYSLGRNX and data integrity of dropped tablespaces??15_Stovall, Martin16_MStovall@USG.COM30_Wed, 3 May 2000 08:30:42 -0500367_iso-8859-1 The copy and the drop is at the TABLESPACE level - it has nothing to do with dropping a table in a multiple table tablespace. Once the TABLESPACE is dropped the SYSLGRNX records are useless in any recovery situation. The SYSLGRNX records should be cleaned up when a DROP TABLESPACE is done, not a DROP TABLE just like the rest of the catalog objects. [...] 10283 156 56_Re: SYSLGRNX and data integrity of dropped tablespaces??20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 3 May 2000 09:29:41 -0400354_iso-8859-1 Bill,

Thanks for the reply. But this still doesn't explain why partitioned tablespace (which can never have more than 1 table per space) should not be cleaned up. Also when table was dropped DB2 could have known that there was only one table in the tablespace, it would have been nicer if everything was cleaned up then and there. [...] 10440 25 27_Re: Nonmatching index scans10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 3 May 2000 13:37:51 GMT318_us-ascii Hi Rohn, If you have created the index on child then what is the accesspath of your second query i.e SELECT * FROM TableA WHERE (parent = :value1 AND child = :value2) OR (parent = :value2 AND child = :value1)

I am unable to visualize the new accesspath.Kindly send the same if you have done that. [...] 10466 116 23_Re: Hybrid Join Failure10_Bill Disch23_bill.disch@GENMILLS.COM30_Tue, 2 May 2000 15:45:15 -0500529_iso-8859-1 Scott,

What release of DB2 are you running? We had problems with hybrid join since it was first introduced (1995?). We set the ZPARM to disable Hybrid join. With each new release of DB2 the same problem occurred, but IBM was unable to reproduce the error on their system. Finally, at V5.1, IBM was able to fix the problem and we have had no more problems (that we know of !) with hybrid joins. The APAR for our problem was PQ27723. The fixes are UQ32759 (V4), UQ32760 (V5), and UQ32761 (V6). We are at V5. [...] 10583 220 56_Re: SYSLGRNX and data integrity of dropped tablespaces??0_19_Tim.Lowe@STPAUL.COM30_Wed, 3 May 2000 08:55:35 -0500371_us-ascii Since the syslogrange data is kept by tablespace, I do not understand why you are discussing a "drop table". I would have expected a discussion on why the syslgrnx data is not deleted when the "drop tablespace" is done. (And, since a "drop table" cannot be done on a partitioned tablespace, then the "drop tablespace" would have to be done instead anyway.) [...] 10804 62 31_Re: Migrating from DB2 V4 to V550_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM30_Wed, 3 May 2000 10:09:57 -0400402_- I have done this, only because we were in a hurry to migrate to V5 in an environment. You need to be careful in 3 places.

DB2.*.SAMPLIB(DSNTIJMV) & (DSNTIJMW) & (DSNZPARM)

THE MV & MW ARE MVS SPECIFICATIONS.

Reasons : This is where your DB2 start up procedures are developed and you may have different parameters definitions. In the IRLM PC=YES vs PC=NO, etc. region sizes. [...] 10867 17 56_Re: SYSLGRNX and data integrity of dropped tablespaces??17_Andreas Constanti24_andreas.constanti@AGI.CH30_Wed, 3 May 2000 16:17:10 +0100367_us-ascii Tim

The DSN1PRNT with the FORMAT option shows empty space as HOLE: as opposed to RECORD:

Regards, Andy

================================================ 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. 10885 88 27_Re: Nonmatching index scans0_19_Tim.Lowe@STPAUL.COM30_Wed, 3 May 2000 09:19:27 -0500570_us-ascii Sanjeev/Rohn, From what I have seen, if he had an index on the columns "parent" and "child", and a second index on "child", then both of the queries should perform fairly well. I would expect multi-index access for both of them, not scans of indexes or tablespaces . The following query is similar, and my explain shows multi-index access for it, matching on 2 columns of the index: SELECT DBNAME, TSNAME, CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE (CREATOR = ? AND NAME = ?) OR (CREATOR = ? AND NAME = ?) The following query is similar to the other query, [...] 10974 97 58_Re: Help for S978 error in PLATINUM QUICKCOPY + more infos18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE30_Wed, 3 May 2000 09:11:32 -0500643_- Hello Massimo,

As you have the ALLMSGS parameter you should get more information in the PTIIMSG file. But it does not seem to be allocated. So it might be worth rerunning with that file (as well as PTIMSG).

Madeleine.



On Tue, 2 May 2000 14:42:45 +0200, Massimo Scarpa wrote:

>Hi Kurt & all db2-lers, thanks for the post in reply. > >After a while I obtained the full JCL of PLATINUM QUICKcopy and error codes >below: > >10.58.39 JOB11740 IEF403I PUTUTIL - STARTED - TIME=10.58.39 > 11.06.47 JOB11740 *ICH409I 978-004 ABEND DURING EXTRACT PROCESSING > 11.07.18 JOB11740 IEA794I SVC [...] 11072 129 58_Re: Help for S978 error in PLATINUM QUICKCOPY + more infos0_19_Tim.Lowe@STPAUL.COM30_Wed, 3 May 2000 09:28:36 -0500642_us-ascii I know what the problem is! You are copying BMC tablespaces using Platinum!

Just kidding! (-:

Thanks, Tim













Massimo Scarpa on 05/02/2000 07:42:45 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

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





Subject: Help for S978 error in PLATINUM QUICKCOPY + more infos















Hi Kurt & all db2-lers, thanks for the post in reply. [...] 11202 18 58_Re: Help for S978 error in PLATINUM QUICKCOPY + more infos14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 3 May 2000 16:55:44 +0200381_us-ascii THANKS FOR HELP (grrrrrr.........) !!!! :-)))

Really, it's becaming a problem ......:-((((

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. 11221 79 27_Re: Nonmatching index scans14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Wed, 3 May 2000 11:04:00 -0400524_us-ascii The only way you will get rid of any non-matching index scan is to create an index on the child column. I also see you seem to be indicating the developers want to know if one "exists". I would say the use of count(*) is not the best way if the number is only being used to indicate rows being present to match you criteria. Doing a singleton select and checking for 0 or -811 is usually much faster and less expensive. Also if you typically have one (either parent or child) that most of the time will get an [...] 11301 299 37_Repost: DB2 Skills Program Initiative13_Morrill, John12_JohnM@VP.NET30_Wed, 3 May 2000 09:31:50 -0600741_- 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,

Please find below an important message and request from my colleague, Les Chapman, of our DB2 certification team. Les is initiating a pilot program with North American higher education institutions and is looking for industry partners that could provide intern placements, full time employment, or mentoring assignments for graduates of [...] 11601 88 40_Re: Control Center Incomplete structures19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Wed, 3 May 2000 08:37:39 -0700310_- Assuming this is DB2 for OS/390, then the DB2 instances need to be at least V5 put level 9906 to enable the Control Center administration. From your description, it appears that you have DVAB on that put level or later, and SAAB on an earlier version. Check with your systems people if you're not sure. [...] 11690 112 40_Re: Control Center Incomplete structures19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Wed, 3 May 2000 08:40:22 -0700588_- Amendment: The control center support functionality on OS/390 is an option even within current maintenance. It simply may not be installed.

Tim

> -----Original Message----- > From: Ohling, Tim R - CNF > Sent: Wednesday, May 03, 2000 8:38 AM > To: DB2-L@RYCI.COM > Subject: RE: Control Center Incomplete structures > > Assuming this is DB2 for OS/390, then the DB2 instances need to be at > least V5 put level 9906 to enable the Control Center administration. From > your description, it appears that you have DVAB on that put level or > later, and SAAB on an earlier [...] 11803 267 25_TDAN.com May Announcement13_Morrill, John12_JohnM@VP.NET30_Wed, 3 May 2000 09:54:08 -0600490_- Greetings DB2-L Subscribers!

Below is an announcement from The Data Administration Newsletter, a free Web site about data administration.

Cheers! J. Micheal Morrill DB2-L Chief List Owner





-----Original Message----- From: Robert S. Seiner (CIBER/TDAN) [mailto:rseiner@tdan.com] Sent: Tuesday, May 02, 2000 7:26 PM To: TDAN Reader List Subject: TDAN.com May Announcement



Re: The Data Administration Newsletter -- http://www.tdan.com [...] 12071 153 32_Re: DB2 Enforced RI Benchmarking9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Wed, 3 May 2000 10:13:15 -0600429_iso-8859-1 I believe the performance issues have been "well addressed" in these responses. The other main concern in using DB2 enforced RI is the Appication Programming side. I have personnaly witnessed major failures in applications that were written using "program enforced" RI. These application can be tested successfully and go into Production and the data RI errors might not appear until months after implementation. [...] 12225 13 14_DB2 V7 manuals15_Belfield, Al G.15_AGBELFI@TWA.COM30_Wed, 3 May 2000 11:12:15 -0500388_iso-8859-1 Has anyone managed to find the DB2 V7 manuals on the IBM website? Or do I have to download the whole Beta of DB2 V7 to get the manuals?

================================================ 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. 12239 76 55_Re: DB2-L Digest - 2 May 2000 to 3 May 2000 (#2000-130)10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Wed, 3 May 2000 09:19:39 -0700629_us-ascii >At 01:00 AM 5/3/00 -0500, you wrote: >To: DB2-L@RYCI.COM >cc: >Subject: Bufferpool thresholds



>I have a bufferpool that has 1000 buffers defined strictly for use of >DSNDB07. I have 6 DASD workfiles total about 2800 cyls for sort work to >support this bufferpool. Two of the workfiles have secondary extents that >can be expandable. We use TMONDB2 to monitor. During a 15 minutes >interval, each minute hit the Data Manager threshold (95%) and the >Immediate >Write threshold (97.5). The highest one is 890 for DM and 92 for IW, and >yet the DASD workfiles never expanded. Can someone explain this? [...] 12316 32 42_Updating/Inserting information into BLOB's15_Joel Zigelstein20_jzigel1@HALLMARK.COM30_Wed, 3 May 2000 11:25:24 -0500615_- Hi everyone!

I'm just curious if anyone out there has ever attempted to update information stored originally as a BLOB (i.e. a scanned image) and then use it somewhere else. Here is a potential scenario.

I have a series of scanned shipping manifests/labels particular to each of our different couriers. I would like to be able to "place" the contents of our shipment onto the manifests/labels, based on that particular shipment. We have logic to determine which is the best courier for the shipment based on the shipment destination so we cannot guarantee any one courier for any one shipment. [...] 12349 41 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 3 May 2000 13:14:57 -0400401_ISO-8859-1 Since you have no real control over the number of pages/write, and since the vdwqt setting has minimal impact on this number, you "should" normally want a trickle write instead of heavy bursts of writes. This has nothing really to do with exploiting hardware. There are two normal ckpt times: a. records written, based on the logload parm b. when the system rolls to a new log dataset [...] 12391 12 14_IDUG attendees0_22_Eric_Robida@BCBSME.COM30_Wed, 3 May 2000 14:00:56 -0400313_us-ascii Will anyone from Anthem be attending IDUG in Dallas 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. 12404 68 12_Index Design23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 3 May 2000 14:10:30 -0400579_- Greetings !!

We have following scenario and I am wondering what would be the best index design

DB2/OS390 ver 5 (soon to be ver 6)

1) TableA has columns COL1, COL2, COL3, COL4,.......COLn

TableA has several queries with following flavor of where clauses

1) Where COL1 IN (.,.,.,.,.,.,.) 2) Where COL2 IN (.,.,.,.,.,.,,) 3) Where COL3 IN (.,.,.,.,.,.,) 4) Where COl1 IN (...) and COL2 IN (.....) 5) Where COL1 IN (...) and COL3 IN(....) 6) Where COL2 IN (...) and COL3 IN (...) 7) where COL1 IN (...) and COL2 IN (....) and COL3 IN (....) [...] 12473 23 40_Re: Control Center Incomplete structures13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Wed, 3 May 2000 14:19:39 -0400385_iso-8859-1 Hi Mike,

I believe the key components for the Control Center support of OS/390 subsystems are provided by a set of stored procedures on the OS/390 subsystem. Have a look in SYSROUTINES for differences in SP names defined. I suspect that'll lead to your answer. Are we correct in assuming that DVAB and SAAB are different subsystems with different maint. levels? [...] 12497 163 44_Re: Converting Indexes from Type 1 to Type 214_Bistricer, Avi20_BistricerA@CONED.COM30_Wed, 3 May 2000 16:15:30 -0400410_windows-1252 I attempted to use the ReOrg method by converting the index first and ReOrg the tablespace with SORTDATA. However, our ReOrg jobs start with QUIESCE to the tablespace (in order to establish a recovery point, in case of a problem with the ReOrg). The QUIESCE failed because of RECP on the indexes and the job aborted. Why does the QUIESCE util care about RECP on an index ? Any way around it? [...] 12661 26 32_Dynamic SQL Caching and DSNT500I12_Rompot, Kurt26_kurt.rompot@INGRAMBOOK.COM30_Wed, 3 May 2000 15:21:31 -0500469_iso-8859-1 We just turned on dynamic SQL caching in of our DB2/OS390 V5 subsystems. The dynamic SQL cache quickly used up all of the remaining free pages in our EDM pool.

We received the following message in our DB2 log:

DSNT500I *DBT2 DSNGEDLC RESOURCE UNAVAILABLE REASON 00C90089 TYPE 00000600 NAME

What is the significance of this message? Specifically, what does it mean for name to be blank? None of our applications seem to have failed. [...] 12688 114 40_Re: Problems creating tablespaces on AIX10_Mike Wiles14_mwiles@UCG.COM30_Wed, 3 May 2000 17:34:30 -0400474_iso-8859-1 Thanks for the reply Dan!

I've run it a few different ways, under the command center on my NT workstation, from a CLP on my NT workstation, under the command center on my NT workstation through Exceed (an X-Windows client for NT), and from a CLP on my NT workstation through Exceed. Going through Exceed should be the same as actually running on the AIX machine (I think.) None of these methods worked until we changed the volume owner to "db2inst1". [...] 12803 31 34_Any value to adding another index?15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM30_Wed, 3 May 2000 15:14:07 -0700570_iso-8859-1 I've been looking at all the queries explained for a given table. This table has 336,500 rows, approx. 8000 pages. There are 24 queries in all. In eight of the queries the predicate is two columns, cust_acct_ky and bill_prep_dte, index access, match cols = 1. The first column, cust_acct_ky, is the first column in the primary key and its clustering index. My question is this...Would I gain anything to add an index on just those two columns? I have created the index in test and the queries will use it. Match cols goes to 2. None of the eight queries [...] 12835 44 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU30_Thu, 4 May 2000 08:58:19 +1000312_iso-8859-1 Well, you do need to be a little careful here. I followed this advice (0% threshold) a few years ago. One particular application's run time more than doubled and it was already taking several hours before that happened. Looking at the job afterwards all the increase was due to Async Write Wait. [...] 12880 38 31_Change password via DB2 Connect8_LAM, Leo17_l.lam@IOOF.COM.AU30_Thu, 4 May 2000 09:29:30 +1000437_iso-8859-1 We tried to change the mainframe password via DB2 Connect. It works fine when there's only one client making the change. When two or more clients tried to change the password at the same time, we got Dr. Watson msg on the gateway.

I've found that the apar item "JR13295 DB2PEM32.DLL IS NOT THREAD SAFE" is in the DB2 Connect Fix pack 9. And we are at fix pack 11, so I'd expect the problem should have been fixed. [...] 12919 325 32_Re: DB2 Enforced RI Benchmarking14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Thu, 4 May 2000 09:49:50 +1000534_us-ascii Thanks Ulrich,

I agree with all of that. Note that application R.I. needs an index but is not quite a fussy as DB2 R.I. on having all the F.K. columns and in correct sequence.

We sometimes choose to leave out F.K. index on extremely large tables due to impact on partitioned table Reorg. The penalty is parent delete not possible as you said. Many shops have Data Archiving projects that fail to take into account that parent delete requires the F.K. index and may make the archiving strategy impractical. [...] 13245 186 26_Re: compression dictionary14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Thu, 4 May 2000 09:52:42 +1000686_us-ascii Ruediger,

Yes, I also had to use replace on each partition to build a new compression dictionary.

Michael Hannan

>From: Ruediger Kurtz >Organization: HUK-COBURG >Subject: Re: compression dictionary >To: DB2-L@RYCI.COM > >Although this is not a question I thought I'd throw it out to you; > >recently I tried to partition a compressed tablespace; I unloaded the >data using n-unload-jobs, for each future partition one job; I then >dropped and re-created the tablespace and ran the load-jobs, one for >each partition; I thought as the partitions are empty anyway I might use >'load resume'; each of the load-jobs abended [...] 13432 51 34_Any value to adding another index?14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Thu, 4 May 2000 10:46:40 +1000659_us-ascii Sandi,

The fact that the column is nullable does not matter. Still can be indexed. F.K.s often nullable for optional relationships to a parent.

If predicates on bill_prep_dte do good filtering then an index on the 2 cols. is worth trying. If filtering is very weak, don't bother.

From: Michael Hannan

>From: "Mitchell, Sandi" >Subject: Any value to adding another index? >To: DB2-L@RYCI.COM > >I've been looking at all the queries explained for a given table. This table >has 336,500 rows, approx. 8000 pages. There are 24 queries in all. In eight >of the queries the predicate is two [...] 13484 95 16_Re: Index Design14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Thu, 4 May 2000 10:46:57 +1000401_us-ascii Sanjay,

I believe IN prohibits use of List Prefetch and Multi-index processing in DB2 V5.

Consider indexes: a. COL1, COL2, COL3 b COL2, COL3, COL1 c COL3, COL1, COL2

Subsequent columns for index screening purposes even if not matching. See if any IN predicates can be changed to '=' or range predicates to allow an extra matching column. Unusual to see so much INs. [...] 13580 40 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Wed, 3 May 2000 04:56:11 -0500541_us-ascii Joel Goldstein wrote: > > The number of times you will see this kind of wait is miniscule, > 99% of all write is asynch to the thread, and usually occurs > after the thread has committes and is gone. > Regards, > Joel

Agreed. This mostly happens for poorly performing I/O subsystems or ones that don't have enough NVS for true DASD Fast Write. I wanted to also point out that in our particular environment, it is the next-arriving tran that wants to update an in-flight page and gets caught waiting on asynch write I/O. [...] 13621 42 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Wed, 3 May 2000 04:59:56 -0500396_us-ascii Joel Goldstein wrote: > > Jim, > You are getting too bogged down in detail. Write thresholds should not > have > anything to the hardware device.

If I can delay almost all my writes until checkpoint AND my DASD I/O subsystem can take the burst, I end up saving a fair amount of CPU time since each physical start I/O (channel program) will have more updated pages within it. [...] 13664 25 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 3 May 2000 22:30:58 -0400433_ISO-8859-1 Jim, You have totally missed the point - which is that your writes will NOT have any more pages per I/O.

Joel



Message text written by DB2 Data Base Discussion List >If I can delay almost all my writes until checkpoint AND my DASD I/O subsystem can take the burst, I end up saving a fair amount of CPU time since each physical start I/O (channel program) will have more updated pages within it.< [...] 13690 34 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Wed, 3 May 2000 05:07:44 -0500286_us-ascii Joel Goldstein wrote: > > Jim, > You have totally missed the point - which is that your writes will NOT have > any more pages per I/O.

Please explain. If my deferred write threshholds are high, won't there be more updated pages per DB2 checkpoint than if I had 0%? [...] 13725 78 38_Re: Any value to adding another index?10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Thu, 4 May 2000 04:02:45 GMT587_us-ascii Hi Sandi, As there are 336,500 rows in the table and cardinality of cust_acct_ky is 187,038 then if we think from the default distribution of the data every cust_acct_ky is having 336,500 rows/187,038 --> approx. 2 rows(rounded).If this is the case actually (you know your data) then try avoiding the index because every index search will at the max go for two data pages, otherwise if the distribution is very non-uniform or will be non-uniform it is worth creating another index on these two columns. You are at a good position because as i feel the cluster ratio of the [...] 13804 45 40_Queries regarding the UDB database setup0_18_dsi.listsrv@DB.COM30_Thu, 4 May 2000 10:15:43 +0530475_us-ascii Hi All, We are re-engineering our Credit Apllication software with true 3-tier Architecture as follows:





- Front-end screens to be developed in Java using Swing classes. - Application server written in C++ (with some 3rd party libraries) which in turn is wrapped around with RMI interface provided by Java. - The Decentral Database server has been decided upon to be UDB 6.1 Fixpack 2. - The Host/Central Database server would be MVS DB2. [...] 13850 42 18_Re: DB2 V7 manuals14_Richard Yevich21_ryevich@BELLSOUTH.NET30_Thu, 4 May 2000 00:01:20 -0400674_iso-8859-1 The V7 manuals (DB2 for UNIX/NT etc)., will be available when the product becomes available (GA). Until then, they are part of the beta download.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@RYCi.com DB2 Performance Journal, Quarterly -- www.YLAssoc.com www.DB2-Portal.com





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Belfield, Al G. > Sent: Wednesday, May 03, 2000 12:12 PM > To: DB2-L@RYCI.COM > Subject: DB2 V7 manuals > > > Has anyone managed to find the DB2 V7 manuals on the IBM website? > Or do I have to download the whole Beta of DB2 [...] 13893 230 44_Re: Converting Indexes from Type 1 to Type 20_18_mebert@AMADEUS.NET30_Thu, 4 May 2000 09:20:19 +0100428_us-ascii Sure, get rid of the QUIESCE. It is completely unnecessary anyway. If the REORG fails, and you cannot restart it, just do a RECOVER TABLESPACE (to current) which will restore the situation just before the REORG.

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







From: "Bistricer, Avi" on 03/05/2000 20:15 GMT [...] 14124 46 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Thu, 4 May 2000 07:22:36 GMT735_us-ascii Hi all, What i have found from the Joel's presentation of Bufferpool performance metrics and tuning and sizing are : Regarding DWQT and VDQT, _________________________________________________________________________________________________ ** Reaching either of these thresholds will trigger deferred writes of those pages, and will also increment the respective counter. Most installations should lower both of these thresholds, based on a calculation of the average number of pages/write. So far, most observed installations show a page/write ratio between two and five. This means that DB2 must schedule multiple physical writes each time these thresholds are reached. DB2 can (potentially) write up to 128 pages (for [...] 14171 163 38_Re: Any value to adding another index?0_18_mebert@AMADEUS.NET30_Thu, 4 May 2000 09:33:54 +0100520_us-ascii Hello Sandi,

sorry if I digress but why do you bother? I would think that no query on this table should take longer than maybe 2 mins CPU, that's a total of 4 hrs per week for all jobs. If it's much more, then I'd start looking for a problem (I had one case last year where one query doing a complicated 3-table join - table sizes around 200.000, 100.000, 500 rows - was taking 14 hrs CPU because of a length mismatch in a host variable; after adding a single COBOL MOVE, this went down to 6 mins). [...] 14335 25 54_Help for S978 error in PLATINUM QUICKCOPY + more infos14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 4 May 2000 10:43:48 +0200581_us-ascii Many many thanks to all people for help !!!!

Unfortunately the problem still remain so don't hesitate to post your suggestions & wises

if you met the same problem using PLATINUM quickcopy or if you meet some Platinum

technical expert....

Cheers & Regards

Max Scarpa (unhappy) Data & System Admin

================================================ 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. 14361 27 64_How many extents DB2 can use to satisfy a given Primary Quantity18_Padmanabham, Paddy27_PPadmanabham@IDAHOPOWER.COM30_Wed, 3 May 2000 15:12:36 -0600482_iso-8859-1 Hi,

This may be a 'silly' question. But I remember to have read at some place, that DB2 can try to statisfy a primary quantity by taking a maximum of 5 extents. Is that Correct? To ask in general, if db2 cannot satisfy a primary quantity (during tablespace creation etc.) in one extent, what happens? Will db2 use multiple extents to satisfy the specified primary quantitiy? Or will db2 fail the command saying that this primary quantity cannot be allocated? [...] 14389 86 36_Re: Dynamic SQL Caching and DSNT500I15_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK30_Thu, 4 May 2000 11:40:01 +0100372_us-ascii Kurt,

EDM Pool failures are always of that format (at least those that I have seen), I don't think there is any significance as to why the name is blank (anyone care to enlighten me?).

A type 600 resource is listed as 'EDM Pool Space'.

You should still have seen some sort of error message (non-zero SQL code) in the application though. [...] 14476 52 40_Re: Control Center Incomplete structures19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 4 May 2000 07:25:13 -0500620_ISO-8859-1 David, Mike, Look for SPs DSNACCMG and DSNACCAV on DB2 UDB/OS390. These support Control Center. If you have Visual Explain you'll need DSNWZP.

HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, [...] 14529 71 36_Re: Dynamic SQL Caching and DSNT500I14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US30_Thu, 4 May 2000 08:31:59 -0400368_us-ascii Good Morning Kurt,

The name being blank is normal - Its the EDM pool ! An application most lokly did fail, just no one was notified (or it hasn't trickled down to you yet) Check the *MSTR address space or the SYSLOG for messages (904 I believe) - It looks as though you will have to be increasing the EDM pool size - or reconsider the cashing :-) [...] 14601 28 36_Re: Dynamic SQL Caching and DSNT500I11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Thu, 4 May 2000 07:31:43 -0500389_- Reason code 00C90089 means someone is suffering, they just haven't squeaked loudly enough (but they usually will, eventually).

00C90089 says EDM pool does not have enough storage to satisfy retrieval of a DBD, SKCT or SKPT. Since TYPE 600 refers to the EDMPOOL (V5 Msgs and Codes Resource types, pg X-7), then there is no additional benefit to add resource NAME in DSNT500I. [...] 14630 56 44_Re: Queries regarding the UDB database setup19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 4 May 2000 07:46:49 -0500581_ISO-8859-1 Srini, all info pertains to DB2 UDB/OS390. See below.

>-----Original Message----- >From: dsi.listsrv@DB.COM [mailto:dsi.listsrv@DB.COM] >Sent: Wednesday, May 03, 2000 11:46 PM >To: DB2-L@RYCI.COM >Subject: Queries regarding the UDB database setup

>2) Is there any advantage/disadvantage if a seperateTable space is defined for each >table in the database?

Some single-table tablespace advantages: LOAD is less complex. Maintenance utilities operate at the tablespace or tablespace partition level. More granularity provides higher concurrency. [...] 14687 108 44_Re: Queries regarding the UDB database setup10_Leo Pedron19_lpedron@BOSCOVS.COM30_Thu, 4 May 2000 08:54:33 -0400589_iso-8859-1 Hi,

There are several advantages for placing tables in separate tablespaces. By using DMS you can place you indexes in one tablespace, data in another, and LOB's in another. The advantage to doing this is multiple access paths to the data, as long as the containers are on separate devices. You also have more control over which tables reside in which bufferpools by placing each table in it's own tablespace. This is also true for SMS, but with DMS you can get your indexes and data in separate buffers. The disadvantage is more maintenance and you have to be more [...] 14796 27 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 4 May 2000 08:50:37 -0400419_ISO-8859-1 Yes, but that is exactly what you DON'T want..... because the avg pages/write will not really change..... because you will flood the system with physical write I/Os.

Regards, Joel







Message text written by DB2 Data Base Discussion List >Please explain. If my deferred write threshholds are high, won't there be more updated pages per DB2 checkpoint than if I had 0%? [...] 14824 116 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 4 May 2000 09:00:37 -0400406_ISO-8859-1 First, DB2 can write either 32 or 64 pages (64 for utilities) per write. It can schedule up to 128 pages for one write engine. When the updated pages are far apart (which you have no control over) you just don't get a high number of pages per write because of the 150 RBA threshold. Raising or lowering the thresholds has little to no effect on the number of pages/write (in most systems). [...] 14941 60 69_Re: How many extents DB2 can use to satisfy a given Primary Quant ity19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 4 May 2000 08:01:38 -0500340_ISO-8859-1 Paddy, DB2 UDB/OS390 V6. The PRIQTY must be satisfied within 5 extents or the allocation attempt fails. Also note that if VCAT.DBNAME.DSNDBD.I0001.A001 reaches its maximum size, DB2 looks for another PRIQTY when creating A002. Where the space is found depends on if you're using SMS, STOGROUP, or user-defined DB2 datasets. [...] 15002 17 25_DSNDB07 and Shark - pav's10_Tom Taylor17_ttaylor@CHUBB.COM30_Thu, 4 May 2000 09:54:41 -0400366_us-ascii HI all

Any one have any concerns about placing DSNDB07 data sets on a Shark device that has PAV's

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. 15020 86 69_Re: How many extents DB2 can use to satisfy a given Primary Quant ity12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 4 May 2000 06:49:30 PDT616_- A quick note: When using USER DEFINED VSAM files, DB2 will NOT allocate the A002 file. This must be done by the user PRIOR to DB2 needing it. You would get a -904 if needing the file and it is not pre-allocated.

Craig Patton Independent DBA



>From: "DAVIS, RICK (SBCSI)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: How many extents DB2 can use to satisfy a given Primary Quant > ity >Date: Thu, 4 May 2000 08:01:38 -0500 > >Paddy, > DB2 UDB/OS390 V6. The PRIQTY must be satisfied within 5 extents or >the allocation [...] 15107 66 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 4 May 2000 08:37:36 -0500619_ISO-8859-1 Hi Joel, This "thread" is really helping me understand backwrites. Thanks again for helping on this issue. Can you please handle a couple more questions? Isn't pages/write related to pages-updated/dataset? If I understand it correctly, the backwrites are done by dataset and up to 128 pages per I/O. And, the backwrites are scheduled by oldest pages first and repeated until the available pages in the pool is 20% lower than the particular threshold that was reached? Does this mean that while DB2 is waiting for the success or failure of each I/O, that threads with updates for those backwritten pages [...] 15174 174 27_DANGER! This is a NEW Virus11_Ahl, Dana J19_AHLDANAJ@MATTEL.COM30_Thu, 4 May 2000 06:55:08 -0700501_- Got us this morning. This is not a drill. Delete them before opening. Subject of e-mail: ILOVEYOU VBS.LoveLetter.A This is an email worm, mIRC worm, and file infector. Also known as: Category: Worm Infection length: 10307 Virus definitions: Pending

Threat assessment: Damage: High Distribution: High Wildness: High





Wild Number of infections: More than 1000 Number of sites: More than 10 Geographic distribution: High Threat containment: Moderate Removal: Moderate [...] 15349 100 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 4 May 2000 08:57:48 -0500381_ISO-8859-1 Hi Joel, Does your following statement mean the RBAs' of the CIs? "DB2 can (potentially) write up to 128 pages (for one object) with a single I/o; however, it will only write Cis within a range of 150 RBAs with a single I/o to reduce the duration of write events." Would this indicate that good locality of reference could drive pages/write up? Regards, Rick Davis [...] 15450 199 51_Repost: IDUG North America 2000...It's not too late13_Morrill, John12_JohnM@VP.NET30_Thu, 4 May 2000 08:31:04 -0600622_- 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,

IDUG North America is right around the corner. There?s still time to register in advance. Advance registrations will be accepted until May 5. After that date, you can still register on-site in Dallas beginning May 14. [...] 15650 591 61_Repost: DB2 Sets BI Performance Record in TPC-H 1TB Benchmark13_Morrill, John12_JohnM@VP.NET30_Thu, 4 May 2000 08:31:03 -0600730_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,

IBM announced last week that its next generation RS/6000 SP and DB2 Universal Database Version 7 have set new records for both performance and price/performance in the industry standard TPC-H benchmark. Below is a bulletin summarizing the results, as well as URLs for Web sites with addition information and details. [...] 16242 536 116_Repost: Refresh of IBM DB2 UDB for OS/390: Version 6 Delivers New Functions and RML (Recommended Maintenance Level )13_Morrill, John12_JohnM@VP.NET30_Thu, 4 May 2000 08:31:04 -0600628_iso-8859-1 Greetings!

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

Cheers!

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



Dear DB2 Interested Parties,

The subject announcement of the new DB2 Universal Database for OS/390 Version 6 Recommended Maintenance Level (RML) and (significant) new function can be found at: http://www.ibmlink.ibm.com/usalets&parms=H_200-104 [...] 16779 28 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 4 May 2000 11:33:06 -0400596_ISO-8859-1 Rick, Exactly. If your updated pages (the rba of the CI) are close together, then the number of pages/write will be better/higher. Keep in mind that tgis is not something you can control.... it's just how the applications reference and update the data pages.

Regards, Joel +++++++++++++++++++++++++++++++++

Message text written by DB2 Data Base Discussion List >.....however, it will only write Cis within a range of 150 RBAs with a single I/o to reduce the duration of write events." Would this indicate that good locality of reference could drive pages/write up?< [...] 16808 51 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 4 May 2000 11:56:28 -0400628_ISO-8859-1 Rick, See notes in-line... by +++++ Regards, Joel



Message text written by DB2 Data Base Discussion List > Isn't pages/write related to pages-updated/dataset? If I understand it correctly, the backwrites are done by dataset. +++++ correc,t and can be triggered by either vdwqt or dwqt

And, the backwrites are scheduled by oldest pages first and repeated until the available pages in the pool is 20% lower than the particular threshold that was reached? +++++ Not totally sure if it goes by oldest for vdwqt, but I think so. Don't know if the 20% applies to vdwqt. For other thresholds, yes. [...] 16860 34 54_PC/IXF file format and version #, DB2 UDB V6 on Win NT0_19_Sven.Heidorn@CSN.SE30_Thu, 4 May 2000 18:00:22 +0200498_ISO-8859-1 Hi all,

We often use QMF for windows to pull data from our OS/390 DB2 V5 to populate the development database, DB2 UDB PE V6 on Win NT. We run a query in QMF and export the result to a file in IXF format which we then import into DB2 UDB. This worked fine with DB2 CS but with DB2 UDB V6 we get an error during import saying that the H record of the IXF file has the wrong version #. In the V6 data movement utilities guide and reference it says that the version # is set to [...] 16895 41 38_Re: Any value to adding another index?15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM30_Thu, 4 May 2000 09:36:50 -0700620_iso-8859-1 My thanks to all who replied. I do appreciate the comments and insight.

-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: Thursday, May 04, 2000 1:34 AM To: DB2-L@RYCI.COM Subject: Re: Any value to adding another index?



Hello Sandi,

sorry if I digress but why do you bother? I would think that no query on this table should take longer than maybe 2 mins CPU, that's a total of 4 hrs per week for all jobs. If it's much more, then I'd start looking for a problem (I had one case last year where one query doing a complicated 3-table join - [...] 16937 13 51_non-matching idex scan with no additonal predicates15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM30_Thu, 4 May 2000 10:45:18 -0700394_iso-8859-1 What happens during a non-matching index scan when there are no additional predicates to apply? Does it effectively become a tablespace scan?

================================================ 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. 16951 12 46_Re: DB2 Connect for DB2 V 5 and NT (SQL10007N)13_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Thu, 4 May 2000 12:35:57 -0500457_- What DB2 authorizations do you have? Are you using the userid you set up when DB2 UDB was installed (DB2ADMIN)? Or are you using a different userid? If a different one, check to see what DB2 authorizations it has.

================================================ 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. 16964 12 18_Re: DB2 PE Connect13_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Thu, 4 May 2000 12:40:20 -0500416_- What we found with NT V5 DB2 Connect PE is that if you can get someone with ADMIN rights to set up your first remote Database connection, anyone can set up additional ones.

================================================ 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. 16977 20 33_Re: UDB Version 5 SAMPLE Database13_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Thu, 4 May 2000 12:49:46 -0500526_- One of the things I found when creating a new Database on AIX, is that creating the database did not make it show up in Control Center. You have to catalog it as well.

When you do a List Database Directory, does it show up?

Try Catalog Database Sample to see if that works. If not, try doing a Drop Database Sample from the command window and see if that works, then recreate it and catalog it. One other thing I had to play with is that when I finally got the database cataloged, it kept showing up as a [...] 16998 106 63_Re: optimizer use of runstats multi-column frequency statistics12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Thu, 4 May 2000 13:14:12 -0500597_us-ascii Tim - thanks for the heads up on this.

Has anyone applied this PTF yet? Our systems programmer is hopeing someone else has, and has not had problems with it. She was reluctant to apply it so soon after the APAR closed.

Thanks.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Steve Grimes Washington University Information Systems OS/390 V2R6 DB2 UDB 6.1 W: 314-935-4376 H: 636-928-5005 Isa.64:6 Steve_Grimes@aismail.wustl.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ---------------------- Forwarded by Steve Grimes/Systems/ais on 05/04/2000 01:13 PM --------------------------- [...] 17105 36 62_Switching to DB2: Faster and cheaper - Free DB2 Migration Tool13_Martin Spratt18_sprattm@US.IBM.COM30_Thu, 4 May 2000 14:14:05 -0400510_us-ascii DB2-L List members,

The beta version of the ManTech SQL Conversion Workbench Version 3 (code named SProCT) is now available as a free download and will convert Oracle, Sybase and SQLServer to DB2.

Drop by the IBM DB2 Migration portal and link to DB2 Migration technical online webinars, white papers, customer case studies and other resources. You can also link to the SMPO DB2 Migration Team website for assistance to migrate from Informix, Adabas, IDMS, Datacom, Supra and Total. [...] 17142 67 72_DB2 V7.1 - easier migrations from Oracle, Sybase, SQLServer and Informix13_Martin Spratt18_sprattm@US.IBM.COM30_Thu, 4 May 2000 14:28:07 -0400571_us-ascii DB2-L list members,

DB2 V7.1 carries enhancements to enable rapid and economic migrations from other database engines to DB2. These features enable smoother emulation of competing features so migrations can become faster and less complicated, using manual or automated conversion methods.

DB2 V7.1 enhancements that aid in DB2 Migrations are:

1. New SQL Procedure Language * ANSI and ISO Standards based * Based on PSM (Perstisent Storage Module) * Available and portable on all DB2 platforms * Leverage current T-SQL and PL/SQL skills [...] 17210 31 46_Re: DB2 Connect for DB2 V 5 and NT (SQL10007N)11_Tim Kearney22_tkearney@ACCESS360.COM30_Thu, 4 May 2000 15:00:02 -0500553_- On Thu, 4 May 2000 12:35:57 -0500, Shauna Hadden wrote:

>What DB2 authorizations do you have? Are you using the userid you set up >when DB2 UDB was installed (DB2ADMIN)? Or are you using a different >userid? If a different one, check to see what DB2 authorizations it has. > >================================================ >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. [...] 17242 129 16_Re: Index Design23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 4 May 2000 16:42:42 -0400593_- Thank you Michael,

Yes..... it is unusual to have so many 'IN' in an SQL. Actually the SQL is built dynamically after the user selects few items of the menu(s), so even if the user may select 1 item (which is rare), the application still builds the IN list.

Sanjay

> -----Original Message----- > From: Michael Hannan [SMTP:mhannan@C031.AONE.NET.AU] > Sent: Wednesday, May 03, 2000 8:47 PM > To: DB2-L@RYCI.COM > Subject: Re: Index Design > > Sanjay, > > I believe IN prohibits use of List Prefetch and Multi-index processing in > DB2 V5. > > Consider indexes: > a. [...] 17372 27 23_Info in DB2 Logs OS/39017_Pickett, Mary Ann24_pickettm@DOT.STATE.AL.US30_Thu, 4 May 2000 16:46:49 -0500397_iso-8859-1 Hi all,

We are on version 5.1 DB2 for OS/390. Does anyone know of a way to tell from the logs what user has updated a table? For example, we had some rows deleted from a table and would like to find out who did the deletion. We dumped the logs with DSN1LOGP and could see when they were deleted but could not figure out how to tie the delete operation to a particular user. [...] 17400 71 19_Unix "grep" Command11_Dale Miller23_dmille@TRADESERVICE.COM30_Thu, 4 May 2000 18:08:16 -0400545_us-ascii

Hello, We are in the process of porting from a MicroFocus COBOL running on AIX with CISAM files to the same environment but with DB2 as the database. One nice feature of our system is the way we accomplish key word searches. A Cobol program does a 'system call' using a Unix command named Grep and a file name to be greped. The Unix Grep program will read through the file ( a CISAM database record file only; not the key file) looking for a string of data to match. The Unix Grep program is not compatable with Windows or [...] 17472 55 23_Re: Unix "grep" Command18_Gert van der Kooij15_geko@WANADOO.NL30_Fri, 5 May 2000 00:15:14 +0200471_iso-8859-1 Hi, Can't you use 'normal' embedded sql? We use it with MF COBOL for our application which runs unchanged on both AIX and NT. You can't use a grep command to search a db2 database. You should be able to start a script witch executes db2 clp statements and pipe the output to some NT grep program (MKS Toolkit grep, NT Resource Kit find or another unix emulation toolkit). I think it doesn't make sense to do it that way but maybe you can make this clear? [...] 17528 42 23_Re: Unix "grep" Command12_Kline, Wayne27_wayne.kline@INGRAMMICRO.COM30_Thu, 4 May 2000 15:42:45 -0700633_iso-8859-1 Dale: What do you hope to accomplish by moving to DB2?

Wayne

-----Original Message----- From: Dale Miller [mailto:dmille@TRADESERVICE.COM] Sent: Thursday, May 04, 2000 3:08 PM To: DB2-L@RYCI.COM Subject: Unix "grep" Command



Hello, We are in the process of porting from a MicroFocus COBOL running on AIX with CISAM files to the same environment but with DB2 as the database. One nice feature of our system is the way we accomplish key word searches. A Cobol program does a 'system call' using a Unix command named Grep and a file name to be greped. The Unix Grep program will read through [...] 17571 43 8_bad news8_jgross2817_jgross28@HOME.COM30_Thu, 4 May 2000 17:26:24 -0500475_us-ascii I'm very sorry to have to share this news. My name is Shannon and I'm middle child of one of the members of your list serve, Jeff Gross, jgross27@home.com. Dad passed away just a few days ago at his home of an aneurysm. It was quick and painless, if very sudden. As you might already know, Dad was doing well in all areas: business, family, friendships, spirituality, and even health. It is of great comfort to our family that he ended on such a positive note. [...] 17615 35 56_Determining unused indexes in a Dynamic SQL environment.0_39_Patrick_D_Poziwilko@EMAIL.WHIRLPOOL.COM30_Thu, 4 May 2000 14:21:52 -0400550_US-ASCII The SAPR3 comes installed with thousands of indexes of which a many are not being used. The high water mark for open datasets is about 4000. We have a number of tables that perform issues with inserts, updates, deletes and selects. A mumber of the tables currently have a large number of indexes that appear to be questionable (low cardinality or containing columns not been used by the company). A problem is trying to determine which indexes are not being used. The environment is totally dynamic, no specific plans statically bound. [...] 17651 35 43_Errored user indexes after DB2 V5.1 upgrade16_Staci Stephenson28_staci_stephenson@ACS-INC.COM30_Thu, 4 May 2000 14:54:55 -0500598_US-ASCII Hi, We're receiving the following errors on some user indexes since upgrading a test DB2 from V4.1 (9805) to V5.1 (9911). DSNI014I _DSN2 DSNKIXDB DATA IN USE DURING ABEND REASON 00C90101 ERQUAL 5006 TYPE 00000303 NAME DIBNF03A.XBNX002 .X'00000002' CONNECTION-ID=QCPCICSR CORRELATION-ID=GT00O020 LUW-ID=* Our short term resolution is to recover the indexes, but obviously we can't upgrade our production DB2 if the upgrade is going to potentially corrupt user indexes that could take hours to recover. If anyone has ran into this and can share their experience, I would appreciate it. [...] 17687 165 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Fri, 5 May 2000 10:08:14 +1000588_us-ascii Rick, I hope you don't mind me adding my A$0.02 worth (might be worth less in other parts). I know this has been covered before, but for those to whom I'm telling how to suck eggs please bear with me. Basically each pageset (tablespace, tablespace partition, index space, index space partition) has a queue for 'updated' (ie pages with set-write) pages. These would be 'linked' in increasing page number, so that writes will be done in a forward sequential fashion. One write engine (of which there is an available pool of 300 unless you alter your ZPARMS) will write pages [...] 17853 52 55_Re: non-matching idex scan with no additonal predicates10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Fri, 5 May 2000 03:40:29 GMT583_us-ascii Hi Sandi, If you are talking something about non-matching index scan with equal predicate then it is not but if you are talking about non-matching index scan with something like COL >= Lowest value(literals) and if at all optimizer follows the index for this and it is not the index only access then it is costlier than tablespace scan. At this point of time i can say it depends ,it can be less costlier than tablespace scan, same as tablespace scan or may be costlier than tablespace scan....it depends on the query you write , host variables, literals(if there) and [...] 17906 78 60_Re: Determining unused indexes in a Dynamic SQL environment.13_Adrian Savory24_adrian.savory@ZURICH.COM30_Fri, 5 May 2000 11:07:59 +0100524_us-ascii Patrick,

I agree this is a difficult problem, and one I've not really had a chance to tackle in a big way. There's always the chance that you remove an index only to find out that your critical year-end batch relies on it, even though it's hardly ever used elsewhere.

One option you might want to try is slapping on a performance trace to collect IFCIDs 22 and 63. IFCID 22 gives you a mini-plan for each SQL statement traced, while 63 is the SQL statement itself. Be prepared for a lot of data! [...] 17985 32 21_OS/390 DB2 V5 with V320_Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY30_Fri, 5 May 2000 18:47:48 -0700328_us-ascii Hi,

We have different versions of DB2 OS/390 on the same LPAR at the moment (Version 3, Version 4). We intend to migrate the Version 4 to Version 5 whilst leaving Version 3 in tact. Only Version 5 libraries will be defined in the LNKLST whilst for Version 3, STEPLIP be specified (exception being DSNLINK). [...] 18018 76 12_Re: bad news9_Ted Pesta18_tpesta@AMQUEST.COM30_Fri, 5 May 2000 07:05:17 -0400633_iso-8859-1 Dear Shannon, Please accept my deepest condolences for the loss of your beloved father.

Ted Pesta AmQUEST, Inc.

-----Original Message----- From: jgross28 [mailto:jgross28@HOME.COM] Sent: Thursday, May 04, 2000 6:26 PM To: DB2-L@RYCI.COM Subject: bad news

I'm very sorry to have to share this news. My name is Shannon and I'm middle child of one of the members of your list serve, Jeff Gross, jgross27@home.com. Dad passed away just a few days ago at his home of an aneurysm. It was quick and painless, if very sudden. As you might already know, Dad was doing well in all areas: business, family, [...] 18095 18 14_15 table limit11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Fri, 5 May 2000 07:03:35 -0500362_- Hello DB2-L, For about a year (since PQ21815), the 15 table join limit has been increased. Now it is being re-inforced by PQ31326. Does anyone have any experience with exceeding the 15 table limit and then trying to rein it back to 15? In other words, how does one close the barn door after the horse is gone? Regards, John Rosser Duke Energy Corporation [...] 18114 28 43_Problem installing DB2 Connect V6 on Win NT0_19_Sven.Heidorn@CSN.SE30_Fri, 5 May 2000 14:20:46 +0200508_ISO-8859-1 Hi all,

I'm trying to install DB2 Connect PE V6 on Win NT. The machine has DB2 UDB PE V6 with fix pack 2 installed. When I start the install shield I get a warning that the products are on different maintenance levels. My plan was to install DB2 Connect and then put on fix pack 3 for both products. Now, when I get to the last window in the install shield and click Next to start the installation, I get a message that says, "component missing in the registry" and the install hangs. [...] 18143 71 47_Re: Errored user indexes after DB2 V5.1 upgrade11_Chad Walmer19_cwalmer@RITEAID.COM30_Fri, 5 May 2000 08:33:22 -0400587_iso-8859-1 Staci, We received the same type of errors when we applied maintenance from 9906-0001. If your shop is using Platinum Rapid Reorg then this product is probably the cause of your errors. Apparently, Reorg was incorrectly setting the HPGIFLGS field in the index header page to x'50' instead of x'40'. The x'50' indicates that the index is a partitioned index when in fact it is not and this conflicts with the description in the DBD. Either PTF UQ32127 or UQ39035 (forget which one) now checks the validity of this bit (index header page compared to DBD) according to IBM. [...] 18215 86 63_Re: optimizer use of runstats multi-column frequency statistics11_Jim Medlock24_jmedlock@ALLIANCESYS.COM30_Fri, 5 May 2000 07:35:28 -0600 18302 74 25_Re: OS/390 DB2 V5 with V312_John Cameron26_JCameron@MSI-INSURANCE.COM30_Fri, 5 May 2000 07:56:18 -0500451_us-ascii Actually, we ran into this. We are running DB2 Version 5 on OS/390 (2.6). We have been using stored procedures for awhile and decided to change to Work Load Managed stored procedures. Couldn't get them to work. Turned out that the last DBA forgot to replace the ERLY code when he went from Version 3 to Version 4. Other than WLM stored procedures, everything else worked with the old ERLY code so we didn't know about it until recently. [...] 18377 45 18_Re: 15 table limit12_craig patton21_prgpatton@HOTMAIL.COM28_Fri, 5 May 2000 06:00:42 PDT313_- John,

I would NOT do this, but 1 technique I've heard of is to Break the SQL into VIEWS and then JOIN the views. This effectively gets rid of the 15 table limit (provided the views Materialize). Personally, I'd suggest rewriting the SQL and performing more work in the calling program, if possible. [...] 18423 94 18_Re: 15 table limit13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM30_Fri, 5 May 2000 09:16:13 -0400324_us-ascii Regrettably, one must read the manuals closely to understand what the "15-table limit" is/was, and what actually was "fixed". I recommend that we all be very careful about saying or writing "this limitation has been removed", or "this limitation has been fixed". That's not the case, ..., not exactly, anyway. [...] 18518 28 28_How to read info in DB2 Logs17_Pickett, Mary Ann24_pickettm@DOT.STATE.AL.US30_Fri, 5 May 2000 08:48:32 -0500434_iso-8859-1 Hello All,



I am trying to identify which user deleted some records from a particular table. Does anyone know of a way to tell from the logs what user has updated a table? I dumped the logs with DSN1LOGP and could see when they were deleted but could not figure out how to tie the delete operation to a particular user. We are on version 5.1 DB2 for OS/390. Any help on this will be greatly appreciated. [...] 18547 23 21_DB2 OS/390 vs DB2 UDB14_Michael Cramer19_mcramer@ORGANIC.COM30_Fri, 5 May 2000 10:11:24 -0400458_us-ascii I'm transitional from supporting DB2 OS/390 V5.1 to DB2 UDB V6.1 on Sun Solaris and I'm looking for resources that will help me understand the differences. I'm primarily interested in the physical file system differences. Does anyone have links to resources? I have classes scheduled for the future. .............................. Michael A. Cramer Hosting Engineer Organic, Inc. mcramer@organic.com 248.205.3081 .............................. [...] 18571 135 18_Re: 15 table limit14_Richard Yevich21_ryevich@BELLSOUTH.NET30_Fri, 5 May 2000 09:23:31 -0400336_iso-8859-1 15 table limit was removed recently in V6 with an ENHANCEMENT. It was not in the original GA. It is part of the V6 refresh announced this week and is available through normal maintenance.

BUT...this is only for Star Joins!!!!!!!!!!

The largest one I have been informed of so far, was a 31 table star join. [...] 18707 38 98_Central PA DB2 Users Group Classes by Martin Hubel - June 27th & June 28th - The DB2 I/O Workshops10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Fri, 5 May 2000 10:28:40 -0400410_us-ascii Greetings everyone!

The Central PA DB2 Users Group will be hosting Martin Hubel & his DB2 I/O Workshop series. The classes are tentatively scheduled for: Tuesday, June 27th - The DB2 I/O Workshop: Part 1: Physical I/O Wednesday, June 28th - The DB2 I/O Workshop: Part 2: Logical I/O

The classes will be held at the Camp Hill Corporate Center, Building 300 & will be $150 per class. [...] 18746 69 58_Re: PC/IXF file format and version #, DB2 UDB V6 on Win NT8_Rob Lake20_robert.lake@CONTI.DE30_Fri, 5 May 2000 09:18:57 -0500302_- As far as PC/IXF file formats are concerned, my understanding is as follows:

For DB2 UDB V5 the IXFHVERS field in the PC/IXF file header record which indicates the PC/IXF format level used when the file is created is '0001'

For DB2 UDB V6 the IXFHVERS field has a value of '0002'. [...] 18816 74 78_Last PeopleSoft 7.x Tuning Class for DB2 for OS/390 - San Francisco - June 5-613_BRIAN HOLROYD31_BRIAN_HOLROYD@DB-CONSULTING.COM30_Fri, 5 May 2000 07:47:54 -070099_iso-8859-1 Please find details and registration at http://www.db-consulting.com/seminar.htm

18891 106 61_QRe: Determining unused indexes in a Dynamic SQL environment.9_Rob Crane22_racrane@CONCENTRIC.NET30_Fri, 5 May 2000 09:05:17 -0600554_us-ascii Typically the vendor approach to this question/issue is to capture all the SQL (dynamic and static) for a subsystem and gather information on the objects that were used to satisfy your SQL queries (buffer pools, stogroups, database, tablespace, table, index, etc.). This collection occurs during a user specified interval (say 12 hours). Once the information is gathered it can be compared to the catalog (dsndb06) and kick out reports about the objects that exist in the catalog but were not used by the SQL that ran during that interval. [...] 18998 35 40_Changing Passwords via DB2 Connect v5.2.15_Timothy J Tiner28_Timothy_J_Tiner@NOTES.FH.COM30_Fri, 5 May 2000 08:11:07 -0700376_us-ascii We are experiencing some difficulty using the 'Change Password' feature when using DB2 Connect version 5.2. I was curious if someone else in DB2 land has had some experience with this. We realize that the 'Extended Security' parameter in the DB2 for OS/390 sub-system must be enabled to 'YES'. We have done this. However, we continue to see the following error: [...] 19034 13 18_Re: 15 table limit11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Fri, 5 May 2000 10:13:04 -0500313_- I should have mentioned this is DB2 V5 subsystem.

Thanks, John

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19048 75 18_Re: DB2 V7 manuals10_Park, Stan17_SPark@STATE.NM.US30_Fri, 5 May 2000 09:36:42 -0600590_iso-8859-1 You can download the V7 UDB Beta PDF manuals *only* (following is from their site):

Thank you. To begin downloading, choose a file below.



Documentation Readme Information

readme.txt (54,623)

Manuals in PDF format

openbdoc.zip (118,233,429)

OLE DB Documentation

ibmdadb2.htm (1,300,701)

OLAP Documentation

olappdf.zip (11,770,200) ....etc -----Original Message----- From: Richard Yevich [mailto:ryevich@BELLSOUTH.NET] Sent: Wednesday, May 03, 2000 10:01 PM To: DB2-L@RYCI.COM Subject: Re: DB2 V7 manuals [...] 19124 67 27_Re: Info in DB2 Logs OS/39015_Toine Michielse18_vndobtm@US.IBM.COM30_Fri, 5 May 2000 17:40:38 +0100332_us-ascii Hello Mary Ann,

Once you have found the log record that represents the delete, you can correlate that to the record that represents a particular UR (using the URID on the printed logrecord).

You can then find the UR in the summary report. The UR record will identify the AUTHID..

Hope this helps, [...] 19192 162 62_Bad News (was Re: Errored user indexes after DB2 V5.1 upgrade)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:41:59 -0500645_us-ascii

At 02:54 PM 5/4/00 -0500, you wrote: >Hi, >We're receiving the following errors on some user indexes since upgrading >a test >DB2 from V4.1 (9805) to V5.1 (9911). >DSNI014I _DSN2 DSNKIXDB DATA IN USE DURING ABEND > REASON 00C90101 > ERQUAL 5006 > TYPE 00000303 > NAME DIBNF03A.XBNX002 .X'00000002' > CONNECTION-ID=QCPCICSR > CORRELATION-ID=GT00O020 > LUW-ID=* >Our short term resolution is to recover the indexes, but obviously we can't >upgrade our production DB2 if the upgrade is going to potentially corrupt user >indexes that could take hours to recover. If anyone has ran into this and can >share their experience, I [...] 19355 525 60_Bad News (was Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:02 -0500594_us-ascii

At 10:08 AM 5/5/00 +1000, you wrote: >Rick, > I hope you don't mind me adding my A$0.02 worth (might be worth > less in >other parts). I know this has been covered before, but for those to whom I'm >telling how to suck eggs please bear with me. Basically each pageset >(tablespace, tablespace partition, index space, index space partition) has a >queue for 'updated' (ie pages with set-write) pages. These would be >'linked' in >increasing page number, so that writes will be done in a forward sequential >fashion. One write engine (of which there is an available pool of [...] 19881 206 70_Bad News (was Re: non-matching idex scan with no additonal predicates)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:15 -0500582_us-ascii

At 03:40 AM 5/5/00 +0000, you wrote: >Hi Sandi, > If you are talking something about non-matching index scan with equal >predicate then it is not but if you are talking about non-matching index >scan with something like COL >= Lowest value(literals) and if at all >optimizer follows the index for this and it is not the index only access >then it is costlier than tablespace scan. > At this point of time i can say it depends ,it can be less costlier >than tablespace scan, same as tablespace scan or may be costlier than >tablespace scan....it depends on the [...] 20088 302 75_Bad News (was Re: Determining unused indexes in a Dynamic SQL environment.)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:55 -0500557_us-ascii

At 11:07 AM 5/5/00 +0100, you wrote: >Patrick, > >I agree this is a difficult problem, and one I've not really had a chance to >tackle in a big way. There's always the chance that you remove an index >only to >find out that your critical year-end batch relies on it, even though it's >hardly >ever used elsewhere. > >One option you might want to try is slapping on a performance trace to collect >IFCIDs 22 and 63. IFCID 22 gives you a mini-plan for each SQL statement >traced, >while 63 is the SQL statement itself. Be prepared for a [...] 20391 274 27_Bad News (was Re: bad news)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:56 -0500578_us-ascii

At 07:05 AM 5/5/00 -0400, you wrote: >Dear Shannon, >Please accept my deepest condolences for the loss of your beloved father. > >Ted Pesta >AmQUEST, Inc. > > -----Original Message----- > From: jgross28 [mailto:jgross28@HOME.COM] > Sent: Thursday, May 04, 2000 6:26 PM > To: DB2-L@RYCI.COM > Subject: bad news > > I'm very sorry to have to share this news. My name is >Shannon and I'm > middle child of one of the members of your list serve, Jeff >Gross, > jgross27@home.com. Dad passed away just a few days ago at >his home of an > aneurysm. It was quick [...] 20666 127 33_Bad News (was Re: 15 table limit)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:58 -0500664_us-ascii

At 07:03 AM 5/5/00 -0500, you wrote: >Hello DB2-L, >For about a year (since PQ21815), the 15 table join limit has been >increased. Now it is being re-inforced by PQ31326. >Does anyone have any experience with exceeding the 15 table limit and then >trying to rein it back to 15? In other words, how does one close the barn >door after the horse is gone? >Regards, >John Rosser >Duke Energy Corporation > >================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can >be reached at DB2-L-REQUEST@RYCI.COM. [...] 20794 138 62_Bad News (was Re: Problem installing DB2 Connect V6 on Win NT)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:58 -0500541_us-ascii

At 02:20 PM 5/5/00 +0200, you wrote: >Hi all, > >I'm trying to install DB2 Connect PE V6 on Win NT. The machine has DB2 UDB >PE V6 with fix pack 2 installed. When I start the install shield I get a >warning that the products are on >different maintenance levels. >My plan was to install DB2 Connect and then put on fix pack 3 for both >products. >Now, when I get to the last window in the install shield and click Next to >start the installation, I get a message that says, "component missing in >the registry" and the [...] 20933 249 62_Bad News (was Re: Errored user indexes after DB2 V5.1 upgrade)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:42:59 -0500604_us-ascii

At 08:33 AM 5/5/00 -0400, you wrote: >Staci, >We received the same type of errors when we applied maintenance from >9906-0001. If your shop is using Platinum Rapid Reorg then this product is >probably the cause of your errors. Apparently, Reorg was incorrectly >setting the HPGIFLGS field in the index header page to x'50' instead of >x'40'. The x'50' indicates that the index is a partitioned index when in >fact it is not and this conflicts with the description in the DBD. Either >PTF UQ32127 or UQ39035 (forget which one) now checks the validity of this >bit (index header page [...] 21183 381 78_Bad News (was Re: optimizer use of runstats multi-column frequency statistics)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:10 -0500581_us-ascii At 07:35 AM 5/5/00 -0600, you wrote:

>Steve, > >We'll be applying and testing the V5 version of this APAR sometime next >week. I can forward status to you at that time. > > > >You may want to consider configuring a DB2 subsystem under a test LPAR for >future testing. We clone our development DB2 subsystem under such an LPAR >and use it to test fixes prior to migrating them into our production >environment. > >Regards, > >Jim > >@ >Sent by: DB2 Data Base Discussion List >05/04/2000 01:14 PM EST >Please respond to DB2 Data Base Discussion [...] 21565 268 40_Bad News (was Re: OS/390 DB2 V5 with V3)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:10 -0500594_us-ascii

At 07:56 AM 5/5/00 -0500, you wrote: >Actually, we ran into this. We are running DB2 Version 5 on OS/390 (2.6). >We have been using >stored procedures for awhile and decided to change to Work Load Managed >stored procedures. >Couldn't get them to work. Turned out that the last DBA forgot to replace >the ERLY code when he >went from Version 3 to Version 4. Other than WLM stored procedures, >everything else worked >with the old ERLY code so we didn't know about it until recently. > >John Cameron >651.631.7188 >jcameron@msi-insurance.com > > > > Jenny Kuan > Kong Lang [...] 21834 187 33_Bad News (was Re: 15 table limit)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:11 -0500638_us-ascii

At 06:00 AM 5/5/00 -0700, you wrote: >John, > >I would NOT do this, but 1 technique I've heard of is to Break the SQL into >VIEWS and then JOIN the views. This effectively gets rid of the 15 table >limit (provided the views Materialize). Personally, I'd suggest rewriting >the SQL and performing more work in the calling program, if possible. > >Craig Patton >Independent DBA > > >>From: John Rosser >>Reply-To: DB2 Data Base Discussion List >>To: DB2-L@RYCI.COM >>Subject: 15 table limit >>Date: Fri, 5 May 2000 07:03:35 -0500 >> >>Hello DB2-L, >>For about a year (since [...] 22022 308 33_Bad News (was Re: 15 table limit)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:12 -0500601_us-ascii

At 09:16 AM 5/5/00 -0400, you wrote: >Regrettably, one must read the manuals closely to understand what the >"15-table limit" is/was, and what actually was "fixed". I recommend that >we all be very careful about saying or writing "this limitation has been >removed", or "this limitation has been fixed". That's not the case, ..., >not exactly, anyway. > >As I understand it from the V6 SQL manual, there is still a 15-"thing" >limit, i.e., you can only specify 15 "tables or table expressions" >separated by commas in the FROM clause of a SELECT. So, in a sense, there >is still [...] 22331 145 47_Bad News (was Re: How to read info in DB2 Logs)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:21 -0500600_us-ascii

At 08:48 AM 5/5/00 -0500, you wrote: >Hello All, > > >I am trying to identify which user deleted some records from a particular >table. >Does anyone know of a way to tell from the logs what user has updated a >table? >I dumped the logs with DSN1LOGP and could see when they >were deleted but could not figure out how to tie the delete operation to a >particular user. > We are on version 5.1 DB2 for OS/390. Any help on this will be greatly >appreciated. > > >Thanks, >Mary Ann Pickett >DBA/ALDOT > >================================================ >To change your subscription [...] 22477 134 40_Bad News (was Re: DB2 OS/390 vs DB2 UDB)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:24 -0500756_us-ascii

At 10:11 AM 5/5/00 -0400, you wrote: >I'm transitional from supporting DB2 OS/390 V5.1 to DB2 UDB V6.1 on Sun >Solaris and I'm looking for resources that will help me understand the >differences. I'm primarily interested in the physical file system >differences. Does anyone have links to resources? I have classes >scheduled for the future. >.............................. >Michael A. Cramer >Hosting Engineer >Organic, Inc. >mcramer@organic.com >248.205.3081 >.............................. > >================================================ >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. [...] 22612 175 117_Bad News (was Re: Central PA DB2 Users Group Classes by Martin Hubel - June 27th & June 28th - The DB2 I/O Workshops)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:32 -0500567_us-ascii

At 10:28 AM 5/5/00 -0400, you wrote: >Greetings everyone! > >The Central PA DB2 Users Group will be hosting Martin Hubel & his DB2 I/O >Workshop series. The classes are tentatively scheduled for: >Tuesday, June 27th - The DB2 I/O Workshop: Part 1: Physical I/O >Wednesday, June 28th - The DB2 I/O Workshop: Part 2: Logical I/O > >The classes will be held at the Camp Hill Corporate Center, Building 300 & >will >be $150 per class. > >Martin is also planning to stay & present at our free RUG meeting on June >29th. >(I'll be sending out the RUG [...] 22788 257 73_Bad News (was Re: PC/IXF file format and version #, DB2 UDB V6 on Win NT)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:32 -0500546_us-ascii

At 09:18 AM 5/5/00 -0500, you wrote: >As far as PC/IXF file formats are concerned, my understanding is as follows: > >For DB2 UDB V5 the IXFHVERS field in the PC/IXF file header record which >indicates the PC/IXF format level used when the file is created is '0001' > >For DB2 UDB V6 the IXFHVERS field has a value of '0002'. > >This is because the format has changed for V6 to cater for the increases in >SQL limits, such as the length of a table name (the PC/IXF format level 2 >now provides a 128 character field to hold [...] 23046 102 97_Bad News (was Re: Last PeopleSoft 7.x Tuning Class for DB2 for OS/390 - San Francisco - June 5-6)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:42 -0500652_us-ascii At 07:47 AM 5/5/00 -0700, you wrote: >Please find details and registration at >http://www.db-consulting.com/semi >nar.htm >

This is an auto-reply message.

I'm very sorry to have to share this news. My name is Shannon and I'm middle child of one of the members of your list serve, Jeff Gross, jgross27@home.com. Dad passed away just a few days ago at his home of an aneurysm. It was quick and painless, if very sudden. As you might already know, Dad was doing well in all areas: business, family, friendships, spirituality, and even health. It is of great comfort to our family that [...] 23149 168 59_Bad News (was Re: Changing Passwords via DB2 Connect v5.2.)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:44 -0500654_us-ascii

At 08:11 AM 5/5/00 -0700, you wrote: >We are experiencing some difficulty using the 'Change Password' feature when >using DB2 Connect version 5.2. I was curious if someone else in DB2 land has >had some experience with this. We realize that the 'Extended Security' >parameter in the DB2 for OS/390 sub-system must be enabled to 'YES'. We have >done this. However, we continue to see the following error: > >"The connection test failed. > >[IBM][CLI Driver] SQL30020N Execution failed because of a Distributed >Protocol >Error that will affect the successful execution of subsequent commands and SQL >statements: Reason Code "1232". [...] 23318 252 33_Bad News (was Re: DB2 V7 manuals)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:43:58 -0500653_us-ascii

At 09:36 AM 5/5/00 -0600, you wrote: >You can download the V7 UDB Beta PDF manuals *only* >(following is from their site): > >Thank you. To begin downloading, choose a file below. > > > Documentation > Readme Information > > readme.txt (54,623) > > Manuals in PDF format > > openbdoc.zip (118,233,429) > > OLE DB Documentation > > ibmdadb2.htm (1,300,701) > > OLAP Documentation > > olappdf.zip (11,770,200) >....etc >-----Original Message----- >From: Richard Yevich [mailto:ryevich@BELLSOUTH.NET] >Sent: Wednesday, May 03, 2000 10:01 PM >To: DB2-L@RYCI.COM >Subject: Re: DB2 V7 manuals > > >The V7 manuals (DB2 for UNIX/NT etc)., [...] 23571 115 33_Bad News (was Re: 15 table limit)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:44:19 -0500406_us-ascii

At 10:13 AM 5/5/00 -0500, you wrote: >I should have mentioned this is DB2 V5 subsystem. > >Thanks, >John > >================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can >be reached at DB2-L-REQUEST@RYCI.COM.

This is an auto-reply message. [...] 23687 231 42_Bad News (was Re: Info in DB2 Logs OS/390)8_jgross2817_jgross28@HOME.COM30_Fri, 5 May 2000 12:44:21 -0500667_us-ascii

At 05:40 PM 5/5/00 +0100, you wrote: >Hello Mary Ann, > >Once you have found the log record that represents the delete, you can >correlate that to the record that represents a particular UR (using the >URID on the printed logrecord). > >You can then find the UR in the summary report. The UR record will >identify the AUTHID.. > >Hope this helps, > >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 > > >"Pickett, Mary Ann" @RYCI.COM> on 05/04/2000 >10:46:49 PM > >Please [...] 23919 79 57_Re: non-matching index scan with no additional predicates15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM30_Fri, 5 May 2000 12:54:47 -0700527_iso-8859-1 This is the query in question. Explain shows access type = I, match cols = 0, index only = N, and prefetch = S. The only index defined for the table does include SALES_TICKET_DTE as the second column. This table is small so it may not be a big deal, but I've seen it recently on some of our larger tables also. What else can I look at to determine if something needs to change here, like adding an index on the predicate? Or is a non-matching index scan that is not index only generally considered a bad thing? [...] 23999 152 23_Re: Unix "grep" Command11_Dale Miller23_dmille@TRADESERVICE.COM30_Fri, 5 May 2000 16:35:50 -0400594_us-ascii

Wayne, We market a package to the electical distribution industry and for competitve purposes we need to offer a relational database option. We are also porting to SQLServer on NT, and may look at the new database offering from Oracle, when we accomplish the above. Some of the reasons would be allowing users to take advantage of more modern tools for customization and adhoc reporting, and to take advantage of stored procedures etc. and recovery features to approach 24x7 up time. There doesn't seem to be alot of users of DB2 on AIX, from the responses I get. Is that [...] 24152 76 78_Last PeopleSoft 7.x Tuning Class for DB2 for OS/390 - San Francisco - June 5-613_BRIAN HOLROYD31_BRIAN_HOLROYD@DB-CONSULTING.COM30_Fri, 5 May 2000 07:47:54 -070099_iso-8859-1 Please find details and registration at http://www.db-consulting.com/seminar.htm

24229 55 28_NEODBUG May 11, 2000 Meeting9_jim.szabo22_jim.szabo@MCIWORLD.COM30_Fri, 5 May 2000 18:59:34 -0400525_iso-8859-1 Subject: NEODBUG May Quarterly Meeting

The next quarterly meeting of the Northeast Ohio Database Users Group (NEODBUG) will be held on Thursday, May 11, 2000 at the Hilton South in Independence, Ohio, from 08:30am until 04:15pm.

The following topics will be presented:

"Next Generation Internet - Next Generation e-business" by Fernando Lopez of IBM Corporation. Fernando will talk about the Internet today, the "next generation" Internet, "next generation" e-business requirements, etc. [...] 24285 60 57_Re: non-matching index scan with no additional predicates10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Sat, 6 May 2000 06:12:52 GMT569_us-ascii Hello Sandi, First of all if the creation of an additional index pays good with minimal negative impact then we should always go for creating the new index in the used column.But, in your case, the range of the rows qualify always depend on the values in the host variables and if the range is going to be very high most of the time then you may not get any significant difference in the performance(except if the number of pages scanned is different by a high margin).If the ranges are very small or occasionaly high then additional index (if affordable [...] 24346 60 50_Jeff Gross (FW: Bad News (was Re: 15 table limit))4_IBM116_IBM1@UBIL.COM.TR30_Sat, 6 May 2000 10:14:07 +0300607_- for pity's sake,

please take Jeff Gross off the list, it is heart-breaking to keep on reading this...

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



> -----Original Message----- > > > This is an auto-reply message. > > I'm very sorry to have to share this news. My name is Shannon and I'm > middle child of one of the members of your list serve, Jeff Gross, > jgross27@home.com. Dad passed away just a few days ago at his home of an > aneurysm. It was quick and painless, if very sudden. As you might already > [...] 24407 43 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Fri, 5 May 2000 23:49:56 -0500536_us-ascii Joel Goldstein wrote: > > Yes, but that is exactly what you DON'T want..... because the > avg pages/write will not really change..... because you will flood > the system with physical write I/Os.

I still don't understand.

Bufferpool has, for example, 1000 pages comprised of all the indexes for a given table. If, at DB2 checkpoint, there are 500 updated pages, and let's say the comprise a footprint on DASD of 50 different 150 CI RBA ranges, won't there be 10 pages per physical write I/O channel program? [...] 24451 111 45_Re: VERTICAL DEFERRED WRITE THRESHOLD SETTING15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Fri, 5 May 2000 23:53:03 -0500335_us-ascii Joel Goldstein wrote: > > First, > DB2 can write either 32 or 64 pages (64 for utilities) per write. It can > schedule up to 128 pages for one > write engine. > When the updated pages are far apart (which you have no control over) you > just don't get a high number of > pages per write because of the 150 RBA threshold. [...] 24563 73 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM30_Sun, 7 May 2000 19:01:50 -0500711_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes): - English : http://www.multimania.com/db2usa/eliendb2.htm - Français: http://www.multimania.com/db2usa/liendb2.htm

Last update on Monday, May 8th 2000 ===================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm IBM announced a refresh of DB2 UDB for OS/390, Version 6 with new functions and recommended maintenance level.



- Technical papers: http://www.multimania.com/db2usa/earticle.htm Yevich, Lawson and Associates (YL&A, Inc) launched DB2 Performance Journal in format PDF. First issue is available for free on line (registration) [...]