1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2001, week 3 2 41 31_Re: Count is there without data23_Puddy, Andrew (CALBRIS)36_Andrew.PUDDY@COMALCO.RIOTINTO.COM.AU31_Thu, 15 Mar 2001 06:11:41 -0000370_iso-8859-1 are there any messages or error codes relating to the select * . the count (*) could be resolved without opening the vsam data cluster. What os is this running on ??

-----Original Message----- From: Mike Friedlander [mailto:friedlander@FIRSTINVESTORS.COM] Sent: Thursday, 15 March 2001 6:19 To: DB2-L@RYCI.COM Subject: Count is there without data [...] 44 54 34_help required - long running query18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM31_Thu, 15 Mar 2001 11:56:38 +0500505_- > Thank you very much James Campbell, Terry Purcell, Bruce, James Kwan and > Walter for your suggestions. > > LEAFDIST is 0 for this index. FAROFFPOSF is 90210 and NEAROFFPOS is > 7492800. Moving the user objects out of BP0, I will suggest this to the > DBAs but I am not sure if I will be able to convince them. (I am a only a > programmer and not a DBA or whatever, so how much weight would my opinion > carry is the question). This is a batch job which runs everyday and > unfortunately this is [...] 99 62 44_Re: Zparm CONTSTOR - contract thread storage23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 15 Mar 2001 06:56:44 -0000310_iso-8859-1 Mike

it has been a while since I visited this area and this is why I have sent out JCs presentation URL to all those that requested it - to ensure the correct information is received.

You are perfectly correct. My thoughts always come across in a mad way :-).

To clarify : [...] 162 135 49_Re: FW: DB2 for OS/390 stored procedures question37_=?iso-8859-1?Q?=C4rlebrandt_Michael?=28_Michael.Arlebrandt@VOLVO.COM31_Thu, 15 Mar 2001 08:08:23 +0100444_iso-8859-1 Hi!

Be avare of that there are still some of the IBM provided SP's that runs in NO WLM ENVIRONMENT and are linked with DSNALI instead of DSNRLI

DSNWZP which reads the ZPARM and DSNACCMD,DSNACCQC,DSNACCMG and DSNACCAV used by control center

regards

_________________________________ Michael Ärlebrandt DB2 Technical Specialist Volvo Information Technology Dept 2540, DA2N SE-405 08 Gothenburg, Sweden [...] 298 60 17_AW: SQL CODE -44412_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 15 Mar 2001 09:06:15 +0100790_iso-8859-1 Kypa,

it seems that your programm that is associated with the called stored procedure (or UDF) could not be found. Problem with library concatenation or somewhat like this ?

HTHT.

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 ---------------------------------------------------------------------- Disclaimer: All thoughts are mine and not of the company. [...] 359 80 38_Re: help required - long running query14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 15 Mar 2001 19:20:49 +1100331_iso-8859-1 My immediate response is 'yes, combine the expense tracking cursor and SURVEY-2 into a single cursor'. The major advantage is that DB2 will be to use a more efficient technique to get the totality of what you want. For example, DB2 might decide that it is more efficent to use a tablespace scan followed by a sort. [...] 440 133 72_help required - long running query - trace output from mainview - part I18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM31_Thu, 15 Mar 2001 13:38:56 +0500421_iso-8859-1 All, The trace output had to be split into two parts, because it exceeds the 250 lines limit of DB2-L. The first half of it is pasted below. To preserve the alignment use courier font of size 10 BOOLE AND BABBAGE---------- SUMMARY TRACE ENTRY ------------- RX AVAILABLE SERV ==> STRAC INPUT 03:42:08 INTVL=> 3 LOG=> N TGT==> DB36 PARM ==> NHP701P,SEQ=4 ROW 1 OF 169 SCROLL=> CSR EXPAND: MON(WKLD), DETAIL [...] 574 241 73_help required - long running query - trace output from mainview - part II18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM31_Thu, 15 Mar 2001 13:40:57 +0500568_iso-8859-1 > All, > The trace output had to be split into two parts, because it exceeds the > 250 lines limit of DB2-L. The second half of it is pasted below. To > preserve the alignment use courier font of size 10 > - - - - - - - - - - - - SQL STATEMENT EXECUTION COUNTS - - - - - - - - - - - - SELECT..........452,683 LOCK TABLE...............0 CREATE.............0 INSERT............1,443 GRANT/REVOKE.............0 DROP...............0 UPDATE............1,071 SET CURR.SQLID...........0 ALTER..............0 DELETE............1,442 SET HOST VAR. ...........0 [...] 816 51 35_Re: Can we switch off Db2 logging??0_18_mebert@AMADEUS.NET31_Thu, 15 Mar 2001 09:45:36 +0100490_us-ascii Keep it simple. If you just want to reduce the space consumed by the logs, and don't need logging anyway, use single (not dual) logging and reduce the log size and/or number of logs. Also, delete the archive logs as soon as they are created (maybe specifying a retention period of 1 day is enough in your case; probably you can set up SMS to delete them every hour). These parameters are controlled by ZPARMs. Changing size and number of logs is described in the Admin Guide. [...] 868 90 31_Re: Count is there without data0_18_mebert@AMADEUS.NET31_Thu, 15 Mar 2001 10:01:45 +0100473_iso-8859-1 Bill,

(DB2 V5, OS390) the reason for the IC/Recover is that it is much more efficient than a REORG (far less CPU, less elapsed time) which, after all, is for REORGing the data and not for resizing or extent consolidation (the latter is most efficiently done by STOP/ADRDSSU COPY/START). Note that RECOVER will only resize the TS, not the associated indexes, so I generally follow it by a REBUILD INDEX(ALL) TABLESPACE db.ts SORTDEVT SYSDA SORTNUM 3. [...] 959 113 38_Re: help required - long running query18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Thu, 15 Mar 2001 18:43:12 +0930624_us-ascii Howzit Nagaraj?

> This process is repeated for every value of WS-CTL-FILE-ID-X (there are > about 295,000 WS-CTL-FILE-ID-X s to be processed). As James Kwan pointed

Opening and closing the SURVEY-2 cursor 295,000 is unnecessary overhead and will contribute significantly to your elapsed time.

> cobol program and finally comes to SURVEY-2 cursor. Will it help if I > move the filtering logic into SURVEY-2 cursor, and combining expense > tracking cursor and SURVEY-2 cursor into one cursor. I know that it is > always good to move the filtering logic into SQL than do it in the > program. [...] 1073 27 21_Sqlcode805 with DProp13_Wolfgang Wolf17_wolferl69@GMX.NET31_Thu, 15 Mar 2001 03:24:18 -0600359_ISO-8859-1 Hi forumers, I just installed my DPROP V7 and when starting the capture, I receive:

ASN0001E The Capture program encountered an SQL error. The routine nam e is get_current_server() ; the SQL request is GET CURRENT SERVER; the table name is ASN.IBMSNAP_REGISTER; the SQLCODE is -805; the SQLERRML is 43; the SQLERRMC is DB2P..ASNLDM61.£ [...] 1101 234 43_Re: DB2 Estimator V7 (7.0.4) and PeopleSoft12_Troy Coleman19_Colematr@MEIJER.COM31_Thu, 15 Mar 2001 06:37:36 -0500411_US-ASCII Steve, That worked. I started in column 11 and received the tables ok. I guess I never noticed that before. In fact I tried to start in column 13 and the tool will only allow you to start in 1 - 11.

Thanks again,

Troy Coleman Coleman Consulting, Inc.

>>> Steve.Tennant@ATO.GOV.AU 03/14/01 09:44PM >>> **************************************************************** IMPORTANT [...] 1336 22 9_Question.15_Kannan, Perumal22_perumal.kannan@EDS.COM31_Thu, 15 Mar 2001 04:15:38 -0600441_- Hello List, Thanks for your time. Can I use ALTER to rename a column name in an existing table, I am with DB2 Version 5plus on S/390. If I can, what should I do to take care if the table is having data capture facility "ON"? how it will affect the modification? What should I do if I want to add a new column, again if the Data Capture Facility is already there for the table, how it will affect? Thanks in advance. Have a nice time! [...] 1359 20 31_Re: Count is there without data14_Frank Marchese16_fmarches@CSC.COM31_Thu, 15 Mar 2001 07:35:44 -0500469_us-ascii Could be that if you have an index on this table, it's ok and the Optimizer is using it for the COUNT query - that's why the count is returning the correct value but for some reason the base table is fubarb and is either empty and/or cannot be accessed. Try selecting a specific row....look on DB2MSTR JES log to see if any error msgs are showing up there. Try DSN1PRNT on the table to see what's there. Try a INSERT...SELECT to repopulate the new table. [...] 1380 65 13_Re: Question.0_19_mike.holmans@BT.COM31_Thu, 15 Mar 2001 12:46:00 -0000298_- No, you can't rename a column using ALTER. You can get the same effect by creating a view, but that's not a course to be recommended (hi Craig!).

The migration tool we use can automate the process of unloading, renaming, and reloading, and I presume that all such tools will do that. [...] 1446 70 61_rename a column name in an existing table in DB2 Version 5...12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 15 Mar 2001 13:48:27 +0100888_iso-8859-1 Kannan,

as far as I know you cannot rename a column name in an existing table in DB2 Version 5 on S/390.

Within Version 6 there is a very limited possibility: ALTER TABLE ALTER COLUMN columname SET DATATYPE varchar/character varying/char varying/(integer).

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 ---------------------------------------------------------------------- Disclaimer: All thoughts are mine and not of the company. "Just the world as I [...] 1517 184 38_Re: Dynamic Access path change - HELP!0_18_mebert@AMADEUS.NET31_Thu, 15 Mar 2001 13:57:01 +0100565_us-ascii Dear List,

the problem I had last week is back. I thought I had it solved by putting a TS into a different BP, but it appears this was just a random change. Today the batch is again spinning its wheels, getting nowhere with a lot of effort. Again, the index access path is not being used. Instead, there is a lot of activity in the BP1 bufferpool, where the big TS partition resides (table T1_AIRBKGT_DETAIL/TS PNB0J1.PNBS34W1). It looks like DB2 is accessing the small TS first, and doing a TS scan of the big partition for each matching row. [...] 1702 77 35_Re: Can we switch off Db2 logging??11_Moore, Tony15_TMoore@IKON.COM31_Thu, 15 Mar 2001 08:02:51 -0500437_iso-8859-1 Ross, You can switch off DB2 archive logging using an OFFLOAD=NO parm in the DSN6LOGP macro of ZPARM. DB2 will continue to use each of your defined active logs, but will not dump them to archive each time it rolls to the next.

WARNING: With logging turned off, if you have a long running thread that fails and tries to roll back past the oldest active log record you have available, your DB2 subsystem will crash. [...] 1780 58 13_Re: Question.18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Thu, 15 Mar 2001 22:34:29 +0930769_us-ascii Howzit Perumal?

No, you can't. ALTER ADD column.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800











"Kannan, Perumal" @RYCI.COM> on 15/03/2001 19:45:38

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Question.





Hello List, Thanks for your time. Can I use ALTER to rename a column name in an existing table, I am with DB2 Version 5plus on S/390. If I can, what should I do to take care if the table is having data capture facility "ON"? how it will affect the modification? [...] 1839 95 44_Re: Slooooooow Delete, DB2 V6.1, OS/390 2.1016_Michael McKinney16_mmckinne@CSC.COM31_Thu, 15 Mar 2001 08:32:22 -0500352_us-ascii I believe the Foreign Keys you described might be a big cause of this. Even though you stated that the child tables contain no 2002 records, I believe DB2 will check the child tables with each delete you do against the parent table.

Another thing that might be slowing you down is if you have multiple indexes on the parent table. [...] 1935 15 60_Anyone develop a matrix on UDB for AIX Performance monitors?12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Thu, 15 Mar 2001 07:47:52 -0600404_iso-8859-1 Looking to find out if anyone out there has developed a matrix to use to evaluate UDB AIX (or NT) performance monitors available on the market

?

================================================ 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. 1951 71 30_DB2 SysProg (Rockland Cty, NY)16_Charles Valentin15_VALENCH@AOL.COM29_Thu, 15 Mar 2001 08:51:16 EST456_US-ASCII Hi List, I'm looking for a DB2 Systems Programmer to work @ our Orangeburg, NY (Rockland Cty),NY IS HQ. Requirements: BS, 6yrs+ exp,SMP/E, Data Sharing, (TMON,BMC,CDB,File-DB2,Xpediter), Restart/Recovery, IBMLink, etc. We're preparing to go Data Sharing & need qualified individual with expertise in this area. Qualified candidates can e-mail or fax resume & salary requirements to: charles.valentin@verizonwireless.com or Fax (845) 365-5937 [...] 2023 18 38_Re: Dynamic Access path change - HELP!15_Christopher Tee24_chris.tee@TOTALISE.CO.UK31_Thu, 15 Mar 2001 13:56:54 +0000484_ISO-8859-1 Is T1_OFFICES_REF. IDAMOFFICE a nullable column? If so, this would cause a TS scan.

----------------------- InterMutual Healthcare from Totalise. Peace of mind at an affordable price. Visit http://www.intermutual.com/health/

================================================ 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. 2042 27 25_FW: Cataloging a Database11_Scott Adams20_Sadams@AMERISURE.COM31_Thu, 15 Mar 2001 09:03:19 -0500549_iso-8859-1 Hello, We are currently in the process of rolling out Windows 2000 machines at our site and are trying to catalog the necessary Databases on the local machine. We are attempting to connect to both DB2 v5 for 0s/390, through a DB2 Connect 7.1 Gateway, and DB2 UDB 5.2 on a Windows NT machine. Does the ID that I use to run the script have to be locally defined to the machine and be within the administrators group? What exactly is DB2 v5.2 (fixpack8) looking for regarding authorization when attempting to catalog a Node & Database? [...] 2070 19 38_Re: Dynamic Access path change - HELP!16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 15 Mar 2001 08:58:47 -0500405_us-ascii Michael,

Can you make this a correlated sub-query, and perhaps this will cause the outer table to be accessed first.

HTH,

Rick Creech

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2090 51 38_FW: Dynamic Access path change - HELP!20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 15 Mar 2001 09:03:50 -0500477_us-ascii Hello Dr. Ebert

Both of your SQLs are JOIN. The difference is that one is coded as subquery but converted as join by DB2 and other is coded as join itself. What is strange is both are join but have different access paths. Firstly, why I am saying that a subquery is a JOIN? If you look at the QBLOCKNO for the first two rows of QUERYNO= 1, they have value as one (1) itself indicates that it is not a subquery but transformed into a join by the Optimizer. [...] 2142 40 25_Re: Sqlcode805 with DProp15_Christopher Tee24_chris.tee@TOTALISE.CO.UK31_Thu, 15 Mar 2001 14:07:59 +0000628_ISO-8859-1 Have you bound your V7 packages on both source and target systems?

>===== Original Message From DB2 Data Base Discussion List ===== >Hi forumers, >I just installed my DPROP V7 and when starting the capture, I receive: > >ASN0001E The Capture program encountered an SQL error. The routine nam >e is get_current_server() ; the SQL request is GET CURRENT SERVER; the > table name is ASN.IBMSNAP_REGISTER; the SQLCODE is -805; the SQLERRML > is 43; the SQLERRMC is DB2P..ASNLDM61.£ > >I now cannot find the reason, because I bound the packages and the plan >just before starting the capture. [...] 2183 59 69_Fetching first n records of large table (limited predicate filtering)19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 15 Mar 2001 08:01:45 -0600311_us-ascii Guys,

I'm looking for some help on the best way to code this in DB2 UDB (Unix). I am using Perl DBI as the programming language but can change to C if this would suit the solution better.

I've "sanitized" the table definitions to protect the guilty .

I have three tables - [...] 2243 61 35_Re: Can we switch off Db2 logging??11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 15 Mar 2001 09:13:40 -0500380_iso-8859-1 Assuming this is absolutely true "I have no data that needs recovery."

One way would be to archive to disk and then trigger on the DSNJ139I :DB26 LOG OFFLOAD TASK ENDED

message to go delete all the archive disk files. If you do not have a message automation system in place, another way would be to just run a batch job n times a day to delete them. [...] 2305 34 24_SQL5043N on starting DAS17_Marcius D. Moraes22_marcius@GOLDNET.COM.BR31_Thu, 15 Mar 2001 11:15:46 -0300414_us-ascii Hi,

In the ISP where I work, we have a server running the IBM Net.Commerce V3.1.2 over a DB2 Database, and based on a Windows NT platform. But when we start the DB2 administrative instance an SQL5043N is issued, and in the file db2diag.log is written the following message:

"DIA3016W DISCOVER mode = SEARCH was configured, however, DISCOVER_COMM was not configured with any protocols." [...] 2340 17 18_Error Explanation?9_John Cops31_John.Cops@HERITAGEINSURANCE.COM31_Thu, 15 Mar 2001 08:25:56 -0600372_us-ascii Does anybody know what this error/reason code means?

SYSTEM ABEND CODE 04E REASON CODE 00C90101

thanks John

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2358 37 38_Re: Dynamic Access path change - HELP!16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 15 Mar 2001 09:30:12 -0500487_us-ascii Michael,

Maybe I should expand a little on my last message. I always approach this kind of situation with the following:

What I am trying to achieve is to get DB2 to access the table first that will give the smallest subset of data, because the second table is only accessed the number of times that the first table has qualifying rows. This is determined by the cardinality of the tables and of course how many rows qualify after the predicates are applied. [...] 2396 49 47_Returning first n rows from large table quickly19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 15 Mar 2001 08:24:20 -0600480_us-ascii Platform DB2 UDB on Unix.

I need help to improve the performance of the following (sanitized to protect the guilty ) table and query options -

Table TRANSACTION (TRANSACTION_ID, TRANSACTION_TIMESTAMP, more fields) with PK first two and cardinality approximately 3 million and growing

Table TRANSACTION_DETAIL (TRANSACTION_ID, TRANSACTION_TIMESTAMP, DETAIL_SEQNO, more fields) with PK first three and cardinality approx 16 million and growing [...] 2446 119 31_Re: Mainview for DB2 and DB2 PM14_William Poston21_william.poston@DB.COM31_Thu, 15 Mar 2001 08:33:57 -0600580_us-ascii Does any one have any feelings comparing insight with mainview?



---------------------------------------- Message History ----------------------------------------



From: Shapiro.Dave@principal.com on 03/14/2001 05:02 PM GMT

Please respond to DB2-L@ryci.com

To: DB2-L@ryci.com cc: Subject: Re: Mainview for DB2 and DB2 PM





Cathy,

Interesting. We dumped Omegamon for Mainview and never looked back. I attended a demo for DB2 PM at IDUG and was unimpressed with it's functionality (in comparison). [...] 2566 27 31_Replication storage information15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Thu, 15 Mar 2001 09:42:19 -0500494_iso-8859-1 When you define a replication source and subscription in UDB v7.1, where is this information stored - whether on the mainframe or the distributed server?

Another way of asking this is: What part (if any) does the mainframe play when creating a replication source/subscription?

Thanks -

Rick McClendon D.B.A. Systems Analyst Florida State University C6124 University Center Tallahassee, FL 32306-2620 E-Mail : rmcclend@admin.fsu.edu Voice : (850) 644 - 4953 [...] 2594 31 38_Re: Dynamic Access path change - HELP!9_Colin Fay13_cfay2@CSC.COM31_Thu, 15 Mar 2001 09:44:22 -0500476_us-ascii Hi Dr Ebert,

I always enjoy you emails !

Here's an idea:

Add to index on t1_offices_ref , the column cdsunm so you have (non-unique?) index

(idamoffice ,cdsunm)

(if you require uniqueness rule , add a separate index) (also pin that index into its own bufferpool, sized appropriately?)

hopefully making you access INDEXONLY for the join syntax perhaps this will push the access sequence to process the large table first. [...] 2626 42 22_Re: Error Explanation?0_19_mike.holmans@BT.COM31_Thu, 15 Mar 2001 14:47:49 -0000438_- Yes. It means your tablespace is up the chute. It's often caused by DSN1COPYing datasets from one subsystem to another without making sure SEGSIZE is the same in both, but there are plenty of other ways. C90101 is short for "Call 911".





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 [...] 2669 52 13_Re: IBM C/C++12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 15 Mar 2001 06:53:46 -0800612_us-ascii It is. The output of the builder REXX routine is a C program that is compiled and linkedited. Without a C/C++ compiler you cannot use the Stored Procedure builder tool. --- James Campbell wrote: > I think Stored Procedure Builder is one. (I think the output of the > builder > is actually a C program.) > > /* standard disclaimer */ > James Campbell > Hansen Corporation > > -----Original Message----- > From: JOHN G. MATTHEWS [mailto:HC#JGM@MVS.MEDCTR.UCLA.EDU] > Sent: Thursday, 15 March 2001 3:46 > To: DB2-L@RYCI.COM > Subject: [DB2-L] IBM C/C++ > > > We are on [...] 2722 29 22_Re: Error Explanation?0_25_haroldramsey@NETSCAPE.NET31_Thu, 15 Mar 2001 09:56:45 -0500710_ISO-8859-1 You have an internal error within DB2. What I would look for is an inconsistent data problem.





DB2 Data Base Discussion List wrote: > > Does anybody know what this error/reason code means? > > SYSTEM ABEND CODE 04E REASON CODE 00C90101 > > thanks > John > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. > __________________________________________________________________ Get your own FREE, personal Netscape Webmail account today at http://webmail.netscape.com/ [...] 2752 47 22_Re: Error Explanation?0_24_lightsey@ITS.STATE.MS.US31_Thu, 15 Mar 2001 08:59:05 -0600407_us-ascii For my users, it generally means a tablespace restore from a dsn1copy died and no one noticed until the message showed up. HTH







John Cops cc: Sent by: DB2 Data Base Subject: Error Explanation? Discussion List



03/15/2001 08:25 AM Please respond to DB2 Data Base Discussion List [...] 2800 65 22_Re: Error Explanation?0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Thu, 15 Mar 2001 09:52:38 -0500577_us-ascii John,

Check your *MSTR for additional info (tablespace, index, etc) . (Also the console log)

00C90101

Explanation: The data manager detected an internal error within DB2. This error may indicate inconsistent data or an error in internal DB2 control structures or code.

This abend reason code is issued by the following CSECT: DSNnnnnn

'nnnnn' is the CSECT name identifier given in VRARRK5 of the VRA in the SDWA (see the Problem Determination section of this message). 'DSNnnnnn' is the full CSECT name given in the dump title. [...] 2866 56 28_Re: SQL5043N on starting DAS0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Thu, 15 Mar 2001 09:54:25 -0500689_us-ascii Did you recycle UDB after making the config changes?







"Marcius D. Moraes" @RYCI.COM> on 03/15/2001 09:45:46 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: SQL5043N on starting DAS



Hi,

In the ISP where I work, we have a server running the IBM Net.Commerce V3.1.2 over a DB2 Database, and based on a Windows NT platform. But when we start the DB2 administrative instance an SQL5043N is issued, and in the file db2diag.log is written the following message: [...] 2923 118 22_Re: Error Explanation?13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 15 Mar 2001 09:10:17 -0600535_iso-8859-1 Since you do not seem to have access to the manuals ...

visit http://www-4.ibm.com/software/data/db2/os390/v6books.html and checkout the messages and codes.





hth gm





00C90101





Explanation: The data manager detected an internal error within DB2. This

error may indicate inconsistent data or an error in internal DB2 control

structures or code.





This abend reason code is issued by the following CSECT: DSNnnnnn [...] 3042 96 22_Re: Error Explanation?12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV31_Thu, 15 Mar 2001 10:16:19 -0500479_iso-8859-1 Aside from the answers already given, another possible cause is systemic. The csect of the offending module helps to narrow down what's causing it. Look in the dump and see what is referenced.



If the abending CSECT is DSNIBMCL:500B, look at APAR PQ33533.





-----Original Message----- From: John Cops [mailto:John.Cops@HERITAGEINSURANCE.COM] Sent: Thursday, March 15, 2001 9:26 AM To: DB2-L@RYCI.COM Subject: Error Explanation? [...] 3139 21 44_Re: Slooooooow Delete, DB2 V6.1, OS/390 2.1012_Jim Harrison17_jharrison@GMX.NET31_Thu, 15 Mar 2001 10:18:23 -0500485_us-ascii Absolutely. It might even be like the case we had where each search for a child record resulted in a tablespace scan! Talk about a hog. (Yeah, bad SQL can slip by unnoticed)

At 08:32 AM 03/15/2001 -0500, Michael McKinney said: >I believe the Foreign Keys you described might be a big cause of this. >Even though you stated that the child tables contain no 2002 records, I >believe DB2 will check the child tables with each delete you do against the >parent table. [...] 3161 60 35_Re: Can we switch off Db2 logging??13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 15 Mar 2001 09:30:21 -0600530_us-ascii Make sure you don't generate unnecessary logging either. If you load tables, be sure to use LOG NO. The default is to log and this can generate a lot of logging.

"Hayden, Lee" wrote:

> Assuming this is absolutely true "I have no data that > needs recovery." > > One way would be to archive to disk and then trigger on > the > DSNJ139I :DB26 LOG OFFLOAD TASK ENDED > > message to go delete all the archive disk files. If you > do not have a message automation system in place, another > way would be to [...] 3222 43 33_Looking for UDB Content Providers9_Cam White20_cwhite@REVEALNET.COM31_Thu, 15 Mar 2001 10:44:15 -0500460_iso-8859-1 RevealNet is looking for UDB content providers (Unix and NT). If you have strong user experience in any of the following areas, please contact Michael Gilsbach, DB2 Product Manager (mgilsbach@revealnet.com): - Design and Development - Installation and Configuration - Administration - Tuning and Troubleshooting - Networking Content providers will be compensated for all accepted material. Contact Michael Gilsbach at 540-373-7703 for details. [...] 3266 75 13_Re: IBM C/C++13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 15 Mar 2001 10:02:26 -0600280_iso-8859-1 Myron,

I am in the same boat. First I investigated using Java SP's, but IBM's DB2 implementation does not use a JVM. We have to buy a Java Compiler to make LE370 code. I guess if it did use a JVM we would have to pay for that anyway. Same for the C++ code. [...] 3342 163 40_Re: what can I do to achieve a good perf64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Thu, 15 Mar 2001 10:07:49 -0600360_ISO-8859-1 Hi Walter.

The answer to your question, "Can we do anything ahead of time to assure a good performance and not wait until a bad performance will catch us up." is yes, and many, many shops have done this. It is a very broad question, but is still worth addressing.

I think that this can be looked at from a few gross perspectives. [...] 3506 73 28_UDB V6.1 On Solaris Question18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 15 Mar 2001 11:12:17 -0500414_iso-8859-1 Anyone ever experience the following situation: 1) installed UDB V6.1 fixpack3 on Solaris OS v2.7 2) created an instance successfully 3) created the DAS server successfully 4) successfully started the UDB server (db2start) 5) tried to start the DAS server but received SQL1072C 6) shutdown UDB (db2stop) 7) successfully started the DAS server 8) tried tos tart the UDB server but received SQL1072C [...] 3580 122 13_Re: IBM C/C++12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 15 Mar 2001 08:17:53 -0800579_us-ascii Glenn I don't know the price of a C compiler on your machine. But unless you don't have many stored procedures, I think that the time saved by your JAVA and other programmers creating SP's using the Stored Procedure Builder (SPB) would more than pay for the cost of a C compiler. The increase in productivity using the SPB is that great. I've seen it. My client wrote close to 100 stored procedures with basically one programmer in about three weeks. Granted many of the stored procedures are pretty simple. But still, the increase in productivity using the SPB to [...] 3703 145 13_Re: IBM C/C++13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 15 Mar 2001 10:29:47 -0600406_iso-8859-1 Myron,

