1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l February 2000, week 1 2 140 42_Re: FW: SQL Performance for Report program0_24_ssethi@LOT.TATASTEEL.COM28_Tue, 1 Feb 2000 03:18:58 GMT483_us-ascii Hi Madhavan, I don't think if DB2 (and that too on OS/390) is there then there is a point going for 2nd approach.It is always better to try and tune the queries and have a look at the design issues for getting the better performance. Its important to use DB2 efficiently as it has got many capabilities. When u get helpless and cann't tune the queries try sequence ur application run so that it will be in the off-peak hours and the system can be utilised efficiently. [...] 143 159 42_Re: FW: SQL Performance for Report program0_24_ssethi@LOT.TATASTEEL.COM28_Tue, 1 Feb 2000 03:07:02 GMT515_us-ascii Hi, If for some of them u can have indexes then also the 1st approach is better than the 2nd one as the maintainability and automation of the program is much easier and better in the 1st approach.Suppose u are doing it thru the 2nd method then u have to have a JCL which will unload the data after doing select from the 3 tables, then sorting of data is done in the next step of the JCL and in the COBOL program reading 1 million rows is really boring .If u have got some changes in the report format [...] 303 69 52_Re: Regarding OPERATIVE column in sysibm.syspackage.0_24_ssethi@LOT.TATASTEEL.COM28_Tue, 1 Feb 2000 09:00:38 GMT392_us-ascii Hi Ravi,

A VALID 'N' plan/package can have OPERATIVE "Y" but an OPERATIVE "N" plan/package can never have VALID "Y"(as i think). If ur plan/package is refering to a particular object which it is not able to find after a try to REBIND(if VALID = "N") or it is not able to take the alternate route to process the requirements, then plan/package is marked as OPERATIVE "N". [...] 373 115 7_Locking10_Milos Flek17_milos.flek@FSP.CZ30_Tue, 1 Feb 2000 11:09:03 +0100320_iso-8859-2 I have the CursorStability isolation level & row locking is set for a table. This is my sample tbl1 table:

CREATE TABLE tbl1 (ID INTEGER NOT NULL, COL1 INTEGER );

CREATE UNIQUE INDEX XPKtbl1 ON tbl1 (ID ASC); CREATE INDEX XIE1tbl1 ON tbl1 (COL1 ASC); ALTER TABLE tbl1 ADD PRIMARY KEY (ID); [...] 489 97 12_DCLGEN ERROR14_Popy A. Dimoka24_dimokap@INTERAMERICAN.GR30_Tue, 1 Feb 2000 12:28:00 +0200680_iso-8859-7 HELLO LIST,

we have just migrated from DB2 FOR OS390 V4 to V5 (PUT 9907). Eversince , when i try to run dclgen i get the following error message: ------------------------------------------------------------------------------------------ DSNT408I SQLCODE = -818, ERROR: THE PRECOMPILER-GENERATED TIMESTAMP 15BDAA020A185D68 IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP 154475D01F032E28 BUILT FROM THE DBRM DSNECP68 DSNT418I SQLSTATE = 51003 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF' [...] 587 49 16_Re: DCLGEN ERROR9_SrinivasG17_SRINIVASG@INF.COM30_Tue, 1 Feb 2000 19:05:00 +0530761_iso-8859-7 HI, You have to start from the Precompiler stage in the Bind Process in order for the Time stamp on Load Module to match that of the Plan.

Regards,

Srinivas G

-----Original Message----- From: Popy A. Dimoka [mailto:dimokap@INTERAMERICAN.GR] Sent: Tuesday, February 01, 2000 3:58 PM To: DB2-L@RYCI.COM Subject: DCLGEN ERROR



HELLO LIST,

we have just migrated from DB2 FOR OS390 V4 to V5 (PUT 9907). Eversince , when i try to run dclgen i get the following error message: ---------------------------------------------------------------------------- -------------- DSNT408I SQLCODE = -818, ERROR: THE PRECOMPILER-GENERATED TIMESTAMP 15BDAA020A185D68 IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP [...] 637 115 16_Re: DCLGEN ERROR9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Tue, 1 Feb 2000 14:14:36 +0000466_us-ascii DCLGEN executes a DB2 load module and uses a DB2 package. The load module and the DB2 package contain a timestamp which indicates they are derived from the same source code. You are executing a load module which has a timestamp 15BDAA020A185D68 (=1996-12-16 20.16.33.61) and this is using a package which has a timestamp 154475D01F032E28 (=1994-11-06 15.14.12.23). It therefore looks like you are running a load module (V5?) with an old package (V4?). [...] 753 57 16_Re: DCLGEN ERROR0_24_lightsey@ITS.STATE.MS.US30_Tue, 1 Feb 2000 08:31:57 -0600428_us-ascii I had a similar error with APPLY on os390 getting the -818 when trying to talk to a UDB on unix. I ended up finding the jobs to relink the IBM-provided object modules for APPLY to recreate the load module and then rebound it. The -818 vanished.





"Popy A. Dimoka" cc: Sent by: DB2 Subject: DCLGEN ERROR Data Base Discussion List [...] 811 171 16_Re: DCLGEN ERROR13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 1 Feb 2000 10:24:42 -0500486_iso-8859-1 -818 Errors should be simple to resolve. In the case of DCLGEN, it's a bit more convoluted.

Step 1) find the load module you're executing. Step 2) find the DBRM timestamp in the catalog. Step 3) if they contain unequal times find out why

I had to do a bit of hacking to find the load module for DCLGEN. The module you're having problems with is DSNECP68. It happens to be a part of DSNECP10. You can use ISRFIND (or ISRDDN) if you are at ISPF v4 or beyond [...] 983 42 45_Where is the TCPIP info manual for SQL30081N?0_14_dcreed@CSC.COM30_Tue, 1 Feb 2000 09:28:47 -0600311_us-ascii OK, I am trying to find out in what manual I can look up a return code in. I am getting the famous SQL30081N msg.

I know I need to break out the rc1 rc2 rc3 etc.. but cannot seem to find what manual this information is contained within. Any pointers to the correct manual would be helpful. [...] 1026 212 32_Re: Deadlock and isolation level14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Tue, 1 Feb 2000 09:30:44 -0500398_iso-8859-1 Dr. Ebert,

I understand that you were not necessarily describing for OS/390 but you did make some comparisons. Unfortunately your description of Isolation Levels and Locking has no bearing on what is currently implemented in DB2 for OS/390. Rows are not always locked for CS, since lock avoidance was added to DB2 back in Version 3. This implemented what you refer to as CR. [...] 1239 67 49_Re: Where is the TCPIP info manual for SQL30081N?13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Tue, 1 Feb 2000 09:35:27 -0600314_US-ASCII Hello Danny, the return codes can be found in the IBM DB2 Connect APPC, CPI-C, and SNA Sense Codes Manual. Regards, Kurt





>>> 02/01/00 09:28AM >>> OK, I am trying to find out in what manual I can look up a return code in. I am getting the famous SQL30081N msg. [...] 1307 76 15_Re: Date format14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US30_Tue, 1 Feb 2000 10:51:11 -0500591_us-ascii Ken,

You didn't specify what environment you were wanting to use this date format in ? - If it is COBOL look into using the LE callable services - eg."3.5.33 CEEFTDS--Format Time and Date into Character String of SC26-3312-02"

HTH

Bill G.



Peter Schwarcz (Bigpond) wrote:

> the following sql works with DB2 UDB V5 for OS390 > > SELECT CURRENT DATE > , SUBSTR(DIGITS(YEAR(CURRENT DATE)),7,4) || '-' || > CASE MONTH(CURRENT DATE) > WHEN 1 THEN 'JAN' > WHEN 2 THEN 'FEB' > WHEN 3 THEN 'MAR' > WHEN 4 THEN 'APR' > WHEN 5 THEN 'MAY' > WHEN [...] 1384 78 11_Re: Locking14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Tue, 1 Feb 2000 09:54:37 -0500373_iso-8859-2 Milos,

Very important to know what version of DB2 and what Platform (Unix, NT, etc). I am also not sure what you are really trying to do, since COMMIT would certainly remove the timeout. It would also be a safe assumption here that the problem could be locking concurrency in index XIE1tbl1 and not the data. So how many rows are in this test table? [...] 1463 71 49_Re: Where is the TCPIP info manual for SQL30081N?9_Phil Cody22_pcody@DLLR.STATE.MD.US30_Tue, 1 Feb 2000 10:55:30 -0500505_iso-8859-1 That message can be found in THE Message Reference (5.2) on pg 531

Have had the same problem periodically for about two months. IBM reccomended that we install FIXPACK 12 " Interim A" . we are in the progress of scheduling in our test system. This seems to have stumped them also! ----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, February 01, 2000 10:28 AM Subject: Where is the TCPIP info manual for SQL30081N? [...] 1535 158 16_Re: DCLGEN ERROR12_Bing Alabata25_bing.alabata@MCMASTER.COM30_Tue, 1 Feb 2000 10:12:20 -0600615_us-ascii Hi Popy,

Using Tape 9906 in our V4 to V5 migration, we encountered the same error on running DSNTEJ2C (COBOL IVP job). As in your case, the DSNTIJSG ran OK. On closer examination, the V5 DSNTIJSG did not contain a BIND PLAN(DSNEDCL)... for DCLGEN. It did have a BIND PACKAGE(DSNEDCL)...

The -818 error stems from running the V5 DCLGEN module with the V4 DCLGEN PLAN. If you review your V4 install or migration, there was a BIND PLAN(DSNEDCL) MEMBER(DSNECP68)... Since it did not specify a PKLIST parameter, your DCLGEN PLAN still points to your V4 timestamp which obviously is different [...] 1694 16 11_DB recovery10_Nash Gupta19_nashgupt@US.IBM.COM30_Tue, 1 Feb 2000 10:14:29 -0600481_- Platfrom: DB 5.2 on AIX

Problem: The backup image is on server1 and I want to recover it on server2. I tried ftping the image on server2, but this doesn't work either. Whatz the best/reliable process for doing so?

TIA, Nash

================================================ 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. 1711 298 15_Re: Date format14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Tue, 1 Feb 2000 11:45:30 -0500309_iso-8859-1 This is typical of the way IBM does things. Instead of letting the user construct his own format (the way Windows does; for once Microsoft got something right), they play Big Brother and try to tell everyone what they can use. Of course they don't happen to have the format I use on letters ( [...] 2010 306 15_Re: Date format14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Tue, 1 Feb 2000 11:47:41 -0500531_iso-8859-1 Sorry for the incomplete transmission. Should read:

This is typical of the way IBM does things. Instead of letting the user construct his own format (the way Windows does; for once Microsoft got something right), they play Big Brother and try to tell everyone what they can use. Of course they don't happen to have the format I use on letters (YYYY MONTH D). Developers shoud try to leave as much choice to the users as possible instead of trying to read their minds and give them what they think they want. [...] 2317 173 44_Re: DB2 active logs and IBM Raid-1 type DASD22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Tue, 1 Feb 2000 11:45:46 -0500347_iso-8859-1 Rick -

I agree with you.

A single active log gets you a single archive log & a potential single point of failure on the archive log. If the single archive log is on mirrored disk, then maybe you can get away with it. However, archive log on Raid-1 DASD has the same risks you mentioned for active log on Raid-1 DASD. [...] 2491 293 32_Re: Deadlock and isolation level0_18_mebert@AMADEUS.NET30_Tue, 1 Feb 2000 17:52:29 +0100440_us-ascii Actually I got these Isolation levels and their effects from a recent Informix training; I certainly did not try to describe DB2's internal workings. However I also was told that these Isolation levels are ANSI standard, so that the behaviour should be (more or less) as described, irrespective of the actual implementation or RDBMS. Is that not so? (For the original problem by M. Flek, do you have a different explanation?) [...] 2785 84 49_Re: Where is the TCPIP info manual for SQL30081N?13_Umair Hussain25_umair_hussain@HOTMAIL.COM28_Tue, 1 Feb 2000 11:14:19 CST494_- here is the link to the message ... ftp://ftp.software.ibm.com/ps/products/db2/info/vr6/htm/db2m0/sql30000.htm#HDRSQL30000

I would check to see if db2tcp processess on your database server for your instance are running or not ....



Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|************** [...] 2870 164 44_Re: DB2 active logs and IBM Raid-1 type DASD22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Tue, 1 Feb 2000 12:27:13 -0500685_iso-8859-1 Greg -

Thanks, that's very useful.

I've always used dual logging & never had a reason to research if I could dual archive log from a single active log. That's what I get for assuming...





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

> ---------- > From: > Greg.Palgrave@BANKWEST.COM.AU[SMTP:Greg.Palgrave@BANKWEST.COM.AU] > Sent: Monday, January 31, 2000 7:05 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] DB2 active logs and IBM Raid-1 type DASD > > Mike, > > You can specify dual archives even if you only have single active logs. > > If you have dual actives, it tries to take a copy [...] 3035 26 65_Question 2. CAE exclusive -vs- Share MODE - can if find the user?0_14_dcreed@CSC.COM30_Tue, 1 Feb 2000 11:16:30 -0600510_us-ascii Sorry, if dupped got message that was rejected, trying again,

Thanks I got a good response from previous question so I will try another. I have recently found where a user selects the Exclusive mode on the CAE sign in and is locking everyone else out. This happens to be a pass thru to the host, but not sure it really matters. What is the easiest way to quickly find that user? I would personally like to visit this individual :) Is there a list dcs application or something I could use? [...] 3062 39 9_subscribe14_khalid mahsoun19_kmahsoun@TRI.NET.SA30_Tue, 1 Feb 2000 20:39:06 +030050_windows-1256 SUBSCRIBE DB2-L khalid mahsoun DIGEST 3102 40 9_subscribe14_khalid mahsoun19_kmahsoun@TRI.NET.SA30_Tue, 1 Feb 2000 20:42:57 +030050_windows-1256 SUBSCRIBE DB2-L khalid mahsoun DIGEST 3143 100 22_Re: bind authorization7_Amy Tam20_atam@PAINEWEBBER.COM30_Tue, 1 Feb 2000 12:41:09 -0500704_iso-8859-1 Doesn't it depends on whether you specified the owner or let it default. A Secondary authid can add new plans if your explicitly specify the OWNER value.

-----Original Message----- From: Harvey Wachtel [mailto:babybaby@UAPC.CUNY.EDU] Sent: Monday, January 31, 2000 4:40 PM To: DB2-L@RYCI.COM Subject: Re: bind authorization



Administration Guide contains a table titled "Privileges Required for Basic Operations" that explains the rules in detail. In the section on "Operations on Plans and Packages" the table indicates that FREE, COPY, BIND REPLACE, REBIND and execute can be authorized via a secondary authorization ID, but BIND of a new plan requires the primary [...] 3244 110 49_Re: Where is the TCPIP info manual for SQL30081N?12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 1 Feb 2000 10:28:45 -0800569_us-ascii We've had the same problem for about 5 months. Have opened several PMRs on it with little success so far. Have gotten a lot of finger pointing from one IBM group. First they pointed the finger at Microsoft, and then at the Network, never at the real problem.

We've put on lots of PTFs on TCPIP, DB2, OMVS, VTAM as a result with little success. Now we're trying to catch the failures with traces (both on the client - network niffer traces, and on the mainframe with packet traces. Hard part is to time the traces for randomly infrequently ocurring [...] 3355 125 22_Re: bind authorization0_23_Mike_Levine@TEKHELP.NET30_Tue, 1 Feb 2000 13:31:11 -0500361_us-ascii Hi,

This is true. The OWNER must have the necessary authority to BIND the plan. However, the OWNER can be a primary authorization id or a secondary authorization id. If the OWNER is allowed to default it defaults to the primary auth id which must have the necessary access.

Regards,

Michael Levine Premier Data Services, Inc. [...] 3481 11 12_RCT Question11_Tom Johnson25_new_to_db2@SPRINGMAIL.COM30_Tue, 1 Feb 2000 14:19:45 -0500377_- We have multiple AORs using the same RCT to attach to a DB2 subsystem. In the RCT we are setting up some protected threads. Not all AORs execute the same programs (ex:customer support vs. order add). The protected threads are for the order add applications. If we establish 5 protected threads is this 5 protected threads per AOR or a total of 5 protected threads? TIA. [...] 3493 76 75_Chuck Hoover's DB2 buffering and DB2 batch design presentations a re online16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM30_Tue, 1 Feb 2000 14:41:57 -0500444_iso-8859-1



As promised, I am letting everyone know that both Chuck Hoover presentations, are now available on our web site.

They are;

1) Everything You Ever Wanted to Know About DB2 Buffering (but were afraid to ask ) 2) Batch Application Design for DB2 for OS/390



Both these presentations are in PowerPoint 97. If you going to print them, make sure you print both the slides and the notes. [...] 3570 37 25_SELECT INTO not selecting12_Jason Hughes20_Jason.Hughes@TRW.COM30_Tue, 1 Feb 2000 10:47:38 -0800585_US-ASCII We have a stored procedure which is giving us some trouble and I'm not sure why. It is a COBOL stored procedure running against v5.1 on a OS/390 mainframe.

The first SELECT INTO statement is returning a +100, no matter what the values in the WHERE clause are. I have ran the SELECT select from both SPUFI and the UDB Command Center, replacing the host-variables with the respective hard-coded values, and it returns the correct row. I have doubled check the size/type of the host variables and they agree with the size/type of the columns being matched on. I know [...] 3608 36 16_Re: RCT Question0_23_Mike_Levine@TEKHELP.NET30_Tue, 1 Feb 2000 14:48:58 -0500615_us-ascii Hi,

Since the RCT is shared there will be five protected threads per region. The same is true for the other parms like THRDM, THRDA, etc. Each region thinks the RCT is it's own.

Regards,

Michael Levine Premier Data Services, Inc.



>We have multiple AORs using the same RCT to attach to a DB2 subsystem. In the RCT we are setting up some protected threads. Not all AORs execute the same programs (ex:customer support vs. order add). >The protected threads are for the order add applications. If we establish 5 protected threads is this 5 protected threads per AOR or [...] 3645 64 16_Re: RCT Question19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 1 Feb 2000 14:09:38 -0600565_iso-8859-1 Hi all, Are you SYSPLEX using Dynamic Transaction Routing (DTR)? If not, why not set up a RCT for the order add AORs, and another for the cust. serv. AORs. Better yet, a RCT per AOR.

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 [...] 3710 18 29_Re: SELECT INTO not selecting10_Ruth Blake15_rblake3@CSC.COM30_Tue, 1 Feb 2000 14:04:24 -0600506_us-ascii Here's a dumb suggestion, but if the host variables contain text values, be sure that upper-lower case is correct. It's easy to overlook and sometimes DISPLAYs appear to be uppercase anyway.

From the "I've seen it before" archive.

Ruth Blake

================================================ 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. 3729 19 12_SQL and REXX20_Alvarez, Maximiliano20_MAXIMILIANO@IADB.ORG30_Tue, 1 Feb 2000 15:21:43 -0500390_iso-8859-1 Hi All,

I was wondering if I could execute SQL statements from a REXX program.

I'd appreciate any help.

Thanks. Max.

================================================ 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. 3749 48 29_Re: SELECT INTO not selecting0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 1 Feb 2000 14:27:52 -0600420_us-ascii It's kind of an obvious suggestion, but have you debugged the program to be 100% sure that the WHERE clause host variable(s) is being populated with the correct value and that the value is there immediately before SQL execution? Since you've verified that the SQL works outside of the SP, is it possible that your paramter list going into the SP is incorrect or getting cleared before the SQL is executed? [...] 3798 33 16_Re: SQL and REXX20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 1 Feb 2000 15:49:38 -0500642_us-ascii You could if you have REXX DB2 interface from IBM or other vendor.

> -----Original Message----- > From: Alvarez, Maximiliano [SMTP:MAXIMILIANO@IADB.ORG] > Sent: Tuesday, February 01, 2000 3:22 PM > To: DB2-L@RYCI.COM > Subject: SQL and REXX > > Hi All, > > I was wondering if I could execute SQL statements from a REXX program. > > I'd appreciate any help. > > Thanks. > Max. > > ================================================ > 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. [...] 3832 26 28_FW: clustered database on NT12_Rakesh Madan22_rmadan@CYVEILLANCE.COM30_Tue, 1 Feb 2000 15:50:48 -0500397_iso-8859-1 I was wondering if anyone was running a clustered DB2 database on NT. I am to embark on such an endeavor and would appreciate any and all pointers, do's and don'ts and sources of tech information.

I look forward to hearing from you.

Thanks,

Rakesh

Rakesh Madan Senior Database Administrator http://www.cyveillance.com "Minding your business on the Net" [...] 3859 68 16_Re: SQL and REXX19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 1 Feb 2000 15:01:26 -0600602_iso-8859-1 Max, Check out URL below provided by Linda. 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." [...] 3928 16 29_Re: SELECT INTO not selecting12_Jason Hughes20_jason.hughes@TRW.COM30_Tue, 1 Feb 2000 15:02:33 -0600547_- Forgot to add in: This stored procedure was written/tested/debugged on our machine. When we moved it over to the client's machine (did a compile/bind), the SELECT statement no longer worked. The calling program is MS-Access so I know the call to the stored procedure has not changed.

Jason Hughes

================================================ 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. 3945 14 48_Regarding OPERATIVE column in sysibm.syspackage.28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM28_Tue, 1 Feb 2000 13:28:32 PST440_- OPERATIVE=N indicates that a package dependent has been altered or dropped. An auto bind failure with OPERATIVE=N issues reason code 00E30305. Read the explanation for that reason code.

Dan

================================================ 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. 3960 163 11_Re: Locking11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU30_Wed, 2 Feb 2000 08:37:43 +1100573_- Hi Milos, How about some more information? For UDB for Unix, NT etc. try the DB2 command: get snapshot for locks on . This will tell you what locks (and what type of lock) are held at the time.

Regards, Bruce

> -----Original Message----- > From: Milos Flek [SMTP:milos.flek@FSP.CZ] > Sent: Tuesday, February 01, 2000 9:09 PM > To: DB2-L@RYCI.COM > Subject: Locking > > I have the CursorStability isolation level & row locking is set for a > table. This is my sample tbl1 table: > > CREATE TABLE tbl1 (ID INTEGER NOT NULL, COL1 INTEGER ); > > [...] 4124 130 15_Re: DB recovery11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU30_Wed, 2 Feb 2000 08:50:23 +1100449_- Hi Nash, We do this frequently without problems. We ftp the backup file / files to the 2nd machine and run a redirected restore (the names of the directories are different on the 2nd machine).

