1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2001, week 2 2 20 40_Re: RC/Comapare, RC/Migration re-visited10_Alan Smith25_alancsmith@BTINTERNET.COM30_Sun, 8 Apr 2001 09:55:49 +0100526_us-ascii If you're doing a Quick Migrate you can choose UPDATE OPTIONS and set RI (Local/Global) to 'N' - this will mean it won't include other tables because of RI.

RC/Compare doesn't have this option, which implies that it won't include stuff because of RI.

Alan Smith

================================================ 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. 23 23 91_Re: Does anyone have a method of turning off the "RETVLCFK=YES" option for specific queries12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Sun, 8 Apr 2001 08:25:01 -0500300_us-ascii Larry:

Has what I sent you worked? I have no means of testing here without going through a lot of set up. I have been experimenting with the hints switch, but it does not work like the others. This increases my interest in know if what I sent you worked.

GSH 888-494-9019 [...] 47 99 82_Re: -440 when invoking DB2 stored procedure thru Powerbuilder and DB2 Connect v7.110_teldb2kals22_teldb2kals@TELSTRA.COM30_Mon, 9 Apr 2001 11:40:02 +1000427_us-ascii Hi Ramesh,

I am not sure what exact problems u r facing. We had some problems initially, but that was due to some coding errors. We have got the hang of it now. I have found the the PATH parameter in the connect string is used for stored procedures also.

I am pasting a copy of the connect string we use. (we r using DB2 Connect v5.2, but r upgrading to v7.1, and havent found any differences yet). [...] 147 56 17_24x7 Requirements11_Paul Traney22_paultraney@HOTMAIL.COM30_Sun, 8 Apr 2001 22:24:41 -0500600_iso-8859-1 Bob,

Sorry, no offense.....but that's not the response I was looking for. As my initial post stated, I am looking for solid EVIDENCE from experienced users who are USING a true 24x7 solution on a daily basis for reorgs and copies......regardless of who you're using. Is your prompt to contact my BMC rep an indication that you are you using BMC? If so, what are your experiences with true 24x7? Is it truly 24x7? Is there any downtime whatsoever (DB2 log apply, Renames, Data-sharing, etc.)? These are the areas that I need to investigate. Can you share any details concerning [...] 204 140 21_Re: 24x7 Requirements5_chris22_hhardy1@HOUSTON.RR.COM30_Sun, 8 Apr 2001 22:48:55 -0500588_iso-8859-1 Camron in Houston employes 24x7 environment meeting business unit needs around the world becuase of the different time zones the outage window is null or very small..... Many installations have/has 24x7 dating back to mid 70's because of business unit requirements nothing to do with this web - internet stuff..... I can supply you with a bunch per my 25 years in this data processing field if you want it.... ----- Original Message ----- From: Paul Traney Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Sunday, April 08, 2001 10:24 PM Subject: 24x7 Requirements [...] 345 92 15_WAIT DRAIN LOCK12_Steve Howell23_HOWELLS@STGEORGE.COM.AU30_Mon, 9 Apr 2001 14:46:16 +1000393_us-ascii Hi,

Since upgrading DB2 to version 6 we have noticed a few application programs executing as started tasks spending a lot of time in WAIT-DRNLOCK according to OMEGAMON II for DB2 Version 500. This does not seem to be causing the application any major delays. Partitioned tablespaces are involved but the partitioned keys are not being updated. Has anyone else seen this ? [...] 438 22 49_Allen Tsygan/PSG/Prudential is out of the office.12_Allen Tsygan23_allen_tsygan@PRUSEC.COM30_Mon, 9 Apr 2001 01:00:04 -0400586_us-ascii I will be out of the office from 04/06/2001 until 04/16/2001.

I will respond to your message when I return. If you need immediate DB2 assistance please call: ELLIOT COHEN 212-778-2778 REGGIE CULPEPPER 212-778-5319 HOWARD HIMY 212-778-2627 MARK MONTALBANO 212-778-3309 EMAN ABDU 212-778-3362

Not necessarily in that order.

================================================ 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. 461 59 19_Re: WAIT DRAIN LOCK15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Mon, 9 Apr 2001 16:31:02 +1000660_- Hi Steve,

Just a suggestion, but you're not experiencing lock escalation, are you? Any of the ZPARM thresholds (sorry, can't remember which ones and too lazy to look them up) governing lock escalation different, either accidentally or intentionally, from V5?

Just a thought.



Raymond

> -----Original Message----- > From: Steve Howell [SMTP:HOWELLS@STGEORGE.COM.AU] > Sent: Monday, 9 April 2001 2:46 pm > To: DB2-L@RYCI.COM > Subject: WAIT DRAIN LOCK > > Hi, > > Since upgrading DB2 to version 6 we have noticed a fewa pplication > programs executing as started tasks spending a lot of time inW AIT-DRNLOCK > according [...] 521 18 15_Re: DB2 V5 JDBC10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 9 Apr 2001 08:39:54 +0200437_us-ascii Yes it does.

Try to load the newest Type-2 JDBC driver, it supports SQLJ too (but there are some bugs). It works better that type-1 driver for Java programs.

HTH Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 540 47 36_Re: Replication Problem and FIXPACK219_Dimitris Margaritis20_margardi@NOVABANK.GR30_Mon, 9 Apr 2001 10:23:57 +0200601_iso-8859-1 Hi, Unfortunately the fixpack 2a didn't fix the problem. I'm waiting fixpach 3 ......



Best Regards

Dimitris Margaritis System & Database Administrator ---------------------------------------------- NOVABANK S.A. I/T Production 182, Sygrou Av., 176 71 Athens, Greece Phone: + 301 9557146 Fax: + 301 9530658 Email: margardi@novabank.gr





-----Original Message----- From: Neil_D._Thompson@eu.fmo.com [mailto:Neil_D._Thompson@eu.fmo.com] Sent: Friday, April 06, 2001 12:10 PM To: margardi@NOVABANK.GR Subject: RE:Replication Problem and FIXPACK2 [...] 588 145 17_Re: DB2 Estimator11_John Curran26_John.Curran@DRESDNERKB.COM30_Mon, 9 Apr 2001 08:55:55 +0100429_iso-8859-1 Hi Carlton

DB2 Estimator supplies sample SQL which allows you to import the DDL into Spufi.

After you have created a project and entered the DB2 Estimator - Select Sub -Project screen, there is an option to "Import Tables" click on this and it provides sample SQL to generate Table, indexes DDL and other options to generate their associated stats. Copy these to Spufi and add any local predicates. [...] 734 39 31_More Stored Procedure questions11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Mon, 9 Apr 2001 09:32:33 +0100489_- All,

How I love the stored procedure builder. I am having two problems and any assistance in their resolution would be grateful.

Problem 1 - I am getting what appears to be a random error on the build process. The precise error is a return code of 1 when the DSNTPSMP rexx code performs an EXECIO into the SQLSRC temporary dataset. I have created just over 20 stored procedures and I get this on 5 of them, I can see no pattern and am getting increasingly frustrated. [...] 774 206 65_DB2 V6.1 on OS390: Wrong result of a 'group by' (one row beneath)12_Peter Horner27_peter.horner@JULIUSBAER.COM30_Mon, 9 Apr 2001 13:19:43 +0200354_us-ascii Hi list,

Has anybody noticed this effect? The view as described further down (1) is based on file which was created with a TSO/3.4-SAVE-command. Selecting high level qualifier TC01 gives proper result. When selecting all or even a subset of rows, we receive the wrong result. The result row is one row beneath of where it should be. [...] 981 179 47_Re: Order by using the Order by index or sort ?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Mon, 9 Apr 2001 17:17:50 +0530536_iso-8859-1 Boubakar,

I think Terry's solution of adding the zipprefix is good one if you can afford this. I believe depending on the cardinality of the columns i.e wkfs and zipcode. If the cardinality of wkfs and/or zipcode is low, then index on wkfs and zipcode can give you some better performance. The matchcols will be zero but scan will be indexonly without sort. So, please mention the cardinality of both the columns individually and fullkeycard as well if the index on both is created(in the order i have mentioned) [...] 1161 236 70_Re: DB2 V6.1 on OS390: Wrong result of a 'group by' (one row bene ath)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Mon, 9 Apr 2001 17:37:11 +0530354_iso-8859-1 Peter,

Query looked OK to me. Please recheck the result because the result from the 2nd query also shows count(*) as 7827 but for TC02. Please recheck if this is overlooked. I am finding the count(*) and cylinder both are same for TC02 in the 2nd query result.

Let me know if there is something which i missed in the query. [...] 1398 232 47_Re: Order by using the Order by index or sort ?0_29_sflindsey@HIGHLIGHTS-CORP.COM30_Mon, 9 Apr 2001 08:06:30 -0400561_us-ascii Just an FYI:

The USPS refers to the 3 first digits of the zip code as the Sectional Center Facility. Many magazine publishers have what is called a matchcode which is composed of various pieces of the name and address. For us these fields are referenced in an index which helps us locate a customer quickly when they do not have their account number. The index is composed of the sectional center facility (first 3 digits of the zip code), the 4th and 5th digits of the zip code. 3 characters from the 3 last name, the first character from [...] 1631 33 31_message errors from db2diag.log22_Marcius Dellano Moraes22_marcius@GOLDNET.COM.BR30_Mon, 9 Apr 2001 07:33:33 -0500621_- Hi,

Sometimes I get the following messages in my db2diag.log:

2001-04-08-05.21.36.697000 Instance:DB2 Node:000 PID:266(db2syscs.exe) TID:282 Appid:none buffer_pool_services sqlbGetFreeSlot Probe:727 DIA9999E An internal error occurred. Report the following error code : "FFFFD12C".

2001-04-08-05.36.25.815000 Instance:DB2 Node:000 PID:266(db2syscs.exe) TID:304 Appid:none buffer_pool_services sqlbFreeUpSlot Probe:121

All pages in buffer pool 1 are in use.

String Title: PID:266 Node:000 Could not fix page for objID=29, tbspaceID=2, objType=0, parentObjID=29 parentTbspaceID=2. [...] 1665 137 21_Re: 24x7 Requirements16_Michael McKinney16_mmckinne@CSC.COM30_Mon, 9 Apr 2001 09:10:17 -0400542_iso-8859-1 Paul,

Might not be exactly what you were looking for, but I wanted to share my thoughts and experiences on 24 x 7. I work for CSC on a Medicaid project. A requirement of the project is 24 x 7 DB2 and application availability, with stiff financial penalties for each minute of down time as well as response times above a set threshold. Our client has the position that 24 x 7 means exactly that, 24 hours a day, seven days a week, 365 days a year. I am currently involved in discussions with my management in which I am [...] 1803 30 27_DB2 v5 CLI , OS/390 and USS10_Lisa Bates19_lbates@JHANCOCK.COM30_Mon, 9 Apr 2001 08:27:19 -0500557_- Hello listers. I am posting this question for one of my C++, DB2 programmers. I hope I can phrase it correctly and reflect the question accurately, here goes. The C++ program in question lives in a uss HFS. "Help. I can figure out how to get the module containing the embedded SQL through the DB2 preprocessor and then get an object created. This will effectively create a DBRMLIB member with the same name as the module. Normally, I would create a plan and bind the DBRM and member to that plan. However, the CLI book indicates that when using both [...] 1834 67 40_Re: RC/Comapare, RC/Migration re-visited0_19_mike.holmans@BT.COM30_Mon, 9 Apr 2001 15:03:24 +0100639_- Could the appearance of unexpected tables have anything to do with views being created over a table in db A and a table in db C?





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

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

> -----Original Message----- > From: Carlton Enuda [SMTP:carltonenuda@HOTMAIL.COM] > Sent: Friday, April 06, 2001 8:55 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] RC/Comapare, RC/Migration re-visited > > Dear List, > About a week ago I wrote for help with the steps to compare two DBs and > generate a [...] 1902 24 31_Help - Stored Procedure Problem13_Davis, Ronald17_DavisRo@CONED.COM30_Mon, 9 Apr 2001 10:29:07 -0400489_ISO-8859-1 This is my first implementation of stored procedures. Has anyone ever gotten the a -113. The exact error message looks like this:



Microsoft OLE DB Provider for ODBC Drivers error '80004005' [IBM][CLI Driver][DB2] SQL0969N There is no message text corresponding to SQL error "-113" in the message file on this workstation. The error was returned from module "DSNXRCAL" with original tokens "ipwodesc 000". SQLSTATE=42602 /Property_Records/wodesc.asp, line 34 [...] 1927 81 35_Re: Help - Stored Procedure Problem15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 9 Apr 2001 09:42:55 -0500582_iso-8859-1 Hi, Ronald, This is from the DB2 for OS/390 Messages and Codes manual. HTH, Linda



-113

-113 INVALID CHARACTER FOUND IN string, REASON CODE nnn

Explanation: The string contains an invalid character. It can be an SQL ordinary identifier name, a host variable name, or a DBCS comment.

For SBCS SQL ordinary identifiers, names of buffer pools, databases, plans, and storage groups must contain only uppercase alphabetic or national characters and numerics when CHARSET is KATAKANA; the first character must be alphabetic or national. [...] 2009 31 31_Triggers / Before Trigger Issue15_Brian Ogrydziak24_bogrydziak@CHECKFREE.COM30_Mon, 9 Apr 2001 09:25:57 -0500372_- Hi Folks, I was wondering if any of you may have experienced this problem and hopefully have a solution.



What I'm Trying to do:

Set up a Trigger on Table A for 6 fields that will write to a log table when any of those 6 fields change. I'm using a before and after trigger so I'm expecting to get both a before and after image of the 6 fields. [...] 2041 36 39_Re: Partitioning compressed tablespaces10_Steve Lamb25_Steve.Lamb@POWERGEN.CO.UK30_Mon, 9 Apr 2001 15:43:42 +0100520_us-ascii Thanks Marcus - I gave the REORG option a go and it was about 3x faster than DSNTIAUL !

Steve







_________________________ Disclaimer Notice __________________________ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. [...] 2078 248 39_Re: Partitioning compressed tablespaces64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Mon, 9 Apr 2001 09:58:40 -0500547_ISO-8859-1 Steve,

If I had to use the IBM supplied tools, (that's V6 and below), I would do as Marcus suggests and use REORG UNLOAD EXTERNAL or whatever the syntax is) to get the data out, uncompressed. Be sure to code LOG NO. You'll have to guess at the size of the unload dataset that you'll need though, since it is currently compressed, unless you can find a way to have IBM REORG allocate the unload dataset dynamically the correct size for you. Check with your DASD folks to be sure you have the space you think you will need so [...] 2327 84 35_Re: Triggers / Before Trigger Issue12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 9 Apr 2001 10:12:26 -0500599_us-ascii Hello,

Just a thought, but we use just "after" triggers for this, and reference the "old" and "new" values in two separate insert statements.

I~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Steve Grimes Washington University Information Systems OS/390 V2R10 DB2 UDB 6.1 W: 314-935-4376 H: 636-928-5005 Isa.64:6 Steve_Grimes@aismail.wustl.edu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~















Brian Ogrydziak on 04/09/2001 09:25:57 AM

Please respond to DB2 Data Base Discussion List [...] 2412 95 16_DB2 Load Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Mon, 9 Apr 2001 22:14:45 +0700531_ISO-8859-1 Dear all,

We are having a problem when loading a text file to a DB2 table. The file is in CSV format, extracted from AS/400. The problem occured, in my opini0n, maybe because the file contained one blank row at the end of file. Everytime we loaded it to the table, the process will take a very long time to finish, the file itself was 1 GB, but after I deleted the blank row the Loding process ran very fast (around 5 minutes with 4,5 million data). So I believe the blank row at the EOF is the big problem. [...] 2508 66 35_AW: Help - Stored Procedure Problem12_Peter, Georg15_G.Peter@DZBW.DE30_Mon, 9 Apr 2001 17:16:04 +0200693_iso-8859-1 Ronald,

SQLCODE -113 (SQLSTATE 42602) means INVALID CHARACTER ENCOUNTERED.

HTH.

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Life is 10% what happens to you and 90% how you react to it. [...] 2575 64 35_Re: More Stored Procedure questions12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 9 Apr 2001 08:59:05 -0700572_us-ascii On problem 2, I took your SQL and put into my Version 7.2 SPB and it built just fine. I strongly suspect that somehow you have a bad character in your stored procedure.

On problem 1, two possibilities come to mind: 1) you have some space problems with the dataset. or 2) you're running into a REXX problem. If you don't have all the latest fixes for REXX/DB2 interface, it would be worth while getting them. --- Carl Newton wrote: > All, > > How I love the stored procedure builder. I am having two problems and > any [...] 2640 110 25_Re: INFO on DB2 utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 9 Apr 2001 11:02:57 -0500529_iso-8859-1 The guys installing V7 here couldn't find the MODIFY utility with our tapes. Does that one have any competition?

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services "Imagine the Creator as a stand-up comedian - and at once the world becomes explicable." - H.L. Mencken

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Friday, April 06, 2001 11:33 AM To: DB2-L@RYCI.COM Subject: Re: INFO on DB2 utilities [...] 2751 182 25_Re: INFO on DB2 utilities12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 9 Apr 2001 11:19:52 -0500478_iso-8859-1 RE: [DB2-L] INFO on DB2 utilitiesLinda:

BMC has a modify utility, but I do not believe it is as full function as the IBM version. I'd suggest talking with IBM about this issue. I know I have.

Gerald Hodge HLS Technologies, Inc. 888-494-9019 -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Billings, Linda Sent: Monday, April 09, 2001 11:03 AM To: DB2-L@RYCI.COM Subject: Re: INFO on DB2 utilities [...] 2934 146 35_Re: Triggers / Before Trigger Issue15_Brian Ogrydziak24_BOgrydziak@CHECKFREE.COM30_Mon, 9 Apr 2001 12:24:24 -0400328_us-ascii Steve, Thanks for your response, I did a poor job of explaining what type of trigger I'm using. What I should of said is that I'm using an 'AFTER' trigger and the before image is not working correctly. THANKS for your input. Here's a copy of the trigger:

Note: BEFORE.ADR1 and BEFORE.ADR2 end up as spaces. [...] 3081 90 25_Re: INFO on DB2 utilities14_Richard Arnold24_Richard.Arnold@CHASE.COM30_Mon, 9 Apr 2001 11:27:51 -0500598_iso-8859-1 BMC CopyPlus for DB2 has modify capability; pluse it does wildcards,

Like MODIFY TABLESPACE KLKDP*.* DELETE AGE(90).

It's been a real timesaver for us.

Richard Arnold DBA JPMorganChase Investor Services











"Billings, Linda" @RYCI.COM> on 04/09/2001 11:02:57 AM

Please respond to DB2 Data Base Discussion List





Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: INFO on DB2 utilities [...] 3172 88 35_Re: More Stored Procedure questions11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Mon, 9 Apr 2001 17:38:10 +0100310_- Myron,