Thanks for the good feedback - i appreciate it and it has sparked by interest again. I may try and get something working on the NT/UDB world and see if I can sell it that way.

I understand that the SPB will generate Java code as well .... so the backend language is transparent to programmers. Too bad the SPB does not knockout Cobol ... then life would be good again. [...] 3849 29 20_ZPARM param PARAMDEG16_Ian Brodie Smith18_ismith2@UK.IBM.COM31_Thu, 15 Mar 2001 16:19:01 +0000450_us-ascii Hi,

I am trying to understand what effect PARAMDEG has. (I have looke in the manuals but can find no reference)

We have PARAMDEG set to 0 Various applications use SET CURRENT DEGREE ANY I can see that the maximum level of parallelism that has been used is 8 (in TMON DB2) So why 8 ? What does PARAMDEG do ?, does 0 mean no limit for current degeree ? What are the recommendations for PARAMDEG ? we are DB2 v5 on os390 2.8 [...] 3879 190 42_Re: FW: Dynamic Access path change - HELP!0_18_mebert@AMADEUS.NET31_Thu, 15 Mar 2001 17:36:10 +0100432_us-ascii Hello all,

I have concatenated 4 replies I received below in temporal sequence. Answers/comments here:

- none of the involved columns is nullable.

- making this a correlated subquery as QUERYNO=3, below are the SQL and EXPLAIN (QUERYNO=1 was the original subselect, QUERYNO=2 the explicit join). ** This looks quite promising, and I will ask the developers to change their program accordingly. ** [...] 4070 75 51_Re: Returning first n rows from large table quickly19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 15 Mar 2001 10:38:40 -0600418_us-ascii Thanks Helen,

The DESC was an oversight.

But I didn't know that clause existed. Just tried it from Command Center, and a statement which just didn't respond before came back in 18 seconds elapsed without any extra tuning. The actually (unsanitised) statement is actually a 6-way join so I'm delighted with the result so far - and the developer and his manager just about kissed my feet [...] 4146 155 13_Re: IBM C/C++14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 15 Mar 2001 10:45:19 -0600534_iso-8859-1 Myron,

Just to answer one of your thoughts. Java Stored Procedures and now even Java User-defined functions (with some limitations) are supported in OS/390 DB2 V7, without requiring the special compiler. They will use new technology allowing the use of a new JVM.

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 [...] 4302 53 24_Re: ZPARM param PARAMDEG12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 15 Mar 2001 12:06:14 -0500432_us-ascii I believe setting it to 0 turns it off for whole system unless you do set degree within application. parmdeg is a max for whole subsystem..







Ian Brodie Smith @RYCI.COM> on 03/15/2001 11:19:01 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: [...] 4356 81 24_Re: ZPARM param PARAMDEG13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 15 Mar 2001 10:08:47 -0800370_iso-8859-1 PARAMDEG was introduced by APAR PQ28414 (PTF UQ33442 for v5). The default value 0 means that the degree of parallelism is NOT limited. You can specify values from 0 to 254, but unless you have a reason to limit parallelism, leave it at 0 (see APAR text for more details). I do not believe this limit can be overridden by SET CURRENT DEGREE in a program. [...] 4438 48 46_Re: inactive (distributed) thread holding lock10_Linux POP319_cwalmer@RITEAID.COM31_Thu, 15 Mar 2001 13:25:46 -0500295_iso-8859-1 Dave, Have a look at PQ28037. We thought we might be hitting this issue in V6 but apparently it only relates to V5. However, in V6, inactive threads can still hold DBD and SKCT/SKPT locks. IBM recommended lowering the POOLINAC parm (V6 zparm) to limit the impact of these locks. [...] 4487 174 31_Re: Mainview for DB2 and DB2 PM12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 15 Mar 2001 20:45:25 +0200743_iso-8859-1 Hi, I've had enough of insight ;-)) Still prefer omegamon

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com ----- Original Message ----- From: "William Poston" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, March 15, 2001 4:33 PM Subject: Re: Mainview for DB2 and DB2 PM



> Does any one have any feelings comparing insight with mainview? > > > ---------------------------------------- Message History ---------------------------------------- > > > From: Shapiro.Dave@principal.com on 03/14/2001 05:02 PM GMT > > Please respond to DB2-L@ryci.com > > To: DB2-L@ryci.com > cc: > Subject: Re: Mainview for DB2 and DB2 PM > > > > Cathy, > [...] 4662 31 34_Reorg at partition level with NPIs14_Richard Arnold24_Richard.Arnold@CHASE.COM31_Thu, 15 Mar 2001 13:05:35 -0600367_us-ascii Hello list,

DB2 for OS/390 V6; OS 390

We have a theory about reorging a TS at the partition level, and would like someone to either confirm our view, or correct us.

We have a large partitioned tablespace with 5 NPIs. Our theory is that we can reorg partitions with the IBM reorg utility simultaneously without clashing on the NPIs. [...] 4694 66 24_Re: ZPARM param PARAMDEG14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 15 Mar 2001 13:08:25 -0600359_iso-8859-1 Ian,

Once it was a hidden ZPARM but was externalized in V5 with PTF, and that is where the initial documentation was. 0 = parallelism unlimited, and that is the default setting. It is good to use where parallelism always falls back due to many things. Another use is to limit parallelism to a subset of the number of CPUs on a machine. [...] 4761 197 31_Re: Mainview for DB2 and DB2 PM16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 15 Mar 2001 14:15:29 -0500392_iso-8859-1 Is Insight a CA product? If so, take a close look at the contract for some surprises you may see when you do CPU upgrades :=(

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: Thursday, March 15, 2001 1:45 PM To: DB2-L@RYCI.COM Subject: Re: Mainview for DB2 and DB2 PM [...] 4959 46 23_Datajoiner/DB2 question18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Thu, 15 Mar 2001 14:17:13 -0500591_iso-8859-1 We are currently using Datajoiner Version 2.2 (I think) and we are at DB2 Version 5 on OS/390. Here's my problem: Datajoiner sits on an NT server and we have DDF threads coming through Datajoiner up to the DB2 mainframe, if you will. My problem is lately the Datajoiner database is locking up and no one can access Datajoiner. Ie; someone launches a MS Access against Datajoiner to open up a catalog table on Datajoiner and their desktop just gets the hour-glass, nothing happens. When I turn on the Datajoiner Monitor I can see the 'applications waiting for locks' increase [...] 5006 73 30_Re: Platinum Tools With DB2 V612_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 15 Mar 2001 12:27:00 -0500358_- Just ordered this tape. But, Technician told me that messages will still be sent to SYSLOG. There is still a problem with this and they are working on further fixes. Please let us know if this is not true.

>>> DB2-L@RYCI.COM@inter2 03/10/01 03:27PM >>> Maintenance Tape 1047HA has been released and solves the LMP keys internal calls mismatch. [...] 5080 67 34_Reorg at partition level with NPIs15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 15 Mar 2001 13:30:25 -0600572_us-ascii Richard,

sample reorg stmt :

REORG TABLESPACE XYZ.ABC PART n LOG NO SORTDATA NOSYSREC SORTKEYS COPYDDN(PARTnA,PARTnB) UNLDDN(SYREC23) UNLOAD CONTINUE STATISTICS TABLE(ALL) INDEX(ALL)

Regards Nayeem ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 03/15/2001 01:25 PM ---------------------------



Richard Arnold on 03/15/2001 01:05:35 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US) [...] 5148 49 37_Forte Applications and DB2 UDB Tuning12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Thu, 15 Mar 2001 13:51:19 -0600605_US-ASCII Are there any other organizations that use the Forte development language accessing DB2 UDB out there? I would like to hear of your experiences in trying to tune the environment. What considerations have you made to contend with the dynamic nature of all the Forte SQL. Do you enlarge the memory area within DB2 that handles dynamic SQL? What steps does your organization take to catch poor performing SQL before production rollouts. When observing metrics of DB2 performance, do you also look at Forte metrics (number of transactions) and AIX metrics (hard drive activity or cpu activity). [...] 5198 127 30_Re: Platinum Tools With DB2 V60_19_csutfin@AMSOUTH.COM31_Thu, 15 Mar 2001 14:09:54 -0600299_us-ascii Bob,

Tape 1047HA got rid of the messages for me. I also had to add an LMP Key for Batch Processor. (We actually use that).

Now if they can get rid of the S0C4 in PRF I'll be one step further along.

Good Luck Carol Sutfin Corporate DBA - Tech. Support AmSouth Bank [...] 5326 39 37_DB2, Net.data and web server question13_Mario Novelli15_mfn@PO.CWRU.EDU31_Thu, 15 Mar 2001 15:41:59 -0500410_us-ascii We are using Net.data to retrieve data from DB2 over the web. There are 5 DB2 subsystems across two LPARS with shared DASD, catalogs.... and coupled but not an official sysplex.

Lets say DB2 1 through 4 run on LPAR A while DB2 5 runs on LPAR B. Also on LPAR B is Net.data and OE running Websphere. The Net.data initialization file (db2www.ini) contains "DB2SSID DB2 5", the DB2 on lpar B. [...] 5366 63 49_Reorg at partition level with NPIs: Clarification14_Richard Arnold24_Richard.Arnold@CHASE.COM31_Thu, 15 Mar 2001 14:51:44 -0600448_us-ascii Hello list,

Let me clarify my question. We want to run concurrent reorgs against different partitions without clashing on the NPIs. Just plain old SHRLEVEL NONE reorg.

The purpose of requesting the clarification is that on of my co-workers believed that a reorg of, say, partition 17 of a 64 partition tablespace would attempt to rebuild the entire NPI, thus preventing us from reorging more than 1 partition at a time. [...] 5430 161 42_Re: FW: Dynamic Access path change - HELP!13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 15 Mar 2001 14:56:59 -0600454_us-ascii Michael,

Sorry to come in late, but it appears that the original SQL may have chosen a degree of parallelism which was degraded at runtime due to insufficient bufferpool storage.

I know the trace output you included showed zero parallel tasks, and this suggests to me that rather than reduce the degree, it has degenerated entirely to a sequential access path. The explain output however did not include the parallel columns. [...] 5592 44 41_Reorg at partition level with NPIs -Reply12_John Bucaria22_jbucaria@MAIL2.PCH.COM31_Thu, 15 Mar 2001 16:00:10 -0500482_US-ASCII I'm doing it at this very moment with DB2 V5.

John

>>> Richard.Arnold@CHASE.COM 03/15/01 02:05pm >>> Hello list,

DB2 for OS/390 V6; OS 390

We have a theory about reorging a TS at the partition level, and would like someone to either confirm our view, or correct us.

We have a large partitioned tablespace with 5 NPIs. Our theory is that we can reorg partitions with the IBM reorg utility simultaneously without clashing on the NPIs. [...] 5637 100 49_Reorg at partition level with NPIs: Clarification15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 15 Mar 2001 15:30:46 -0600380_us-ascii Richard,

Yes , it is possible to run reorg on all partitions together. create different jobs for each partition using diff utilid's & different required files and run all jobs( say 20 jobs for 20 parts) together with diff jobnames,utilid's. For files you can specify secondary qualifier which identifies partition number. example : aaaa.bbbb.partnnn. ....... [...] 5738 75 56_Reorg at partition level with NPIs: Clarification -Reply12_John Bucaria22_jbucaria@MAIL2.PCH.COM31_Thu, 15 Mar 2001 16:45:03 -0500533_US-ASCII You are right, he is wrong.

>>> Richard.Arnold@CHASE.COM 03/15/01 03:51pm >>> Hello list,

Let me clarify my question. We want to run concurrent reorgs against different partitions without clashing on the NPIs. Just plain old SHRLEVEL NONE reorg.

The purpose of requesting the clarification is that on of my co-workers believed that a reorg of, say, partition 17 of a 64 partition tablespace would attempt to rebuild the entire NPI, thus preventing us from reorging more than 1 partition at a time. [...] 5814 76 41_Re: DB2, Net.data and web server question10_Linux POP319_cwalmer@RITEAID.COM31_Thu, 15 Mar 2001 17:09:32 -0500577_iso-8859-1 Mario, The problem is that net.data and WebSphere when running in OE must connect to a local database and use its DDF to connect to other remote DB2s. Basically, you have three options:

1) install net.data and WebSphere on another server somewhere (UNIX, linux, NT) along with DB2 Connect (either just the runtime client or EE) 2) install net.data and WebSphere on LPAR A 3) For a non-IBM solution, I believe Shadow Direct has a DRDA server that can run on OS/390. You could use this gateway to access the other DB2s and would then not need DB2 5's DDF. [...] 5891 80 53_Re: Reorg at partition level with NPIs: Clarification11_Kwan, James18_James_Kwan@BMC.COM31_Thu, 15 Mar 2001 16:18:14 -0600507_iso-8859-1 If you reorg the whole TS, the NPI will be rebuilt, otherwise it will be updated.

James

-----Original Message----- From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] Sent: Thursday, March 15, 2001 2:52 PM To: DB2-L@RYCI.COM Subject: Reorg at partition level with NPIs: Clarification



Hello list,

Let me clarify my question. We want to run concurrent reorgs against different partitions without clashing on the NPIs. Just plain old SHRLEVEL NONE reorg. [...] 5972 24 27_Stored Procedure/CICS Paper15_Paul S Rockwood19_rockwood@US.IBM.COM31_Thu, 15 Mar 2001 17:30:25 -0500281_us-ascii A customer suggested that we look at a paper by Chris Date and Geoff Sharman regarding CICS vs stored procedures. They said it was very good and focused on scalability issues. I'm hoping someone will be able to point to a link for the paper, or know someone who can. [...] 5997 20 43_Re: Any pitfalls with RELEASE(DEALLOCATE) ?13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Thu, 15 Mar 2001 17:04:34 -0600524_- Thanks everybody In summary two things have been mentioned to watch out for: * Table / Tablespace locks which are not released on COMMIT. This should not be a problem in our case as page locks are being used and there have been no esclations till now. * Thread reuse holding ( and accumulating ) resources : As I mentioned the packages I'm planning to bind are all batch ( using TSO attach ) and AFAIK there is no thread reuse in this situation. Or did the things change while I was napping ? We are on V5 OS/390 2.8 [...] 6018 30 46_Re: inactive (distributed) thread holding lock14_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Thu, 15 Mar 2001 17:32:02 -0600591_- We have had similar problems and here is what we found in our sutuation hoping it is helpful. We had distributed threads inactive in wait-remreq holding locks. In some case they were X locks. What was happening is that the user was writing application using visual age for java which has a workbench component. A connection was made to DB2 holding a thread from the workbench. When an application was run from within the workbwnch and failed the application cut communication with DB2 but the workbench still had a connection. By exiting the workbench the thread was freed. There was [...] 6049 81 61_rename a column name in an existing table in DB2 Version 5...16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 16 Mar 2001 01:46:43 +0000440_iso-8859-1 ----- Forwarded by Aurora E DellAnno/CAM/Candle on 03/16/2001 01:44 AM -----

Kannan,

not even our oh-so-nice DB/Workbench lets you quite do that, especially with v5.

However, were you to upgrade yor DB2 subsystem, starting from v6 you can manipulate your existing columns a bit more (ALTER TABLE datatype etc.).



Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...] 6131 93 23_Re: DR with DB2 and IMS16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 16 Mar 2001 00:56:47 +0000477_us-ascii





Guys,

sorry for not enclosing the address, I thought I'd be boring and repeating myself....

it's IMS-L@lists.missouri.edu, however if you can't find the thread please contact me offline since I have saved the postings (I hope so at least)...

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 414 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com [...] 6225 54 32_DB2 and the Java Virtual Machine15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 16 Mar 2001 15:57:24 +1100431_- So I take it from the total absence of replies that no one has anything constructive to say about my problems installing DB2 for Windows 98 V6.1. You know, the business of DB2 not being able to start the Java Virtual Machine when I try to fire up the Control Center. Fair enough. Does anyone know the name of a Listserv group more focused on DB2 for Intel boxes and, if so, could they please let me know so I can ask there. [...] 6280 21 26_Managing the mapping table10_Alan Smith25_alancsmith@BTINTERNET.COM31_Fri, 16 Mar 2001 07:33:32 +0000329_us-ascii For those using SHRLEVEL CHANGE online reorgs - how do you manage the mapping tables? Do you -

1) Have a fixed set of mapping tables which you share across reorgs? 2) Have a dedicated mapping table for each tablespace? 3) Create the mapping table/tablespace/index before each reorg and drop them at the end? [...] 6302 32 30_Re: Managing the mapping table0_18_mebert@AMADEUS.NET31_Fri, 16 Mar 2001 09:43:22 +0100389_us-ascii We've got one permanent mapping table TS with 8 mapping tables. 4 are used by production jobs, 4 are for the DBA. See numerous postings in the archives about the setup of mapping TS/Indexes.

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



From: Alan Smith on 16/03/2001 07:33 GMT [...] 6335 119 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Fri, 16 Mar 2001 10:21:31 +0100605_us-ascii Hi Richard,

while you can do it (the BUILD2 phase will update the corresponding Logical NPI partitions only), it doesn't mean you should. 1) Don't run too many jobs, especially high-CPU ones like REORG, in parallel. As a rule of thumb, I'd say don't exceed the number of processors (assuming you've got the machine to yourself). 2) The NPIs are updated in an SQL-like manner, and it's much slower than a REBUILD. Also, any NPI key may belong to one, many, or all partitions (see my posting "Re: Online Reorg Space Problems" dated 17.4.2000). That means that (different RIDs belonging [...] 6455 19 45_Re: Reorg at partition level with NPIs -Reply10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 16 Mar 2001 10:45:48 +0100503_us-ascii It was possible since DB2 V 3, but the NPI index(es) was updated (sllloooowwww) and only 1 reorg at time could update

that index (you had a serialization on that index). Now it's faster, but not so faster as I expected.

Regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6475 14 24_Re: ZPARM param PARAMDEG16_Ian Brodie Smith18_ismith2@UK.IBM.COM31_Fri, 16 Mar 2001 09:50:33 +0000320_us-ascii Thanks to Richard, Cathy and Jeff for their input.

Ian B 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. 6490 74 32_NT, AIX/UDB-7.1/Database Replica15_Agarwal, Ashish28_Ashish.Agarwal@HPSGLOBAL.COM31_Fri, 16 Mar 2001 16:17:16 +0500517_- Hi all, I am new to DB2-7.1. My problem is that I have to create a Database replica on another machine. Do i have to export each table and then import each table on the new server. In oracle, we have the full database backup option whereby I can export and then import the enitre database to any terminal. Dont i have a similar option in DB2? This thing needs to be done for Win-Nt to Win-Nt and Win-NT to AIX both( am using blobs too). Can anybody give me some ideas please? Thanks in advance Regards Aashish [...] 6565 86 53_Re: Reorg at partition level with NPIs: Clarification12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Fri, 16 Mar 2001 07:45:27 -0500602_us-ascii Is that true for a single partition LOAD as well or is it just for a REORG?

Martin Wolff.

"Kwan, James" wrote:

> If you reorg the whole TS, the NPI will be rebuilt, otherwise it will be > updated. > > James > > -----Original Message----- > From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] > Sent: Thursday, March 15, 2001 2:52 PM > To: DB2-L@RYCI.COM > Subject: Reorg at partition level with NPIs: Clarification > > Hello list, > > Let me clarify my question. We want to run concurrent reorgs against > different partitions > without clashing on the NPIs. Just [...] 6652 83 36_Re: DB2 and the Java Virtual Machine16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Fri, 16 Mar 2001 07:48:34 -0500601_us-ascii DB2EUG DIGEST http://people.mn.mediaone.net/scottrmcleod/ For subscription info see bottom of message

To receive messages separately, send email to db2eug-request@lists.best.com containing the line: subsingle To unsubscribe, send: unsubscribe









"Bell, Raymond W" on 03/15/2001 11:57:24 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: DB2 and the Java Virtual Machine [...] 6736 37 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Fri, 16 Mar 2001 14:22:47 +0100538_us-ascii Yes. No, that's ambiguous, try again: Yes to the first part, No to the second. Also note that if you do a LOAD REPLACE INTO TABLE .. PART nn, DB2 will have to scan the entire NPI (or all NPIs) in order to delete old RIDs pointing to the loaded partition in addition to inserting the new ones. With REORG, DB2 knows which keys/RIDs need updating. But it doesn't first store the keys affected by the LOAD REPLACE, so it has to check all of them. With other words, the BUILD2 phase with LOAD REPLACE will take even longer than [...] 6774 49 30_Re: Managing the mapping table16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 16 Mar 2001 08:27:54 -0500488_iso-8859-1 Have tried these. Results: 1) Have a fixed set of mapping tables which you share across reorgs? -- requires careul attention to scheduling. 2) Have a dedicated mapping table for each tablespace? -- this is what we wound up doing long-term. Requires more space than the other options. 3) Create the mapping table/tablespace/index before each reorg and drop them at the end? -- tried this but catalog/directory contention during tight reorg window caused too many problems. [...] 6824 173 42_Re: FW: Dynamic Access path change - HELP!20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 16 Mar 2001 08:41:41 -0500366_iso-8859-1 Guten Tag Dr. Ebert

After all these information, it is becoming clear, what exactly may have happened runtime.

Tablespace on BP8 was the first table doing a Sequential scan with 9241 getpages. Since column CDSUNMC is has very low cardinality (67), it doesn't do any useful filtering and that makes sways the optimizer to go for a scan. [...] 6998 65 69_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spaces.10_Roger Hecq19_Roger.Hecq@MSDW.COM31_Fri, 16 Mar 2001 08:57:15 -0500583_us-ascii If this REXX is still available, I would like to obtain a copy of it.

Thanks in advance.

Christine Sawyer wrote:

> Hi Jim, > Could you please send me a copy also? > > Thanks > Christine > > >From: Jim Lewandowski > >Reply-To: DB2 Data Base Discussion List > >To: DB2-L@RYCI.COM > >Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa > > ces. > >Date: Tue, 11 Jan 2000 10:09:00 -0600 > > > >I had sent out many copies of an SMF 42 subtype 6 REXX that provided a > >detail and summary [...] 7064 56 36_Re: NT, AIX/UDB-7.1/Database Replica12_Cook, Nicole19_Nicole_Cook@BMC.COM31_Fri, 16 Mar 2001 08:08:48 -0600504_iso-8859-1 Hi Ashish,

Just like Oracle, you can take a full database backup and then restore it on a new machine. Look under restore in the command reference, it has the exact syntax to use when restoring to a new machine.

Or another option is to use BMC's Change Manager for DB2 Universal Database. With one of the many features of this product, you can migrate complete databases, or just the objects of your choice from one database to another (it will migrate the data as well!!). [...] 7121 96 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Fri, 16 Mar 2001 15:31:57 +0100553_iso-8859-1 Hi,

the same applies to me as well.

cheers ruediger kurtz

Roger Hecq schrieb: > > If this REXX is still available, I would like to obtain a copy of it. > > Thanks in advance. > > Christine Sawyer wrote: > > > Hi Jim, > > Could you please send me a copy also? > > > > Thanks > > Christine > > > > >From: Jim Lewandowski > > >Reply-To: DB2 Data Base Discussion List > > >To: DB2-L@RYCI.COM > > >Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa > > > [...] 7218 106 36_Re: NT, AIX/UDB-7.1/Database Replica18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Fri, 16 Mar 2001 09:35:09 -0500394_iso-8859-1 Below is a sample on how you would restore one database to another database. This script was used on a Solaris machines but all you have to do is code the appropriate directories if you are going to NT. Also, you must make sure that the codepage is the same on each machine (your source machine and your destination machine) and you cannot go from Solaris to NT and vice-versa. [...] 7325 102 45_H E L P - Can I get a variable into a VIEW???10_Mark Labby16_mlabby@PHEAA.ORG31_Fri, 16 Mar 2001 09:19:13 -0500514_us-ascii I have been trying to take a complex Join with SubSelects in the statement and convert that to a View of the data. We have the Join that we need coded and it is working very efficiently. In trying to convert it to a View, the host variable that is used has to be pulled from the Join. The host variable that the Join uses is the bf_ssn, which is the first node in the primary index of both tables and has a cardinality of 1.5 million in both tables. This causes the access to go to a tablespace scan [...] 7428 17 16_LISTCAT Question10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Fri, 16 Mar 2001 09:48:37 -0500496_us-ascii DB2 v5 OS/390 Is there a way, using LISTCAT output to determine what the capacity of a partition is? Example: Partitioned tablespace, not large, with greater than 32 partitions. Max dataset size is 1gig. Can this info be determined on a LISTCAT?

================================================ 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. 7446 122 12_Use Of Nulls13_Brunner Don J21_Don.J.Brunner@IRS.GOV31_Fri, 16 Mar 2001 09:49:16 -0500372_iso-8859-1 Platform: DB2 UDB/OS390



I'm forwarding to the list a question about the use of nulls from one of our developers. I know some time ago there was much discussion about this subject on the list. I've searched the archives and discovered that the conversation must have taken place prior to Oct 1999, which is the date where the archives begin. [...] 7569 19 13_SQL CODE -44413_Doyle, Ingell21_DoyleI@UIHC.UIOWA.EDU31_Fri, 16 Mar 2001 08:43:31 -0600578_iso-8859-1 I received a -444 SQLCODE for the first time this week while testing a new stored procedure. It began showing up after a new compile, and continued to show up after recompile, and even after backing out changes to a point knew the stored procedure did work. Only after stopping/starting the stored procedure address space was I able to get it working again. There are several reasons the -444 SQLCODE can show up. Mine seemed to be the one due to the LOADLIB having taken extents; only by Stopping/Starting the stored procedure address space could I resolve it. [...] 7589 40 16_LISTCAT Question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 16 Mar 2001 09:09:28 -0600707_us-ascii Retrieve it from sysibm.systablepart table.



---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 03/16/2001 09:02 AM ---------------------------



Bill Brown on 03/16/2001 08:48:37 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: LISTCAT Question





DB2 v5 OS/390 Is there a way, using LISTCAT output to determine what the capacity of a partition is? Example: Partitioned tablespace, not large, with greater than 32 partitions. Max dataset size is 1gig. Can this info be determined on a LISTCAT? [...] 7630 41 13_SQL CODE -44415_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 16 Mar 2001 09:14:57 -0600774_us-ascii One of the load module not found in SYSIBM.SYSROUTINES . Look into EXTERNAL_NAME column of SYSIBM.SYSROUTINES . ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 03/16/2001 09:08 AM ---------------------------



"Doyle, Ingell" on 03/16/2001 08:43:31 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: SQL CODE -444





I received a -444 SQLCODE for the first time this week while testing a new stored procedure. It began showing up after a new compile, and continued to show up after recompile, and even after backing out changes to a point knew the stored procedure did work. [...] 7672 72 30_Re: Managing the mapping table14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 16 Mar 2001 15:16:45 -0000419_iso-8859-1 How about an option 4) Use an On-Line reorg utility that doesn't require a mapping table at all (i.e. CA or BMC).