db2 restore db from /db2backup/xxxx into redirect db2 SET TABLESPACE CONTAINERS FOR 3 USING ( PATH '/testdb/xxxx/yyyy) .... db2 restore db continue [...] 4255 40 111_Central PA RUG - April 5th and 6th - DB2 UDB for V6 & DRDA Classes - $150 each! - Linda Claussen - Register NOW10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Tue, 1 Feb 2000 17:15:44 -0500418_us-ascii Hi all! I wanted to give everyone a head's up on two upcoming all-day DB2 classes.

WHERE - Camp Hill, PA (Near Harrisburg)

WHEN - April 5th & April 6th

WHO - Linda Claussen

WHAT - DB2 UDB for OS/390 Version 6 - Migration and Implementation - April 5th

- DRDA and DB2 Connect - Putting the pieces of the puzzle together. (DRDA, DB2 Connect, TCP/IP and More) - April 6th [...] 4296 46 15_Re: Date format14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Wed, 2 Feb 2000 09:33:18 +1000471_us-ascii Sorry Harvey, I'm missing something. I would have thought that supplying the source code for the ALTDATE routine is just about the maximum possible flexability.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 james.campbell@energyis.com.au -----Original Message----- From: Harvey Wachtel [mailto:babybaby@UAPC.CUNY.EDU] Sent: Wednesday, February 02, 2000 2:48 AM To: DB2-L@RYCI.COM Subject: Re: Date format [...] 4343 48 16_Re: SQL and REXX14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Wed, 2 Feb 2000 09:55:23 +1000566_us-ascii DB2 V5 and later - use IBM's REXX-DB2 interface, see other postings for web address DB2 V4 and before - use Dave Boll's interface, now at http://jupiter.ryci.com/cgi/wa.exe?A2=ind0002&L=db2-l-documents&F=&S=&P= 61

or buy a 3rd party product

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

-----Original Message----- From: Alvarez, Maximiliano [mailto:MAXIMILIANO@IADB.ORG] Sent: Wednesday, February 02, 2000 6:22 AM To: DB2-L@RYCI.COM Subject: SQL and REXX [...] 4392 142 22_Re: bind authorization14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Tue, 1 Feb 2000 18:56:42 -0500444_us-ascii You're right. I didn't give all of the details. The manual actually specifies that the primary authorization ID must have BINDADD privilege, or SYSCTRL or SYSADM authority in order to "BIND a new plan using the default owner or primary authorization ID".







Amy Tam cc: Sent by: DB2 Subject: Re: bind authorization Data Base Discussion List [...] 4535 82 15_Re: Date format14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Tue, 1 Feb 2000 19:03:19 -0500386_us-ascii Sorry, it's I who must have missed something. I had the impression that this was a built-in function that could only be used with the specified list of formats. I've had this frustration with date formats before -- for example when DOS wouldn't let me set my PC's date format to yyyy-mm-dd unless I also changed the currency symbol to Japanese Yen or something like that. [...] 4618 38 45_Re: MODIFY UTILITY FOR Paritioned Tablespaces11_Prince, Lee18_Lee_Prince@BMC.COM30_Tue, 1 Feb 2000 17:57:38 -0600602_ISO-8859-1 Hi all,

The BMC Utility C+/Modify has a highly efficient and high performance mechanism for managing SYSCOPY.

Some of the functions C+/Modify performs are:

-A Highly Improved performance of Modify -A highly granular mechanism of deleting rows from SYSCOPY -Direct update of SYSCOPY rows (modifying SYSCOPY info) -Synchronizing the ICF catalog and SYSCOPY (true image copy existence reporting) -Verify recoverability -Provide wildcard support for improving specification of MODIFY steps -Direct support for SAP R/3 groups -Direct support for Recovery Manager groups [...] 4657 47 28_Deadlock and isolation level12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Wed, 2 Feb 2000 13:42:41 +1300404_us-ascii 'Gentlemen',

As I understand it, be aware that the following statement may not be quite true...

"The CS isolation level will cause a row to be locked as it is fetched, then the lock will be released again for this row when the next row is fetched. That way the row cannot be changed by another application while the cursor is on it (that's why it's called Cursor Stability)." [...] 4705 186 32_Re: Deadlock and isolation level14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Tue, 1 Feb 2000 18:50:43 -0500406_iso-8859-1 IBM's DB2 is closer to the standard than any other DBMS, and IBM sits in a leadership position on those committees now (not necessarily in the past, but now). That being said...there are some family differences in descriptions, and differences between the family members and the standards and DRDA, etc., as is necessary when things have been implemented before the standards matured, yes? [...] 4892 331 15_Re: Date format15_Toine Michielse18_vndobtm@US.IBM.COM30_Sat, 4 Mar 2000 08:22:17 +0000420_iso-8859-1 Harvey,

For some reason my last append in this thread got bounced back....

You must be very pleased with DB2 V6 then....Because is IBM does not give you exactly what you expect from a function....There's a good chance you can implement it using an external UDF. So I guess in your words...the developers left you as much choice as possible...You now have more flexibility then ever before. [...] 5224 140 23_Re: DCLGEN ERROR-SOLVED14_Popy A. Dimoka24_dimokap@INTERAMERICAN.GR30_Wed, 2 Feb 2000 10:33:57 +0200403_iso-8859-1 Thanks to all who replied to my message!

I ran the suggested bind plan : ------------------------------------------------------------ BIND PLAN(DSNEDCL) PKLIST(DSNEDCL.DSNECP68) - ISOLATION(CS) ACTION(REPLACE) RETAIN

------------------------------------------------------------ and the problem was solved!

Best Regards,

Popy A. Dimoka INTERDATA S.A. Athens

5365 277 32_Re: Deadlock and isolation level0_18_mebert@AMADEUS.NET30_Wed, 2 Feb 2000 10:00:49 +0100497_us-ascii Dear List:

please be informed that I am not the final authority on DB2 (or anything) and that you should not take anything I write as the truth (newbie or not). I am reporting on things, and commenting on things, to the best of my (current) knowledge, and often with personal opinions and prejudices. Usually I am not even deeply researching everything I say as I think this would be detrimental to the spirit of a discussion (not to mention that I am as busy as all of you). [...] 5643 26 23_Appl Performance Tuning20_Baskaran Subramaniam27_subramaniam.baskaran@DB.COM30_Wed, 2 Feb 2000 15:42:07 +0530365_us-ascii Hi All

In my site we are preparing a document on performance tuning of SQL & database and best practices (or guidelines) for coding DB2 application programs. In this regard we have collected some information from Craig Mullins' DB2 Developers Guide, Does anyone has prepared such a document, if so could you please mail me a copy of the same.. [...] 5670 399 69_=?iso-8859-1?B?UulmLiA6IFJlOiBEZWFkbG9jayBhbmQgaXNvbGF0aW9uIGxldmVs?=0_26_denzil.coulter@UNICIBLE.CH30_Wed, 2 Feb 2000 11:41:07 +0100309_iso-8859-1 Dr. Michael Ebert,

No apology needed. I enjoy reading yours and everybody elses comments on this list. I think that everyone on the list is in agreement with you its just that from time to time some tend to get carried away with their responses (which in this case simply lacked tact). [...] 6070 21 69_Re: Question 2. CAE exclusive -vs- Share MODE - can if find the user?13_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Wed, 2 Feb 2000 06:20:15 -0600412_- You didn't say whether you are using SNA or TCP/IP. I personally don't recommend LIST DCS APPLICATIONS, because it really does not give you much information about the user that's useful. Have you tried -Display Thread within DB2? That would give you either the IP address or the Userid. If they are doing this so fast that you can't catch them, maybe a monitor such as BMC's Boole and Babbage could help. [...] 6092 27 32_Re: FW: clustered database on NT10_Bob Harbus18_rharbus@CA.IBM.COM30_Wed, 2 Feb 2000 07:09:33 -0600341_- On Tue, 1 Feb 2000 15:50:48 -0500, Rakesh Madan wrote:

>I was wondering if anyone was running a clustered DB2 database on NT. I am >to embark on such an endeavor and would appreciate any and all pointers, >do's and don'ts and sources of tech information. > >I look forward to hearing from you. >Rakesh [...] 6120 115 21_R: bind authorization17_Bartolucci Silvia25_S.Bartolucci@FONDIARIA.IT30_Wed, 2 Feb 2000 14:20:26 +0100664_iso-8859-1 Thank you very much !!!! With the name of the group (FDB2PL01) in the owner keyword it works. Bye

Silvia







> -----Messaggio originale----- > Da: rick creech [SMTP:ykcirc@HOTMAIL.COM] > Inviato: lunedì 31 gennaio 2000 23.14 > A: DB2-L@RYCI.COM > Oggetto: Re: bind authorization > > Use the racf group as the OWNER. > Regards, > Rick Creech > > > >From: Bartolucci Silvia > >Reply-To: DB2 Data Base Discussion List > >To: DB2-L@RYCI.COM > >Subject: bind authorization > >Date: Mon, 31 Jan 2000 09:00:19 +0100 > > > >Hi , i've a problem with bind authorization and RACF [...] 6236 53 27_Re: Appl Performance Tuning14_Arnold, Mark S20_Mark.Arnold@BNSF.COM30_Wed, 2 Feb 2000 07:30:54 -0600767_iso-8859-1 Baskar,

If you are on OS/390 platform, you might also want to gather material from Gabrielle Wiorkowski's "DB2 for OS/390 Development For Performance". Check her web site: www.GabrielleDB2.com.



Mark Arnold BNSF Railway

-----Original Message----- From: Baskaran Subramaniam [mailto:subramaniam.baskaran@DB.COM] Sent: Wednesday, February 02, 2000 4:12 AM To: DB2-L@RYCI.COM Subject: Appl Performance Tuning



Hi All

In my site we are preparing a document on performance tuning of SQL & database and best practices (or guidelines) for coding DB2 application programs. In this regard we have collected some information from Craig Mullins' DB2 Developers Guide, Does anyone has prepared such a document, if [...] 6290 54 52_Re: Regarding OPERATIVE column in sysibm.syspackage.14_Grainger, Phil21_Phil.Grainger@CAI.COM30_Wed, 2 Feb 2000 13:45:43 -0000323_- DB2 switches VALID from Y to N when you do an alter (or drop) which invalidates the current plan. At next execution, DB2 tries a dynamic rebind. If THIS fails (maybe you dropped the table!) then OPERATIVE gets switched from Y to N. Now, no more dynamic rebinds will be attempted and you have to do an explicit BIND. [...] 6345 18 26_DB2 Resource type :WAITECB15_Brian Zamborski18_bzamborski@USS.COM30_Wed, 2 Feb 2000 08:48:26 -0500343_us-ascii In one of our production CICS regions, we have hit MAXTASKS of 60 on one occasion. While DB2 did not appear to be under stress, the tasks which backed up in CICS showed that they were waiting for a DB2 resource - DB2 resource type : WAITECB. Can anyone point us to documentation that will explain what WAITECB is indicative of ? [...] 6364 34 22_BEWARE DEFAULT BLKSIZE12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM30_Wed, 2 Feb 2000 05:49:59 -0800532_us-ascii DB2'ers, Some of you may be interested in this. Our DB2 Ver. 5.1 active logs are very large (3889) cyls on 3390 mod-9. In our DR testing, using the default blksize of 28672 for the archives, we were unable to download a full archive to a empty 3390-mod-9. Turns out the 28672 blksize allows 7 CIs per track. Blocking with the old default 24576 allows 12 CI's per track. So you are losing close to 50% of space using the new default. If you try to copy the archive tape and reblock it then at DB2 startup you recieve a [...] 6399 56 27_Re: Appl Performance Tuning11_John Wynton21_jwynton@THEMISINC.COM30_Wed, 2 Feb 2000 08:48:26 -0500784_windows-1252 Baskar:

How about training?

Regards,

John Wynton Themis, Inc. Specialists in DB2, MQ and CICS Training 1-800-756-3000; 908-233-8900 (Int'l); 908-233-7401 (Fax) http://www.themisinc.com



-----Original Message----- From: Baskaran Subramaniam [mailto:subramaniam.baskaran@DB.COM] Sent: Wednesday, February 02, 2000 5:12 AM To: DB2-L@RYCI.COM Subject: Appl Performance Tuning



Hi All

In my site we are preparing a document on performance tuning of SQL & database and best practices (or guidelines) for coding DB2 application programs. In this regard we have collected some information from Craig Mullins' DB2 Developers Guide, Does anyone has prepared such a document, if so could you please mail me a copy of the [...] 6456 78 27_Re: Appl Performance Tuning20_Baskaran Subramaniam27_subramaniam.baskaran@DB.COM30_Wed, 2 Feb 2000 20:02:59 +0530684_us-ascii Hi John

Good idea.... But we are in India far away from you..............(but not so far away thro net)... :-)

Baskar



Please respond to DB2-L@ryci.com

To: cc: Subject: Re: Appl Performance Tuning







Baskar:

How about training?

Regards,

John Wynton Themis, Inc. Specialists in DB2, MQ and CICS Training 1-800-756-3000; 908-233-8900 (Int'l); 908-233-7401 (Fax) http://www.themisinc.com



-----Original Message----- From: Baskaran Subramaniam [mailto:subramaniam.baskaran@DB.COM] Sent: Wednesday, February 02, 2000 5:12 AM To: DB2-L@RYCI.COM Subject: Appl Performance Tuning [...] 6535 53 26_Re: BEWARE DEFAULT BLKSIZE17_Snoeyenbos, Craig35_Craig.Snoeyenbos@STARWOODHOTELS.COM30_Wed, 2 Feb 2000 09:36:12 -0500418_iso-8859-1 Mike, Thanks for the heads-up, some definite "opportunities" here for those of us that mix tape and disk.

Would it work if you reblocked to 24576 going tape to disk and re-assembled ZPARMS with that as the blocksize?

Craig Snoeyenbos Starwood Hotels all usual and most unusual disclaimers apply

-----Original Message----- From: Mike O'Brien [mailto:michael_obrien_48127@YAHOO.COM] [...] 6589 77 26_Re: BEWARE DEFAULT BLKSIZE12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM30_Wed, 2 Feb 2000 06:46:10 -0800595_us-ascii No, the blksize refers to the archive log blksize, not the size of the active logs. They are just defined as vsam linear datasets, no real blksize shows. --- "Snoeyenbos, Craig" wrote: > Mike, > Thanks for the heads-up, some definite > "opportunities" here for those of us > that mix tape and disk. > > Would it work if you reblocked to 24576 going tape > to disk and re-assembled > ZPARMS with that as the blocksize? > > Craig Snoeyenbos > Starwood Hotels > all usual and most unusual disclaimers apply > > -----Original Message----- > From: [...] 6667 89 26_Re: BEWARE DEFAULT BLKSIZE17_Snoeyenbos, Craig35_Craig.Snoeyenbos@STARWOODHOTELS.COM30_Wed, 2 Feb 2000 10:05:53 -0500656_iso-8859-1 I was thinking of reproing the archives to disk as archives, leaving the new active logs empty. Craig

-----Original Message----- From: Mike O'Brien [mailto:michael_obrien_48127@YAHOO.COM]

No, the blksize refers to the archive log blksize, not the size of the active logs. They are just defined as vsam linear datasets, no real blksize shows. --- "Snoeyenbos, Craig" wrote: > Mike, > Thanks for the heads-up, some definite > "opportunities" here for those of us > that mix tape and disk. > > Would it work if you reblocked to 24576 going tape > to disk and re-assembled > ZPARMS with that [...] 6757 89 26_Re: BEWARE DEFAULT BLKSIZE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 09:10:23 -0600582_iso-8859-1 Craig, Beware of (3rd party product?) ICEGENER. If this is called instead of IEBGENER, and if you leave off DCB info for output dataset, it will optimize blocking to the output device type and possibly change blocking of archive on you. I've simply adopted a policy of always coding DCB info for the output. I got caught on this because a sysprog in a prior life set up IEBGENER to ALIAS to ICEGENER and the @#$%^&@ didn't tell anyone. Also, note, until V6 fixes this, that DB2 doesn't like multi-volume tape archives, therefore, size your archives on what will fit [...] 6847 119 26_Re: BEWARE DEFAULT BLKSIZE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 09:21:24 -0600617_iso-8859-1 Craig, As follow-up. When we got hit with the ICEGENER problem I described earlier, I simply IEBGENERed, via the "sneaky" ICEGENER ALIAS, the archive tapes to DASD specifying the correct blocksize.

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 [...] 6967 115 26_Re: BEWARE DEFAULT BLKSIZE11_rick creech18_ykcirc@HOTMAIL.COM28_Wed, 2 Feb 2000 07:22:41 PST562_- FYI, Info on sizing the active logs if you anticipate reproing the archive logs back to them: from the db2 Admin Guide:

We recommend that the number of records for the active log be divisible by the blocking factor of the archive log (DASD or tape). To determine the blocking factor of the archive log, divide the value specifed on the BLOCK SIZE field of installation panel DSNTIPA by 4096 (that is, BLOCK SIZE / 4096). Then modify the DSNTIJIN installation job so that the number of records in the DEFINE CLUSTER for the active log data set is a [...] 7083 96 26_Re: BEWARE DEFAULT BLKSIZE12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM28_Wed, 2 Feb 2000 07:24:41 PST650_- I did the changes and saved a lot space on disk. Thank a lot for your advise.

Harbry.

>From: Mike O'Brien >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: BEWARE DEFAULT BLKSIZE >Date: Wed, 2 Feb 2000 06:46:10 -0800 > >No, the blksize refers to the archive log blksize, not >the size of the active logs. They are just defined as >vsam linear datasets, no real blksize shows. >--- "Snoeyenbos, Craig" > wrote: > > Mike, > > Thanks for the heads-up, some definite > > "opportunities" here for those of us > > that mix [...] 7180 28 11_DB2 Classes13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG30_Wed, 2 Feb 2000 09:22:26 -0600380_US-ASCII Hello, I was wondering where you all think the best classes are offered for beginning DB2 sysprogs. I am currently checking out IBM and TechKnowledge. Are there others? One of my fellow sysprogs told me to check and see if Platinum still had their excellent education...but going to there site..I am sent to CA's and they dont appear to offer any specific training. [...] 7209 50 15_Re: DB2 Classes16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Wed, 2 Feb 2000 10:43:15 -0500761_us-ascii http://www.protechpts.com/ http://www.themisinc.com/ http://www.learningtree.com/









Mike Lawrence on 02/02/2000 10:22:26 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: DB2 Classes







Hello, I was wondering where you all think the best classes are offered for beginning DB2 sysprogs. I am currently checking out IBM and TechKnowledge. Are there others? One of my fellow sysprogs told me to check and see if Platinum still had their excellent education...but going to there site..I am sent to CA's and they dont appear to offer any specific training. [...] 7260 56 15_Re: DB2 Classes19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 09:45:00 -0600600_iso-8859-1 Mike, If you've done MVS, oops, OS/390 installs, you'll have no problem installing DB2. Its the customization and DSNTINST CLIST that will be a challenge. Go thru the OS/390 Install Guide, this will give you an idea where you'll need training.

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 [...] 7317 126 26_Re: BEWARE DEFAULT BLKSIZE12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM30_Wed, 2 Feb 2000 07:24:35 -0800537_us-ascii Craig, I don't think my original note was clear enough. I am talking about downloading the archive tape to a disk as a archive, not into the logs. The archive tape is created with the default blksize, the logs do not have it. The point is, with smaller logs, the archive will still be created with default blksize, but it will now fit on a 3390 disk. Disk space will still be wasted but it will fit. Going to the 24576 blksize will make better use of the disk when you download the archive, but makes less efficient use of [...] 7444 47 30_Re: DB2 Resource type :WAITECB19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 09:47:15 -0600578_iso-8859-1 Brian, Really reaching here, could this be related to the POST and WAIT ECB Macros? Which might make sense in the situation you describe. Didn't get a hit when I searched MSG's and codes for "wait" and "waitecb" in V5 manual. You have msg number?

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 [...] 7492 17 30_Re: DB2 Resource type :WAITECB12_Dallas Wacha18_dwacha@NEONSYS.COM30_Wed, 2 Feb 2000 09:52:11 -0600427_us-ascii Dallas Wacha@NEON 02/02/2000 09:52 AM

I would start by determining what it is you have specified in for RCT THRDMAX value. This could be the origin of the wait activity.

================================================ 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. 7510 140 26_Re: BEWARE DEFAULT BLKSIZE17_Snoeyenbos, Craig35_Craig.Snoeyenbos@STARWOODHOTELS.COM30_Wed, 2 Feb 2000 11:03:24 -0500621_iso-8859-1 Mike, I was trying to think of a bailout for the DR exercise when you found the problem. You, and others, are quite clear on the considerations for setting things up to avoid future problems. Thanks, Craig

-----Original Message----- From: Mike O'Brien [mailto:michael_obrien_48127@YAHOO.COM] Craig, I don't think my original note was clear enough. I am talking about downloading the archive tape to a disk as a archive, not into the logs. The archive tape is created with the default blksize, the logs do not have it. The point is, with smaller logs, the archive will still be created with default [...] 7651 129 26_Re: BEWARE DEFAULT BLKSIZE14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Wed, 2 Feb 2000 11:18:11 -0500396_us-ascii Is this a bug in DB2? Accepted coding practice in MVS is for programs never to specify blocksize on input datasets but to accept whatever is in the JFCB or file label (DSCB or tape label), in that order. The only exception I have encountered until now has been the pre-SMS linkage editor, which has some peculiar restrictions on the blocksize of its card-image control file SYSLIN. [...] 7781 37 30_Catalogue/rectory in extents ?19_Briggs, N. - Neil -25_N.Briggs@CANON-EUROPA.COM30_Wed, 2 Feb 2000 17:16:36 +0100367_iso-8859-1 Dear All

I have just checked the listcat information for the production catalogue and directory and have noticed that there are a number of tablespaces and indexspaces that have gone into extents.

I was wondering what would be the best approach to redefining the tablespaces and indexspaces so that all data resides in the first extent. [...] 7819 71 34_Re: Catalogue/rectory in extents ?15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Wed, 2 Feb 2000 11:27:14 -0500612_iso-8859-1 Dr. Michael Ebert has recently published DB2 newsgroup postings providing procedures regarding DB2 directory and catalog reorg and JCL setups that could be used as samples in one's environment to do what you are needing. Check the DB2 archives for this information. I found it very useful in our environment and have successfully reorganized the production environment without problems. It has cleaned up all the extents and I was able to see a slight overall improvement in processing times as well. His reasons for performing it was he had run out of space for a particular table if I remember [...] 7891 161 26_Re: BEWARE DEFAULT BLKSIZE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 10:34:13 -0600605_iso-8859-1 Harvey, Please don't be alarmed. This thread is N O T about any "bugs" within DB2. We're discussing utility programs used to handle archive logs once DB2 has created them and preserving or initially choosing the correct blocksize used by DB2 for archiving.

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 [...] 8053 201 26_Re: BEWARE DEFAULT BLKSIZE14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Wed, 2 Feb 2000 11:45:58 -0500499_us-ascii I realize that you're discussing the copying of archives, but I got the impression that DB2 recovery was unable to read an archive file whose blocksize is larger than the default set in ZPARMS. Did I misunderstand that? If not, it strikes me as unusual and badly designed, if not actually "buggy". Most programs don't care what the input blocksize is, as long as it's consistent with the LRECL and RECFM and as long as the address space has sufficient virtual storage for the buffers. [...] 8255 233 26_Re: BEWARE DEFAULT BLKSIZE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 2 Feb 2000 11:02:44 -0600537_iso-8859-1 Harvey, Since day one of DB2 back on MVS we have not been able to change the archive blocksize from that in ZPARM. I don't know if this changes in V6 or beyond. If you consider this "buggy", I can see how you could. It might help our understanding if somebody can tell us the reason(s) why? 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 [...] 8489 33 34_Default Isolation on Dynamic Binds18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM30_Wed, 2 Feb 2000 12:13:52 -0500329_iso-8859-1 We are currently using DB2 Connect against our DB2/OS390 mainframe. We are currently running DB2 V5.0. Can anyone answer the following question for me about the Dynamic Bind:

>> Is there any way that we can make the queries coming through DDF from DB2 Connect be bound with Isolation CS instead of RR?" << [...] 8523 28 23_SYSLOG message trapping13_Jackson, Jeff20_Jeff.Jackson@FMR.COM30_Wed, 2 Feb 2000 12:12:46 -0500429_iso-8859-1 I've been tasked with coming up with a list of messages produced by the various DB2 address spaces which should be trapped and either flagged or have action taken upon. Rather than reviewing the "messages and code" manual, I was hoping someone on the list might be doing something similar and would be willing to share their message list for use as a starting point. Any help or suggestions would be appreciated. [...] 8552 369 57_Re: R(unknown chars)f. : Re: Deadlock and isolation level11_Thomas Bird21_tbird@TJBVENTURES.COM30_Wed, 2 Feb 2000 09:59:16 -0800733_us-ascii I agree. The material was useful and informative.

Regards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.com





|--------+--------------------------> | | denzil.coulter@U| | | NICIBLE.CH | | | | | | 02/02/00 02:41 | | | AM | | | Please respond | | | to DB2 Data Base| | | Discussion List | | | | |--------+--------------------------> >-----------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Thomas J Bird/IDI) | | Subject: R?f. : Re: Deadlock and isolation level | >-----------------------------------------------------------------------| [...] 8922 58 34_Re: Catalogue/rectory in extents ?14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Wed, 2 Feb 2000 11:26:10 -0500338_iso-8859-1 It would be nice if he could put the whole Catalog REORG and JCL thing together in a single text file and post it to DB2-L-Documents so anyone who needs it could download it and it would be there for everyone. After all, it is quite long.

Procedures for posting to DB2-L-Documents are on http://www.ryci.com/db2-l. [...] 8981 24 22_Improved DB2 Utilities9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Wed, 2 Feb 2000 10:17:47 -0700384_iso-8859-1 Last year our IBM represenative said that IBM was planning to upgrade the DB2 V5 unload so it would read from Image Copies. Now that IBM is offering a product known as DB2 High Performance Unload, has anybody received any upgrades (PTF's, APARS etc.) to Version 5 to allow the DSNTIAUL unload to read from Image Copies without having to invest extra mony for DB2 HPU? [...] 9006 42 14_COBOL problem.0_19_csutfin@AMSOUTH.COM30_Wed, 2 Feb 2000 11:34:55 -0600416_- Hopefully someone on the list can help me.

The evironment is DB2 V4.1 MVS 5.2.2. We have a batch DB2 program that runs just fine in our DB2 test environment. Standard no-nonsense COBOL for MVS program.

In order to put the program into production, it is moved (IEBGENER copy) to the production libraries, both load module and DBRM and then the production plan is bound using the new DBRM module. [...] 9049 62 38_Re: Default Isolation on Dynamic Binds13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Wed, 2 Feb 2000 11:36:13 -0600686_US-ASCII Hello Stephen, You can rebind the packages

DSN S(XXXX) REBIND PACKAGE(NULLID.SQLABBxx) ISOLATION(CS) - DEGREE(x) CURRENTDATA(xx) VALIDATE(RUN) OWNER(xxxxxxxx) - DEFER(PREPARE)

The packages default to the NULLID collection unless you have changed this. You can find the package names in syspackage or in the DB2 Connect servers x:\sqllib\bnd directory. HTH Kurt











>>> "Whittaker, Stephen" 02/02/00 11:13AM >>> We are currently using DB2 Connect against our DB2/OS390 mainframe. We are currently running DB2 V5.0. Can anyone answer the following question for me about the Dynamic Bind: [...] 9112 28 13_Natural X DB26_monica25_monica@POINT.BANEB.COM.BR30_Wed, 2 Feb 2000 15:50:05 -0200368_iso-8859-1 Hi, all,

Does anyone of you have expertise using Natural (Adabas) to access DB2 ? We're are experiencing a problem, and we cannot implement a Natural Static Plan to access DB2. Every time any sqlcode <> 0 occurs, our application returns a -805 error. Everyhing goes OK with dinamoc plan., but our access to DB2 catalog becomes a caos sometimes. [...] 9141 48 26_Re: Improved DB2 Utilities11_Kwan, James18_James_Kwan@BMC.COM30_Wed, 2 Feb 2000 11:55:45 -0600406_ISO-8859-1 I just don't understand why would IBM improve their existing utilities while they can sell their HP utilities. What I think what IBM will do in the near future is that they will enter utility market with high speed utility (probably those from InfoTel). Of course these utilities will not be free. Just image what they are going to do with those utilities you are getting for 'free' today. [...] 9190 69 18_Re: COBOL problem.11_Jim Keohane19_jimkeo@LOCKSTAR.COM30_Wed, 2 Feb 2000 13:01:15 -0800571_us-ascii If you are copying a load module from one pds to another using IEBGENER that is a problem. Use IEBCOPY or use ISPF 3.3. IEBGENER doesn't copy directory info for members in PDS. - Jim Keohane

csutfin@AMSOUTH.COM wrote:

> Hopefully someone on the list can help me. > > The evironment is DB2 V4.1 MVS 5.2.2. > We have a batch DB2 program that runs just fine in our DB2 test > environment. Standard no-nonsense COBOL for MVS program. > > In order to put the program into production, it is moved (IEBGENER copy) to > the production libraries, both [...] 9260 261 26_Re: BEWARE DEFAULT BLKSIZE22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Wed, 2 Feb 2000 12:58:22 -0500627_iso-8859-1 Yes, I'd like to know why as well.



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

> ---------- > From: DAVIS, RICK (SBCSI)[SMTP:RD8246@MOMAIL.SBC.COM] > Sent: Wednesday, February 02, 2000 12:02 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] BEWARE DEFAULT BLKSIZE > > Harvey, > Since day one of DB2 back on MVS we have not been able to change > the > archive blocksize from that in ZPARM. I don't know if this changes in V6 > or > beyond. If you consider this "buggy", I can see how you could. > It might help our understanding if somebody can tell us [...] 9522 54 38_Re: Default Isolation on Dynamic Binds14_McCombs, Terry20_tmccomb@SEDGWICK.GOV30_Wed, 2 Feb 2000 12:02:12 -0600497_iso-8859-1 On each workstation, in the client configuration assistant CLI/ODBC settings, advanced, for the database, set isolation level (TXNISOLATION) to CS. I don't bind the RR packages, which forces the users to do this in order to use DB2 Connect.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Whittaker, Stephen Sent: Wednesday, February 02, 2000 11:14 AM To: DB2-L@RYCI.COM Subject: Default Isolation on Dynamic Binds [...] 9577 57 27_Re: SYSLOG message trapping17_Bruzdzinski, Mike24_MIKE.BRUZDZINSKI@SSA.GOV30_Wed, 2 Feb 2000 13:02:16 -0500451_iso-8859-1 Check with the folks at Maryland Department of Transportation Information Center.