Problem 1 - The space problem would not explain why some times it worked and sometimes it did not. I have increased the size of the temporary work dataset in the DSN1WLM address space, to no avail. IBM were in last week to install the REXX/DB2 interface and applied all the recent APAR's. [...] 3261 109 25_Re: INFO on DB2 utilities12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 9 Apr 2001 11:57:55 -0500421_iso-8859-1 Richard:

You may be able to answer this. Does BMC's modify call the IBM modify? If so it would appear you need to purchase both.

Gerald Hodge HLS Technologies, Inc. 888-494-9019

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Richard Arnold Sent: Monday, April 09, 2001 11:28 AM To: DB2-L@RYCI.COM Subject: Re: INFO on DB2 utilities [...] 3371 118 35_Re: More Stored Procedure questions12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 9 Apr 2001 10:36:31 -0700523_us-ascii For problem 2, did you try cutting the SQL out of your email and pasting it into the Wizard and then going on to create the SP. I didn't use the SQL assistant, but did cut the SQL out of your email and put it into the wizard at the time where you select the assistant. It worked just fine for me at that point. --- Carl Newton wrote: > Myron, > > Problem 1 - The space problem would not explain why some times it > worked > and sometimes it did not. I have increased the size [...] 3490 17 80_Joins - any good documentation (concise and clear) on the various types of joins13_Judi Westcott23_judi_westcott@YAHOO.COM30_Mon, 9 Apr 2001 12:43:59 -0500427_- Hello

Does anyone know of a good reference for explaining, in basic terms, the various types of joins and giving good, concrete examples of their usage?

Many thanks Judi

================================================ 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. 3508 49 84_Re: Joins - any good documentation (concise and clear) on the various types of joins9_Cam White20_cwhite@REVEALNET.COM30_Mon, 9 Apr 2001 14:33:52 -0400646_iso-8859-1 Judi,

Download a free 30-day trial version of thr Knowledge base for DB2 from http://www.revealnet.com and you will get plenty of information on Join design with examples and performance checklists.

Best wishes,

Cam White RevealNet, Inc.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Judi Westcott > Sent: Monday, April 09, 2001 1:44 PM > To: DB2-L@RYCI.COM > Subject: Joins - any good documentation (concise and clear) on the > various types of joins > > > Hello > > Does anyone know of a good reference for explaining, in basic terms, the > [...] 3558 208 47_Re: Order by using the Order by index or sort ?22_Boubakar Saim Haddache16_bsaim@OTOOBE.COM30_Mon, 9 Apr 2001 20:46:43 +0200503_iso-8859-1 thank you for your answers, i want to give some details of my question.





INTRODUCTION

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

We have a data base of companies.

The company table has several columns of data (turnover, zip code ...), all searchable columns having indexes.

Each company is also categorized into a tree structure giving its classification/activity.





TYPICAL QUERY

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

Here is a typical query : [...] 3767 138 25_Re: INFO on DB2 utilities15_Murley, Michael22_Michael_Murley@BMC.COM30_Mon, 9 Apr 2001 13:46:54 -0500548_iso-8859-1 Gerald,

When you drop a table, DB2 marks the Record Type OBD as logically deleted in the DBD. As long as recovery information exists for the table space prior to the DROP TABLE, DB2 holds on to the logically deleted OBD.

When BMC C+/Modify detects that all recovery information prior to such a logical deletion has been removed, it calls the DB2 Modify utility to perform DBD cleanup to get rid of the logically deleted OBD. If the DB2 Modify utility is not present, C+/Modify will not be able to effect that cleanup. [...] 3906 47 85_Re: Joins - any good documentation (concise and clear) on the var ious types of joins19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Mon, 9 Apr 2001 14:56:14 -0500665_iso-8859-1 Judi, I think there's an excellent intro to the join concept in SC26-9937, An Intro to DB2 V7 for OS390/zOS at url:

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

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, [...] 3954 21 13_COBOL access?13_Dennis Taylor14_ismgr@PCTC.COM30_Mon, 9 Apr 2001 13:07:58 -0700505_us-ascii Is anyone on list using Acucobol or something similar to access a DB2 database?



Dennis Taylor -------------------------------- Star light, star bright, First star I see tonight, I wish I may, I wish I might, Oh nuts! It's just a satellite.

================================================ 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. 3976 18 24_Stored Procedure Builder13_O'Neill, Mike19_Mike.O'Neill@53.COM30_Mon, 9 Apr 2001 16:20:29 -0400316_iso-8859-1 I thought I read somewhere that you could run stored procedure builder thru the stored procedure address space and that didn't require the WLM address space. Can someone verify that for me. And if so, do I need to add more libraries (SYSEXEC) to the stored procedure address sapce proc? Thanks, Mike [...] 3995 103 35_Re: More Stored Procedure questions11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Mon, 9 Apr 2001 17:38:10 +0100317_us-ascii Myron,

Problem 1 - The space problem would not explain why some times it worked and sometimes it did not. I have increased the size of the temporary work dataset in the DSN1WLM address space, to no avail. IBM were in last week to install the REXX/DB2 interface and applied all the recent APAR's. [...] 4099 20 32_applet support in DB2 for OS/39011_Matt Kelley18_mgk333@HOTMAIL.COM30_Mon, 9 Apr 2001 16:55:33 -0500350_- Does DB2 for OS/390 provide a JDBC driver that can be used by an applet, for example, from a Windows-based web browser?

I know that this can be done using the net driver from DB2 on workstation platforms and DB2 Connect, but this does not work for me if I need to deploy the applet from an OS/390-based web server (or does it/can it?). [...] 4120 40 39_Gen DDL for Create Trigger from Catalog5_Chris17_isatcjk@YAHOO.COM30_Mon, 9 Apr 2001 15:04:29 -0700485_us-ascii Hi Everybody, I just found that Platinum's RC/Query doesn't give a (known) way to generate DDL for a Create Trigger statement.

I have but haven't used RC/Migrator and RC/Update,,,

How could I generate the DDL?

I see two rows in the sysibm.syspackstmt, seq 0 and seq 1, and I see my triggered insert buried in there is a unpretty way. I'd rather not have to write SQL to join the Systriggers table and the syspackstmt table, and then edit the result. [...] 4161 78 43_Re: Gen DDL for Create Trigger from Catalog14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Mon, 9 Apr 2001 17:42:35 -0500357_iso-8859-1 Source of the trigger:

SELECT TEXT, SEQNO FROM SYSIBM.SYSTRIGGERS WHERE SCHEMA = schema_name AND NAME = trigger_name ORDER BY SEQNO

The rest of the information is there in the catalog tables, documented, and there are quieres in the IBM manuals that show you how to get all the DDL. It often help to Read The Friendly Manuals. [...] 4240 140 25_Re: INFO on DB2 utilities14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Mon, 9 Apr 2001 17:42:36 -0500706_iso-8859-1 Unless something changed recently, BMC's Modify calls IBM Modify and you need both.

Regards, Richard

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



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Gerald Hodge > Sent: Monday, April 09, 2001 11:58 AM > To: DB2-L@RYCI.COM > Subject: Re: INFO on DB2 utilities > > > Richard: > > You may be able to answer this. Does BMC's modify call the IBM > modify? If > so it would appear you need to [...] 4381 123 25_Re: INFO on DB2 utilities12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 9 Apr 2001 11:57:55 -0500421_iso-8859-1 Richard:

You may be able to answer this. Does BMC's modify call the IBM modify? If so it would appear you need to purchase both.

Gerald Hodge HLS Technologies, Inc. 888-494-9019

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Richard Arnold Sent: Monday, April 09, 2001 11:28 AM To: DB2-L@RYCI.COM Subject: Re: INFO on DB2 utilities [...] 4505 123 25_Re: INFO on DB2 utilities12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 9 Apr 2001 11:57:55 -0500421_iso-8859-1 Richard:

You may be able to answer this. Does BMC's modify call the IBM modify? If so it would appear you need to purchase both.

Gerald Hodge HLS Technologies, Inc. 888-494-9019

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Richard Arnold Sent: Monday, April 09, 2001 11:28 AM To: DB2-L@RYCI.COM Subject: Re: INFO on DB2 utilities [...] 4629 32 24_Java and DB2 - Success!!15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 10 Apr 2001 10:12:43 +1000499_- Colleagues,

I've been lying low on this one for awhile, having a bash at it every now and then when I felt like wasting a few hours. The only thing left was to uninstall JDK 1.3.0, which I was loathe to do. I was waiting for a friend of mine - with broadband internet access - to download fixpack 7 before doing this, but in the end I just did it. And what do you know? As soon as JDK 1.3.0 was removed the Control Center (sp.) fired up fine. I've now got a CD from my friend with FP7 [...] 4662 34 80_Joins - any good documentation (concise and clear) on the various types of joins13_Judi Westcott23_judi_westcott@YAHOO.COM30_Mon, 9 Apr 2001 12:43:59 -0500440_us-ascii Hello

Does anyone know of a good reference for explaining, in basic terms, the various types of joins and giving good, concrete examples of their usage?

Many thanks Judi

================================================ 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. [...] 4697 148 21_Re: 24x7 Requirements11_James Szabo18_jim.szabo@CORE.COM30_Mon, 9 Apr 2001 21:26:25 -0400564_iso-8859-1 Mike,

The thing I find interesting is the clash between the third-party products (and now, possibly IBM?) that automate the change of a table definition (and have allowed us to do just about any type of change easily) and the need for 24x7.

Since we've had a third-party change management product, one of my DBAs has actually run a script to change the order of columns in a table, because the client liked a better order when they used query products (e.g. QMF, Business Objects) that displayed all columns to build your query from! [...] 4846 34 80_Joins - any good documentation (concise and clear) on the various types of joins13_Judi Westcott23_judi_westcott@YAHOO.COM30_Mon, 9 Apr 2001 12:43:59 -0500440_us-ascii Hello

Does anyone know of a good reference for explaining, in basic terms, the various types of joins and giving good, concrete examples of their usage?

Many thanks Judi

================================================ 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. [...] 4881 15 23_Index Access - Resolved0_14_mtdage@ATT.NET31_Tue, 10 Apr 2001 02:56:44 +0000393_- Problem resolved after running full runstats. SQL query now using appropriate index.

I thought I'd share this info to the list.

Mayflor

================================================ 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. 4897 133 35_Re: More Stored Procedure questions12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 9 Apr 2001 10:36:31 -0700523_us-ascii For problem 2, did you try cutting the SQL out of your email and pasting it into the Wizard and then going on to create the SP. I didn't use the SQL assistant, but did cut the SQL out of your email and put it into the wizard at the time where you select the assistant. It worked just fine for me at that point. --- Carl Newton wrote: > Myron, > > Problem 1 - The space problem would not explain why some times it > worked > and sometimes it did not. I have increased the size [...] 5031 133 35_Re: More Stored Procedure questions12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 9 Apr 2001 10:36:31 -0700523_us-ascii For problem 2, did you try cutting the SQL out of your email and pasting it into the Wizard and then going on to create the SP. I didn't use the SQL assistant, but did cut the SQL out of your email and put it into the wizard at the time where you select the assistant. It worked just fine for me at that point. --- Carl Newton wrote: > Myron, > > Problem 1 - The space problem would not explain why some times it > worked > and sometimes it did not. I have increased the size [...] 5165 63 84_Re: Joins - any good documentation (concise and clear) on the various types of joins9_Cam White20_cwhite@REVEALNET.COM30_Mon, 9 Apr 2001 14:33:52 -0400646_iso-8859-1 Judi,

Download a free 30-day trial version of thr Knowledge base for DB2 from http://www.revealnet.com and you will get plenty of information on Join design with examples and performance checklists.

Best wishes,

Cam White RevealNet, Inc.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Judi Westcott > Sent: Monday, April 09, 2001 1:44 PM > To: DB2-L@RYCI.COM > Subject: Joins - any good documentation (concise and clear) on the > various types of joins > > > Hello > > Does anyone know of a good reference for explaining, in basic terms, the > [...] 5229 63 84_Re: Joins - any good documentation (concise and clear) on the various types of joins9_Cam White20_cwhite@REVEALNET.COM30_Mon, 9 Apr 2001 14:33:52 -0400646_iso-8859-1 Judi,

Download a free 30-day trial version of thr Knowledge base for DB2 from http://www.revealnet.com and you will get plenty of information on Join design with examples and performance checklists.

Best wishes,

Cam White RevealNet, Inc.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Judi Westcott > Sent: Monday, April 09, 2001 1:44 PM > To: DB2-L@RYCI.COM > Subject: Joins - any good documentation (concise and clear) on the > various types of joins > > > Hello > > Does anyone know of a good reference for explaining, in basic terms, the > [...] 5293 114 27_Re: Index Access - Resolved15_MAHSOUN, KHALID29_kmahsoun@SAUDIAIRLINES.COM.SA31_Tue, 10 Apr 2001 07:14:09 +0300610_WINDOWS-1256 hi What do mean by full runstats



-----Original Message----- From: mtdage@ATT.NET [SMTP:mtdage@ATT.NET] Sent: 10 April, 2001 05:57 Õ To: DB2-L@RYCI.COM Subject: Index Access - Resolved

Problem resolved after running full runstats. SQL query now using appropriate index.

I thought I'd share this info to the list.

Mayflor

================================================ 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. 5408 174 43_Re: Gen DDL for Create Trigger from Catalog16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 10 Apr 2001 07:20:27 +0100610_- Hi Chris,

You should be able to use the DDL command infront of the trigger. Then type EDIT in the command in case you would like to change something in the trigger text. You also can use RC/Migrator to extract the trigger text.

best regards Steen Rasmussen Computer Associates Denmark

> -----Original Message----- > From: Chris [SMTP:isatcjk@YAHOO.COM] > Sent: Tuesday, April 10, 2001 12:04 AM > To: DB2-L@RYCI.COM > Subject: Gen DDL for Create Trigger from Catalog > > Hi Everybody, > I just found that Platinum's RC/Query doesn't give > a (known) way to generate DDL for a Create [...] 5583 68 36_Re: applet support in DB2 for OS/39012_Simon George21_Simon.George@UBSW.COM31_Tue, 10 Apr 2001 08:04:37 +0100490_US-ASCII I Believe you need a Type-4 driver for applet support. IBM only currently supply Type-2 and 3 JDBC drivers with DB2. Not entirely sure when the Type-4 will be available, sometime this year I hope!

Cheers Simon George

Tele (internal)74408 (external)020 7567 4408 email Simon.George@usbw.com



-----Original Message----- From: mgk333 [mailto:mgk333@HOTMAIL.COM] Sent: 09 April 2001 22:56 To: DB2-L Cc: mgk333 Subject: applet support in DB2 for OS/390 [...] 5652 19 37_Connect Runtime Client V7 and msquery10_Alain Pary19_Alain.Pary@DEXIA.BE31_Tue, 10 Apr 2001 14:19:49 +0200402_- Hello, We have the same problem as the problem described in the item 015590 00/09/15 11:27 28 Connect Runtime Client V7 failure . In the archive list we found no solution . We try to use msquery 2000 but the problem persist . We don't know how to increase the size of the msquery stack (see q114939 faq#3 in Ms knowledge base article). Can somenone help us to resolve our problem . best regards [...] 5672 33 22_Summit DB2 Performance11_Pedro Ramos18_ramos@MAINFRAME.PT31_Tue, 10 Apr 2001 14:23:17 +0100407_iso-8859-1 Hello everyone. Mainframe is promoting in Portugal on the 19th of April in Lisbon the Summit DB2 Performance. If you are interested in attending please contact me sending an email to ramos@mainframe, so i can send you the summit invitation.

Com os nossos melhores cumprimentos, Best regards, Pedro Ramos



MAINFRAME, Lda Rua António Nobre 10 R/C 2800-259 Almada Portugal [...] 5706 64 28_Re: Java and DB2 - Success!!23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 10 Apr 2001 13:40:12 +0100439_iso-8859-1 Raymond

Welldone for sticking the course .. since getting our AIX config (using 1.1.8 of JDK) working I have not been back to revist and look at what I did, been up to my neck in recovery crap.

All the best

Leslie

-----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: Tuesday, April 10, 2001 1:13 AM To: DB2-L@RYCI.COM Subject: Java and DB2 - Success!! [...] 5771 17 37_DB2 on VM - mass deletes from a table10_Wes Wright23_wesleyw@RENEWAL-IIS.COM31_Tue, 10 Apr 2001 08:30:20 -0500320_- Hello, In DB2 on VM, is there a way to quickly remove all rows from a table without any logging? Or, is there a way to use the DATALOAD utility with a REPLACE option so that the existing data in the table is overwritten. We have some tables that we would like to easily purge the existing data and load new data. [...] 5789 23 14_DB2 and COBOL?13_Dennis Taylor14_ismgr@PCTC.COM31_Tue, 10 Apr 2001 07:27:25 -0700604_us-ascii I'm expanding my previous query a little: is anyone on the list using *any* brand of cobol with direct access to DB2 tables?





Dennis Taylor -------------------------------- COME FROM:

An instruction proposed by an anonymous programmer to resolve the "GO TO" controversy. The industry is still bristling with acute disbelief.

================================================ 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. 5813 56 18_Re: DB2 and COBOL?13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 10 Apr 2001 09:27:07 -0500443_us-ascii COBOL OS/390 -





Dennis Taylor cc: Sent by: DB2 Subject: DB2 and COBOL? Data Base Discussion List



04/10/2001 09:27 AM Please respond to DB2 Data Base Discussion List











I'm expanding my previous query a little: is anyone on the list using *any* brand of cobol with direct access to DB2 tables? [...] 5870 64 18_AW: DB2 and COBOL?12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 10 Apr 2001 16:42:37 +0200811_iso-8859-1 Dennis, as far as I know there is no "*any brand of cobol with direct access to DB2 tables. You need to embed SQL in a cobol program - otherwise the good old DB2 for OS/390 does not understand what you wanna do... ;-))

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Life is 10% what happens to you and 90% how you react [...] 5935 21 22_SPB & COBOL Generation13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 10 Apr 2001 09:53:31 -0500305_iso-8859-1 Hi,

I wanted to know if there are plans for the Stored Procedure Builder to generate Cobol code for the OS390 environment. The SPB seems great, but we do not have C or Java on the OS390 so the tool is of little benefit for us.

Perhaps someone from IBM may have some insight. [...] 5957 38 22_Re: AW: DB2 and COBOL?13_Dennis Taylor14_ismgr@PCTC.COM31_Tue, 10 Apr 2001 08:04:30 -0700277_us-ascii At 04:42 PM 4/10/01 +0200, you wrote: >Dennis, as far as I know there is no "*any brand of cobol with direct access >to DB2 tables. >You need to embed SQL in a cobol program - otherwise the good old DB2 for >OS/390 does not understand what you wanna do... ;-)) > [...] 5996 44 41_Re: DB2 on VM - mass deletes from a table13_Jeff Jennings25_Jeff_Jennings@FSAFOOD.COM31_Tue, 10 Apr 2001 07:55:28 -0700412_us-ascii Wes, What I have used in the past is a DROP TABLE, CREATE TABLE, DATALOAD sequence. This works quite well. It does have at least one major drawback in that you also have to recreate all non-primary indexes on the table.