I've never been sure why the IBM implementation even uses a table/index for RID updates - maybe someone from IBM knows??

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 7745 45 17_Re: SQL CODE -44413_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 16 Mar 2001 09:24:01 -0600503_iso-8859-1 Ingell,

You could also run a JCL IEBCOPY to compress the dataset (input and output dataset being the same loadlib, with DISP=SHR). This will compress the extents and therefore you do not need to stop/start the SP.

Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Doyle, Ingell Sent: Friday, March 16, 2001 8:44 AM To: DB2-L@RYCI.COM Subject: SQL CODE -444 [...] 7791 87 42_Re: FW: Dynamic Access path change - HELP!0_18_mebert@AMADEUS.NET31_Fri, 16 Mar 2001 16:29:24 +0100432_us-ascii Hello Mr. Pillay,

your mention of SYSTABSTATS finally pointed me in the right direction. Basically, the big partitioned TS was supposed to be handled like this:

- a series of "feeder" jobs puts data into the partition (one partition per day) - then a housekeeping job I developed runs (IC/REORG+Inline IC/STOP/ADRDSSU COPY/START/RUNSTATS) - then, the application job making use of the daily data is run. [...] 7879 105 30_Re: Managing the mapping table10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Fri, 16 Mar 2001 09:28:37 -0600564_us-ascii We just simply didn't have the DASD farm it would have required to create mapping tables for all of our tablespaces they 'need' 24x7 - does anyone have that much DASD?

So - our approach since we have some really, really large varying down to very small tables was to create a fixed number of generic mapping tables on each DB2 region, and a control table with the jobname and mapping table name & millions of rows we calculated would fit. A program calls the mapping control program with parms of say, 25 million rows & the jobname, grabs one [...] 7985 147 49_Re: H E L P - Can I get a variable into a VIEW???13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 16 Mar 2001 09:34:47 -0600614_us-ascii Mark,

What you are experiencing is view materialization, and what you would prefer is view merge.

Dependant on your release of DB2.....V5 as soon as you coded the outer joins and the GROUP BY, the optimizer must fully evaluate the view SQL, and then apply the predicates from the query against the view. V6 improves this and does not materialize for much of the outer join processing (I haven't checked the exact rules), and V7 improves this further to incorporate UNION simplification; but the rules still state that a GROUP BY within a view definition will result in materialization. [...] 8133 54 20_Re: LISTCAT Question0_20_John_Lendman@FPL.COM31_Fri, 16 Mar 2001 10:40:56 -0500665_us-ascii Yes it can, you take the High Used RBA and you can see how much space a tablespace is taking.

John





"Bill Brown" cc: Sent by: "DB2 Subject: LISTCAT Question Data Base Discussion List"



03/16/01 09:48 AM Please respond to "DB2 Data Base Discussion List"











DB2 v5 OS/390 Is there a way, using LISTCAT output to determine what the capacity of a partition is? Example: Partitioned tablespace, not large, with greater than 32 partitions. Max dataset size is 1gig. Can this info be determined on a LISTCAT? [...] 8188 19 49_Re: H E L P - Can I get a variable into a VIEW???15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 16 Mar 2001 09:21:39 -0600409_- Mark

As I know there is no chance to put a variable into a view, but if I look at your view definition, then the right outer isn't really an outer join, because the where-condition make the join to an inner. So the definition can be changed and maybe you can change it that the variable can be used when you select from the view. First of all I would change the rigth outer joins to inner ones. [...] 8208 159 53_Re: Reorg at partition level with NPIs: Clarification19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Fri, 16 Mar 2001 09:51:42 -0600618_iso-8859-1 Hi Doc, So, to reorg large partitioned tablespaces, I assume you first drop all the NPIs, partition reorg with some number of parallel jobs, then rebuild the NPIs in parallel?

Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately [...] 8368 109 79_on SUN DB2 V7.1 SQL1652N File I/O error occurred after installing FP2a_U474811.12_Peter Horner27_peter.horner@JULIUSBAER.COM31_Fri, 16 Mar 2001 16:43:41 +0100345_us-ascii Hi List

I'm on SUN OS 5.8 with DB2 UDB V7.1. I have already installed the 64Bit Fixpack. Everything is fine until apllying the FP2a_U474811 Fixpack. After this I get the message SQL1652N File I/O error occurred when trying to start the db2 administration server with "db2admin start".

DB2 starts fine and works fine. [...] 8478 34 41_Optimize for n rows in a DRDA environment11_John Puglia16_jpuglia@IWON.COM31_Fri, 16 Mar 2001 11:08:15 -0500403_ISO-8859-1 I know the "Optimize for n rows" can be used to influence the optimizer, but it does not limit the number of rows fetched. Where the "Fetch First n rows only" (V7) actually limits the number of rows fetched.

Does "Optimize for n rows" in a DRDA network transmission, act the similarly to the "Fetch First n rows only" (V7) , where by actually limiting the number of rows fetched? [...] 8513 67 20_Re: LISTCAT Question10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Fri, 16 Mar 2001 11:12:08 -0500550_us-ascii Let me clerify. I know that I can use highused and highalloc to determine what percentage of the allocated space is currently being used, but if my tablespace has 50 partitions and is not large, is it possible to determine that the max value of highalloc is 1 gig.

At 10:40 AM 3/16/01 -0500, you wrote: >Yes it can, you take the High Used RBA and you can see how much space a >tablespace is taking. > >John > > > > "Bill Brown" > SOUTH.NET> cc: > Sent by: "DB2 Subject: LISTCAT Question > Data [...] 8581 112 30_Re: Managing the mapping table64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Fri, 16 Mar 2001 10:13:24 -0600378_ISO-8859-1 How about option 5), use an On-Line Reorg utility that doesn't require a mapping table, that really is 100% On-line (i.e. CDB or ... or ... I guess there aren't any others)

Todd Burch CDB Software.

-----Original Message----- From: Phil.Grainger@CA.COM Sent: Friday, March 16, 2001 9:27 AM To: DB2-L@RYCI.COM Subject: Re: Managing the mapping table [...] 8694 105 20_Re: LISTCAT Question0_20_John_Lendman@FPL.COM31_Fri, 16 Mar 2001 11:37:17 -0500298_us-ascii Maybe I don't understand what you are asking. But each partition can be 1G, so if you use LISTCAT on that one tablespace, and calculate the extent size for each extent in tracks times the number of bytes per cylinder and you should be able to determine if it is close to one G limit. [...] 8800 69 45_Re: Optimize for n rows in a DRDA environment14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Fri, 16 Mar 2001 10:35:06 -0600367_iso-8859-1 John,

No, OPTIMIZE FOR x ROWs works with DRDA in a C/S environment to set the size of the blocks actually shipped with result set rows to the "client". It does not limit the result set. There are many variables, since the OPTIMIZE could ask for more data than would fit into a single DRDA block, but that is all documented well in the manuals. [...] 8870 137 20_Re: LISTCAT Question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Fri, 16 Mar 2001 12:15:01 -0500585_- No you can not determine from listcat output that the max a given dataset will grow to, is 1GB, 2GB and so on.

Sanjay Jain



> -----Original Message----- > From: John_Lendman@FPL.COM [SMTP:John_Lendman@FPL.COM] > Sent: Friday, March 16, 2001 11:37 AM > To: DB2-L@RYCI.COM > Subject: Re: LISTCAT Question > > Maybe I don't understand what you are asking. But each partition can be > 1G, > so if you use LISTCAT on that one tablespace, and calculate the extent > size > for each extent in tracks times the number of bytes per cylinder and you > should be able [...] 9008 63 16_Re: Use Of Nulls13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 16 Mar 2001 12:33:08 -0500462_iso-8859-1 Hello Don,

I don't recall any recommendation to avoid use of nulls in the past few years here.

To read your post precisely, "nulls should be avoided unless you have a specific requirement for their use." is technically a true statement. One could just as accurately say "DB2 should be avoided unless you have a specific requirement for its use" or "computers should be avoided unless you have a specific requirement for their use." [...] 9072 190 20_Re: LISTCAT Question16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Fri, 16 Mar 2001 13:03:10 -0500360_us-ascii The maximum size of a partition, for a specific partitioned tablespace, is determined by the total number of partitions (NUMPARTS) defined for said tablespace. Handy-dandy chart from the OS390 V5 SQL Reference Manual:



| | |The maximum size in gigabytes | If LARGE is: | And NUMPARTS is: |is:



| Omitted | 1 to 16 | 4 [...] 9263 17 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 16 Mar 2001 12:12:48 -0600318_US-ASCII Hello Jim, I would also be interested in a copy of the REXX. TIA Kurt

===============================================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. 9281 110 20_Re: LISTCAT Question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 16 Mar 2001 10:17:07 -0800450_iso-8859-1 All you can determine from LISTCAT output is the current allocation -- any attempt to guess at potential expansion depends on available space (among other things), which cannot be determined from LISTCAT. Assuming a PGSIZE of 4K, a single track can hold 49152 bytes, and you would need 21846 tracks to hold a gigabyte. If you have non-4K tablespaces, you'll have to use CISIZE and CI/CA in the LISTCAT output, and compute from there. [...] 9392 90 16_Re: Use Of Nulls11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 16 Mar 2001 11:29:32 -0700479_iso-8859-1 Hi, Dave (and Don),

If I read Don's post correctly, his developer isn't asking if NULLs are good or bad, it's a different users that wants them to change their database design to (from appearances) make it easier for them to access the data. Why would the developer and Don want to (possibly) completely re-design their application and database for another departments convenience? Their app might be just as they and their users want it right now........ [...] 9483 85 57_How to get rid of 'Quiesce Share' status on 2 tablespaces10_Hans Wijte22_hans.wijte@TALKLINE.NL31_Fri, 16 Mar 2001 19:40:24 +0100499_iso-8859-1 Hoi List,

due to a failed load operation on a table at a UDB V7.1 / AIX platform, two tablespaces have a 'quiesce share'-status I can't remove; resetting the status via 'quiesce tablespaces ..for .. reset' doesn't help, and neither does the suggested (in Command Reference) action to do a quiesce ...share in case of a 'phantom' connection. Every connection to these tablespaces is prohibited at the moment, so dropping the tables and recreating them is also not successfull. [...] 9569 97 61_Re: How to get rid of 'Quiesce Share' status on 2 tablespaces16_Michael McCarthy18_mmccarthy@DTCC.COM31_Fri, 16 Mar 2001 14:03:43 -0500567_us-ascii Hans,

If dropping and recreating the tablespaces is an option, you can drop both of the tablespaces in question, as long as you drop them both in the same command, i.e. db2 drop tablespace tblspace1, tblspace2 This should work, although I'm not sure if the quiesce will prevent this also. IBM has a utility that will zap the underlying file to the tablespaces, but they wash their hands of any support on the tables in them after this is done. you'll have to send them the files, and they will send them back. Is dropping them is an option, this [...] 9667 30 20_DB2 and Net.Commerce17_Marcius D. Moraes22_marcius@GOLDNET.COM.BR31_Fri, 16 Mar 2001 16:10:59 -0300588_us-ascii Hi,

At my last message, I said I have the Net.Commerce software running over a DB2 database version 5.2!

I've fixed the problem with the SQL 5043N message, but I still have a problem. Every time I start Net.Commerce, the DB2 becomes inconsistent. I took a look at the net.commerce logs, but there weren't any problem with the transactions listed. The db2diag.log file doesn't report any problem with the NC applications, too. Once I've run the db2 trace utility, however, I couldn't find any useful information, because the file is so big, and very confusing! [...] 9698 57 24_Re: DB2 and Net.Commerce14_Scott Saunders20_ssaunders@SIEBEL.COM31_Fri, 16 Mar 2001 11:30:33 -0800386_iso-8859-1 Marcius; What do you mean the 'database is inconsistent'? Are you seeing a line like this:

Database is consistent = NO

in the database configuration? This is normal when connections are open to the database. Or is there another symptom?

Regards, Scott Saunders Siebel Systems, Inc.

Opinions expressed are my own and not those of my employer. [...] 9756 25 26_Cleaning up unused columns0_22_wjmartin@HOUSEHOLD.COM31_Fri, 16 Mar 2001 14:39:47 -0500308_us-ascii One of our programmers asked if there was a way to look at all columns in a database to find if there are any that are unused - ie, space in all rows of character, 0 in all rows of decimal, etc. I think the main reason is they want to ensure I have enough to do (unload, drop, create, load) :) [...] 9782 83 17_Re: SQL CODE -44412_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 16 Mar 2001 10:59:04 -0800587_us-ascii And let's not forget our friend PDS (from the CBT share tape, I believe) that does this online. It will even extend the PDS directory blocks! It's a good idea to overallocate the SP load library and compress it on a regular basis. Even if you aren't adding new members, just updating old ones will tend to throw the library into secondary extents. Some folks have allocated the library, not allowing secondary extents so that the compiles into that library would fail, which the average programmers seemed to understand easier than the -444. Not my recommendation, though. [...] 9866 17 67_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Fri, 16 Mar 2001 14:51:54 -0500451_- Hi Jim, I would also be interested in a copy of the REXX. Thanks in Advance _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9884 70 30_Re: Cleaning up unused columns11_Paul Murphy22_Paul_Murphy@MAY-CO.COM31_Fri, 16 Mar 2001 14:06:39 -0600335_us-ascii One of the easiest ways is to run a REORGCHK against the tables. Make sure your statisitics are up to date. The column labeled CARD indicates the cardinality (number of rows) in the table.

Another option is to select CARD from SYSCAT.TABLES or SYSTABLES for each table in question.



Hope this helps! [...] 9955 63 61_Re: How to get rid of 'Quiesce Share' status on 2 tablespaces0_24_William_O'Black@FFIC.COM31_Fri, 16 Mar 2001 12:07:59 -0800392_iso-8859-1 Hans, I had that problem several times. The way I solved it was to do another load with a null input file (or an input file with only a couple of records). It completed normally and then the tablespace was freed up.

Bill O'Black Fireman's Fund Insurance Co.









Hans Wijte @RYCI.COM> on 03/16/2001 10:40:24 AM [...] 10019 46 46_Re: inactive (distributed) thread holding lock13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Fri, 16 Mar 2001 14:17:40 -0600680_iso-8859-1 This is the same thing we experienced.

-----Original Message----- From: Craig McKellar [mailto:craig.mckellar@BIGPOND.COM] Sent: Thursday, March 15, 2001 6:32 PM To: DB2-L@RYCI.COM Subject: Re: inactive (distributed) thread holding lock



We have had similar problems and here is what we found in our sutuation hoping it is helpful. We had distributed threads inactive in wait-remreq holding locks. In some case they were X locks. What was happening is that the user was writing application using visual age for java which has a workbench component. A connection was made to DB2 holding a thread from the workbench. When an application was run [...] 10066 57 46_Re: inactive (distributed) thread holding lock13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 16 Mar 2001 14:24:19 -0600424_iso-8859-1 Craig and Chad,

Thanks for the responses. We did get some doc and IBM is evaluating it at this time. I've forwarded on the PTF you flagged (Chad)to our systems folks.

The lock in question was on a user tablespace (I don't know the type of lock right off hand).

The idle thread time-out would not help for this situation, as the thread was INACTIVE (not to be confused with IDLE ACTIVE). [...] 10124 77 64_FW:Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index12_Henry Daries23_Henry.Daries@BCBSKS.COM31_Fri, 16 Mar 2001 14:20:00 -0600453_- Please add me to the list of those wanting a copy of the REXX program. Thanks so much! ------------------( Forwarded letter 1 follows )-------------------- Date: Fri, 16 Mar 2001 08:57:15 -0500 To: DB2-L@RYCI.COM From: Roger.Hecq@msdw.com Sender: owner-db2-l@RYCI.COM Reply-To: Roger.Hecq@msdw.com Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index

If this REXX is still available, I would like to obtain a copy of it. [...] 10202 90 46_Re: inactive (distributed) thread holding lock0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 16 Mar 2001 14:52:08 -0600449_us-ascii Will a commit or the IDTHOIN zparm release the JAVA connection???

We have run into a similar, but may slightly different, problem. One of our tech support groups is evaluating Java & Websphere. Java has a lock on the DB (resource type = 100) so that we can't do any DB, TS, TB changes on that development environment while java is running. If it goes into production configured like that, I for one, am going to really hate it. [...] 10293 105 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 16 Mar 2001 13:07:10 -0800474_iso-8859-1 I searched the archives, this thread is actually very old. If anyone has the REXX exec and/or Chuck Hoover's presentation, please send them to me and I'll upload them to the DB2-L document archive. Thanks.

Cathy.Taddei@pacificorp.com

-----Original Message----- From: Henry Daries [mailto:Henry.Daries@BCBSKS.COM] Sent: Friday, March 16, 2001 12:20 PM To: DB2-L@RYCI.COM Subject: FW:Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index [...] 10399 107 61_Re: How to get rid of 'Quiesce Share' status on 2 tablespaces0_22_BILL_GALLAGHER@PHL.COM31_Fri, 16 Mar 2001 16:10:07 -0500518_iso-8859-1 Hans,

In a phantom quiesce situation, the quiesce will become owned by the next connection that issues the "QUIESCE TABLESPACES . . ." command. You should be able to issue the "QUIESCE TABLESPACES . . . SHARE" command to take ownership of the quiesce, and then issue the "QUIESCE TABLESPACES . . . RESET" command to remove the tablespaces in question from quiesce state. It's not clear from your original note if you are doing the "QUIESCE . . . RESET" after reissuing the QUIESCE . . . SHARE". [...] 10507 142 46_Re: inactive (distributed) thread holding lock12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 16 Mar 2001 13:11:33 -0800381_us-ascii Glad to see more than one person is having fun with JAVA and Websphere not releasing locks. We've found that with Websphere Connection Pooling, besides the locks not being released the connection isn't even terminated. Plus we can't do any Builds of Stored procedures due to locks on plan DSNRRSAF. Its a major issue and we're working with IBM to resolve this issue. [...] 10650 97 60_Re: FW:Re: DB2-OS/390- Statistical Analysis of Tablespaces a17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Fri, 16 Mar 2001 14:10:54 -0700745_US-ASCII Hi Jim,

Please add me to the list for a copy of the REXX program. Thanks a lot.



______________________________ Reply Separator _________________________________ Subject: FW:Re: DB2-OS/390- Statistical Analysis of Tablespaces and I Author: DB2 Data Base Discussion List at INTERNET-MAIL Date: 3/16/01 2:20 PM



Please add me to the list of those wanting a copy of the REXX program. Thanks so much! ------------------( Forwarded letter 1 follows )-------------------- Date: Fri, 16 Mar 2001 08:57:15 -0500 To: DB2-L@RYCI.COM From: Roger.Hecq@msdw.com Sender: owner-db2-l@RYCI.COM Reply-To: Roger.Hecq@msdw.com Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index [...] 10748 119 61_Re: How to get rid of 'Quiesce Share' status on 2 tablespaces11_RICHARD YAN23_DHSPA3F@DHS.STATE.IL.US31_Fri, 16 Mar 2001 15:31:26 -0600372_US-ASCII Hans,

Several things you might want to try, but I can't guarantee it would help. 1. Try loading the table with option "replace" and create an empty file for load input. 2. If #1 doesn't work, quiesce tablespace for exclusive. 3. Then quiesce tablespace for share or reset.

If still doesn't work, call IBM and ask them to redesign LOAD utility. [...] 10868 68 37_DB2, Net.data and web server question0_14_mtdage@ATT.NET31_Fri, 16 Mar 2001 21:33:06 +0000464_- Our environment is similar to yours relative to Net.data, Websphere, and USS(Unix System Services formerly known as Open Edition). We have WEBS LPAR equivalent to your LPAR B and all the databases we access are found in ASYS LPAR. You need a local DB2 on your LPAR B where you define all the communication database(DRDA connections) that determines which DB2 subsystem you wnat to connect to on another LPAR. No DB2 Connect, or Shadow Direct, etc.. needed. [...] 10937 139 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 16 Mar 2001 15:33:27 -0600382_us-ascii Flow chart for ur requirement : 1) Set Chuck_found_flag to false 1) Search & locate chuck in the list 2) If found ( set Chuck_found_flag to true ) else keep doing search chuck over the net until you found set Chuck_found_flag to true 3) If Chuck_found_flag then request presentation from chuck If Chuck_agrees then Chuck updates list archives else sorry endif end_if. [...] 11077 162 46_Re: inactive (distributed) thread holding lock13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 16 Mar 2001 15:46:40 -0600500_iso-8859-1 Myron,

This is interesting. In our case, it is the Java "fat clients" (appl code on each workstation) that are the offenders. When these apps have migrated to Websphere and Websphere connection pooling, they behave properly (active thread ==> commit ==> thread goes inactive (no locks)).

I haven't spent the time (yet) to figure out why this is so. I can speculate that Websphere introduces a more structured unit of work model (transaction management) to the equation. [...] 11240 108 46_Re: inactive (distributed) thread holding lock13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 16 Mar 2001 15:34:55 -0600318_iso-8859-1 Rohn,

Yes and yes. A COMMIT allows the ACTIVE thread to go INACTIVE by releasing the locks. To be a candidate to become an INACTIVE thread, it must hold no locks (thus our concern in the case I'm describing, where an INACTIVE thread is holding locks...., we're hoping it must be a fixable bug). [...] 11349 40 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspac es14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 16 Mar 2001 15:14:03 -0700771_- As would I please.



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



> ---------- > From: Carlton Enuda[SMTP:carltonenuda@HOTMAIL.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, March 16, 2001 12:51 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Indexspaces > > Hi Jim, > I would also be interested in a copy of the REXX. > Thanks in Advance > _________________________________________________________________ > Get your FREE download of MSN Explorer at http://explorer.msn.com > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. [...] 11390 133 46_Re: inactive (distributed) thread holding lock0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 16 Mar 2001 16:29:51 -0600470_us-ascii Thanks for the info Dave.

But I don't think this answer helps our problem. From talking to the testers, it appears our problem is not the table/index locks for individual threads held by transactions. Rather it appears to be the pooled connection held websphere, whose intent is to cut down on connection overhead. I'm looking for the parameter that will tell websphere the pooled connection has been idle too long, so it is OK to let go of the DB. [...] 11524 89 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 16 Mar 2001 15:36:25 -0700417_iso-8859-1 I'd like a copy too, please

Robert Jans Albertsons, Inc.



-----Original Message----- From: Roger Hecq [mailto:Roger.Hecq@msdw.com] Sent: Friday, March 16, 2001 6:57 AM To: DB2-L@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spaces.



If this REXX is still available, I would like to obtain a copy of it. [...] 11614 63 30_Re: Cleaning up unused columns18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Sat, 17 Mar 2001 13:45:44 +0930341_us-ascii Howzit WJ Martin?

The way I'd do it is to do runstats for all tables in all tablespaces and then query the system catalog. If you don't want to update your catalog stats, use the report yes update none options.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia [...] 11678 46 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.11_Richard Tan19_richtan@AU1.IBM.COM31_Sat, 17 Mar 2001 16:01:08 +1000533_us-ascii Jim,

Add me to the list please.

Thanks Richard Tan







Kurt Sahlberg @RYCI.COM> on 17/03/2001 04:12:48 am

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.





Hello Jim, I would also be interested in a copy of the REXX. TIA Kurt [...] 11725 134 30_Re: Managing the mapping table12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 17 Mar 2001 13:37:41 +0200392_iso-8859-1 Hi, We have a fixed set (8 map tables). All reorg runs are managed via a scheduler (control-m in our case). What matters is the index on the mapping tables and not the TS, so there's only one segmented TS with priqty 720 secqty 0 and the indexes - one per table are 720 / xxxxxx - pick your choice). Why not 3rd party product ? - Budget (or you may call it price/performance) [...] 11860 49 31_Re: Stored Procedure/CICS Paper12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 17 Mar 2001 14:04:08 +0200687_iso-8859-1 Hi, Go to http://www.db2mag.com/ and search for CICS ...







Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com ----- Original Message ----- From: "Paul S Rockwood" Newsgroups: bit.listserv.db2-l To: Sent: Friday, March 16, 2001 12:30 AM Subject: Stored Procedure/CICS Paper



> A customer suggested that we look at a paper by Chris Date and Geoff > Sharman regarding CICS vs stored procedures. They said it was very good > and focused on scalability issues. I'm hoping someone will be able to > point to a link for the paper, or know someone who can. > > > Paul S. [...] 11910 191 53_Re: Reorg at partition level with NPIs: Clarification12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 17 Mar 2001 13:43:06 +0200291_iso-8859-1 Hi, For "small" partitioined TS we do it with the NPIs, for the big ones - we drop the NPIs and recreate (this really hurts as IBM has not given yet the online index creation). In one case we even reverted to having the NPI as separate tables, but this is a different story. [...] 12102 38 47_CPU/Elapsed time to move data to host variables9_Ken Paris17_KenParis@LVCM.COM31_Sat, 17 Mar 2001 06:37:42 -0800448_iso-8859-1 We've been running into unexpectedly long elapsed times and high CPU times. We've identified one of the issues as the number of columns selected (we know it's good to select only the columns you need but are surprised at the amount of elapsed time and CPU time this takes).

We created a little test program which declares a cursor with no WHERE clause (resulting in the expected access type 'R' with sequential prefetch on). [...] 12141 178 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Sat, 17 Mar 2001 16:48:29 +0100299_us-ascii Hello Rick,

no, generally our partitioned TSs are handled in a manner of "one partition per day/week/month", so there is no need to REORG the full TS all at once. What's more, usually they are maintained by LOADs in the proper sequence, so most of them don't need REORG at all. [...] 12320 85 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.11_Judy Kibler21_judith.kibler@TRW.COM31_Sat, 17 Mar 2001 09:59:59 -0600757_us-ascii Jim,

Please also add me to the list!

Thanks, Judy Kibler









Richard Tan on 03/17/2001 12:01:08 AM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Judy Kibler/Nashville)





Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.

















Jim,

Add me to the list please.

Thanks Richard Tan







Kurt Sahlberg @RYCI.COM> on 17/03/2001 04:12:48 am

Please respond to DB2 Data Base Discussion List [...] 12406 20 27_How to trace DB/2 commands.17_Massimo Biancucci19_mbiancucci@DEBIS.IT31_Sat, 17 Mar 2001 17:04:03 +0100453_us-ascii I need to trace all the -STO, -STA, -TERM commands given to a DB/2.

I looked for a IFCID .... but I wasn't able to find it !

Every help will be appreciated.

Best regards.

Max

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 12427 38 30_Moving Database from NT to AIX11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID29_Sat, 17 Mar 2001 17:01:03 GMT373_ISO-8859-1 Dear all,

We are facing a problem in one of our shops. They have a new AIX box, but their old DB2 are still in NT box. How to move the current NT DB2 into AIX DB2? We've tried to copy the backup from NT to an AIX directory, and create db from backup using the backup. But because the backup in AIX is in different format, we cannot create it on AIX. [...] 12466 127 41_Re: Forte Applications and DB2 UDB Tuning11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sat, 17 Mar 2001 12:16:11 -0500599_us-ascii Stop the finger pointing, or at least point with accuracy.

Guaranteed Breakthrough Results for DB2 $5,000 Reward / Lifetime of Satisfaction