I believe John Yost (410-768-7606) is still the manager of the DB2 section and Steve Gartside was responsible for the code. This was about 3 years ago. I was part of that section and seem to remember Steve wrote a program to trap the errors and create a warning database for us to check. I was in development so it is probably in production now! [...] 9635 44 17_Re: Natural X DB213_Toppins, Mike19_MToppins@UNIPAC.COM30_Wed, 2 Feb 2000 11:12:37 -0700429_iso-8859-1 It sounds like you've got an ON ERROR routine coded that's calling a static module that isn't "in sync" with the rest of the modules. I've also had this situation occur because the NATURAL buffer pool was not flushed after the module was bound.

-----Original Message----- From: monica [mailto:monica@POINT.BANEB.COM.BR] Sent: Wednesday, February 02, 2000 10:50 AM To: DB2-L@RYCI.COM Subject: Natural X DB2 [...] 9680 55 27_Re: SYSLOG message trapping12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM30_Wed, 2 Feb 2000 09:51:27 -0800419_us-ascii Jeff, all I can say is "good luck". Management at my last place wanted the same thing. All I did was show them the message/codes manual with the thousands of possiblities and they decided other work was more important. I realize many messages require no action but an almost impossible task to go through them all to determine what you want. Also, consider maintenance for new messages, new version, etc. [...] 9736 26 14_Codepage error10_Nash Gupta19_nashgupt@US.IBM.COM30_Wed, 2 Feb 2000 12:12:12 -0600452_- Platform: DB2 5.2 on AIX\

Problem: SQL2548N Database codepage "0" indicated in the backup image differs from that of the current on-disk database codepage "819". The Restore operation has failed

I resolved another problem where codepage was "850" by creating a database with the same codepage .. That worked..

This is for a different database whose codepage I don't remember now .. I dont' understand what codepage "0" is ? [...] 9763 217 42_Re: TIMESTAMP as Non-intelligent ID for PK15_Philip, Sibimon19_PhilipS@SEALAND.COM30_Wed, 2 Feb 2000 13:42:41 -0500297_iso-8859-1 As Eric said it may be better to you integer instead of a timestamp as the non-intelligent primary key. When we consider couple of millions of rows in the table, we can save lot of space and also processing. Any program which does a index scan or table space scan will run faster. [...] 9981 33 17_Re: Natural X DB29_Tony Mann19_tmann@UTILICORP.COM30_Wed, 2 Feb 2000 13:12:12 -0600399_- Monica,

check the setting of your Natural option STATDYN. This controls Natural *automatically* retrying a static sql statement through the dynamic package if an sqlcode is detected.

It can be set to (from memory here) ALWAYS, NEVER or SPECIAL. ALWAYS = any non zero sqlcode triggers a retry through dynamic access NEVER = no retry through dynamic SPECIAL = only for -805, -818 [...] 10015 113 28_Re: DB2 and ESS dasd WARNING0_29_Joseph_Esposito@PUTNAMINV.COM30_Wed, 2 Feb 2000 15:15:34 -0500595_us-ascii Take a look at apar OW41795: ABEND0C4 OR ABENDC0D IOSVSSCQ OVERLAY IOSB SHARK D/T2105







From: csutfin@AMSOUTH.COM on 01/31/2000 05:09 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Joseph Esposito/FRA/PutnamInv)





Subject: Re: DB2 and ESS dasd WARNING













John/Leslie We also have Shark. I am checking on the microcode level. We did an upgrade on it about 10 days ago. Will let you both know. [...] 10129 21 16_Re: RCT Question12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Thu, 3 Feb 2000 09:37:20 +1300507_us-ascii Tom,

You'll get 5 protected threads per AOR. We have two CICS regions here that use the same RCT definition. Each takes a copy, effectively. So transaction definitions that are only used by one region are wasted for the other.



Raymond

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10151 32 15_Re: Date format12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Thu, 3 Feb 2000 09:25:35 +1300393_us-ascii Esteemed colleagues,

In the unlikely event one of the 34-ish date formats available in DB2 V6 (OS/390) isn't what you want, IBM have provided - and have always provided - the ability for users to code their own routines to display dates any way they like. Not sure how you do it - ISO has always worked for me - but it has something to do with the LOCAL date/time format. [...] 10184 77 69_Re: Question 2. CAE exclusive -vs- Share MODE - can if find the user?0_14_dcreed@CSC.COM30_Wed, 2 Feb 2000 15:43:18 -0600542_us-ascii hmmm. Actually I think I spoke too soon on the Exclusive aspect. I don't even know if it is possible to do this, since I tried it and got an error. So meantime I retract the question.

The list dcs applications did get much better in V5.2 and on with the extended attribute. I was surprised to see that it gave the machine name of your NT/95 box which now helps to identify users in the client login id. Used to be, you were stuck with the authorization id, which was written at the application level to avoid getting new [...] 10262 26 31_ASNADONE exit in DataPropagator10_Moe, Sammi24_Sammi.Moe@PACIFICORP.COM30_Wed, 2 Feb 2000 14:23:25 -0800404_iso-8859-1 Dear Colleagues:

We are starting to use the Dataprop Apply and Capture as part of our data-warehouse project (DB2 V5.1 and Windows NT to UNIX). Has anyone else implemented dataprop, and if so, do you have any experiences which would be helpful? We are especially looking for information on the ASNADONE exit and whether anyone was using it (examples would be greatly appreciated). [...] 10289 73 27_Re: SYSLOG message trapping10_Park, Stan17_SPark@STATE.NM.US30_Wed, 2 Feb 2000 15:39:58 -0700312_- Here we trap all "severe" DB2 errors and send them to various people. We have the Boole/BMC MainView for DB2 OS390 Performance Monitor and it has this facility included. Right now we are accepting their definition of what "severe" means (mostly a DB2 SYSTEM problem as opposed to an application problem). [...] 10363 232 26_Re: BEWARE DEFAULT BLKSIZE10_Leo Flores23_leoflores@EARTHLINK.NET30_Wed, 2 Feb 2000 15:12:00 -0800485_us-ascii Hi, I'll take a shoot at this, but I'm only guessing.

Good Coding Practice is to always READ input records into a WORK AREA for processing. This makes it easier to DEBUG in case of ABEND's and also for viewing data during program testing. This also helps from going outside of the Program Storage Area(PSA) in processing the input record(we call these OC 4's). However, this takes additional CPU cycles to move the data once it is in the BUFFER to that WORK AREA. [...] 10596 125 27_Re: SYSLOG message trapping0_19_Tim.Lowe@STPAUL.COM30_Wed, 2 Feb 2000 17:45:54 -0600345_us-ascii We trap a lot of errors automatically here too. I do hate to say it, but sometimes, it seems as though the automation can create a bigger problem than the problem that was being trapped. (How would you like to come in on monday to hundreds of automatically generated email messages about an error that kept occurring all weekend?) [...] 10722 32 31_Off topic post - Mail Trailers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Thu, 3 Feb 2000 09:44:01 +1000509_us-ascii Hi Folks, Sorry for the off topic post, but I just wanted to say that I think some of the e-mail trailers are getting out of hand in length. These days for some of the e-mails if I want to scroll down a post that contains a number of other posts to get the hang of the thread it's starting to take forever. Do we really need 2 pages on the end of our e-mails? I admit that mine is probably too short...and half a dozen lines etc... whatever... is great: but I think when they are getting over 1 [...] 10755 52 27_Re: Appl Performance Tuning23_AITHA, BHEEMSEN (SBCSI)23_B4AITHA@MSG.PACBELL.COM30_Wed, 2 Feb 2000 15:56:02 -0800732_iso-8859-1 Baskar,

Can you please send me a softcopy of yours, once you're done with successfully documenting it.

Thanx.. b4aitha@msg.pacbell.com



-----Original Message----- From: Baskaran Subramaniam [mailto:subramaniam.baskaran@DB.COM] Sent: Wednesday, February 02, 2000 2:12 AM To: DB2-L@RYCI.COM Subject: Appl Performance Tuning



Hi All

In my site we are preparing a document on performance tuning of SQL & database and best practices (or guidelines) for coding DB2 application programs. In this regard we have collected some information from Craig Mullins' DB2 Developers Guide, Does anyone has prepared such a document, if so could you please mail me a copy of the same.. [...] 10808 79 29_Reply to BEWARE DEFAULT BLKSI16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU30_Wed, 2 Feb 2000 16:04:00 -0800752_- *** Original Author: MICHAEL_OBR@YAHOO - Brien; 02/02/00 05:51

>Received: from mshieldsrv.medctr.ucla.edu (149.142.194.75) by > MVS.MEDCTR.UCLA.EDU > (IBM MVS SMTP V3R2) with TCP; Wed, 02 Feb 00 05:51:14 PDT >Return-Path: >Received: from ( 208.225.214.81 ) by mshieldsrv.medctr.ucla.edu with SMTP > (MailShield v1.5); Wed, 02 Feb 2000 05:53:53 -0000 >Received: from jupiter (jupiter.ryci.com) by ryci.com (LSMTP for Windows NT > v1.1b) with SMTP id <0.0008AB65@ryci.com>; Wed, 2 Feb 2000 7:42:47 -0600 >Received: from RYCI.COM by RYCI.COM (LISTSERV-TCP/IP release 1.8d) with spool > id 7499 for DB2-L@RYCI.COM; Wed, 2 Feb 2000 07:42:47 -0600 >Received: from web1803.mail.yahoo.com by ryci.com (LSMTP for Windows NT [...] 10888 36 26_Re: Improved DB2 Utilities12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 2 Feb 2000 19:09:29 -0600319_- I'm sorry, but I think your IBM representative was not correct. We made a number of improvements in Version 5 utilities, with more in the service stream, like REORG UNLOAD EXTERNAL. Perhaps that was the source of the confusion, but unload from an image copy was not part of any Version 5 work that I know about. [...] 10925 54 26_Re: Improved DB2 Utilities24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Wed, 2 Feb 2000 17:23:03 -0800584_- Does the new utility allow for an unload from a concurrent copy type of image copy?

> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Wednesday, February 02, 2000 5:09 PM > To: DB2-L@RYCI.COM > Subject: Re: Improved DB2 Utilities > > I'm sorry, but I think your IBM representative was not correct. We made a > number of improvements in Version 5 utilities, with more in the service > stream, like REORG UNLOAD EXTERNAL. Perhaps that was the source of the > confusion, but unload from an image copy was not part of any Version 5 > work [...] 10980 118 26_Re: BEWARE DEFAULT BLKSIZE11_Michael Lim24_TSMLIM@HKCLEARING.COM.HK30_Thu, 3 Feb 2000 09:57:29 +0800630_US-ASCII Rick,

Can you please elaborate on your comment that 'DB2 doesn't like multi-volume tape archives'? The archive logs at my shop goes into multiple tapes and I haven't noticed any problems.

Michael

>>> "DAVIS, RICK (SBCSI)" 2/2/00 11:10 PM >>> Craig, Beware of (3rd party product?) ICEGENER. If this is called instead of IEBGENER, and if you leave off DCB info for output dataset, it will optimize blocking to the output device type and possibly change blocking of archive on you. I've simply adopted a policy of always coding DCB info for the output. I got caught on this [...] 11099 24 53_Checkpoint/Restart Packages w/ Extended QSAM support?11_Suresh Sane21_data_arch@HOTMAIL.COM28_Wed, 2 Feb 2000 23:11:35 CST401_- I need some help from anyone who has evaluated and/or is using any vendor package for application checkpoint/restart.

I evaluated and implemented Sysdata's Quickstart a few years ago. The client now has a need for Extended QSAM support which apparently is not available with Quickstart. Are there vendor packages that do provide this support? How well do they work? Appreciate any help. [...] 11124 62 57_Re: Checkpoint/Restart Packages w/ Extended QSAM support?15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU30_Thu, 3 Feb 2000 15:37:41 +1000578_us-ascii Check out Relational Architects International - they have a product called Smart/RESTART. http://www.relarc.com









Suresh Sane on 03/02/2000 15:11:35

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Shaun Z Lombard/CorpServ/qdot/au) Subject: Checkpoint/Restart Packages w/ Extended QSAM support?