Jeff







Wes Wright on 04/10/2001 06:30:20 AM

Please respond to DB2 Data Base Discussion List [...] 6041 75 22_Re: AW: DB2 and COBOL?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 10 Apr 2001 10:19:08 -0500546_- Hi, Dennis, I don't mean to be the bearer of bad news but this thing has disaster written all over it. I have never seen or heard of an application written for one DBMS or even VSAM access being ported successfully over to another type of DBMS without doing any thinking about the design. It sounds like Acu4GL was treating the Oracle data base as a great big VSAM file on a non-OS390 server. It probably bypassed whatever processing efficiencies that Oracle had. I have to question why you even want a RDBMS if you aren't going to use it [...] 6117 38 36_Re: Integer Function in DB2 V5/OS39013_Keerthi Chegu18_lpbusobj@YAHOO.COM31_Tue, 10 Apr 2001 10:04:39 -0500442_- Hi All:

I treid the following syntax:

select int(substr('123456',1,4)) -1 , inv_nbr from inv_t;



Gives me the following error on V5 OS390 in the QMF panel :

sql error on or before line 1 position 8.

If I remove the int function:

select substr('123456',1,4),inv_nbr from inv_t then it works.

I am totally puzzled. Our DB2 DBA doesn't seem to have an answer to this. Is it a bug ? [...] 6156 46 41_Re: DB2 on VM - mass deletes from a table24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Tue, 10 Apr 2001 11:39:03 -0700561_us-ascii Wes, The only way to suppress logging on DB2 Server for VSE&VM is to run in Single User Mode with a LOGMODE=N. You will have to take a database archive to run the database in Multi-User Mode again.

Frank



On Tue, 10 Apr 2001 08:30:20 -0500, Wes Wright wrote:

>Hello, >In DB2 on VM, is there a way to quickly remove all rows from a table >without any logging? Or, is there a way to use the DATALOAD utility with a >REPLACE option so that the existing data in the table is overwritten. We >have some tables that we would [...] 6203 49 22_Re: AW: DB2 and COBOL?13_Dennis Taylor14_ismgr@PCTC.COM31_Tue, 10 Apr 2001 08:42:54 -0700447_us-ascii At 10:19 AM 4/10/01 -0500, you wrote:

> I don't mean to be the bearer of bad news but this thing has >disaster written all over it. I have never seen or heard of an application >written for one DBMS or even VSAM access being ported successfully over to >another type of DBMS without doing any thinking about the design. It sounds >like Acu4GL was treating the Oracle data base as a great big VSAM file on a >non-OS390 server. [...] 6253 23 37_DB2 Batch Checkpoint Restart Products10_Iris Wilde16_wildei@TDBANK.CA31_Tue, 10 Apr 2001 11:44:45 -0400411_iso-8859-1 Hello List: We're in the process of looking at DB2 for OS/390 Batch Checkpoint Restart Products and were wondering if you have any suggestions, comments, gotchas, things that are really important to watch for or advice about specific products. Also, if you went through this type of evaluation, what were the deciding factors in choosing one product over the others? Would appreciate any input. [...] 6277 23 17_Stored procedures14_Michele Saffer32_michele.saffer@CONSULTEC-INC.COM31_Tue, 10 Apr 2001 10:18:48 -0500538_- I am looking for a way to keep two different versions of the same stored procedure (one for unit test and one for system test) on the same DB2 subsystem. I am using DB2 version 5 in a non-WLM environment. I know I can use the AUTHID in SYSPROCEDURES to point to two different load modules, and this works fine in our Powerbuilder online environment where we are using generic AUTHIDs. It does not work in batch, where the AUTHID is the TSO ID. I tried SET CURRENT SQLID before I called the stored procedure, but that did not work. [...] 6301 15 23_Index Access - Resolved0_14_mtdage@ATT.NET31_Tue, 10 Apr 2001 15:59:40 +0000370_- DBA was doing sampling runstats only. I'm sure they have their reasons.

Hope this answers your question.

Mayflor

================================================ 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. 6317 88 22_Re: AW: DB2 and COBOL?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 10 Apr 2001 11:04:59 -0500561_- Hi, again, When I mean successful I mean actually taking advantage of what the RDBMS was designed to do. I have seen tools that do that but they essentially treat the RDBMS as a big VSAM file. The SQL is seldom efficient compared to what could be done if the application were reworked. Translating code from one type of language or RDBMS to another can be similar to translating from one foreign language to another. An example, we hosted a foreign exchange student from Germany a few years ago. I used to tell my children to "knock themselves out" when [...] 6406 68 36_Re: Integer Function in DB2 V5/OS39011_David Nance16_DWNance@FHSC.COM31_Tue, 10 Apr 2001 12:05:55 -0400292_US-ASCII Keerthi, Yes there is a table sysibm.sysdummy1 that you can use for testing functions such as this one. I have run the follwoing sql on both a v6 and a v5 system. I get the same error message as you on the V5 system, but successful(1233) on V6. I know we have a query somewhere [...] 6475 23 18_Re: DB2 and COBOL?13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Tue, 10 Apr 2001 10:42:38 -0500402_- Excuse me but would you please explain "direct" a little more. Are you looking for access to the underlying VSAM dataset bypassing DB2 altogether ? Or is it that you are looking for COBOL calls which can * get a page / row given page id / row id and page set id * get a list of RID's from an index given a set of column values * update / delete a row identified by RId Just too puzzled Mohammad [...] 6499 72 36_Re: Integer Function in DB2 V5/OS39013_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Tue, 10 Apr 2001 09:20:42 -0700590_iso-8859-1 when I run

SELECT INT(SUBSTR('123456',1,4)) -1 FROM SYSIBM.SYSDUMMY1;

I get

COL1 ----------- 1233

use the help key (F1) from QMF and you will get a more meaningful explanation of the error. The first message just tells you where it hurts. sysibm.sysdummy1 is supposed to always return just one row.

Welcome to DB2









-----Original Message----- From: Keerthi Chegu [mailto:lpbusobj@YAHOO.COM] Sent: Tuesday, April 10, 2001 9:05 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Integer Function in DB2 V5/OS390 [...] 6572 31 22_Re: AW: DB2 and COBOL?13_Dennis Taylor14_ismgr@PCTC.COM31_Tue, 10 Apr 2001 09:33:24 -0700319_us-ascii At 11:04 AM 4/10/01 -0500, you wrote: >to tell my children to "knock themselves out" when they were going to do >something that I thought was a bit foolish but harmless. One day our German >student told one of us to "make yourself unconscience". Well, it sort of >means the same thing but really doesn't. [...] 6604 17 25_DB2 STORED PROD: NUMTCB=?12_Clark, Kevin15_KClark@FDIC.GOV31_Tue, 10 Apr 2001 12:41:21 -0400345_- ALL,

Is there an SQL code to determine if NUMTCB has been reached ?



Kevin Clark

================================================ 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. 6622 19 24_web development listserv0_26_mmetcalf@NOTES.STATE.NE.US31_Tue, 10 Apr 2001 11:48:34 -0500586_us-ascii Does anyone know of a listserv for web development? I have seen some questions and answers here on the DB2-L but thought there might be another one for more detailed web development information. I am specifically interested in webserver connection pooling threads (JDBC).

Thanks in advance for any input.

Michelle Metcalf

================================================ 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. 6642 15 48_DEC(15) or DEC(31) Precision for Decimal Numbers23_McKinnon, Neal (Dallas)28_Neal.Dallas.McKinnon@FMR.COM31_Tue, 10 Apr 2001 13:03:02 -0400541_iso-8859-1 Has anyone done any performance or other studies on pre-compiler options (or BIND options) for the difference between DEC(15) and DEC(31) precision ? Has anyone noticed a negative impact on overall application performance in a volatile mixed environment ( high on-line and heavy batch ) ?

================================================ 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. 6658 156 18_Re: DB2 and COBOL?0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 10 Apr 2001 12:06:40 -0500589_us-ascii I have to agree with Linda. What you describe is essentially running a code translator on the fly. So the most obvious disadvantage is that all of your sql will be dynamic, requiring a PREPARE (equivalent to a BIND) for every execution of every SQL statement, CPU cycle expensive!

We had one of our major applications run through a code translator to convert it from an IMS-like DBMS to DB2. The resulting SQL is written at the lowest common denominator level, in other words, DUMB code! It works, but it is very inefficient. As Linda points out, the basic translated [...] 6815 48 41_Re: DB2 Batch Checkpoint Restart Products9_Cam White20_cwhite@REVEALNET.COM31_Tue, 10 Apr 2001 13:18:05 -0400673_iso-8859-1 Softbase Systems has batch checkpoint/restart tools. Their website is http://www.softbase.com

Best wishes,

Cam White RevealNet, Inc.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Iris Wilde > Sent: Tuesday, April 10, 2001 11:45 AM > To: DB2-L@RYCI.COM > Subject: DB2 Batch Checkpoint Restart Products > > > Hello List: We're in the process of looking at DB2 for OS/390 Batch > Checkpoint Restart Products and were wondering if you have any > suggestions, > comments, gotchas, things that are really important to watch for or advice > about specific products. Also, if you went [...] 6864 111 36_Re: Integer Function in DB2 V5/OS3900_29_sflindsey@HIGHLIGHTS-CORP.COM31_Tue, 10 Apr 2001 13:41:40 -0400405_us-ascii According to the OS/390 V5 SQL Reference manual, "the INTEGER function returns an integer representation of its argument. The argument must be a number."

The result of the SUBSTR function is a character string. Therefore, INTEGER does not have a number to perform the function on. The DECIMAL function works because "the first argument must be either a character string or a number." [...] 6976 32 33_Integer Function in DB2 V5/OS390.17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Tue, 10 Apr 2001 10:48:51 -0700673_US-ASCII Dave,



I tried the following:

SELECT (INT(SUBSTR('123456',1,4),8) -1) FROM SYSIBM.SYSDUMMY1

I'm getting -104 illegal symbol on "INT".

SELECT DECIMAL(SUBSTR('12343456',1,4),8) - 1 FROM SYSIBM.SYSDUMMY1 above works - result is equal to 1233.

Tested by Keerthi,

SELECT SUBSTR('123456'1,4) also works, However the result is equal to 1234.

Please clarify further, Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7009 67 37_Re: Integer Function in DB2 V5/OS390.11_Walton Rick31_Rick.Walton@GENERALCASUALTY.COM31_Tue, 10 Apr 2001 13:11:51 -0500845_iso-8859-1 Hey here is what I am getting.

You have to convert both sides of the equation.

SELECT INT(SUBSTR('123456',1,4)) - INT(1) FROM SYSIBM.SYSDUMMY1 ---------+---------+---------+---------+---------+---------+----

---------+---------+---------+---------+---------+---------+---- 1233 DSNE610I NUMBER OF ROWS DISPLAYED IS 1 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ---------+---------+---------+---------+---------+---------+---- DSNE617I COMMIT PERFORMED, SQLCODE IS 0 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---- DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1 DSNE621I NUMBER OF INPUT RECORDS READ IS 2 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 17 [...] 7077 26 37_Re: Integer Function in DB2 V5/OS390.13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 10 Apr 2001 14:53:32 -0400362_iso-8859-1 Hi Armando, As Roger Miller pointed out,

"Please check that SQL Reference Version 5 again for the INTEGER function. Only DECIMAL works on strings in V5. I suspect that others are remembering Version 6, where INTEGER works on strings. If you use the Version 6 book, note the change bars (|) at the left, which show that these are changes." [...] 7104 125 37_Re: Integer Function in DB2 V5/OS390.16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 10 Apr 2001 14:59:23 -0400411_iso-8859-1 That is odd. If I use your SQL on V5 I get: SQLERROR ON SELECT COMMAND, PREPARE FUNCTION

RESULT OF SQL STATEMENT:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "INT". SOME SYMBOLS THAT MIGHT

If I then change INT to INTEGER in your SQL I get: SELECT INTEGER(SUBSTR('123456',1,4)) - INTEGER(1)

FROM SYSIBM.SYSDUMMY1

SQLERROR ON SELECT COMMAND, PREPARE FUNCTION [...] 7230 42 41_Re: DB2 Batch Checkpoint Restart Products11_Mark Harmon17_mhinnyc@YAHOO.COM31_Tue, 10 Apr 2001 13:48:06 -0500532_- Iris,

One possible strategy for chosing a product is what can it do for you and at what cost.

By "cost" I mean issues such as: -How much retrofitting of existing apps is needed. That is, will you have to change source code of existing programs, change JCL, maybe recompile and relink? -What changes are needed to your program preparation procedures? -How much burden is placed on application programmers in developing new restartable applications? Do they have to learn a whole new API for the restart product? [...] 7273 55 26_Re: Summit DB2 Performance19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Tue, 10 Apr 2001 14:21:53 -0500531_iso-8859-1 Hi, How can I access the conference procedings.I will it be on internet. Thanks & Regards

-----Original Message----- From: Pedro Ramos [mailto:ramos@MAINFRAME.PT] Sent: Tuesday, April 10, 2001 6:23 AM To: DB2-L@RYCI.COM Subject: Summit DB2 Performance



Hello everyone. Mainframe is promoting in Portugal on the 19th of April in Lisbon the Summit DB2 Performance. If you are interested in attending please contact me sending an email to ramos@mainframe, so i can send you the summit invitation. [...] 7329 26 14_regarding load17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 10 Apr 2001 14:38:29 -0500510_- Hi Folks,

We are using DB2 V6 on OS390. i have a question on LOAD. i need to unload the data from prod. from one of the table and i need to load to TEST table. i may be unloading around 100000 records by giving a sql. but the user wants only 20000 thousand records out of 100000 records. so is there any way i can restrict to load only 20000 thousand records. or is there any option i need to include in sysin load statement. can anybody help in this. we don't have any tool... we are using just [...] 7356 136 37_Re: Integer Function in DB2 V5/OS390.17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Tue, 10 Apr 2001 13:19:26 -0700488_US-ASCII Hi again here's the error message I'm getting:



SELECT INT(SUBSTR('123456',1,4)) - INT(1)

FROM SYSIBM.SYSDUMMY1;

---------+---------+---------+---------+---------+---------+------- --+---------+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "INT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: AVG, SUBSTR, MAX, HEX, FLOAT, DATE, YEAR, ETC. DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNHSM2A SQL PROCEDURE DETECTING ERROR [...] 7493 51 18_Re: regarding load13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Tue, 10 Apr 2001 14:24:25 -0600473_iso-8859-1 If you can't right a WHERE clause to limit the unload output, unload it all and send it through the batch sort utility with

//SYSIN DD * SORT FIELDS=COPY,STOPAFT=nnn END /*

There is also a SKIPREC=nnn parm that I have used discard the front of a file.





-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Tuesday, April 10, 2001 1:38 PM To: DB2-L@RYCI.COM Subject: [DB2-L] regarding load [...] 7545 64 18_Re: regarding load64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Tue, 10 Apr 2001 15:45:02 -0500404_ISO-8859-1 It can't be done with IBM load. You'll have to implement another method. For a mere 20,000 records, I'd write a REXX program to scrape off the first 20,000 out of the 100,000 and write them to another file.

Todd Burch. CDB Software.



-----Original Message----- From: ravibh@HOTMAIL.COM Sent: Tuesday, April 10, 2001 2:59 PM To: DB2-L@RYCI.COM Subject: regarding load [...] 7610 56 18_Re: regarding load10_Rob Wright17_rwright@LIC.CO.NZ31_Wed, 11 Apr 2001 09:09:23 +1200529_us-ascii Here's the REXX to do it - I did something similar last week ...

EXTRACT REXX program: /* REXX */ max_rec=20000 do i=1 to max_rec "EXECIO 1 DISKR BIGFILE" rcode=RC if rcode > 0 then do /* eof or other error - let's get out */ "EXECIO 0 DISKR BIGFILE (FINIS" "EXECIO 0 DISKW WEEFILE (FINIS" EXIT RCODE end "EXECIO 1 DISKW WEEFILE" rcode=RC if rcode > 0 then do /* dsn full or other error - let's get out */ "EXECIO 0 DISKR BIGFILE (FINIS" "EXECIO 0 DISKW WEEFILE (FINIS" EXIT RCODE end end /* do */ "EXECIO 0 [...] 7667 70 41_Re: DB2 on VM - mass deletes from a table24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Tue, 10 Apr 2001 17:14:56 -0700577_us-ascii Wes and Jeff, In my haste, I forgot the obvious solution of a DROP DBSPACE and then following Jeff's sequence below. The DROP DBSPACE does not log all of the deleted rows, but the DROP TABLE will.

Frank

On Tue, 10 Apr 2001 07:55:28 -0700, Jeff Jennings wrote:

>Wes, > What I have used in the past is a DROP TABLE, CREATE TABLE, DATALOAD >sequence. This works quite well. It does have at least one major drawback in >that you also have to recreate all non-primary indexes on the table. > >Jeff > > > > >Wes Wright on [...] 7738 85 41_Re: DB2 on VM - mass deletes from a table16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 10 Apr 2001 22:57:45 +0100435_us-ascii Wes,

you can do this if you run in SINGLE USER mode, with LOGMODE=N. You then have to take a log archive.

If I remember rightly, don't know if this could be of any use to you, but Fillmore Group have a whole range of courses etc. on DB2 Server for VM (& VSE), which as far as I know are very good. I think Frank Fillmore actually subscribes to the listserve so he might actually get in touch - worth while! [...] 7824 172 37_Re: Integer Function in DB2 V5/OS390.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 10 Apr 2001 16:55:39 -0500335_us-ascii Armando,

This has been answered in many of the previous emails, but you may have missed it:

In V5, INTEGER cannot accept a character datatype (so you must use DECIMAL), and INT is not a valid synonym of INTEGER.

In V6, INTEGER can accept a character datatype, and INT and INTEGER are interchangable. [...] 7997 48 36_Re: applet support in DB2 for OS/3900_15_leon@CA.IBM.COM31_Tue, 10 Apr 2001 18:13:56 -0400451_us-ascii If you want to deploy Java applets that work with DB2 for OS/390 you must use DB2 Connect. DB2 Connect provides pure Java JDBC driver that is suitable for applet deployment.

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



Matt Kelley @RYCI.COM> on 04/09/2001 05:55:33 PM

Please respond to DB2 Data Base Discussion List [...] 8046 92 36_Re: applet support in DB2 for OS/3900_15_leon@CA.IBM.COM31_Tue, 10 Apr 2001 18:14:45 -0400467_us-ascii DB2 Connect provides everything that is needed to enable Java applets to work with DB2 for MVS/ESA or DB2 for OS/390 data.

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