Forte, Siebel, Peoplesoft, i2, S1, SAP, Net.Commerce, Net.Data, and many others use dynamic SQL. As data statistics change, the DB2 optimizer is free to change its mind about access path selection. So, just as quickly as you solve today's performance problem, a new performance challenge lurks days away. Since your business depends on these applications, you've got about 2 minutes to solve any performance problem. Good as done. [...] 12594 26 38_sort dups from load input file -- help15_Amy C Whitehead14_B06ACW@FDS.COM31_Sat, 17 Mar 2001 12:24:18 -0500321_us-ascii Hi all, I am in a crunch. I need to sort dups from a load file,but keep 1 to load. I have done this in the past, but it has been too long. Does anyone have JCL to share for this?

Thanks, Amy







Amy C. Whitehead Federated Systems Group Database Architecture (678) 474-3418 [...] 12621 89 34_Re: Moving Database from NT to AIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sat, 17 Mar 2001 13:43:13 -0500745_us-ascii Hi Endy,

Switching operating systems is not allowed with DB2 backup and restore.

To move your database from DB2/NT to DB2/AIX, IBM supplies a very handy utility called "db2move" (see below for details).

Once you get your DB2/AIX database built, contact us for performance tools. :)

Cheers, Scott

http://www.database-guys.com/results/

/(dbmsi1)--> db2move ** ** DB2MOVE ** ** Please read the DB2 Administration Guide for more info! ** ** Usage: "db2move dbname action [options]" ** ** - Action: must be EXPORT, IMPORT, or LOAD. ** - Options: If not specified, defaults will be used. ** ** Option Default Notes ** ========================================================================= ** [...] 12711 187 46_Re: inactive (distributed) thread holding lock12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 17 Mar 2001 13:29:57 -0800548_us-ascii Rohn, There is a timeout parameter in Websphere that tells it to release the thread. I'm not sure at the moment of the exact parm but it allows you tell Websphere how long to let the thread sit idle - a lot like the IDTHOIN parm in DB2.

Myron --- Rohn.Solecki@MTS.MB.CA wrote: > Thanks for the info Dave. > > But I don't think this answer helps our problem. From talking to the > testers, it appears our problem is not the table/index locks for > individual > threads held by transactions. Rather it appears to be the pooled > [...] 12899 205 46_Re: inactive (distributed) thread holding lock12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 17 Mar 2001 13:43:07 -0800613_us-ascii On your websphere Java code, are they standard Java apps or are they EJBs? I suspect that they are standard Java apps being managed by Websphere. Our problem is in Bean managed EJBs. Websphere doesn't seem yet to be cleaning up connections properly. --- "Shapiro, Dave" wrote: > Myron, > > This is interesting. In our case, it is the Java "fat clients" (appl > code on each workstation) that are the offenders. When these apps > have migrated to Websphere and Websphere connection pooling, they > behave properly (active thread ==> commit ==> thread goes inactive > (no [...] 13105 52 42_Re: sort dups from load input file -- help12_Mark Granger22_the_grange@MBOX.COM.AU31_Sun, 18 Mar 2001 08:42:24 +1100461_us-ascii Amy, SUM FIELDS=NONE in the sort options will remove duplicates and leave the first one (I think).

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



----- Original Message ----- From: Amy C Whitehead Date: Sunday, March 18, 2001 4:24 am Subject: sort dups from load input file -- help [...] 13158 109 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.15_Kannan, Perumal22_perumal.kannan@EDS.COM31_Sun, 18 Mar 2001 02:24:32 -0600657_- Please add my name to the list..Thanks.

Thanks & Regards, Kannan P.



> -----Original Message----- > From: Robert Jans [SMTP:robert_jans@ALBERTSONS.COM] > Sent: Saturday, March 17, 2001 4:06 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > I'd like a copy too, please > > Robert Jans > Albertsons, Inc. > > > > -----Original Message----- > From: Roger Hecq [mailto:Roger.Hecq@msdw.com] > Sent: Friday, March 16, 2001 6:57 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index > spaces. > > > If [...] 13268 98 73_Re: Fetching first n records of large table (limited predicate filtering)17_Miguel de Andrade29_miguel.andrade@BTINTERNET.COM31_Sun, 18 Mar 2001 04:29:50 -0600436_- Hi Phil, just from the top of my mind...

wouldn't a descending index help on TRANSACTION_TIMESTAMP ?

As for the increment something like this : where :increment > (Select count(*) from table b where a.TRANSACTION_TIMESTAMP < b.TRANSACTION_TIMESTAMP )



Just an idea...

Best Regards, Miguel de Andrade

On Thu, 15 Mar 2001 08:01:45 -0600, Philip Nelson (DBA) wrote: [...] 13367 30 38_sort dups from load input file -- help13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM31_Sun, 18 Mar 2001 09:57:07 -0500564_us-ascii The Jcl could be like this :

//SORT01 EXEC PGM=SORT, // REGION=1024K //SYSOUT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SORTIN DD DISP=SHR,DSN=TMVS860.RCVPOL.QA1.SEQ

//SORTOUT DD DISP=SHR,DSN=TMVS860.RCVPOL.QA1.SORTOUT.SEQ //SYSIN DD * SORT FIELDS=(3,9,FL,A,12,4,BI,A,17,4,BI,A),EQUALS SUM FIELDS=NONE

//*THE 'SORT FIELDS' GIVES THE FIELDS TO BE SORTED , THE CONVENTION IS: //* START POS , LENGTH , DATA TYPE , ASC OR DESC , ... //*THE 'EQUALS' PARAMETER PRESERVES THE ORDERING OF ROWS //* THE 'SUM FILELDS' ELIMINATES THE DUPLICATES [...] 13398 28 58_Win32: Access to DB2 Stored Procedures from Visual Basic 613_Boris Cinkler23_boris_cinkler@YAHOO.COM31_Sun, 18 Mar 2001 11:49:14 -0400456_iso-8859-1 One beginers question:

How I can execute SP via ADO and OLE DB, with ADODB.command object? SP can be executed from Command Center, but from VB I get Following error: [IBM][CLI Driver][DB2/NT] SQL1109N The specified DLL "ProcJava1" could not be loaded.

Thanks

Boris Cinkler



_________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com [...] 13427 128 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.14_Scott Saunders20_ssaunders@SIEBEL.COM31_Sun, 18 Mar 2001 10:25:40 -0800741_iso-8859-1 Please add me as well.

Scott Saunders Siebel Systems, Inc.

-----Original Message----- From: Kannan, Perumal [mailto:perumal.kannan@EDS.COM] Sent: Sunday, March 18, 2001 12:25 AM To: DB2-L@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.



Please add my name to the list..Thanks.

Thanks & Regards, Kannan P.



> -----Original Message----- > From: Robert Jans [SMTP:robert_jans@ALBERTSONS.COM] > Sent: Saturday, March 17, 2001 4:06 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > I'd like a copy too, please > > Robert Jans > Albertsons, Inc. > > [...] 13556 70 44_DB2 Performance Journal - Single Issues Sale12_Susan Lawson24_susan_lawson@YLASSOC.COM31_Sat, 17 Mar 2001 14:36:16 -0600340_iso-8859-1 Dear DB2 Professional,

The YL&A DB2 Performance Journal(DPJ) can now be purchased by single issue in addition to the 1 year subscription offer. Each DPJ contains 5-7 in-depth white papers written by some of the most notable authors in the industry dealing with the hottest topics in DB2 performance on all platforms. [...] 13627 62 36_Re: DB2 and the Java Virtual Machine14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 09:25:19 +1100645_iso-8859-1 another alternative is the newsgroup comp.databases.ibm-db2

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: Thomas E. Faglon [mailto:tfaglon@TELCORDIA.COM] Sent: Friday, 16 March 2001 23:49 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 and the Java Virtual Machine



DB2EUG DIGEST http://people.mn.mediaone.net/scottrmcleod/ For subscription info see bottom of message

To receive messages separately, send email to db2eug-request@lists.best.com containing the line: subsingle To unsubscribe, send: unsubscribe [...] 13690 33 62_Re: DB2-OS/390- Statistical Analysis of Tablespaces and I ndex14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 09:33:40 +1100544_iso-8859-1 The presentation, as with other Chuck Hoover presentations, is available at http://www.compuware.com/products/hoover/index.asp. As you have to register (free) to get to them, I doubt that Compuware would appreciate them being re-hosted.

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: Taddei, Cathy [mailto:Cathy.Taddei@PACIFICORP.COM] Sent: Saturday, 17 March 2001 8:07 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2-OS/390- Statistical Analysis of Tablespaces and Index [...] 13724 41 31_Re: How to trace DB/2 commands.14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 09:52:26 +1100427_iso-8859-1 1) ifcid 0004 records -STA TRACE 2) ifcid 0005 records -STO TRACE 3) ifcid 0090 records the text of a command 4) ifcid 0091 records the completion of a command

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: Massimo Biancucci [mailto:mbiancucci@DEBIS.IT] Sent: Sunday, 18 March 2001 3:04 To: DB2-L@RYCI.COM Subject: [DB2-L] How to trace DB/2 commands. [...] 13766 28 62_Question regarding IGGCSI00 (Catalog search interface Program)12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Sun, 18 Mar 2001 17:15:47 -0600568_- Hi folks:

I heard that using the IGGCSI00 program you can get plenty of information from the Catalog such as Volume , HURBADS and HARBADS in order to estimate space allocation and that sort of stuff but I'm trying to change the Rexx program that comes in the sys1.samplib(IGGCSIRX) to invoke this program and get the information what I want but is not working at all. Has anyone used this program with this purpose?. Could you please provide me with a copy of this rexx program getting HURBADS and HARBADS from the MVS catalog. Thanks a lot and regards, [...] 13795 91 20_Re: LISTCAT Question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 10:25:49 +1100605_iso-8859-1 As far as VSAM is concerned, a (non extended) cluster can grow to 4Gb. Doesn't matter whether the tablespace is partitioned (and how many partitions it has) or not. DSN1COPY could copy 4Gb into any DB2 tablespace cluster (I hope that's true, I've never tried it).

However, DB2 uses low level exits into VSAM Media Manager to control all allocations. This is what: - stops cluster growing beyond their appropriate size - ensures that the initial allocation on a new volume uses a DB2 secondary allocation size, not the primary size. - (if you are using STOGROUP volume lists) issues [...] 13887 100 17_Re: SQL CODE -44414_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 10:48:56 +1100650_iso-8859-1 Or, you could use a PDSE for the load library - and never compress again.

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: tim malamphy [mailto:timalamphy@YAHOO.COM] Sent: Saturday, 17 March 2001 5:59 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] SQL CODE -444



And let's not forget our friend PDS (from the CBT share tape, I believe) that does this online. It will even extend the PDS directory blocks! It's a good idea to overallocate the SP load library and compress it on a regular basis. Even if you aren't adding new members, just updating old ones will tend to [...] 13988 50 66_Re: Question regarding IGGCSI00 (Catalog search interface Program)14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 19 Mar 2001 11:22:26 +1100519_iso-8859-1 1) personally, I'ld use XHARBADS and XHURBADS to cater for extended clusters over 4Gb.

2) send me a copy of your program, and I'll have a look at it. (Don't use Reply - send it directly to james.campbell@hancorp.com.au)

/* standard disclaimer */ James Campbell Hansen Corporation -----Original Message----- From: HARBRY ARIZA [mailto:harbry_a@HOTMAIL.COM] Sent: Monday, 19 March 2001 10:16 To: DB2-L@RYCI.COM Subject: [DB2-L] Question regarding IGGCSI00 (Catalog search interface Program) [...] 14039 94 51_Re: CPU/Elapsed time to move data to host variables13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 18 Mar 2001 23:57:31 -0600327_iso-8859-1 Ken,

It's not unreasonable to expect the overhead you are seeing. I did a quick estimate of the number of CPU instructions required comparing passing 40 or 10 columns (using Frank Ingrassia's Optimizer formulas). I ignored the I/O cost (and CPU to support the I/O), since it should be the same for both. [...] 14134 66 69_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspac es.11_Iztok Jeras27_Iztok.Jeras@OMVISTRABENZ.SI31_Mon, 19 Mar 2001 08:26:49 +0100634_- Interested for the REXX too,

TIA

Iztok JERAS



> -----Original Message----- > From: Richard Tan [SMTP:richtan@AU1.IBM.COM] > Sent: Saturday, March 17, 2001 7:01 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Indexspaces. > > Jim, > > Add me to the list please. > > Thanks > Richard Tan > > > > > Kurt Sahlberg @RYCI.COM> on 17/03/2001 > 04:12:48 > am > > Please respond to DB2 Data Base Discussion List > > Sent by: DB2 Data Base Discussion List > > > To: DB2-L@RYCI.COM > cc: > Subject: Re: [...] 14201 29 31_Re: How to trace DB/2 commands.10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Mar 2001 09:01:27 +0100415_us-ascii Hi Max, howizit ? Take a look at:

xxxxx.SDSNSAMP(DSNWEIDS) -----> DB2 global trace description



xxxxx.SDSNSAMP(DSNWMSGS) -----> IFCID description

IFCID 90,91 seems to be useful. Beware, they belong to PERFORMANCE trace, but for commands IBM says it's not too

expensive in terms of CPU usage.

May be Mauro has some REXX to start trace and to read IFI buffers. [...] 14231 77 42_500+ links about DB2 for OS/390 (03/12/01)6_DB2usa18_db2usa@HOTMAIL.COM31_Mon, 19 Mar 2001 01:41:40 -0600538_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.db2usa.com - Français: http://www.db2france.com

Last update on Monday, March 12th 2001 ======================================

* Links about DB2: http://db2usa.hypermart.net/eliendb2.htm IBM just announced Planned Availability Date of IBM UDB Server for OS/390 and z/OS, Version 7.

Take a look at Version 7 page on IBM's website. Version 7 documentation is already available on IBM's website. [...] 14309 58 66_Re: Question regarding IGGCSI00 (Catalog search interface Program)0_18_mebert@AMADEUS.NET31_Mon, 19 Mar 2001 09:23:17 +0100496_us-ascii Hi Harbry,

I have placed a sample program (REXX to call IGGCSIRX and SAS to extract the info) in the DB2-L-ARCHIVES on 6.6.2000. I too found out that the IBM-supplied sample program has a bug but didn't bother to find out what it was. One word of caution. I found out that very occasionally, the CSI returns with RC=0 and Resume Flag=' ' (no more data) but actually returns only a small part of the data. If you rerun, it's ok. I suspect it's a matter of catalog contention. [...] 14368 42 42_Re: sort dups from load input file -- help10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Mar 2001 09:35:58 +0100516_us-ascii Hi Amy, below a sample I use in a JCL to eliminate duplicate database names:



//IFSORT IF RC NE 69 THEN //SORTX EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(2,5),RLSE) //SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(2,5),RLSE) //SORTIN DD DSN=SAS1.PR.UCERS101.DB2CONT2,DISP=SHR //SORTOUT DD DSN=SAS1.PR.UCERS101.DB2CONTR,DISP=(NEW,CATLG), // UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE), // DCB=(LRECL=80,RECFM=FB) //SYSIN DD * SORT FIELDS=(1,19,CH,A) SUM FIELDS=NONE END /* // ELSE [...] 14411 15 52_Cathy L Peck/ISG/CORP/Highmark is out of the office.10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Mon, 19 Mar 2001 07:01:05 -0500389_us-ascii I will be out of the office starting 03/19/2001 and will not return until 03/23/2001.

I will respond to your message when I return.

================================================ 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. 14427 85 69_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces13_Brunner Don J21_Don.J.Brunner@IRS.GOV31_Mon, 19 Mar 2001 07:35:55 -0500406_iso-8859-1

Jim,

I'd like a copy also.

Thanks

-----Original Message----- From: Robert Jans [mailto:robert_jans@ALBERTSONS.COM] Sent: Friday, March 16, 2001 5:36 PM To: DB2-L@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.



I'd like a copy too, please

Robert Jans Albertsons, Inc.

14513 113 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index13_Steven Kinler17_SKinler@VNSNY.ORG31_Mon, 19 Mar 2001 07:54:39 -0500679_- Please add me to the list of those wanting a copy of the REXX program. Thanks you

-----Original Message----- From: Henry Daries [SMTP:Henry.Daries@BCBSKS.COM] Sent: Friday, March 16, 2001 3:20 PM To: DB2-L@RYCI.COM Subject: FW:Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index

Please add me to the list of those wanting a copy of the REXX program. Thanks so much! ------------------( Forwarded letter 1 follows )-------------------- Date: Fri, 16 Mar 2001 08:57:15 -0500 To: DB2-L@RYCI.COM From: Roger.Hecq@msdw.com Sender: owner-db2-l@RYCI.COM Reply-To: Roger.Hecq@msdw.com Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index [...] 14627 50 42_Re: sort dups from load input file -- help14_Kurt Wickhorst28_Kurt.Wickhorst@METAVANTE.COM31_Mon, 19 Mar 2001 07:16:45 -0600594_us-ascii AMY

The SORT FIELDS = NONE PARAMATER WILL ACCOMPLISH THIS







Amy C Whitehead @RYCI.COM> on 03/17/2001 11:24:18 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: sort dups from load input file -- help



Hi all, I am in a crunch. I need to sort dups from a load file,but keep 1 to load. I have done this in the past, but it has been too long. Does anyone have JCL to share for this? [...] 14678 130 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index14_Joey Advincula29_joey_advincula@MAINFRAMER.NET31_Mon, 19 Mar 2001 05:23:59 -0800622_- please send me a copy too...

thanks

--- Steven Kinler > wrote: > Please add me to the list of those wanting a copy of the REXX >program. > Thanks you > > -----Original Message----- > From: Henry Daries [SMTP:Henry.Daries@BCBSKS.COM] > Sent: Friday, March 16, 2001 3:20 PM > To: DB2-L@RYCI.COM > Subject: FW:Re: DB2-OS/390- Statistical Analysis of >Tablespaces and Index > > Please add me to the list of those wanting a copy of the REXX >program. Thanks > so much! > ------------------( Forwarded letter 1 follows )-------------------- > Date: Fri, 16 Mar 2001 08:57:15 -0500 > To: [...] 14809 209 53_Re: Reorg at partition level with NPIs: Clarification19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Mon, 19 Mar 2001 07:39:51 -0600634_iso-8859-1 Hi Dr. Ebert, sounds like good planning paid off! Thanks.

Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 15019 16 50_Rule:Re[2]: sort dups from load input file -- help11_Ken Liberty29_Ken_Liberty@CONSULTEC-INC.COM31_Mon, 19 Mar 2001 08:40:35 -0500450_us-ascii I will be Out of the Office until Friday, March 16th for DBA Support and DMEM Processing please contact Y. Hamilton (X7282). I will be returning to the office on Monday, March 19th.

Thanks Ken

================================================ 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. 15036 78 36_Re: DB2 and the Java Virtual Machine19_Papazissimos, James32_James.Papazissimos@USA.XEROX.COM31_Mon, 19 Mar 2001 08:55:40 -0500324_ISO-8859-1 The problem is that IBM ships it's "enhanced" version of Java with DB2, and the control center will not run with any other Java version. My bet is that you have another version of Java installed higher in the PATH concatenation sequence.

It took a while and a lot of swearing to figure that out, too. [...] 15115 154 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index15_Zimmerman Terry35_Terry.Zimmerman@GENERALCASUALTY.COM31_Mon, 19 Mar 2001 07:58:57 -0600723_iso-8859-1 Please send me a copy also.

Thanks

-----Original Message----- From: Joey Advincula [mailto:joey_advincula@MAINFRAMER.NET] Sent: Monday, March 19, 2001 7:24 AM To: DB2-L@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index



please send me a copy too...

thanks

--- Steven Kinler > wrote: > Please add me to the list of those wanting a copy of the REXX >program. > Thanks you > > -----Original Message----- > From: Henry Daries [SMTP:Henry.Daries@BCBSKS.COM] > Sent: Friday, March 16, 2001 3:20 PM > To: DB2-L@RYCI.COM > Subject: FW:Re: DB2-OS/390- Statistical Analysis of >Tablespaces and Index > > Please add me to the [...] 15270 15 57_DB2-OS/390- Statistical Analysis of Tablespaces and Index0_14_mtdage@ATT.NET31_Mon, 19 Mar 2001 14:22:47 +0000334_- Hi Jim,

I am also interested in getting a copy of this REXX.

Thanks, 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. 15286 138 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index14_Dean Montevago18_DMonteva@VNSNY.ORG31_Mon, 19 Mar 2001 09:23:43 -0500601_iso-8859-1 I would like a copy to please Thanks Dean

> -----Original Message----- > From: Steven Kinler > Sent: Monday, March 19, 2001 7:55 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index > > Please add me to the list of those wanting a copy of the REXX > program. > Thanks you > > -----Original Message----- > From: Henry Daries [SMTP:Henry.Daries@BCBSKS.COM] > Sent: Friday, March 16, 2001 3:20 PM > To: DB2-L@RYCI.COM > Subject: FW:Re: DB2-OS/390- Statistical Analysis of > Tablespaces and Index > > Please add me to the list of those [...] 15425 99 36_Re: DB2 and the Java Virtual Machine12_Travis, John27_John.Travis@CAPGEMINI.CO.UK31_Mon, 19 Mar 2001 14:31:40 -0000322_- Jim, we also found this problem which in our case was caused by the 'Oracle' client also putting on its own "enhanced" version. There was nothing for it but to decide which machine got DB2 and which got Oracle as the two would not co-operate.

> Regards > John Travis > > E-Mail : John.Travis@capgemini.co.uk [...] 15525 19 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT31_Mon, 19 Mar 2001 15:44:47 +0100370_iso-8859-1 I would like to receive a Copy TIA

Bruno Lucchetti Barilla Alimentare S.p.A. Via Mantova 166 43100 Parma Italy

================================================ 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. 15545 74 42_Re: sort dups from load input file -- help12_Deluca, Fred27_Fred.Deluca@CCRA-ADRC.GC.CA31_Mon, 19 Mar 2001 09:54:32 -0500473_iso-8859-1 I think you mean SUM FIELDS = NONE.

//SYSIN DD * SORT FIELDS=(1,4096,BI,A),EQUALS SUM FIELDS=NONE

Thanks, Fred De Luca DB2 Specialist Fred.Deluca@ccra-adrc.gc.ca



-----Original Message----- From: Kurt Wickhorst [mailto:Kurt.Wickhorst@METAVANTE.COM] Sent: March 19, 2001 8:17 AM To: DB2-L@RYCI.COM Subject: Re: sort dups from load input file -- help



AMY

The SORT FIELDS = NONE PARAMATER WILL ACCOMPLISH THIS [...] 15620 42 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.5_Chris17_isatcjk@YAHOO.COM31_Mon, 19 Mar 2001 06:59:21 -0800334_us-ascii I'd be interested in the REXX also.

Maybe I could post this on my web page?

I looked all over for something that would do what Mr. Hoover's book says...that being, a the objects which had the most re-reads from disk during a five minute period.

On another topic, what does everyone think of this... [...] 15663 96 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.13_Walter Wojcik19_walter@ABINITIO.COM31_Mon, 19 Mar 2001 09:54:34 -0500720_us-ascii Please add me to the list.

Regards, Walter Wojcik walter.wojcik@abinitio.com (781) 301-2000







"Kannan, Perumal" Sent by: DB2 Data Base Discussion List 03/18/2001 03:24 AM Please respond to DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces. > > >From: Jim Lewandowski > > >Reply-To: DB2 Data Base Discussion List > > >To: DB2-L@RYCI.COM > > >Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index > spa > > > ces. > > >Date: Tue, 11 Jan 2000 10:09:00 -0600 > > > > > >I had [...] 15760 65 69_Re: DB2-OS/390- Statistical Analysis of Tablespaces and I ndexspaces.0_19_mike.holmans@BT.COM31_Mon, 19 Mar 2001 15:13:12 -0000308_- The re-read ratio is a statistic which was developed by Mike Barnard, then of CGU Life. He gave a presentation on it at IDUG Europe 1999, which won the Best User Speaker prize, so he delivered it again at IDUG North America last year. Looking through the Proceedings for those events should help you. [...] 15826 14 5_Sorry19_Pedro Correia (DSI)13_TE00102@MC.PT31_Mon, 19 Mar 2001 15:19:59 +0100278_- This is only a test. Regards Pedro

================================================ 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. 15841 144 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.15_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK31_Mon, 19 Mar 2001 15:22:19 +0000368_us-ascii

I think it's fair to say that this would be an excellent candidate for the DB2-L-Documents site... ...that way everybody can (potentially) have access to the code without all 2000+ members of the Listserv feeling the need to reply personally.

Best regards,

Gordon Fishwick. DB2 Systems Programmer. Scottish and Southern Energy plc. [...] 15986 34 61_FW: DB2-OS/390- Statistical Analysis of Tablespaces and Index13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 19 Mar 2001 10:42:03 -0500407_iso-8859-1 > If anyone hasthe REXX exec and/or Chuck Hoover's presentation, >please send them to me and I'll upload them to the DB2-L document archive.

------------- Hi Cathy,

I'm not absolutely sure, but I suspect my good friend Chuck Hoover would prefer that folks access this presentation as well as all of his others which are available from http://www.compuware.com/products/db2.htm [...] 16021 15 61_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index8_A. Adams23_Andi_Adams@VANGUARD.COM31_Mon, 19 Mar 2001 10:40:53 -0500310_us-ascii hi jim,

may i also recv a copy of this. thanks, andi

================================================ 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. 16037 18 51_Re: CPU/Elapsed time to move data to host variables12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 19 Mar 2001 09:44:28 -0600473_- There is quite a bit of variation, but that's about the right magnitude. In addition to the other good suggestions, you can use Estimator for a reasonable guideline. Varchar and NUL-delimited is more expensive, NULLs are more expensive than NOT NULL, avoid having pointers to handle NULL if there are no nulls. The code in several of the DB2 dynamic SQL applications (DSNTEP2, DSNTIAUL) was tightened up over the releases and we dropped out quite a bit of cpu time. [...] 16056 18 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.0_26_carl.pizzarro@HIGHMARK.COM31_Mon, 19 Mar 2001 11:05:57 -0500323_us-ascii Jim,

Please also add me to the list!

Thanks, Carl Pizzarro

================================================ 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. 16075 106 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.12_Rumyee, Paul19_paul.rumyee@HBC.COM31_Mon, 19 Mar 2001 11:14:05 -0500623_- Gord.. What is the URL of the DB2-L-Documents Site? Thx. Paul

> -----Original Message----- > From: Gordon Fishwick [SMTP:Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK] > Sent: Monday, March 19, 2001 10:22 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > > I think it's fair to say that this would be an excellent candidate for the > DB2-L-Documents site... > ...that way everybody can (potentially) have access to the code without > all > 2000+ members of the Listserv feeling the need to reply personally. > > Best regards, > > Gordon Fishwick. > DB2 [...] 16182 103 32_Re: DB2-OS/390- Statistical Anal8_DB2 Mail23_DB2.Mail.R000@SYSCO.COM31_Mon, 19 Mar 2001 11:47:00 -0500548_- We would like a copy, too, please.

