1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2001, week 5 2 58 21_Re: Workfile Prefetch14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Mon, 29 Jan 2001 05:04:40 -0600307_iso-8859-1 When you say sort pool shortage, do you mean the value that is controlled by ZPARM SRTPOOL? If so, the answer is 'it depends...', but is often 'no'.

If you have a bufferpool dedicated to your sort database (DSNDB07) and you get prefetch disabled, then look at the following in order: [...] 61 20 31_Re: V6 on OS/390: wrong index ?14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Mon, 29 Jan 2001 05:08:03 -0600345_iso-8859-1 If all other things are equal, DB2 will use the index it finds first in the SYSINDEXES table. This normally means it uses the index with the lowest OBJID.

If you define INDEX1, then INDEX2 and all else is equal, DB2 will use INDEX1. If you then drop INDEX1 and re-define it, DB2 will probably choose INDEX2 in the future. [...] 82 21 14_catalog mining12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Mon, 29 Jan 2001 11:15:26 -0000536_- ALL, Can anyone in the list tell me about any DB2 website which talks about catalog mining with sample SQLs. Thanx.

Regards Rakesh

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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 104 53 18_Re: catalog mining16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 29 Jan 2001 16:55:40 +0530667_- Go to www.idug.org and look at the latest IDUG Journal.

However, If Suresh Sane (Writer of Catalog Mining) is going through my this mail, i am still awaiting the reply from him regarding my questions related to his article.

Regards, Sanjeev

> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Monday, January 29, 2001 4:45 PM > To: DB2-L@RYCI.COM > Subject: catalog mining > > ALL, > Can anyone in the list tell me about any DB2 website which talks about > catalog mining with sample SQLs. > Thanx. > > Regards > Rakesh > > _________________________________________________________________________ > Get [...] 158 172 33_Re: Reusing tablespace partitions14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 29 Jan 2001 12:42:11 -0000469_iso-8859-1 and of course, the other warning should be "Even if what you have done seems to work fine NOW, there is no guarantee that it will continue to do so"

A great example was a looonnnggg time ago when I 'discovered' that you could image copy tablespaces that were started RO! Great for consistency across a number of objects. Start them all RO and then run a series of image copies. All tables are available for SELECT but no-one can change anything. [...] 331 119 33_Re: Reusing tablespace partitions14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 29 Jan 2001 12:42:19 -0000528_iso-8859-1 Michael,

You are right - I was slightly confusing myself!

It's the PAGE numbers (which are part of the RID of course) which indicate which partition a particular page belongs. However, our testing seems to indicate that Db2 doesn't check these (at present....) as rigorously as it should/could do!

Phil G

-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@BMC.COM] Sent: 26 January 2001 14:55 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Reusing tablespace partitions [...] 451 26 28_Stored Procedure 5.1 and 6.111_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Mon, 29 Jan 2001 10:40:56 -0500295_us-ascii We are running DB2 version 5.1 on OS390, and we have a group interested in using stored procedures. I would like to know if the Stored Procedure Builder shipped out with DB2 UDB for NT/9x developers kit can be use to develop stored procedures to run on DB2 Version 5.1?. We are 4 months 478 97 12_Re: 2G limit16_Alison Pelletier26_alison_pelletier@YAHOO.COM31_Mon, 29 Jan 2001 07:41:52 -0800353_us-ascii Francis,

Thank you for your reply, but it does bring up another question I have. When DB2 tries to extend to the second dataset, does it require that primary allocation to be contiguous? It appeared to me at the time that the primary allocation could have been fulfilled easily with 5 extents, just not contiguous in one big chunk. [...] 576 23 20_Re: 4K work datasets0_26_truman.g.brown@VERIZON.COM31_Mon, 29 Jan 2001 10:59:35 -0500585_us-ascii I have to disagree with the post that said a few large 4K's are better, and that if the GP/ I/O was > 500 your dedicated VBP was too large.

Remember that 4K's contain multiple work pagesets, and that in storage sorts and access from the BP without I/O means performance for cursor operations. We have several large active DB2's with 16 or more large 4K's, large (50,000 pg) dedicated 4K BP's, and several of these BP's are backed with HP's. These DB2's are tuned for online performance with lots of distributed access and the users are delighted with performance. [...] 600 25 20_DB2 Unicode support?14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Mon, 29 Jan 2001 11:29:24 -0500400_iso-8859-1 Hi All,

We are using UDB V7.1 on NT here. Our database needs to support UCS-2/UTF-8 in the future to store multi-language characters (like strings in Japanese, Chinese etc.). Is there something that needs to be done prior to and during creating the database to support this ? Does a column which will store the multi-language strings needs to be defined in any particular way? [...] 626 67 16_Re: VSAM RENAMES12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Mon, 29 Jan 2001 11:35:22 -0500334_- Ronald,

We have a similar application that is live 24x7 and there is a need to load data to denormalized tables to optimize some query performance. The following process is used (for 3 different "sets" of tables in this app) to allow continuous use by an online, while updating the data via utilities a few times a day. [...] 694 18 33_Retreiving the 30 smallest values15_Schaeffer, Dave28_dave.schaeffer@HONEYWELL.COM31_Mon, 29 Jan 2001 09:58:22 -0700503_- Does anyone have a example of SQL that will return the 30 smallest values of a table? We are a OS/390 shop running V5.1 of DB2.



Dave Schaeffer Database Administrator Bendix CVS (a division of Honeywell Inc.) E-Mail: Dave.Schaeffer@Honeywell.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. 713 60 37_Re: Retreiving the 30 smallest values11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 29 Jan 2001 11:18:15 -0600540_- Dave:

Try this (smallest 30 values of column c2)-

SELECT T1.C1, T1.C2 FROM MYTABLE T1 WHERE 30 > (SELECT COUNT(*) FROM MYTABLE T2 WHERE T2.C2 < T1.C2) ORDER BY T1.C2

A couple of notes: 1. The smallest 30 can return more than 30 rows if C2 can have duplicate values (in general, it will). For example, if I have 29 values <= 100 and the next highest value is 150 but occurs 10 times, each of these 150's will be displayed in the 30 smallest - so your answer will actually consist of 39 values not 30. 2. The best [...] 774 40 33_Data Propagator OS/390 Spill File13_Moss, William15_MossW@AETNA.COM31_Mon, 29 Jan 2001 12:19:25 -0500540_iso-8859-1 I'm learning about Data Propagator in OS/390 and I have a question about the spill file.

The Apply task has this DD statement:

//ASNASPL DD DSN=&&ASNASPL, // DISP=(NEW,DELETE,DELETE), // UNIT=SYSDA,SPACE=(CYL,(100,1000),RLSE), // DCB=(RECFM=VB,BLKSIZE=6404)



These the files that get allocated:

IGD101I SMS ALLOCATED TO DDNAME (ASNASPL ) DSN (SYS01029.T095232.RA000.J8DB6GA3.ASNASPL.H01 )

IGD101I SMS ALLOCATED TO DDNAME (ASNAS000) DSN (SYS01029.T095310.RA000.J8DB6GA3.ASN000.H01 ) [...] 815 165 31_Re: V6 on OS/390: wrong index ?9_Mark Ruhe19_Mark.Ruhe@QUEST.COM31_Mon, 29 Jan 2001 09:25:02 -0800379_iso-8859-1 JP,

Are COLY and COLZ in index2? If so this would explain why Index2 is being chosen as the query would be index only. Just a thought.

Mark Ruhe Quest Software

-----Original Message----- From: Jean-Paul Coenen [mailto:jpc_db2@HOTMAIL.COM] Sent: Sunday, January 28, 2001 6:43 AM To: DB2-L@RYCI.COM Subject: [DB2-L] V6 on OS/390: wrong index ? [...] 981 83 37_Re: Retreiving the 30 smallest values10_Shukla, JR18_JR.Shukla@BNSF.COM31_Mon, 29 Jan 2001 13:42:11 -0600435_iso-8859-1 If you have multiple rows with the same value of C2, this query would not work. I would change the sub-select to read

SELECT COUNT(DISTINCT(T2.C2)) FROM MYTABLE T2 WHERE T2.C2 <= T1.C2

J.R. Shukla jr.shukla@bnsf.com

-----Original Message----- From: Suresh Sane [mailto:data_arch@HOTMAIL.COM] Sent: Monday, January 29, 2001 11:18 AM To: DB2-L@RYCI.COM Subject: Re: Retreiving the 30 smallest values [...] 1065 80 20_Using a Index or Not0_20_John_Lendman@FPL.COM31_Mon, 29 Jan 2001 15:19:31 -0500301_us-ascii I just ran across several SQL calls that choose not to use an index that was created just for them to use in this SQL. I know there is a lot of information that you need to make a somewhat intelligent guess at what caused my problem, but I will try to give you some limited information. [...] 1146 18 24_Re: Using a Index or Not0_26_truman.g.brown@VERIZON.COM31_Mon, 29 Jan 2001 15:23:36 -0500364_us-ascii John,

A quote from "Optimizer in Wonderland":

"'Twas brillig in the slithey toves..."

George

================================================ 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. 1165 32 31_Re: V6 on OS/390: wrong index ?16_Jean-Paul Coenen19_jpc_db2@HOTMAIL.COM31_Mon, 29 Jan 2001 21:30:06 +0100389_- Thanks, Mark Ruhe and Edward Vassie !

No Mark, only one of three columns is in index2.

I'll verify the OBID's tomorrow morning, Edward.

JP ------------------------------------------------------------------ If all other things are equal, DB2 will use the index it finds first in the SYSINDEXES table. This normally means it uses the index with the lowest OBJID. [...] 1198 42 23_Re: parallel processing12_Dallas Focht21_dallas.focht@PMIC.COM31_Mon, 29 Jan 2001 15:14:57 -0600331_us-ascii This is a little late, but we had problems with parallelism in DB2 V6 because of the enourmous number of SET CURRENT DEGREE statements being executed. Lookng at the explain output, it looks like this is happening when the ACCESS DEGREE could not be determined at bind time. At this time we are not using parallelism. [...] 1241 116 37_Re: Retreiving the 30 smallest values11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 29 Jan 2001 15:29:28 -0600409_- JR,

David's set up may be such that duplicates may not be present making this discussion academic but still...

Consider the table to contain 10 rows with a value of 100, 10 with 200 and 10 with 300 (all others being higher than 300). What is my top 30 list? Should I not stop at 300? I think so. If you use DISTINCT, you will get many more in the list that (in my opinion) do not belong. [...] 1358 56 12_Re: 2G limit14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 30 Jan 2001 09:04:44 +1100292_iso-8859-1 Alison,

As far as VSAM is concerned, the second dataset is just another cluster - so the rules for allocating space are the same as for the first dataset. Hence, the primary extent of the second dataset can use up to 5 extents - but they have to be on the same volume. [...] 1415 14 51_Daniel Sullivan/ZI/USA/Zurich is out of the office.15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM31_Mon, 29 Jan 2001 09:36:42 -0600365_us-ascii I will be out of the office from 01/29/2001 until 01/30/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. 1430 81 23_Re: parallel processing17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Mon, 29 Jan 2001 16:15:03 -0600366_- Hi James,