I need some help from anyone who has evaluated and/or is using any vendor package for application checkpoint/restart. [...] 11187 61 27_Re: SYSLOG message trapping22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 3 Feb 2000 06:21:13 +0000496_ISO-8859-1 Jeff I have written a rexx that once a day produces a print of the ACTIVE MSTR address space using batch SDSF and then strips things out , speaks to DB2 about certain SAP things and then reports to me any things that it does not have in a list of "ignore" DSN messages for.. it does this each day based on a date calculated on the fly .. i.e all problems that are three days or newer from the MSTR address space (just in case somebody does not check the report on a daily basis). [...] 11249 110 27_Re: SYSLOG message trapping9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Thu, 3 Feb 2000 08:48:10 +0000573_us-ascii I agree with Mike in that it is almost impossible to produce (and keep current) a list of messages to trap. I think it is far easier, and safer, to produce an exclusion message list and then report everything else. We use a REXX exec (similar to that of Leslie) which runs every hour to check and report by email, the last hours 'unexcluded' messages. Running every hour is a compromise......it means that you have a report reasonably close to when the message was produced and also means that you are not flooded with alerts for every individual message (we [...] 11360 72 52_Re: Regarding OPERATIVE column in sysibm.syspackage.10_Leo Flores23_leoflores@EARTHLINK.NET30_Thu, 3 Feb 2000 01:16:13 -0800575_us-ascii This may be what caused me a problem when I was altering some production indexes to type 2 and then recovering the indexes to change them to type 2. I did an alter index, then a recover index(no rebinds) one index at a time during a quiet period(about midnight).

Out of several thousand indexes altered only 1 plan that was not auto rebound successfully caused one online program to abend for about 6 minutes before I received TRNX abend messages on my terminal and did an explicit rebind. I noticed that the plan was marked VALID(NO) and OPERATIVE(NO). [...] 11433 67 34_Re: Catalogue/rectory in extents ?13_Aratos Sicyon25_aratos_sicyon@HOTMAIL.COM28_Thu, 3 Feb 2000 11:32:32 EET322_- I think Michael is making more friends than you are, Richard.

>It would be nice if he could put the whole Catalog REORG and JCL >thing >together in a single text file and post it to DB2-L-Documents >so anyone >who needs it could download it and it would be there for >everyone. After >all, it is quite long. [...] 11501 151 34_Re: Catalogue/rectory in extents ?0_18_mebert@AMADEUS.NET30_Thu, 3 Feb 2000 11:06:38 +0100286_us-ascii I had to raise my eyebrow at that (guess the shape of my ears).... in fact, I had planned to do this all along. Thanks to RY for providing the necessary kick. I have submitted the stuff one hour ago to the DB2-L-Documents; let's see how long the publishing process takes. [...] 11653 81 19_DCLGEN ERROR-SOLVED14_Popy A. Dimoka24_dimokap@INTERAMERICAN.GR30_Thu, 3 Feb 2000 13:20:42 +0200432_iso-8859-7 (This is a repost...)

Thanks to all who replied to my message!

I ran the suggested bind plan : ------------------------------------------------------------ BIND PLAN(DSNEDCL) PKLIST(DSNEDCL.DSNECP68) - ISOLATION(CS) ACTION(REPLACE) RETAIN

------------------------------------------------------------ and the problem was solved!

Best Regards,

Popy A. Dimoka INTERDATA S.A. Athens

11735 216 34_Re: Catalogue/rectory in extents ?13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 3 Feb 2000 08:09:39 -0500501_iso-8859-1 Oh dear! The publishing process involves human intervention (mine) but I just approved the submission a half hour ago so it should be on the web site (http://jupiter.ryci.com/archives/db2-l-documents.html ) by now. Thanks again Dr. Ebert.

-----Original Message----- From: mebert@AMADEUS.NET [SMTP:mebert@AMADEUS.NET] Sent: Thursday, February 03, 2000 5:07 AM To: DB2-L@RYCI.COM Subject: Re: Catalogue/rectory in extents ? [...] 11952 10 28_Re: DB2 and ESS dasd WARNING11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Thu, 3 Feb 2000 07:03:26 -0600290_- OW41795 is not the same problem I encountered.

================================================ 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. 11963 20 18_Checkpoint/Restart0_27_sue.cloutier@CUNAMUTUAL.COM30_Thu, 3 Feb 2000 07:15:50 -0600323_us-ascii Does anyone use a vendor package for checkpoint/restart logic in COBOL programs? If so, I have the standard questions, what is the name of the package, who is the vendor and are you happy with it? Any assistance would be appreciated. We are DB2 OS/390 V5.

Thank you, Sue Cloutier DBA CUNA Mutual Group [...] 11984 138 27_Re: SYSLOG message trapping22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 3 Feb 2000 13:30:38 +0000307_ISO-8859-1 further to this .. I forgot to tell you all that we have an Asembler alert monitor that a friend of mine here has written .. and it send messages to specified users through GATE .. it is quite sexy and we are about to limit the messages by variables .. so I can limit what I see and when .. [...] 12123 34 24_DB2-L-Documents web site13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 3 Feb 2000 08:46:56 -0500355_us-ascii When you go to this web site, you will see on the home page, several options, including "Join or leave the List". It is NOT necessary to join the list in order to view the archives. The emails and their attached documents are stored in directories based on the month and year in which they were submitted. There is also a search capability. [...] 12158 13 16_DSNZPARM COMPACT11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Thu, 3 Feb 2000 07:40:33 -0600344_- Deliberating on whether to compact tape archive logs. Any opinions? Regards, John Rosser Duke Energy

================================================ 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. 12172 25 26_Check Constraint questions0_33_WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV30_Thu, 3 Feb 2000 08:31:25 -0500369_US-ASCII Morning list, We've run into a situation where our DAs have provided a CHECK CONSTRAINT statement that spans two lines.

PY_RT_DETRMNT_IND CHAR(1) NOT NULL, CONSTRAINT V4PSNACT CHECK (PY_RT_DETRMNT_IND IN (' K ' , ' M ' , ' R ' , ' S ' , ' U ' , ' V ' , ' 2 ', ' 3 ' , ' 4 ' , ' 5 ' , ' 6 ' , ' 7 ' , ' A ' , ' B ' , ' C ' , ' E ' , ' F ' , ' J ' , [...] 12198 137 27_Re: SYSLOG message trapping13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 3 Feb 2000 09:05:35 -0500609_iso-8859-1 Andy, could you post it to the DB2-l-documents web site? Also, this is a stupid question, but, do you mean it sends a Windows-based email or do you have a mainframe-based email system?

Thanks!

> -----Original Message----- > From: Andy Hunt [SMTP:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] > Sent: Thursday, February 03, 2000 3:48 AM > To: DB2-L@RYCI.COM > Subject: Re: SYSLOG message trapping > > I agree with Mike in that it is almost impossible to produce (and keep > current) > a list of messages to trap. I think it is far easier, and safer, to > produce an > exclusion message list [...] 12336 22 26_Is ListServer Still Alive?15_Jeffery A Price17_jprice@IPALCO.COM30_Thu, 3 Feb 2000 09:10:16 -0500377_us-ascii Dear List Owner(s) (Sorry for using list, instead of your ID(s)),

I have been receiving the DB2-L on a daily basis (digested, of course) for over a year. I have not received my daily email since Tuesday, 2/1/2000. Could you please look into this for me?

Thanks, Jeffery A. Price Associate Engineer Indianapolis Power and Light Co. jprice@ipalco.com [...] 12359 27 20_Net.Data Maintenance15_Jeffery A Price17_jprice@IPALCO.COM30_Thu, 3 Feb 2000 09:22:07 -0500602_us-ascii Dear List,

I am entering new territory working with Net.Data for OS/390, and was curious as to your experiences with applying maintenance to this product. Do you use a separate HFS dataset to apply maintenance to, and then issue the mount command to mount the new file system)? Or do you just have one HFS dataset, and have a maintenance directory plus a production directory, and move modules hit by maintenance with copy commands (or change webserver directives to point to new files)? I would greatly appreciate anyone sharing their maintenance strategies/philosophies with me. [...] 12387 34 27_Re: SYSLOG message trapping12_Vernon, John27_John.Vernon@TWC.STATE.TX.US30_Thu, 3 Feb 2000 08:31:46 -0600598_- Not meaning to advertise someone's product, but we utilize LandMarks Tmon for DB2 (and CICS and MVS and Navigraph and Naviplex). In Tmon for DB2, there is the capability to display DB2, IRLM and MVS messages, as well as filter them based upon Set Processing. I find this feature extremely helpful, and can also set up Exception Messages (in Tmon for DB2) that will send TSO messages to my screen informing me of potentially system-threatening situations (also very useful for tuning purposes). I can also generate reports based upon the messages received. All in all, a rather helpful tool. [...] 12422 46 57_Re: Checkpoint/Restart Packages w/ Extended QSAM support?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 09:34:39 -0500645_us-ascii Hi Suresh

How are you ? We use BMC AR/CONTROL. I don't know much about this product but I can find from my fellow DBA. I think it supports variety of file (VSAM/QSAM etc.) along with IMS and DB2.

Regards Venkat Pillay

> -----Original Message----- > From: Suresh Sane [SMTP:data_arch@HOTMAIL.COM] > Sent: Thursday, February 03, 2000 12:12 AM > To: DB2-L@RYCI.COM > Subject: Checkpoint/Restart Packages w/ Extended QSAM support? > > I need some help from anyone who has evaluated and/or is using any vendor > package for application checkpoint/restart. > > I evaluated and implemented Sysdata's Quickstart a few [...] 12469 24 18_DB2 and Top Secret12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Thu, 3 Feb 2000 09:44:28 -0500554_iso-8859-1 I am establishing a production system utilizing DB2 (OS/390, DB2 V5) and would like to have 8 groups built in Top Secret that I can add users to, so that I don't need to specifically define each new user (they would pick up the attributes of the group to which they belong). My sysprog / security officer has told me that Top Secret does not support the group concept, only profiles. I find this hard to believe. How do shops with TS establish their security environments? Are groups not supported in TS? Any info is greatly appreciated. [...] 12494 69 57_Re: Checkpoint/Restart Packages w/ Extended QSAM support?14_Stevens, Wayne21_Wayne_Stevens@BMC.COM30_Thu, 3 Feb 2000 08:49:06 -0600729_ISO-8859-1 You might check out this URL about APPLICATION RESTART CONTROL (AR/CTL(tm)).

http://www.bmc.com/rs-bin/RightSite/getcontent/bmcdoc.html?dmw_objectid=0900 320180078082&dmw_format=html

Wayne Stevens BMC Software, Inc. wayne_stevens@bmc.com







-----Original Message----- From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Sent: Thursday, February 03, 2000 8:35 AM To: DB2-L@RYCI.COM Subject: Re: Checkpoint/Restart Packages w/ Extended QSAM support?



Hi Suresh

How are you ? We use BMC AR/CONTROL. I don't know much about this product but I can find from my fellow DBA. I think it supports variety of file (VSAM/QSAM etc.) along with IMS and DB2. [...] 12564 44 22_Re: DB2 and Top Secret14_McCombs, Terry20_tmccomb@SEDGWICK.GOV30_Thu, 3 Feb 2000 08:59:03 -0600443_iso-8859-1 Top Secret has the concept of "IBMGROUP", which you can assign to a user or a profile as a resource. It becomes a secondary i.d. Then when you grant access to the group, the user or profile picks up the access.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Andy Wheeler Sent: Thursday, February 03, 2000 8:44 AM To: DB2-L@RYCI.COM Subject: DB2 and Top Secret [...] 12609 185 27_Re: SYSLOG message trapping9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Thu, 3 Feb 2000 14:58:26 +0000275_us-ascii Lynne, I wrote the program for my current client, so I guess it is their property. I will talk to my manager about distributing it to the website. Im currently handing over work before I start my holidays so will let you know when I get back (couple of weeks). [...] 12795 46 22_Re: DB2 and Top Secret12_Lisle, Kathy19_KLLISLE@INTEGON.COM30_Thu, 3 Feb 2000 10:11:00 -0500588_iso-8859-1 I am in a similar situation. On a test lpar, our system programmer installed the sample connection exit with the IBM-supplied sample exit routine (used for RACF). Then, we granted table authorizations (SELECT, ADD, etc.) to a test Top Secret group and successfully accessed the tables using an ID that is in the group. When I granted EXECUTE authority on a plan to a real group of programmers on another lpar, however, a person in that group was unable to execute the plan. Yes, I should have tested EXECUTE; no, I didn't. My to-do list for this week includes researching [...] 12842 42 26_V4 -- V5 Migration Y2K bug14_george mclaren31_george_mclaren@STANDARDLIFE.COM30_Thu, 3 Feb 2000 15:15:04 -0000427_iso-8859-1 For any interested parties...especially those still running V4.



This following happened as a result of migrating a V4 subsystem to V5 ..after 01/01/2000.

When I decided to look through Spufi, at table SYSPLAN, the new (V5) column BOUNDTS caught my attention.

It appeared that PLANS bound since 01/01/00..at V4 had been converted by CATMAINT ..to have a BOUNDTS field starting 1900- [...] 12885 25 12_DB2 and ZACK12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM30_Thu, 3 Feb 2000 07:40:55 -0800611_us-ascii Help if you can, tried this before but response was minimal, think it is because most readers of this list are DBA type folks. Please pass on to your sys. prog. if you don't know. Would like to know if anybody has put together code to trap DB2 archive messages then create necessary JCL to submit for copying archive to be taken offsite. The real hanger is, have they done it using ZACK (automated operator product) now residing with CA. Hate to reinvent the wheel if someone has something that works. thanks. __________________________________________________ Do You Yahoo!? Talk to your friends [...] 12911 42 30_Off topic post - Mail Trailers14_Donovan, Peggy22_peggy.donovan@SSMB.COM30_Thu, 3 Feb 2000 10:40:08 -0500436_- I AGREE.

I receive 1 of 4 digests that are cut off. I suspect there is a problem on the server handling large digests as I have received much larger e-mail from business contacts. I have reported this by addressing several e-mails to list owners. I have received only automated replies - never anything from a human. List owners - are you aware there is an ongoing problem delivering at least the digest form of the list? [...] 12954 18 34_Rebalance a partitioned tablespace11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM30_Thu, 3 Feb 2000 09:40:48 -0600496_ISO-8859-1 Is there a DB2 V5 APAR available that will allow me to rebalance a partitioned tablespace via an “ALTER INDEX xxxxxxx PART # VALUES(????????)” statement ?

Or will I need to wait until I install UDB for OS/390 (DB2 V6) to use this feature?

===============================================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. 12973 58 22_Re: DB2 and Top Secret13_Bruce Cornell23_bcornel@DOA.STATE.LA.US30_Thu, 3 Feb 2000 09:48:43 -0600582_us-ascii Your security officer needs to investigate the IBMGROUP resource in Top Secret. One of those needs to be created for each group that you need. The security officer can then attach the userids to the IBMGROUPs as needed. He can also put the IBMGROUP in a profile.

The IBMGROUP will correspond to the secondary authids you will use in DB2. You grant the permissions in DB2 to the secondary authid. The DB2 user sets the current SQLID to the secondary authid. DB2 then will make a call to the external security package. Top Secret will receive this call and check [...] 13032 65 22_Re: DB2 and Top Secret12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Thu, 3 Feb 2000 11:02:56 -0500446_iso-8859-1 I'd like to hear how your tests go. I've finally convinced (after showing him emails from this list) the systems manager that groups are supported in TS. He is having the sysprog research the matter futher. I'll let you know how it turns out.

Andy

-----Original Message----- From: Lisle, Kathy [mailto:KLLISLE@INTEGON.COM] Sent: Thursday, February 03, 2000 9:11 AM To: DB2-L@RYCI.COM Subject: Re: DB2 and Top Secret [...] 13098 80 22_Re: DB2 and Top Secret12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Thu, 3 Feb 2000 11:08:29 -0500429_iso-8859-1 Many thanks. I didn't think this was rocket science but, as we all run into from time to time, the sysprog wasn't going to budge. This email, and a couple others like it, finally has the attention of the system manager who is going back to the sysprog and having him learn how to do it. They sure don't like to be embarrassed, do they. Ah well, a little humility is good for all of us. Thanks again for the info. [...] 13179 17 24_Re: Net.Data Maintenance13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Thu, 3 Feb 2000 21:38:08 +0530471_us-ascii hi !! Anything in OMVS segment we have it in seperate 2 File systems , I mean 2 sets of HFS. just like alternate residency volumes to apply patch etc. We do the same in OMVS also. Its better to do that way .

Vishy

================================================ 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. 13197 77 27_Re: SYSLOG message trapping24_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM30_Thu, 3 Feb 2000 11:13:08 -0500636_- I would very much like to see the code for digesting the MSTR a/o log messages from Leslie and others. Thx.

Dave

Dave Gendron Colorado Springs, CO 719-520-4760 dave.gendron@coastalcorp.com

-----Original Message----- From: Leslie Pendlebury-Bowe Sent: Wednesday, February 02, 2000 11:21 PM To: DB2-L@RYCI.COM Subject: Re: SYSLOG message trapping

Jeff I have written a rexx that once a day produces a print of the ACTIVE MSTR address space using batch SDSF and then strips things out , speaks to DB2 about certain SAP things and then reports to me any things that it does not have in a list of "ignore" [...] 13275 56 29_Re: SELECT INTO not selecting14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM30_Thu, 3 Feb 2000 08:24:53 -0800349_iso-8859-1 Jason, I encountered a similar problem. One of the stored procedure parms which was being used in a predicate had been padded with something (don't remember the acutal hex content) instead of blanks. I did the DISPLAY too but it appeared as the parm data followed by blanks in the SPAS output. Had me baffled for awhile. Rgds, Steve [...] 13332 63 30_Re: Check Constraint questions15_Toine Michielse18_vndobtm@US.IBM.COM30_Thu, 3 Feb 2000 17:37:50 +0000471_us-ascii Good morning Wayne,

Are you sure that the program you use to query SYSCHECKS does not cutoff the column value after having displayed x bytes? (column is defined as VARCHAR(3800)).

Regards,

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



WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV@RYCI.COM> on 02/03/2000 01:31:25 PM [...] 13396 69 20_Access path problems10_Terry Lake31_Terry.Lake@BRIDGE.BELLSOUTH.COM30_Thu, 3 Feb 2000 10:39:13 -0600576_US-ASCII In a nutshell, the problem is that the query runs like a basset hound in production, and a greyhound on Testbed. Object structures, package versions, table data, even Runstats are all identical. Our system folks say that maint levels are the same. Yet the optimizer has chosen a different access path in Production. It runs 30 minutes on Testbed, 3 hours in production. Testbed is on a smaller machine and Production is usually at 100%, but would that make that much of a difference to the optimizer? And yes, I realize that the optimizer is in the realm of the [...] 13466 89 30_Re: Check Constraint questions12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 3 Feb 2000 09:09:02 PST316_- Wayne, I agree with Toine as a possibility as to why you are not seeing the entire constraint.

Why not test the last value (or each value) in the constraint and a value that is NOT included in the restraint. Simple INSERT or UPDATE statements will validate your constraint.

Craig Patton DB2 DBA [...] 13556 60 35_Re: Off topic post - Mail Trailers.13_John Arbogast16_jfarbo@YAHOO.COM30_Thu, 3 Feb 2000 09:06:27 -0800534_us-ascii I believe we could do without the 1/2 page of disclaimer debris/weasel words at the end of messages.



--- Paul A Redhead wrote: > Hi Folks, > Sorry for the off topic post, but I just wanted > to say that I think some of > the e-mail trailers are getting out of hand in > length. These days for some of > the e-mails if I want to scroll down a post that > contains a number of other > posts to get the hang of the thread it's starting to > take forever. Do we really > [...] 13617 97 24_Re: Access path problems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 12:13:11 -0500437_us-ascii Terry

If you are on version 5, the common mistake is not to look at CARDF, COLCARDF, FIRSTKEYCARDF, FULLKEYCARDF and FREQUENCYF. Are they same in test and production ?

Here is what you have to look for ---> CARDF in SYSIBM.SYSTABLES COLCARDF in SYSIBM.SYSCOLUMNS (Check all the columns used in the WHERE predicate) FULLKEYCARDF & FIRSTKEYCARDF in SYSIBM.SYSINDEXES FREQUENCYF in SYSIBM.SYSCOLDIST (if exists) [...] 13715 34 32_JDBC Connection to DB2 on OS/39018_Whittaker, Stephen26_stephen.whittaker@CPLC.COM30_Thu, 3 Feb 2000 12:18:15 -0500553_iso-8859-1 I've heard that you can use a straight JDBC connection to DB2 V5 from OS/390. Can someone, in a nutshell, give me an idea of how this works? I'm curious as to what type of DB2 security restrictions might be encountered. ie; When I installed DB2 Connect here we had all sorts of userid authorization issues. As a DB2/DBA what should we be looking for to support this type of connection, etc. I apologize if this question is not to clear for I'm an ole mainframe DB2/DBA and just starting to scratch the surface on this new stuff. I would [...] 13750 60 30_Re: V4 -- V5 Migration Y2K bug0_17_JTonchick@AOL.COM28_Thu, 3 Feb 2000 12:29:45 EST540_US-ASCII I'm VERY interested in this. I'm on the verge of migrating from v4 - v5. I'm going to call IBM and ask to be put on the interested parties list. Does this cause any operating errors, or is it just history data that gets lost?

I was prevented from installing v5 during Y2k testing because there was a "freeze" on system software changes. Now that we're into 2000, I've done the v5 smp work, and am in the process of applying PDO9952 to v4. I'd like to finish the migration to v5 this quarter, but now it looks like I'll [...] 13811 48 24_Re: Access path problems14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 3 Feb 2000 12:31:08 -0500858_ISO-8859-1 If everything is the same, why is the number of index pages in production 8 times higher? Something does not quite compute there... Also, are your buffer pools and assignments the same on both systems? Buffer pool sizes may affect access path choices by the optimizer.

Regards, Joel |======================================================= | Joel Goldstein | Responsive Systems Company |======================================================= | WEB Site | www.responsivesystems.com |======================================================= | Buffer Pool Tool for DB2 & DASD/Xpert for DB2 | >>> The Answers for your performance problems <<< |------------------------------------------------------- | Tel.(800) DB2-EXPErt Tel.(800) 322-3973 | Tel.(732) 972-1261 Fax.(732) 972-9416 |======================================================= [...] 13860 244 42_Re: TIMESTAMP as Non-intelligent ID for PK19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 3 Feb 2000 11:36:09 -0600599_iso-8859-1 Sibimon Philip, Thank you very much. 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." [...] 14105 41 38_Re: Rebalance a partitioned tablespace14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 3 Feb 2000 11:41:17 -0500636_iso-8859-1 It is a V6 feature, as there are many changes in the system to support it, including changes to REORG etc.

Regards, Richard +===+===+===+===+====+ ryevich@attglobal.net

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Joe Bitetto > Sent: Thursday, February 03, 2000 10:41 AM > To: DB2-L@RYCI.COM > Subject: Rebalance a partitioned tablespace > > > Is there a DB2 V5 APAR available that will allow me to rebalance a > partitioned tablespace via an > “ALTER INDEX xxxxxxx PART # VALUES(????????)” statement ? > > Or will I need to wait until I install [...] 14147 132 24_Re: Access path problems14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 3 Feb 2000 11:47:07 -0500722_iso-8859-1 Terry,

Any differences in any of the following could cause access path changes and there appear to be some differences from what you said.

The optimizer will make use of the following in order to build this best plan:

Catalog statistics from the following tables are used for access path selection Columns from SYSTABLES - CARDF, EDPROC, NPAGES, PCTROWCOMP Columns from SYSTABLESPACE - NACTIVEF, Columns from SYSCOLUMNS - COLCARDF, HIGH2KEY, LOW2KEY Columns from SYSINDEXES - CLUSTERING, CLUSTERRATIOF, FIRSTKEYCARDF, FULLKEYCARDF - NLEAF, NLEVELS Columns from SYSCOLDIST - COLVALUE, FREQUENCYF, TYPE - CARDF, COLGROUPCOLNO, NUMCOLUMNS Columns from SYSTABSTATS - CARDF, NPAGES Columns [...] 14280 65 16_Re: DB2 and ZACK22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 3 Feb 2000 12:48:18 -0500431_- Mike -

We use a SYSLOG scanning tool (SOLVE Operations MVS from Sterling Software). Using the archive message as a trigger, a script is fired that substitutes the archive dsname in a skeleton JOB and submits the job to JES2.

The skeleton JOB is very simple. It contains a jobcard and an EXEC proc. The archive log & BSDS dsnames are passed to the proc via symbolic substitution. The proc is a 2 step IEBGENER. [...] 14346 37 24_Re: Access path problems15_Toine Michielse18_vndobtm@US.IBM.COM30_Thu, 3 Feb 2000 19:18:11 +0000359_us-ascii Hello Terry,

I assume you're aware that even if the query in Prod/Test have the same access paths, you might still see differences in elapsed time because of difference in I/O waits (number of I/O, I/O performance) and dispatching etc....

The following is a long shot and DB2 behaviour may have changed but it is an experience.... [...] 14384 64 24_Re: Access path problems14_Wayne Driscoll19_wdriscoll@QUEST.COM30_Thu, 3 Feb 2000 11:03:23 -0800595_iso-8859-1 Toine, When a table doesn't have an index explicitly defined as CLUSTERED, DB2 will treat the first index created as the CLUSTERING index. Because of this, when there are no stats, and an order by is involved, the order of index creation will impact the decision to sort the data vs. access via clustering index. Because of this I would always recommend that a clustering index is always chosen for tables that have multiple indexes. Otherwise, any operation that requires a drop and create of the table and all its dependants could have a major impact on access path selection. [...] 14449 70 24_Re: Access path problems14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 3 Feb 2000 13:05:15 -0500409_iso-8859-1 Toine,

The situation you described, normally only happens when a clustering index is defaulted and not defined. No way to know if that was your case. There are other situations were creation ordering of indexes affecting access paths can occur but we have not seen them repeat in V5, where many changes occurred in the index selection process according to documented optimizer changes. [...] 14520 80 35_Re: Off topic post - Mail Trailers.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 3 Feb 2000 13:13:57 -0600574_iso-8859-1 John, I think I speak for many DB2-L listers in that I'd love to do away with the following trailer; however, its required by my employer. I'm careful to delete any extra trailers if they appear more than once. Sorry, 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 [...] 14601 84 24_Re: Access path problems0_23_Mike_Levine@TEKHELP.NET30_Thu, 3 Feb 2000 14:22:48 -0500629_us-ascii Hi Wayne,

You can still avoid sorts with non-clustering indexes if the high order columns of the index match your ORDER BY columns. The index does not have to be the clustering index to avoid a sort. Did I understand your response correctly?

Regards,

Michael Levine Premier Data Services, Inc.



>Toine, >When a table doesn't have an index explicitly defined as CLUSTERED, DB2 will >treat the first index created as the CLUSTERING index. Because of this, >when there are no stats, and an order by is involved, the order of index >creation will impact the decision to sort the data [...] 14686 100 33_Re: Reply to BEWARE DEFAULT BLKSI19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 3 Feb 2000 13:32:55 -0600540_iso-8859-1 John, Snip>> So if I change my ZPARMS today from 28K to 24K for archive logs(on tape), and then at disaster recovery time copy the 24K tape logs to 24K DASD and the 28K tape logs to 24K DASD(assuming I have a mix of blocksizes still), then I should be OK? Endsnip<< Yes. Snip>> is it really the case that you can't change the blocksize once an archive log is created or is it that you must have all archive logs the same blocksize? I don't see a blocksize in DSNJU004 output. Endsnip<< The blocksize of an Archive log, when [...] 14787 114 24_Re: Access path problems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 14:33:38 -0500342_- To add on Mike's response -

For "Index only access" clustering or non-clustering does not make difference but NLEVELS (sometimes NLEAF for non-matching) does.

Optimizer does not consider I/O (so far I have seen) for NLEVELS = 2 (type 2 indexes must have NLEVELS> 1) hence index with NLEVELS=2 is always a favorite index. [...] 14902 54 22_Re: DB2 and Top Secret0_19_csutfin@AMSOUTH.COM30_Thu, 3 Feb 2000 13:35:45 -0600433_- Andy,

We use Top Secret with GROUPS here. It works very well. TSS has a resource of IBMGROUP that defines a secondary authid. Have your security administrator read the "Implementation: Other Interfaces Guide" for the DB2 authorizations exit and Secondary Authid's.

Carol Sutfin AmSouth Bank NA csutfin@amsouth.com







Andy Wheeler on 02/03/2000 08:44:28 AM [...] 14957 35 23_DB2 Connect and NetWare13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US30_Thu, 3 Feb 2000 14:46:19 -0500530_US-ASCII Greetings,

We currently have NetWare 5 and Websphere 3.0 installed for our LAN. We also have OS/390 V2.6 and DB2 UDB V5 for OS.390. I have been asked if there is a way to connect to DB2 UDB V5.1 for OS/390 from the LAN using JDBC and Websphere.

So far all I can find in redbooks and other manuals is that we would need DB2 Connect V5 EE installed on the platform from which we which to connect to DB2 UDB V5.1 for OS/390. That would mean we would have to install DB2 Connect on the LAN. My problem is, [...] 14993 44 29_DB2 Connect Using DCE Tickets18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM30_Thu, 3 Feb 2000 14:47:14 -0500550_iso-8859-1 Has anyone used this method of handling security from DB2 Connect into the DB2 mainframe world? We're currently at DB2 V5 and I've been doing some reading about these DCE tickets and would like to get some first hand knowledge from anyone that might be using them. 1) what software if any is requrired 2) how were the tickets set up 3) Is DCE software needed at the DRDA server and the DB2 Client Workstation? 4) Is there alot upfront work that needs to be done in order to install something like DCE? Etc.... As I'm sure most of you [...] 15038 130 26_Re: BEWARE DEFAULT BLKSIZE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 3 Feb 2000 14:02:59 -0600580_iso-8859-1 Michael, DB2 for OS/390 V5. I said it backwards, you are correct and I am wrong. I was thinking one thing and writing another. We archive to DASD and archive datasets on DASD cannot span volumes. Hope this didn't cause anyone problems. 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 [...] 15169 47 20_Re: DSNZPARM COMPACT19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 3 Feb 2000 14:13:08 -0600568_iso-8859-1 John, This item has come up from time to time, check list archives. As I recall, consensus of opinions was that because archive logs must be read backwards on occasion, they shouldn't be compressed. By the way, on this subject, I had a tape vendor tell me that his unit had no problem reading a compressed tape backward. Said the unit could read it backwards just as fast as forwards. Bong, gong show time! 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 [...] 15217 39 20_Re: DSNZPARM COMPACT15_Webster, Murray30_Murray.Webster@CCRA-ADRC.GC.CA30_Thu, 3 Feb 2000 14:37:01 -0500362_iso-8859-1 This might depend on your tape medium but I believe that this could cause recovery slow-downs. This is because archive logs are read in reverse during recovery and if "Compact" is used, they would have to be "un-compacted" first before they could be read.