Thanks, Evelyn J. Runyon 281-584-4028 Runyon.Evelyn@Corp.sysco.com ------------------( Forwarded letter 1 follows )-------------------- Date: Fri, 16 Mar 2001 15:36:25 -0700 To: DB2-L@RYCI.COM From: Robert.Jans[robert_jans]@ALBERTSONS.COM Sender: owner-db2-l@RYCI.COM Reply-To: DB2.Data.Base.Discussion.List[DB2-L]@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa

I'd like a copy too, please

Robert Jans Albertsons, Inc. [...] 16286 159 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.15_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK31_Mon, 19 Mar 2001 16:45:18 +0000625_us-ascii Paul,

As requested...

http://jupiter.ryci.com/archives/db2-l-documents.html

...enjoy (?).

Best regards,

Gordon Fishwick. DB2 Systems Programmer. Scottish and Southern Energy plc.







"Rumyee, Paul" on 19/03/2001 16:14:05

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Gordon Fishwick/HAV/SSE) Subject: Re: [DB2-L] DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.





Gord.. What is the URL of the DB2-L-Documents Site? Thx. Paul [...] 16446 138 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.13_Lynne Flatley17_LFlatley@NEFN.COM31_Mon, 19 Mar 2001 11:56:22 -0500820_iso-8859-1 Here it is...

http://jupiter.ryci.com/archives/db2-l-documents.html

-----Original Message----- From: Rumyee, Paul [SMTP:paul.rumyee@HBC.COM] Sent: Monday, March 19, 2001 11:14 AM To: DB2-L@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.

Gord.. What is the URL of the DB2-L-Documents Site? Thx. Paul

> -----Original Message----- > From: Gordon Fishwick [SMTP:Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK] > Sent: Monday, March 19, 2001 10:22 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > > I think it's fair to say that this would be an excellent candidate for the > DB2-L-Documents site... > ...that way everybody can [...] 16585 178 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.12_Rumyee, Paul19_paul.rumyee@HBC.COM31_Mon, 19 Mar 2001 12:24:13 -0500702_iso-8859-1 Gord.. Thx. Paul

> -----Original Message----- > From: Gordon Fishwick [SMTP:Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK] > Sent: Monday, March 19, 2001 11:45 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > Paul, > > As requested... > > http://jupiter.ryci.com/archives/db2-l-documents.html > > ...enjoy (?). > > Best regards, > > Gordon Fishwick. > DB2 Systems Programmer. > Scottish and Southern Energy plc. > > > > > "Rumyee, Paul" on 19/03/2001 16:14:05 > > Please respond to DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > cc: (bcc: Gordon Fishwick/HAV/SSE) > Subject: [...] 16764 39 68_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspaces.14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 19 Mar 2001 12:47:18 -0500607_ISO-8859-1 Then hit ratio goal for an index pool is 95%... whcih does not mean you can really obtain this. If your indexes are very large and very random, you won't. The level of activity for any object determines how many pages will be in the pool at any one time, and objects that have a low rate of reference will usually have a much higher number of I/Os. Also, be sure you are using the correct system hit ratio formula, and not using a getpage/rio ratio. Regards, Joel +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Message text written by DB2 Data Base Discussion List >I counted [...] 16804 22 17_Sql code is -904.17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Mon, 19 Mar 2001 10:46:34 -0700300_US-ASCII Hi,

One of our user received the above error with reason code 00c200f6, Type resource 00000220 which is pointing to a Data set. My question is can this error occur if another user is using this data set? Or Please let me know the shortest way to fixed the problem. Thanks a lot. [...] 16827 178 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Mon, 19 Mar 2001 09:50:55 -0800410_us-ascii Jim, I would be very interested in the REXX exec if you could send me a copy.

Thank you..





Lynne Flatley cc: Sent by: DB2 Subject: Re: DB2-OS/390- Statistical Data Base Analysis of Tablespaces and Index spa ces. Discussion List



03/19/01 08:56 AM Please respond to DB2 Data Base Discussion List [...] 17006 67 42_DB2 for OS/390: Getpage Reduction Question0_16_dbussier@CSC.COM31_Mon, 19 Mar 2001 12:51:53 -0500397_us-ascii Hi List,

We're in the process of tuning a batch application to reduce the current wall time of 7+ hours, and at this point tuning the SQL is not an option. The first task was to place the indexes and tablespaces in separate bufferpools (a future task will separate them by access type (random vs. seq) and working set size) and to increase the size of those pools as follows: [...] 17074 68 30_Re: Cleaning up unused columns15_Hernandez, Hugo22_Hugo_Hernandez@BMC.COM31_Mon, 19 Mar 2001 11:57:07 -0600546_iso-8859-1 Hello wjmartin:

I think that this could be a procedure:

1) Run the RUNSTATS utility, with the options of TABLE ALL or COLUMN(ALL). After this you have a number for each table-column in the CARD column of SYSIBM.SYSCOLUMNS. 2) If CARD is 1 then do a SELECT column from xxx.table (the table where the column is) and be sure that the value is not a DB2 default. 3) Other way to do this is to make the following SQL (remember to runstats before) SELECT 'SELECT ' !! NAME !! ' FROM ' !! TBCREATOR !! '.' !! TBNAME !! ';' [...] 17143 163 53_Re: Reorg at partition level with NPIs: Clarification11_James Drewe20_James.Drewe@AEXP.COM31_Mon, 19 Mar 2001 10:55:51 -0700560_us-ascii Dr. Ebert

If I may restate what you said, an NPI can only be reorganized (that is, its LEAFDIST reduced) either through a a complete tablespace reorganization or a complete NPI reorganization. Even with online reorganization, a 10 terrabyte tablespace or its NPI would not be practical to reorg.

I think if the priority could be raised with IBM, a reorg at the NPI PIECESIZE would be the next best solution to a complete tablespace reorganization. This way the NPI, as well as the tablespace, could be reorganized in smaller parts. [...] 17307 97 21_Re: Sql code is -904.16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 19 Mar 2001 13:11:14 -0500191_us-ascii





From the DB2 OS390 V5 Messages and Codes Manual:

*********************,Text Below Copyright (c) 2001, IBM,*********************

00C200F6

17405 94 46_Re: DB2 for OS/390: Getpage Reduction Question12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 19 Mar 2001 20:25:16 +0200480_iso-8859-1 Hi, What type of activity? (select, cursors, update ,delete ,...) Did you use "release(commit)" or "deallocate". Amount of data in the involved tables may have changed as well.



Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com ----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Monday, March 19, 2001 7:51 PM Subject: DB2 for OS/390: Getpage Reduction Question [...] 17500 90 46_Re: DB2 for OS/390: Getpage Reduction Question14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 19 Mar 2001 13:46:36 -0500686_ISO-8859-1 The answer is Total SQL before 73,261,027 after 69,955,851

Your workload was not too close in the before/after scenarios, with 3.3 million fewer sql calls.

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



Message text written by DB2 Data Base Discussion List >Hi List,

We're in the process of tuning a batch application to reduce the current wall time of 7+ hours, and at this point tuning the SQL is not an option. The first task was to place the indexes and tablespaces in separate bufferpools (a future task will separate them by access type (random vs. seq) and working set size) and to [...] 17591 132 46_Re: DB2 for OS/390: Getpage Reduction Question0_16_dbussier@CSC.COM31_Mon, 19 Mar 2001 13:47:37 -0500533_us-ascii More information:

SQL Counts: SELECT 11,538,117 OPEN 10,883,650 FETCH 36,426,952 CLOSE 10,881,223 SET HOST VAR 224,580 COMMIT 1,329 All others zero.

RELEASE(COMMIT)

No changes in table sizes, there was no online or batch activity between runs.











Isaac Yassin @RYCI.COM> on 03/19/2001 01:25:16 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 17724 24 20_Archive Logs To Disk16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Mon, 19 Mar 2001 14:11:03 -0500481_iso-8859-1 Hey Folks.. We've implemented archiving our active logs to disk in development. We are using dual logging. Our active logs are sized at 1500 tracks per data set. In our ZPARMS, ALCUNIT=CYL, PRIQTY=100 & SECQTY=10, but when they are archived to disk, they take up 2580 tracks. Any ideas? Also, when we implement in Production, and there is not enough space to allocate the archive log to disk, will DB2 retry and a message be sent to the console if our ARCWTOR=YES? [...] 17749 54 24_Re: Archive Logs To Disk19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Mon, 19 Mar 2001 13:25:50 -0600603_iso-8859-1 Hi Michael, did you check BLKSIZE? It should to be changed match the disk used for most efficient space utilization. Also, make sure this is covered in your disaster and local recovery JCL.

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 [...] 17804 40 24_Re: Archive Logs To Disk13_Gleason, Mike22_MGleason@UILLINOIS.EDU31_Mon, 19 Mar 2001 13:26:43 -0600664_iso-8859-1 Hi Mike, Check your zparm for archive blksize. If it's over half-track it will take twice the space.

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Monday, March 19, 2001 1:11 PM To: DB2-L@RYCI.COM Subject: Archive Logs To Disk



Hey Folks.. We've implemented archiving our active logs to disk in development. We are using dual logging. Our active logs are sized at 1500 tracks per data set. In our ZPARMS, ALCUNIT=CYL, PRIQTY=100 & SECQTY=10, but when they are archived to disk, they take up 2580 tracks. Any ideas? Also, when we implement in Production, and there is not enough space to [...] 17845 39 24_Re: Archive Logs To Disk50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Mon, 19 Mar 2001 14:27:13 -0500659_iso-8859-1 BSDS & LOG are archived together did you factor that

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Monday, March 19, 2001 2:11 PM To: DB2-L@RYCI.COM Subject: Archive Logs To Disk



Hey Folks.. We've implemented archiving our active logs to disk in development. We are using dual logging. Our active logs are sized at 1500 tracks per data set. In our ZPARMS, ALCUNIT=CYL, PRIQTY=100 & SECQTY=10, but when they are archived to disk, they take up 2580 tracks. Any ideas? Also, when we implement in Production, and there is not enough space to allocate the archive log to disk, will DB2 [...] 17885 50 21_Re: Sql code is -904.19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Mon, 19 Mar 2001 13:28:42 -0600635_iso-8859-1 Hi Armando, either increase time to wait for DFHSM recall or increase the time to migrate, maybe both.

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 [...] 17936 177 49_Re: H E L P - Can I get a variable into a VIEW???10_Mark Labby16_mlabby@PHEAA.ORG31_Mon, 19 Mar 2001 14:27:32 -0500534_us-ascii Thanks Terry and Walter!

It looks like Terry hit it right on the head - We are at DB2v6, so it seems that the GROUP BY is causing it to go to view merge. I've been discussing it with the developers and they have looked into the query and said they can't remove the GROUP BY. At this point, they seem to be leaning to using the SQL directly although they might like us to investigate the possibility of this being made into a User Defined Function. Since I've never done anything with UDFs, I have no idea if this [...] 18114 55 24_Re: Archive Logs To Disk12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 19 Mar 2001 21:33:38 +0200286_iso-8859-1 Hi, You hve blksize for archive set at 28672 which give you one block per 3390 track. 1500*12=18000 pages . 7 pages per 28672 block gives 2571 tracks . Allocation is in cylinders - so 2580 tracks = 172 cyls. Change blksize of archive to 24576 to get 2 blocks per track. [...] 18170 40 24_Re: Archive Logs To Disk17_Belfield, Alma G.21_Alma.Belfield@TWA.COM31_Mon, 19 Mar 2001 13:40:23 -0600666_iso-8859-1 Check your BLKSIZE zparm..... choose the best size for the type of disk you're using with an LRECL of 4096. Not sure about the wtor....

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Monday, March 19, 2001 1:11 PM To: DB2-L@RYCI.COM Subject: Archive Logs To Disk



Hey Folks.. We've implemented archiving our active logs to disk in development. We are using dual logging. Our active logs are sized at 1500 tracks per data set. In our ZPARMS, ALCUNIT=CYL, PRIQTY=100 & SECQTY=10, but when they are archived to disk, they take up 2580 tracks. Any ideas? Also, when we implement in Production, [...] 18211 51 24_Re: Archive Logs To Disk16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 19 Mar 2001 14:38:50 -0500454_iso-8859-1 Michael, Did you remember to change BLKSIZE in the DSN6ARVP macro of DSNZPARM to some 3390-friendly number? If you still have a tape-friendly BLKSIZE you could waste almost 1/2 of each DASD track.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Monday, March 19, 2001 2:11 PM To: DB2-L@RYCI.COM Subject: Archive Logs To Disk [...] 18263 58 30_Re: Cleaning up unused columns16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 19 Mar 2001 20:40:57 -0000386_- Hello Wjmartin,

There are a couple of issues in your question. When you refer to UNUSED columns - are you talking about columns "without used data" or are you thinking of columns NOT being used in SQL ?

I feel its dangerous just to check the content of the columns, since 0 for decimal and integer columns is valid as well as spaces is valid for character columns. [...] 18322 62 12_SQL Question10_Karau, Joe22_Joe.Karau@KINGLAND.COM31_Mon, 19 Mar 2001 14:31:50 -0600585_iso-8859-1 Hello all,

I'm new to this list, so if this question is considered off-topic please let me know and I won't post similar questions in the future (and would be very grateful if you could point me to a better resource).

Anyway, we are implenting an online application, and a couple of our tables are searchable. We are now trying to create a method to search by "aliases" as well as the entries in the searchable tables. However, we are witnessing a drastic decrease in performance, searches are taking between 2 and 2 1/2 times as long when using aliases. [...] 18385 15 10_Oracle DBA0_26_mmetcalf@NOTES.STATE.NE.US31_Mon, 19 Mar 2001 14:47:11 -0600361_us-ascii Can someone suggest the going rate for a (contract) Oracle DBA?

Michelle Metcalf Database Administrator

================================================ 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. 18401 37 14_Re: Oracle DBA16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Mon, 19 Mar 2001 16:02:46 -0500667_us-ascii Sure, 0.50 per hour!











mmetcalf@NOTES.STATE.NE.US on 03/19/2001 03:47:11 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: RICHARD E MOLERA/SallieMae) Subject: Oracle DBA





Can someone suggest the going rate for a (contract) Oracle DBA?

Michelle Metcalf Database Administrator

================================================ 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. [...] 18439 21 14_Re: Oracle DBA0_18_jtulman@MAAINC.COM31_Mon, 19 Mar 2001 15:56:23 -0500377_us-ascii Depending on actual years of experience (real world versus classes), here in the Northeast Oracle DBA's are getting between $80 to $120/hr direct. In other words, that is without a recruiters markup. Good Luck.

Justin S. Tulman Michael Anthony Associates, Inc. Work: (781) 237-4950 x33 Work: (800) 337-4950 x33 Fax: (781) 237-6811 Cellular: (508) 361-0072 [...] 18461 142 30_Version 5 to 6 possible glitch0_24_db46@DAIMLERCHRYSLER.COM31_Mon, 19 Mar 2001 16:09:55 -0500813_us-ascii The following query worked last week in version 5, but now is returning a FALSE result set in version 6

SELECT T1.I_LOGON, VALUE (T4.N_PERSN_FIRST, 'UNKNOWN') AS N_PERSN_FIRST, VALUE (T4.N_LAST, 'UNKNOWN') AS N_LAST, T1.D_INSTLD, T1.I_HDWE_SER, T1.MOD_FIRST, T1.MOD_LAST, VALUE (T2.I_BLDG, 0) AS I_BLDG, VALUE (T2.N_BLDG, '------') AS N_BLDG, VALUE (T2.C_BLDG_AREA_LOC, '------') AS C_BLD_AREA_LOC, VALUE (T2.I_LOC_CFM, '------') AS I_LOC_CFM, VALUE (T3.I_CORPLOC, 'CORP') AS I_CORPLOC, VALUE (T3.I_DEPT, 0) AS I_DEPT, T1.N_LAST, T1.N_PERSN_FIRST FROM ( SELECT A.I_LOGON, A.D_INSTLD, A.I_HDWE_SER, SUBSTR (A.I_HDWE_MOD, 16, 5) AS MOD_FIRST, SUBSTR (A.I_HDWE_MOD, 1, 15) AS MOD_LAST, F.N_LAST, F.N_PERSN_FIRST, A.I_EQUIP FROM G.GPHDWE A, G.GHDPER E, G.GPERSN F WHERE A.D_INSTLD BETWEEN &START [...] 18604 53 30_Queryno assigned within Select12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Mon, 19 Mar 2001 15:12:32 -0600419_US-ASCII Most people understand how you can set the QUERYNO and QUERYTAG within an EXPLAIN statement. For example:

EXPLAIN ALL WITH SNAPSHOT SET QUERYTAG = 'VIS' SET QUERYNO = 2 FOR SELECT UNIT_ID FROM UNIT_INDV_KEY WHERE INDV_ID = ?;

is perfectly valid.

Is there a way to SET the QUERYNO and QUERYTAG within the actual SELECT, INSERT, UPDATE or DELETE statements within the application code? [...] 18658 197 34_Re: Queryno assigned within Select0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 19 Mar 2001 15:31:04 -0600597_iso-8859-1 Yes, OS/390 V6.1 (I'm not sure about the other boxes). Here are some notes I put together for our production support group, suggesting we adopt it as a new coding standard. We have a few, very large, very important, programs that over-ran QUERYNO when it was small int.

**************

Currently (V6) QUERYNO is only used in Plan table. It defines a static plan table id for an SQL statement the is populated at BIND with EXPLAIN time. Without queryno you have to have the Precompile Listing (which we do not keep) to easily related PLAN Table entries to source code. [...] 18856 153 34_Re: Queryno assigned within Select13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 19 Mar 2001 15:51:33 -0600395_iso-8859-1 Unfortunately this in non-OS/390, hence the reference to querytag...

The queryno clause is not supported, but if you were very keen you could also duplicate your SQL and code the EXPLAIN statement at the beginning for each call. Probably not a practical method, but unfortunately I'm not aware of any easier technique (other than some script to extract out the SQL etc.). [...] 19010 129 16_Re: SQL Question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 19 Mar 2001 16:20:50 -0600399_iso-8859-1 Joe,

This is definitely on-topic...and as for any better resources...there aren't too many as inexpensive as this.

Regardless whether this is OS/390 (could only be V6 looking at the syntax) or non-OS/390, the major issue with the SQL performance, forgetting the design for a second, is the non-index matching predicate:

UPPER(ALIAS) LIKE '%OUR SEARCH CRITERIA%' [...] 19140 103 34_Re: Queryno assigned within Select0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 19 Mar 2001 16:58:07 -0600452_us-ascii I don't mean to be contrary, but are you sure about your response Terry? After reading your response, I tested it in our development environment, DB2 V6.1 OS390/2.10 and it appeared to work fine.

ie, I added QUERYNO clause to an existing SQL embedded in an existing COBOL program,

EXEC SQL SELECT ERRT ,ERRTYPC INTO :DCL-ERRTXTV.ERRT ,:DCL-ERRTXTV.ERRTYPC FROM ERRTXTV WHERE ERRC = :DCL-ERRTXTV.ERRC QUERYNO 123456 END-EXEC [...] 19244 155 46_Re: DB2 for OS/390: Getpage Reduction Question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 19 Mar 2001 17:02:44 -0600379_us-ascii Danny,

Is your COMMIT strategy based on time or number of updates? If it is time, then you may have significantly increased the amount of SQL activity you have performed between each COMMIT.

And with RELEASE(COMMIT), you would lose your positioning for index lookaside (which saves you getpages), also getpages on data pages and dynamic prefetch etc. [...] 19400 167 34_Re: Queryno assigned within Select13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 19 Mar 2001 17:07:32 -0600363_us-ascii Ron,

It definitely does work on OS/390...and I did mention this (although I did not explicitly refer to it's delivery in V6).

What I also mentioned was that Doug's initial query was non-OS/390 (since he referred to querytag which is a non-OS/390 clause). But unfortunately the queryno cannot be used in non-OS/390 as it can in OS/390. [...] 19568 123 16_Re: SQL Question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 20 Mar 2001 10:16:54 +1100359_iso-8859-1 Joe

1) no, this sort of question is not off-topic.

2) there are essentially three ways of restructuring your query:

a) use an IN predicate on an uncorrelated subselect (as Terry mentioned) , SELECT * FROM COMPMT A WHERE A.OID IN (SELECT B.MOID FROM CALIAST B WHERE UPPER(ALIAS) LIKE '%OUR SEARCH CRITERIA%') ORDER BY DESC [...] 19692 81 36_Re: DB2 and the Java Virtual Machine15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 20 Mar 2001 10:16:26 +1100592_- Thomas, 'Grange', Chenny, Richard, Craig, Mark, James (C and P) and John,

Thanks for all your comments so far. I've now got a couple of other places to ask, but I'll persevere with DB2-L for now and the suggestions I've received.

My main problem is that I don't know (didn't; I found from the docs it wanted 1.1.7B. Thanks for the confirmation, Richard) what version DB2 wants, and don't know (although I think I know; 1.3.0) what version I have. I can see DB2's installed a whole bunch of Java-related libraries under SQLLIB\JAVA but doesn't seem to know they're there. [...] 19774 163 34_Re: Version 5 to 6 possible glitch14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 20 Mar 2001 10:41:49 +1100460_iso-8859-1 do any of the numerous apars on outer joins apply to you?

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: db46@DAIMLERCHRYSLER.COM [mailto:db46@DAIMLERCHRYSLER.COM] Sent: Tuesday, 20 March 2001 8:10 To: DB2-L@RYCI.COM Subject: [DB2-L] Version 5 to 6 possible glitch



The following query worked last week in version 5, but now is returning a FALSE result set in version 6 [...] 19938 105 46_Re: DB2 for OS/390: Getpage Reduction Question18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Tue, 20 Mar 2001 09:15:00 +0930425_us-ascii Howzit Danny?

Could it be that you had a lot of page stealing prior to doubling the size of your bufferpools and hence an increase in getpages. Might also partially account for the reduction in sync reads.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









dbussier@CSC.COM@RYCI.COM> on 20/03/2001 03:21:53 [...] 20044 36 36_Re: DB2 and the Java Virtual Machine14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 20 Mar 2001 10:53:07 +1100491_iso-8859-1 Its not the UDB demo that will beat you, it's the "W" in W98 that will do it.

Now if you did the right thing and stuck to SQL Server and W2K ... ... ... Hmm, on second thoughts you still be ground down.

/* standard disclaimer */ James Campbell Hansen Corporation -----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: Tuesday, 20 March 2001 10:16 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 and the Java Virtual Machine [...] 20081 44 36_Re: DB2 and the Java Virtual Machine15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 20 Mar 2001 11:19:16 +1100371_iso-8859-1 Don't worry, James. I won't let the b@$tards grind me down. Wow, a really strong feeling of déjà vu just washed over me. Wonder where that came from... Oh God, no more Latin, please!

Oh, and the day I go anywhere near SQL Server is the day I hang up my spurs and guns forever. I've got blue blood, you know, and it's Gerstner's, not the Windsors'. [...] 20126 43 14_Re: Oracle DBA14_subrata mondal25_subratamondal@HOTMAIL.COM31_Tue, 20 Mar 2001 00:56:09 -0000237_- I have seen Dba on sybase getting only 56 $ an hour in my company and another DBA on Oracle getting 110 $ per hour. I have also seen DBA on Db2 equally near 100 $ an hour - This is at Washington - DC state and tri state vicinity. [...] 20170 73 27_Reorg Table - sort by index10_Jerry Long27_Jerry.Long@MACQUARIE.COM.AU31_Tue, 20 Mar 2001 12:10:34 +1100570_iso-8859-1 Hi Folks,

The answer to this is probably obvious, but please indulge an old mainframer finding his way in the brave new world of DB2 UDB.

In DB2/Solaris V6, if a table with multiple non-clustering indexes is reorged with REORG TABLE A INDEX B, where in the catalog is it recorded that INDEX B was used? My interest is so that I can determine which of the records produced by REORGCHK on clusterratio per index is actually relevant. You might argue that if no clustering index is defined, then none of them are relevant - but if a table has [...] 20244 121 31_Re: Reorg Table - sort by index14_Scott Saunders20_ssaunders@SIEBEL.COM31_Mon, 19 Mar 2001 18:21:36 -0800327_iso-8859-1 Jerry; You will have to execute runstats after the reorg and see that INDEX B is 100% clustered. Of course, other indexes may also end up being similarly clustered. There is nothing that I know of, up to V6.1, in the catalog that will record the fact that INDEX B was selected to be clustered during the reorg. [...] 20366 81 31_Re: Reorg Table - sort by index11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Mon, 19 Mar 2001 22:50:16 -0500506_us-ascii Jerry,

It's not stored in the catalog, but the information is kept in the history file for the instance. Consider the following science project:

db2 "create index dbmsi1.lname_ix on dbmsi1.employee (lastname desc) " db2 "create index dbmsi1.fname_ix on dbmsi1.employee (firstnme asc) " db2 "reorg table dbmsi1.employee index dbmsi1.fname_ix use tempspace1" db2 "reorgchk update statistics on table dbmsi1.employee" db2 "list history reorg containing dbmsi1.employee for sample" [...] 20448 188 49_Re: H E L P - Can I get a variable into a VIEW???16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 20 Mar 2001 11:55:18 +0530411_iso-8859-1 Mark,

If i am not wrong then you were having problems with the tablespace scan in tables LN80 and non-matching index scan in LN10(How is this possible ?) once the host variables are changed to some values. Tablespace scan on LN80 can still be thought about but its very difficult to get how LN10 is having non-matching index scan if bf_ssn is the first node of the index(on both tables). [...] 20637 83 32_Re: DB2-OS/390- Statistical Anal14_Razvan Tomescu14_rtomescu@FX.RO31_Tue, 20 Mar 2001 09:26:12 +0200587_iso-8859-1 Hello Jim

I would like a copy too.

Thanks a lot Razvan Tomescu





> > >From: Jim Lewandowski > > >Reply-To: DB2 Data Base Discussion List > > >To: DB2-L@RYCI.COM > > >Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index > spa > > > ces. > > >Date: Tue, 11 Jan 2000 10:09:00 -0600 > > > > > >I had sent out many copies of an SMF 42 subtype 6 REXX that provided a > > >detail and summary of I/O response times (and their components) for > > >each table/indexspace for a DB2 DBM1 [...] 20721 14 27_How to trace DB/2 commands.0_25_jaap.kuijt@NL.ABNAMRO.COM31_Tue, 20 Mar 2001 08:45:04 +0100337_us-ascii Hello Jim, If there is a REXX available, I'm interested too. /kind regards, Jaap Kuijt

================================================ 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. 20736 57 24_Re: Archive Logs To Disk10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 20 Mar 2001 08:58:47 +0100554_us-ascii Hi Michael, for the unused space other listers answered to ur question.

As for ARCWTOR if you are archiving to DASD and there's not enough space for archive log DB2 will display messages such as DSNJ008I,DSNJ115I,DSNJ128I...etc and in this case you can use the command -ARCHIVE LOG CANCEL OFFLOAD to (temporary) defer the action. BUT when all active logs are full (arrgghh !!! DSNJ111E) all DB2 activities are stopped and (sometimes) you have to shoot DB2 between the eyes. ALL these messages are independent from the ARCWTOR value. [...] 20794 96 46_Re: DB2 for OS/390: Getpage Reduction Question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 20 Mar 2001 13:28:41 +0530415_- Hi Danny,