Thanks for replying but we are not executing any SET CURRENT DEGREE statements. but i am wondering is sometime the job runs just like that with DEGREE (1). and some time it will take time more than that with DEGREE (YES) option.... can i put back to DEGREE (YES)???? or shall i test some other with DEGREE (1)????. can somebody help in this. [...] 1512 28 32_Am I connecting via ODBC or DRDA12_tim malamphy20_timalamphy@YAHOO.COM31_Mon, 29 Jan 2001 14:28:15 -0800548_us-ascii If I catalog an OS/390 database on my local NT UDB 7.1 EE system (with DB2 connect installed as well), and register it for ODBC, how do I know if I'm using DRDA or ODBC to make a connection? I'm under the impression that DRDA and ODBC are separate protocols lthough they both connect me to DB2, but some recent comments confused me. Any pointer to documentation describing the differences between ODBC and DRDA would be appreciated. I've got it all working, I just want to understand what the heck is going on. Thanks in advance. Tim [...] 1541 23 13_DB2 ESTIMATOR17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Mon, 29 Jan 2001 16:25:09 -0600416_- Hi Folks,

Can somebody help how to caluculate the space to create a tablespace in db2 v6. is there any specific tools to use or DB2 ESTIMATOR is good to use. if DB2 ESTIMATOR is good what is the site name to download.

Thanks KUMAR.



_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. [...] 1565 114 24_Re: Using a Index or Not15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 30 Jan 2001 11:10:17 +1100536_- I'm sure you'll get lots of replies for this one, but here's my brief one. If the values you've specified for those two columns aren't very unique (i.e. have a low filter factor) DB2 may well be deciding it's in its best interests to skip the index and just prefetch data straight from the table(space). If you had an index on SEX and said in a query, '...and SEX='F'...' this predicate only filters out roughly half the rows, so isn't really useful. Why go to both the index and the data when I (DB2) can cut my losses and just [...] 1680 47 17_Re: DB2 ESTIMATOR16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Mon, 29 Jan 2001 19:13:45 -0500614_us-ascii hi, site info http://www-4.ibm.com/software/data/db2/os390/estimate/downloads.html tom f







ravi kumar hassan on 01/29/2001 05:25:09 PM

Please respond to DB2 Data Base Discussion List

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







Hi Folks,

Can somebody help how to caluculate the space to create a tablespace in db2 v6. is there any specific tools to use or DB2 ESTIMATOR is good to use. if DB2 ESTIMATOR is good what is the site name to download. [...] 1728 123 38_Re: Mining the DB2 Catalog With SQL !!11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 29 Jan 2001 18:19:41 -0600412_- Sanjeev – first of all, thank you for the positive feedback.

Here are my thoughts on some of the issues you have raised:

1. Proactive v/s Reactive: Granted that several shops remain in a fire-fighting mode but should not. I contend that a majority of performance problems can be predicted (provided stats and explain analysis is proper) so that only the few surprises need to be dealt with. [...] 1852 56 13_ODBC AND DRDA14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Mon, 29 Jan 2001 17:31:32 -0800535_- Tim, ODBC is an API. DRDA is a protocol used to connect to DB2 on OS/390 and other platforms. DB2 Connect installed as a gateway on an NT or AIX/UNIX server provides the Application Requestor portion of DRDA. Ths Application Server postion is provided by the host database you connect to. So you use ODBC to issue calls to a backend database such as Db2 for OS/390 via a DB2 Connect AR. So DRDA is used from the DB2 Connect server to host DB2. You are using ODBC and DRDA at the same time. Each provides a unique function. HTH. [...] 1909 169 24_Re: Using a Index or Not12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Mon, 29 Jan 2001 20:25:56 -0600375_iso-8859-1 I think this is close to the answer. Just let me add a point. Since this is a SAP system, DB2 does not see literals in the predicate; just host variables. This means the optimizer must guess at the selectivity. It assumes that 1/colcardf * number of rows will be returned. With a list of three terms in an in-list, it would be 3 * 1/colcarf * number of rows. [...] 2079 15 52_Cathy L Peck/ISG/CORP/Highmark is out of the office.10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 30 Jan 2001 01:00:47 -0500389_us-ascii I will be out of the office starting 01/29/2001 and will not return until 02/01/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. 2095 174 47_=?ISO-8859-7?Q?=C1=D0=3A_Using_a_Index_or_Not?=19_Alekos Papadopoulos13_apapad@NBG.GR31_Tue, 30 Jan 2001 08:43:12 +0200402_ISO-8859-7 Did you use parameter FREQVAL NUMCOLS 2 in the RUNSTATS? MANDT is well known for its low number of distinct values.

Alekos Papadopoulos M.Sc. System DB Administrator National Bank of Greece

-----Αρχικό μήνυμα----- Από: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Αποστολή: Τρίτη, 30 Ιανουαρίου 2001 2:10 πμ Προς: DB2-L@RYCI.COM Θέμα: Re: Using a Index or Not [...] 2270 37 44_Testing Disaster Recovery at your local site10_Lee Mandel16_DBMSUser@AOL.COM29_Tue, 30 Jan 2001 06:13:35 EST377_US-ASCII Hi All, Looking in IBM manuals (including Red books) I cannot find procedures for Testing Disaster Recovery at your local site before going to the Off-site location. Can someone point me to any books, procedures, or software that will assist testing before going to the Hot Site? How far can you test without having a complete DB2 Subsystem up and running. Thanks, Lee 2308 36 31_Datajoiner 2.1 Database Manager18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 30 Jan 2001 07:15:55 -0500582_iso-8859-1 HELP!! We are using Datajoiner and seems we have a major problem with the database manager configuration file. We can't get to datajoiner this morning and its running production. It keeps giving me a sql5005C error msg. No applications can run against it and I can't even log onto the datajoiner database.... Is anyone using this product and have any idea of how to get to the database manager configuration file or to restore it?? I can't get to the file from inside datajoiner. Keep getting error msgs. Is this something we have to restore or is there any command [...] 2345 117 23_Re: parallel processing19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 30 Jan 2001 07:09:17 -0600484_iso-8859-1 Kumar, IT DEPENDS! Please scan the db2-l archives on 'parallel' -- much good info there. The consensus opinion on parallelism is that it really only benefits certain queries with large answer sets and should always be compared against DEGREE(1) before deciding to use it. (The cost for setting up parallelism at run time can be considerable.) Run time variations usually end up being DB2 subsystem load issues (pool(s) availability), I/O subsystem and OS390 workload. [...] 2463 20 43_OS/390 DB2 Stored Procedure Debugging Tools9_Chu, Pius14_ChuP@CONED.COM31_Tue, 30 Jan 2001 08:53:12 -0500313_iso-8859-1 Compuware XPERITER came out a new feature to their existing product to debug the DB2 stored procedure. Is anyone out there know or use this product and comment on this? I like to know what are alternatives other than doing a "DISPLAY" within your program or using LE370 debugging tool. Thank you. [...] 2484 44 36_Re: Am I connecting via ODBC or DRDA11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Tue, 30 Jan 2001 09:18:03 -0500300_us-ascii I think you use both based on what application you are using to access the HOST data... If you use the DB2 Command line you may bypass the ODBC interface, if you use a product such as MS Access you must ODBC interface as well as DRDA... Here is some information for the DB2 Admin. book [...] 2529 34 20_Assigning a constant13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 30 Jan 2001 08:14:28 -0600558_us-ascii hi... we're attempting to load a table using DSNUTILB the column ORG_CDE, I would like to assign the initial value of 'B'. This value is not on the input file. the column is defined as not null is there anyway to do this???

DB2 V5