Simon George @RYCI.COM> on 04/10/2001 03:04:37 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 8139 46 15_Re: DB2 V5 JDBC0_15_leon@CA.IBM.COM31_Tue, 10 Apr 2001 18:25:29 -0400596_us-ascii JDBC drivers for DB2 for OS/390 are provided from 2 sources: 1. DB2 for OS/390 itself and 2. DB2 Connect

Drivers provided with the DB2 for OS/390 product can be used to develop Java applications that run on OS/390 and access DB2 for OS/390 data. JDBC drivers provided with DB2 Connect are used to develop Java applets and applications that run on Windows, OS/2, various UNIXs, Linux/Intel and Linux/390. Drivers provided with DB2 Connect support DB2 for MVS/ESA V3.1, 4.1 and DB2 for OS/390 V5.1, 6.1 and 7.1. DB2 Connect also provides SQLJ support that can be used with the [...] 8186 35 44_Stored Procedures - WLM Refresh/APF Problems11_john turman20_turman10@HOTMAIL.COM31_Tue, 10 Apr 2001 17:36:31 -0500407_- Hi Everyone,

We are unable to run the WLM_REFRESH stored procedure AND create stored procedures from the 'workstation' without modifying the STEPLIB files in the WLM JCL on the mainframe.

When we make all the files APF authorized (in the WLM STEPLIB JCL) the WLM_REFRESH stored procedure works but we get a 'can not ALLOCATE DDNAME(PRCDBRM) rc=-3' error from the REXX program DSNTPSMP. [...] 8222 39 28_Re: web development listserv9_Eric Kwai18_Eric_Kwai@DTCC.COM31_Tue, 10 Apr 2001 17:52:00 -05001040_- Michelle: Try the following url: (One long url) http://www-1.ibm.com/servlet/support/manager? rt=3&rs=0&navkey=2ByDocumentCategory&path=category%3DForums+% 2F+discussion+groups%06Product+Group%3DSoftware%06Product+Family% 3DWeb+Application+Servers%06Product+Type%3DWebSphere+Application+Server

and

http://www-4.ibm.com/software/webservers/appserv/education.html http://edocs.bea.com/index.html

Regards, Eric

On Tue, 10 Apr 2001 11:48:34 -0500, mmetcalf@NOTES.STATE.NE.US wrote:

>Does anyone know of a listserv for web development? I have seen some >questions and answers here on the DB2-L but thought there might be another >one for more detailed web development information. I am specifically >interested in webserver connection pooling threads (JDBC). > >Thanks in advance for any input. > >Michelle Metcalf > >================================================ >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 [...] 8262 22 86_Connectivity to DB2 for OS/390 R6.1 - what is the minimum requirement/cheapest option?10_Rob Wright17_rwright@LIC.CO.NZ31_Wed, 11 Apr 2001 11:38:08 +1200431_us-ascii I have searched the archives, but haven't come up with an option other than DB2 Connect or possibly Neon Systems. Are there other ways to connect an application running on a Sun/Solaris box to DB2 for OS/390 6.1? What is the simplest/cheapest method of achieving this? I only really need an ODBC driver. Do I need to purchase DB2 Connect for this, or is there an option to purchase an ODBC driver at a lot less cost? [...] 8285 120 22_Re: AW: DB2 and COBOL?18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Wed, 11 Apr 2001 11:19:09 +0930517_us-ascii Howzit Linda?

And then you could but ball bearings on the 4 wheels and call it a 4GL-Wagon and get some real productivity improvements. :-)

Cheers Bruce











"Billings, Linda" @RYCI.COM> on 11/04/2001 01:34:59

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: AW: DB2 and COBOL? [...] 8406 19 46_Is there a limitation of the maximum blob size20_Yasas Mallawarachchi14_yasas@SLIIT.LK31_Wed, 11 Apr 2001 08:36:57 +0600483_iso-8859-1 Hi All,

I am using DB2 database in our AS/400 using client access for NT. I get an error when I try to store an image file with 1MB of size saying invalid blob length. Is there a limitation in BLOB field size?

Yashas

================================================ 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. 8426 26 36_Re: applet support in DB2 for OS/39011_Matt Kelley18_mgk333@HOTMAIL.COM31_Tue, 10 Apr 2001 21:45:32 -0500445_- >If you want to deploy Java applets that work with DB2 for OS/390 you must >use DB2 Connect. DB2 Connect provides pure Java JDBC driver that is >suitable for applet deployment. > >Leon Katsnelson >Product Manager, DB2 Application Development and DB2 Connect >mailto:leon@ca.ibm.com

While it may be true that this JDBC driver is pure Java, it always requires the JDBC Applet Server running on the same box as the web server, right? [...] 8453 74 25_Re: INFO on DB2 utilities10_Linda Ball18_linda_ball@BMC.COM31_Tue, 10 Apr 2001 23:58:41 -0500353_- MODIFY RECOVERY is in the DB2 Recovery and Diagnostics Utilities per IBM's WEB page. So is MODIFY STATISTICS.

Gerald seems to be in a loop asking Richard's opinion about C+/MODIFY (hi, Gerry), but Michael Murley has explained when IBM's MODIFY would be called if you are using the facility provided by BMC. See that message for that info. [...] 8528 36 40_Precompile warning using Identity Column11_Michael Lim22_MichaelLim@HKEX.COM.HK31_Wed, 11 Apr 2001 15:08:02 +0800467_- Hello all,

I have a seeding table defined as follows: CREATE SEED_TABLE ( NEXT_NUM DECIMAL(8,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENTED BY 1 CACHE 20))

The program issues: EXEC SQL INSERT INTO SEED_TABLE (NEXT_NUM) VALUES (DEFAULT) END-EXEC.

When the program is precompiled, I get the following warning: DSNH408I W DSNHANAL LINE 107 COL 15 STATEMENT INSERTS OR UPDATES COLUMN "NEXT_NUM" WITH INCOMPATIBLE DATA TYPE [...] 8565 183 44_Re: Precompile warning using Identity Column16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 11 Apr 2001 08:38:57 +0100623_- Hi Michael -

You can not INSERT into a column defined as IDENTITY. DB2 will automatically assign a value when you do an INSERT. So the warning message DATA_TYPE must reference to the IDENTITY clause.

Steen Rasmussen CA Denmark > -----Original Message----- > From: Michael Lim [SMTP:MichaelLim@HKEX.COM.HK] > Sent: Wednesday, April 11, 2001 9:08 AM > To: DB2-L@RYCI.COM > Subject: Precompile warning using Identity Column > > Hello all, > > I have a seeding table defined as follows: > CREATE SEED_TABLE ( > NEXT_NUM DECIMAL(8,0) NOT NULL > GENERATED ALWAYS AS IDENTITY > (START WITH 1 > INCREMENTED BY [...] 8749 76 44_Re: Precompile warning using Identity Column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 11 Apr 2001 13:48:09 +0530636_- This is interesting. What should be the INSERT query in this one column table then ?

Is this ok for not getting any warning ?

INSERT INTO SEED_TABLE (NEXT_NUM) VALUES ( )

Regards Sanjeev





> -----Original Message----- > From: Rasmussen, Steen [SMTP:Steen.Rasmussen@CA.COM] > Sent: Wednesday, April 11, 2001 1:09 PM > To: DB2-L@RYCI.COM > Subject: Re: Precompile warning using Identity Column > > Hi Michael - > > You can not INSERT into a column defined as IDENTITY. DB2 will > automatically assign a value when you do an INSERT. So the warning message > DATA_TYPE must reference to the [...] 8826 38 34_How to get rid of a STOPP status ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 10:46:38 +0200373_us-ascii Here a question from a friend of mine

DB2 V4, no recent PTFs applied (they are moving to an UNIX-based system)

They have an index (to recover) in STOPP. IMS WFI region was stopped but nothing changed. Token was canceled but

nothing changed. The first recover was canceled with MVS CANCEL FORCE command. So there are dirty entries in DB2. [...] 8865 69 44_Re: Precompile warning using Identity Column16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 11 Apr 2001 09:50:45 +0100338_- Ooooops - I was wrong. You can do the INSERT as described. I tried a couple of things - and when DECIMAL(8,0) is used in the CREATE statement - I get SQL-199. If I remove the scale so its DECIMAL(8) then the create is OK.

So I do not know WHAT this precompiler warning realy tells you - except that there is an APAR=PQ25498 [...] 8935 83 38_Re: How to get rid of a STOPP status ?14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 11 Apr 2001 09:58:37 +0100469_iso-8859-1 Find out what else is using it by issuing a -DIS DB(dbname) SPACENAM(ixname) USE (or LOCKS instead of USE) and exterminate them.

Ciao, Marcus Davage 'The views expressed are my own and do not necessarily represent the views or policy of my employer, Lloyds TSB Bank plc.'



-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: 11 April 2001 09:47 To: DB2-L@RYCI.COM Subject: How to get rid of a STOPP status ? [...] 9019 39 4_Help5_elibs17_elibs@NETEASE.COM31_Wed, 11 Apr 2001 17:11:27 +0800129_gb2312 Hello,List: could someone kindly tell me where to get the "DB2 Diagnosis Guide and Reference " book? Thank your in advance 9059 28 38_Re: How to get rid of a STOPP status ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 11:19:14 +0200393_us-ascii Hi Marcus...

They said that there's no resource/thread/util running on that index, there's only a STOPP status. The canceled recover

still exists as TOKEN if you execute a -DIS THREAD(*), but this util doesn't appers in a -DIS DB(xxx) USE/LOCKS.

They tried with -START DB(xxx) spacenam(yyy) access(RW/UT/FORCE) but nope, I think that some control blocks may [...] 9088 41 44_Re: Precompile warning using Identity Column11_Michael Lim22_MichaelLim@HKEX.COM.HK31_Wed, 11 Apr 2001 17:28:04 +0800636_- The APAR describes a similar problem but for NULLs instead of Identity columns. I already have the APAR applied anyway.

With regard to Sanjeev's suggestion, 'INSERT INTO SEED_TABLE (NEXT_NUM) VALUES ( )' does not help because the syntax is not valid.

Mike.



> -----Original Message----- > From: Rasmussen, Steen [SMTP:Steen.Rasmussen@CA.COM] > Sent: Wednesday, April 11, 2001 4:51 PM > To: DB2-L@RYCI.COM > Subject: Re: Precompile warning using Identity Column > > Ooooops - I was wrong. You can do the INSERT as described. > I tried a couple of things - and when DECIMAL(8,0) is used in the CREATE > [...] 9130 19 8_Re: Help14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Wed, 11 Apr 2001 04:38:17 -0500452_iso-8859-1 Hi Elibs,

as far as I know you have to buy it. It is not available for free on IBM Bookmanager website.

Ask around at your site - if you have DB2 - someone may be hoarding it.

CC

================================================ 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. 9150 26 8_Re: Help18_Michael J. O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV31_Wed, 11 Apr 2001 05:56:23 -0500458_US-ASCII the diagnosis guide is provided when you buy DB2... one copy per installation package... as far as i know



______________________________ Reply Separator _________________________________ Subject: Help Author: "DB2 Data Base Discussion List" at smtplink Date: 4/11/01 4:11 AM



Hello,List: could someone kindly tell me where to get the "DB2 Diagnosis Guide and Reference "book? Thank your in advance [...] 9177 105 8_Re: Help16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Wed, 11 Apr 2001 11:55:23 +0100342_us-ascii





The Diag Guide & Ref books are part of the Licensed books - and they are not available for download or free browse from the IBM websites.

However, one copy (or more if you ordered and paid for them) are included with the Software Product - so you should have a copy with the rest of your manuals. [...] 9283 34 44_Re: Precompile warning using Identity Column13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 11 Apr 2001 07:10:16 -0400405_iso-8859-1 Hello Michael, Looked originally to me like you have created an unusable table.

Indentity columns specifying "Genereated always" tells DB2 you won't be supplying a value for it. However, the Insert syntax appears to require you to supply a column and value. Sounds like a famous Joseph Heller novel.

But, further research and experimentation shows you are merely missing the [...] 9318 92 18_Re: regarding load0_29_sflindsey@HIGHLIGHTS-CORP.COM31_Wed, 11 Apr 2001 07:22:25 -0400547_us-ascii You cannot restrict the data with the load utility. If you have not unloaded the data yet, you should use DSNTIAUL to do the unload. DSNTIAUL is standard with DB2. If you specify the SQL parm option, you can include a specfic SQL statement and the where clause that will limit the unload to the 20,000 rows that you need. SYSREC00 is your unload file and SYSPUNCH will create load cards for the data that was unloaded. You can then use the load utility to load the data to your test table. See the DSNTIAUL example below if you have [...] 9411 59 38_Re: How to get rid of a STOPP status ?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 11 Apr 2001 17:16:41 +0530582_- Max,

We had to face the same situation in my previous installation and we had to stop and restart DB2. Fortunately it was with the test subsystem.

let's see if someone else have better solution.

Regards Sanjeev

> -----Original Message----- > From: Max Scarpa [SMTP:mscarpa@CESVE.IT] > Sent: Wednesday, April 11, 2001 2:17 PM > To: DB2-L@RYCI.COM > Subject: How to get rid of a STOPP status ? > > Here a question from a friend of mine > > DB2 V4, no recent PTFs applied (they are moving to an UNIX-based system) > > They have an index (to recover) [...] 9471 29 8_Re: Help16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 11 Apr 2001 17:19:39 +0530471_- Not available free in web. It comes with licensed copy of DB2 or probably have to be bought.

BUT WHY is this so ?? Some IBMer's may reply to this.

Regards Sanjeev

> -----Original Message----- > From: elibs [SMTP:elibs@NETEASE.COM] > Sent: Wednesday, April 11, 2001 2:41 PM > To: DB2-L@RYCI.COM > Subject: Help > > Hello,List: > could someone kindly tell me where to get the "DB2 Diagnosis Guide and > Reference " book? > Thank your in advance [...] 9501 52 39_Help W/Partition Table Rolloff Strategy25_Cavanaugh, Michael S H07A27_Michael.Cavanaugh@CIGNA.COM31_Wed, 11 Apr 2001 07:44:34 -0400400_us-ascii We have a tablespace containing approx. 2 years worth of data partitioned in 2 week blocks (60 partitions) by TIMESTAMP. Once full, the 2year+ data will be removed and stored on a separate archive table. As we near the point of filling the last partition, we're looking for strategies with which to simplify the management of these partitions. Our options at this point are as follows: [...] 9554 61 44_Re: Precompile warning using Identity Column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 11 Apr 2001 17:29:16 +0530396_- Oh, missed this one. Thanks Dave for the clarification. "Overriding value". Insert syntax in Sql Reference also mentions:

For a ROWID or an identity column that was defined as GENERATED ALWAYS,you must specify DEFAULT unless you specify the OVERRIDING USER VALUE clause to indicate that any user-specified value will be ignored and a unique system-generated value will be inserted. [...] 9616 55 22_Re: AW: DB2 and COBOL?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 11 Apr 2001 07:23:40 -0500399_- Thanks goodness, I wasn't telling you something that you didn't already know. It makes me crazy when I see managers make decisions like that. I suppose that if the resources aren't there then you gotta do what you gotta do. However, it is possible that you are wasting more money on operating the system that way than it would cost if you just rewrote the thing, hopefully, more efficiently. [...] 9672 137 22_Re: AW: DB2 and COBOL?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 11 Apr 2001 07:24:23 -0500416_- Does that come with nifty wood-grain side panels?

Linda

-----Original Message----- From: Bruce W Williamson [mailto:Bruce.Williamson@NT.GOV.AU] Sent: Tuesday, April 10, 2001 8:49 PM To: DB2-L@RYCI.COM Subject: Re: AW: DB2 and COBOL?



Howzit Linda?

And then you could but ball bearings on the 4 wheels and call it a 4GL-Wagon and get some real productivity improvements. :-) [...] 9810 119 43_Re: Help W/Partition Table Rolloff Strategy16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 11 Apr 2001 18:02:55 +0530596_iso-8859-1 Hi Mike,

If you go through the technical update of DB2 UDB for V6 you will find one good tricks mentioned to use Timestamp as the partitioning key. Please find the paragraph mentioned below from Technical Update.

<< In most cases, a timestamp format is not acceptable to the business, as the number will be quoted and will appear on documents. Timestamps are most useful for key generation when a random number is required. Then a reversed form can be a simple way of generating a key. Timestamps are also useful when you require rows to be inserted randomly across [...] 9930 24 38_Re: How to get rid of a STOPP status ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 14:54:57 +0200291_us-ascii Hi Sanjeev, howizit ??

Yes I experimented this some years ago, and apparently there's no solution. I heard that in V5 this problems is not met

but I don't know if this is due to some new PTFs /improvements for V5 or it's due to the fact nobody met a STOPP status [...] 9955 40 25_numlkus max reached ? why15_Neff, Stephen R16_NeffSR@STATE.GOV31_Wed, 11 Apr 2001 08:54:00 -0400345_iso-8859-1 Hello,

A program has two sets of tables it updates. One time via package set it acts on SETA. SETA has more data and more than likely more updates then SETB. And yet when the program runs using SETA there is no problem. However infrequently when the program works on SETB table the program receives the following message: [...] 9996 20 85_Re: Joins - any good documentation (concise and clear) on the var ious types of joins10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 15:05:22 +0200372_us-ascii What about:

http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM ???

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10017 67 29_Re: numlkus max reached ? why12_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 11 Apr 2001 09:10:42 -0400396_US-ASCII The total pages is on all tablespace and indexspace you touch not just the one you died on. One thought is that the index pages are split and you have more index pages to touch for SETB than you have for SETA. Reorgs may help. If you can't change the application to put more frequent commits in then you may consider changing the locksize to any and allow DB2 to escalate the lock. [...] 10085 60 29_Re: numlkus max reached ? why11_David Nance16_DWNance@FHSC.COM31_Wed, 11 Apr 2001 09:15:02 -0400376_US-ASCII Does this program perform any commits during or after processing SETA, or any where else in the program? Are there any other jobs running that may have a high number of locks? Remember you are reaching you're system maximum, not your table max.

Dave Nance First Health Services, Corp. (804)527-6841

>>> NeffSR@STATE.GOV 04/11/01 08:54AM >>> Hello, [...] 10146 64 29_Re: numlkus max reached ? why0_19_mike.holmans@BT.COM31_Wed, 11 Apr 2001 14:11:31 +0100616_- I don't suppose you've managed to specify something other than LOCKMAX SYSTEM for one set of tablespaces but not the other, have you?

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

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

> -----Original Message----- > From: Neff, Stephen R [SMTP:NeffSR@STATE.GOV] > Sent: Wednesday, April 11, 2001 1:54 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] numlkus max reached ? why > > Hello, > > A program has two sets of tables it updates. One time via package > set it acts on SETA. [...] 10211 57 38_Re: How to get rid of a STOPP status ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 11 Apr 2001 08:27:23 -0500588_iso-8859-1 Max, this may be one of those times when a drop and recreate of SYSUTILX is required, with all the normal caveats . . .

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 [...] 10269 122 40_Re: Help W/Partition Table Rolloff Strat64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 11 Apr 2001 08:49:01 -0500454_ISO-8859-1 Hi Michael.