I am not sure about the calculation of pages read per prefetch but it should not be 59.7(as derived by you). Other things like reduction in Sync. I/O and Getpages seems to me is because of the reduction in total number of SQLs for both the cases(before and after). The "after" test is having approx. 4 million less SQL execution and who know which all SQLs(may be more and more costly one). [...] 20891 69 51_Re: CPU/Elapsed time to move data to host variables16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 20 Mar 2001 14:45:28 +0530435_- I am not sure if anyone has replied but found none in my mail box. There are few guesses which i can make.

I heard/read/experienced that if anything passed to RDS(from Data Manager) for predicate evaluation, then each column of the row is passed for checks. This leads to very high CPU time. But in your case i do not find any predicate evaluation, it is simply the rows from the pages are to be dumped(Tablespace scan). [...] 20961 72 16_Re: Use Of Nulls16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 20 Mar 2001 15:02:45 +0530569_- My idea of choosing NULLs or Not NULL is truly dependent on requirements. NULLs have got some meaning and those are well described in Boonie Baker's article titled "Much Ado About Nulls" .

http://www.db2mag.com/db_area/archives/2000/q1/programmer.shtml

I have also found that NULLs have been not used properly in many places. I believe this should be derived truly from data modelling and should not be used unless until it has got some business meaning, performance reasons(Unique with Not Null) or some other ideas mentioned in Boonie's article. [...] 21034 118 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Tue, 20 Mar 2001 10:42:01 +0100362_us-ascii There are problems with that - what if the PIECESIZE is not big enough to accept the reorganised index (because of FREEPAGE and PCTFREE)? That's a small problem. During Unload, the utility would get all the information required about the final space required. If necessary, FREEPAGE and/or PCTFREE could be reduced during RELOAD and a note issued. [...] 21153 138 16_AW: Use Of Nulls12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 20 Mar 2001 11:14:55 +0100568_iso-8859-1 Sanjeev,

from my point of view it is better to avoid nullable columns in general, Some years ago we tested such columns. And we found pitfalls. For example you have to avoid nulls in columns that are candidates for arithmetic logic. Some of the SQL builtin functions (MAX, MIN, SUM, AVG, COUNT DISTINCT) omit column occurences with null. On the other hand a count(*) does not omit columns set to null. And the result of an AVG compared to a "SUM divided through COUNT(*)" will not end up in the same result when it is computed for a column that [...] 21292 19 81_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Indexspac es.--(Repeated)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 20 Mar 2001 15:45:27 +0530452_- I looked at the archive and saw questions are already answered. I think i have missed many mails in last 4-5 days.

Sorry for repeating thoughts(may be in all the replies today).

Regards, Sanjeev

================================================ 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. 21312 152 16_Re: Use Of Nulls0_27_jim.leask@RS-COMPONENTS.COM31_Tue, 20 Mar 2001 10:38:27 -0000673_iso-8859-1 Peter,

If you used count(column_name) then the count would omit nulls.

Jim.

-----Original Message----- From: Peter, Georg [mailto:G.Peter@DZBW.DE] Sent: 20 March 2001 10:15 To: DB2-L@RYCI.COM Subject: AW: Use Of Nulls



Sanjeev,

from my point of view it is better to avoid nullable columns in general, Some years ago we tested such columns. And we found pitfalls. For example you have to avoid nulls in columns that are candidates for arithmetic logic. Some of the SQL builtin functions (MAX, MIN, SUM, AVG, COUNT DISTINCT) omit column occurences with null. On the other hand a count(*) does not omit columns set to [...] 21465 238 39_Re: Reorg at partition level with NPIs:64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Tue, 20 Mar 2001 07:32:52 -0600460_ISO-8859-1 Mr. Drewe,

In response to your request for a third-party vendor to solve the problem of reorganizing large NPIs in a reasonable time, let me propose that such a solution exists today.

As to the "solution" of dropping and recreating the NPIs, this is inane. The "recreate" will require a read of the entire tablespace, a huge sort, and the sequential rewrite of the entire NPI. This is the suicide solution to conflict resolution. [...] 21704 125 31_Re: Reorg Table - sort by index22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Tue, 20 Mar 2001 08:58:56 -0500539_iso-8859-1 Scott -

---------------------------------------------------------------------------- Comment: REORG INDEX 2 USE 1 Start Time: 20010319222729 End Time: 20010319222731

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

The comment tells us that this table was reorg'd using the 2nd index (relative to 1) using tablespace with tablespace ID "1" (tablespaces, of course, are numbered relative to 0).

Does this mean we must know the indexes' create sequence? [...] 21830 25 39_Re: Reorg at partition level with NPIs:10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 20 Mar 2001 14:55:25 +0100593_us-ascii Can your reorg make a good espresso or avoid unwelcome pregnancy ?

Plz avoid this commercial, we have enough foulders gloryfing the power of non-IBM toolz & utilitiez in our offices.

Just personal feelingz & opinionz.

Regards

Max Dr. Scarpa Senior DB2 sysprog & data admin

'No ghe xe schei' Giacomo Casanova

================================================ 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. 21856 66 39_Re: Reorg at partition level with NPIs:16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 20 Mar 2001 09:09:43 -0500310_us-ascii Max, I must disagree. Part of the requirements of our job as DBA is to know the various tools and what their capabilities are. Of course, we must consider the source of the information; and we should always require a trial period as a minimum of proof.

Just my opinion.

Rick Creech [...] 21923 39 39_Re: Reorg at partition level with NPIs:0_18_mebert@AMADEUS.NET31_Tue, 20 Mar 2001 15:21:27 +0100430_us-ascii Max,

the DB2-L is for everything to do with DB2 - vendor comments and product naming are expressis verbis allowed by the rules. Also, some companies do indeed favor the use of 3rd party products (makes it easier to cope with high personnel fluctuations). Me, I think that developing these tools sounds like a lot more fun than using them (that's mere operating), but creative artists often are undervalued... [...] 21963 86 39_Re: Reorg at partition level with NPIs:13_O'Neill, Mike19_Mike.O'Neill@53.COM31_Tue, 20 Mar 2001 09:38:25 -0500469_iso-8859-1 I thought the comments about the CDB software was very helpful. I even printed them and passed them along to our DBA staff who are currently looking for a timely solution to this very problem (large NPI reorgs). So IMHO keep the info flowing. Thanks, Mike

-----Original Message----- From: Charles F Creech [mailto:cfcreech@DUKE-ENERGY.COM] Sent: Tuesday, March 20, 2001 9:10 AM To: DB2-L@RYCI.COM Subject: Re: Reorg at partition level with NPIs: [...] 22050 74 39_Re: Reorg at partition level with NPIs:0_19_mike.holmans@BT.COM31_Tue, 20 Mar 2001 14:44:32 -0000594_- We don't want the list to simply become an advertising forum, where any and all vendors announce any and all products whenever they feel like it.

In this particular case, though, a user of the list stated that he had a requirement for functionality not provided within the IBM base product (and how do we feel like treating IBM's own utilities now that they have been downgraded to products in comeptition with other vendors?), and a vendor then replied that his company already has a product which meets the specified requirement. That seems to me to be perfectly legitimate. It [...] 22125 161 31_Re: Reorg Table - sort by index14_Scott Saunders20_ssaunders@SIEBEL.COM31_Tue, 20 Mar 2001 06:48:13 -0800393_iso-8859-1 Mike; I think the original question was in regard to the SYSCOPY table in DB2 OS390. There is no equivalent in the UDB for LUNO system catalog but there is the history file as you correctly pointed out.

The history will remain until it is pruned explicitly or unless the retention period is set and passed. The retention period can be set in the database configuration, [...] 22287 172 16_Re: Use Of Nulls19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 20 Mar 2001 08:58:33 -0600341_iso-8859-1 Hi all, IT DEPENDS! I've been following this thread and must finally "speak out." Nulls are legitimate in relational design. One can shoot themselves in the foot with nulls if not correctly employed. Some cases where I've seen nulls used appropriately are: middleInitial, and some dates, like endDate.

HTH, Rick Davis [...] 22460 38 39_Re: Reorg at partition level with NPIs:10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 20 Mar 2001 16:10:20 +0100344_us-ascii Hi Dr Ebert & Rick & others.

I don't want to prevent ISV from posting technical information about their products, that would be the last straw !!

DB2-L is an open forum (I hope).

What I don't like is the glorification of their product to realize, later, that I buy a Gruyere cheese. I tested and used quite a [...] 22499 47 16_Re: Use Of Nulls13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 20 Mar 2001 10:34:41 -0500502_iso-8859-1 Hello Georg,

>And we found pitfalls. For example >you have to avoid nulls in columns that are candidates for arithmetic logic. >Some of the SQL builtin functions (MAX, MIN, SUM, AVG, COUNT DISTINCT) omit >column occurences with null. On the other hand a count(*) does not omit >columns set to null. And the result of an AVG compared to a "SUM divided >through COUNT(*)" will not end up in the same result when it is computed for >a column that can contain null.... And so on.... [...] 22547 21 15_DB2 SQl Tunning8_Ali INAL20_aliia@KOCBANK.COM.TR31_Tue, 20 Mar 2001 17:59:15 +0200460_iso-8859-9 Is there any DB2 tool for application development, SQL tunning except for IBM Client tool?? Which one do you recommend?

Ali ÝNAL System Development Kocbank A. S. 0216 4540600 / 4145 aliia@kocbank.com.tr

===============================================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. 22569 46 19_Re: DB2 SQl Tunning16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 20 Mar 2001 11:34:52 -0500403_ISO-8859-9 Platinum {now CA :=( } Detector together with Plan Analyzer is a pretty powerful combination for this if you are OS/390. No idea about the smaller OS tools.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Ali INAL [mailto:aliia@KOCBANK.COM.TR] Sent: Tuesday, March 20, 2001 10:59 AM To: DB2-L@RYCI.COM Subject: DB2 SQl Tunning [...] 22616 41 19_Re: DB2 SQl Tunning20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM31_Tue, 20 Mar 2001 11:30:53 -0500423_iso-8859-9 Ali ÝNAL

For SQL tuning -BMCs Apptune product -CA/Platinums Dectector

Lisa ----- Original Message ----- From: Ali INAL Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, March 20, 2001 10:59 AM Subject: DB2 SQl Tunning



Is there any DB2 tool for application development, SQL tunning except for IBM Client tool?? Which one do you recommend? [...] 22658 205 31_Re: Reorg Table - sort by index22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Tue, 20 Mar 2001 11:24:55 -0500479_iso-8859-1 Scott Saunders - Thanks for the info. I'd like to credit but I was quoting Scott Hayes who pointed out the contents of the history file.

Scott Hayes or anybody - Given your comments below, does this mean we must know the indexes' create sequence?

The snip is from Scott Hayes' reply. ---------------------------------------------------------------------------- Comment: REORG INDEX 2 USE 1 Start Time: 20010319222729 End Time: 20010319222731 [...] 22864 13 0_12_Ginger Mejia28_Info@PALLADIANCONSULTING.COM31_Tue, 20 Mar 2001 09:06:41 -0800261_iso-8859-1 REVIEW-L

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22878 61 22_repost: oracle vs. db215_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 20 Mar 2001 18:30:07 +0100602_iso-8859-1 Hello all, This is a repost from the Datawarehouse Listserver. Can you give it your best shot.?

Kind regards,

Jaap Slot.



From: guenther.schoebel@de.pwcglobal.com

Hi all, I'm in a good mood that you can help me. My client is about to build a dw and he wants a suggestion rather to take Oracle (8x or 9x) or DB2 (udbv7. or EEE). From my point of view both RDBMS are quite similar concerning their features and the choice depends mainly on the available know how on the one side and the current technical environment. The price will also be a fact. Are [...] 22940 94 26_Re: repost: oracle vs. db215_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Tue, 20 Mar 2001 13:00:39 -0500348_iso-8859-1 What is the replication source for the DWH data? If it is DB2, I Highly recommend UDB v7.1 That way DWH development staff can glean DB2 processing "styles" implemented at your site quickly and easily from your operational DBA's. Also, operational DBA's may be able to provide some backup support because the systems are so similar. [...] 23035 96 19_Re: DB2 SQl Tunning16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 20 Mar 2001 18:03:43 +0000460_us-ascii





!Candle DB-Explain might be of interest to you, if so please contact me offline and I can start ranting and raving about our oh-so-wonderful products "in private", so that my caro amico Max Scarpa may keep on being my friend!!!

:-)

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 414 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com [...] 23132 120 65_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa12_Henry Daries23_Henry.Daries@BCBSKS.COM31_Mon, 19 Mar 2001 16:12:00 -0600766_- Please add my name to the list. Thanks ------------------( Forwarded letter 1 follows )-------------------- Date: Sun, 18 Mar 2001 02:24:32 -0600 To: DB2-L@RYCI.COM From: perumal.kannan@EDS.COM Sender: owner-db2-l@RYCI.COM Reply-To: DB2.Data.Base.Discussion.List[DB2-L]@RYCI.COM Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa

Please add my name to the list..Thanks.

Thanks & Regards, Kannan P.



> -----Original Message----- > From: Robert Jans [SMTP:robert_jans@ALBERTSONS.COM] > Sent: Saturday, March 17, 2001 4:06 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and > Index spa ces. > > I'd like a copy too, please > > Robert Jans > Albertsons, Inc. >
but I need to fix this : DSNJ111E - OUT OF SPACE IN ACTIVE LOG DATA SETS ICH408I USER(DSN1MSTR) GROUP(SYS1 ) NAME(DSN1MSTR ) 764 DSN610.ARCHLOG1.D01079.T2010339.B0000707 CL(DATASET ) VOL(OS39M1) DEFINE - GROUP NOT DEFINED ICH408I USER(DSN1MSTR) GROUP(SYS1 ) NAME(DSN1MSTR ) 765 DSN610.ARCHLOG1.D01079.T2010339.B0000707 CL(DATASET ) VOL(OS39M1) DEFINE - GROUP NOT DEFINED DSNJ103I - DSNJDS01 LOG ALLOCATION ERROR 766 DSNAME=DSN610.ARCHLOG1.D01079.T2010339.B0000707, ERROR STATUS=970C0000, SMS REASON CODE=00004274 [...] 23294 33 65_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa12_Gregg Sawyer27_gregg.a1.sawyer@VERIZON.COM31_Tue, 20 Mar 2001 14:34:28 -0500700_us-ascii This "list" has transmogrified into the I'm-too-lazy-to-read-list-messages-and-too-discourteous-to-consider-responding-privately

list.

Is this really how you want to advertise yourselves to your fellow professionals? Please reconsider before signing up for it...

Gregg Sawyer

>Please add my name to the list. Thanks

>Please add my name to the list..Thanks.

> I'd like a copy too, please



> > >I had sent out many copies of an SMF 42 subtype 6 REXX that provided a > > >detail and summary of I/O response times (and their components) for > > >each table/indexspace for a DB2 DBM1 subsystem. Anyone else interested [...] 23328 59 18_Re: Archive Log PB14_Picou, Michael33_picou.michael@MAIL.DC.STATE.FL.US31_Tue, 20 Mar 2001 14:56:18 -0500475_iso-8859-1 Looks like DB2 is trying to archive an active log and the group SYS1 does not exist/have authority to create the archive log.

-----Original Message----- From: Stéphane COEZ [mailto:scoez@HARRYSOFTWARE.COM] Sent: Tuesday, March 20, 2001 2:35 PM To: DB2-L@RYCI.COM Subject: Archive Log PB



I'm not a DBA, I have a DB2 V6.1 running on a OS390/V2R8 on PC500. I'm not a system guy... I'm not a RACF guru I'm just a "advanced" selfmade user... [...] 23388 74 18_Re: Archive Log PB19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 20 Mar 2001 13:55:20 -0600623_iso-8859-1 You don't appear to be archiving the active logs for whatever reason (SMS involved?). You need to somehow get the archive process running.

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, [...] 23463 61 65_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 20 Mar 2001 12:09:06 -0800502_iso-8859-1 Folks, there is no list! As I've already pointed out, the original offer of a REXX was made over a year ago. Jim no longer uses the email address in the original note, and I don't know how to reach him. I had hoped that someone on this listserve either knows how to reach Jim or received the REXX from him a year ago and would be willing to help. If anyone has it and will send it to me, I'll take the time to update it or enhance it enough to make it my own (with all previous credits [...] 23525 29 33_Load question from prod. to test.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 20 Mar 2001 14:19:45 -0600436_- Hi Folks,

We are using DB2 V6 on os/390. request from someone to refresh the data from prod. to test. region... we have a table called TAB1 which has two columns in prod. and three columns in test region... but when i try to unload the data from prod.(TAB1) and load it to TAB1 in test region it is abending because of no. columns mismatch.... but user wants to be three columns in TAB1 in test region... i did like this. [...] 23555 57 65_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Tue, 20 Mar 2001 14:29:59 -0600742_- Hi Jim,

I am interested in getting a copy of REXX for analysis of tablespaces and index spa

Thanks KUMAR



>From: Gregg Sawyer >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa >Date: Tue, 20 Mar 2001 14:34:28 -0500 > >This "list" has transmogrified into the >I'm-too-lazy-to-read-list-messages-and-too-discourteous-to-consider-responding-privately > >list. > >Is this really how you want to advertise yourselves to your fellow >professionals? Please reconsider before signing up for it... > >Gregg Sawyer > > >Please add my name to the list. Thanks > > >Please add my name [...] 23613 57 18_Re: Archive Log PB22_Fisher, Raymond (ITSC)27_raymond.fisher@MAIL.IHS.GOV31_Tue, 20 Mar 2001 13:49:50 -0700377_iso-8859-1 Turn off forward recovery.

-----Original Message----- From: Stéphane COEZ [mailto:scoez@HARRYSOFTWARE.COM] Sent: Tuesday, March 20, 2001 12:35 PM To: DB2-L@RYCI.COM Subject: Archive Log PB



I'm not a DBA, I have a DB2 V6.1 running on a OS390/V2R8 on PC500. I'm not a system guy... I'm not a RACF guru I'm just a "advanced" selfmade user... [...] 23671 46 37_Re: Load question from prod. to test.16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Tue, 20 Mar 2001 14:07:52 -0700385_iso-8859-1 Leave out of the LOAD command the column you don't want to load. The only requirement for this "missing" column is that it have a DEFAULT value.

Duane

-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Tuesday, March 20, 2001 1:20 PM To: DB2-L@RYCI.COM Subject: Load question from prod. to test.



Hi Folks, [...] 23718 61 36_Overhead for Dynamic SQL in UDB/AIX?0_22_BILL_GALLAGHER@PHL.COM31_Tue, 20 Mar 2001 16:20:06 -0500411_us-ascii Hi,

First, our environment is UDB v6.1 (fixpack 5) on AIX 4.3.3.

We have an application that just went into production that was written using purely dynamic SQL (i.e. no parameter markers, just straight dynamic SQL). There was no specific reason why it was written this way other than the fact that the developer didn't know what he was doing (he thought he WAS writing static SQL). [...] 23780 102 18_Re: Archive Log PB12_Mark Granger22_the_grange@MBOX.COM.AU31_Tue, 20 Mar 2001 15:22:25 -0600579_us-ascii Stéphane, It looks like you need your RACF person, but if you don't have one you need to do one of the following (most will have to be done with a good RACF signon anyway, FIRECALL or something similar)- 1.Change the DB2 Userid (DSN1MSTR) to have a valid Group associated. It is saying RACF Group SYS1 is not defined. 2.Define SYS1 Group to RACF with authority to Allocate DSN610 high level qualifier Log datasets 3.Change the Log Dataset High Level Qualifier in Zparms to something that can be allocated, but I think the Invalid Group will still stop you 4.Change [...] 23883 16 13_List for CICS11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 20 Mar 2001 13:41:51 -0800385_iso-8859-1 Hi all, Our poor CICS guy is having trouble with websphere and CICS. Does anyone know if there is a list for CICS?? thanks, cliff:-)

================================================ 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. 23900 31 65_DB2 Connect Products 7.1 Unattended/Silent Installation Question?11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Tue, 20 Mar 2001 16:41:13 -0500384_us-ascii Has anyone done a 7.1 unattended install?. I get it to launch the install and it gets passed the first screen, but it stops on the "SELECT PRODUCTS" and I get -12 error message. After I hit Next the installation stops. I got it to record the initial install, I changed the Response file to look the way I want my clients to be installed, and the setup.iss looks okay. HELP... 23932 65 37_Re: Load question from prod. to test.11_Moore, Tony15_TMoore@IKON.COM31_Tue, 20 Mar 2001 16:31:51 -0500409_iso-8859-1 Kumar, What is the format of the additional column in your test table? If it's character, here's a quick and dirty way to do it...

Allocate another unload file with a record length larger than the original by the length of the additional column. I.E> Say the unload file is 50 bytes long and the column is 10 bytes long... then allocate an unload file with a record length of 60 bytes. [...] 23998 18 29_Re: FW: Cataloging a Database13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Tue, 20 Mar 2001 15:37:12 -0600297_- I don't think you can use DB2 Connect V7.1 with OS390 DB2 V5.2 or UDB Version 5.2 since it is back two levels....at least we could not. You can access V6 and V7, but not V5.2. You might want to try it on one machine before you go thru all of the work of setting it up on multiple machines. [...] 24017 102 37_Re: Load question from prod. to test.0_19_Tim.Lowe@STPAUL.COM31_Tue, 20 Mar 2001 15:55:50 -0600338_us-ascii Kumar, If the new column in the test table does not have a default value, then you could create a "default" value in the unload file when you unload prod. Using DSNTIAUL with the SQL parm, you could do something like one of the following depending on column type: SELECT A.*,SUBSTR(' ',1,1) AS NEW_COLUMN FROM tablename A ; [...] 24120 42 17_Re: List for CICS12_Mark Granger22_the_grange@MBOX.COM.AU31_Tue, 20 Mar 2001 15:59:22 -0600531_us-ascii Cliff, You can find all the Listservers at http://www.lsoft.com/lists/listref.html . The CICS list is LISTSERV@LISTSERV.UGA.EDU to join and CICS- L@LISTSERV.UGA.EDU for messages.

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



----- Original Message ----- From: Cliff Boley Date: Tuesday, March 20, 2001 3:41 pm Subject: List for CICS [...] 24163 48 17_Re: List for CICS0_19_Tim.Lowe@STPAUL.COM31_Tue, 20 Mar 2001 16:05:07 -0600443_us-ascii You might want to check out http://www.nauticom.net/www/softwise/mfdisc.htm This contains a list of mainframe discussion groups, one of which is on CICS.

Thanks, Tim





Cliff Boley cc: Sent by: DB2 Data Base Subject: List for CICS Discussion List



03/20/2001 03:41 PM Please respond to DB2 Data Base Discussion List [...] 24212 42 17_Re: List for CICS11_John Wynton21_jwynton@THEMISINC.COM31_Tue, 20 Mar 2001 17:02:31 -0500589_iso-8859-1 Cliff:

Subscribe to CICS-L at LISTSERV@LISTSERV.UGA.EDU.

Cheers, John Wynton Senior Account Manager Themis, Inc. Specialists in DB2, CICS & MQSeries Education 1-800-756-3000; 908-233-8900 (Int'l) http://www.themisinc.com





-----Original Message----- From: Cliff Boley [mailto:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Tuesday, March 20, 2001 4:42 PM To: DB2-L@RYCI.COM Subject: List for CICS



Hi all, Our poor CICS guy is having trouble with websphere and CICS. Does anyone know if there is a list for CICS?? thanks, cliff:-) [...] 24255 96 37_Re: Load question from prod. to test.11_Kwan, James18_James_Kwan@BMC.COM31_Tue, 20 Mar 2001 16:23:54 -0600460_iso-8859-1 Kumar,



Tony has given you a good way to populate the addition column with different value.

Alternatively if you don't care of the value of the additional column, then just point modify column definition in your control card to point to any column/position that will match your column definition. One of the tricks I like to use is to find a single column that only contain space and have my column point to that position. [...] 24352 67 37_Re: Load question from prod. to test.12_Gregg Sawyer27_gregg.a1.sawyer@VERIZON.COM31_Tue, 20 Mar 2001 17:36:05 -0500465_us-ascii While there are several technical answers to your question, it strikes me that the real problem is that your test and production configurations are dissimilar - this is going to be a continuing source of problems, proper testing not being the least of them.

As with many such issues, it seems the real fix is to address the underlying cause - which of course will set you banging your head against the "That's the way its got to be" barrier... [...] 24420 17 17_Re: List for CICS26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Tue, 20 Mar 2001 17:01:17 -0600413_- Yep, that's a good one along with www.listz.com and www.lsoft.com for their users(we are).

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. 24438 79 59_DB2-OS/390- Statistical Analysis of Tablespaces***REXX INFO13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 20 Mar 2001 15:15:38 -0800461_iso-8859-1 Thanks to Shaun for sending me the following post. I overlooked it in the archives because it had a different subject. THERE IS NO FREE REXX anymore -- it has been included in a software product from SolarFlare Software. Unfortunately I've been unable to find any information about SolarFlare. They're included on IBM's ISV list, but I couldn't find a web site for them. If anyone has more information, please post it to DB2-L@RYCI.COM. Thanks. [...] 24518 144 18_Re: Archive Log PB14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 21 Mar 2001 10:30:13 +1100495_iso-8859-1 My take on it is that in installation panel DSNTIPP, you specified "ARCHIVE LOG RACF ===> YES". This is asking DB2 to create an individual RACF profile for each archive log data set. Unfortunately, you didn't then give appropriate authority (to USER(DSN1MSTR) GROUP(SYS1)) to actually create the profile.

The better way of doing this is to say NO - and DB2 won't try to create the individual profile. Instead use a generic profile - so you get the RACF protection anyway. [...] 24663 38 29_Re: FW: Cataloging a Database13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Tue, 20 Mar 2001 17:30:46 -0600307_iso-8859-1 Shauna,

My understanding is that client support is 2 up, 1 down in relation to the server. So a 5.2 client can use a 7.1 DB2 server (DB2 Connect EE or UDB), but a 7.1 client cannot use a 5.2 server (just a v6). We have a scenario where 5.2 clients have used v7.1 DB2 Connect servers. [...] 24702 58 17_Re: List for CICS11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 20 Mar 2001 15:34:59 -0800527_iso-8859-1 Thanks to all for the quick response. And thanks for my CICS quy, I hope he'll be as happy with the CICS list as I've been with this list. cliff:-)

-----Original Message----- From: Mark Granger [mailto:the_grange@MBOX.COM.AU] Sent: Tuesday, March 20, 2001 1:59 PM To: DB2-L@RYCI.COM Subject: Re: List for CICS



Cliff, You can find all the Listservers at http://www.lsoft.com/lists/listref.html . The CICS list is LISTSERV@LISTSERV.UGA.EDU to join and CICS- L@LISTSERV.UGA.EDU for messages. [...] 24761 52 30_Fwd: Identity Column Attribute11_Jim Keister22_jkeister@ALTAVISTA.NET31_Tue, 20 Mar 2001 18:55:02 -0500561_- > DB2 OS/390 V6 > > I am doing some testing with the 'Identity' column attribute and have encountered a couple of problems: > > 1. create table test > (empno integer generated always as identity > start with 100 increment by 10, > id integer, > name char(30)) > in dbname.tsname; > > DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WITH, TOKEN KEY WAS EXPECTED > DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE > > **** Removing 'start with 100 increment by 10'resolves the problem but DB2, by default, then starts with the number 1 and increments [...] 24814 56 37_Re: Load question from prod. to test.14_Michael Finnis28_MFinnis@PRINCETONSOFTECH.COM31_Tue, 20 Mar 2001 19:19:43 -0500606_ISO-8859-1 Hi Kumar,

You can achieve this easily with Princeton Softech's Move for DB2. I am a Product Specialist (technical resource) for Princeton Softech Australia, India, Asia and Middle East and would be happy to provide more information off line: mailto:mfinnis@princetonsoftech.com

or you can look at the web site:

http://www.princetonsoftech.com/movefordb2

Cheers, Mike Finnis

-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Wednesday, March 21, 2001 6:50 AM To: DB2-L@RYCI.COM Subject: Load question from prod. to test. [...] 24871 27 18_Java & DB2 (again)15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 21 Mar 2001 11:23:57 +1100313_- Colleagues,

Not having a lot of joy here. Could someone fortunate enough to have DB2 UDB V6 running nicely on Windows 98 do me the great service of sending me:

1) a copy of their autoexec.bat 2) a copy of their dbm cfg 3) the output from the SET command (so I can see what variables are set) [...] 24899 128 37_Re: Load question from prod. to test.15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Tue, 20 Mar 2001 18:29:42 -0600372_us-ascii Kumar,