LOAD DATA RESUME NO REPLACE LOG NO INDDN SYSREC00 INTO TABLE DB2TEST.SAMPLE_TABLE ( ACCT_NUM POSITION( 25 ) CHAR( 12) , REGN_PROCS_CDE POSITION( 25 ) CHAR( 4) , ORG_CDE POSITION( 33 ) SMALLINT , SS_NUM POSITION( 259 ) CHAR( 9) , NAME_MID POSITION( 38 ) CHAR( 25) , NAME_NICK POSITION( 102 ) [...] 2564 44 49_Compuware's OS/390 DBA-XPERT Utility Manager?????14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM31_Tue, 30 Jan 2001 08:56:42 -0600619_iso-8859-1 I'd appreciate any experiences from other sites with DBA-XPERT on their usage of the Utility Manager feature to perform normal maintenance functions.

Reason for my inquiry: I have been implementing it at our site with reasonable success(v 5.6.2), but I am hitting some obstacles on some things like backing up the DB2 Catalog and tape refer-backs on some of our larger tablespaces.(we do not have any huge tablespaces) When I call support I get the impression that maybe it isn't widely used, since some of the limitations I've encountered I'd expect to be common situations but they have not be [...] 2609 54 24_Re: Assigning a constant12_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 30 Jan 2001 10:03:44 -0500517_US-ASCII It looks like ORG_CDE is not character it is SMALLINT. The IBM LOAD does not support providing a value other than default. I always advice the programmer to provide a value in the input LOAD file. If the load file is not in the same format as the table then you may choose another position in the file to load. The column will have garbage in it. You will then have to run a separate SQL Update statement to fix the column. Good luck. Troy Coleman Coleman Consulting, Inc. troycci@colemanconsulting.com [...] 2664 77 24_AW: Assigning a constant12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 30 Jan 2001 16:04:01 +0100779_iso-8859-1 Derez,

my idea might be not the best way but it works. Run your load job, take an image copy (because you're working with LOG NO), and then UPDATE DB2TEST.SAMPLE_TABLE SET ORG_CDE = 'B' WHERE "your where-clause";

HTH.

With kind regards - mit freundlichen Grόssen, G e o r g H . P e t e r 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 ---------------------------------------------------------------------- [...] 2742 83 48_Re: Testing Disaster Recovery at your local site0_20_John_Lendman@FPL.COM31_Tue, 30 Jan 2001 10:08:11 -0500430_iso-8859-1 Hey, Lee I not sure about any books, but we do here with a small LPAR on our test machine. We do have every thing up and running, but we have to schedule it when time permits. But most of our testing is done offsite. John





"Lee Mandel" cc: Sent by: "DB2 Subject: Testing Disaster Recovery at your Data Base local site Discussion List" [...] 2826 34 20_Assigning a constant19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US29_Tue, 30 Jan 2001 10:32:40 EST573_us-ascii Derez,

I notice that your ORG_CDE in the load control cards is defined as SMALLINT. So you may want to assign a numeric constant to it.

There are a few ways in which you could achieve what you want.

1. Edit the input file and replace the desired value in the appropriate columns in the input file itself. 2. If the data comes from another table from which it is loaded, then do an unload with 'PARM(SQL)' and give your Select statement with the constant instead of the column name for ORG_CDE. 3. Update the table after loading the table [...] 2861 68 28_Long names in UDB for OS/39012_Jacob Ganzel19_jacobg@SEMECH.CO.IL31_Tue, 30 Jan 2001 17:02:06 +0200410_x-user-defined Hello list.

Does version 7 (or next version) of UDB for OS/390 supports column and table names longer then 18 bytes ?

Thanks for reply. --------------------------------------------------------------- Jacob Ganzel Database Products . SEMECH Software Marketing Ltd.

3b Netanyahu St., OR YEHUDA, ISRAEL 60376 Email: jacobg@semech.co.il Tel.: +972-3-5333144 Fax.: +972-3-5333132 2930 63 24_Re: Assigning a constant13_Horacio Villa17_hvilla@TTI.COM.AR31_Tue, 30 Jan 2001 12:45:29 -0300599_iso-8859-1 Put the value you want in the input file. You can use SORT with OUTREC FIELDS. //SORTIN DD DSN=your.input.file,DISP=SHR //SORTOUT DD DSN=your.output.file,DISP=(,CATLG), // DCB=(LRECL=xx,RECFM=FB),SPACE=(CYL,(n,n),RLSE), // UNIT=SYSDA //SYSIN DD * SORT FIELDS=(....) OUTREC FIELDS=(1,267, CHECK THIS NUMBERS.... C'B') the value you want /*

Hope this helps. Horacio Villa -----Mensaje original----- De: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]En nombre de Derez D. Lusk Enviado el: Martes 30 de Enero de 2001 11:14 Para: DB2-L@RYCI.COM Asunto: Assigning a constant [...] 2994 109 24_Re: Assigning a constant13_Terry Shockey30_Terry.Shockey@OCSE.STATE.AR.US31_Tue, 30 Jan 2001 09:45:23 -0600452_iso-8859-1 another way to acomplish this if ORG_CDE was defined as a 1 byte character field would be;

1. TO specify NOT NULL WITH DEFAULT 'B' for column ORG_CDE when the table is defined note(thus specifying the specific default value for column ORG_CDE).

2. remove the reference to ORG_CDE from your load statement and the default constant "b" will be automatically inserted into that column as the default during the load process. [...] 3104 46 48_What kind of DBA does it take for an ERP system?13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Tue, 30 Jan 2001 09:53:06 -0600403_iso-8859-1 We are in the process of evaluating and chosing an ERP system for Sedgwick County, and I am curious about which of my team members should be assigned the primary responsibility for the database maintenance. The choice of ERP has been narrowed down (in alphabetical order) to PeopleSoft and SAP. (Please, no recommendations on which; the decision is up to the users and out of my hands.) [...] 3151 97 27_DB2 Connect 7.1 error on NT13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Tue, 30 Jan 2001 09:52:15 -0600347_US-ASCII We have a new NT server...it had 5.2 on it and all its databases defined that it could get to.... we then installed the upgrade to 7.1 with fixpac2 following the instructions and we receive error

"The list of databases could not be retrieved. SQL1038C An I/O error occurred while accessing the node directory. SQLSTATE=58031" [...] 3249 43 48_Re: Testing Disaster Recovery at your local site20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM29_Tue, 30 Jan 2001 11:04:34 EST376_US-ASCII Lee, Yes there is a step by step approach to testing DR at your local site without having a complete DB2 Subsystem to test on. You must use another set of BSDS. You can verify most DR jobs JCL, RBA Numbers, and Control cards before going to the Off-site location. Visit: http://www.recoveryknowledge.bigstep.com Look under Partial DR testing at your local site. [...] 3293 85 52_Re: What kind of DBA does it take for an ERP system?0_25_jwstimmel@DUKE-ENERGY.COM31_Tue, 30 Jan 2001 11:09:28 -0500459_us-ascii Do your shop a favor and assign the MOST EXPERIENCED DBA you have for this effort!!! Speaking from experience - DB2 DBA for PeopleSoft HR and Financials...inexperience will cost you dearly!!! We have felt that pain at our shop...









Terry McCombs cc: Sent by: DB2 Subject: What kind of DBA does it take for an Data Base ERP system? Discussion List [...] 3379 110 31_Re: DB2 Connect 7.1 error on NT12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Tue, 30 Jan 2001 10:12:30 -0600406_iso-8859-1 If you remember one of the local databases try cataloging it back to where it was -- i had this happen on one of the AIX boxes going from V5 to V6 and i just recataloged all the databases and i was ok

-----Original Message----- From: Mike Lawrence [mailto:mlawrence@ASCENSIONHEALTH.ORG] Sent: Tuesday, January 30, 2001 9:52 AM To: DB2-L@RYCI.COM Subject: DB2 Connect 7.1 error on NT [...] 3490 91 52_Re: What kind of DBA does it take for an ERP system?12_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 30 Jan 2001 11:14:56 -0500574_ISO-8859-1 Hi Terry, I have been support PeopleSoft implementations for the past 4 ½ years. I would not want to throw a new DBA with no experience into PeopleSoft. The tools that PeopleSoft has does not really support a DB2 platform very well. Most OS/390 DBA's will continue to use their inhouse migration tools. If you do not have a migration tool you may want to get one. The PeopleSoft delivered out of the box is probably not what you want to implement in production. The following are some counts for you: The default install is 9 Databases, 59 tablespaces, 8211 [...] 3582 41 17_INTELLIGENT MINER11_Emrah GUVEN28_Emrah.GUVEN@PAMUKBANK.COM.TR31_Tue, 30 Jan 2001 18:26:08 +0200290_iso-8859-9 Hi all ,

Our Problem is about Intelligent Miner for Data ...

We have installed Intellignet Miner 6.1 for OS/390 and IM Client for NT . When we want to try to run a datamine function at the host site from the client we get the message in the end of this mail . [...] 3624 15 54_Off-site disaster recovery node question (DB2 EEE AIX)11_Mike Fatula17_mfatula@PHEAA.ORG31_Tue, 30 Jan 2001 11:19:35 -0500477_us-ascii We are planning to perform our first off-site recovery test. They tell me we will have different Node placement on these recovery boxes than our production system. Does anyone know if this will pose a problem at recovery time.

================================================ 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. 3640 43 52_Re: What kind of DBA does it take for an ERP system?13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Tue, 30 Jan 2001 16:13:09 -0000295_iso-8859-1 I'd have to go for one of the experienced guys. Ease of administration of ERPs is overstated - regardless of having a fancy gui to play with you will still have the same housekeeping issues and any performance issues will be a challenge to an experienced DBA let alone a newbie. [...] 3684 78 52_Re: What kind of DBA does it take for an ERP system?9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Tue, 30 Jan 2001 09:41:57 -0700358_iso-8859-1 Howdy Terry,

Both Peoplesoft and SAP come with a set of DBA tools but they are "rudimentry" at best. Both packages come packaged with THOUSANDS of DB2 objects. You will need a VERY experienced DB2 DBA to be able to sort out the databases, tablespaces, tables and indexes and install them in a functionally meaningfull DB2 environment. [...] 3763 26 34_Accessing Stored Procedure Source.12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM31_Tue, 30 Jan 2001 12:02:34 -0500551_us-ascii Dear list,

As part of the upgrade of DB2 from V5 to V6, the stored procedure conversion job is run that moves stored procedures from SYSPROCEDURES to SYSROUTINES. Everything went well for us and all the stored procedures moved in with the SYSPROC schema.

We would like to do two things, change their schema's name to something more application friendly and create V6 'CREATE PROCEDURE ....... ' source so the applications people can change them and we can easily move them into production. Is there an easy way to do this? [...] 3790 17 7_DSNTEP212_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Tue, 30 Jan 2001 11:17:29 -0600320_iso-8859-1 We are currently at DB2V5.10 and would like to use the PL1 program, DSNTEP2, but do not have a PL1 compiler. The object code is being delivered with V6 but with no DBRM. Can't bind it without a DBRM, can't execute it without a PLAN (or can we?).

Where can I get the compiled load module and DBRM? [...] 3808 52 35_Re: Datajoiner 2.1 Database Manager16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM31_Tue, 30 Jan 2001 12:20:36 -0500693_iso-8859-1 I think we experienced this error when the Unix license daemon wasn't running. Ask your Unix people to make sure it's up.

-----Original Message----- From: Whittaker, Stephen [mailto:stephen.whittaker@PGNMAIL.COM] Sent: Tuesday, January 30, 2001 7:16 AM To: DB2-L@RYCI.COM Subject: Datajoiner 2.1 Database Manager



HELP!! We are using Datajoiner and seems we have a major problem with the database manager configuration file. We can't get to datajoiner this morning and its running production. It keeps giving me a sql5005C error msg. No applications can run against it and I can't even log onto the datajoiner database.... Is anyone using this product and [...] 3861 77 52_Re: What kind of DBA does it take for an ERP system?10_Bill Disch23_bill.disch@GENMILLS.COM31_Tue, 30 Jan 2001 11:20:59 -0600550_iso-8859-1 I concur with the responses so far. Go with the experienced DBAs as primary with the new person concentrating on learning to be a DBA first, then an ERP DBA second. I have supported SAP R/2 w DB2 on OS/390 for 7+ years and am involved in the conversion to R/3 using Oracle on HP/UX. The tools provided by the ERP vendors are usually not very efficient for DBA tasks. Also, an ERP system is designed to let you decide on the database vendor/platform. In order for this to work, the vendor does not take advantage of the lates greatest [...] 3939 80 52_Re: What kind of DBA does it take for an ERP system?14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM31_Tue, 30 Jan 2001 11:31:48 -0600579_iso-8859-1 We just went through a PeopleSoft installation in June 2000 on DB2 OS/390. In our case we had an experienced DBA be primary on the project and a newer DBA be his backup. Our project manager felt the duration/expense of the install and the business sensitivity of the system required a backup DBA. I think if the newer DBA had run with this we would have jeopardized performance and possibly even our go live date. Even with PeopleSofts upgrade tools, these systems are too big to expect someone to lead the technical & infrastructure charge and learn DBA work at [...] 4020 50 53_Re: Compuware's OS/390 DBA-XPERT Utility Manager?????13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM31_Tue, 30 Jan 2001 12:39:52 -0500579_us-ascii Hi, Craig! I use DBA-Xpert quite frequently to do table changes, reorgs, backups, etc.... I don't have problems generating the referbacks when I need to do a special backup. When generating the backup job and before you tell it to , is the stack option on the "Edit Copy Parms" screen set to "YES"?? If not, then change it. This will stack the datasets and generate the referbacks as well as use the RETAIN parameter so the tape doesn't rewind after finishing the first backup. If you have any other questions, please feel free to contact me. Scott Lindsey Sr. [...] 4071 82 53_Re: Compuware's OS/390 DBA-XPERT Utility Manager?????13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Tue, 30 Jan 2001 11:49:06 -0600430_iso-8859-1 To piggyback on this:

I don't use UM as such in DBA-Xpert, but also, like Scott, use the product to do table changes, reorgs, backups, etc., both on an as needed basis, and to generate production JCL that runs on a regular basis. The reason I did it this way instead of using UM was so that I could take advantage of our production control system, which makes sure that someone is notified if a job abends. [...] 4154 96 52_Re: What kind of DBA does it take for an ERP system?13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Tue, 30 Jan 2001 11:53:21 -0600428_iso-8859-1 Thanks to everyone who has replied, either here or directly to me. The answers are 100% "go with your experienced DBA", so that's where we'll start. I had hoped the tools that come with the products (I've only seen a brief demo) were a nice shortcut, but can understand why they wouldn't be optimized for a particular DBMS, so will reduce my expectations some! And thanks for the "good lucks", I will need them! [...] 4251 50 11_Re: DSNTEP211_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Tue, 30 Jan 2001 11:55:01 -0600699_us-ascii Yes, you can run DSNTEP2 without PLI in version 6.1. Look at the following members in the version 6.1 SDSNSAMP library: DSNTEJ1L DSNTEJ1P