Managing partitioned tablespaces can be a pain. Your particular scenario, of an ever-increasing key - getting ready to hit the last partition, was posed to us several years ago. We developed a technology to exactly solve that problem. We then turned it into product called CDB/PartRoll. Since then, however, we have merged that technology with another one for managing partitions, and we now call them both CDB/Repart. [...] 10392 21 38_Re: How to get rid of a STOPP status ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 15:58:33 +0200315_us-ascii Hi Rick, howizit ??

I'm not sure of this, but there's no 'frozen' utility in SYSUTILX, I guess that at this level there's some dirty control block you

cannot 'clean' unless to shoot DB2 between its (her ? his ? Is DB2 a female or a male ? Boh...) blue eyes....

Regards Max Scarpa [...] 10414 94 29_Re: numlkus max reached ? why15_Neff, Stephen R16_NeffSR@STATE.GOV31_Wed, 11 Apr 2001 10:05:34 -0400512_iso-8859-1 Thanks you --- QUESTIONS ASKED AND ANSWERED

I don't suppose you've managed to specify something other than LOCKMAX SYSTEM for one set of tablespaces but not the other, have you?

NO, All tablespaces have lockmax set the same to 0.

Does this program perform any commits during or after processing SETA, or any where else in the program? Are there any other jobs running that may have a high number of locks? Remember you are reaching you're system maximum, not your table max. [...] 10509 25 18_Re: regarding load10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Apr 2001 16:22:34 +0200368_us-ascii You may build an unique index in the target table so you can discard rows having duplicate keys during load

It's a (somewhat) self- limitating method, but in this case source and target doesn't match, target has 1 (may be)

perturbating index (but it may be dropped after you used it) and often you cannot build such index; but sometimes it [...] 10535 48 38_Re: How to get rid of a STOPP status ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 11 Apr 2001 09:31:50 -0500619_iso-8859-1 Would whoever is causing the below to be sent out please stop. Note the email address provided in the message isn't valid either, jeeeeeez!

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, [...] 10584 123 29_Re: numlkus max reached ? why11_David Nance16_DWNance@FHSC.COM31_Wed, 11 Apr 2001 10:32:05 -0400523_US-ASCII How many locks does this job accumlate? As I said your hitting your SYSTEM lockmax, meaning not just updates done in SETB, but everything within your subsystem. Since your program has no commits and you say it is doing a fairly large amount of updates on SETA. You need to put some type of commit logic into your program(i.e. every 5000 updates, or something). Do you have any other large batch applications running at the same time that, also, do not commit and are taking up more than their share of locks? [...] 10708 23 23_Modify Recovery Options9_Wu, James13_jwu@KRAFT.COM31_Wed, 11 Apr 2001 09:35:41 -0500589_iso-8859-1 I would like to make a suggestion to IBM:

Currently Modify Recovery has two DELETE options: AGE and DATE. Can IBM add another option called 'GENERATION' (or 'COPIES', or whatever) that allows us to keep a certain number of image copies and delete the rest, regardless the 'age' of the image copies. This new option would be very useful for those 'rarely changed' tablespaces and partitions which we only need to take the image copies once for a long period of time. It could also avoid the last image copy being deleted by the Modify Recovery which forces the 'Copy [...] 10732 29 20_ACCESS PATH QUESTION16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 11 Apr 2001 07:39:29 -0700596_us-ascii We have an application performing an update of various fields in a table, and is coded such that the predicate matches on 6 of the first 10 columns of the unique clustering index of a partitioned tablespace (64 partitions noncompressed rows). The explain output indicates a list prefetch access which seems odd due to the fact that the entire index is 100 percent clustered, and is only in four levels. One other thing to mention is that all the columns referenced within the predicate are matched up against host variables and no literals are used. Additionally, when I change the [...] 10762 155 29_Re: numlkus max reached ? why23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 11 Apr 2001 11:06:28 -0400577_iso-8859-1 While SETB tables have less data than SETA, it is quite likely that more rows/pages are updated while processing SETB. Hence the process hits NUMLKUS limit(which is set at the process level not sub-system). Since you have not mentioned anything about the kind of processing done, would it be possible for you to run the update/delete SQLs as SELECT and come with number of rows affected in SETB and SETA. You would also have to consider any INSERT statement. I know the lock level for all tables is 'PAGE', but it may give you an explanation for your query. As [...] 10918 37 8_Re: Help23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Wed, 11 Apr 2001 11:10:20 -0400426_- Diagnosis guide is a restricted publication and is only provided to IBM DB2 customer (with a valid customer id).

Sanjay Jain

> -----Original Message----- > From: elibs [SMTP:elibs@NETEASE.COM] > Sent: Wednesday, April 11, 2001 5:11 AM > To: DB2-L@RYCI.COM > Subject: Help > > Hello,List: > could someone kindly tell me where to get the "DB2 Diagnosis Guide and > Reference " book? > Thank your in advance [...] 10956 144 29_Re: numlkus max reached ? why15_Neff, Stephen R16_NeffSR@STATE.GOV31_Wed, 11 Apr 2001 11:18:33 -0400545_iso-8859-1 thank you,

The definition of numlkus from the INSTALLATION GUIDE is

Specify the maximum number of page or row locks that a single application can hold concurrently on all table spaces. The maximum includes locks on data pages, index pages, subpages, and rows that the program acquires when it accesses table spaces. The limit applies to all table spaces defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. 0 means that there is no limit to the number of page and row locks a program can acquire. [...] 11101 72 24_Re: ACCESS PATH QUESTION0_19_Tim.Lowe@STPAUL.COM31_Wed, 11 Apr 2001 10:32:59 -0500415_us-ascii Jeff, Have you gathered KEYCARD stats so that the optimizer knows the cardinality of the first 6 columns of the index?

Thanks, Tim





"Jeff A L'Italien" To: DB2-L@RYCI.COM Subject: ACCESS PATH QUESTION Sent by: DB2 Data Base Discussion List



04/11/2001 09:39 AM Please respond to DB2 Data Base Discussion List [...] 11174 16 34_Problems reading listserv messages12_Roland Holup19_rholup@MEGSINET.NET31_Wed, 11 Apr 2001 10:04:10 -0500348_- I have been receiving the DB2 listserv for over a year. I have been able to read all the attached e-mail until the past few days. When I click on the e-mails, some of them return the Windows Internet Explorer (IE) pulls up the browser and displays, "Invalid syntax error IE". Inside the the text box is says, "The page cannot be displayed". [...] 11191 67 41_Re: DB2 Batch Checkpoint Restart Products14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Wed, 11 Apr 2001 10:35:39 -0500928_iso-8859-1 BMC Software offers the Application Restart Control product to control checkpoint/restart in application programs. It can be deployed to provide automatic checkpoint/restart services without requiring application changes. Details can be found at: http://www.bmc.com/products/proddocview.html?id=2005&r=Americas&l=en

Cheers, Craig S. Mullins Director, DB2 Technology Planning BMC Software http://www.bmc.com Craig_Mullins@bmc.com http://www.craigsmullins.com craig@craigsmullins.com

------------------------------ >Date: Tue, 10 Apr 2001 13:18:05 -0400 >From: Cam White >Subject: Re: DB2 Batch Checkpoint Restart Products > >Softbase Systems has batch checkpoint/restart tools. Their website >is http://www.softbase.com > >Best wishes, >Cam White >RevealNet, Inc. > > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Iris [...] 11259 43 7_DB2DART10_Mark Savin31_Mark.Savin@SCOOT-TECHNOLOGY.COM31_Wed, 11 Apr 2001 16:43:01 +0100669_iso-8859-1 Hi all,

I have a question: is it safe to run DB2DART online? We're using DB2 UDB v6.1 on AIX 4.3.3 on our website and we're trying to see if we can get away with no downtime.

Any thoughts would be greatly appreciated!

Thanks,

Mark Savin DBA Scoot Tech



********************************************************************** The contents of this e-mail and any attachments are intended for the named addressee only and may be confidential. Unless you are the named addressee or authorised to receive the e-mail of the named addressee you may not disclose, use or copy the contents of the e-mail. If you received [...] 11303 76 11_Re: DB2DART18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 11 Apr 2001 11:57:17 -0400589_iso-8859-1 I highly recommend you use db2dart in the offline mode. Without any real documentation, I believe db2dart reads the files directly without going through the dbms. So if your dbms is writing to the database, db2dart could find partially written pages and identify them as bad.

I have spent a significant amount of time studying db2dart and working with IBM to try and get better documentation. Their db2dart /h help facility states you should run this offline. I have heeded that recommendation since this is really a debugging utility for IBM and not a DBA utility. [...] 11380 35 27_Re: Modify Recovery Options20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Wed, 11 Apr 2001 12:05:33 EDT143_US-ASCII James, Great idea. We really need this option.

Ed DB2 Recovery On-site and Off-site DR http://www.recoveryknowledge.bigstep.com 11416 76 36_Re: applet support in DB2 for OS/3900_15_leon@CA.IBM.COM31_Wed, 11 Apr 2001 12:23:31 -0400581_us-ascii Everything that you said in your email is absoltely correct. This JDBC driver communicates with the JDBC Applet Server which is an integral part of the DB2 Connect server products. Threfore, DB2 Connect server must be used. Java sand box rules dictate that unsigned applets must come from the same server. Therefore, DB2 Connect and a web server must reside on the same machine. Signed Java applets do not have to abide by this rule. Using signed Java applets would allow using a web server that does not run on the same machine. For example, you can run DB2 Connect [...] 11493 85 9_IDUG Info14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 11 Apr 2001 09:30:24 -0700626_- FYI.IDUG 2001 - North America Discount Deadline Approaching

IDUG 2001 - North America is quickly approaching, May 13-17, in Orlando, Florida. Register by April 13, and save $145 off the full registration price. Now in its 13th year, this premier DB2 education event, hosted by the International DB2 Users Group (IDUG) will feature more than 140 technical sessions, covering the full breadth of the DB2 product family. Featured presenters will include IBM's Janet Perna & Dr. Pat Selinger and 1996 Mt. Everest disaster survivor Beck Weathers, plus more. Plus new this year, IDUG and IBM will be offering free DB2 [...] 11579 141 91_Re: Connectivity to DB2 for OS/390 R6.1 - what is the minimum req uirement/cheapest option?14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 11 Apr 2001 09:38:10 -0700590_iso-8859-1 Rob, You need software that converts the request to DRDA protocol. DB2 Connect provides an IBM ODBC driver but in addition DB2 Connect serves as an Application requestor in the DRDA scheme of things. There are alternatives to DB2 Connect, although I have never used them, as DB2 Connect has filled my requirements. Some third party products do not convert the data stream to DRDA protocol but have a STC on the host that passes the request to DB2 using CAF. You really need to test your requirements with DB2 Connect and any other possibility and determine which product is [...] 11721 39 33_problems after implementing DFSMS14_Enser, Matthew27_Matthew.Enser@USA.XEROX.COM31_Wed, 11 Apr 2001 12:32:46 -0400582_ISO-8859-1 Our DBA group recently implemented DFSMS for our IMS and DB2 databases. The implementation over the weekend went fine, for IMS we coded 50 asterixes (*) in the IDCAMS defines, and for DB2 we added them to the stogroups. (as many *'s as there are candidate SMS volumes, decreed by our storage management group) The problems started Monday morning when the IMS databases were being allocated to our online system. We received messages stating that no more datasets could be opened. The problem seems to be that the TIOT table had ran out of space due to the number of [...] 11761 47 21_Re: DB2PM sort option15_Hernandez, Hugo22_Hugo_Hernandez@BMC.COM31_Wed, 11 Apr 2001 11:52:17 -0500464_iso-8859-1 Just add a new DD in your DB2PM jcl, the sintax would be:

//DFSPARM DD * your new sort parameters

HTH

Hugo A. Hernandez Torres Software Consultant BMC Software de Mexico, S.A. de C.V. mailto:hugo_hernandez@bmc.com



-----Original Message----- From: ccemilog@YKB.COM [mailto:ccemilog@YKB.COM] Sent: Thursday, April 05, 2001 9:31 AM To: DB2-L@RYCI.COM Subject: DB2PM sort option [...] 11809 119 11_Re: DB2DART10_Mark Savin31_Mark.Savin@SCOOT-TECHNOLOGY.COM31_Wed, 11 Apr 2001 17:52:41 +0100420_iso-8859-1 Mate,

It looks like a problem's occurring with some DB2 APARs which are fixed in FixPak 7. We're trying so see what index corruption (if any) is on the db at the moment.

Cheers for your help.

Mark





-----Original Message----- From: Krawetzky, Peter J [mailto:KrawetzkyPJ@AETNA.COM] Sent: Wednesday, April 11, 2001 4:57 PM To: DB2-L@RYCI.COM Subject: Re: DB2DART [...] 11929 65 37_Re: problems after implementing DFSMS16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 11 Apr 2001 12:55:23 -0400310_iso-8859-1 Don't know about IMS, but what works for us in DB2 V5 is CREATE STOGROUP sgname VOLUMES('*') VCAT hlq ;COMMIT;

DB2 then finds all the vols defined in the SMS storage group which match the DSN FILTLIST in the SMS ACS routine. SMS folks add/remove vols from the SMS storage group at will. [...] 11995 135 11_Re: DB2DART18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 11 Apr 2001 13:14:57 -0400398_iso-8859-1 I will say it's very possible to have the corruption and not know it. Our online client-server environment never hit the corrupt index pages. It was an archive process that hit the corrupt pages of the index.

-----Original Message----- From: Mark Savin [mailto:Mark.Savin@SCOOT-TECHNOLOGY.COM] Sent: Wednesday, April 11, 2001 12:53 PM To: DB2-L@RYCI.COM Subject: Re: DB2DART [...] 12131 96 37_Re: problems after implementing DFSMS0_19_Tim.Lowe@STPAUL.COM31_Wed, 11 Apr 2001 12:38:18 -0500572_us-ascii Matt, Yes, I encountered this problem several years ago. You might want to address this to the IMSlist rather than the DB2list since you are really describing an IMS problem.