You can as well try doing this : 1) I believe your target table is empty and suggest you to unload this empty table which will create dummy empty data file. 2)copy your Production table's unloaded data (of two cols) & reformat accordingly into dummy empty data file and use this file as input file for uploading into target table (test region) [...] 25028 93 34_Re: Fwd: Identity Column Attribute14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 21 Mar 2001 11:52:12 +1100448_iso-8859-1 Jim,

1a) try: create table test (empno integer generated always as identity (start with 100 increment by 10), id integer, name char(30)) in dbname.tsname; (ie parenthesis before "start" and after the increment)

1b) "overriding user value" is part of the syntax for INSERT.

2) IDENTITY_VAL_LOCAL() returns the last generated identity (as DEC(31)). SET :hv = INTEGER(IDENTITY_VAL_LOCAL()) might be what you want. [...] 25122 58 65_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 20 Mar 2001 21:17:55 -0500713_ISO-8859-1 Cathy, Even though the software was given out by Jim without charge, you do not have the right to re-distribute it, and/or modify it and re-distribute it - without Jim's express written permission. Regards, Joel +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++ Buffer Pool Tool for DB2 - The only product that can Predict the effect of changes & show how to properly group objects into pools based on access DASD/Xpert for DB2 - Easily shows all your DASD performance problems, and recommends tuning options. Visit www.responsivesystems.com - performance white papers and presentations +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++ [...] 25181 60 18_Re: Archive Log PB10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 21 Mar 2001 16:10:21 +1000383_ISO-8859-1 Hi Stephane,

I think the message indicates that the group DSN610 (the HLQ of the dataset u r trying to define) is not defined on the system. RACF expects the HLQs of datasets to be defined as groups.

It is possible that u might need to change the default dataset name for the archlogs, or create the group DSN610 and give access to the relevant userid. [...] 25242 96 26_AW: repost: oracle vs. db212_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Wed, 21 Mar 2001 09:11:12 +0100656_iso-8859-1 Guenther,

have a look at the www-sites of Yevich Lawson & Associates (www.ylassoc.com). There is a white paper ": DB2 Universal Database and Total Cost of Ownership (TCO)" which compares DB2 and Oracle.

HTH

Thomas



> -----Ursprüngliche Nachricht----- > Von: Slot, JP (Jaap) [mailto:J.P.Slot@RF.RABOBANK.NL] > Gesendet am: Dienstag, 20. März 2001 18:30 > An: DB2-L@RYCI.COM > Betreff: repost: oracle vs. db2 > > Hello all, > This is a repost from the Datawarehouse Listserver. Can you > give it your > best shot.? > > Kind regards, > > Jaap Slot. > > > From: guenther.schoebel@de.pwcglobal.com > > Hi all, [...] 25339 39 27_DDF Priority in a WLM World24_Peter_Schwarcz (Bigpond)26_Peter_Schwarcz@BIGPOND.COM31_Wed, 21 Mar 2001 19:24:33 +1000438_iso-8859-1 We are running DB2 V5 with on OS/390 2.8 running Workload Manager in Goal Mode.

I have the DDF address space running with WLM enclave support enabled.

During the busiest times of the day, DB2 CONNECT requests can take 40 seconds to complete. This connect time compares to less than a second during normal workloads. Once a connection is established the JDBC calls to stored procedures are consistently fast. [...] 25379 150 18_Re: Archive Log PB32_=?iso-8859-1?Q?St=E9phane?= COEZ23_scoez@HARRYSOFTWARE.COM31_Wed, 21 Mar 2001 11:32:30 +0100321_iso-8859-1 Thanks James,

but :

I do not have any profile (no DSN610, DSN610.* or whatever...) The initial install of DB2 was already made on my ADCD OS390 installation, I think I'm NOT in "ARCHIVE LOG RACF ==> YES" but i'm not sure. Do I have to create all these RACF dataset profile or not, and how ? [...] 25530 24 32_XML Extender (UDB 7.1 AIX/WinNt)11_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Wed, 21 Mar 2001 12:18:29 +0100343_us-ascii Hi Listers,

I have to make a presentation about the XML Extender for our application developers. I have got the IBM-book "XML Extender Administration and Programming", that seems quite good. Could you give me another source of information about the XML Extender, perhaps a good summary of the features, or a presentation? [...] 25555 33 35_WLM-managed stored procedures & RRS11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 21 Mar 2001 07:39:53 -0500601_iso-8859-1 I'm hoping somebody can answer this question. I can't call IBM support (when I press 4 for OS/390, it rings once and then there's nothing) and they've removed our IBMLINK access (second time this year IBM has erroneously dropped all the good stuff like SIS and ETR).

We want to start using WLM-managed stored procedures. I passed the information on to our OS/390 guy to define the WLM application environment and start RRS (since we weren't using it). We've run into a glitch, though. We don't use a coupling facility and instead use data-only logger. According to the doc, RRS [...] 25589 66 39_Re: WLM-managed stored procedures & RRS13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Wed, 21 Mar 2001 08:22:46 -0500473_US-ASCII Tina,

I assume you'll get a more definitive answer from an IBM-er on this. Meanwhile, the "Getting Started with DB2 Stored Procedures ...." (SG24-4693-01) RedBook notes in section 3.14:

"... you have to implement ... RRS. ... Check the PLEXCFG parameter of IEASYSnn member in SYS1.PARMLIB. ... If the parameter specification is PLEXCFG=(MONOPLEX,OPI=NO), ... you don't need to have a coupling facility nor set up the whole Sysplex environment." [...] 25656 71 39_Re: WLM-managed stored procedures & RRS0_19_mike.holmans@BT.COM31_Wed, 21 Mar 2001 13:35:44 -0000609_- I'm no authority on this, but I'm reading lots of documentation about it in preparation for doing it ourselves.

The redbook "Getting Started with Stored Procedures" (SG24-4693-01) has this to say:

"You must be running in one of two Sysplex environments. Check the PLEXCFG parameter of IEASYSnn in SYS1.PARMLIB

If the parameter specification is PLEXCFG=(MONOPLEX,OPI=NO), it indicates that the system is to be part of a single-system Sysplex that must use a Sysplex couple dataset. In this case, you don't need to have a coupling facility nor set up the whole Sysplex environment." [...] 25728 58 22_Re: Java & DB2 (again)23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 21 Mar 2001 13:29:10 -0000414_iso-8859-1 OS/2 ... excuse me while I cough and spit on the floor :-) (only you and I know why I say that).

Raymond .. I'm having my own battles here with Java on 6.1 ..... arggggggg

all the best

Leslie

-----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: Wednesday, March 21, 2001 12:24 AM To: DB2-L@RYCI.COM Subject: Java & DB2 (again) [...] 25787 22 17_SAS/DB2 and V6/V716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 21 Mar 2001 08:59:45 -0500528_iso-8859-1 We are running SAS 6.09. The IBM-supplied DBRM checker objects to SAS/DB2's lack of colons (e.g. DESCRIBE SQLST01 INTO SQLDA ).

Anyone here have SAS/DB2 running with V6 or V7? What version of SAS is required?

regards,

eric pearson NS ITO Database Support

================================================ 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. 25810 22 21_XPDETER and DB2 V6/V716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 21 Mar 2001 09:01:46 -0500537_iso-8859-1 We are running XPEDITER. The IBM-supplied DBRM checker objects to XPED's lack of colons (e.g. FETCH SELECT_CURSOR USING DESCRIPTOR SQLDA).

Anyone here have XPED running with V6 or V7? What version of XPED is required?

regards,

eric pearson NS ITO Database Support

================================================ 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. 25833 18 22_Re: Java & DB2 (again)10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 21 Mar 2001 14:54:30 +0100455_us-ascii Hi Leslie, what about the battles with SQLJ, if any ? We are using a small Java application, but we want to develop some

SQLJ-based appz. Have you never used it ?

Thanks & regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 25852 48 22_Re: Java & DB2 (again)23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 21 Mar 2001 14:10:41 -0000384_iso-8859-1 Hi Max

... no experience here yet .. but will have soon (I hope). I hea that SQLJ is being re-visited bigtime in the US .. due to performance issues ... and general method problems.

Les

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Wednesday, March 21, 2001 1:55 PM To: DB2-L@RYCI.COM Subject: Re: Java & DB2 (again) [...] 25901 61 21_Re: SAS/DB2 and V6/V70_22_BILL_GALLAGHER@PHL.COM31_Wed, 21 Mar 2001 09:27:22 -0500425_us-ascii Eric,

We are running SAS 6.09 with SAS/DB2 against DB2 v6 with no problems.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: SAS/DB2 and V6/V7 Sent by: DB2 Data Base Discussion List



03/21/01 08:59 AM Please respond to DB2 Data Base Discussion List [...] 25963 70 48_Re: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?0_22_BILL_GALLAGHER@PHL.COM31_Wed, 21 Mar 2001 09:39:48 -0500587_us-ascii Ian,

Increasing the package cache size would just alleviate the symptoms, but not address the problem. The problem is that the application should have been written using static SQL. I can increase the package cache to improve the hit ratio, but as the volume of data and transactions for the application continue to grow, the package cache will have to grow accordingly as well. If the application can be rewritten using static SQL, we should be able to keep the package cache at a reasonably sized, fixed size. That's my goal, and I need to be able to convince the [...] 26034 147 63_Re: DB2-OS/390- Statistical Analysis of Tablespaces***REXX INFO12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 21 Mar 2001 06:46:14 -0800404_us-ascii Has anyone looked into Barry Merrill's MXG Guide? He used to have have a bunch of code (mostly SAS, but I think there was some REXX too), which would process every RMF/SMF record known to man. It was both in hardback book form, and on a floppy/CD for a very reasonable price. I haven't looked at the details, nor have I checked the licensing issues, but I found his web site @ www.mxg.com. [...] 26182 85 21_Re: SAS/DB2 and V6/V716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 21 Mar 2001 09:55:12 -0500508_iso-8859-1 Bill, Have you done a BIND or REBIND of the plan since going to V6? There was a thread earlier which said if you had existing plans/packages with the 'colon deficiency' they would continue to execute under V6 but could no longer be bound.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: BILL_GALLAGHER@PHL.COM [mailto:BILL_GALLAGHER@PHL.COM] Sent: Wednesday, March 21, 2001 9:27 AM To: DB2-L@RYCI.COM Subject: Re: SAS/DB2 and V6/V7 [...] 26268 26 29_Re: Java & DB2 (again) + SQLJ10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 21 Mar 2001 15:59:19 +0100348_us-ascii Hi Leslie...

I tested just 2 minutes ago SQLJ, but IBM sample doesn't work, i.e. all works fine and there are no errorz, but the result of

the query (a query on SYSIBM:SYSTABLES) is not displayed. I'll ask to our Java programmer, I think it's program error.

Anyway thanks for your infos, let's stay touch....:-)) [...] 26295 41 36_Re: NT, AIX/UDB-7.1/Database Replica20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Wed, 21 Mar 2001 15:57:07 +0100444_iso-8859-1 Hi there, another option is to use the db2move utility: in this way you can export all the tables for one database or you can chose the creator or some name criteria for the tables, I think it's very similar to Oracle export; then you can use db2move to import or load the tables in another database. You can find more informatons about that in the Db2 manuals, Command reference adn Db2 movement utilities. Fabrizio Napolitano [...] 26337 128 21_Re: SAS/DB2 and V6/V70_22_BILL_GALLAGHER@PHL.COM31_Wed, 21 Mar 2001 10:06:10 -0500510_us-ascii Eric,

I'm not responsible for installing or maintaining the SAS/DB2 product in our shop, but looking at the BOUNDTS in SYSIBM.SYSPLAN for the SASDB2E plan, it looks like it has not been rebound since we upgraded to DB2 v6 last April/May.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: Re: SAS/DB2 and V6/V7 Sent by: DB2 Data Base Discussion List [...] 26466 59 32_Table containing IDENTITY column13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM31_Wed, 21 Mar 2001 10:06:28 -0500422_us-ascii A table was created with the following command :

CREATE TABLE STAFF1 (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY , NAME CHAR(8) NOT NULL , SALARY DECIMAL(5,2), COMM DECIMAL(5,2))

The following actions were preformed : ---------------------------------------------------------- Insert one row . Value of EMPNO is 1.

Do a LOAD REPLACE with a few rows . Value of EMPNO starts with with 2 . [...] 26526 85 61_Re: How to get rid of 'Quiesce Share' status on 2 tablespaces20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Wed, 21 Mar 2001 16:05:31 +0100340_iso-8859-1 Hello, instead of using a null input file, you can use the TERMINATE option instead of INSERT or REPLACE, ie: load from ... TERMINATE into tbname; It works I used many times. If the tablescaces are in an exclusive quiesce you should use the same userid to quiesce them and then call the load termianate. Fabrizio Napolitano [...] 26612 122 38_DB2 DBA- Sr level- NC and FL Positions13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Wed, 21 Mar 2001 10:19:06 -0500397_iso-8859-1 Hi DB2 Friends-

Wanted to let you know that my DB2 DBA needs may be expanding soon-- I may have requirements both in the NORTH CAROLINA area and the FLORIDA area.

This DB2 department is a TOP FOCUS for the company's hiring efforts at the moment because of the large number of special PROJECTS that are going on and will continue to happen with DB2 as their database. [...] 26735 17 7_DSNDB0116_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Wed, 21 Mar 2001 10:36:27 -0500419_iso-8859-1 Hey Folks.. Can anyone tell me where I can find a schematic of DSNDB01... Tablespaces and associated indexes? Thanks....

Michael Dempsey UNC Health Care System

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 26753 141 70_Re: DB2-OS/390- Statistical Analysis of Tablespaces and Index spa ces.16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Wed, 21 Mar 2001 15:00:02 +0000489_us-ascii Jim,

same here, but it really would be great to get it onto the doc-list!

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

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

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER



no trees were killed in sending this message. However, a large number of electrons were seriously inconvenienced :-) [...] 26895 183 53_Re: Reorg at partition level with NPIs: Clarification11_Kwan, James18_James_Kwan@BMC.COM31_Mon, 19 Mar 2001 12:42:20 -0600685_iso-8859-1 Jim,

I think Dr. Ebert is referring only to IBM online reorg. With BMC's online reorg, NPIs will be reorganised.

Regards,

James

-----Original Message----- From: James Drewe [mailto:James.Drewe@AEXP.COM] Sent: Monday, March 19, 2001 11:56 AM To: DB2-L@RYCI.COM Subject: Re: Reorg at partition level with NPIs: Clarification



Dr. Ebert

If I may restate what you said, an NPI can only be reorganized (that is, its LEAFDIST reduced) either through a a complete tablespace reorganization or a complete NPI reorganization. Even with online reorganization, a 10 terrabyte tablespace or its NPI would not be practical to reorg. [...] 27079 46 11_Re: DSNDB0119_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 21 Mar 2001 10:14:38 -0600625_iso-8859-1 Hi Michael, DSNDB01 is in the Diagnosis Guide and Reference . . .

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 [...] 27126 25 49_Determining RACF groups via DB2 stored procedure?13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 21 Mar 2001 11:22:01 -0500372_us-ascii Has anyone interrogated RACF to determine if a user is connected to a particular RACF group via a DB2 stored procedure? We have an existing web app that is read-only. It will be enhanced to allow certain users to do updates. I would like to encourage the developer to call a stored procedure to determine if the user has the authority to perform the update. [...] 27152 58 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Wed, 21 Mar 2001 17:26:49 +0100331_us-ascii So does IBMs product, if you do an Index only REORG or a Full TS REORG. The question was about doing a REORG on a partition level (which is not the same as a Full TS REORG using parallelism on a partition level). Are you saying that BMC can REORG a single logical partition of an NPI? Can you tell me how that works? [...] 27211 48 56_Help with a table column that don't want to be EXPLAIN'd13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Wed, 21 Mar 2001 11:49:46 -0500335_- Hi listers, I was trying to explain the SQL statement shown below when I got a -206 and finally a -104 on a column that is in existence. The column "R_PROC_CD" is definitely defined on the table and there is no misspelling. Can someone shed some light why this could occur and how to correct the problem. The statement follows: [...] 27260 43 53_Re: Determining RACF groups via DB2 stored procedure?13_Pranav Sampat21_psampat@PACIFICEX.COM31_Wed, 21 Mar 2001 09:00:21 -0800388_US-ASCII One way would be to issue a SET CURRENT SQLID='RACF group'; this RACF group should be the one which has authority to update. If the statement goes not give SQLCODE of -551 then the user requesting an update has the authority to update. An easy workaround would be to issue an UPDATE and check for SQLCODE. If it fails for authorization then an approrpiate msg. can be sent. [...] 27304 66 60_Re: Help with a table column that don't want to be EXPLAIN'd0_18_mebert@AMADEUS.NET31_Wed, 21 Mar 2001 18:02:01 +0100611_iso-8859-1 Missing Periods, see below. You'll be showered with responses I guess...

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



From: Carlton Enuda on 21/03/2001 16:49 GMT

Hi listers, I was trying to explain the SQL statement shown below when I got a -206 and finally a -104 on a column that is in existence. The column "R_PROC_CD" is definitely defined on the table and there is no misspelling. Can someone shed some light why this could occur and how to correct the problem. The statement follows: [...] 27371 63 60_Re: Help with a table column that don't want to be EXPLAIN'd14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 21 Mar 2001 12:11:13 -0500640_iso-8859-1 Don't you need a period before R_PROC_CD in your where clause? Same for P_ID and B_MAJ_PROG_CD ???

-----Original Message----- From: Carlton Enuda [mailto:carltonenuda@HOTMAIL.COM] Sent: Wednesday, March 21, 2001 11:50 AM To: DB2-L@RYCI.COM Subject: Help with a table column that don't want to be EXPLAIN'd



Hi listers, I was trying to explain the SQL statement shown below when I got a -206 and finally a -104 on a column that is in existence. The column "R_PROC_CD" is definitely defined on the table and there is no misspelling. Can someone shed some light why this could occur and how to correct the [...] 27435 116 60_Re: Help with a table column that don't want to be EXPLAIN'd0_26_JCameron@MSI-INSURANCE.COM31_Wed, 21 Mar 2001 11:05:53 -0600439_iso-8859-1 I think you're missing periods before the column names in your WHERE clause.

John Cameron JCameron@msi-insurance.com







Carlton Enuda cc: Sent by: DB2 Subject: Help with a table column that don't want to be EXPLAIN'd Data Base Discussion List



03/21/01 10:49 AM Please respond to DB2 Data Base Discussion List [...] 27552 84 60_Re: Help with a table column that don't want to be EXPLAIN'd13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 21 Mar 2001 11:07:43 -0600552_iso-8859-1 Carlton,

You are missing a period between the qualifiers and the column names in the WHERE and ORDER BY clauses

WHERE (TESTDB1.R_RT_PROC_PV_MP_TB R_PROC_CD = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB P_ID = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB B_MAJ_PROG_CD = ?) ORDER BY TESTDB1.R_RT_PROC_PV_MP_TB R_RT_BEG_DT DESC;

Should be: WHERE (TESTDB1.R_RT_PROC_PV_MP_TB.R_PROC_CD = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB.P_ID = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB.B_MAJ_PROG_CD = ?) ORDER BY TESTDB1.R_RT_PROC_PV_MP_TB.R_RT_BEG_DT DESC; [...] 27637 77 60_Re: Help with a table column that don't want to be EXPLAIN'd16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Wed, 21 Mar 2001 12:12:56 -0500543_us-ascii You have a syntax error in the SQL statement.

Hint: It is somewhere on the WHERE clause: WHERE (TESTDB1.R_RT_PROC_PV_MP_TB R_PROC_CD = ?)

Look very carefully and you will find it.

Rick Molera











Carlton Enuda on 03/21/2001 11:49:46 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: RICHARD E MOLERA/SallieMae) Subject: Help with a table column that don't want to be EXPLAIN'd [...] 27715 127 60_Re: Help with a table column that don't want to be EXPLAIN'd10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 21 Mar 2001 11:13:23 -0600566_iso-8859-1 Carlton, Try putting the '.' in the column names in the where clause WHERE (TESTDB1.R_RT_PROC_PV_MP_TB * here * R_PROC_CD = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB * here * P_ID = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB * here * B_MAJ_PROG_CD = ?) ORDER BY TESTDB1.R_RT_PROC_PV_MP_TB * And here * R_RT_BEG_DT DESC;

WHERE (TESTDB1.R_RT_PROC_PV_MP_TB.R_PROC_CD = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB.P_ID = ?) AND (TESTDB1.R_RT_PROC_PV_MP_TB.B_MAJ_PROG_CD = ?) ORDER BY TESTDB1.R_RT_PROC_PV_MP_TB.R_RT_BEG_DT DESC; Thanks. Missy Case FDR TMDBA 701-275-6358 [...] 27843 27 20_location of DSNTPSMP11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Wed, 21 Mar 2001 17:07:12 -0000412_- All,

I am struggling to get the Stored Procedure Builder to work, despite IBMs presence on site. We think the problem may be that we do not have the compiled REXX DSNTPSMP in the right library.

Error is

Create stored procedure returns -430 [IBM] [CLI Driver][DB2] SQL0430N User defined function "PROCEDURE" (specific name "SYSPROC.DSNTPSMP") has abnormally terminated. SQLSTATE=38503 [...] 27871 94 60_Re: Help with a table column that don't want to be EXPLAIN'd0_24_William_O'Black@FFIC.COM31_Wed, 21 Mar 2001 09:13:59 -0800398_iso-8859-1 I'm not sure if this is your whole query, but it looks like you've got an extra comma before the FROM and you're missing periods after the table name in the where clause and order by statement.









Carlton Enuda @RYCI.COM> on 03/21/2001 08:49:46 AM

Please respond to DB2 Data Base Discussion List [...] 27966 94 60_Re: Help with a table column that don't want to be EXPLAIN'd13_Larry Jardine18_misdb2@CTGCORP.COM31_Wed, 21 Mar 2001 12:21:01 -0500499_ISO-8859-1 It appears you have a few syntax errors.

1. Extra comma before the FROM should be deleted. 2. Missing periods prior to each column name in the WHERE clause. 3. Missing period prior to the column name in the ORDER BY clause.



______________________________ Reply Separator _________________________________ Subject: Help with a table column that don't want to be EXPLAIN'd Author: DB2 Data Base Discussion List at internet Date: 3/21/01 11:49 AM [...] 28061 58 53_Re: Determining RACF groups via DB2 stored procedure?13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 21 Mar 2001 12:36:54 -0500611_us-ascii Pranav, I really like the SET CURRENT SQLID='RACF group' idea.

Thanks so much!

> -----Original Message----- > From: Pranav Sampat [SMTP:psampat@PACIFICEX.COM] > Sent: Wednesday, March 21, 2001 12:00 PM > To: DB2-L@RYCI.COM > Subject: Re: Determining RACF groups via DB2 stored procedure? > > One way would be to issue a SET CURRENT SQLID='RACF group'; > this RACF group should be the one which has authority to update. If the > statement goes not give SQLCODE of -551 then the user requesting an update > has the authority to update. > An easy workaround would be to issue an UPDATE [...] 28120 107 53_Re: Reorg at partition level with NPIs: Clarification11_James Drewe20_James.Drewe@AEXP.COM31_Wed, 21 Mar 2001 09:59:19 -0700347_us-ascii Dr. Ebert

Thank you for your insight.

I have been told by an IBM performance instructor that well designed DB2/OS390 tables have been able to handle as many as 1000 inserts per second. Based on this discussion, can I deduce that we cannot have large, very active NPIs that are organized?

IBM, are you listening? [...] 28228 19 53_Re: Determining RACF groups via DB2 stored procedure?14_subrata mondal25_subratamondal@HOTMAIL.COM31_Wed, 21 Mar 2001 17:38:46 -0000 28248 78 64_Re: DSNDB01 (and a bit of "DB2-OS/390- Statistical Analysis...")16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Wed, 21 Mar 2001 17:17:27 +0000546_us-ascii Michael,

you can find it in the Diagnosis Guide and Reference.

To every body on the list, I am one of the guilty people who asked to be "added to the list" before reading the last one of my 125 unread messages from the list today... please let's not get too stroppy about this, I agree that when I found out that about 3/4 of my unread messages were "please me too" I felt vaguely self-conscious, but ranting and raving is not good for our health (nor for many other things, I guess). ANYWAY, sorry I was one of those [...] 28327 26 7_DSNDB077_JABARAR19_JABARAR@OAKWOOD.ORG31_Wed, 21 Mar 2001 13:18:40 -0500548_iso-8859-1 Hello DB2 users,

We are Running DB2 4.1 on OS/390 2.6. I have a user who is trying to run a report in Cognos Impromptu through Shadow Direct and is receiving a -904 return code. The resource that is not available are the DB2 work file. We currently have 5 4K table spaces in DSNDB07, all with a primary allocation of 500 cylinders and secondary allocation of 5 cylinders. All table spaces are at 123 extents. My question is should I add another table space to DSNDB07 or can I do a IDCAMS ALTER ADDVOL to add another pack to [...] 28354 84 32_CCSID Error on trigger creation.22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Wed, 21 Mar 2001 10:52:45 -0800428_- Hi there,

This one is driving me crazy.. I successfully created this trigger in one of our test environments. When I try and create it in our production environment I get the following error:

CREATE TRIGGER CTSDB2.CIS03A01

AFTER INSERT ON CTSDB2.CIS_INVC_TYPE

REFERENCING NEW AS N

FOR EACH ROW MODE DB2SQL

WHEN (N.TYPE_OF_INVC_CD = 'S')

INSERT INTO CTSDB2.CIS_INVC_TYPE_S [...] 28439 52 11_Re: DSNDB0715_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 21 Mar 2001 12:54:49 -0600356_iso-8859-1 Assuming that the customer was the only one using DSNDB07, we usually ask them what on earth they were doing to use up that much DASD by sorting or whatever. If their SQL is from another planet then we help them tune it.

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services [...] 28492 49 11_Re: DSNDB0712_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Wed, 21 Mar 2001 13:57:07 -0500720_us-ascii Are you getting a 00D70014 error?