"Vernon, John" cc: Sent by: DB2 Data Subject: DSNTEP2 Base Discussion List



01/30/01 11:17 AM Please respond to DB2 Data Base Discussion List











We are currently at DB2V5.10 and would like to use the PL1 program, DSNTEP2, but do not have a PL1 compiler. The object code is being delivered with V6 but with no DBRM. Can't bind it without a DBRM, can't execute it without a PLAN (or can we?). [...] 4302 45 24_DB Backup to tape on AIX15_Bose, Sandip X.23_SXBose@ARKBLUECROSS.COM31_Tue, 30 Jan 2001 11:58:41 -0600506_iso-8859-1 > A question on backing up a database directly to a tape device. > > We have AIX 4.3, UDB 6.1 and a DLT 7337 drive for tape backups. We were > trying to test the amount of time it took to backup a 20 GB database > directly to a tape. > > We used the following command: > > db2 backup database sample to /dev/rmt1 > > We got the following error message: > > SQL2045W Warning! Error "2" occurred while writing to media "/dev/rmt1". > On investigation, it was found that this was due to the > [...] 4348 202 35_Re: Mining the DB2 Catalog With SQL12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 30 Jan 2001 10:57:59 -0800570_us-ascii Has anyone out there used I-Miner with the DB2 catalog as input? I'm just getting familiar with I-miner and thought that using it to identify correllations in the catalog would make an interesting study. Tim --- Suresh Sane wrote: > Sanjeev – first of all, thank you for the positive > feedback. > > Here are my thoughts on some of the issues you have > raised: > > 1. Proactive v/s Reactive: Granted that several > shops remain in a > fire-fighting mode but should not. I contend that a > majority of performance > problems can be [...] 4551 73 21_Re: INTELLIGENT MINER12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 30 Jan 2001 11:47:42 -0800576_us-ascii looks like you've got a bad userid/password combination. Assuming you have different id/passwords on your NT and OS390 boxes, check your security settings on the PC to see where the security is done at the client, server, or dcs, then use that system's combination of id/password.

--- Emrah GUVEN wrote: > Hi all , > > Our Problem is about Intelligent Miner for Data ... > > We have installed Intellignet Miner 6.1 for OS/390 > and IM Client for NT . > When we want to try to run a datamine function at > the host site from [...] 4625 42 25_Datajoiner / DBM CFG file18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 30 Jan 2001 14:57:43 -0500547_iso-8859-1 Early today I posted a request for help concerning a problem we were having with our Datajoiner 2.1 product on Production. (we are using DB2 V5 on os/390 and Datajoiner sits on an NT box). What I've been able to determine is that the DBM CFG file may have gotten corrupted. I now know that this is in the /sqllib/db2/db2systm file. Does anyone have any thoughts on how this file would get corrupted? Ie; what could corrupt the file so that the Database Manager is not working. We did a restore of all the files using a copy we had [...] 4668 67 34_Database design & Web applications13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 30 Jan 2001 14:05:00 -0600362_iso-8859-1 Hi,

I wanted to know of some techniques used for web database design to manage for various query options the user can supply - not adhoc, but heading in that direction. Does anyone know what techniques the big search engines use - AltaVista, Amazon etc. I assume they denormalize, have many indexes, have big machines and heaps of memory. [...] 4736 168 52_Re: What kind of DBA does it take for an ERP system?5_chris22_hhardy1@HOUSTON.RR.COM31_Tue, 30 Jan 2001 14:26:57 -0000290_iso-8859-1 RE: [DB2-L] What kind of DBA does it take for an ERP system? Terry, with the proper suite of tools and a methodology you don't necessarily need the $85K\yr dba..... but with so much $$'s already invested in SAP why not have someone who can bat clean-up for the position.... [...] 4905 61 29_Re: Datajoiner / DBM CFG file5_chris22_hhardy1@HOUSTON.RR.COM31_Tue, 30 Jan 2001 14:29:18 -0000622_iso-8859-1 test ----- Original Message ----- From: "Whittaker, Stephen" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, January 30, 2001 7:57 PM Subject: Datajoiner / DBM CFG file



> Early today I posted a request for help concerning a problem we were having > with our > Datajoiner 2.1 product on Production. (we are using DB2 V5 on os/390 and > Datajoiner sits > on an NT box). > What I've been able to determine is that the DBM CFG file may have gotten > corrupted. > I now know that this is in the /sqllib/db2/db2systm file. > Does anyone have any [...] 4967 138 15_Re: DB2 connect11_Vigil, Juan18_Juan.Vigil@FMR.COM31_Tue, 30 Jan 2001 16:10:54 -0500598_- Tam

We encountered the very same problem and would liken to know what exactly you change.

Your attention will be greatly appreciated.

Juan Vigil

> -----Original Message----- > From: Tam Tran [SMTP:ttran@LASERCOM.COM] > Sent: Monday, December 18, 2000 4:47 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 connect > > > Hi Wayne, > > Thank you very much for reply. I got a solution for that. You can find > solution > on : > > http://support.microsoft.com/support/kb/articles/Q102/9/73.asp?VOTE=2 > > Solution is given by Harry Cable > > Regards > > TAM > > > > > > > > [...] 5106 58 24_Re: Assigning a constant15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 31 Jan 2001 09:20:55 +1100464_- Hi Derez,

If you're saying your load file doesn't have a value for ORG_CDE and you want to provide the literal 'B' then IBM's load utility won't help. I wanted to do the same thing a few weeks back - and in fact ran the job again yesterday - and used BMC's LoadPlus utility after being pointed in that direction by someone on this list. Worked beautifully. 'course, if you don't have LoadPlus or the CA equivalent it's gonna be a whole lot harder... [...] 5165 31 32_Re: Long names in UDB for OS/39013_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 30 Jan 2001 17:26:01 -0500319_iso-8859-1 >Does version 7 (or next version) of UDB for OS/390 >supports column and table names longer then 18 bytes ?

Hi Jacob, No, Os/390 Version 7 does not support names greater in length than 18.

At the U.S. DB2 Tech conf. in Las Vegas last October, it was announced that the next release would. [...] 5197 66 32_Re: Long names in UDB for OS/39012_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Tue, 30 Jan 2001 17:43:27 -0500559_iso-8859-1 Dave,