Obviously, you might want to consider using less than 50 volumes (we use 4-8 volumes depending on database size). You did not say what IMS release you are running, but you might want to consider migrating to IMS V6 which uses an "extended TIOT" (above the line) to be able to keep more datasets open. (See "Support for UCB VSCR" in the IMS V6 release guide. IMS V5 w/64K TIOT [...] 12228 23 20_DB2 V6 / DB2 for AIX11_Moore, Tony15_TMoore@IKON.COM31_Wed, 11 Apr 2001 13:56:03 -0400323_iso-8859-1 Yo Listers, We are in the planning stages of a DB2/MVS V4 to V6 upgrade. In our UNIX environment, we have DB2 for AIX 2.1.2 which acts as a gateway for UNIX applications accessing DB2/MVS data. We are at V1.2 of OS/390 but will be at V2.10 at the time of the DB2 upgrade. Our UNIX environment is AIX 4.3.3. [...] 12252 35 37_Looking for DB2 user base information15_Madison Pereira21_MktgStudent11@AOL.COM29_Wed, 11 Apr 2001 13:59:02 EDT195_US-ASCII Hello everyone, I was hoping someone could tell me how many worldwide DB2 users IBM has. How large is their install base? Is it bigger then Oracle's?

Thank you in advance, Madison 12288 38 13_DB2 Wait time14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 11 Apr 2001 14:08:22 -0500480_iso-8859-1 Listers,

I have a Strobe report that indicates 95% of a batch process' runtime is "wait time". 82% of that is in DB2 and narrowed down to one open cursor statement. It appears the source is the DSNVSR csect. The function description for the csect is SUSP/RES/CANCEL Synchron

I don't have Strobe manuals yet and did not find any information in the DB2 Manwells on DSNVSR...Any ideas on how to tell precisely what we are waiting on - Locks,I/O, etc.?? [...] 12327 29 53_Fw: Formatting snapshot/event monitor outputs:DB2/AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 11 Apr 2001 15:56:06 -0400659_iso-8859-1 ----- Original Message ----- From: "Manas Dasgupta" To: Sent: Wednesday, April 11, 2001 3:50 PM Subject: Formatting snapshot/event monitor outputs:DB2/AIX



> I am currently working on setting up a process of extracting performance > data from DB2/AIX (snapshot and event monitors) and putting them in tables > so that we have a record of DB2 activity that we can query against. > I was wondering if anyone had done similar work parsing through > snapshot/event monitor output and would like to share scripts or have any > advice or pointers on this topic. > > Thanks, > Manas. [...] 12357 12 17_Stored Procedures13_Davis, Ronald17_DavisRo@CONED.COM31_Wed, 11 Apr 2001 16:05:58 -0400313_iso-8859-1 If I have only 1 result set, do I need a result set locator.

================================================ 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. 12370 66 17_Re: DB2 Wait time12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Wed, 11 Apr 2001 15:09:44 -0500368_iso-8859-1 Scott:

If you look in SDSNSAMP members DSNWMODS and DSNWEIDS you'll see entries, but I doubt if they'll be of much help. The IFCID 93 documented in DSNWMSGS in SDSNSAMP has a little more information. It states that a suspend was call, but that a suspension of the TCB or SRB might or might not happen. The record is marked for IBM Service only. [...] 12437 127 18_Re: regarding load17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Wed, 11 Apr 2001 16:01:56 -0500325_- HI sflindsey,

Thank you for your suggession, as per you i am using the sql to unload the data and keeping in the dataset . but for my sql with predicate class... i am getting almost 100000 records... so how would i restrict to download only 20000 records. is there any option do i need to specify in the sql??? [...] 12565 26 21_Re: Stored Procedures19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Wed, 11 Apr 2001 16:11:03 -0500514_iso-8859-1 Yes, I think

-----Original Message----- From: Davis, Ronald [mailto:DavisRo@CONED.COM] Sent: Wednesday, April 11, 2001 1:06 PM To: DB2-L@RYCI.COM Subject: Stored Procedures



If I have only 1 result set, do I need a result set locator.

================================================ 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. [...] 12592 169 18_Re: regarding load16_Richard Muirhead19_dbaguru@HOTMAIL.COM31_Wed, 11 Apr 2001 16:37:44 -0500 12762 68 17_Re: DB2 Wait time11_Chad Walmer19_cwalmer@RITEAID.COM31_Wed, 11 Apr 2001 17:52:06 -0400577_iso-8859-1 Scott, I also recently noticed a high amount of service wait time for a batch job. As it turns out, each time the wait time was very high, the batch job had abended. I believe this time reflects how much time was spent aborting (rolling back) any changes. If you have the accounting data available, I would suggest running a detailed accounting report for the batch job. I believe you will see the COMMIT UPDATE time to be the majority of this service wait time. Check out the description of Suspension/System Times and Events for the accounting report on page [...] 12831 38 55_Stored Procedure Builder - Communication Error Detected11_john turman20_turman10@HOTMAIL.COM31_Wed, 11 Apr 2001 16:58:41 -0500645_- Hello Everyone,

I'm getting the following message from the stored procedure builder on the desktop, any thoughts?

Thanks, John

SYSPROC.WLM_REFRESH - Calling stored procedure.

SYSPROC.WLM_REFRESH - The value(s) of the input parameters:

WLM_ENV_NAME = WLMENVP SSID = DSNP



[IBM][CLI Driver][DB2] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the error: "recv". Protocol specific error code(s): "*", "*", "0". SQLSTATE=08001 [...] 12870 24 38_Submitting a JCL from Stored procedure19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Wed, 11 Apr 2001 17:41:02 -0500376_iso-8859-1 Hello Experts, I was trying to submit a JCL from stored procedure by using IKJTSOEV service and then invoking rexx and from rexx I am trying to submit a JCL. Will it work?

Actually my requirement is to submit a job when user clicks a button in front end which is web enabled. Which will be easiest way to do this job? MQs or any other kind of triggers. [...] 12895 167 22_Re: AW: DB2 and COBOL?18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Thu, 12 Apr 2001 08:57:18 +0930702_us-ascii Howzit Linda?

Nah, but it does have teflon coated seat covers.

Cheers Bruce









"Billings, Linda" @RYCI.COM> on 11/04/2001 21:54:23

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: AW: DB2 and COBOL?





Does that come with nifty wood-grain side panels?

Linda

-----Original Message----- From: Bruce W Williamson [mailto:Bruce.Williamson@NT.GOV.AU] Sent: Tuesday, April 10, 2001 8:49 PM To: DB2-L@RYCI.COM Subject: Re: AW: DB2 and COBOL? [...] 13063 57 42_Re: Submitting a JCL from Stored procedure10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 12 Apr 2001 10:26:41 +1000350_us-ascii Hi Balaji,

I am not sure if u have any specific requirement to invoke stored procedures. If all u need to do is submit a job, and DB2 is not needed to be involved, u can submit it using ftp to the mainframe. U shd be able to call ftp when the user clicks the button. The job can be in a file on ur PC, or in a mainframe dataset. [...] 13121 74 42_Re: Submitting a JCL from Stored procedure19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Wed, 11 Apr 2001 19:47:32 -0500442_iso-8859-1 Hi kals, Your first suggesting looks interesting. I am with you until we ftp it to mainframe. But how can we trigger the job. Can you pl elaborate on this. Conceptually I am not clear about how can we submit a job from ftp. Regards

-----Original Message----- From: teldb2kals [mailto:teldb2kals@TELSTRA.COM] Sent: Wednesday, April 11, 2001 5:27 PM To: DB2-L@RYCI.COM Subject: Re: Submitting a JCL from Stored procedure [...] 13196 79 38_Re: How to get rid of a STOPP status ?25_Robinson, Peter [IBM GSA]33_Peter.J.Robinson@TEAM.TELSTRA.COM31_Thu, 12 Apr 2001 10:51:08 +1000533_- Hi Max

In V4 the command timeout may not work but under V6 it does - we had a recent test in production :-( The accidental stop of a highly used tablespace during our peak load took about 35 minutes to time-out.

The tablespace got stuck in read only with stop pending. The most expedient way to get all the activity out of the way and let the stop go ahead (just to start it again) was to shut down the CICS-DB2 attaches for a bit. Before we could do this, there had to be endless conference calls with business [...] 13276 45 22_Re: AW: DB2 and COBOL?15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 12 Apr 2001 11:03:22 +1000675_- I don't think 'nifty' is the adjective of choice regarding 'wood'-grain side panels. More like naff, cheezy, gaudy, tacky, etc...



Raymond ;o) PS. I apologise in advance if this gets (is already?) too off-topic for some.

> -----Original Message----- > From: Billings, Linda [SMTP:linda.billings@DOA.STATE.WI.US] > Sent: Wednesday, 11 April 2001 10:24 pm > To: DB2-L@RYCI.COM > Subject: Re: AW: DB2 and COBOL? > > Does that come with nifty wood-grain side panels? > > Linda > > -----Original Message----- > From: Bruce W Williamson [mailto:Bruce.Williamson@NT.GOV.AU] > Sent: Tuesday, April 10, 2001 8:49 PM > To: DB2-L@RYCI.COM > Subject: Re: AW: [...] 13322 110 42_Re: Submitting a JCL from Stored procedure10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 12 Apr 2001 11:21:20 +1000438_us-ascii Hi Balaji,

I dont know abt ur actual front end interface, hence i cannot help u much with triggering the ftp. But I am sure there must be an easy way to invoke ftp/email/telnet and other such programs from ur front end. (I am not much familiar with web programming, but in a nutshell, if u have a web screen with an OK button, u shd be able to trigger off a script which invokes ftp and passes the desired commands.) [...] 13433 17 22_Re: AW: DB2 and COBOL?26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Wed, 11 Apr 2001 20:37:58 -0500517_- Maybe there should be an OT DB2-L? When I was on the West coast, my neighbor had a real Woody with a vanity plate of "Woody1". Cherry Red had a room full of blue ribbons. Actually drove it on weekends! Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 13451 52 42_Re: Stored procedures - different versions10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 12 Apr 2001 11:51:29 +1000409_us-ascii Hi Michele,

DB2 v6 allows this facility using SCHEMA, but I am not so sure abt v5. A possibility (though not attractive) - u can have different proc names for the two versions, and then check the environment(system test or unit test) in ur program and call the correct proc.

Another (unattractive) possibility - U can try getting a second userid for the time till u migrate to v6. [...] 13504 40 42_Re: Submitting a JCL from Stored procedure23_Puddy, Andrew (CALBRIS)36_Andrew.PUDDY@COMALCO.RIOTINTO.COM.AU31_Thu, 12 Apr 2001 03:43:31 -0000478_iso-8859-1 one way to fulfil your requirements would be to use FTP to JES.

Andrew Puddy

-----Original Message----- From: K.Balaji (Exchange) [mailto:K.Balaji@TARGET.COM] Sent: Thursday, 12 April 2001 8:41 To: DB2-L@RYCI.COM Subject: Submitting a JCL from Stored procedure



Hello Experts, I was trying to submit a JCL from stored procedure by using IKJTSOEV service and then invoking rexx and from rexx I am trying to submit a JCL. Will it work? [...] 13545 31 43_Re: Help W/Partition Table Rolloff Strategy7_Sri Sri20_sri_db2l@HOTMAIL.COM31_Wed, 11 Apr 2001 23:37:39 -0500563_- Hi, Michael

I think, it is not required to drop the table for this purpose. Use the technique "Rebalancing of the partitions" for this. This is valid only for DB2 V6 OS/390

The following steps may be able to help you. These steps you need to experiment in a test database.

a) Take a full image copy of the tablespace. b) Unload the data from the first fewer partitions which you want to archive. c) delete the data from the partitions which you archived. (assuming this is a dependent table and is not a parent for any other table and [...] 13577 36 18_Re: regarding load7_Sri Sri20_sri_db2l@HOTMAIL.COM31_Thu, 12 Apr 2001 00:03:30 -0500574_- Hi,Ravi

It will be easier if you have a driver table in your system. This kind of requirement came once in our system to create a 'STRESS test database'. ---------------------------------------------------------------------- say From your production system if you want to load a certain number of records into a test database. ---------------------------------------------------------------------

Suppose you have a driver table say 'TAXES'. say,Your test team is interested in rows from the 'TAXES', 'TAX-HISTORY', 'TAX-LOCATION' tables for 1999 year. [...] 13614 31 8_SQL4300N14_Hrishiraj Nair28_hrishirajnair@REDIFFMAIL.COM31_Thu, 12 Apr 2001 05:18:45 -0000321_- We have DB2 Workgroup Edition v7.1 running on Suse Linux 6.4. The installation is perfectly okei and I am able to use the database.

The problem is when I try to create stored procedures from a sample (eg; Outcli.java) I get an error SQL4300N. I have set the JDK11_PATH to my Java location /usr/jdk118/bin/. [...] 13646 30 18_DB2 UDB for AS/40030_GROUPIT - Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY31_Thu, 12 Apr 2001 14:15:11 +0800334_iso-8859-1 I would appreciate assistance on connectivity to DB2 UDB for AS400 V4R4:

1) Phase 1 : User uses a browser to access DB2 UDB for AS400 directly Browser ===> DB2 UDB for AS400

2) Phase 2 : User uses a browser to access same DB2 UDB AS400 over the web

Browser ====> WebServer ====> DB2 UDB for AS400 [...] 13677 13 44_Paul Murphy/MDSC/MAYCO is out of the office.11_Paul Murphy22_Paul_Murphy@MAY-CO.COM31_Thu, 12 Apr 2001 01:07:46 -0500336_us-ascii I will be out of the office starting 04/12/2001 and will not return until 04/17/2001.

================================================ 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. 13691 31 37_Re: problems after implementing DFSMS10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 12 Apr 2001 09:09:48 +0200388_us-ascii Hi Matthew,

I met this problem (or a very similar problem) some years ago, using a new release of a financial CICS/DL-I (no local

DL/I) application and the problem was due to TIOT size (datasets opens > 10000). We had to reduce the number of

dataset belonging to that application. We had problems with RECON datasets,too, due to the high number of DL/I [...] 13723 28 18_Re: regarding load18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE31_Thu, 12 Apr 2001 02:57:22 -0500548_- Hello,

As far as I can see in the Load Doc (db2 v5, sorry we are not v6 yet), there is a WHEN clause : "The WHEN clause tells which records in the input data set are to be loaded. If there is no WHEN clause (and if FORMAT UNLOAD was not used in the LOAD statement), all records in the input data set are loaded into the specified tables or partitions. (Data beyond the range of the partition specified is not loaded.) The option following WHEN describes a condition; input records that satisfy the condition are loaded. Input records [...] 13752 53 27_Re: Modify Recovery Options16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 12 Apr 2001 14:30:22 +0530560_- James,

Are you mentioning something like this. Suppose SYSCOPY have entry of 5 image copies for a particular tablespace. As per you, In Modify Recovery, are we specifying something like this. DELETE COPIES(4). and it will keep the latest copy and delete all the 4 previous one. If at all this is the case, then specifying copies as 5 will again put the table in copy pending state. Now, we are looking at the dates and ages in the SYSCOPY for specifying it out here. As per this case, we have to look at the count(*) for a particular tablespace. [...] 13806 61 24_Re: ACCESS PATH QUESTION16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 12 Apr 2001 14:35:32 +0530427_- Jeff,

You mentioned 6 of the first 10 columns of the index. I am taking that as first 6 columns of the index. I personally think this should not happen. Can you please look into the order of the predicate and make it same as it appears in Index. I find no other reasons for that. Sometimes order matters but not sure if it is in this case. Probably Terry will have a better answer for the SQL performance issues. [...] 13868 179 29_Re: numlkus max reached ? why16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 12 Apr 2001 14:55:30 +0530317_iso-8859-1 What is the LOCKSIZE definition of tablespace ?. It looks to me the LOCKSIZE is PAGE and LOCKMAX is zero. Make LOCKSIZE to ANY and LOCKMAX to SYSTEM so that the lock escalation can take place. Unless until we are very sure about the behaviour of our applications in the tables, we should use default. [...] 14048 54 18_Re: regarding load16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 12 Apr 2001 14:58:14 +0530319_- Not sure if multiple WHEN clause can be specified. It has got many restrictions. I believe if it is a one time job. Edit the dataset, go to line number 20000 and issue delete records after that. You can write one small rexx to automate that and the load JCL should run that rexx first and then the LOAD utility. [...] 14103 67 27_Re: Modify Recovery Options0_18_mebert@AMADEUS.NET31_Thu, 12 Apr 2001 11:38:07 +0200598_us-ascii Sanjeev,

I think James wants to specify how many Image copies to RETAIN in SYSCOPY. However, there are difficulties involved. The major one is the subtle interaction between Full TS ICs and partition level ICs (you cannot delete a partition level entry, if an older full TS entry exists). If you use a program to generate the MODIFY statements, you can do this today, by analysing the contents of SYSCOPY - an interesting non-trivial task. I myself use a SAS program to generate MODIFYs (with a more complex strategy, to handle our mixture of very often copied vs. very seldom [...] 14171 103 27_Re: Modify Recovery Options19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 12 Apr 2001 07:16:15 -0500624_iso-8859-1 Hi Doc, et. al., I vote with something like this: MODIFY RECOVERY TABLESPACE dbname.tsname DELETE RETAIN(3). Meaning delete all but the latest three copies and let DB2 figure out the partition, incremental, and concurrent copy issues.

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 [...] 14275 41 41_Re: Looking for DB2 user base information19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 12 Apr 2001 07:18:37 -0500606_iso-8859-1 Hi Madison, I don't know the total DB2 licenses for DB2 in all its flavors but IBM did just announce its 10,000th OS390/zOS license.

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, [...] 14317 62 22_Re: AW: DB2 and COBOL?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 12 Apr 2001 07:26:36 -0500471_- When I said nifty I was being facetious. I was thinking of the Family Truckster in National Lampoon's Vacation movie.

Linda

-----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: Wednesday, April 11, 2001 8:03 PM To: DB2-L@RYCI.COM Subject: Re: AW: DB2 and COBOL?



I don't think 'nifty' is the adjective of choice regarding 'wood'-grain side panels. More like naff, cheezy, gaudy, tacky, etc... [...] 14380 127 27_Re: Modify Recovery Options14_Scott Trometer22_scott.trometer@RCI.COM31_Thu, 12 Apr 2001 07:32:23 -0500496_iso-8859-1 What about entries for quiesce, reorg, load, etc.?

-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:rd8246@SBC.COM] Sent: Thursday, April 12, 2001 7:16 AM To: DB2-L@RYCI.COM Subject: Re: Modify Recovery Options



Hi Doc, et. al., I vote with something like this: MODIFY RECOVERY TABLESPACE dbname.tsname DELETE RETAIN(3). Meaning delete all but the latest three copies and let DB2 figure out the partition, incremental, and concurrent copy issues. [...] 14508 32 15_database design12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Thu, 12 Apr 2001 07:36:19 -0500595_iso-8859-1 I have been reviewing some database designs for both packaged and custom applications and they seem to me to be pretty horrible unless there has been some kind of revolution that passed me by....

Is someone recommending that every table in a database have a unique unmeaningful number key called numid? Is there a movement to take out all referential integrity from the database and have the application control it and all other business rules - instead of putting these rules in the database? Some of these tables are just crazy - for example - in the department table - [...] 14541 143 27_Re: Modify Recovery Options16_Robert H. Tilkes22_tilkesr@NATIONWIDE.COM31_Thu, 12 Apr 2001 08:47:55 -0400463_us-ascii I second that vote. I would be really helpful when attempting to keep the MVS Catalog and Syscopy insync dealing with GDG base datasets as IC.

Bob Tilkes









"DAVIS, RICK (SBCSI)" To: DB2-L@RYCI.COM cc: Sent by: DB2 Data bcc: Base Discussion Subject: Re: Modify Recovery List Options





04/12/01 07:16 AM Please respond to DB2 Data Base Discussion List [...] 14685 196 29_Re: numlkus max reached ? why13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 12 Apr 2001 07:46:50 -0500596_iso-8859-1 From my standpoint, I always suggest using locksize PAGE and LOCKMAX 0 unless there is a proven need to have escalation (and then only use it as a short-tern patch until the application can be "fixed"). From an availability standpoint I really don't like to see escalation. Yes, your locking becomes more efficient, but at a cost of complete unavailability for other applications accessing that data (nice thing about this list, you get lots of opinions). On the lock-escalation question, I would look at a couple things -- look at the foreign keys on the table as locks may need [...] 14882 65 19_Re: database design15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 12 Apr 2001 08:06:35 -0500593_iso-8859-1 Hi, Louise, I haven't had any direct experience in this area but that hasn't stopped me from expressing an opinion before. You could be right (and probably are). There may not have been any thought about database design in the packages that you are looking at. I have heard of packages designed for a non-relational database system (or even a different type of RDBMS) being picked up and unceremoniously plopped down on a RDBMS without no redesign whatsoever. Then people wonder why it runs so poorly. See recent discussion on DB2 and COBOL on this listserv and quote below by [...] 14948 66 19_Re: database design16_Eachus, Walter N15_walt@LEHMAN.COM31_Thu, 12 Apr 2001 09:01:07 -0400384_us-ascii Louise

The problem is no one wants to do data design to really understand the data. Once the data is understood then meaningful keys can be developed along with business rules supported by referential integrity.

In packaged products I have found they do this because it is easy for them to maintain for many platforms. Still no excuse from my perspective. [...] 15015 103 41_dynamic query performance problem with V611_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 12 Apr 2001 08:27:16 -0500463_iso-8859-1 We upgraded this past weekend to V6.1 (on OS/390). The only problem we're having is with a job that uses DSNTIAUL to unload data. The select is:

SELECT CHAR(CUSTOMER_ID) , LAST_USED_DT , E_MAIL_ADDR_TX , DIGITS(WEB_BROWS_CMPTL_CD) , DIGITS(E_MAIL_ADR_ORIG_CD) , 'P' FROM DDFSP01.VCEMA1 A , DDFSP01.VCNCL1 B WHERE A.CLIENT_ID = 1 AND B.CLIENT_ID = 1 AND A.CONSUMER_ID = B.CONSUMER_ID AND B.CLUB_ID ¬= 10 AND E_MAIL_ADR_ORIG_CD ¬= 2 WITH UR; [...] 15119 61 42_Re: Submitting a JCL from Stored procedure5_Chris17_isatcjk@YAHOO.COM31_Thu, 12 Apr 2001 06:37:39 -0700359_us-ascii Hi, A nice person from this list several years ago sent this... the key part is the SITE command. I never tried to get the spool output back, I had just sent whatever output I was looking for to a file, then ftp'd the file down after waiting a period of time for the job to complete. Kludgy but it was just to make one of my admin tasks easier. [...] 15181 20 19_Re: database design0_26_truman.g.brown@VERIZON.COM31_Thu, 12 Apr 2001 09:38:33 -0400350_us-ascii Louise,

A lot of third party software packages were designed/developed by people inexperienced with DB2 working with DB2 versions 2 and 3. Horrible doesn't begin to describe them, they all have too many tables, indexes, and data redundancy. I won't name nmaes, but one that starts with P from a company named CA comes to mind... [...] 15202 164 18_Re: regarding load15_Robert lawrence21_rlawrence@BOSCOVS.COM31_Thu, 12 Apr 2001 09:41:11 -0400615_iso-8859-1 Hi , If you do not have FETCH for first N only available I would use either IDCAMS REPRO Ifile(infile) ofile(outfile) count(N) or SORT COPY option or ICETOOL with appropriate parameters