Murray Webster CCRA-ADRC DB2 Software Support ITB/TOCS/HTM/AIS/MEDS (613) 941-6710 [...] 15257 22 22_Improved DB2 Utilities28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM28_Thu, 3 Feb 2000 12:32:11 PST531_- UNLOAD from an image copy is in plan but not on the version, or in the form you are been told. Rather than modify the unload portion of REORG to unload from an image copy, this function, along with other UNLOAD capabilities, needs to be a separate utility. We have more flexibility of syntax and function, and less potential for disrupting REORG by doing this. When and where to deliver this function has been debated before and will be again no doubt. My feeling today is that you will see it in Version 7, and not before. [...] 15280 21 35_Re: Off topic post - Mail Trailers.0_19_Tim.Lowe@STPAUL.COM30_Thu, 3 Feb 2000 14:45:06 -0600409_us-ascii When I see long trailers with "legal disclaimers", I wonder if they could ever be enforced? For example, if the disclaimer says that this is "confidential", but it is being sent to a public list, could this possibly be enforced? I am not a lawyer, but it seems that this is some lawyers idea of "covering himself" by inconveniencing everyone else. This is one reason why lawyers get a bad name. [...] 15302 42 35_Re: Off topic post - Mail Trailers.17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Thu, 3 Feb 2000 15:05:16 -0600652_ISO-8859-1 Isn't that a shame, Tim? 99% of the lawyers ruin it for all the rest.

:-)

KenK. (I guess I'd BETTER include ... my opinion only!)

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Thursday, February 03, 2000 2:45 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Off topic post - Mail Trailers.



When I see long trailers with "legal disclaimers", I wonder if they could ever be enforced? For example, if the disclaimer says that this is "confidential", but it is being sent to a public list, could this possibly be enforced? I am not a lawyer, but it seems that this is [...] 15345 62 27_Re: DB2 Connect and NetWare13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US30_Thu, 3 Feb 2000 16:15:17 -0500424_US-ASCII Hi Stan,

The decision was made to use Novell NetWare and stay away from Win95 or NT.

Burgess

>>> "Park, Stan" 02/03/00 04:01PM >>> Do you have an NT server? That is what we will use.

-----Original Message----- From: Burgess Evans [mailto:BMEVANS@AUDITOR.STATE.OH.US] Sent: Thursday, February 03, 2000 12:46 PM To: DB2-L@RYCI.COM Subject: DB2 Connect and NetWare [...] 15408 23 24_Re: Access path problems10_Terry Lake31_terry.lake@BRIDGE.BELLSOUTH.COM30_Thu, 3 Feb 2000 15:19:52 -0600594_- Thanks to everyone for the responses. And to Richard Yevich for his suggestion to look further into the catalog tables, which is where the problem actually resided. The problem was caused by duplicate entries in SYSIBM.SYSCOLDIST. Don't really know why they were orphaned only there. One set from 1999-10-01 and the current set of stats. Runstats would only overlay the current stats. The 1999-10-01 stats remained untouched. I deleted the old stats, rebound, and now the query has the same access path in Test and Prod. But now I have to explain the whole mess to the developer and why [...] 15432 42 7_ts scan0_24_Munwar.Malik@ACS-GSG.COM30_Thu, 3 Feb 2000 13:50:17 -0500535_US-ASCII why I am getting TS scan from the following sql? Column loan_id_num column is indexed. PLAIN PLAN SET QUERYNO = 99998 FOR SELECT ID_NUM,TXN_ID_NUM FROM CDBAD001.DISB WHERE LOAN_ID_NUM = 000202700949043207668345 --OR LOAN_ID_NUM = 000968600000000555133804 --OR LOAN_ID_NUM = 001490700000000566319794 --OR LOAN_ID_NUM = 002230500000000434067324) -- AND SEQ_NUM > 0 --OPTIMIZE FOR 1 ROW ; ---------+---------+---------+---------+---------+---------+---------+ ----- DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 [...] 15475 44 24_Re: Access path problems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 16:36:28 -0500667_us-ascii "RUNSTAT TABLESPACE ... TABLE ALL INDEX ALL" Wipes out previous rows from SYSIBM.SYSCOLDIST. "RUNSTAT TABLESPACE ... " Does not touch SYSIBM.SYSCOLDIST.

Someone would have inserted row manually in that table or a third party tool would have done that.

> -----Original Message----- > From: Terry Lake [SMTP:terry.lake@BRIDGE.BELLSOUTH.COM] > Sent: Thursday, February 03, 2000 4:20 PM > To: DB2-L@RYCI.COM > Subject: Re: Access path problems > > Thanks to everyone for the responses. And to Richard Yevich for his > suggestion to look further into the catalog tables, which is where the > problem actually resided. The problem was caused by [...] 15520 84 22_Re: DB2 and Top Secret12_Lisle, Kathy19_KLLISLE@INTEGON.COM30_Thu, 3 Feb 2000 16:42:24 -0500393_iso-8859-1 Your notes prompted me to get on with my testing, and the problem is now resolved. Security had forgotten to set up the IBMGROUPs on the 2nd LPAR. They're good folks -- we'll allow them one slip. Thanks to Bruce Cornell and Carol Sutfin for their responses; as soon as I mentioned IBMGROUPs to our security people, the lightbulbs came on, and the problem was quickly resolved. [...] 15605 48 27_TCP/IP GETHOSTBYADDR FAILED13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Thu, 3 Feb 2000 15:51:08 -0600630_US-ASCII Greeting, I'm setting up TCP/IP communications to DB2 on a test lpar. Environment is OS/390 2.7 and DB2 5.1 PDO 9918 + hypers. When DB2 starts DDF I'm seeing the following messages in the MSTR AS

DSNL003I - DDF IS STARTING DSNL512I - DSNLILNR TCP/IP GETHOSTBYADDR FAILED WITH RETURN CODE=1 AND REASON CODE=00000000 DSNT704I - SYSIBM.DSNRLST01 HAS BEEN STARTED FOR THE RESOURCE LIMIT FACILITY DSN9022I - DSNTCSTR 'START RLIMIT' NORMAL COMPLETION DSNL004I - DDF START COMPLETE LOCATION DB2T LU NET1.LUDB2T GENERICLU -NONE DOMAIN -NONE TCPPORT 446 RESPORT 33000 DSN9022I - DSNYASCP 'START DB2' NORMAL COMPLETION [...] 15654 70 31_Re: TCP/IP GETHOSTBYADDR FAILED11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Thu, 3 Feb 2000 16:00:52 -0600614_us-ascii In the Install Guide Version 6.1 page 478. See the section 3. This will fix your problem... : )







Kurt Sahlberg on 02/03/2000 03:51:08 PM



Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Jeff Faughn/MDSC/MAYCO) Subject: TCP/IP GETHOSTBYADDR FAILED







Greeting, I'm setting up TCP/IP communications to DB2 on a test lpar. Environment is OS/390 2.7 and DB2 5.1 PDO 9918 + hypers. When DB2 starts DDF I'm seeing the following messages in the MSTR AS [...] 15725 24 21_Don't worry, be happy12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Fri, 4 Feb 2000 10:07:48 +1300337_us-ascii Michael,

Your comments are, as always, valued. Please don't go all quiet on us. I have it on good authority that the review board has finished its enquiry and will not censure you. Just make sure to distinguish fact from supposition.

Remember, don't sweat the petty things, and don't pet the sweaty things... [...] 15750 30 33_Inner Joins for Updating/Deleting14_David Simmonds24_dsimmonds@LIFE-OF-JA.COM30_Thu, 3 Feb 2000 17:10:51 -0800317_us-ascii Hi there,

I have been struggling with a problem for a very long time and cannot find an answer in SQL reference, SQL books, On the Internet or in user groups (this or any other).

I want to find out how to update/delete from one table using selection criteria based on two tables. Example: [...] 15781 116 35_Re: Off topic post - Mail Trailers.13_John Arbogast16_jfarbo@YAHOO.COM30_Thu, 3 Feb 2000 14:04:26 -0800560_us-ascii Thats exactly why I use this yahoo account and not my corp email address.



--- "DAVIS, RICK (SBCSI)" wrote: > John, I think I speak for many DB2-L listers in that > I'd love to do away > with the following trailer; however, its required by > my employer. I'm > careful to delete any extra trailers if they appear > more than once. > Sorry, > 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 [...] 15898 68 31_Re: TCP/IP GETHOSTBYADDR FAILED11_rick creech18_ykcirc@HOTMAIL.COM28_Thu, 3 Feb 2000 14:23:28 PST645_- FYI, Look at II10962 for the things that can be done to resolve this. We added //SYSTCPD to the DDF started task. Regards, Rick Creech



>From: Kurt Sahlberg >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: TCP/IP GETHOSTBYADDR FAILED >Date: Thu, 3 Feb 2000 15:51:08 -0600 > >Greeting, >I'm setting up TCP/IP communications to DB2 on a test lpar. >Environment is OS/390 2.7 and DB2 5.1 PDO 9918 + hypers. >When DB2 starts DDF I'm seeing the following messages in the >MSTR AS > > DSNL003I - DDF IS STARTING > DSNL512I - DSNLILNR TCP/IP GETHOSTBYADDR FAILED [...] 15967 40 22_Re: R410 DDL Generator14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 4 Feb 2000 09:37:07 +1000493_us-ascii Linda,

I do not have a full version of DDL generator for Version 4 of DB2. I actually don't have any spare time to solve it right now but will notify the list if have achieved it.

Right now I have a generator of Index DDL for V4 only.

Replacing CASE expressions with something else is the tricky bit. It is possible, I think. Right now I'm a bit bogged down on complex CASE constructs for the table column definitions. The format will have to be simplified. [...] 16008 105 27_Press Release: DB2 UDB UNIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Thu, 3 Feb 2000 18:03:29 -0500504_iso-8859-1 FOR IMMEDIATE RELEASE February 3, 2000



Database-GUYS Inc. Unveils New Product Offering

SPENCERPORT, NY - (February 3, 2000) - Database-GUYS Inc. announced today the general availability of SQL-GUY(TM), their newest performance management tool for DB2(R) UDB UNIX. SQL-GUY(TM), which was developed with significant customer input, is the only product on the market that provides instantaneous real-time analysis of the most costly SQL statements in any workload mix. [...] 16114 160 37_Re: Inner Joins for Updating/Deleting11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU30_Fri, 4 Feb 2000 10:02:16 +1100611_- Hi David, How about:

Update table1 set field3 = value where field1 = value1 and value2 = (select field2 from table2 where field2 = value2)

This works at least for UDB v5.2.

HTH, Bruce



> -----Original Message----- > From: David Simmonds [SMTP:dsimmonds@LIFE-OF-JA.COM] > Sent: Friday, February 04, 2000 12:11 PM > To: DB2-L@RYCI.COM > Subject: Inner Joins for Updating/Deleting > > Hi there, > > I have been struggling with a problem for a very long time and cannot > find an answer in SQL reference, SQL books, On the Internet or in user > groups (this or any other). [...] 16275 61 33_Inner Joins for Updating/Deleting14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 4 Feb 2000 10:00:29 +1000419_us-ascii David,

Use a correlated subquery.

Update Field3 in table1 Where table1.field1 = Value1 and exists (select 1 from table2 where table2.field2 = Value2 and table2.joincol = table1.joincol)

If no suitable index for subquery, consider using:

Update Field3 in table1 Where table1.field1 = Value1 and table1.joincol IN (select table2.joincol from table2 where table2.field2 = Value2) [...] 16337 94 20_Access path problems14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 4 Feb 2000 10:00:36 +1000362_us-ascii Terry,

A quick observation first. Your clustering index is 93% clustered I think. This is really very bad. I would never allow less than 98% on a small table. Otherwise "death by random I/O".

Reorg your table and Run RUNSTATS!

If possible, collect the multi column cardinality stats (KEYCARDs) to further assist the optimizer. [...] 16432 77 37_Re: Inner Joins for Updating/Deleting20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 18:14:31 -0500653_us-ascii and value2 = (select field2 from table2 where field2 = value2)

The "=" condition will work only if there is one row qualified in the subquery.

I guess you meant to use distinct --->

and value2 = (select distinct field2 from table2 where field2 = value2)

This works



> -----Original Message----- > From: ALLEN,Bruce [SMTP:bruce.allen@EMPLOYMENTNATIONAL.COM.AU] > Sent: Thursday, February 03, 2000 6:02 PM > To: DB2-L@RYCI.COM > Subject: Re: Inner Joins for Updating/Deleting > > Hi David, > How about: > > Update table1 set field3 = value > where field1 = value1 > and value2 = (select field2 from [...] 16510 63 24_Re: Access path problems14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 4 Feb 2000 10:04:47 +1000703_us-ascii Venkat,

You are right that RUNSTATS can replace COLDIST stats but only 1st column of indexes (not single column unique). Other columns are left alone.

Potentially other columns might be populated manually, by 3rd party products, or be due to an old index subsequently dropped.

From: Michael Hannan

>From: "Pillay, Venkat (PCA)" >Subject: Re: Access path problems >To: DB2-L@RYCI.COM > >"RUNSTAT TABLESPACE ... TABLE ALL INDEX ALL" Wipes out previous rows from >SYSIBM.SYSCOLDIST. >"RUNSTAT TABLESPACE ... " Does not touch SYSIBM.SYSCOLDIST. > >Someone would have inserted row manually in that table or a third party tool >would have done [...] 16574 68 52_Imagecopy changelimit - incorrect escalation to full0_16_acaldwe2@CSC.COM30_Fri, 4 Feb 2000 10:28:03 +1000630_us-ascii Hi folks,

When I run a DB2 incremental image copy (os/390 V5.1), I have been getting a message DSNU402I -DBAP DSNUBAIC - INCREMENTAL IMAGE COPY DISALLOWED FOR REQUESTED TABLESPACE, FULL IMAGE COPY WILL BE TAKEN ,which of course results in B37 abend due to unexpected dataset size.

The reasons given in the manual do not seem to apply The request was for one of the DB2-maintained table spaces DSNDB01.DBD01, DSNDB06.SYSCOPY, or DSNDB01.SYSUTILX. >>>> target was application tablespace A LOAD or REORG utility was run on the table space or data set with no subsequent full image copy. >>>> last utility [...] 16643 97 24_Re: Access path problems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 3 Feb 2000 18:29:49 -0500509_us-ascii Michael

If index is unique (assume single column unique index) then earlier the runstats never used to populate SYSIBM.COLDIST but used to populated COLDIST for the same index and same column when "KEYCARD FREQVAL NUMCOLS 1" was specified.

Now with APAR (I don't remember the APAR #) the value and frequency (Well.. I can't understand how useful this is) first column of unique index is populated in SYSIBM.SYSCOLDIST after runstats. May be it is done just to make things uniform. [...] 16741 168 35_Re: Off topic post - Mail Trailers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Fri, 4 Feb 2000 09:48:05 +1000543_us-ascii John, Since I, and I think many others on this list, have the opportunity to read and respond to the posts with the blessing of our employers during work time, I personally would rather do that then hit the net again when I get home. I'm afraid that while I do work in the computing industry I'm just not sufficiently nerdy for that. If you choose to respond via your yahoo accout, that's great. I choose to respond via my work account. I don't add the trailer. Everything that goes out of this place has the trailer added by my [...] 16910 31 22_Re: DB2 and Top Secret12_Tony Babonas14_Asbdb2@AOL.COM28_Thu, 3 Feb 2000 20:46:47 EST425_US-ASCII For future reference, please be aware that RACF and Top Secret have subtle variations to their terminology. IE.

racf profile = a unit of resource to which permits can be made. TS profile = a group of possibly varying types resources to which users can be added.

RACF group = a collection of resources(possibly none) to which users can be connected (added in TS lingo) TS IBMGROUP = DB2 sec authid [...] 16942 36 26_DB2 V5.1 & Parallelism....16_Charles Valentin15_VALENCH@AOL.COM28_Thu, 3 Feb 2000 21:14:11 EST442_US-ASCII Greetings, We are on DB2 V5.1 @9903+Hipers & process over 4.5 million DB2 threads daily with heavy Batch Billing, Reports/Queries & Online activity

After encountering several problems/abends using Parallelism, we altered VPPSEQT to zero to disable parallelism. After conferring with IBM on several problems, they faxed me a list of ptf's that should be included with 9908 Put-Level & I'm ready to apply this maintenance. [...] 16979 80 11_Re: ts scan0_24_ssethi@LOT.TATASTEEL.COM28_Fri, 4 Feb 2000 03:00:28 GMT598_us-ascii Hi Munwar, Is columns loan_id_num is the first column of index ,i mean is matching index scan possible ?. Use of IN is better than use of OR if u are using the OR predicate on the same column .Most importantly see ur catalog statistics and find out which condition is doing more filtering.See the COLCARDF in SYSIBM.SYSCOLUMNS corresponding to SEQ_NUM and FIRSTKEYCARD in SYSIBM.SYSKEYS corresponding to LOAN_ID_NUM and find out which predicate is doing more filtering as u are specifying the hard coded values for all the where clause.If u want to force the index then i think using [...] 17060 215 35_Re: Off topic post - Mail Trailers.14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 3 Feb 2000 21:42:34 -0500635_iso-8859-1 Paul and others,

It is not the mandated company trailers that are really the problem, but the multiple lines of non-mandated graphics, and non-translated mime encoding, multiples lines and boxes, etc. that are the problem, unfortunately a lot generated by CIS, other ISPs, and personal attention grabbers [:-)].