I know in our environment there is a lot of whining about being compatible with SQL Server (something like 120 character column names- at least with the gui you don't have to type that crap in ....but how does 120 characters fit on a standard PC Screen when you also need to look at the *attributes* for a column?????) I've even heard complaints because IDMS supports 30 characters and DB2 only 18. And to be honest it is kind of a pain to have a cross reference between the 30 character IDMS names and the 18 character DB2 names [...] 5264 37 52_INTRDR & stored procedure on db2 v6 os/390 problem ?0_24_lightsey@ITS.STATE.MS.US31_Tue, 30 Jan 2001 17:09:02 -0600566_us-ascii Another in the list of gremlins - user has a stored procedure that reads JCL lines from a db2 table based on input parms and puts them to the internal reader ( running in the db2-managed sp address space ). Under v5, no problem - but now that we've converted to v6, there is a consistent abend s04e r=00e50013 and the systsprint shows 00e30104. Neither the calling program nor the stored procedure needs/does a commit, the sp is defined commit_on_return = no. The JCL that is put to the internal reader works nicely even though the sp abends. User put [...] 5302 15 29_DB2 V7 and Simple Tablespaces15_Knabach, Mark L20_mark.knabach@EDS.COM31_Tue, 30 Jan 2001 18:25:45 -0500495_iso-8859-1 I've heard that Simple Tablespaces are no longer supported in DB2 V7. I can't seem to find documentation in support of this rumor. Is this in fact true, or are Simple Tablespaces still supported? Any assistance with this issue is appreciated.

================================================ 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. 5318 47 21_DB2V6 utilities OS39014_Hans Dingerdis21_dingerdis@SIONELL.COM31_Sat, 31 Jan 2009 01:13:13 +0100183_iso-8859-1 Does anybody already has experience in comparing BMC fast utils with DB2V6 utils? E.g. performance, functionality. If so please mention also BMC release. Gr. Hans Dingerdis 5366 21 0_14_busayo adebayo15_bayo9@YAHOO.COM31_Tue, 30 Jan 2001 16:48:15 -0800550_us-ascii Hi, Does anyone know of a tool that can convert MVS DB2 Script to AIX Script. Thanks for your suggestion in advance.







__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.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. 5388 25 56_Re: INTRDR & stored procedure on db2 v6 os/390 problem ?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 30 Jan 2001 19:58:30 -0500332_iso-8859-1 Hi Bruce, I think you might be onto something.

Didn't STOP RUN get outlawed a long time ago???

I'm probably missing a detail or three, but in the subroutine and S.P. arena, I have seen several problems solved by replacing STOP RUN with GOBACK.

When you went to v6, did you perhaps move into LE? [...] 5414 78 56_Re: INTRDR & stored procedure on db2 v6 os/390 problem ?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 31 Jan 2001 12:18:56 +1100366_iso-8859-1 Bruce

Change the STOP RUN to a GOBACK. Appl Prog and SQL Guide section 7.2.4.4 Writing a stored procedure as a main program or subprogram says "[a]void using statements that terminate the Language Environment enclave when the program ends. Examples of such statements are STOP or EXIT in a PL/I subprogram, or STOP RUN in a COBOL subprogram." [...] 5493 17 37_Re: Retreiving the 30 smallest values12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 30 Jan 2001 20:13:37 -0600485_- One of the little items I like in DB2 for OS/390 V7 is called limited fetch, a clause that makes the job a lot easier.

SELECT x, y, z, FROM tablex ORDER BY x desc FETCH FIRST 30 ROWS ONLY ;

Roger Miller, DB2 for OS/390 and z/OS

================================================ 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. 5511 69 38_Re: Accessing Stored Procedure Source.14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 30 Jan 2001 20:54:35 -0600393_iso-8859-1 Martin,

If I follow you correctly: you can run a couple of simple queries (SYSROUTINES and SYSPARMS), to extract from the catalog the information needed for the CREATE PROCEDURE statement. You can even have the queries create the DDL correctly. Secondly, change the schema name in the DDL, execute the DDL to create the new "schema.sp", then drop the "SYSPROC.old" one. [...] 5581 48 33_Re: DB2 V7 and Simple Tablespaces14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Tue, 30 Jan 2001 20:54:35 -0600723_iso-8859-1 Simple, segmented, and partitioned are still alive and well in DB2 V7 OS/390.

Regards, Richard

+===+===+===+===+====+ Richard Yevich Yevich Lawson & Assoc (YL&A) richard_yevich@ylassoc.com http://www.ylassoc.com 1-217-744-0000 +===+===+===+===+====+





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Knabach, Mark L > Sent: Tuesday, January 30, 2001 5:26 PM > To: DB2-L@RYCI.COM > Subject: DB2 V7 and Simple Tablespaces > > > I've heard that Simple Tablespaces are no longer supported in DB2 V7. I > can't seem to find documentation in support of this rumor. Is this in fact > true, or are Simple Tablespaces still [...] 5630 39 37_Re: Retreiving the 30 smallest values15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 31 Jan 2001 13:54:30 +1100565_- Roger,

Nice feature. Not, by any chance, available in V6 as an APAR, I suppose?



Raymond



> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Wednesday, 31 January 2001 1:14 pm > To: DB2-L@RYCI.COM > Subject: Re: Retreiving the 30 smallest values > > One of the little items I like in DB2 for OS/390 V7 is called limited > fetch, > a clause that makes the job a lot easier. > > SELECT x, y, z, FROM tablex > ORDER BY x desc > FETCH FIRST 30 ROWS ONLY ; > > Roger Miller, DB2 for OS/390 and [...] 5670 17 24_Re: Assigning a constant13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 30 Jan 2001 20:45:10 -0600450_us-ascii Thanks to all who responded, the 2 solutions are feasable using sort to add the value to the file (for large loads) and / or load the table then update the field (for small loads)

thanks again

================================================ 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. 5688 34 51_Greater Cincinnati DB2 Users Group Meeting 02/20/0114_Gill, Joseph G19_joseph.gill@EDS.COM31_Tue, 30 Jan 2001 23:17:42 -0500511_iso-8859-1 The Greater Cincinnati DB2 Users Group invites you to:

February 2001 User Group Meeting - "DB2 and E-Business: New Performance Challenges" Presenter: Ed Woods, Candle Corporation

Tuesday, February 20th, 1:00-4:00PM Procter & Gamble Health Care Research Center Auditorium Mason, Ohio

Our Agenda: 01:00 Greeting and Introduction 01:15 Internet Technology, DB2, Java, and Websphere 02:25 02:45 Understanding Internet and DB2 performance 03:45 Wrap-up and Prize Drawing [...] 5723 26 34_Checking Consistency Tokens in DB212_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Wed, 31 Jan 2001 00:02:42 -0600350_iso-8859-1 Across time there have been questions on how to validate the consistency tokens present in DBRMs, a DB2 Subsystem and a load module. If you telnet to p390.hlstechnologies.com and log on as USER21, USER22 or USER23 with the password 'demo' then you can examine a commercial solution that provides this function through an ISPF screen. [...] 5750 18 16_MVSScirpt to AIX14_busayo adebayo15_bayo9@YAHOO.COM31_Tue, 30 Jan 2001 22:43:14 -0800491_us-ascii Does anyone know of a tool that could convert DB2 MVSScript to AIX script. Thanks

__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.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. 5769 78 31_Re: DB2 Connect 7.1 error on NT0_24_markus.schaub@SYSTOR.COM31_Wed, 31 Jan 2001 07:53:52 +0100325_us-ascii hi mike

i can not remember the error-message, but i had the same problem on a sun box while upgrading udb connect ee from 5.2 to 7.1 fixpac1. the sun box was acting as gateway server only, so i just:

1. dropped the instance 2. create a new instance 3. cataloged the node(s) 4. cataloged the db(s) [...] 5848 77 43_Re: nmake error in Stored Procedure Builder20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM31_Wed, 31 Jan 2001 09:53:52 -0000321_- Hi Carl

I haven't seen a response to your question so far. I think the problem is that you haven't got a C/C++ compiler installed - or if you have, then you haven't set the DB2 registry variables. See the release note for whichever compiler you are using for which variables to set and what to set them to. [...] 5926 81 24_Re: Assigning a constant14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 31 Jan 2001 10:04:05 -0000362_iso-8859-1 I can't remember whether this came in with V5 or V6, but you can create a table and specify a default value for a column

"CREATE TABLE .......... COL1 CHAR(1) WITH DEFAULT 'B'" etc.

However, now you will ALWAYS have 'B' as the default (you can't remove the default clause without dropping the table) which may not be what you want. [...] 6008 37 37_Re: Retrieving the 30 smallest values14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 31 Jan 2001 10:04:10 -0000474_iso-8859-1 but then this raises the issue of semantics someone also pointed out last week.

Does the query want the "first 30 rows in descending order of some value" or "the rows that have a column value >= the first 30 values"

When someone says "the highest 30 values" that's unfortunately ambiguous. What happens if there are 31 (or more) rows with the SAME highest value. FETCH FIRST 30 will miss the 31st which may (or may not) be a valid thing to do. [...] 6046 40 38_Re: Checking Consistency Tokens in DB210_john clark20_pak02449@PIXIE.CO.ZA31_Wed, 31 Jan 2001 12:26:38 +0530341_us-ascii On that subject, about a year ago I offered a REXX I had written which displays consistency tokens from various sources.

It needs RLX (from Relational Architects), but may be customizable to work with IBM's interface.

If anyone would like it or has used it already and has any comments, please email me off list. [...] 6087 47 21_Timestamp differences14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 31 Jan 2001 10:32:43 -0000464_iso-8859-1 Nanno, Nanno.

I have a basic, teeny table with JOBNAME, JOBNUM, START_TIMESTAMP and END_TIMESTAMP on it. When I select from it, how can I get the END_TIMESTAMP - START_TIMESTAMP value (returned in seconds) to be displayed in TIME format (hh:mm:ss) without faffing about with stuff like... HOUR(END_TIMESTAMP - START_TIMESTAMP) as hours, MINUTE(END_TIMESTAMP - START_TIMESTAMP) as minutes, SECOND(END_TIMESTAMP - START_TIMESTAMP) as seconds? [...] 6135 52 18_15-255 Table Joins9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 31 Jan 2001 11:04:18 +0000590_us-ascii Hi, OS390 2.8 at DB2 V5.1

We have just hit an issue introduced with PQ31326(UQ39732) which reduced the table-join limit back down to 15 after PQ21815(UQ29567) allowed table-joins up to 255. Apparently UQ29567 could cause DB2 storage problems (and consequent DB2 abends). 'Open stable doors' and 'bolting horses' spring to mind, since there must be a lot of sites out there that now have bound plans with more than 15 table joins. These still seem to work with UQ39732 on - you just get an error when you try to rebind it. I don't think the hold information for UQ39732 [...] 6188 98 43_Re: nmake error in Stored Procedure Builder11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Wed, 31 Jan 2001 11:57:01 -0000555_- This was the solution, thanks.

-----Original Message----- From: Pav Kumar-Chatterjee [mailto:pavkc@HOTMAIL.COM] Sent: 31 January 2001 09:54 To: DB2-L@RYCI.COM Subject: Re: nmake error in Stored Procedure Builder



Hi Carl

I haven't seen a response to your question so far. I think the problem is that you haven't got a C/C++ compiler installed - or if you have, then you haven't set the DB2 registry variables. See the release note for whichever compiler you are using for which variables to set and what to set them to. [...] 6287 50 42_REORG option DISCARD - a confirmation.....10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 31 Jan 2001 13:13:16 +0100293_us-ascii I tested we new functions of REORG utility some months ago (UNLOAD EXTERNAL & DISCARD), as implemented via PTFs. If i remeber well all was ok with ' = ' operand (were was a problem with NPI when reorganizing a partitioned tablespace but we applied a PTF to resolve the problem). [...] 6338 27 52_REORG option DISCARD - a confirmation.....- addendum10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 31 Jan 2001 13:18:35 +0100612_us-ascii In reorg SYSIN statements

REORG TABLESPACE DSDBESTC.DSTSECAR PART 02 LOG NO SORTDATA SORTKEYS SHRLEVEL REFERENCE PUNCHDDN SYSPUNCH DISCARDDN SYSDISC DISCARD COPYDDN SYSCOPY DEADLINE NONE FROM TABLE CESVPROD.DSTBECAR WHEN (PROGR < 328388)

the field PROGR is defined as SMALLINT

..... Thanks & regards Max Scarpa DB2 ...? (maybe tape cleaner)

================================================ 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. 6366 54 18_UDB/DB2 on AIX box29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV31_Wed, 31 Jan 2001 08:56:26 -0500538_us-ascii I had a situation come up yesterday. On one of our aix boxes that has UDB v6 fixpack 2 on it, the sqllib directory was deleted for one of the instances on that box. The aix administrator copied over the sqllib from another aix box. Of course the database information in the instance was wrong and IBM says to issue the db2iupdt and db2ln commads and sqllib would be ok. But since the database structure and data were also created in that sqllib directory we had to find a back up to restore. Any way my question is this, if [...] 6421 46 16_DSN1COPY problem14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 31 Jan 2001 14:25:17 -0000339_iso-8859-1 Ladies and Gentelmen,

I am running DB2 V5.1 on OS/390 V2.8. I have two single, segmented, non-partitioned tablespaces, "A" and "B". I also have a partitioned tablespace, "C". The tables defined in those tablespaces are identical.

I DSN1COPY A to B with OBIDXLAT - no problem. Can I also DSN1COPY from A to C? [...] 6468 68 20_Re: DSN1COPY problem11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 31 Jan 2001 08:48:25 -0600602_iso-8859-1 DSN1COPY expects the defination of the target and the source to be the same hence you CANNOT do a DSN1COPY from A to C.

HTH. Amit





-----Original Message----- From: Davage, Marcus [mailto:Marcus.Davage@LLOYDSTSB.CO.UK] Sent: Wednesday, January 31, 2001 8:25 AM To: DB2-L@RYCI.COM Subject: DSN1COPY problem



Ladies and Gentelmen,

I am running DB2 V5.1 on OS/390 V2.8. I have two single, segmented, non-partitioned tablespaces, "A" and "B". I also have a partitioned tablespace, "C". The tables defined in those tablespaces are identical. [...] 6537 77 20_Re: DSN1COPY problem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 31 Jan 2001 09:48:26 -0500413_iso-8859-1 Marcus, The segmented tablespaces each consist of one dataset. The partitioned tablespace consists of multiple datasets. DSN1COPY copies from one dataset to one other dataset. I suspect that 'A' has some column data values which are inappropriate for the the corresponding 'C' "partitioning index" columns in any single partition.

So it looks like you need to UNLOAD from A then LOAD to C. [...] 6615 61 38_Re: Checking Consistency Tokens in DB212_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Wed, 31 Jan 2001 08:50:55 -0600630_us-ascii John: We would encourage anyone who is interested in exploring the technical issues to obtain one of the freeware solutions. The reasons for a commercial solution are : support of DB2 changes, use of assembler language instead of REXX and thus the ability to fully exploit MVS features such as the current MVS Binder Technology, execution efficiencies in CPU overhead and the advantage a vendor has though seeing the product work in differing environments. Our CheckBind program has been in use for almost eight years, and integrates into most change management technology. The new ISPF and batch programs allow use [...] 6677 68 20_Re: DSN1COPY problem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 31 Jan 2001 14:50:10 -0000352_iso-8859-1 You will be inundated with replies to this one so I will be quick and short.

You can only DSN1COPY between two EXACTLY IDENTICAL tablespaces/indexspaces

So, simple to partitions is NO Segmented to segmented is OK PROVIDED the segment size is the same Simple to segmented is NO etc

Phil Grainger Computer Associates [...] 6746 210 20_Re: DSN1COPY problem10_Bill Disch23_bill.disch@GENMILLS.COM31_Wed, 31 Jan 2001 08:54:15 -0600609_iso-8859-1 No. In each case, DSN1COPY will succeed, but building the indexes and reading the data may show errors. In order for the process to work, the source and destination structures must be identical in terms of tablespace type and if segmented, segsize. Each type of tablespace has a specific page structure (see Diagnosis Guide and Reference) which must match the structure defined in the DB2 Catalog in order for DB2 to use the data. DSN1COPY is a brute force page by page copy working outside of DB2's control. I have successfully(accidently) used DSN1COPY to copy a tablespace into a completey [...] 6957 69 20_Re: DSN1COPY problem0_19_mike.holmans@BT.COM31_Wed, 31 Jan 2001 15:05:40 -0000338_- No, you can't, because DSN1COPY does a page by page copy. How would it deal with a space map page when trying to put it into a partition, for instance?

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 [...] 7027 26 46_Help with Partition Table with historical data14_Carroll, Ouida24_Ouida.Carroll@BCBSLA.COM31_Wed, 31 Jan 2001 09:34:48 -0600531_iso-8859-1 > We have an existing partitioned table holding 3 years of information which > is partitioned by month. Each month we drop and recreate this table to > shift the partitions for the rotating 3 years of information. > > Is there a way to design this table so that we don't have to drop and > recreate this very large table every month? If you have any ideas, I > would appreciate some alternatives. > Thanks for your input. > > > Ouida Carroll > Sr Data Base Analyst > BCBSLA All opinions expressed are my own and do [...] 7054 107 20_Re: DSN1COPY problem12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 31 Jan 2001 16:46:11 +0100824_iso-8859-1 Marcus, go the safe way.

Unload the data from your single, segmented, non-partitioned tablespace "A" and the load the data into your partitioned tablespace "C". If doing so you are on the safe side of life, even if there exist millions of rows......... ;-))