HTH Bob Lawrence DBA Boscov's Dept Stores LLc





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > ravi kumar hassan > Sent: Wednesday, April 11, 2001 5:02 PM > To: DB2-L@RYCI.COM > Subject: Re: regarding load > > > HI sflindsey, > > Thank you for your suggession, as per you i am using the sql to unload > the data and keeping in [...] 15367 23 23_EDM Pool Best Fit Zparm10_Fred Edgar22_fred_edgar@HOTMAIL.COM31_Thu, 12 Apr 2001 08:31:55 -0500374_- Hi,

Is anyone using this Zparm (EDMBFIT)? We are having EDM pool failures and are at our limit on memory. I've read that latch-wait time will increase, but I don't know if that's a big deal or not, or if this parm change will help at all. Our EDM pool is about 10K pages. We're on V6 of DB2 on OS/390. I would appreciate any information, experiences or ideas. [...] 15391 90 42_Re: Submitting a JCL from Stored procedure11_David Nance16_DWNance@FHSC.COM31_Thu, 12 Apr 2001 09:42:15 -0400429_US-ASCII You can use CA7, to start a job with the successful ftp of a dataset.

Dave Nance First Health Services, Corp. (804)527-6841

>>> K.Balaji@TARGET.COM 04/11/01 08:47PM >>> Hi kals, Your first suggesting looks interesting. I am with you until we ftp it to mainframe. But how can we trigger the job. Can you pl elaborate on this. Conceptually I am not clear about how can we submit a job from ftp. Regards [...] 15482 162 27_Re: Modify Recovery Options15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 12 Apr 2001 08:46:19 -0500526_iso-8859-1 For BMC COPY PLUS users, C+/Modify supplies the MAXCOPIES and MAXFULLCOPIES keywords to determine how many image copies to retain in SYSCOPY.

Michael Murley BMC

-----Original Message----- From: Robert H. Tilkes [mailto:tilkesr@NATIONWIDE.COM] Sent: Thursday, April 12, 2001 7:48 AM To: DB2-L@RYCI.COM Subject: Re: Modify Recovery Options



I second that vote. I would be really helpful when attempting to keep the MVS Catalog and Syscopy insync dealing with GDG base datasets as IC. [...] 15645 171 18_Re: regarding load16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Thu, 12 Apr 2001 06:49:32 -0700331_iso-8859-1 People are answering different questions because we're lacking information.

Do you need to load 20k specific rows or any of 20% of the 100k rows? Can you change the unload, or are you dealing with an existing unload file where all of the rows are required for something else? Does it have to be exactly 20k? [...] 15817 79 15_Controlling DDL15_Hissam Tressa K23_Tressa.K.Hissam@IRS.GOV31_Thu, 12 Apr 2001 09:40:34 -0400601_iso-8859-1



I am interested to know if anybody uses a third-party tool to control their DDL and if so, what the tool is. We had a request from a project to transmit all our DDL through Endevor (which we use for most everything else) to accomplish this. After much discussion, it was decided that, because of Endevor's constraints among other reasons, using it in the traditional way was not feasible for all DDL. However, we do need to incorporate some form of control over this code for security reasons. Can anyone help? Currently, developers submit their DDL to us, we normally [...] 15897 102 24_Re: ACCESS PATH QUESTION16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Thu, 12 Apr 2001 06:34:58 -0700591_us-ascii Sanjeev,

Thanks for your input. Yes, the first six columns of the 10 column clustering index for the partitioned tablespace lie within the predicate with equal (=) conditions. One thing which I found out after I had sent the memo, was that the update statement contained columns 7 through 10 of the index. Tim Lowe suggested that I might execute runstats with the KEYCARD option which I tried, but didn't expect differences. I ran runstats with the KEYCARD option and NUMCOLS 6, but it still showed list prefetch. I then reran the stats with NUMCOLS 10 and got the same [...] 16000 41 19_Re: Controlling DDL18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 12 Apr 2001 10:16:00 -0400727_iso-8859-1 We use IS*Integrity from Chain Link Technologies Inc.

Their website is www.clti.com



-----Original Message----- From: Hissam Tressa K [mailto:Tressa.K.Hissam@IRS.GOV] Sent: Thursday, April 12, 2001 9:41 AM To: DB2-L@RYCI.COM Subject: Controlling DDL









I am interested to know if anybody uses a third-party tool to control their DDL and if so, what the tool is. We had a request from a project to transmit all our DDL through Endevor (which we use for most everything else) to accomplish this. After much discussion, it was decided that, because of Endevor's constraints among other reasons, using it in the traditional way was not [...] 16042 67 19_Re: database design12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 12 Apr 2001 07:29:25 -0700644_us-ascii Some of this nonsense (IMHO) is coming from the Object world. There are many "pure" object designers recommending to use nothing but generated non-meaningful keys for all tables. See Scott Ambler's paper Mapping Objects to Relational Databases at www.ambysoft.com/mappingObjects.pds as an example of this.

I don't happen to agree with this as it causes all kinds of problems when trying to use the tables in a meaningful manner. Many extra indexes must be created just to have somewhat decent performance on SELECTs with the obvious degradation on INSERTS and UPDATES because of the extra indexes. I'm working with one web [...] 16110 134 45_Re: dynamic query performance problem with V60_19_mike.holmans@BT.COM31_Thu, 12 Apr 2001 14:37:38 +0100661_ISO-8859-1 If those indexes had CLIENT_ID as their first columns rather than CONSUMER_ID, then you'd have something to match on.

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

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

> -----Original Message----- > From: Tina Hilton [SMTP:Tina.Hilton@BMSUS.COM] > Sent: Thursday, April 12, 2001 2:27 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] dynamic query performance problem with V6 > > We upgraded this past weekend to V6.1 (on OS/390). The only problem we're > having is with a job that uses DSNTIAUL to unload data. The [...] 16245 157 27_Re: Modify Recovery Options19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 12 Apr 2001 10:00:08 -0500598_iso-8859-1 Hi Scott, Maybe I don't understand your response, but it would seem that one wouldn't want control over reorg and load because they impact copy validity. I think I get it for QUIESCEs.

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 [...] 16403 86 19_Re: database design16_Eachus, Walter N15_walt@LEHMAN.COM31_Thu, 12 Apr 2001 11:19:10 -0400669_us-ascii It also comes from the distributed world. Sybase used to recommend that all PrimaryKeys should be non-meaningful integers.

> -----Original Message----- > From: Myron Miller [SMTP:myronwmiller@YAHOO.COM] > Sent: Thursday, April 12, 2001 10:29 AM > To: DB2-L@RYCI.COM > Subject: Re: database design > > Some of this nonsense (IMHO) is coming from the Object world. There > are many "pure" object designers recommending to use nothing but > generated non-meaningful keys for all tables. See Scott Ambler's paper > Mapping Objects to Relational Databases at > www.ambysoft.com/mappingObjects.pds as an example of this. > > I don't happen to agree with [...] 16490 77 27_Re: Modify Recovery Options11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Thu, 12 Apr 2001 08:28:03 -0700351_iso-8859-1 James, better yet, why not a 'KEEP (n) COPIES' which would keep the most recent n copies. That way you would always have at least one copy and the syntax would make it very clear what you're doing........ oooops, I forgot, couldn't have that the mid-range people might be able to figure out what we're doing....... (just teasing!) [...] 16568 86 12_Re: SQL4300N19_CHRISTOPHER STARNES14_b05cxs@FDS.COM31_Thu, 12 Apr 2001 11:17:52 -0400618_us-ascii Raj, getting the environment configured properly for Java SPs was tough. What little doc there is is extremely fragmented. Here's what I did for Red Hat Linux 6.2 with DB2 UDB V7.1 EE:

- From the Appliction Building Guide:

On Linux, the Java Virtual Machine implementation does not work well in programs that run in a "setuid" environment. The shared library that contains the Java interpreter, libjava.so, may fail to load. As a workaround, you can create a symbolic link to the JVM shared library in /usr/lib, with a command similar to the following (depending on where Java is installed [...] 16655 75 27_Re: EDM Pool Best Fit Zparm9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 12 Apr 2001 16:26:40 +0100540_us-ascii Fred, At DB2 V5 - we have set it to YES since we did suffer from occasional EDM Pool failure problems and had EDM Pools > 40Mb. We have reduced EDM Pool failures... but we've also increased EDM Pool sizes and reduced DBD sizes so its difficult to determine whether it was due to EDMBFIT or not. PQ21171 goes on about using CLASS(24) to measure latch suspension times. As far as I can find out, this is an IBM Reserved Class and so not much use to us. I presume the EDM Pool latch suspensions are counted differently to 'data' [...] 16731 71 19_Re: database design19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 12 Apr 2001 10:52:27 -0500565_iso-8859-1 Louise, Boy, tell me about it! And I agree with you and most of the responses so far. Another point to ponder: Some, maybe most, OO designers include a surrogate key for all entities/use cases! When you ask them why they say "that's the way OO does things"! I'm not dumping on surrogate keys; where they apply they are great. However, with good, 3rd normal form DB design, very few surrogate keys are required. Search the archives on this subject and you'll see that we, the DBA community, need to "push back" on some of these issues. I believe the [...] 16803 158 24_Re: ACCESS PATH QUESTION34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM31_Thu, 12 Apr 2001 11:56:50 -0400604_iso-8859-1 Jeff

I read your email late. This is very common scenario and rules are fixed for updating indexed column.

Rule: If you update indexed column then each updated key column must have a corresponding predicate with "=" clause or IS NULL.

In case this rule is not followed then matching index access is not possible. In such scenario list prefetch is allowed as a substitute to matching index access (provided list prefetch is efficient access path compared to other possible means). Cluster ratio does not help (to get matching index scan) when updating indexed column. [...] 16962 33 38_Resolution of Stored Procedure problem11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Thu, 12 Apr 2001 16:47:03 +0100571_- All,

We have now solved our EXECIO error and I thought I would share the workaround with the community in order that it may save other people the hassle we have had.

The Stored Procedure Builder allows you to enter unlimited (I gave up at column 2000) text on each line, unfortunately what is not documented anywhere is that if you go beyond col 72 the DSNTPSMP rexx exec does a wobbly and generates you an EXECIO error. With hindsight and rembering those wonderful days of green screen this seems obvious, but when you've been in the client world a [...] 16996 27 21_Re: 24x7 Requirements12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 12 Apr 2001 11:04:06 -0500372_- You forgot some of the big problems that I seem to see every week, where the resources needed exceed those provided or the application is not designed to handle concurrent access, so the information is not usable during the batch update cycle. Applications that mandated to be 24 x 7, but are not designed for continuous availability seems like a continuing story. [...] 17024 12 15_DSNDB07 sizing?11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 12 Apr 2001 11:34:00 -0500315_us-ascii Does anyone know the "best" way to size DSNDB07 datasets?... : )

================================================ 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. 17037 94 27_Re: EDM Pool Best Fit Zparm23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 12 Apr 2001 17:17:03 +0100439_iso-8859-1 Hi Fred

I can only add to what Andy has said.

We run with NO and an EDM pool of 60Mb. We also have a 120Mb Dataspace backing the EDM pool for the Global Stmt Cache.

We are V6 and Os390 2.8.

From what I know you are able to monitor the Latch contetion (24) via DB2 PM stats (long) reports, however, I have as yet have not got around to doing so .. i.e switching it on and monitoring the reports. [...] 17132 20 17_Re: DB2 Wait time12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 12 Apr 2001 11:28:49 -0500422_- When it comes to working out wait time, the DB2 accounting data is generally one of the best sources. If you are running V6 or later, the class 3 wait times are separated out even more. For an example, look at chapter 25 in the DB2 Administration Guide for V6, the accounting report -- long. If it's a single cursor, then it may be time for the SQL trace if the accounting and explain data do not show the problem. [...] 17153 19 27_Re: EDM Pool Best Fit Zparm12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 12 Apr 2001 11:38:22 -0500370_- I know about a couple of customers who needed that, but generally did other changes first. Have you thought through other choices? The DB2 UDB for OS/390 Storage Management article in the Spring 2000 IDUG Solutions Journal by John Campbell and Mary Petras has lots of options.

http://www.idug.org then Solutions Journal then Spring 2000, then this article [...] 17173 46 17_Re: DB2 Wait time14_Scott Trometer22_scott.trometer@RCI.COM31_Thu, 12 Apr 2001 11:54:05 -0500609_iso-8859-1 Thanks to all for the info...I'll let you know how it turns out.

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Thursday, April 12, 2001 11:29 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Wait time



When it comes to working out wait time, the DB2 accounting data is generally one of the best sources. If you are running V6 or later, the class 3 wait times are separated out even more. For an example, look at chapter 25 in the DB2 Administration Guide for V6, the accounting report -- long. If it's a single cursor, then it may be time for the [...] 17220 70 37_Transient Data Table design questions12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM31_Thu, 12 Apr 2001 12:44:36 -0400505_us-ascii Ladies and Gentlemen, I am designing a transient data table, and, certain that I am not the first to face this challenge, am soliciting the list for common experience and advice.

The design components are as follows :

Base Table (BASE) - very large Raw data for reports.

Report Staging Table (STAGE) - approx. 10% of BASE Materialised reports subsetting BASE data according to varying predicates, ordering, etc. Each report typically 100 < n < 10,000 (n = no. of rows). [...] 17291 152 45_Re: dynamic query performance problem with V611_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 12 Apr 2001 13:14:56 -0500477_iso-8859-1 In this case I don't think it would help much. I found out that the only value for this column is '1' so it doesn't eliminate any rows.

-----Original Message----- From: mike.holmans@BT.COM [mailto:mike.holmans@BT.COM] Sent: April 12, 2001 8:38 AM To: DB2-L@RYCI.COM Subject: Re: dynamic query performance problem with V6



If those indexes had CLIENT_ID as their first columns rather than CONSUMER_ID, then you'd have something to match on. [...] 17444 114 19_Re: database design15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 12 Apr 2001 14:53:26 -0400367_us-ascii YES! I agree whole heartedly. Unfortunately, it seems that most of the time when I try to "push back" I am out numbered. So I end up winning some battles, but losing the war. Another argument that I've had the pleasure of dealing with is "well, if the business key value changes, we don't want to have to change it in multiple places". Give me a break! [...] 17559 31 24_SYSPACKSTMT STMT Parser?11_Bill Happel22_WRHappel@US.FORTIS.COM31_Thu, 12 Apr 2001 14:50:17 -0500350_US-ASCII Does anyone have a utility which merges, then parses the STMT column in SYSPACKSTMT into a readable format?

I know DB2PM has the capability, but we do not have DB2PM. I know the batch package of Omegamon has the capability, but we will not pay for it. Matter of fact, we have very few tools and none of them has this capability. [...] 17591 133 27_Re: Modify Recovery Options9_Wu, James13_jwu@KRAFT.COM31_Thu, 12 Apr 2001 14:52:03 -0500611_iso-8859-1 What I want is the ability to RETAIN a number of image copies in SYSCOPY. The main purpose is to avoid the deletion of the LAST image copy by Modify Recovery accidentally. I like Rick's idea of 'Modify Recovery Delete ... Retain(n)'.

This is useful not only for rarely updated tablespaces, but also for the tablespaces partitioned in the way that most updates are taking place only in certain partitions (e.g. partitioned by calendar dates). In this case, you just want to take image copies on the currently updated partition(s) and keep some image copies for other partitions in SYSCOPY [...] 17725 211 29_Re: numlkus max reached ? why9_Birk, Tim17_BirkT@DIEBOLD.COM31_Thu, 12 Apr 2001 15:58:21 -0400371_iso-8859-1 Assuming you have two packages, one for SETA and one for SETB, have you checked the isolation level? Is it possible that SETA is CS and SETB is RR?

Regards, Tim

-----Original Message----- From: Vaughan, Mike [mailto:Vaughan.Mike@PRINCIPAL.COM] Sent: Thursday, April 12, 2001 8:47 AM To: DB2-L@RYCI.COM Subject: Re: numlkus max reached ? why [...] 17937 62 28_Re: SYSPACKSTMT STMT Parser?14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Thu, 12 Apr 2001 13:56:31 -0700463_iso-8859-1 I have a rexx that parses the dbrm and one that parses the peoplesoft sql table. Either could be adapted for SYSPACKSTMT.

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



-----Original Message----- From: Bill Happel [mailto:WRHappel@US.FORTIS.COM] Sent: Thursday, April 12, 2001 12:50 PM To: DB2-L@RYCI.COM Subject: SYSPACKSTMT STMT Parser? [...] 18000 18 14_Appending data10_Bob Riehle19_bob.riehle@WAMU.NET31_Thu, 12 Apr 2001 14:12:52 -0700362_us-ascii Is there a way to append data to tables without having to have exclusive locks? DB2 OS/390 V6 Thanks Bob Riehle

================================================ 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. 18019 36 28_Re: SYSPACKSTMT STMT Parser?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 12 Apr 2001 17:22:56 -0400415_iso-8859-1 Hi Bill, I expect that all DB2 Admin tools have this capability.

Of course I'd recommend ours-- DBA-XPERT; File-AID for DB2 also has this feature.

I'll be pleasantly surprised if you find someone with a homegrown version they'll share with you. It is a fair piece of work to write one.

Back in my DBA mercenary days, I spent several weeks writing one in Rexx, in my spare time. [...] 18056 53 27_Re: database design, etc...20_Forestall, Philip T.19_forestall@SPRINT.CA31_Thu, 12 Apr 2001 22:46:41 -0400513_us-ascii Louise,

Your experience reminds me of my own.

I implemented DB2 databases for packaged software from four of the big integrators through the 90's. In all of them, the core code was at least 25 years old. In all of them, the developers' DB2 experts were recently self-taught and their use of DB2, never mind the relational model, was just plain primitive. In all of them, DB2 had been added only for marketing credibility. Pretty obvious really. One of them even privately admitted it. [...] 18110 61 20_Massive Lock Timeout17_Fernando Manching17_Kiwibisoy@AOL.COM29_Thu, 12 Apr 2001 23:41:10 EDT578_US-ASCII Hi Everyone,

We are running UDB for AIX on a RISC box. Lately, we have been experiencing lock timeouts on one of our tables. The table is quite small, only about 16,000 rows with a length of 50 bytes. The table is on a DMS managed tablespace . Here our the parameters that I have tried to tweak but never resolved the problem. locklist = 400, maxlock=33, locktimeout=15 The only change when we experienced this problem is the introduction of 35 more users to the application. What is intriguing is that even without anybody running the application, if I do [...] 18172 32 18_Re: Appending data16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 13 Apr 2001 10:45:23 +0530635_- I don't think so. Let's see if i can also learn the new techniques.

Regards Sanjeev

> -----Original Message----- > From: Bob Riehle [SMTP:bob.riehle@WAMU.NET] > Sent: Friday, April 13, 2001 2:43 AM > To: DB2-L@RYCI.COM > Subject: Appending data > > Is there a way to append data to tables without having to have exclusive > locks? > DB2 OS/390 V6 > Thanks > Bob Riehle > > ================================================ > 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. [...] 18205 29 19_Re: DSNDB07 sizing?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 13 Apr 2001 10:52:55 +0530641_- As you have put the "BEST" inside the quotes(" ") so i presume you know what is mentioned in the Admin Guide. That works as the best way for me.

Regards Sanjeev

> -----Original Message----- > From: Jeff Faughn [SMTP:Jeff_Faughn@MAY-CO.COM] > Sent: Thursday, April 12, 2001 10:04 PM > To: DB2-L@RYCI.COM > Subject: DSNDB07 sizing? > > Does anyone know the "best" way to size DSNDB07 datasets?... : ) > > ================================================ > 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 [...] 18235 149 45_Re: dynamic query performance problem with V616_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 13 Apr 2001 11:28:44 +0530556_iso-8859-1 Tina,