JABARAR @RYCI.COM> on 03/21/2001 01:18:40 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DSNDB07



Hello DB2 users,

We are Running DB2 4.1 on OS/390 2.6. I have a user who is trying to run a report in Cognos Impromptu through Shadow Direct and is receiving a -904 return code. The resource that is not available are the DB2 work file. We currently have 5 4K table spaces in DSNDB07, all with a primary allocation of 500 cylinders and secondary allocation [...] 28542 56 11_Re: DSNDB0719_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 21 Mar 2001 13:00:01 -0600593_iso-8859-1 The method to add space to DSNDB07 is in the System Admin Guide. However, seems that a single user needing over 2500 cyls might be an indicator that the SQL should be looked at to reduce the space required/rows returned/etc.

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 [...] 28599 57 11_Re: DSNDB0712_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 21 Mar 2001 21:15:50 +0200451_iso-8859-1 Hi, First check what the query is and whether the user really need a big sortwork file, otherwise you may find s/he sorts too many rows that are not needed at all.

You can stop dsndb07 / delete and redefine the datasets using more space / start dsndb07.

The point is to have the correct query and not to be dragged into always enlarging your dsndb07 - do it as a last resort, only after you are sure it is really needed. [...] 28657 56 11_Re: DSNDB0711_Vince Crose17_vcrose@US.IBM.COM31_Wed, 21 Mar 2001 14:08:49 -0500787_us-ascii Thank you

Vince Crose

S/390 Java System & Performance Test Project Lead Department GW6F Strategic Application Evaluation Test

Tie line 8-293-8217/ External (914)-433-8217 Internet address: vcrose@us.ibm.com





JABARAR @RYCI.COM> on 03/21/2001 01:18:40 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: DSNDB07





Hello DB2 users,

We are Running DB2 4.1 on OS/390 2.6. I have a user who is trying to run a report in Cognos Impromptu through Shadow Direct and is receiving a -904 return code. The resource that is not available are the DB2 work file. [...] 28714 61 11_Re: DSNDB0711_Vince Crose17_vcrose@US.IBM.COM31_Wed, 21 Mar 2001 14:09:59 -0500479_us-ascii Richard,

I haven't done DB2 in a while but you are at the very limit of extents for VSAM. That is all you are allowed................

Thank you

Vince Crose

S/390 Java System & Performance Test Project Lead Department GW6F Strategic Application Evaluation Test

Tie line 8-293-8217/ External (914)-433-8217 Internet address: vcrose@us.ibm.com





JABARAR @RYCI.COM> on 03/21/2001 01:18:40 PM [...] 28776 99 36_Re: CCSID Error on trigger creation.10_Mike Hsiao20_mike.hsiao@SABRE.COM31_Wed, 21 Mar 2001 13:17:49 -0600630_us-ascii Marty,

You need PQ42601/UQ49651 PTF to fix the problem.

Thanks, Mike Hsiao



"Killen, Martin W - CNF" wrote:

> Hi there, > > This one is driving me crazy.. I successfully created this trigger in one > of our test environments. When I try and create it in our production > environment I get the following error: > > CREATE TRIGGER CTSDB2.CIS03A01 > > AFTER INSERT ON CTSDB2.CIS_INVC_TYPE > > REFERENCING NEW AS N > > FOR EACH ROW MODE DB2SQL > > WHEN (N.TYPE_OF_INVC_CD = 'S') > > INSERT INTO CTSDB2.CIS_INVC_TYPE_S > > (KEY_NBR, TYPE_OF_BILL_CD, TYPE_OF_INVC_CD, EFF_START_TMST > > [...] 28876 57 11_Re: DSNDB0716_Michael McKinney16_mmckinne@CSC.COM31_Wed, 21 Mar 2001 14:25:26 -0500402_us-ascii Richard,

I ran into a similar problem at Kay Bee Toys (previous employer) using DB2 v4, Shadow Direct, and the BRIO software.

Our solution was to add more tablespaces. But we had to do it a couple times. The first allocation of new work tablespaces was not enough.

Mike







JABARAR @RYCI.COM> on 03/21/2001 01:18:40 PM [...] 28934 85 53_Re: Reorg at partition level with NPIs: Clarification11_Kwan, James18_James_Kwan@BMC.COM31_Wed, 21 Mar 2001 13:44:31 -0600338_iso-8859-1 Dr. Ebert

I am not saying BMC can reorg a single logical partition of an NPI. I don't think any utility can. What I was saying is BMC online reorg does not require to have a separate step to update NPI after the reorg. Ie we can 'practically' reorg a partition of large tablespace without the build-2 phase outage. [...] 29020 143 48_Re: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?0_22_BILL_GALLAGHER@PHL.COM31_Wed, 21 Mar 2001 14:50:09 -0500589_us-ascii Juan,

Two very good points. I wouldn't have a problem with the application using dynamic SQL with parameter markers to take better advantage of the caching. But if they have to make changes to the application to convert the most frequently executed SQL statements from purely dynamic SQL, they might as well go all the way to static SQL as opposed to dynamic SQL with parameter markers. From what they've told me about the application, they have no specific need to use dynamic SQL at all. The only reason they wrote it using dynamic SQL was because they THOUGHT they [...] 29164 41 19_Re: DB2 SQl Tunning12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 21 Mar 2001 21:56:52 +0200497_iso-8859-9 Hi, We use DB/Explain from Candle , but there are others. Compare and decide.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net isaacyassin@yahoo.com ----- Original Message ----- From: "Ali INAL" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, March 20, 2001 5:59 PM Subject: DB2 SQl Tunning



Is there any DB2 tool for application development, SQL tunning except for IBM Client tool?? Which one do you recommend? [...] 29206 18 64_DB2-OS/390- Statistical Analysis of Tablespaces and Index spaces13_Dubeau, Denis19_DUBEADE@DSHS.WA.GOV31_Wed, 21 Mar 2001 11:46:54 -0800399_iso-8859-1 Jim, I'm very much interested in this REXX utility if you are still distributing it.

Thanks in advance, Denis

================================================ 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. 29225 76 60_Re: Help with a table column that don't want to be EXPLAIN'd13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Wed, 21 Mar 2001 12:05:32 -0500465_ISO-8859-1 You don't say, but I'll assume this is DB2 for OS/390 and you're doing your Explain from SPUFI.

First I'd look in your SPUFI dataset member for either: (1) Hex or invalid non-printing characters on the line(s) surrounding those where the column is mentioned; (2) remembering that SPUFI only processes columns 1-72, so anything beyond that gets truncated; (3) check the way you cut/paste-d the SQL into the member. Your WHERE clause predicate [...] 29302 33 38_Cost of using DATA CAPTURE (CHANGES) ?10_Mark Labby16_mlabby@PHEAA.ORG31_Wed, 21 Mar 2001 15:04:43 -0500319_us-ascii We have been asked to look into using Data Propagator to start moving data around to some remote sites. One of the things that we see that we would be required to do is to use DATA CAPTURE (CHANGES) on the tablespaces which they want to keep updated. The Administration Guide for DB2v6 for OS/390 states: [...] 29336 47 22_Re: Java & DB2 (again)12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 21 Mar 2001 12:09:11 -0800510_us-ascii I can send this for Version 7 of UDB. I'm not running V6. Why don't you upgrade to V7? I think many of your problems might go away with V7? I know it fixes a number of Java related problems. --- "Bell, Raymond W" wrote: > Colleagues, > > Not having a lot of joy here. Could someone fortunate enough to have > DB2 > UDB V6 running nicely on Windows 98 do me the great service of > sending me: > > 1) a copy of their autoexec.bat > 2) a copy of their dbm cfg > 3) [...] 29384 111 36_Re: CCSID Error on trigger creation.11_Jakobs, Jef18_JJakobs@HARTIC.COM31_Wed, 21 Mar 2001 14:06:21 -0600382_iso-8859-1 Marty,

I've run into something similar.

Don't remember why, but decided to put everything starting at "FOR EACH ROW" on one line (creating one very long line), and the error went away.

From others I think I understood that DB2 can have problems differentiating semicolons and "end of statement" characters.

Try it and see what happens :) [...] 29496 139 36_Re: CCSID Error on trigger creation.22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Wed, 21 Mar 2001 12:34:39 -0800630_- Thanks for the suggestion, but alas, I get the same error..

Marty Killen SR DB2 DBA CNF INC Information Technology 503-450-2681 - CNF INC 503-450-6038 - Conway e-mail - killen.martin@cnf.com

"Kind words can be short and easy, but their echoes are truly endless" Mother Theresa

> -----Original Message----- > From: Jakobs, Jef [SMTP:JJakobs@HARTIC.COM] > Sent: Wednesday, March 21, 2001 12:06 PM > To: DB2-L@RYCI.COM > Subject: Re: CCSID Error on trigger creation. > > Marty, > > I've run into something similar. > > Don't remember why, but decided to put everything > starting at "FOR EACH ROW" on one [...] 29636 56 42_Re: Cost of using DATA CAPTURE (CHANGES) ?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 21 Mar 2001 20:37:21 -0000272_- Hi mark -

A few years ago when I was a DBA, we ran into a project too where we were affraid of HOW much Data Capture Changes would cost. We meassured everything WITHOUT Data Capture for a week - then we ALTER'ed all the tables and did the same meassurement. [...] 29693 141 37_Re: Load question from prod. to test.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Wed, 21 Mar 2001 14:30:46 -0600526_- Hi Tim,

Thanks for your sugession... but i have a three columns in TAB1 in test region which is already existing. but the unloaded data will be from prod. which is of only two columns. actually in the test table already i have a data.... what i am doing is i deleting all the records before i load the prod. data using load utility. the problem is since prod. data has only two columns it is kicking me out. is there any way i can unload the data from prod. which can be compitable to test region with the three [...] 29835 134 36_Re: CCSID Error on trigger creation.22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Wed, 21 Mar 2001 13:29:43 -0800431_- Thanks Mike.. I talked with our SYSPROG's and we do not have these applied to our production environment yet.. They were working on getting these applied, but hadn't got there yet.. Thanks for the quick response.. I did hear from one other indvidual that they were having the exact same problem. It has to do with our production tables being created around the V4 time and our test tables are much younger... Thanks again.. [...] 29970 31 33_DB2 connect v5 to v7 upgrade woes0_24_lightsey@ITS.STATE.MS.US31_Wed, 21 Mar 2001 15:42:58 -0600532_us-ascii My main user is looking to upgrade his runtime clients from db2 connect v5 to v7 and has hit a snag - the machines seem to be able to communicate with the mainframe but get a -440 on any call to any stored procedure(s). Putting the runtime client and appropriate configs back to v5 allows all to run as smoothly as normal again, trying again on the v7 yields the -440 again. Other client machines have v6 connect on them and they work fine, too. I don't have access to any of these client machines and no one has seen [...] 30002 189 37_Re: Load question from prod. to test.64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 21 Mar 2001 15:47:58 -0600498_ISO-8859-1 Kumar, the explanation that Tim gave you should work just fine. What messages are you getting when it "kicks you out?"

What are you using to unload the production table? If you are using the IBM sample program DSNTIAUL, it will generate LOAD DATA control cards that you will have to modify. If running DSNTIAUL, use the PARM=SQL option and code the full SELECT statement in SYSIN. When coding the SELECT statement, add a constant integer value to your select list like this: [...] 30192 45 16_Re: Use Of Nulls14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Wed, 21 Mar 2001 11:02:02 -0600564_iso-8859-1 With all this discussion about NULLs lately I just have to add my two cents worth on the topic. Nulls have some legitimate uses. As with all things, you should understand how they work before using them.

And I do not think the answer can ever be "We avoid NULLs altogether so we never have to deal with them." For folks with that mindset, did you know that you may have absolutely no columns defined as nullable in any of your databases, and still get a null result back from an SQL query? For example, consider the following SQL statement: [...] 30238 66 37_Re: DB2 connect v5 to v7 upgrade woes0_20_John_Lendman@FPL.COM31_Wed, 21 Mar 2001 17:11:22 -0500391_us-ascii A -440 is store procedure not found. My guess is the store procedures are not in the new version 7 catalog.

John





lightsey@ITS.S TATE.MS.US To: DB2-L@RYCI.COM Sent by: "DB2 cc: Data Base Subject: DB2 connect v5 to v7 upgrade woes Discussion List"



03/21/01 04:42 PM Please respond to "DB2 Data Base Discussion List" [...] 30305 24 28_DB2 DBA Position, Durham, NC16_Jeffrey A Agosta20_agost003@MC.DUKE.EDU31_Wed, 21 Mar 2001 15:48:14 -0600513_- Duke University Medical Center has a permanent full time opening for an experienced senior level DB2 DBA.

Required skills: Minimum 4 years DB2 on OS/390, TSO/ISPF, JCL, DB2.

Preferred Skills: Sybase, IMS, DB2 Performance Monitor, Platinum Tools, REXX, SMP/E, CICS.

Responsibilities: Day to day DB2 operations, RDBMS and peripheral software upgrades, 3rd party and in-house written tools/utilities maintenance, disaster recovery, tuning, on-call support, and automation of daily tasks. [...] 30330 48 42_Re: Cost of using DATA CAPTURE (CHANGES) ?17_Demetris Kasheris19_DKasheris@LAIKI.COM31_Wed, 21 Mar 2001 23:31:59 +0200652_us-ascii Need help on improving the sort time required in running queries on datawarehouse tables in V6 DB2.

The sample query looks like this:

SELECT AGE_RNGE,COUNT(*) FROM table1,table2 WHERE table2.CUSTAGE BETWEEN FROM_AGE AND TO_AGE GROUP BY AGE_RNGE;

The measurements were as follows:

SQL_CALL STMT# SQL MXRC INDB2_TIME INDB2_CPU -------------------- ----- -------- ---- ------------ ------------ PREPARE 00210 1 0 00:00.071897 00:00.021576 OPEN CURSOR 00210 1 0 04:12.660976 00:29.828050 FETCH 00210 3 +100 00:00.000774 00:00.000773 CLOSE CURSOR 00210 1 0 00:00.000059 00:00.000059 Total 04:12.733707 00:29.850460 [...] 30379 153 36_Re: CCSID Error on trigger creation.10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 22 Mar 2001 08:31:37 +1000372_ISO-8859-1 Hi Marty,



We had the same problem on our system. We found the PTFs but they had not yet been applied.

The problem is due to the CCSID of pre v5 objects being 0. One alternative we have implemented is to ALTER the CCSID of the database and tablespace to 37.

ALTER DATABASE CCSID 37; ALTER TABLESPACE CCSID 37; [...] 30533 60 15_Reduce CPU Time11_Moore, Tony15_TMoore@IKON.COM31_Wed, 21 Mar 2001 16:31:33 -0500409_iso-8859-1 Yo List, I'm running CICS V4 / DB2 V4 on an OS/390 1.3 machine (yes, we're in the stone-age) and have a situation come up that I was wondering if you folks could pass a few suggestions. Some application changes were moved in this past weekend that has caused our CPU utilization to go through the roof. The change was not in the SQL code, but in the number of times the statement is executed. [...] 30594 61 19_Re: DB2 SQl Tunning12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Wed, 21 Mar 2001 16:32:38 -0600403_iso-8859-1 A tool called APPTUNE from my company, BMC Software, Inc. is used by hundreds of companies to tune SQL for UDB on OS/390. The product provides English explanations of SQL, recommendations on changes for performance improvements, statistics on SQL executions and object level access information. If you want to find out more details you can get information of the product at www.bmc.com. [...] 30656 56 37_Re: DB2 connect v5 to v7 upgrade woes10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 22 Mar 2001 08:43:16 +1000382_ISO-8859-1 Hi Bruce,

U might like to run a CLI trace on ur client and see what procedure is actually being called. (We have been helped by these traces in the past). U might find a SELECT from SYSIBM.SYSROUTINES statement (assuming ur mainframe DB2 version is v6) with appropriate predicates which might be of help. An omegamon trace might also be helpful to find this. [...] 30713 187 37_Re: Load question from prod. to test.0_19_Tim.Lowe@STPAUL.COM31_Wed, 21 Mar 2001 17:00:55 -0600372_us-ascii Kumar, If you unload your production table using DSNTIAUL with the SQL parm, using a sysin statement like this: SELECT A.*, 0 AS C3 FROM T1 A ; Then,your production unload file would now contain 3 columns(C1 and C2 from your table, and C3 with a literal 0), your load control cards would also specify 3 columns, and you could load to test without a problem. [...] 30901 27 19_Re: DB2 SQl Tunning10_Deb Jenson20_deb.jenson@QUEST.COM31_Wed, 21 Mar 2001 16:36:34 -0600509_- Aliia, Quest Software offers an excellent SQL Tuning tool available for both DB2 UDB and DB2 on OS/390. This tool provides not only the explain access path and intelligent tuning advice, but goes beyond other explain products by providing the facilities that allow you apply the advice and compare the execution results against the original SQL. You can create as many modified versions of the original SQL statement and continue to perform comparisons until you have tuned the SQL to meet your needs. [...] 30929 46 43_Using Identity_Val_Local() function in SQLJ10_Roger Hecq19_Roger.Hecq@MSDW.COM31_Wed, 21 Mar 2001 18:03:14 -0500574_us-ascii We have an SQLJ program which is doing an insert of a row with an identity column. After doing the insert, they want to use the Identity_Val_Local() DB2 built-in function to retrieve the value of the identity column (see below). We changed the host variable name (:identity) but that did not change the result. If we remove the Set statement, the program runs successfully. From reviewing the DB2 JAVA manual, I get the impression that the SET :hv statement is not supported. Is this correct? We are running DB2 OS/390 V6 at the 0008 (August 2000) cumm level. [...] 30976 70 22_Re: Java & DB2 (again)15_Donna O'Connell26_donna.oconnell@SAFEWAY.COM31_Wed, 21 Mar 2001 15:07:39 -0800311_us-ascii I would be interested in receiving the information for UDB Version 7 for Windows ME. I had tried to install UDB Version 6 under Windows 98 SE- and could not get the control center to work. I was told that this was a security issue - because I do not have a password when I bring up Windows 98 SE. [...] 31047 37 22_Re: Java & DB2 (again)15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 22 Mar 2001 10:11:21 +1100328_- Leslie,

Oh, come on. That was kinda fun, doing the OS/2 thing with DB2 2.1 (was that the version?). Not having some Big Iron around made a nice change. I actually enjoyed it; didn't you? Wonder how Andrew and the guys are doing...

Good luck with Java. If you can send me that stuff I'd (still) be grateful. [...] 31085 68 22_Re: Java & DB2 (again)15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 22 Mar 2001 10:14:46 +1100311_- Yes, please. V7 is better than nothing. I don't really want to upgrade because a) I want V6 to work, and b) I'd like to do the certification guide with the matching software. Maybe I should just give up and go the V7.1 way, but not until I've banged my head against a brick wall a few more times on V6.1 [...] 31154 87 16_Re: Use Of Nulls12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 22 Mar 2001 10:20:10 +1100549_- Additionally according the good book "V6 SQL Ref Guide" Chapter 4 Col Functions Verse 8

"... and the people did feast on the tomatoes, artichokes and ..." " Yes well skip a bit please brother"

Apologies I've watched far too much Monty Python to be healthy.

Anyway the "good" book says that NULLS can be omitted from some calculations, for example like AVG! check a table where you numeric nullable data for eg the test I just ran Col A in Tab B has a count of 48. 24 values are null and 24 are "2". The AVG calculates as [...] 31242 101 22_Re: Java & DB2 (again)10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM31_Wed, 21 Mar 2001 17:44:55 -0600564_- I had similar problem, I could solve this by calling from dos window "db2logon" and Log-in with any user name and password.

> ---------- > From: Donna O'Connell[SMTP:donna.oconnell@SAFEWAY.COM] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, March 21, 2001 5:07 PM > To: DB2-L@RYCI.COM > Subject: Re: Java & DB2 (again) > > I would be interested in receiving the information for UDB Version 7 for > Windows ME. > I had tried to install UDB Version 6 under Windows 98 SE- and could not > get the control center to work. > I was told that [...] 31344 133 49_DELAWARE VALLEY DB2 USERS GROUP MEETING 3/30/200118_Mulligan, Thomas J30_Thomas.Mulligan@PHL.BOEING.COM31_Wed, 21 Mar 2001 18:53:42 -05001148_ISO-8859-1 DELAWARE VALLEY DB2 USERS GROUP ____________________________________________________________________________ _ MEETING DATE: Friday, March 30th, 2001 LOCATION: Dave & Buster's Christopher Columbus Boulevard Philadelphia, PA STARTING TIME: 9:00 AM REGISTRATION: 8:30 AM - 9:00 AM COST: ADVANCE REGISTRATION: MEMBERS $30, NON-MEMBERS $40 WALK IN REGISTRATION: MEMBERS $50, NON-MEMBERS $60 ____________________________________________________________________________ _ AGENDA

09:00 - 09:15 Opening Remarks Rachel Murawski, Chairperson DVDUG ____________________________________________________________________________ _ 09:15 -10:30 Pools 2000: DB2 Caching Mechanisms Willie Favero: BMC ____________________________________________________________________________ _ 10:30 - 10:45 BREAK ____________________________________________________________________________ _ 10:45 - 12:00 Data Replication Tim Bowders ____________________________________________________________________________ _ 12:00 - 12:15 Elections ____________________________________________________________________________ _ 12:10 - 01:00 LUNCH: Sponsored by BMC [...] 31478 36 31_Re: DDF Priority in a WLM World14_Peter Schwarcz26_Peter_Schwarcz@BIGPOND.COM31_Wed, 21 Mar 2001 17:30:49 -0600664_ISO-8859-1 It's time to answer my own question.

I have been reading the DB2 Adminiastraion Guide and in the chapter "Managing DB2 Threads" and the section "Using Workload Manager to set performance objectives" the following paragraph answers my question:

"The MVS performance objective of the DDF address space or the WLM- established stored procedures address spaces does not govern the performance objective of the user thread. As described in “MVS performance options for DB2” on page 614, you should assign the DDF address space and WLM-established stored procedures address spaces to an MVS performance objective that is similar to the DB2 [...] 31515 65 47_Re: Using Identity_Val_Local() function in SQLJ14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 22 Mar 2001 11:02:56 +1100727_iso-8859-1 Just as a bypass, does

SELECT IDENTITY_VAL_LOCAL() INTO :identity FROM SYSIBM.SYSDUMMY1

work?

/* standard disclaimer */ James Campbell Hansen Corporation -----Original Message----- From: Roger Hecq [mailto:Roger.Hecq@MSDW.COM] Sent: Thursday, 22 March 2001 10:03 To: DB2-L@RYCI.COM Subject: [DB2-L] Using Identity_Val_Local() function in SQLJ



We have an SQLJ program which is doing an insert of a row with an identity column. After doing the insert, they want to use the Identity_Val_Local() DB2 built-in function to retrieve the value of the identity column (see below). We changed the host variable name (:identity) but that did not change the result. If we remove the Set [...] 31581 52 36_Re: Table containing IDENTITY column14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 22 Mar 2001 11:03:14 +1100456_iso-8859-1 QMF V6 is incapable of handling several new facilities of DB2 V6 (eg LOBs and ROWIDs). It simply would not surprise me if it cannot handle a 'generate always' identity column.

/* standard disclaimer */ James Campbell Hansen Corporation

-----Original Message----- From: Jaydeep Ghosh [mailto:Jaydeep_Ghosh@PROGRESSIVE.COM] Sent: Thursday, 22 March 2001 2:06 To: DB2-L@RYCI.COM Subject: [DB2-L] Table containing IDENTITY column [...] 31634 106 61_Re: Help with a table column that don't want to be EXPLAI N'd14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 22 Mar 2001 11:03:44 +1100589_iso-8859-1 Carlton,

by now I assume you've fixed the problem (been given a fish) - but let me tell a debugging factoid (teach you to fish): Notice the line

DSNT416I SQLERRD = 0 0 0 -1 1042 0 SQL DIAGNOSTIC INFORMATION

SQLERRD(5) (1042 in this case) indicates the position of a syntax error in a PREPARE or DESCRIBE statement. Noting that you are using SPUFI which simply concatenates 72 byte lines together - including blank lines before the SQL (unlike DSNTEP2 which squeezes out unnecessary blanks), we can divide 1042 by 72 to get line offset 14, position 34. [...] 31741 152 18_Re: Archive Log PB10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 22 Mar 2001 10:07:43 +1000336_ISO-8859-1 Hi Stephane,

U can define a new group (TSO ADDGROUP DSN610 supgroup(xxxxx)) where "xxxxx" is the superior group under which u wish to define DSN610. U can probably get ur RACF security administrator to do this.

Please refer to Db2 v6 Admin Guide chapter 3 section 3.5 (Protecting data sets) for more info. [...] 31894 56 61_Re: Help with a table column that don't want to be EXPLAI N'd0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 21 Mar 2001 18:33:02 -0600269_us-ascii James, thanks a 'gazillion'!

That's such a great, relevant, tip for me that even though the end of the work day was a hour ago, I had to log back into DB2 and try it for myself. I immediately wrote up a tip and posted it for our developers to see! [...] 31951 167 26_FW: [DB2-L] Archive Log PB14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 22 Mar 2001 12:02:06 +1100620_iso-8859-1 Stéphane

Perhaps then, RACF has been set up with PROTECTALL (ie RACF wants every dataset protected). Logon using a userid with racf SPECIAL, issue TSO command SETROPTS LIST to see. To create a generic profile (ie one that will protect every dataset whose name conforms to its pattern), use, for example, ADDSD 'DSN610.*' UACC(READ) ADDSD 'DSN610.ARCHLOG*.*' FROM('DSN610.*') UACC(NONE) PE 'DSN610.ARCHLOG*.*' ID(SYS1) ACCESS(ALTER) However, there are so many variables in the way you have set things up that I just cannot give you specific instructions without specific details about your system. [...] 32119 138 35_Is the best reorg the one not done!11_James Szabo18_jim.szabo@CORE.COM31_Wed, 21 Mar 2001 22:46:24 -0500379_iso-8859-1 Dear list,

Looking for a discussion on this point.

Much is made about the issues re: DB2 for OS/390 V6 online reorg - long BUILD2 phase, inability to reorg NPI partitions, etc. Some of this may be fixed in V7, but I would proceed with caution using all that new code.

I ask: do DBAs rely on the reorg utility too much? I had one DBA tell me: [...] 32258 18 29_Rebalancing Partitioning Keys12_John Piccoli16_jpiccoli@LOOK.CA31_Wed, 21 Mar 2001 23:12:08 -0500418_us-ascii Does anyone know of any technique to rebalance partitioning keys(numeric) that don't involve a series of counts on key ranges - DB2 for OS/390?

John Piccoli CDS

================================================ 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. 32277 108 19_Re: Reduce CPU Time13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 21 Mar 2001 22:17:57 -0600339_iso-8859-1 Tony,

The reason the OPEN is so quick is because for this SQL (no sorts, materialization etc), the data retrieval is ocurring with each fetch, hence the CPU and elapsed time increased there. Had you materialized the result set at OPEN CURSOR, then OPEN would be the highest cost, and fetches relatively inexpensive. [...]