HTH.

With kind regards - mit freundlichen Grόssen, G e o r g H . P e t e r 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 ---------------------------------------------------------------------- [...] 7162 61 20_Re: DSN1COPY problem16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Wed, 31 Jan 2001 09:03:49 -0700582_- Short answer is NO.

> -----Original Message----- > From: Davage, Marcus [SMTP:Marcus.Davage@LLOYDSTSB.CO.UK] > Sent: Wednesday, January 31, 2001 7:25 AM > To: DB2-L@RYCI.COM > Subject: DSN1COPY problem > > Ladies and Gentelmen, > > I am running DB2 V5.1 on OS/390 V2.8. > I have two single, segmented, non-partitioned tablespaces, "A" and "B". > I also have a partitioned tablespace, "C". > The tables defined in those tablespaces are identical. > > I DSN1COPY A to B with OBIDXLAT - no problem. > Can I also DSN1COPY from A to C? > > Yours doubtfully, > Marcus > > > [...] 7224 65 50_Re: Help with Partition Table with historical data16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 31 Jan 2001 11:14:50 -0500417_iso-8859-1 Recreate the tablespace as LARGE with 254 partitions. Make the LIMITKEYS for each partition so that it covers one month. Size the first 36 partitions full volume. Size the remaining 218 partitions 1 track each. Load the table. A month later: Resize partition 37 to production size. Resize partition 1 to 1 track. Dummy load partitions 1 and 37 to make the resizes take effect and delete the old data. [...] 7290 66 50_Re: Help with Partition Table with historical data0_25_jwstimmel@DUKE-ENERGY.COM31_Wed, 31 Jan 2001 11:17:19 -0500384_us-ascii Do the partitions have to be shifted?

Another way to do it is 'rolling' partitions, i.e. just load/replace the oldest partition with the new incoming data. Since you can operate with total partition independence (assuming you are on DB2/OS390), only the oldest partition would be affected during the load and you would avoid dropping/rebuilding the entire table. [...] 7357 67 54_Help with Partition Table with historical data (Reply)0_28_michael.d.ockenfels@WCOM.COM31_Wed, 31 Jan 2001 10:21:07 -0600374_us-ascii We have a similar situation here. We use a second table that contains the dates and the partition number. We use the VL tablespaces and have it parted to to 254 parts. The load cards, that load onnly ont partition worth of data, are dynamically built by a program that reads the second table. THis allows us to go back to part 1 when part 254 has been loaded. [...] 7425 73 54_Help with Partition Table with historical data (Reply)14_Mike Ockenfels28_michael.d.ockenfels@WCOM.COM31_Wed, 31 Jan 2001 10:21:07 -0600374_us-ascii We have a similar situation here. We use a second table that contains the dates and the partition number. We use the VL tablespaces and have it parted to to 254 parts. The load cards, that load onnly ont partition worth of data, are dynamically built by a program that reads the second table. THis allows us to go back to part 1 when part 254 has been loaded. [...] 7499 23 65_Multi-col indexes vs multiple indexes to avoid a sort on ORBER BY14_Greg DiGiorgio29_gdigior@CI.NEWPORT-NEWS.VA.US31_Wed, 31 Jan 2001 11:32:26 -0800422_us-ascii Dear fellow DB2-L'ers,

Can DB2 avoid a sort (and thereby materialization of the dataset) on a query with an ORDER BY clause if (1) there exists no multiple-column index in the sort order, but (2) there are multiple indexes that DB2 could combine to form the sort order?

I am asking this to help us decide if we should change our strategy of creating mutli-column indexes based on browse order. [...] 7523 68 50_Re: Help with Partition Table with historical data15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Wed, 31 Jan 2001 10:30:57 -0600411_iso-8859-1 One design I saw at a previous place of work, was to use an instance table that returned the partition number for the date you were looking for. They had the partition number as the high order part of the key for the table. This way you just had to change the instance table once a month. With only 36 entries, in your case, it will stay in the buffer pool all the time and not be a bottleneck. [...] 7592 114 50_Re: Help with Partition Table with historical data0_18_mebert@AMADEUS.NET31_Wed, 31 Jan 2001 17:32:55 +0100624_us-ascii Hi Ouida,

you might want to look in the archives for last week, topic "Reusing Tablespace partitions". The most useful solutions would be (in my opinion):

1. Create a Large partitioned TS with 254 partitions where each month you load one partition with new data, and delete the partition 36 months back. Then you need to drop/recreate only every 21 years, or (with V6) do a full-TS Reorg with partition rebalancing (ALTER the limitkeys, then REORG) every couple of years (or whatever you choose). 2. The "standard" solution that we are using extensively: add a column (smallint or char) PART_NUM [...] 7707 100 42_FW: News Roundup: Mainframes to the Rescue13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 31 Jan 2001 11:47:42 -0500662_us-ascii Check out the lead story! For those of us who love DB2 on the mainframe, it will warm our hearts...

> ========================================================= > > LEAD STORY: "Mainframes: Seeing the light" > > Mainframe computers may be the answer to the California power > crunch. A single z900 mainframe costs about $32 per day in > electricity, while power for an equitable configuration of 750 > Unix servers would cost about $624 per day. A new z900 starts > at about $750,000, but the savings in total cost of ownership > may offset the hardware costs. > SOURCE: CRN > http://www.crn.com/Components/Search/Article.asp?ArticleID=23234 [...] 7808 32 56_REORG option DISCARD - a confirmation (problem resolved)10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 31 Jan 2001 17:48:50 +0100474_us-ascii With the statements:

REORG TABLESPACE DSDBESTC.DSTSECAR PART 02 LOG NO DISCARD ................................ FROM TABLE CESVPROD.DSTBECAR WHEN ( BANK = 102 AND PROGR > 0 AND PROGR < 328388 )

all works fine. BANK is the first field of partitioning index while PROGR is the third. No NPI index on the table.

It seems that with PART xx there's the need to specify an = (in the first field of index ?), but I didn't perform any other test. [...] 7841 140 60_Re: REORG option DISCARD - a confirmation (problem resolved)14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 31 Jan 2001 09:01:39 -0800366_iso-8859-1 Max, I had a similar problem implementing this a few months ago. There are restrictions on what you can do but I don't remember the details. At least you know your'e not hearing things! -Phil

-----Original Message----- From: Max Scarpa To: DB2-L@RYCI.COM Sent: 1/31/01 8:48 AM Subject: REORG option DISCARD - a confirmation (problem resolved) [...] 7982 37 24_Stored Procedures survey13_Todd Peterson23_toddpeterson2@YAHOO.COM31_Wed, 31 Jan 2001 09:22:19 -0800489_us-ascii We are looking at the possibility of implementing some of our business logic in stored procedures and would like some information regarding user experiences. We would like to know:

(1) What languages are you using for your stored procedures -- ASSEMBLER, C, COBOL, JAVA, PLI, REXX or SQL Procedure Language.

Further, if you are using SQL Procedure Language, will you tell me if you build the procedures using 1) JCL, 2) DSNTPSMP or 3) Stored Procedure Builder. [...] 8020 79 37_Re: Retrieving the 30 smallest values11_Paul Murphy22_Paul_Murphy@MAY-CO.COM31_Wed, 31 Jan 2001 10:51:00 -0600473_us-ascii Then you would select the first 30 distinct highest values.





"Grainger, Phil" To: DB2-L@RYCI.COM Subject: Re: Retrieving the 30 smallest values Sent by: DB2 Data Base Discussion List



01/31/01 04:04 AM Please respond to DB2 Data Base Discussion List











but then this raises the issue of semantics someone also pointed out last week. [...] 8100 20 24_Stored Procedures survey12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Wed, 31 Jan 2001 19:55:01 +0200474_iso-8859-1 Hi, As there was no email to answer directly (as requested): We use SPL mostly (some JAVA tests) via the SPB. DB2 V6.1. - ans will stay like that till 2Q02.

Isaac Yassin DBMS & IT Consultant yassin@netvision.net.il

================================================ 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. 8121 58 28_Re: Stored Procedures survey13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 31 Jan 2001 12:07:18 -0600397_iso-8859-1 1. Cobol, but we want to use Java. Seems that there is a lot of work to setup Java on OS390, and there may very well be another software component we need to pay for. If Java is too complicated to setup or expensive to use, we will look at SPL. We want out PC based programmers, who do not know Cobol on OS390 environment, to develop their own SP's. We will look at using the SPB. [...] 8180 101 24_Re: Assigning a constant12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Wed, 31 Jan 2001 20:15:20 +0200322_iso-8859-1 Hi, You may use sort to add 'B' on the input record. (or use ORACLE that allows things like that in the load...)

Isaac Yassin DBMS & IT Consultant yassin@netvision.net.il