In continuation to Mike's mail, i would like to have some more information. I am not sure if your SELECT list contain any rows from Table VCEMA1(A) or not. If it is not, you can scan the index(create, as suggested by Mike and using host variables instead of literals, if required) only and then send it to join. This way your poor clustering of the index will not be a problem. I think the comparision of the cost of list prefetch and tablespace scan is done and tablespace scan is choosen. Also check if this is the base view [...] 18385 36 48_Re: Stored Procedures - WLM Refresh/APF Problems0_27_Tom.Decabooter@HONDA-EU.COM31_Fri, 13 Apr 2001 13:56:47 +0200478_us-ascii Hi,

at our shop we created two WLM application environments to circumvent the APF problem. Each WLM application environment calls a different started task. One task only contains APF libraries in the steplib. This task is used for all stored procedures that must run APF authorized (like DSNUTILS).

The other task also contains non-APF libraries in the steplib. This task is used to run all application programs that don't require APF authorization. [...] 18422 128 19_Re: database design19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 13 Apr 2001 08:03:56 -0500566_iso-8859-1 Hi Jay, I'm a proponent of OO design but realize it, like most methodologies, isn't perfect. This whole business with OO design reminds me of lemmings running like the dickens off a cliff! With all the tremendous value OO design offers in most of its methods its a shame that a couple/few bad concepts/methods might "sink" it. The irony of all this lies in the fact that nearly all of its proponents do not have the experience to compare and contrast it against anything else and therefore don't have what it takes to fix or delete its few flaws. An [...] 18551 46 18_Re: Appending data19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 13 Apr 2001 08:09:48 -0500629_iso-8859-1 Hi Bob, When you discover how to do this: patent the process, sell it to IBM, become a billionaire, retire!

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, [...] 18598 122 24_Re: Massive Lock Timeout14_Scott Saunders20_ssaunders@SIEBEL.COM31_Fri, 13 Apr 2001 06:31:47 -0700570_iso-8859-1 Fernando;

Have lock escalations taken place? It sounds like this could be the case, possibly an exclusive lock escalation. Your lock list seems quite small. Depending upon the amount of memory available to you, you may wish to increase the size of this memory area. Take a look at the Admin Guides. When any user uses more than, in your case, 33% of the 400 page lock list, an escalation will be attempted. Also it seems to me that when all locks exceed the size of the memory in lock list escalations may occur. You should check your Db2 DIAGLOG [...] 18721 56 18_Re: Appending data0_19_Tim.Lowe@STPAUL.COM31_Fri, 13 Apr 2001 08:43:26 -0500406_us-ascii Bob, Can you say why you want to do this?

And, will the new DB2 V7 LOAD RESUME concurrency capability satisfy your needs?

Thanks, Tmi





Bob Riehle cc: Sent by: DB2 Subject: Appending data Data Base Discussion List



04/12/2001 04:12 PM Please respond to DB2 Data Base Discussion List [...] 18778 66 18_Re: Appending data19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 13 Apr 2001 08:58:57 -0500606_iso-8859-1 Bob, Tim, As I understand this capability its simply an INSERT as if an application program were doing it. Its value is that hopefully, the space map page locks, etc. won't interfere with other activity.

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 [...] 18845 67 24_Re: Massive Lock Timeout11_RICHARD YAN23_DHSPA3F@DHS.STATE.IL.US31_Fri, 13 Apr 2001 09:06:46 -0500618_US-ASCII Fernando,

You may also check who holds the lock on the table when you "select count(*) ". The command you can use is "get snapshot for locks on dbname".

Regards, Richard Yan IL dept of Human Services.

>>> ssaunders@SIEBEL.COM 04/13/01 08:31AM >>> Fernando;

Have lock escalations taken place? It sounds like this could be the case, possibly an exclusive lock escalation. Your lock list seems quite small. Depending upon the amount of memory available to you, you may wish to increase the size of this memory area. Take a look at the Admin Guides. When any user uses more than, [...] 18913 33 24_Re: Massive Lock Timeout0_25_manas.dasgupta@PFSFHQ.COM31_Fri, 13 Apr 2001 10:27:51 -0400608_us-ascii Fernando:

What does "get snapshot for locks on dbname" show? The table should appear as being locked. You should also be able to identify the Application Handle/ID holding the lock from the report.

Manas.



"Fernando Manching" wrote in message news:<105.1c248c9.2807cf56@aol.com>... > Hi Everyone, > > We are running UDB for AIX on a RISC box. Lately, we have been experiencing > lock timeouts on one of our tables. The table is quite small, only about > 16,000 rows > with a length of 50 bytes. The table is on a DMS managed tablespace . Here > our > the parameters [...] 18947 22 13_Select N rows12_Sams, Debbie13_dsams@UFL.EDU31_Fri, 13 Apr 2001 10:31:28 -0400285_iso-8859-1 I'm trying to code an SQL statement to return only the first 10 rows on DB2 OS/390, V5.1. I know this was discussed before but couldn't find it when I searched the list archives, nor in RYC Knowledge Database nor in the DB2 manuals. Can someone post an example. Thanks [...] 18970 109 18_Re: Appending data0_19_Tim.Lowe@STPAUL.COM31_Fri, 13 Apr 2001 09:32:41 -0500371_us-ascii Rick, My understanding is the same as yours, that this just uses inserts. However, I mentioned this because I was not sure why he wanted to "append" data without an "exclusive" lock. (I was not sure why the term "append" was used) And, the new DB2 V7 Load Resume sharelevel=change capability will certainly help me, and it sounded like this might help him. [...] 19080 178 19_Re: database design15_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 13 Apr 2001 10:54:42 -0400568_us-ascii Hi Rick,

Once again, I couldn't agree more. You just can't convince some folks. Not that their intentions are bad, they're just misguided. And I believe it comes from a lack of understanding of what is truly going on underneath the covers in the database. They just look at it from a purists point of view and think how wonderfully OO'd it is. And typically these are OO developers that want to look at the data as generic reusable objects, just as they do their code. That's great for code, but not for tables. Tables are objects, but are rarely [...] 19259 24 29_EXISTS( SELECT 1 vs. SELECT *23_Daniel Van Nieuwenhuyze32_daniel.vannieuwenhuyze@SIDMAR.BE31_Fri, 13 Apr 2001 16:57:07 +0100306_us-ascii L.S. I just would like to know if there is any difference (CPU and/or IO cost) between:

SELECT A FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T1.A = T2.B) and SELECT A FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T1.A = T2.B)

(By the way, there is an index on T1.A and on T2.B) [...] 19284 138 18_Re: Appending data19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 13 Apr 2001 10:19:21 -0500626_iso-8859-1 Tim, I, like you, would like to hear more from Bob about "append".

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 [...] 19423 58 33_Re: EXISTS( SELECT 1 vs. SELECT *13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Fri, 13 Apr 2001 08:51:36 -0700621_iso-8859-1 Here's a sample test rig for these questions on os390. DSN_STATEMNT_TABLE is new for release 6.





DELETE FROM MY.PLAN_TABLE WHERE QUERYNO IN (1,2); DELETE FROM MY.DSN_STATEMNT_TABLE WHERE QUERYNO IN (1,2); COMMIT; EXPLAIN ALL SET QUERYNO = 1 FOR SELECT A FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T1.A = T2.B); EXPLAIN ALL SET QUERYNO = 2 FOR SELECT A FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T1.A = T2.B); COMMIT; SELECT * FROM MY.PLAN_TABLE WHERE QUERYNO IN (1,2) ORDER BY QUERYNO,QBLOCKNO,PLANNO; SELECT * FROM MY.DSN_STATEMNT_TABLE WHERE QUERYNO IN (1,2) ORDER BY QUERYNO; [...] 19482 29 12_Job in Paris18_Armando Marcotulli15_mg.am@LIBERO.IT31_Fri, 13 Apr 2001 17:53:23 +0200303_US-ASCII Hello list, Probably I will work in Paris for 18 months, my company asked to me if I'm interested in relocating to France. It seems to be a great opportunity. The flat for me and my family will be paid by my company. I would like to get some information about the cost of life and salary. [...] 19512 136 18_Re: Appending data10_Bob Riehle19_bob.riehle@WAMU.NET31_Fri, 13 Apr 2001 09:29:38 -0700566_us-ascii I have tables which are essentially read-only and will be adding (appending) 40,000 + rows nightly. They must be available 24/7/365 .Comprendo? "DAVIS, RICK (SBCSI)" wrote:

> Tim, > I, like you, would like to hear more from Bob about "append". > > 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 [...] 19649 52 17_Re: Select N rows17_Esmaiel Nokhodian15_dbaen@TEXAS.NET31_Fri, 13 Apr 2001 12:00:36 -0500453_iso-8859-1 Debbie,

Here is an example of how you can retrieve the first 10 rows:

SELECT COL1,COL2, COL3 FROM TABLE1 A WHERE 10 = (SELECT COUNT(*) FROM TABLE2 B WHERE A.COL1 = B.COL2) I didn't test this, but I think this is what you want.

Essy.

----- Original Message ----- From: Sams, Debbie Newsgroups: bit.listserv.db2-l To: Sent: Friday, April 13, 2001 9:31 AM Subject: Select N rows [...] 19702 19 31_Stored Procedure accessing VSAM14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 13 Apr 2001 11:01:42 -0600441_- Does someone have a sample SP code accessing VSAM that they would be willing to share? We are not running WLM. TIA.



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

================================================ 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. 19722 161 18_Re: Appending data11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 13 Apr 2001 10:03:10 -0700463_iso-8859-1 Application batch job doing nightly inserts, end user applications bound with UR......

For 40K+ rows, this should work fine, provided the end user applications can tolerate possible data changes........

Robert Jans Albertsons, Inc



-----Original Message----- From: Bob Riehle [mailto:bob.riehle@WAMU.NET] Sent: Friday, April 13, 2001 10:30 AM To: DB2-L@RYCI.COM Subject: Re: Appending data [...] 19884 175 18_Re: Appending data19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 13 Apr 2001 12:23:52 -0500395_iso-8859-1 I am simply trying to indicate that LOAD SHRLEVEL(CHANGE) appears have the same restrictions as any two application/transaction processes would have, e.g. concurrency, lock size and duration, timeout, deadlock, etc. In other words, the rows inserted by LOAD SHRLEVEL(CHANGE) appear to have to obey the same rules as any inserted row e.g. proper page, spacemap page updates, etc. [...] 20060 158 18_Re: Appending data14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US31_Fri, 13 Apr 2001 13:30:09 -0400599_US-ASCII Comprenden? o Comprendeis if we really liked you?

Pero no ... no lo entiendo!

>>> bob.riehle@WAMU.NET 04/13/01 12:29PM >>> I have tables which are essentially read-only and will be adding (appending) 40,000 + rows nightly. They must be available 24/7/365 .Comprendo? "DAVIS, RICK (SBCSI)" wrote:

> Tim, > I, like you, would like to hear more from Bob about "append". > > 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 [...] 20219 47 16_Re: Job in Paris14_Vincent Wright21_vmwusa_2000@YAHOO.COM31_Fri, 13 Apr 2001 13:18:10 -0400595_us-ascii Armando, This may help you: http://www.state.gov/www/perdiems/quarterly_reports/jan00_table1.html Best of luck with your decision. Vincent Wright

Armando Marcotulli wrote:

> Hello list, > Probably I will work in Paris for 18 months, my company asked to > me if I'm interested in relocating to France. > It seems to be a great opportunity. > The flat for me and my family will be paid by my company. > I would like to get some information about the cost of life and salary. > > My skill: > DB2 DBA 9+ years (OS/390) > Oracle DBA 4+ years (Solaris and OS/390) > Oracle [...] 20267 42 35_Re: Stored Procedure accessing VSAM13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Fri, 13 Apr 2001 12:49:49 -0500577_iso-8859-1 For a stored procedure in COBOL, write it like a batch job: include the SELECT statement in FILE-CONTROL (with the name of the DD card as specified in the SPAS = stored procedure address space), an FD in FILE SECTION, the PROCEDURE DIVISION has a USING clause for variables, an OPEN statement, do the STARTs and/or READs as necessary, possibly insert data into a DB2 table or return variables, and CLOSE the VSAM file. The OPEN and CLOSE add a LOT of overhead. If you've done other stored procedures in COBOL, you shouldn't have any trouble figuring out how to [...] 20310 66 17_Re: Select N rows19_K.Balaji (Exchange)19_K.Balaji@TARGET.COM31_Fri, 13 Apr 2001 15:28:34 -0500409_iso-8859-1 Is Debbie looking for top ten rows for given column or just first 10 rows. Either way this sql may return the results that we intend.

-----Original Message----- From: Esmaiel Nokhodian [mailto:dbaen@TEXAS.NET] Sent: Friday, April 13, 2001 10:01 AM To: DB2-L@RYCI.COM Subject: Re: Select N rows



Debbie,

Here is an example of how you can retrieve the first 10 rows: [...] 20377 52 33_Re: EXISTS( SELECT 1 vs. SELECT *14_Richard Yevich17_ryevich@YAHOO.COM31_Fri, 13 Apr 2001 13:43:48 -0700520_us-ascii On the current releases, no. Once upon a time, in the older versions of DB2, there was a small difference.

--- Daniel Van Nieuwenhuyze wrote: > L.S. > I just would like to know if there is any difference (CPU and/or IO > cost) > between: > > SELECT A FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T1.A = T2.B) > and > SELECT A FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T1.A = T2.B) > > (By the way, there is an index on T1.A and on T2.B) > > and also the reason [...] 20430 93 33_Re: EXISTS( SELECT 1 vs. SELECT *13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 13 Apr 2001 15:54:30 -0500406_iso-8859-1 DT,

You must have sent your explain output in an attachment.....and the list does not allow attachments so we did not receive it.

Daniel VN,

There is no discernible difference between SELECT 1 and SELECT * in a correlated existance subquery. For SELECT *, the optimizer does not return the entire row (as SELECT * usually indicates), but rather a true/false indicator. [...] 20524 111 17_Re: Select N rows13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 13 Apr 2001 16:05:30 -0500639_iso-8859-1 Debbie,

The last recollection I have of this discussion was a thread entitled "Retrieving the smallest 30 values" in late January this year.

Basically, there is no single "simple" SQL solution on pre-V7 OS/390 DB2 which provides suitable performance; apart from opening a cursor and fetching 10 rows.

As I have suggested many times on the list previously, the SQL solution provided below is the worst performing solution due to the correlated subquery being executed for every row of the table (ie. there is no stopping mechanism once you reach 10). The formula for the number of subquery rows counted [...] 20636 25 26_Cross-platform CICS to DB213_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 13 Apr 2001 14:17:21 -0700593_iso-8859-1 Dear fellow listers,

For reasons unfathomable by my non-management brain, TPTB would like to move DB2 off the mainframe, most likely to Solaris Unix. We are currently DB2 5.1, CICS 4.1, OS/390 2.7. I suggested that, in view of the enormous batch workload we run, perhaps they'd like to leave DB2 on OS/390 and move the applications to wherever makes the most sense. They're mulling that one over, but in the meantime I'd like to know: is anyone else running CICS on OS/390 and accessing DB2 UDB on another platform? Any ideas how I might quantify the performance hit we [...] 20662 23 32_Trapping -ve SQLcodes with Exits22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Fri, 13 Apr 2001 16:20:16 -0700546_iso-8859-1 Gurus, Has anybody used exits to trap -ve SQLCODE's to do some automatic alerts. My shop has Powerbuilder, CICS and Batch. It is unthinkable to modify code to put in new error handling routines .But I was wondering if I can trap these errors as they are happening in DB2 (and before control returns to the app) and using that exit to call a DB2 stored procedure that I can interface with say MS Outlook. Please let me know if anybody is doing something like this or if my thought is stupid.What I haven't told you is that My shop [...] 20686 24 55_Stored Procedures - Having to run WLM_REFRESH too often11_john turman20_turman10@HOTMAIL.COM31_Fri, 13 Apr 2001 19:11:10 -0500429_- Hello,

I am building MVS/OS390 PSM stored procedures from the workstation and forced to execute WLM_REFRESH prior to every other build of my stored procedures.

Does anyone have thoughts on how I can increase the time between having to run the WLM_REFRESH?

Thanks, John _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com [...] 20711 25 24_Re: Massive Lock Timeout17_Fernando Manching17_Kiwibisoy@AOL.COM29_Sat, 14 Apr 2001 00:38:18 EDT85_US-ASCII I have looked at the snapshot and no lock escalation has taken place at all. 20737 128 39_Re: DRDA connection, DB2 Server for VSE16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Sat, 14 Apr 2001 12:49:44 +0100468_us-ascii Islam,

sorry I have been rather busy over the past couple of weeks and was unable to attend to this.

For startes, you will find details of all the steps for setup at

http://www-4.ibm.com/software/data/db2/vse-vm/t3_710.htm

I am sure you know that the homepage for DB2 Server for VSE and VM can be found at

http://www-4.ibm.com/software/data/db2/vse-vm/

also you might find useful info in the following Redbooks: [...] 20866 241 39_Re: DRDA connection, DB2 Server for VSE24_Islam Abdelaleem Mohamed17_aleemi@EG.IBM.COM31_Sat, 14 Apr 2001 18:43:09 +0300431_iso-8859-1





Aurora, Many thanks for your advise. I had found the following useful resources by searching the RETAIN . ------------------------------------------------------------------- 1- IBM®DB2®Connect Enterprise Edition for OS/2®and Windows® Quick Beginnings Ve r s i o n 7 GC09-2953-00 * the matching parameter between VSE and WIN/2000

2- Connectivity Supplement Ve r s i o n 7 SDB2-CONN-SU [...]