I think that all of us would be more than willing to deal with the necessary company warranty trailers, if it was limited to that. I have stopped appending any company advertising info, and many of the IBMers from the labs don't even sign their emails (regrettably) with company and location. [...] 17276 112 11_Re: ts scan14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU30_Fri, 4 Feb 2000 14:54:45 +1100386_- Munwar,

The commented lines are confusing. Also, the plan_table output does not include the columns which identify the access type (index or TS scan), but I'll take your word for it.

Does the length of the WHERE predicate exactly match the column definition? Another option maybe that RUNSTATS was run on the table when it was empty (I have seen this a while ago). [...] 17389 27 30_Re: V4 -- V5 Migration Y2K bug12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 4 Feb 2000 00:01:13 -0600312_- We opened and assigned PQ35325 for the problem. You will want to apply this APAR before doing any V4=>V5 migrations this year if you plan to reference BOUNDTS. This does not impact DB2 internal processing, BOUNDTS is supplied for reference only. We are sorry about this one, and glad that it's not worse. [...] 17417 21 52_Imagecopy changelimit - incorrect escalation to full28_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM28_Thu, 3 Feb 2000 23:58:04 PST501_- There are fourteen different scenarios, by my count, that can cause the DSNU402I message, thirteen issued by the module you are seeing. The manual summarizes them pretty well, but some detail is glossed over. There are a few scenarios not documented involving local and remote copies taken at different RBAs. If you have previous image copies for LOCAL and REMOTE (see ICBACKUP values) taken at different RBAs, your current incremental may be promoted. I am not certain of the exact scenarios. [...] 17439 155 56_Re: Imagecopy changelimit - incorrect escalation to full0_18_mebert@AMADEUS.NET30_Fri, 4 Feb 2000 09:21:36 +0100390_us-ascii This has happened in our installation some times, but it was simply due to the fact that the application people tried to take a IIC after a REORG. In your case, I would also start with the assumption that DB2 knows what it is doing and that you have overlooked something (in SYSCOPY, probably). Can you supply us with the entire COPY statement, and the result of this SELECT: [...] 17595 135 30_Re: DB2 V5.1 & Parallelism....0_18_mebert@AMADEUS.NET30_Fri, 4 Feb 2000 09:43:04 +0100493_us-ascii This is also a recurring theme on the list, on which I have a strict opinion. We tried parallelism when we went to V5 10 months ago, but turned it off again in a hurry. We applied a host of PTFs, tried it again maybe 6 months ago... a report (I think about 30' CPU) increased 50% in CPU consumption, even WITHOUT using parallelism! So it's back to DEGREE(1) again, with no plans to change this. (I do not have the list of PTFs we applied handy and no intention of compiling it). [...] 17731 49 38_Re: Rebalance a partitioned tablespace14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 3 Feb 2000 11:41:17 -0500636_iso-8859-1 It is a V6 feature, as there are many changes in the system to support it, including changes to REORG etc.

Regards, Richard +===+===+===+===+====+ ryevich@attglobal.net

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Joe Bitetto > Sent: Thursday, February 03, 2000 10:41 AM > To: DB2-L@RYCI.COM > Subject: Rebalance a partitioned tablespace > > > Is there a DB2 V5 APAR available that will allow me to rebalance a > partitioned tablespace via an > “ALTER INDEX xxxxxxx PART # VALUES(????????)” statement ? > > Or will I need to wait until I install [...] 17781 41 20_-805 and BIND traces0_19_mike.holmans@BT.COM30_Fri, 4 Feb 2000 12:10:18 -0000566_- We've just been having a rash of -805 failures in production. We're using old-style plans with DBRMs tightly bound, so we're not invoking the wrong package version or anything obviously fun like that.

As you can imagine, everyone is busily denying responsibility and asserting that they've been doing everything normally. It can't be anything the DBAs have done, because we are perfect by definition, but there are some misguided individuals who are suggesting that there might be something we've overlooked somewhere, however unlikely that may sound. [...] 17823 60 37_Re: Inner Joins for Updating/Deleting0_24_db46@DAIMLERCHRYSLER.COM30_Fri, 4 Feb 2000 07:46:33 -0500458_us-ascii You cannot update one table using another in DB2 unless you are using UDB version 5 or better. If your using MVS DB2 version 5 or better this can be accomplished using temporary tables and select into's.

What type of DB2 are you using?

Dean









David Simmonds @RYCI.COM> on 02/03/2000 08:10:51 PM

Please respond to DB2 Data Base Discussion List [...] 17884 85 13_Index locking10_Milos Flek17_milos.flek@FSP.CZ30_Fri, 4 Feb 2000 13:46:52 +0100373_iso-8859-1 RE: Deadlock and isolation levelHello, can you help me with DB2 UDB 5.2 NT ?

I realized some tests with our DB and I thought that index locking is implicitly not set to ROW. Is this really database feature or my mistake in database configuration or index creation? Where can I to check current index locking? Are some changes in new version DB2 6.1? [...] 17970 58 20_Re: DSNZPARM COMPACT11_Steck, Dave16_Steckd@ABCBS.COM30_Fri, 4 Feb 2000 06:57:03 -0600452_iso-8859-1 True, reading a compressed tape backward does cause recovery slow downs but you have to look at your hardware. On our drives, it is impossible to turn off IDRC compression so the ZPARM is worthless anyway.

The only time you would read a tape backwards is if you had a huge transaction that didn't take a commit and filled all of your active log datasets and then wrapped around again on them before failing and doing a rollback. [...] 18029 54 27_DB2 Connect and DCE Tickets18_Whittaker, Stephen26_stephen.whittaker@CPLC.COM30_Fri, 4 Feb 2000 07:49:53 -0500444_iso-8859-1 The DB2-L did not distribute the below note that I posted yesterday. It says that I had already sent it but to my knowledge I had only posted this question once. I am asking that it be posted again and I apologize if for some reason this was already posted by me or if this has already been discussed and I'm kicking an old horse. Here's my question: >>Has anyone used this method of handling security from DB2 Connect into the [...] 18084 59 24_Re: Access path problems10_Terry Lake31_terry.lake@BRIDGE.BELLSOUTH.COM30_Fri, 4 Feb 2000 06:58:53 -0600694_- Not so in our case. RUNSTATS "TABLE ALL INDEX(ALL) UPDATE ALL" did not delete the earlier older set of entries in SYSCOLDIST. It replaced the most recent stats dated 2000-02-03, not the old 1999-10-01 stats.

TL

On Thu, 3 Feb 2000 16:36:28 -0500, Pillay, Venkat (PCA) wrote:

>"RUNSTAT TABLESPACE ... TABLE ALL INDEX ALL" Wipes out previous rows from >SYSIBM.SYSCOLDIST. >"RUNSTAT TABLESPACE ... " Does not touch SYSIBM.SYSCOLDIST. > >Someone would have inserted row manually in that table or a third party tool >would have done that. > >> -----Original Message----- >> From: Terry Lake [SMTP:terry.lake@BRIDGE.BELLSOUTH.COM] >> Sent: Thursday, [...] 18144 269 30_Re: DB2 V5.1 & Parallelism....17_Hynes, Kenneth J.17_khynes@TRIGON.COM30_Fri, 4 Feb 2000 08:28:10 -0500560_iso-8859-1 Regarding Parallelism,

We run a mixed mode shop with both OLTP and DSS in the same DB2 subsystem -- DSS was first on the system and the OLTP folks just hopped on and kept coming. We have successfully utilized parallelism without the "hit" described by some of the previous responders. Note -- we do NOT use it with OLTP only the DSS and long running batch reporting jobs. I can't site a host of specific programs, but one recent job was predicted to run for over 72 hours extrapolating from a run of about 10-12 hours before the job was [...] 18414 101 11_Re: ts scan0_24_Munwar.Malik@ACS-GSG.COM30_Fri, 4 Feb 2000 08:51:26 -0500413_US-ASCII Thanks Sanjeev for your help.

MY problem was that the index was empty. I did recover index. Sometime ago I ran DSN1COPy on this table and forgot to recover the index.

Thanks again



______________________________ Reply Separator _________________________________ Subject: Re: ts scan Author: DB2 Data Base Discussion List at Internet Date: 2/4/00 3:00 AM [...] 18516 130 11_Re: ts scan0_24_Munwar.Malik@ACS-GSG.COM30_Fri, 4 Feb 2000 08:54:57 -0500517_US-ASCII Thanks .

The index was empty. I ran recover index and runstat to resolve the problem.



______________________________ Reply Separator _________________________________ Subject: Re: ts scan Author: DB2 Data Base Discussion List at Internet Date: 2/4/00 2:54 PM



Munwar,

The commented lines are confusing. Also, the plan_table output does not include the columns which identify the access type (index or TS scan), but I'll take your word for it. [...] 18647 89 20_Re: DSNZPARM COMPACT19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Fri, 4 Feb 2000 07:58:21 -0600568_iso-8859-1 Murray, all, Please consider recovery at a disaster recovery site, using archive logs on tape. That's why its usually better to copy the archives to DASD, if its available at the remote site, prior to starting recoveries. I was once told by a non-IBM vendor that IDRC could not be turned off. Turns out I could. 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 [...] 18737 117 24_Re: Access path problems14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 26 Jan 2000 00:57:08 +1000366_us-ascii Venkat,

I know you will be aware of this already, but in case anyone is unclear.

There is no use for Frequent value stats on Single Column Unique indexes because every value has exactly 1 instance. A perfectly even distribution does not need Frequent Value Stats to calculate filter factors. So its a complete waste of time if populated. [...] 18855 42 24_Re: -805 and BIND traces13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 4 Feb 2000 09:23:13 -0500597_iso-8859-1 Hi Mike, In my half-vast experience, -805s encountered in non-package Plans are symptomatic of missing DBRMs, rather than wrong versions of them.

Are these batch programs or IMS MPPs with subroutines and therefore plans consisting of multiple DBRMs? Or are these CICS transactions in which you XCTL or LINK to other pgms/trans? Are these all new programs which are failing? It seems to me a failed bind would cause a -805(for non-package Plans) only if the DBRM were new to the plan and the Bind Statement were missing it in the MEMBER list. I guess it could also happen if [...] 18898 82 37_Re: Inner Joins for Updating/Deleting13_Smith, Glynne20_SmithGly@NABISCO.COM30_Fri, 4 Feb 2000 09:18:51 -0500712_- I have a similar question! I want to update table DEALCOMM..EMP1 with values from table TDNFDBA.EMP1...something like...(from SQL Server)

update DEALCOMM..EMP1 SET salary=d.salary from dealcomm..EMP1 s, STARDBT1..TDNFDBA.EMP1 D where s.EMPNO = D.EMPNO and D.PROCESS_IND="U"



> -----Original Message----- > From: Michael Hannan [SMTP:mhannan@C031.AONE.NET.AU] > Sent: Thursday, February 03, 2000 7:00 PM > To: DB2-L@RYCI.COM > Subject: Inner Joins for Updating/Deleting > > David, > > Use a correlated subquery. > > Update Field3 > in table1 > Where table1.field1 = Value1 > and exists (select 1 from table2 > where table2.field2 = Value2 > and table2.joincol = table1.joincol) > > If [...] 18981 70 24_Re: -805 and BIND traces20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 4 Feb 2000 09:41:24 -0500649_us-ascii Mike

Performance class 10 or P(30) IFCID(108,109) will give you all bind and rebind operations along with success or failure. Beware -> Remote bind is not reported.

HTH Venkat Pillay

> -----Original Message----- > From: mike.holmans@BT.COM [SMTP:mike.holmans@BT.COM] > Sent: Friday, February 04, 2000 7:10 AM > To: DB2-L@RYCI.COM > Subject: -805 and BIND traces > > We've just been having a rash of -805 failures in production. We're using > old-style plans with DBRMs tightly bound, so we're not invoking the wrong > package version or anything obviously fun like that. > > As you can imagine, everyone is busily [...] 19052 204 30_Re: DB2 V5.1 & Parallelism....20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 4 Feb 2000 09:32:19 -0500448_iso-8859-1 I agree with Dr. Ebert. Not in one installation but a least in 3 installations (both v4 & v5) I have seen CPU parallelism killing the performance especially when shop upgraded to OS-390. Binding with DEGREE(1) had direct effect and everything was back to normal.

Unfortunately for static SQL there is no way to keep I/O || but disable CPU ||. I guess there is a way to tweak a parameter in DSN6SPRC but too dangerous to try. [...] 19257 165 30_Re: DB2 V5.1 & Parallelism....14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Fri, 4 Feb 2000 09:45:26 -0500580_us-ascii My compatriots and myself have been using Parallelism for a couple of years in production environments. I have experienced problems with the widespread use of DEGREE=ANY. My suggestion to you would be to have a standard of DEGREE=1 and bind individual packages with DEGREE=ANY on a case by case basis. The one area we have seen the biggest problem is with singleton selects using parallelism when only a small amount of data would ever be accessed. I might also suggest downloading visual explain (I think it is free from IBM) as it is a great visual way to see the [...] 19423 102 37_Re: Inner Joins for Updating/Deleting20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 4 Feb 2000 09:58:18 -0500674_us-ascii Unfortunately when you join two tables it becomes read-only and updates for joins are not allowed in DB2.

> -----Original Message----- > From: Smith, Glynne [SMTP:SmithGly@NABISCO.COM] > Sent: Friday, February 04, 2000 9:19 AM > To: DB2-L@RYCI.COM > Subject: Re: Inner Joins for Updating/Deleting > > I have a similar question! > I want to update table DEALCOMM..EMP1 with values from table > TDNFDBA.EMP1...something like...(from SQL Server) > > update DEALCOMM..EMP1 > SET salary=d.salary > from dealcomm..EMP1 s, > STARDBT1..TDNFDBA.EMP1 D > where s.EMPNO = D.EMPNO > and D.PROCESS_IND="U" > > > > -----Original Message----- > > From: Michael Hannan [...] 19526 72 33_RRS ATTACH FAILS stored procedure13_John Schluter24_john.schluter@SWIPNET.SE30_Fri, 4 Feb 2000 08:52:15 -0600393_ISO-8859-1 Hi,

DB2 OS/390 v 5.1.2 We are starting up stored procedures using WLM.

Want to call the DSNUTILS procedure using the sample program DSN8EPU.

Kompile and linkedit of DSN8EPU go OK but the execution won't work.

The WLM APPLENV is started and RRS is started. (No CF)

Possible authorization problem ?? Wrong DSNX9WLM kod ?? Wrong DSNUTILS kod ?? [...] 19599 124 37_FW: Inner Joins for Updating/Deleting20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 4 Feb 2000 10:22:10 -0500666_us-ascii Please read DB2 as DB2 for MVS or OS-390 / UDB for OS-390.

For UDB on windows-NT following works.

update DEALCOMM..EMP1 SET salary=(select d.salary from dealcomm..EMP1 s, STARDBT1..TDNFDBA.EMP1 D where s.EMPNO = D.EMPNO and s.EMPNO = 'xxxxx' and D.PROCESS_IND="U") WHERE EMPNO = 'xxxxx'

assuming EMPNO is unique.



> -----Original Message----- > From: Pillay, Venkat (PCA) > Sent: Friday, February 04, 2000 9:58 AM > To: 'DB2 Data Base Discussion List' > Subject: RE: Inner Joins for Updating/Deleting > > Unfortunately when you join two tables it becomes read-only and updates > for joins are not allowed in DB2. > > [...] 19724 16 24_Re: Access path problems0_19_Tim.Lowe@STPAUL.COM30_Fri, 4 Feb 2000 09:33:51 -0600387_us-ascii Would RUNSTATS "TABLE ALL INDEX(ALL KEYCARD) UPDATE ALL" have deleted the earlier older set of entries in SYSCOLDIST?

Thanks, Tim

================================================ 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. 19741 86 31_Re: TCP/IP GETHOSTBYADDR FAILED13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Fri, 4 Feb 2000 09:44:10 -0600360_US-ASCII Thanks to those who replied, It appears we had a bad alt address and found this easily after applying UQ37528. Thanks again. Kurt





>>> rick creech 02/03/00 04:23PM >>> FYI, Look at II10962 for the things that can be done to resolve this. We added //SYSTCPD to the DDF started task. Regards, Rick Creech [...] 19828 45 24_Re: Access path problems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 4 Feb 2000 10:52:09 -0500532_us-ascii As Michael mentioned - "INDEX(ALL KEYCARD)" will populate TYPE 'C' statistics while INDEX ALL populates TYPE 'F' statistics.

This means if someone runs "INDEX ALL" followed by "INDEX(ALL KEYCARD)" then nothing will be deleted from COLDIST but both 'F' and 'C' type statistics are populated.

IF your run "INDEX ALL" followed by "INDEX ALL" then runstats deletes the previous entry before populating COLDIST. Same is true when you run "INDEX(ALL KEYCARD)" followed by another runstats "INDEX(ALL KEYCARD)" [...] 19874 64 24_Re: -805 and BIND traces9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Fri, 4 Feb 2000 15:28:39 -0000293_iso-8859-1 If you are using plans with DBRMs, I would expect -818 errors if the bind had failed but the new load module had been created. I think it is more likely that the DBRM (called program maybe!) has been omitted in BIND statement which would cause a -805 error in old-style plans. [...] 19939 62 26_Re: Improved DB2 Utilities14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Fri, 4 Feb 2000 10:30:22 -0600529_ISO-8859-1 The third party utilities have been taking a beating on the DB2-L list the past couple of weeks. But I just had to post a response to this one. BMC Software has been in the business of providing high speed utilities for years. We understand not only what needs to be done to make utilities run as fast as possible, but we also understand the needs and desires of our customers who run these processes. You do not need to wait for V7 to unload from an image copy -- BMC UNLOAD PLUS does it today, and has had this [...] 20002 45 16_Re: DB2 and ZACK14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Fri, 4 Feb 2000 10:37:42 -0600417_ISO-8859-1 Mike:

I do not have the answer to your question, but I do have a data point for you. ZACK does not reside with CA. It was one of the products that the Dept. of Justice forced CA to sell to allow them to swallow Platinum. CA sold ZACK, as well as most of the other "Z" process automation products to Allen Systems Group, a privately-held systems management company in Florida http://www.asg.com [...] 20048 17 29_Re: SELECT INTO not selecting12_Jason Hughes20_jason.hughes@TRW.COM30_Fri, 4 Feb 2000 11:03:40 -0600410_- Another addition to my confusing problem. I have discovered that even if I remove the WHERE clause from my SELECT INTO, it still returns a +100. The table it is selecting from has more than 1 row in it. I was expecting a -811 (or something similar to it). The fact that this problem occurs in one sub-system but not the other makes it even more confusing. Any thoughts on what we should be looking for? [...] 20066 29 61_JDBC-Websphere stored procedures/DB2 OS390 V5/SQL state 2400013_Michalik, Ken19_kmichalik@KRAFT.COM30_Fri, 4 Feb 2000 11:27:24 -0600318_- Dear List:

We have a situation where Java code running under JDBC in Websphere on OS/390. DB2V5 stored procedures invoked from JAVA in this environment return with an undocumented SQL state of 24000. There is no other information that I have been provided. The SPs work when invoked from an NT platform. [...] 20096 27 32_Read Only Access to DB2 Clusters12_Greg P Mucha24_Greg_P_Mucha@CARADON.COM30_Fri, 4 Feb 2000 17:52:51 +0000536_us-ascii Subject: Read Only Access to DB2 Clusters

Unlike some of my colleagues, I remain unimpressed with DB2's performance. I've processed hundreds of thousands of transactions in the time that DB2 takes to process only a few thousand.

So I'm looking for ways to access the VSAM clusters directly for batch, read-only initiatives. I want to be able to dance through the data, assemble my own indexes knowing what I know about the key structure of my data, and then go after that data in subsequent steps using DB2. [...] 20124 18 27_Re: Deleting DB2 VSAM files12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU30_Fri, 4 Feb 2000 12:59:57 -0500495_iso-8859-1 Hi all, I used BMC to drop db. However, it did not delete the VSAM files. I viewd the VSAM files in 3.4 and tried using IDCAMS to delete them, got a RC=8. Can someone give me a clue as to how to delete these VSAM files.

Thank you. Jeff

================================================ 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. 20143 13 27_Re: Deleting DB2 VSAM files12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM30_Fri, 4 Feb 2000 12:04:44 -0600366_us-ascii Did you try to delete the data componet or the cluster? It sounds to me like you tried to delete the data componet.

================================================ 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. 20157 28 27_Re: Deleting DB2 VSAM files12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU30_Fri, 4 Feb 2000 13:08:07 -0500590_iso-8859-1 I tried both.

-----Original Message----- From: Dan Sullivan [mailto:daniel.sullivan@ZURICHUS.COM] Sent: Friday, February 04, 2000 1:05 PM To: DB2-L@RYCI.COM Subject: Re: Deleting DB2 VSAM files



Did you try to delete the data componet or the cluster? It sounds to me like you tried to delete the data componet.

================================================ 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. [...] 20186 40 27_Re: Deleting DB2 VSAM files9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Fri, 4 Feb 2000 11:07:58 -0700311_iso-8859-1 You need to check if your DB2 Database is using VSAM managed datasets or STOGROUP managed. If they are STOGROUP managed the VSAM files will be "autodeleted" by DB2 otherwise, you must have User-Managed VSAM files in which case you must manually delete them yourself after you drop the Database. [...] 20227 39 27_Re: Deleting DB2 VSAM files14_Wayne Driscoll19_wdriscoll@QUEST.COM30_Fri, 4 Feb 2000 10:23:57 -0800467_iso-8859-1 Are you sure that the drop completed? Make sure that the files are not allocated to any other address spaces, this could cause an RC=8 on the delete. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

-----Original Message----- From: Kalman, Jeff [mailto:JKalman@UNCH.UNC.EDU] Sent: Friday, February 04, 2000 12:00 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Deleting DB2 VSAM files [...] 20267 27 27_Re: Deleting DB2 VSAM files12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM30_Fri, 4 Feb 2000 12:18:18 -0600448_us-ascii here is a post from a few days ago on delete.