I can't remember whether this came in with V5 or V6, but you can create a table and specify a default value for a column [...] 8282 24 45_VI 2.3 FRNMSP01 stored procedure and DB2 V6.116_Marcelo Silveira22_mardb2ques@HOTMAIL.COM31_Wed, 31 Jan 2001 18:33:08 -0000442_- Hello everyone,

I uprading our VI 2.1 to VI 2.3, but I am getting sqlcode-430 when executing stored procedure FRNMSP01. Sample job to create the procedure is for DB2 V5.1 and I had to change it to DB2 V6.1. I was wondering if someone had the same problem.

Regards, Marcelo.



_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com [...] 8307 44 27_Can this be done using SQL?13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Wed, 31 Jan 2001 13:24:44 -0500388_- Hi listers, There are several (hundreds) rows in the original table. I will like to sum some column of the table individually, add(ition) some columns of the table together horizontally and be able to SUM the additions vertically to produce a one row of summned data for these individual columns. To give an idea what I'm trying to accomplish, please take a look at the following: [...] 8352 67 28_Re: Stored Procedures survey12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 31 Jan 2001 10:35:37 -0800312_us-ascii 98% of my clients SPs are in SPL built with the SPB. The others are in COBOL. They're running DB2 V6 on mainframe.

Its a mixture of full business logic in the SP and just data access to tables. The COBOL procedures are used for accessing VSAM. Plans are to use COBOL for IMS access as well. [...] 8420 51 22_Re: UDB/DB2 on AIX box14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 31 Jan 2001 13:53:14 -0500604_iso-8859-1 Sherri:

I would not trust db2iupdt to put everything under sqllib for me. eg. if you have stored procedures which are normally stored in sqllib/function, db2iupdt cannot salvage those. Neither can you retain db2diag.log data. As far as db2java.zip is concerned, we do apply fix packs regularly but have not yet run into any problem syncing db2java.zip. I believe when you run db2iupdt after installing the fixpack it updates db2java.zip in sqllib/java directory. You may want to check the CLASSPATH of processes to ensure its picking up db2java.zip from the sqllib/java directory. [...] 8472 154 24_Re: Assigning a constant23_Catherine Mercado-Chung29_ctmercado-chung@HOUSEHOLD.COM31_Wed, 31 Jan 2001 10:37:00 -0800364_us-ascii Try this one...

LOAD DATA RESUME NO REPLACE LOG NO INDDN SYSREC00 INTO TABLE DB2TEST.SAMPLE_TABLE ( ACCT_NUM POSITION( 25 ) CHAR( 12) , REGN_PROCS_CDE POSITION( 25 ) CHAR( 4) , ORG_CDE POSITION( 33 ) SMALLINT DEFAULTIF(ORG_CDE = ' ') INITIAL('B') , SS_NUM POSITION( 259 ) CHAR( 9) , NAME_MID POSITION( 38 ) CHAR( 25) , NAME_NICK POSITION( 102 [...] 8627 50 49_Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.115_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 31 Jan 2001 13:20:40 -0600298_iso-8859-1 Hi, Marcelo, We had FRNMSP01 installed while we were on V5 of DB2 but when we migrated to V6 we found that we needed to apply UQ46497 for APAR PQ38176 to DB2 to prevent -516 errors from occurring when running FRNMSP01, if I remember correctly. We did not encounter any -430 errors. [...] 8678 24 18_Utility on UDB/DB215_Rajesh Udandrao17_urajes1@YAHOO.COM31_Wed, 31 Jan 2001 11:27:54 -0800446_us-ascii HI Is there any utility in UDB(AIX) to generate the scripts of the views and authorities on a particular table as every time I drop a table I need to see for the information regarding the dependent views and Authorities in the Catalog table.. Pl. let me know

Rajesh

__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 8703 19 55_Impact of online reorg at the partition level on an NPI13_Johnson, Bill18_bjo23@ALLSTATE.COM31_Wed, 31 Jan 2001 13:49:25 -0600467_- All, Can anyone supply or point me to a resource that will shed some light on running online reorg at the partition level and the impact that it will have upon the NPI. Thanks, Bill Johnson DBA Allstate Insurance Co. DB2 V6

================================================ 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. 8723 24 30_Compact Data for Archive Logs?10_Mark Labby16_mlabby@PHEAA.ORG31_Wed, 31 Jan 2001 15:05:36 -0500294_us-ascii ----- Forwarded by Mark Labby/PHEAA on 01/31/01 03:13 PM -----

Mark Could you open up a question on the DB2 list. Ask if anyone is using the COMPACT DATA parameter for the archive log data sets. And if they are using it what are the pros and cons to adopting this feature. [...] 8748 27 14_DB2/UDB ON AIX15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Wed, 31 Jan 2001 12:24:45 -0800433_us-ascii Hi I have some doubts regarding the UDB data base initial set up. Pl. let me know if any one knows

1.How can I decide the file System allocation? 2.Max size of Temporary tablespace size 3.Extent Size 4.Buffer pool allocation. 5.Page size.

Rajesh

__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 8776 38 22_Re: Utility on UDB/DB213_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 31 Jan 2001 14:34:51 -0600465_US-ASCII Rajesh, Use DB2LOOK This will give you everything you need except for recreation of triggers and recreation and references to UDF's. HTH Kurt

>>> urajes1@YAHOO.COM 01/31/01 01:27PM >>> HI Is there any utility in UDB(AIX) to generate the scripts of the views and authorities on a particular table as every time I drop a table I need to see for the information regarding the dependent views and Authorities in the Catalog table.. Pl. let me know [...] 8815 76 49_Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.116_Marcelo Silveira22_mardb2ques@HOTMAIL.COM31_Wed, 31 Jan 2001 20:46:16 -0000652_- Hi Linda,

Thanks very much. I checked that we already have this ptf applied. Could you please let me know what you have defined in RUNOPTS column for this FRNMSP01 on SYSIBM.SYSROUTINES?

Regards, Marcelo.



>From: "Billings, Linda" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.1 >Date: Wed, 31 Jan 2001 13:20:40 -0600 > >Hi, Marcelo, > We had FRNMSP01 installed while we were on V5 of DB2 but when we >migrated to V6 we found that we needed to apply UQ46497 for APAR PQ38176 to >DB2 to prevent [...] 8892 31 38_DB2 UDB 7.1 in development environment12_Aaron O'Hara28_Aaron.Ohara@HELLONETWORK.COM31_Wed, 31 Jan 2001 12:28:21 -0800377_iso-8859-1 Hi all,

I'm new to the list and I'm doing an in depth evaluation of DB2 UDB 7.1 for our Enterprise. I work for a development company which has data centres located nationally.

We operate in a mixed environment and want to support development using Linux, Quality Assurance with NT/2000 and, eventually, production (data centres) with Sun Solaris. [...] 8924 27 40_Joining DB2 Tables across DB2 Subsystems11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Wed, 31 Jan 2001 16:02:52 -0500222_us-ascii Can you join a local table (on subsystem A) with a remote table (on subsystem B) in the same SQL statement?. We get a SQL -512. We have looked at the reference manuals and there is not a clear "YES" or "NO" it can 8952 93 49_Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.115_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 31 Jan 2001 15:09:40 -0600507_iso-8859-1 The RUNOPTS column contains TRAP(ON),TERMTHDAC(UADUMP)

HTH, Linda

-----Original Message----- From: Marcelo Silveira [mailto:mardb2ques@HOTMAIL.COM] Sent: Wednesday, January 31, 2001 2:46 PM To: DB2-L@RYCI.COM Subject: Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.1



Hi Linda,

Thanks very much. I checked that we already have this ptf applied. Could you please let me know what you have defined in RUNOPTS column for this FRNMSP01 on SYSIBM.SYSROUTINES? [...] 9046 106 49_Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.115_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 31 Jan 2001 15:12:38 -0600361_iso-8859-1 Oooops! Wait a minute! The RUNOPTS column is blank for that stored procedure. I had a semi-colon where it should not be.

Sorry about that.

Linda -----Original Message----- From: Billings, Linda Sent: Wednesday, January 31, 2001 3:10 PM To: 'DB2 Data Base Discussion List' Subject: RE: VI 2.3 FRNMSP01 stored procedure and DB2 V6.1 [...] 9153 18 29_v6 migration & host variables10_Tom Taylor17_ttaylor@CHUBB.COM31_Wed, 31 Jan 2001 16:57:06 -0500466_us-ascii HI All

For the migration to V6, I thought IBM developed a rexx exec to scan for statements that don't have a colon in front of host variables. Can some one send me info on where to obtain the exec.

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9172 27 59_Re: Impact of online reorg at the partition level on an NPI15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 31 Jan 2001 16:01:50 -0600525_us-ascii Hi Bill

If you are looking for sample which I have used successfully here it is

REORG TABLESPACE DB1.TS1 PART 1 LOG NO SORTDATA NOSYSREC SORTKEYS COPYDDN(PART01A,PART01B) UNLDDN(SYSPRT1) UNLOAD CONTINUE STATISTICS TABLE(ALL) INDEX(ALL)



Good luck

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9200 43 59_Re: Impact of online reorg at the partition level on an NPI15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 31 Jan 2001 16:03:46 -0600474_us-ascii You have to include SHRLEVEL Reference option , plus you need to use mapping table I believe. Let me know if I am wrong. ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/31/2001 04:00 PM ---------------------------



Mohammed Nayeem 01/31/2001 04:01 PM

To: DB2 Data Base Discussion List cc:

Subject: Re: Impact of online reorg at the partition level on an NPI (Document link: Mohammed Nayeem) [...] 9244 37 44_Re: Joining DB2 Tables across DB2 Subsystems13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 31 Jan 2001 17:11:23 -0500412_iso-8859-1 Hi Jose,

I'm sure there's a clear statement somewhere in the SQL reference telling you you can't do that.

But the only place I can find is in Msgs & Codes where it says:

-512 STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID





Explanation: One of the following conditions exists:





>>>>>> - The statement refers to multiple locations. [...] 9282 133 49_Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.116_Marcelo Silveira22_mardb2ques@HOTMAIL.COM31_Wed, 31 Jan 2001 22:18:46 -0000690_- Hi Linda,

Thanks again for your info.

Regards, Marcelo.



>From: "Billings, Linda" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: VI 2.3 FRNMSP01 stored procedure and DB2 V6.1 >Date: Wed, 31 Jan 2001 15:12:38 -0600 > >Oooops! Wait a minute! The RUNOPTS column is blank for that stored >procedure. I had a semi-colon where it should not be. > >Sorry about that. > >Linda >-----Original Message----- >From: Billings, Linda >Sent: Wednesday, January 31, 2001 3:10 PM >To: 'DB2 Data Base Discussion List' >Subject: RE: VI 2.3 FRNMSP01 stored procedure and DB2 V6.1 > > >The RUNOPTS [...] 9416 39 33_Re: v6 migration & host variables16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 31 Jan 2001 22:18:58 -0000692_- TOM - attached is the link.

http://www6.software.ibm.com/dl/dbrm/dbrmcf-p

You can download a bunch of other stuff by going to WWW.IBM.COM , then search for DOWNLOADS and DB2 :

http://www3.software.ibm.com/srchdown?searchstr=DB2&startrow=11

Best regards Steen Rasmussen Computer Associates

> -----Original Message----- > From: Tom Taylor [SMTP:ttaylor@CHUBB.COM] > Sent: Wednesday, January 31, 2001 10:57 PM > To: DB2-L@RYCI.COM > Subject: v6 migration & host variables > > HI All > > For the migration to V6, I thought IBM developed a rexx exec to scan for > statements that don't have a colon in front of host variables. Can some one > send me info [...] 9456 41 33_Re: v6 migration & host variables16_Marcelo Silveira22_mardb2ques@HOTMAIL.COM31_Wed, 31 Jan 2001 22:22:09 -0000742_- Hi Tom,

This is the website:

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

Marcelo.



>From: Tom Taylor >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: v6 migration & host variables >Date: Wed, 31 Jan 2001 16:57:06 -0500 > >HI All > >For the migration to V6, I thought IBM developed a rexx exec to scan for >statements that don't have a colon in front of host variables. Can some one >send me info on where to obtain the exec. > >Tom > >================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the [...] 9498 179 50_Re: Help with Partition Table with historical data15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Thu, 1 Feb 2001 09:35:29 +1100611_iso-8859-1 Hi Dr E,

Glad to see you still keep the rolling partitions technique. If you want to avoid having to code the partition number in queries (but, presumably, you know what date you're interested in) you could create a view that joins the partitioned table and the partition lookup table 'where a.part_num = b.part_num' and, in the view, provide the date column from the lookup table instead of the partition_number from the partitioned table. That way query writers need never know what the partition number is (because it changes as you roll partitions) and can get by with just providing [...] 9678 75 34_Re: Compact Data for Archive Logs?14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 1 Feb 2001 09:47:17 +1100564_iso-8859-1 This feature uses IDRC on tape drives to compress data. When IDRC was first introduced (more than 10 years ago) there was a problem reading compressed tapes backwards - as can happen during a ROLLBACK of a long running transaction. (Essentially, the drive, while logically reading backwards, actually had to read each block forwards. This meant the drive would "skip two blocks backwards", "read block forwards", "skip two blocks backwards", ... The continual reversal in the direction of tape movement meant that the process was rather slooooow.) [...] 9754 53 44_Re: Joining DB2 Tables across DB2 Subsystems0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 31 Jan 2001 16:49:51 -0600362_iso-8859-1 You can't refer to the remote table directly, but you can via an ALIAS defined in the local DB pointing to the remote DB. Using that approach, I know you can do single table SQL, but I'm not sure if you can do a join using the ALIAS.

The SQL is passed to the remote system as DYNAMIC SQL (not compiled) so it can be a performance problem. [...] 9808 57 44_Re: Joining DB2 Tables across DB2 Subsystems13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 31 Jan 2001 14:53:02 -0800452_iso-8859-1 However, somone on the list told us sometime back that if you have Datajoiner installed, you can.

Ashish.

-----Original Message----- From: Seibert, Dave [SMTP:Dave_Seibert@COMPUWARE.COM] Sent: Wednesday, January 31, 2001 2:11 PM To: DB2-L@RYCI.COM Subject: Re: Joining DB2 Tables across DB2 Subsystems

Hi Jose,

I'm sure there's a clear statement somewhere in the SQL reference telling you you can't do that. [...] 9866 43 44_Re: Joining DB2 Tables across DB2 Subsystems14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 1 Feb 2001 09:57:03 +1100563_iso-8859-1 The only way of doing this is to use DataJoiner ( http://www-4.ibm.com/software/data/datajoiner/ ). But it doesn't run on OS/390.

-----Original Message----- From: Jose' Gomez [mailto:Jose_Gomez@WENDYS.COM] Sent: Thursday, February 01, 2001 8:03 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Joining DB2 Tables across DB2 Subsystems





Can you join a local table (on subsystem A) with a remote table (on subsystem B) in the same SQL statement?. We get a SQL -512. We have looked [...] 9910 31 15_V6 -101 problem13_Cindi Schultz17_schultz.ca@PG.COM31_Wed, 31 Jan 2001 17:44:18 -0500597_us-ascii Hi everyone,

We have V6 in our test/QA systems. We have seen many messages on the DB2-L suggesting to apply PQ40039/UQ46498 (just an FYI - that has been superseded by UQ48003) to prevent the -101 error.

We have not had any occurrences of -101 on our test/qa systems, but the DBA's & applications are concerned that we could face this in production since we cannot test everything and the volume of data is higher in production. Therefore we are considering applying the maintenance now in test/qa and then rolling it out with the production V6 migration in a couple of [...] 9942 62 69_Re: Multi-col indexes vs multiple indexes to avoid a sort on ORBER BY14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 1 Feb 2001 10:00:37 +1100465_iso-8859-1 No: DB2 cannot (currently) join information from multiple indexes to avoid a sort.

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

-----Original Message----- From: Greg DiGiorgio [mailto:gdigior@CI.NEWPORT-NEWS.VA.US] Sent: Thursday, February 01, 2001 6:32 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Multi-col indexes vs multiple indexes to avoid a sort on ORBER BY [...] 10005 102 31_Re: Can this be done using SQL?14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU30_Thu, 1 Feb 2001 09:49:09 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 10108 47 46_Neodbug is bringing Bonnie Baker to Cleveland!11_James Szabo18_jim.szabo@CORE.COM31_Wed, 31 Jan 2001 19:04:57 -0500351_iso-8859-1 You are Invited To a NEODBUG (Northeast Ohio Database Users Group) Special Education Meeting

"Things I Wish They'd Told Me 8 Years Ago, Part 5" and "DB2 Explain Explained"

Presented By Bonnie Baker

February 22, 2001 at the Cleveland Hilton South (Rockside Rd. & I 77) Cleveland, OH USA Hours: 9:00am. to 5:00 p.m. [...] 10156 31 16_DSSIZE Parameter0_20_bjnigh@HOUSEHOLD.COM31_Wed, 31 Jan 2001 15:55:19 -0800368_us-ascii We are currently on DB2 Version 5 for OS/390 and are planning on migrating to DB2 Version 6 this weekend.

One of our Data Warehouse tablespaces (which is defined as LARGE and partitioned by date) has hit the 4 gig VSAM limit. In order to fix this problem, I plan on redefining the tablespace using the DSSIZE parameter available in DB2 Version 6. [...] 10188 97 35_DB2 NYC TRIDEX Eductation Offerings11_Gerri Lloyd17_glloyd@US.IBM.COM31_Wed, 31 Jan 2001 17:29:41 -0700601_us-ascii DB2 NYC March 2nd, Education Seminar with Horacio Terrizzano, IBM

We are happy to announce two additional one day educational classes. Due to the overwhelming demand for Sheryl Larsen's SQL class, we have scheduled an additional class on March 20, 2001. In addition, we are fortunate to be able to offer Interpreting Key Counters in DB2 V5 /V6 Performance by Horacio Terrizzano of the IBM Silicon Valley Lab on Friday March 2, 2001. The classes will be taught from 8:30 - 4:00, IBM Corporation, 9th Floor, 590 Madison Ave. (between 56th &57th) NY, NY for the low cost of $195.00. [...] 10286 71 20_Re: DSSIZE Parameter14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 1 Feb 2001 12:07:08 +1100367_iso-8859-1 No experience on DSSIZE, but you have noted the SMS data class requirements, haven't you. (I only raise the point because you blandly mentioned "recreating the tablespace using the DSSIZE parameter" without mentioning other changes that might be required.)

The page numbers within each partition will change - and DSN1COPY doesn't change them. [...] 10358 101 20_Re: DSSIZE Parameter0_20_bjnigh@HOUSEHOLD.COM31_Wed, 31 Jan 2001 17:55:54 -0800451_us-ascii Yes I do realize there are SMS data class requirements that need to be satisfied in order to use the DSSIZE parameter. Thanks for noting this. Also thanks for the input on DSN1COPY.









James Campbell cc: (bcc: Barbara J. Nigh/Household International) 01/31/2001 05:07 PM Subject: Re: DSSIZE Parameter Please respond to DB2 Data Base Discussion List [...] 10460 89 31_Re: Can this be done using SQL?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 1 Feb 2001 09:50:35 +0530414_- Will something like this work, if i understood the requirement correctly ??

SELECT SUM(colH) as COL_SUM_ALL from (SELECT SUM(colx+coly+colz) as colH from (SELECT SUM(col1) as colx, SUM(col2) as coly,SUM(col3) as colz FORM T1 WHERE .... ............. GROUP BY *** ) as V1) Where GRoup By *** )as V2 where Group By *** *** --> Group By only if you want to select other column with non-column function. [...] 10550 93 31_Re: Can this be done using SQL?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 31 Jan 2001 23:39:42 -0600625_iso-8859-1 Carlton,

If I read your requirement correctly, what you are after is simply:

SELECT SUM(TRANSCNT) AS TRNS_CNT, SUM(SELECTS)+ SUM(INSERTS)+ SUM(UPDATES)+ SUM(DELETES)+ SUM(OPENS)+ SUM(FETCHES) AS SQLCNT, SUM(EDB2TOD)/60 AS INDB2_TIME, SUM(EDB2TCB)/60 AS INDB2_CPU, SUM(BP4KGETPAGE)AS GETPAGE, SUM(BP4KPREFET)+ SUM(BPLISTPREF) + SUM(BPDPF) AS PRE_FETCHES, SUM(WAITREADIO) AS OTHERIO_WAITE_TIME, SUM(BP4KSYNCRD) AS SYNC_READ, SUM(EWAITIO) AS IO_WAIT_TIME, SUM(BPPGUPDAT) AS BUFFER_UPDT FROM CACLI.CAC_PERF_STATS WHERE PLANNAME='PLANNAME' AND CONNECTION LIKE 'CICS%' AND DATE = CURRENT DATE - 1 DAY [...] 10644 107 37_Re: Retrieving the 30 smallest values13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 31 Jan 2001 23:49:21 -0600441_iso-8859-1 You would select what you wanted. Wouldn't you? If you needed the top 30 rows, then select them. If you want the top 30 distinct, then select those with duplicates removed. No use selecting an extra 1 or 2 if your screen etc is limited to 30.

The worst possible solution to this problem is this query: SELECT T1.C1, T1.C2 FROM MYTABLE T1 WHERE 30 > (SELECT COUNT(*) FROM MYTABLE T2 WHERE T2.C2 < T1.C2) ORDER BY T1.C2 [...] 10752 81 38_Re: Checking Consistency Tokens in DB210_john clark20_pak02449@PIXIE.CO.ZA30_Thu, 1 Feb 2001 07:49:14 +0530511_us-ascii Hi Gerald,

I agree that if you are in a massive environment that a commercial consistency token viewer is a good idea. However, as we are the agents for Ralational Architects, it is obviously to our benefit to allow free access to anything we write.

In addition, the REXX I wrote is extremely simple and probably tok me about a day to write. For my needs as a DBA at the time it did everything I needed (i.e proved to the apps. people that the "bind had not fallen off the planet"!) [...]