Try this: //STEP1 EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //DD01 DD UNIT=SYSDA,VOL=SER=LMDB2L,DISP=SHR //SYSIN DD * DELETE (DELETE DB2Q.DSNDBD.DSNDB07.DSN4K01.I0001.A001) FILE(DD01) - CATALOG(cccc) VVR

where cccc is the user catalog that DB2Q is an alias for.



This is just the data componet. Are you specifing 'cluster purge' on your delete statement? [...] 20295 57 36_Re: Read Only Access to DB2 Clusters14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Fri, 4 Feb 2000 13:32:10 -0500540_us-ascii Well having been a DB2 DBA for over 10 years, I must say that the most rational thing to do is to scrap DB2 and use straight VSAM KSDS files for your programs since the myriad of DB2 bells and whistles are not to your liking. Passed that thought, I assume you have tried using indexes and parallelism to accomplish the task, although I am not sure how you would know the access path if you choose not to use explain. The answer to your original question is to look up the tablespace, database and partition numbers in the DB2 [...] 20353 43 27_Re: Deleting DB2 VSAM files12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU30_Fri, 4 Feb 2000 13:36:16 -0500511_iso-8859-1 Thank you for your reply. Jeff

-----Original Message----- From: Dan Sullivan [mailto:daniel.sullivan@ZURICHUS.COM] Sent: Friday, February 04, 2000 1:18 PM To: DB2-L@RYCI.COM Subject: Re: Deleting DB2 VSAM files



here is a post from a few days ago on delete.



Try this: //STEP1 EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //DD01 DD UNIT=SYSDA,VOL=SER=LMDB2L,DISP=SHR //SYSIN DD * DELETE (DELETE DB2Q.DSNDBD.DSNDB07.DSN4K01.I0001.A001) FILE(DD01) - CATALOG(cccc) VVR [...] 20397 50 27_Re: Deleting DB2 VSAM files19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Fri, 4 Feb 2000 12:44:23 -0600563_iso-8859-1 Jeff, If you check closely, there is a way to have BMC delete the user-managed VSAM files during drop DB, if, in fact, they are user-managed files. Deleting non-VSAM vs VSAM files in ISPF 3.4 I believe has to different commands, I think its DEL for non-VSAM and DELETE for VSAM and make sure you enter the command at the Cluster level. 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 [...] 20448 65 27_Re: Deleting DB2 VSAM files12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU30_Fri, 4 Feb 2000 13:47:17 -0500645_iso-8859-1 Thank you for your reply. Jeff

-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Friday, February 04, 2000 1:44 PM To: DB2-L@RYCI.COM Subject: Re: Deleting DB2 VSAM files



Jeff, If you check closely, there is a way to have BMC delete the user-managed VSAM files during drop DB, if, in fact, they are user-managed files. Deleting non-VSAM vs VSAM files in ISPF 3.4 I believe has to different commands, I think its DEL for non-VSAM and DELETE for VSAM and make sure you enter the command at the Cluster level. HTH, Rick Davis "This e-mail and any files transmitted with [...] 20514 49 41_Re: Deleting DB2 VSAM files - do not use!0_23_Mike_Levine@TEKHELP.NET30_Fri, 4 Feb 2000 13:51:42 -0500327_us-ascii Hi,

DO NOT use this JCL for a 'normal' VSAM delete! This is to delete an orphaned VVR entry. In 3.4 type 'DEL' (not 'D') on the cluster component. As long as you have RACF authority and the dataset is not allocated to DB2 this should work.

Regards,

Michael Levine Premier Data Services, Inc. [...] 20564 65 41_Re: Deleting DB2 VSAM files - do not use!12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU30_Fri, 4 Feb 2000 13:55:16 -0500523_iso-8859-1 Thank you for your reply.

Jeff

-----Original Message----- From: Mike_Levine@TEKHELP.NET [mailto:Mike_Levine@TEKHELP.NET] Sent: Friday, February 04, 2000 1:52 PM To: DB2-L@RYCI.COM Subject: Re: Deleting DB2 VSAM files - do not use!



Hi,

DO NOT use this JCL for a 'normal' VSAM delete! This is to delete an orphaned VVR entry. In 3.4 type 'DEL' (not 'D') on the cluster component. As long as you have RACF authority and the dataset is not allocated to DB2 this should work. [...] 20630 104 36_Re: Read Only Access to DB2 Clusters9_Fox, Dave16_Dave_Fox@BMC.COM30_Fri, 4 Feb 2000 12:56:21 -0600688_ISO-8859-1 From the FWIW department: even if you consider doing native VSAM or media-manager access to the underlying clusters, you also need to consider the transient data integrity issues introduced by unwritten pages residing in the various buffer pools. I have applications that read the same sequence of pages repeatedly during a sub-second interval and saw inconsistent data in the form of incomplete row indirection, changed/added/deleted rows, and mismatched space maps. That doesn't even include trying to synchronize indexes to data, referential integrity updates across tables, and updates via the coupling facility. Also, don't even THINK about building your own indexes [...] 20735 178 12_explain this23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM30_Fri, 4 Feb 2000 14:12:08 -0500828_iso-8859-1 Hi All.

I have this query and i could really use some suggestions here. I am trying to look at it many ways but another opinion would be great. SELECT A.CUST_ARGT_ID , A.CMTMT_ADJ_FCTR , A.CMTMT_HIER_CD , A.CMTMT_TYPE_CD , A.DUPL_SUBP_ELG_IND , A.MLTPL_PNLTY_CD , A.PNLTY_NET_GRS_CD , A.RAMP_UP_ELGBL_IND , A.RTRG_NET_GRS_CD , A.SHRTFL_METH_CD , A.SHRTFL_PNLTY_RATE , A.SHRTFL_PROD_CD , A.TERMTN_METH_CD , A.TERMTN_PNLTY_RATE , A.TERMTN_PROD_CD , B.BILG_TRAN_CD , B.CLDR_RLTV_CD , B.CMTMT_METH_CD , B.FIT_CD , B.MSTR_SBPLN_NBR , B.PARNT_SBPLN_NBR , B.RTRG_ACCUM_CD , B.SBPLN_AMT_RLTV_IND , B.SBPLN_DURTN , B.SBPLN_END_DAY , B.SBPLN_END_MTH , B.SBPLN_END_YR , B.SBPLN_MAX_AMT , B.SBPLN_MIN_AMT , B.SBPLN_STA_DAY , B.SBPLN_STA_MTH , B.SBPLN_STA_YR , B.SBPLN_SUPERSET_ID , B.SUBP_FIT_CD , C.CNTRC_TARF_NBR , [...] 20914 26 36_Loading data in Partioned tablespace13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM30_Fri, 4 Feb 2000 14:34:19 -0500520_us-ascii We have a tablespace with 12 partitions . My question is regarding running utilities against this tablespace . Normally ( in simple or segmented tablespace ) , the level of locking during the execution of an utility is tablespace . In this case ,when we are running a load job against partion #1 , what is the object of the lock ? Is the whole tablespace locked or only part1 is locked ? If the lock is only on Part1 , then , can we execute another load job on Part2 at the same time ? Going a bit further [...] 20941 113 40_Re: Loading data in Partioned tablespace0_22_dhonea@TOTALSYSTEM.COM30_Fri, 4 Feb 2000 14:56:29 -0500446_iso-8859-1 The following should answer your questions, at least in part, and was taken from IBM's redbook on locking.

Partition independence provides parallel processing support on the different partitions of a partitioned table space and its related indexes. Partition independence allows parallel execution of all utility jobs, SQL applications, and DB2 commands as long as they address different partitions of a given table space. [...] 21055 99 29_Select MAX taking a long time9_Ken Paris17_KENPARIS@LVCM.COM30_Fri, 4 Feb 2000 11:52:47 -0800526_iso-8859-1 We have what looks like a simple query:

SELECT VALUE(MAX(column1) + 1, 1) FROM TABLE WHERE column1 > 0 WITH UR

When I execute the statement in SPUFI, it comes back right away. When it gets executed in a batch COBOL program, it sometimes takes up to a minute to get the answer (we are in initial testing, so there is nothing running at the same time - batch or online). During some of the long running occurrences, I've noticed Getpages and Read I/O's going up quite a bit in the Insight monitor. [...] 21155 13 24_Changing a DB2 System ID11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Fri, 4 Feb 2000 14:07:43 -0600366_us-ascii My management wants to rename one of our DB2 Systems from "DB2C" to "DB2F". What is the best way to do that?... : )

================================================ 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. 21169 39 28_Re: Changing a DB2 System ID24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Fri, 4 Feb 2000 15:21:06 -0800536_us-ascii Jeff, Dumb question: why does management care what the subsystem is called?

Frank



On Fri, 4 Feb 2000 14:07:43 -0600, Jeff Faughn wrote:

>My management wants to rename one of our DB2 Systems from "DB2C" to "DB2F". >What is the best way to do that?... : ) > >================================================ >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. [...] 21209 37 28_Re: Changing a DB2 System ID11_rick creech18_ykcirc@HOTMAIL.COM28_Fri, 4 Feb 2000 12:23:24 PST380_- FYI, There is a documented procedure for doing this in the Data Sharing Planning and Administration SC26-8961-00 in chapter 4. (It's in this manual because there are some recommended naming standards for a data sharing environment, and for some shops the only way to get close to the recommended names is to consider renaming the existing DB2 system.) Regards, Rick Creech [...] 21247 62 45_DB2 for MVS V4 to DB2 for OS/390 V5 Migration24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Fri, 4 Feb 2000 15:26:36 -0800567_us-ascii Hey list, I am posting this for a colleague in Peru. She wants to migrate a customer from V4 to V5, but there are some questions about the integrity of the current v4 code base. She asked me to validate her approach - which I would like you to do. Following her question is my counter-proposal which basically involved installing a brand-new DB2 V5 subsystem and using a tool like CA-Platinum, BMC, or Candle to migrate everything from old to new. Her concerns with that approach were a) the time involved (it's a 400GB subsystem) and b) the expense of [...] 21310 99 33_Re: Select MAX taking a long time14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Fri, 4 Feb 2000 15:25:41 -0500372_us-ascii



Is the index ascending or decending. If it is decending and this is the Pkey of the table then you should get a 1fetch index only read which is the very fastest I know of. Good Luck.







Ken Paris on 02/04/2000 02:52:47 PM

Please respond to DB2 Data Base Discussion List [...] 21410 30 26_History/Event table design11_learning DB20_thelist_us@YAHOO.COM30_Fri, 4 Feb 2000 12:29:43 -0800434_us-ascii Hi all, I need to design a table to hold some history/event information about the company I work for.This table data will be used for a 'history' link on our site. The table can be something like: (event_id , event_desc, event_date).

My problem is : how do I got about generating a specific event_id(this id would be key but would not appear on the site ofcourse)? Please share any experience anyone has in this. [...] 21441 87 49_Re: DB2 for MVS V4 to DB2 for OS/390 V5 Migration0_23_Mike_Levine@TEKHELP.NET30_Fri, 4 Feb 2000 15:51:47 -0500352_us-ascii Hi Frank,

It really doesn't matter what the maintenance level of the V4.1 subsystem is as long as the required fall back/toleration maintenance is applied. You would typically only consider your counter proposal if you are skipping one or more versions of DB2 for which an IBM migration path is not supported (i.e., V4.1 to V6.1). [...] 21529 13 30_Re: History/Event table design20_Laurence M Crouch Jr24_lmcrouch@DUKE-ENERGY.COM30_Fri, 4 Feb 2000 15:42:07 -0500380_us-ascii If no human needs to use the key for anything I would use a timestamp, not null with default, generated when the row is inserted.

================================================ 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. 21543 59 30_Re: History/Event table design19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Fri, 4 Feb 2000 14:56:59 -0600615_iso-8859-1 J.N., Why not just use the existing PK of the "current" table(s) on the "history" table(s). Did I miss the point????? 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, [...] 21603 40 27_Re: Deleting DB2 VSAM files13_Horton, Libby20_Libby_Horton@BMC.COM30_Fri, 4 Feb 2000 15:21:34 -0600421_ISO-8859-1 Hello, If you drop a database which has VCAT defined objects using BMC Change Manager the AMS commands to delete the VSAM clusters will be in the worklist.

Best regards, Libby Horton BMC Product Support 1-800-537-1813

-----Original Message----- From: Kalman, Jeff [mailto:JKalman@UNCH.UNC.EDU] Sent: Friday, February 04, 2000 12:00 PM To: DB2-L@RYCI.COM Subject: Re: Deleting DB2 VSAM files [...] 21644 57 33_Re: Select MAX taking a long time14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 4 Feb 2000 15:35:08 -0500426_iso-8859-1 Ken,

More than likely, when running SPUFI, the index pages were already in the buffer pool and the query zipped along. When the pages have to be completely staged, then it would take longer. But that it not the problem. The query as written will scan the entire index everytime since it is doing a MAX. You need a descending index on COLUMN1. Second, just for consistency, change the VALUE to COALESCE. [...] 21702 24 53_CA's Technology Forum for DB2 for OS/390 - World Tour13_Thomas, Janis19_Janis.Thomas@CA.COM30_Fri, 4 Feb 2000 16:27:18 -0500626_iso-8859-1 Begin the new Millenium with a technical seminar that could help you and your organization for years to come! The latest and greatest on how to take your DB2 for OS/390 systems to the limit! CA's Technology Forum for DB2 for OS/390 seminars offer you and your organization the opportunity to receive world-class technical training at a location near you. Because CA is committed to DB2 and its users around the world, we continue to bring you these seminars absolutely FREE of charge. Conducted by one of CA's DB2 for OS/390 technical specialists, these tech-intensive, practical classes offer professionals a [...] 21727 52 22_DB2 Row Level Security10_Wang.James24_Wang.James@AAA-CALIF.COM30_Fri, 4 Feb 2000 14:01:05 -0800220_us-ascii How can I control user access to the rows in a DB2 table? Can RACF, TopSecret or ACF2 do it? Or, Is there interface software for this?

James Wang Wang.James@aaa-calif.com 21780 34 57_Re: CA's Technology Forum for DB2 for OS/390 - World Tour0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 4 Feb 2000 16:02:23 -0600411_us-ascii Nice to see the new schedule, not so nice to see that we "got snowed out". We were within a couple of days of having one of the Platinum presentations last June when the series was cancelled by the sellout , sorry merger. So, after the major population centers are done in the current tour, is there any chance of the tour being extened into 'smaller' centers (like Winnipeg Manitoba, Canada)? [...] 21815 75 26_Re: DB2 Row Level Security0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 4 Feb 2000 16:21:03 -0600501_us-ascii I don't know if the security tools can do it, I don't think so. But how about creating views that explicity include or exclude groups of data. If there are no natural criteria to restrict row access, you could add a 'security' column and define your own security categories. i.e security = 0 everyone can view security = 1 everyone can view,delete, update = 2, RACF group 'b' can view = 3, RACF group 'c' can view, delete, update = 4, RACF group 'd' can insert, view, delete, update etc [...] 21891 26 26_Re: DB2 Row Level Security16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Fri, 4 Feb 2000 15:39:15 -0700423_- Use VIEWS.

> -----Original Message----- > From: Wang.James [SMTP:Wang.James@AAA-CALIF.COM] > Sent: Friday, February 04, 2000 3:01 PM > To: DB2-L@RYCI.COM > Subject: DB2 Row Level Security > > How can I control user access to the rows in a DB2 table? > Can RACF, TopSecret or ACF2 do it? > Or, Is there interface software for this? > > James Wang > Wang.James@aaa-calif.com > [...] 21918 35 61_DB2 for MVS V4 to DB2 for OS/390 V5 Migration - clarification24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Fri, 4 Feb 2000 18:01:37 -0800356_us-ascii Here is an updated description of the problem:

>Assume I have this DB2/MVS v4.1 subsystem with problems. >Data is OK. >Is it possible to define a new DB2 subsystem (v4.1) and change DSNZPARM or >wherever it's defined, the references to data sets holding data ? >Would I be able to read data in the database from the new DB2 subsystem? [...] 21954 25 21_recovery history file8_Hao Wang14_Haow88@AOL.COM28_Fri, 4 Feb 2000 18:15:49 EST340_US-ASCII Hi there,

I am new to the DB2. I currently running my db2 on linux system. I am try to create our recovery plan. I have a few of questions.

1. I can restore a database without the DB2 recovery history file. Right?

2. Is the DB2 recovery history file a UNIX file? If it is, what is the name and directory? [...] 21980 169 12_explain this14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sat, 5 Feb 2000 11:23:05 +1000504_us-ascii Kaushal,

For such small tables (< 1000 rows) your 8 minutes of CPU seems incredibly bad performance.

Its not easy to see why.

The important statistics would be Index Clusterratios, and index column cardinalities.

BTW, Attachments are not permiited on the List Serv. Everyone is scared of viruses. In future, you should compress your Explain output to be more compact, omit the unnecessary columns like CREATOR, APPLNAME, PROGNAME, etc. and include in the Email. [...] 22150 59 40_Re: Loading data in Partioned tablespace17_Patricia Candella21_pcandella@METLIFE.COM30_Fri, 4 Feb 2000 14:50:36 -0500496_us-ascii Jaydeep, I can tell you that for DB2 OS/390 - version 4, you can run loads into the different partitions concurrently ONLY IF you do not have any global indexes built over the tablespace - when you have global indexes, - you must "single-thread" the load jobs - one partition at a time. If you only have a clustered index over the partitioned tablespace, then you can run the loads concurrently; the partitions that are not being affected by the load will remain available for use. [...] 22210 94 40_Re: Loading data in Partioned tablespace14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 4 Feb 2000 22:13:42 -0500390_iso-8859-1 Full partition independence between applications and utilities depends on whether you are running IBM's utilities or others, as well as what the applications are doing. There is still the issue with partition CLAIMS and DRAINS that IBM can use (their patent, no external API), and other non-IBM utilities. It is a small issue but one that has blindsided a couple companies. [...] 22305 40 26_Re: DB2 Row Level Security11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Sat, 5 Feb 2000 05:59:42 -0500602_us-ascii Mullins' book suggests using stored procedures to restrict access to rows/columns. We're a university and I believe this approach will work quite well for us. Ex. Academic departments want to update their own courses but let others only see those courses. We could code a stored procedure to:

1) Pick off all the RACF groups to which the caller is connected 2) If resource groups CRSEaaaa are defined, pick off the 5th-8th characters. ex. CRSEMATH, we now have 'MATH' as the determining row qualifier. 3) For UPDATE purposes, show only the rows with 'MATH' as the department name. [...] 22346 11 0_9_Ric Barry20_ricbarry@NETTAXI.COM30_Sat, 5 Feb 2000 12:15:13 -0600265_us-ascii SIGN-OFF DB2-L

================================================ 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. 22358 76 38_DB2 Zone on VisualAge Developer Domain13_Morrill, John12_JohnM@VP.NET30_Sat, 5 Feb 2000 14:37:53 -0700380_iso-8859-1 Dear DB2 Interested Parties,

Below is a bulletin about the "VisualAge Developer Domain" web site, and a view on the web site called "DB2 Zone", which provides easy access to the latest information on Java programming for DB2...check it out!

Regards, Mike + + + + + + +

DB2 Zone on VisualAge Developer Domain: resources for all your Java needs [...] 22435 76 26_Re: Improved DB2 Utilities12_Doug Clifton16_dclifton@EV1.NET30_Sat, 5 Feb 2000 16:23:49 -0600554_us-ascii DSNTIAUL is not an Unload product. It is a sample program supplied by IBM to give an example of how to write code accessing DB2. It was convenient to use this program for unloads when we had nothing else at the time and many people just continued to use it for mass selects (i.e. unloads). This is the reason we are selling HP Unload, because the base utilities did not include an unload. The DB2 Utilities lab told everyone at IDUG and at the Tech Conference last year that they are fully committed to improving the DB2 utilities. I think [...] 22512 75 38_Repost: TDAN.com February Announcement13_Morrill, John12_JohnM@VP.NET30_Sun, 6 Feb 2000 01:45:35 -0700456_iso-8859-1 Re: The Data Administration Newsletter -- http://www.tdan.com

The Data Administration Newsletter issue 11.0 was posted on the 'net on January 1. Issue 11.0 has been well-received ... 160k+ hits in January alone. If you haven't visited this month, TDAN 11.0 contains new articles on data warehouse engineering, web-mining, knowledge management, metadata, stewardship, ... more. The archive contains 110+ articles on similar subjects. [...] 22588 38 13_signoff db2-l6_Bianca17_bianca@OAKWEB.COM30_Sun, 6 Feb 2000 10:11:49 -080019_iso-8859-1

22627 67 11_REXX SMF 4215_Jim Lewandowski22_jlewand@STARNETINC.COM30_Thu, 3 Feb 2000 12:56:47 -0600313_us-ascii I wish to respond to all those that either received a copy of a REXX EXEC that processes SMF 42, subtype 6 records (dataset I/O performance) in the past -or- responded most recently concerning another of my offers. SolarFlare software has had some discussions lately that we wish to share with you. [...] 22695 57 27_CA/Platinum PDA observation10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV30_Sun, 6 Feb 2000 15:08:03 -0600375_iso-8859-1 All,

DB2 v5.1 - Data Sharing (6 members) MVS OS/390 2.6 CA/Platinum V97F

Currently we execute PDA statistics every day because the net insert/delete volume is between 3 - 8.5 million rows. We also export the stats to the test lpars so that the developers receive approximately the same access paths as their application would get in production. [...] 22753 49 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM30_Sun, 6 Feb 2000 15:32:52 -0600542_- Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.multimania.com/db2usa/eindex.htm - French : http://www.multimania.com/db2usa/index.htm

Last updated on Saturday, February 5th 2000 ===========================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm DB2 Today, IBM's DB2 newsletter : January 2000.

SAS/ACCESS with DB2 : on line documentation.

Lista UM DB2 : Discussion list in Portuguese about DB2 (Brazil). [...] 22803 17 20_Welcome back, Dr. E.12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Mon, 7 Feb 2000 11:25:16 +1300360_us-ascii Hi Dr. E. Welcome back.



RB DB2 DBA IBM NZ :o) (God, I can't believe I just used a 'smiley'...)

================================================ 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. 22821 44 20_Re: DSNZPARM COMPACT14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 7 Feb 2000 13:36:34 +1000444_us-ascii John

The first tape drives with compression could only decompress a block by reading it forwards. If a rollback has to read archived tapes, DB2 reads the tape backwards - which is a standard tape facility. Since compression means the data has to be read forwards, the action is: read a block forwards, skip back two blocks, read a block forwards, skip back two blocks, and so on. The direction changes make this very slow. [...] 22866 84 50_Truncated digests (Off topic post - Mail Trailers)14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 7 Feb 2000 14:06:39 +1000589_us-ascii Peggy,

I investigated this problem several years ago. (You can skip to the FINAL CONCLUSION if the bit in the middle is hard going.) What I found was Internet emails using SMTP require:

- Emails are transmitted as a set of text lines less than 81 characters wide, with a subset of characters in the range x'20' to x'7e' and lines terminated by, I think, x'0a'. To send text that doesn't conform to this requires MIME encoding using UUENCODE, quoted stings etc. Normally the conversions to and from internet valid data and what you see on your screen is handled [...] 22951 74 36_Re: Read Only Access to DB2 Clusters14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 7 Feb 2000 14:16:50 +1000400_us-ascii Greg,

Ah, the joys of the rolled-it-myself interface. The delights of finessing bits and bytes. The nightmares when they forget to ask your permission before before changing the internals - you know, the stuff you got out of the Diagnosis Guide and Reference which also says "Attention: Do not use this Diagnosis, Modification, or Tuning Information as a programming interface." [...] 23026 76 15_Re: REXX SMF 4210_David Cohn19_dcohn@ATTGLOBAL.NET30_Sun, 6 Feb 2000 19:26:31 -0800582_us-ascii Hi Jim, How about "I/O MASTER for DB2"? Regards, Dave

Jim Lewandowski wrote:

> I wish to respond to all those that either received a copy of a REXX > EXEC that processes SMF 42, subtype 6 records (dataset I/O performance) > in the past -or- responded most recently concerning another of my > offers. SolarFlare software has had some discussions lately that we > wish to share with you. > > Because SolarFlare Software is a very small software company, we can be > a litte more non-traditional than the larger software companies. It was > because of this [...] 23103 129 31_Re: CA/Platinum PDA observation22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Mon, 7 Feb 2000 06:06:23 +0000343_ISO-8859-1 Daniel - did not have the exact same problem, but one similar. A friend at my current site last year used PDA to copy the stats from our production environment to our QA environment - this cuased DB2 to Abend as PDA was managing to set serious incorrect values in some of the the DB2 catalog tables - in particular SYSCOLDIST. [...] 23233 21 15_Re: REXX SMF 4214_Massimo Scarpa16_mscarpa@CESVE.IT30_Mon, 7 Feb 2000 10:44:09 +0100457_us-ascii Why not :

SMF Extended Rexx Reporter for DB2 = S.E.X.R for DB2 ? or

SMF Extended Rexx I/O Reporter for DB2 = S.E.X.IO for DB2 ? ; -)))



Max Scarpa

PS it's only a joke.....

================================================ 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. 23255 23 33_ODBC, Stored Procedures & DB2 4.141_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR30_Mon, 7 Feb 2000 09:03:09 -0200565_iso-8859-1 We use DB2 OS/390 V4.1 with CAE V2.3. Is it possible to call a stored procedure using ODBC? How shall we proceed?

Thank you for your help.

Claudio ************************************* Claudio Meluzzi Mendes BEG - Banco do Estado de Goias Goiania - GO - Brasil *************************************

================================================ 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. 23279 20 27_Syslgrnx record description14_Massimo Scarpa16_mscarpa@CESVE.IT30_Mon, 7 Feb 2000 12:20:50 +0100465_us-ascii Can anybody suggests where I can find a description of SYSLGRNX record structure

(unloaded by VSAM REPRO) ? I need it to develop some REXX tools.

Thanx in advance

Max Scarpa 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. 23300 118 16_Re: Partitioning14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE30_Mon, 7 Feb 2000 12:43:37 +0100583_us-ascii Hi Ray & Phil,

since it was us that had the problems with an application updating the partitioning key let me add a few comments:

the afore-mentioned application updated the partitioning key when a there was a change of address; this includes a change of one's name as well. As it so happens, 'name' is the first column of the index, 'town' or 'street' or 'postal code' are parts of the part. key as well, but not within the 40-byte-range that is actually used for partitioning. Everytime there was a change of 'postal code' or 'street' or ... the update [...] 23419 48 31_Re: Syslgrnx record description19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Mon, 7 Feb 2000 07:09:11 -0600627_iso-8859-1 Hi Max, Layout's in Diagnosis Gde. Probably better use DSN1PRNT utility rather than REPRO. 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 [...] 23468 14 31_Re: Syslgrnx record description14_Massimo Scarpa16_mscarpa@CESVE.IT30_Mon, 7 Feb 2000 14:26:19 +0100299_us-ascii I got it. Thanks for infos....

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. 23483 73 65_Re: DB2 for MVS V4 to DB2 for OS/390 V5 Migration - clarification11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Mon, 7 Feb 2000 08:57:58 -0500565_iso-8859-1 This is not a good idea. The problem is the assumption that the code and the data are separate, disconnected entities. They are not. They are expected to be at the same level or at a level that is tolerated.

Even though the data (and by default the catalog) may be "ok", that does not mean that the "latest" DB2 code will be happy with it. In the course of maintaining application is not uncommon to run "catmaint". This changes the DB2 catalog to coordinate with the code changes. In addition, changes are also made to "new" user objects as [...] 23557 39 49_what constitutes a unit of work (UDB EEE on AIX)?0_25_Pierre.Evans@AUTOZONE.COM30_Mon, 7 Feb 2000 08:31:27 -0600456_us-ascii Listers, Would the following script, executed on AIX by using "db2 -tvf script.ddl" be treated as one complete unit of work, or as several distinguished by the commit statements ?

- - script.ddl delete from TABLE where condition1....; commit; delete from TABLE where condition2....; commit; delete from TABLE where condition3....; commit; delete from TABLE where condition4....; commit; delete from TABLE where condition5....; commit; [...] 23597 23 16_Re: PRIQTY limit24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM30_Mon, 7 Feb 2000 09:33:25 -0500546_iso-8859-1 Hi all - We tried an ALTER of some tablespaces that have grown since initial implementation where the ts had grown to over 20 extents. My space calculations exceeded the 4194304 stated in SQL Ref so I lowered PRIQTY to 4000000. The ALTER ran ok but reorg failed because (apparently) a 3390 mod3 only has 2,838,016kb per device (so my charts indicate). We eventually had to ALTER the value down and restart the reorg - no lives lost . BUT what is at work here with these 2 values ? According to my chart, only a 3390 mod9 has more [...] 23621 115 26_Re: Improved DB2 Utilities15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Mon, 7 Feb 2000 09:44:52 -0500593_iso-8859-1 Doug,

Why did IBM not include an unload utility within their utility suite for DB2 ? Why can't they include this new HP Unload as the missing unload utility which most of us feel they should have included in the first place instead of charging separately for this new utility? I use DSNTIAUL as my unload utility and will continue to do so unless IBM can supply an adequate unload utility as part of their utility deliverables. I know these questions are probably more related to marketing issues than product development -- but hopefully you or someone in your company [...] 23737 59 16_Re: PRIQTY limit22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Mon, 7 Feb 2000 15:02:29 +0000456_ISO-8859-1 Frank

Max value you can allocate on a single mod3 3390 is, as you say, 2.84gig (3339 cyls).

DB2 cannot allocate anymore than 2gig in anyone single dataset .. so there is little point in trying to allocate above this value for an extent ... I would suggest a trategy of 2gig(if you need 2gig - i.e it will get used) for PRIQTY and then SECQTY that will mean you use all the remaining 0.8gig on the packs .. . just one example. [...] 23797 16 16_Re: PRIQTY limit14_Larry D Bolick24_ldbolick@DUKE-ENERGY.COM30_Mon, 7 Feb 2000 09:58:01 -0500367_us-ascii Why not partition the tablespace into 20 or more partitions. This will provide smaller VSAM objects that are easier to manage from a utility (REORG, COPY, etc) standpoint. Will also enable the ability to use CPU and I/O parallelism with queries that access the table. With DB2 V5, a LARGE tablespace can be partitiioned into 254 parts. Hope this helps. [...] 23814 99 24_Re: Access path problems0_19_Tim.Lowe@STPAUL.COM30_Mon, 7 Feb 2000 09:20:42 -0600313_us-ascii Would RUNSTATS "TABLE ALL INDEX(ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10) UPDATE ALL" have deleted the earlier older set of entries in SYSCOLDIST? In other words, would runstats have deleted ALL rows for this column in SYSCOLDIST in this case, or just selected rows that matched what it was creating? [...] 23914 26 25_Explain / BIND difference0_26_denzil.coulter@UNICIBLE.CH30_Mon, 7 Feb 2000 16:31:47 +0100374_us-ascii Has anyone encountered a similar problem recently?

DB2 V5 PUT9944 OS/390 V2.6

An explain of a 4-way table (inner-)join shows that each table is accessed via an index with MATCHCOLS=1. This is the preferred acces method. After the (RE)BIND with EXPLAIN=YES we see in the PLAN_TABLE that the first table will now be accessed via a TABLESPACE SCAN. [...] 23941 46 29_Re: Explain / BIND difference9_Mark Ruhe15_mruhe@QUEST.COM30_Mon, 7 Feb 2000 07:47:45 -0800313_iso-8859-1 One of the most commonn causes of access path differences between dynamic explain and the explains produced by binds are host variables. Without seeing your SQL it is impossible to say why this is occuring however I assume host variables are present in the SQL? Mark Ruhe Developer Quest Software [...] 23988 58 29_Re: Explain / BIND difference0_19_mike.holmans@BT.COM30_Mon, 7 Feb 2000 15:49:30 -0000387_- The obvious question is whether the stand-alone explain and the rebind explain were done with exactly the same statements in exactly the same environment (ie not only the same catalog stats, but also on the same processor, because the optimizer may decide that a fast processor will be able to get the results out faster by ts scan than by index scan followed by data retrieval). [...] 24047 59 28_Re: Changing a DB2 System ID11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Mon, 7 Feb 2000 10:21:37 -0600703_us-ascii Thanks!... : )







From: rick creech on 02/04/2000 02:23 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Jeff Faughn/MDSC/MAYCO)

Subject: Re: Changing a DB2 System ID







FYI, There is a documented procedure for doing this in the Data Sharing Planning and Administration SC26-8961-00 in chapter 4. (It's in this manual because there are some recommended naming standards for a data sharing environment, and for some shops the only way to get close to the recommended names is to consider renaming the existing DB2 system.) Regards, Rick Creech [...] 24107 10 0_18_Epps, Stephanie P.14_SPEpps@RMC.COM30_Mon, 7 Feb 2000 11:41:00 -0500253_iso-8859-1

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 24118 36 11_CAE Support13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM30_Mon, 7 Feb 2000 12:35:58 -0500358_us-ascii Checked the archives, and thought List members might have more recent info on this.

How are you supporting DB2Connect CAE? Specifically, the CAE desktop component. We have several client / server and web / internet / intranet applications that connect to DB2 for OS/390 using DB2Connect, including PeopleSoft, ASP pages, MS Access, etc. [...] 24155 85 28_Re: Changing a DB2 System ID22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Mon, 7 Feb 2000 17:37:22 +0000301_ISO-8859-1 Hi all .. (hi Rich)

I just did this last weekend in our QA enviroment in readiness for me switching Datasharing on next weekend .. mail me direct I can send you a copy of my procedures .. they may do more than you want - but all the same .. you can get out of it what you want. [...] 24241 64 37_Re: ODBC, Stored Procedures & DB2 4.10_15_leon@CA.IBM.COM30_Mon, 7 Feb 2000 13:24:48 -0500470_iso-8859-1





DB2 Connect comes with an on-line book called "DB2 CLI Guide and Refference" that has a seaparte chapter on Stored Procedures.

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



Cláudio Meluzzi Mendes on 02/07/2000 06:03:09 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: ODBC, Stored Procedures & DB2 4.1 24306 56 53_Re: what constitutes a unit of work (UDB EEE on AIX)?11_Steve Mazer17_smazer@FMTUSA.COM30_Mon, 7 Feb 2000 13:58:58 -0500663_us-ascii Pierre, The situation you describe would be multilple units of work. Even if autocommit was turned off, your explicit commit statements would override it. How are you calculating the amount of log space required? Are you including space reserved for a rollback, if required?

Regards, Steve Mazer Fourth Millennium Technologies

At 08:31 AM 2/7/00 -0600, you wrote: >Listers, >Would the following script, executed on AIX by using "db2 -tvf script.ddl" >be treated as one complete unit of work, or as several distinguished by >the commit statements ? > >- - script.ddl >delete from TABLE where condition1....; >commit; >delete from TABLE [...] 24363 93 53_Re: what constitutes a unit of work (UDB EEE on AIX)?0_25_Pierre.Evans@AUTOZONE.COM30_Mon, 7 Feb 2000 14:21:42 -0600471_us-ascii Steve/Paul, Thanks for responding. We're pretty sure we've got the logs sized accurately accounting for table & index size,# of rows,space for before and after images (in case of rollback) plus adjustments for table & index row headers,index page split and a factor for skewness across the nodes. This calculation has been highly accurate since we've started using it, we have no doubts about it and have verified it with IBM developers & Fourth Millenium. [...] 24457 51 15_Non-DB2 Utility12_Matt Yardley19_yardley@MCMASTER.CA30_Mon, 7 Feb 2000 15:44:07 -0500287_us-ascii Sorry for this being off topic, but, we had a Y2K hit whereby a small utility we used stopped working. The company that supplied it went belly up a while ago, but, since it worked and there were no indications of license limitations we kept on using it for what we needed. [...] 24509 31 25_Triggers programming help11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Mon, 7 Feb 2000 16:31:39 -0500457_us-ascii Here's a TRIGGER I have created on V6 of DB2 UDB for OS390.

CREATE TRIGGER DVLP.T5 AFTER INSERT ON SCMPRSNT REFERENCING NEW AS NROW FOR EACH ROW MODE DB2SQL WHEN (PERSON_ID = 'P009933158') BEGIN ATOMIC INSERT INTO SCMAFILT VALUES('P009933158', 'EMPLOY', 'JLUTHMA', CURRENT_TIMESTAMP); END

Unfortunately, the SQL never gets registered, I think. When I ask DB2 Admin Tool to show me the SQL from package 'T5', it shows only this.. [...] 24541 90 19_Re: Non-DB2 Utility14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU30_Mon, 7 Feb 2000 16:23:11 -0500360_us-ascii As long as we're off-topic already, I hope you won't mind my question: What does this utility have to do with dates that would cause it to stop working because of a Y2K hit?







Matt Yardley cc: Sent by: DB2 Subject: Non-DB2 Utility Data Base Discussion List [...] 24632 17 36_DB2 Connect Personal Edition and MTS16_Swinski, Kenneth32_KSwinski@INTERNAL.MASSMUTUAL.COM30_Mon, 7 Feb 2000 16:26:26 -0500347_us-ascii Does DB2 Connect Personal Edition support Microsoft Transaction Server (MTS) applications connected to S/390 over TCP/IP? I'm trying to set up a comparison of another vendor's ODBC drivers that pass through several gateways and CICS before getting to DB2 vs. DB2 Connect's ODBC drivers. Personal Edition is all I could get for free. [...] 24650 35 29_Re: Triggers programming help12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 7 Feb 2000 15:42:22 -0600658_us-ascii I'm not testing my "AFTER" triggers yet, but could it be that the syntax of:

INSERT INTO SCMAFILT VALUES('P009933158', 'EMPLOY', 'JLUTHMA', CURRENT_TIMESTAMP);

Should be changed to:

INSERT INTO SCMAFILT (col1, col2, col3, col4) VALUES('P009933158', 'EMPLOY', 'JLUTHMA', CURRENT_TIMESTAMP);

I have "Insert" SQL (not in a trigger) that works following the second model.

Good luck!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Steve Grimes Washington University Information Systems OS/390 V2R6 DB2 UDB 6.1 W: 314-935-4376 H: 314-928-5005 Isa.64:6 Steve_Grimes@aismail.wustl.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ [...] 24686 103 19_Re: Non-DB2 Utility12_Matt Yardley19_yardley@MCMASTER.CA30_Mon, 7 Feb 2000 16:48:14 -0500546_us-ascii It seems there is some sort of date checking for licensing, even though there was no mention of it in documentation, output etc. and we bought it outright.

Harvey Wachtel wrote: > > As long as we're off-topic already, I hope you won't mind my question: > What does this utility have to do with dates that would cause it to stop > working because of a Y2K hit? > > Matt Yardley > STER.CA> cc: > Sent by: DB2 Subject: Non-DB2 Utility > Data Base > Discussion > List > OM> > > [...] 24790 65 32_FW: Determining DB2 Object Sizes15_Lentine Chuck O23_Chuck.O.Lentine@IRS.GOV30_Mon, 7 Feb 2000 15:58:54 -0500387_iso-8859-1

> Our data center is running DB2 version 5 on an OS/390 system and we would > like to know if anyone has a tool for monitoring object sizes. We would > prefer a freeware product. The tool would display/print the size of each > partition. That way we could plan maintenance tasks such as adding > partitions and defining large tablespaces. > > > TIA > Chuck Lentine > > 24856 111 53_Re: what constitutes a unit of work (UDB EEE on AIX)?11_Steve Mazer17_smazer@FMTUSA.COM30_Mon, 7 Feb 2000 15:38:21 -0500624_us-ascii Pierre, What rolled back when the logs became full? Did all of the deletes or ust some of them? This should answer the UOW question. Was there anything else going on at the time that might have caused more logs to be considered active?

Regards, Steve Mazer Fourth Millennium Technologies

At 02:21 PM 2/7/00 -0600, you wrote: >Steve/Paul, >Thanks for responding. We're pretty sure we've got the logs sized >accurately accounting for table & index size,# of rows,space for before and >after images (in case of rollback) plus adjustments for table & index row >headers,index page split and a factor [...] 24968 77 36_Re: FW: Determining DB2 Object Sizes0_31_truman.g.brown@BELLATLANTIC.COM30_Mon, 7 Feb 2000 17:16:13 -0500751_us-ascii



Chuck,

The freeware you want is (I)SPF option 3.4 with wildcards to include the datasets.

George









"Lentine Chuck O" on 02/07/2000 03:58:54 PM

Please respond to "DB2 Data Base Discussion List"

To: DB2-L@RYCI.COM cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl) Subject: FW: Determining DB2 Object Sizes







> Our data center is running DB2 version 5 on an OS/390 system and we would > like to know if anyone has a tool for monitoring object sizes. We would > prefer a freeware product. The tool would display/print the size of each > partition. That way we could plan maintenance tasks such [...] 25046 112 36_Re: FW: Determining DB2 Object Sizes0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 7 Feb 2000 16:35:50 -0600358_us-ascii Search the archive for "LISTCAT". There was a discussion about your question (back in November-December ) and using LISTCAT in batch jobs.

Here is a copy of one of the posts in that discussion (Re: FW: Space calculation)

One easy way to calculate the number of active pages is to extract the information from the ListCat command. [...] 25159 158 53_Re: what constitutes a unit of work (UDB EEE on AIX)?0_25_Pierre.Evans@AUTOZONE.COM30_Mon, 7 Feb 2000 16:51:08 -0600498_us-ascii Steve, we can't determine what deletes went through as the tables had to be blown away. Here's the scenario:

2 jobs running simultaneously : the forementioned deletes, and an autoloader process loading data across all nodes. The autoloader doesnt log the data being loaded, so technically the logs should should only have been updated with the deletions. A logfull situation occurred, and we suspect that at some point at least one of our nodes went off the high speed switch. [...] 25318 166 54_db2 os/390 v5.1 - Escalation of Incr Copy to full copy0_16_acaldwe2@CSC.COM30_Tue, 8 Feb 2000 10:08:37 +1000408_us-ascii The problem I reported last Friday happened again this morning. Here is the sequence of events and some detailed info. Running DB2 for OS/390 V5.1 .

JOB 1 Incremental Copy Statement DSNU050I DSNUGUTC - COPY TABLESPACE . DSNUM ALL COPYDDN(SYSCOPY1,SYSCOPY2) FULL NO SHRLEVEL CHANGE Error message DSNU016I DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'0B37' [...] 25485 61 53_Re: what constitutes a unit of work (UDB EEE on AIX)?12_Kent Collins15_kcollins@WF.NET30_Mon, 7 Feb 2000 17:10:05 -0600665_iso-8859-1 Several each completing with the commit.

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net ----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Monday, February 07, 2000 8:31 AM Subject: what constitutes a unit of work (UDB EEE on AIX)?



> Listers, > Would the following script, executed on AIX by using "db2 -tvf script.ddl" > be treated as one complete unit of work, or as several distinguished by > the commit statements ? > > - - script.ddl > delete from TABLE where condition1....; > commit; > delete from [...] 25547 210 58_Re: db2 os/390 v5.1 - Escalation of Incr Copy to full copy15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU30_Tue, 8 Feb 2000 09:55:35 +1000324_us-ascii Allan,

What happens between JOB1 and JOB2 ? I suspect you just terminate the utility and then rerun the job with the increased file sizes. If this is so then the tablespace is left in a copy pending state, due to JOB1 failure. If the tablespace is in copy pend, the copy will revert to Full image copy. [...] 25758 12 21_CA/Platinum Education0_19_valeriet@US.IBM.COM30_Mon, 7 Feb 2000 19:45:36 -0500315_us-ascii Does CA/Platinum still have its DB2 education program/offerings?

================================================ 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. 25771 67 29_Re: Triggers programming help14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Tue, 8 Feb 2000 13:09:30 +1000472_us-ascii Joe,

I see the same effect (Admin Tool displays no stmts for the package of a trigger). But they show up if I do a select directly from syspackstmt.

One possibility is that when you created the trigger, current sqlid was not the tables' owner. (The schema name for the trigger (DVLP) is _not_ used to qualify unqualified table names!! You either have to qualify the table names, or issue a SET CURRENT SQLID.) Check sysibm.systriggers.tbowner. [...] 25839 174 32_TO: Eileen Zakareckis, Tech Week9_jim.szabo22_jim.szabo@MCIWORLD.COM30_Mon, 7 Feb 2000 21:45:06 -0500394_iso-8859-1 Northeast Ohio Data Users Group Discusses Peoplesoft and DB2

The Northeast Ohio Database Users Group (NEODBUG) will hold its quarterly meeting on THURSDAY, FEBRUARY 17, 2000, at the Hilton South Independence, 6200 Quarry Lane in Independence, OH (south of Cleveland).

We are pleased to present topics that should be of interest to all area database professionals: [...] 26014 27 21_regarding datasharing17_ravi kumar hassan18_ravibh@HOTMAIL.COM28_Mon, 7 Feb 2000 18:54:12 PST695_- Hello Everybody

i have a doubt datasharing environment and normal DB2 environment. can somebody explain about what and where exactly they will use datasharing environment. is this datasharing is diff. from normal db2 environment and how?.

can somebody explain in this issue.

Thanks in advance.

Ravi



______________________________________________________ Get Your Private, Free Email 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.