1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2001, week 4 2 69 67_Re: Invoking a DB2 program from a REXX exec in the fore-ground mode17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT31_Wed, 22 Aug 2001 07:41:42 +0100551_us-ascii This should be enough.

"NEWSTACK" QUEUE "RUN PROGRAM(MYPGM) PLAN(MYPLN) " !!, "LIB('MYLIB.LOAD')" QUEUE "END" "DSN SYSTEM(DB2P)" "DELSTACK"

Best regards.











Harish G @RYCI.COM> on 22/08/2001 00.49.06

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Invoking a DB2 program from a REXX exec in the fore-ground mode [...] 72 37 11_unsubscribe14_David Bargeron29_David.Bargeron@BTINTERNET.COM31_Tue, 21 Aug 2001 18:16:18 +010022_iso-8859-1 unsubscribe 110 89 0_22_Preethy B, J16 Chennai25_preethyb@MSDC.HCLTECH.COM31_Wed, 22 Aug 2001 11:48:41 +0530452_iso-8859-1 Hi Issac,



I am in the process of preparing an approach document for the conversion of IMS to Db2. Can u help me out in sending any documents which u have prepared for your project?

Have u got any application program which you had converted or any documents which u had prepared for going about with the conversion? If u can send some sample program which u had converted I can probably get a better idea about it. [...] 200 16 53_Debra Jordan/Riverwoods/BT/DFSI is out of the office.15_Debra D. Jordan33_debrajordan@DISCOVERFINANCIAL.COM31_Wed, 22 Aug 2001 01:22:01 -0500447_us-ascii I will be out of the office starting 08/22/2001 and will not return until 08/24/2001.

I will be out of the office for the remainder of the day. 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. 217 15 53_Cathy Drummond/Santa Teresa/IBM is out of the office.14_Cathy Drummond18_cdrummo@US.IBM.COM31_Wed, 22 Aug 2001 00:41:00 -0600399_us-ascii I will be out of the office starting August 21, 2001 and will not return until August 23, 2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 233 156 25_Re: BETWEEN vs. >= and <=18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Wed, 22 Aug 2001 09:16:19 +0200455_iso-8859-1 I think to see what happen you can do a db2exfmt and see what sql statement the optimizer has written. for the two request. And see if they are the same...

Jean-Jacques Borie Directeur recherche et développement

Tekora, changez votre conception des sites web http://www.tekora.com 17, place de la Résistance - 92130 Issy-les-Moulineaux Tél. : +33 1 55 00 10 03 - Fax : +33 1 55 00 10 10 Por: +33 660199524 jjborie@tekora.com [...] 390 29 35_Re: Unavailable Resource - SYSLGRNX10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 22 Aug 2001 09:31:29 +0200447_us-ascii Hi all

I remember (but it was some years ago) that some 3rd party copy utility couldn't copy DB2 catalog/directory and this

was due to the fact that these utilities are not 'controlled' by DB2. Is it still true ??

Thanks in advance

Max Scarpa Useless non-certified DB2 sysprog Expensive S390 human optional

'The gravity law is the same for everybody, even if some plastic surgeons try to break it' [...] 420 30 45_Re: OS/390 memory abend - Reorg w/ partitions13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Wed, 22 Aug 2001 02:26:12 -0500596_ISO-8859-1 Hi Missy !

We had exactly the same problem while running online reorg (abending with S878 in 4 of 5 times). Try to reduce (!) the region-size. We are running online-reorg with 6144K (region-size in JCL-Header and "step"-size). When we use a bigger region size (i.e. 256 M), the reorg abends with S878, with size=6144K we had no problems at all. I think the number or parallel tasks depends on the chosen region size, because we saw 15 subtasks using 256M and 9 subtasks using 6144K. The problem only occured during reorg of a tablespace having >= four indices, tablespaces [...] 451 132 31_AW: Correlated table expression17_Wittliff, Juergen34_Juergen.Wittliff@DRESDNER-BANK.COM31_Wed, 22 Aug 2001 10:06:08 +0200573_iso-8859-1 The second query in the FROM-Clause is not a "normal" subquery but a nested table. So with the SQL-Coding in this way DB2 dosn't know the x.cola-value in the second query. But perhaps there is a posibility to let DB2 know this value.

Try

select x.cola from (select a.cola from a, b, c, d, e, f where ....) as x --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ,TABLE(select a.cola, sum(b.colb) --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! from a, b where a.cola = x.cola and .... group by a.cola) as w where .... group by x.cola having ..... [...] 584 90 31_Re: Correlated table expression0_24_markus.schaub@SYSTOR.COM31_Wed, 22 Aug 2001 10:24:22 +0200387_us-ascii as far as i can remember you have to precede the table expression with the keyword 'table' to be able to reference a column from the table expression. try the sql with something like this:

select x.cola from TABLE (select a.cola from a, b, c, d, e, f where ....) as x ,TABLE (select a.cola, sum(b.colb) from a, b where a.cola = x.cola and .... group by a.cola) as w [...] 675 42 62_Re: OS390 DB2 V6.1 - Rebuild Index(all) on catalog & Directory12_Paul Packham26_paul.packham@CONSIGNIA.COM31_Wed, 22 Aug 2001 09:42:38 +0000331_us-ascii Aedan,

Don't forget that the cat/dir need to be recovered in a particular order, otherwise it will not work. This is what we do : 1) RECOVER SYSUTILX 2) RECOVER Indexes for SYSUTILX 3) RECOVER DBD01 4) RECOVER other catalog and directory tablespaces and indexes 5) IMAGE COPY catalog and directory tablespaces [...] 718 108 62_Re: OS390 DB2 V6.1 - Rebuild Index(all) on catalog & Directory9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 22 Aug 2001 10:17:41 +0100626_us-ascii Paul/Aedan, Watch out if you have user-defined indexes on your Catalog....

V5 Utility Reference...

"2.13.1.4.8 Recovering Catalog and Directory Objects ...........

| (3) If there are no user defined indexes on the catalog, execute | RECOVER INDEX (ALL) TABLESPACE DSNDB06.SYSxxxx to recover all | the IBM defined indexes on a catalog table space. If user | defined indexes are created on the catalog, the IBM defined | indexes must be recovered individually and the user defined | indexes recovered in a later step. See Appendix D of SQL | Reference for a list of the IBM defined indexes. " [...] 827 78 33_Re: Larry (Oracle) strikes again!16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Wed, 22 Aug 2001 10:25:16 +0100481_us-ascii well this is funny, because I tried to go to Google but I mis-typed it as "www.goggle.com" and I got re-routed to....

http://www.spy-pal.com/

try it for yourselves if you have a moment, it had me doubled up laughing it seemed so appropriate :-)

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...] 906 70 19_Re: Newbie question16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 22 Aug 2001 10:38:12 +0100444_us-ascii did you subscribe twice? or just changed your subscription options?

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

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

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK



* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...] 977 32 70_Gernot Ruban's nice (I think) REXX to calculate BP page residency time10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 22 Aug 2001 11:56:42 +0200326_us-ascii Hi all estimeed listers

I checked an old WEB site (Gernot Ruban's DB2 site was created some years ago) and I found some new REXX execs.

There's a nice REXX (by Mike Bracey) to calculate the residence time of a page in bufferpools and in hiperpools (executing a -DIS BP command).

The URL is: [...] 1010 94 35_Re: UDB on Windows restore question0_20_John_Lendman@FPL.COM31_Wed, 22 Aug 2001 07:26:36 -0400394_us-ascii Endy, thanks for the reply. I will try this, I am V6 and I have not been putting in the database name.

Thanks

John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413





"Endy Lambey" cc: Sent by: "DB2 Subject: Re: UDB on Windows restore question Data Base Discussion List" [...] 1105 213 35_Re: UDB on Windows restore question0_20_John_Lendman@FPL.COM31_Wed, 22 Aug 2001 07:28:18 -0400739_us-ascii Palgrave, thanks I will try what you said.



John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413





"Palgrave, Greg" cc: Sent by: "DB2 Data Base Subject: Re: UDB on Windows restore question Discussion List"



08/21/01 09:44 PM Please respond to "DB2 Data Base Discussion List"











Hi John,

A quirk I have found when trying to do this is that the directory structure is CASE SENSITIVE (thanks very much IBM!) in DB2 so check using DOS etc. that the structure matches case exactly on both machines. From memory, I think DB2 used UPPER case for [...] 1319 88 31_Re: Correlated table expression11_David Nance16_DWNance@FHSC.COM31_Wed, 22 Aug 2001 08:01:52 -0400586_US-ASCII Rick, The -206 is on the columns being referenced in table x. I will try out some of the tips that were passed along and let you all know if it works. Thanks.

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

>>> Rick.Rogers@TELUS.COM 08/21/01 08:27PM >>> Any clue as to which column is being referenced in the -206 error? From your excerpt, I'd assume you believe it is the x.cola reference in the second table reference? Is it possible the error is coming from another column reference? (I'm stumped - it looks like a valid correlated reference to me) [...] 1408 130 48_URGENT! Repartitioning a VLDB in OS/390 DB2 V6.112_Aedan Molony20_Aedan.Molony@PSIR.IE31_Wed, 22 Aug 2001 13:07:25 +0100392_iso-8859-1 Hi list, We have a slot to amend a very big table from 30 parts to 63 parts at the weekend. This is to get around VSAM 4GB limit on some of the partitions. We're going to take the table off air, drop/recreate the table & reload the data previously unloaded while the table was on the air. We reckon the loads to take a couple of hours, during which the table will be off-air. [...] 1539 15 28_Re: ZPARM settings in DB2 V611_Joan Keemle25_KeemleJoanT@JOHNDEERE.COM31_Wed, 22 Aug 2001 06:57:04 -0500354_- Yes, we've had overrides on our panels for years, and install clist did not pull our overrides like we would expect. Instead, it re-calculated and used the calculated values, which were quite a bit smaller in some cases. This got us in to trouble on one system, and that's how we noticed it. Probably should've opened a PMR, but we didn't. * Joan [...] 1555 52 29_DB2 SysProg position in NY...16_Charles Valentin15_VALENCH@AOL.COM29_Wed, 22 Aug 2001 08:13:37 EDT499_US-ASCII Immediate Opening for a: Sr. DB2 Systems Programmer in Orangeburg, N.Y.(Rockland Cty) *** NO AGENCIES, PLEASE *** Must Have Technical Experience in the following areas: DB2 V6.1 w/ Two-Way Data Sharing Environment, QMF, TMONDB2, DB2PM, SMF,SMP/E,FiLe-DB2,Xpediter-TSO/DB2, Bufferpool Tool, RLX,Thread Sentry,Perf/Tuning,DDF,etc. "Qualified" Candidates, please fax/e-mail resume asap to:

Charles Valentin DB2 Systems Support Fax: (845) 365-5937 E-mail: Valench@bam.com Valench@aol.com 1608 27 52_Re: URGENT! Repartitioning a VLDB in OS/390 DB2 V6.10_24_markus.schaub@SYSTOR.COM31_Wed, 22 Aug 2001 14:28:43 +0200323_us-ascii did you consider 'rename table'. what we have done in a similar situation was:

1. create new part. tablespace 2. create new table 3. un-/load data 4. rename old table to something (for easy fallback, if something goes wrong) 5. drop indexes on old table 6. rename new table 7. create & rebuild indexes [...] 1636 140 35_Re: AW: Correlated table expression11_David Nance16_DWNance@FHSC.COM31_Wed, 22 Aug 2001 08:34:00 -0400613_ISO-8859-1 Juergen/Markus, Thanks for the help. I had never seen the prefixing the expression with 'table' before. That did the trick for the -206 that I was fighting with and I got the huge performance gains that I was hoping for. Thanks again.

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

>>> Juergen.Wittliff@DRESDNER-BANK.COM 08/22/01 04:06AM >>> The second query in the FROM-Clause is not a "normal" subquery but a nested table. So with the SQL-Coding in this way DB2 dosn't know the x.cola-value in the second query. But perhaps there is a posibility to let DB2 know this value. [...] 1777 99 31_Re: Correlated table expression0_24_db46@DAIMLERCHRYSLER.COM31_Wed, 22 Aug 2001 08:40:01 -0400318_us-ascii This is because a.cola = x.cola in the second nested table subquery does not relate to the previous as x nested table.

Did you want a.cola = b.cola?

Each nested table is it's own subquery, so DB2 when it attempts to build your second nested table, it does not know what you mean by x.cola. [...] 1877 49 33_Re: Larry (Oracle) strikes again!11_John Wynton21_jwynton@THEMISINC.COM31_Wed, 22 Aug 2001 08:38:37 -0400505_iso-8859-1 Dear Half:

But that is a sponsored link - not the result of a search! Interesting marketing ploy though.

Cheers,

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





-----Original Message----- From: Half Eaten [mailto:half_beaten@YAHOO.COM.AU] Sent: Tuesday, August 21, 2001 10:18 PM To: DB2-L@RYCI.COM Subject: Larry (Oracle) strikes again! [...] 1927 161 34_Rejected posting to DB2-L@RYCI.COM29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV31_Wed, 22 Aug 2001 08:47:23 -0400546_us-ascii If anyone could shed some light on this problem, I would appreciate it. This java code works on a UDB 6.1 server database, but it will not return the second result set when going through db2 connect 6.1 to the mainframe db2 6.1 database. If we insert a second connection statement then the result comes back ok. But we should not have to do that. Is there some parameter on the db2 mainframe that is causing a single connection rather than an interactive session? I have looked through ZPARMS and VTAM parms but have not found any. [...] 2089 32 52_Re: URGENT! Repartitioning a VLDB in OS/390 DB2 V6.110_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 22 Aug 2001 15:05:17 +0200554_us-ascii Why don't you create a table equal to the old table using RENAME TABLE later ?

The owner/creator is the same after rename, you've only to rebind plans/packages (+refresh the staments cached, if any) and to update the tablespace name in utilities jobs (but if you have some 3rd party utilities and you use wildcards it may be not necessary, so use a wise new TS name). You can load the new table and later you can build all NPIs you need. Some tx will work if they use only partitioning index in meanwhile. The old data remains for a [...] 2122 35 75_Cleveland, Ohio-- Distributed Systems DBA and Db2/IDMS Programme r needed--13_Hopkins, John20_JMHopkins@KFORCE.COM31_Wed, 22 Aug 2001 09:30:58 -0400681_- BASED IN CLEVELAND, OHIO $6 Billion Property and Casualty Insurance Company has new relocate able opportunities. (1) Distributed Systems DBA $80K + 15% Bonus -5+ years experience in software implementation and development -Database design experience with UNIX, NT environment utilizing 2 of DB2, Oracle or SQL Server. -Experience with DB2 Connect is essential (2) DB2 and IDMS Database Systems Programmer-$80K + 15% Bonus -7+ years experience -IDMS R14, DB2 V6 systems programming experience in a large multi terabyte database environment -SMPE, Assembler and/or REXX experience -experience migrating to a new version of software (will participate in the migration of DB2V7 [...] 2158 51 35_Re: Unavailable Resource - SYSLGRNX15_Murley, Michael22_Michael_Murley@BMC.COM31_Wed, 22 Aug 2001 08:53:56 -0500466_iso-8859-1 Max,

BMC COPY PLUS copies all the catalog and directory spaces. For the three spaces whose copies are registered in the log rather than SYSIBM.SYSCOPY (DSNDB01.DBD01, DSNDB01.SYSUTILX, DSNDB06.SYSCOPY), we invoke the DB2 Copy utility.

Michael Murley BMC

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Wednesday, August 22, 2001 2:31 AM To: DB2-L@RYCI.COM Subject: Re: Unavailable Resource - SYSLGRNX [...] 2210 23 40_JDBCn connectivity to DB2(v6.1) on OS39010_Earl Dixon19_earl_dixon@BELK.COM31_Wed, 22 Aug 2001 09:01:52 -0500349_- I need some direction!

I have had some developer come to me to find out why they were getting errors trying to connect from a java application running on NT to DB2 on OS390 using JDBC. They have said that they have been able to connect using JDBC going through ODBC to connect to DB2 OS390. The error message that they are getting is: [...] 2234 142 36_Re: DSNREXX and FLOAT/DOUBLE columns14_Toppins, Smike21_smike.toppins@GWL.COM31_Wed, 22 Aug 2001 08:34:29 -0600796_iso-8859-1 I do a SELECT on SYSIBM.SYSTABLES to get table name and CARDF; the "Say NAME CARDF" produces ...

ABSDETLT 0.000000004:: ;DSNREXX ::wx::.: : :@: :w:::: ::::::¢.<(+|&:::::::: :!$*);¬-/::::::::¦,%_>?:::::::::::#@'=":ABCDEFGHI:::::::JKLMNOPQR::::::::STU VWX YZ:::::::::::::::::::::::ABCDEFGHI:::::::JKLMNOPQR::::::::STUVWXYZ::::::0123 456 and continues on ...



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



> ---------- > From: teldb2kals[SMTP:teldb2kals@telstra.com] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, August 21, 2001 7:11 PM > To: DB2-L@ryci.com > Subject: Re: DSNREXX and FLOAT/DOUBLE columns > > Hi Smike, > > I have 2 cols. in a table, one as FLOAT, and the other as DOUBLE. > > I do a SELECT, and [...] 2377 111 44_Re: JDBCn connectivity to DB2(v6.1) on OS39018_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Wed, 22 Aug 2001 16:34:23 +0200462_iso-8859-1 One of my developer recently have the same problem. What we have done is to remove the db2 client from the system. And reinstall it and next he work fine.



Jean-Jacques Borie Directeur recherche et développement

Tekora, changez votre conception des sites web http://www.tekora.com 17, place de la Résistance - 92130 Issy-les-Moulineaux Tél. : +33 1 55 00 10 03 - Fax : +33 1 55 00 10 10 Por: +33 660199524 jjborie@tekora.com [...] 2489 13 5_SPT0113_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Wed, 22 Aug 2001 09:48:15 -0500348_- Fellow listers -

Has anyone else experinced dramatic growth in SPT01 since going to V6 on OS/390?

================================================ 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. 2503 29 19_Foreign key problem15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 22 Aug 2001 10:55:47 -0400578_iso-8859-1 I had problem last night. I thought I will just inform every one.

I have two table table1 and table2

table1 is the parent with table1_col1 and table1_col2 as the primary key in the order I mentioned. Table2 has three column in the key table2_col1 , table2_col2 and table2_col3 in the same order. By mistake I defined a foreign key from table2 to table1 in the order table2_col2 , table2_col1. DB2 allowed me to create this without any problem. I inserted the value 1,100 in table1, but it will not allow me to insert 1,100,1 in table2 saying it is [...] 2533 36 9_Re: SPT0116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 22 Aug 2001 11:03:46 -0400472_iso-8859-1 How dramatic? We went from V5 to V7 and are seeing only slight (about 10% in 2 months) growth of SPT01

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: John Arbogast [mailto:john.arbogast.cquz@STATEFARM.COM] Sent: Wednesday, August 22, 2001 10:48 AM To: DB2-L@RYCI.COM Subject: SPT01



Fellow listers -

Has anyone else experinced dramatic growth in SPT01 since going to V6 on OS/390? [...] 2570 26 23_DB2 security using RACF11_Fred Foster14_B06FMF@FDS.COM31_Wed, 22 Aug 2001 10:55:56 -0400346_us-ascii Hi List,

We are considering using RACF for DB2 security via the DSNX@XAC exit(i.e. using RACF permits instead of doing DB2 grants). We are on DB2 v6. Has anyone else done this and if so:

How is it working? Do you like it? If you had it to do over would you do it again? Did you encounter any issues or major problems? [...] 2597 38 12_DB2 V6 & QMF18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 22 Aug 2001 11:08:40 -0400491_iso-8859-1 Has anyone noticed this problem in QMF:

I run a query and get the result set back to my screen, so far no problem. Then I go into 'form' and do a 'count' on a specific column. I go back out to the form and try to get to the bottom of the result set and NOTHING! It just sits there and will not max down.. I cancel out of the query and run the query again. Then I just do a 'M' and page down and again NOTHING. It just sits there for ever. This particular result set I [...] 2636 46 44_Re: JDBCn connectivity to DB2(v6.1) on OS39014_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 22 Aug 2001 11:12:10 -0400452_iso-8859-1 Is the db2jd process running? If not use db2jstrt with the appropriate port number to start it (default is 6789). Your developer is using 'net' driver which requires db2jd to be up and running.

Manas.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Earl Dixon Sent: Wednesday, August 22, 2001 10:02 AM To: DB2-L@RYCI.COM Subject: JDBCn connectivity to DB2(v6.1) on OS390 [...] 2683 41 37_Re: Subject: ZPARM settings in DB2 V611_Mike Dupuis23_Mike.Dupuis@CLARICA.COM31_Wed, 22 Aug 2001 11:12:59 -0500402_us-ascii Hi Stephen,

I was wondering if you could elaborate on the types of performance problems that were encountered as well as the degree/type of change implemented. I have just increased the SORT POOL and RID POOL size parms on our V6.1 Production DB2 this past weekend. I have had no direct remarks back from our users community and have not noted anything in our DB2 monitors as yet. [...] 2725 74 44_Re: JDBCn connectivity to DB2(v6.1) on OS39010_Earl Dixon19_Earl_Dixon@BELK.COM31_Wed, 22 Aug 2001 11:24:12 -0400426_us-ascii Can you tell me where I can find db2jd to see if it is running. If it is not where can I find instructions on how to start it.

Thanks for your help

Earl







Manas Dasgupta @RYCI.COM> on 08/22/2001 11:12:10 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 2800 37 25_Re: BETWEEN vs. >= and <=14_Richard Yevich17_ryevich@YAHOO.COM31_Wed, 22 Aug 2001 08:27:20 -0700328_us-ascii Let me correctly explain this. When the same column is the left operand, then the between and the = are the same for Filter Factor calculation, NOT ACCESS PATHS.

The [between] is 1 predicate, [col =] are 2 predicates therefore offering the possibility of a different access path based on indexes available. [...] 2838 200 16_Re: DB2 V6 & QMF14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 22 Aug 2001 16:32:16 +0100473_iso-8859-1 This is not a problem with QMF it's a feature (!)

When you run an SQL select in QMF, it declares a cursor and fetches the first 200 (or so) rows and slings them on the screen. It then continues fetching in the background.

When you try and MAX to the bottom, you now have to wait for all the FETCHes to complete before you see any more data. Now you are in SPUFI and are seeing the same problem - it is taking an age to get all your rows back. [...] 3039 92 31_Re: Correlated table expression14_Richard Yevich17_ryevich@YAHOO.COM31_Wed, 22 Aug 2001 08:35:02 -0700336_us-ascii Correlated table expressions require the TABLE specification keyword as folows: HOWEVER WATCH YOUR ACCESS PATHS AS THIS CONSTRUCT CAN CAUSE SCANS)

This does work from a table joined to a correlated table expression, but I have not checked whether it will work with a TE joined to a correlated TE but give it a try. [...] 3132 67 16_Re: DB2 V6 & QMF11_Knust, Paul28_Paul.Knust@MORGANSTANLEY.COM31_Wed, 22 Aug 2001 11:31:12 -0400385_iso-8859-1 Are you saying this worked in v5? Is this a complex query? (Joins, etc) How long did you wait before canceling the query? Check an EXPLAIN of the query. Have someone watch your thread in whichever performance monitor you use. If the query has an unexpectedly enormous result set or if DB2 has to build a huge composite table, you will see this going on in the monitor. [...] 3200 91 44_Re: JDBCn connectivity to DB2(v6.1) on OS39014_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 22 Aug 2001 11:41:49 -0400462_US-ASCII If you are running on NT check 'Task Manager' and look under 'Processes'.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Earl Dixon Sent: Wednesday, August 22, 2001 11:24 AM To: DB2-L@RYCI.COM Subject: Re: JDBCn connectivity to DB2(v6.1) on OS390



Can you tell me where I can find db2jd to see if it is running. If it is not where can I find instructions on how to start it. [...] 3292 25 35_Re: Unavailable Resource - SYSLGRNX10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 22 Aug 2001 17:43:43 +0200465_us-ascii Hi Michael & thanks for your reply

I remember this fact correctly, more or less, and it was what I meant in the previous post, as 3rd party utilities work directly on underlying VSAM file.

If i remember well some old versions of BMC copy+ couldn't copy catalog/directory tablespaces at all but now it calls DSNUTILB to provide this feature (as they call REPAIR to reset COPY pending status after a reorg+ with inline copy for instance). [...] 3318 65 23_Re: Foreign key problem11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Wed, 22 Aug 2001 09:44:32 -0600392_iso-8859-1 Unless I have misunderstood, this should be allowable Sibi.

Columns should be independent of their location in the table layout.

Also, primary key columns may be renamed in the child table, DB2 can't assume that FK columns should have the same name. This is especially useful when you have two foreign key references to the same parent table in one child table. [...] 3384 73 16_Re: DB2 V6 & QMF12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Wed, 22 Aug 2001 11:36:28 -0400551_US-ASCII Steve-

I would change the query to verify that the number of rows you are bringing back is actually 29,000. The thing to remember about QMF is that it will "buffer" a few pages at a time, expecting that you will not need the entire result set. At a previous employer, when we implemented governing, we ran into a similar problem in that the user would be presented a screen and they thought that the query had completed. Because in reality it had not, when they went to print a report (or max to the bottom - essentially the same [...] 3458 131 52_Re: URGENT! Repartitioning a VLDB in OS/390 DB2 V6.114_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Wed, 22 Aug 2001 08:51:41 -0700333_iso-8859-1 Max, One potential problem is that there are quite a number of restrictions on RENAME table, not the least of which include the fact that the table being renamed can NOT have any views dependant upon it. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 3590 56 9_Re: SPT0115_Tanya Haverchak36_Tanya.Haverchak@ANTARESSOLUTIONS.COM31_Wed, 22 Aug 2001 11:43:41 -0400506_us-ascii It happened to us. DB2 directory jumped by 500 cyl. after V6 migration. I ran the reorg on the SPT01 to put it in to one extent, and the next Monday it grew again. I opened ETR with IBM, they were not much help. I was told that some growth is expected, but they could not tell why and by how much. ( I was told that information is proprietary) It has been stable ever since, and otherwise there is no problem. How ever the whole thing is a "mystery". I would be very curios to see if any one [...] 3647 34 34_GET DATABASE CONFIGURATION Command10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Wed, 22 Aug 2001 08:54:39 -0700354_us-ascii

The command GET DB CFG FOR xxxx is working from my server. However, it gives error when I issued the command from CLP or Command window of DB2 client after connected to that database. The message is SQL1403N The username and/or password supplied is incorrect. SQLSTATE=08004

What I should do to make it work from the cleint? [...] 3682 101 16_Re: DB2 V6 & QMF18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Wed, 22 Aug 2001 11:56:00 -0400513_iso-8859-1 Thanks.. I'll try a few of the things suggested here...As far as I know this worked fine in V5 and I don't remember maxing down to the bottom of a QMF result taking so long.. I look at our TMON monitor for the thread and its just grabbing all sorts of cpu time but never gets to the bottom. I'm also hearing now that we are getting funny results back from queries that use to work in V5 and now are acting strange in V6..So who knows what is going on.. Guess I need to dig into this stuff further [...] 3784 74 23_Re: Foreign key problem9_Sally Mir22_Sally.Mir@WACHOVIA.COM31_Wed, 22 Aug 2001 12:11:17 -0400379_us-ascii When you created your foreign key, you told DB2 that table2_col2 had to have a corresponding table1_col1 as well as table2_col1 with a matching table1_col2.

Table1_col1 contained the value of 1 and table1_col2 contained the value of 100. So the proper child in table2 would have 100 in table2_col1 and 1 in table2_col2 in order to have a valid relationship. [...] 3859 43 38_Re: GET DATABASE CONFIGURATION Command18_Gert van der Kooij15_geko@WANADOO.NL31_Wed, 22 Aug 2001 18:11:51 +0200671_iso-8859-1 Hi,

You need to attach to the remote instance (node) with an authorized user before issuing a 'get snapshot' command.

----- Original Message ----- From: "Mathai Joy" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, August 22, 2001 5:54 PM Subject: GET DATABASE CONFIGURATION Command



> > The command GET DB CFG FOR xxxx is working from my > server. However, it gives error when I issued the > command from CLP or Command window of DB2 client after > connected to that database. The message is > SQL1403N The username and/or password supplied is > incorrect. SQLSTATE=08004 > > What [...] 3903 36 38_Re: GET DATABASE CONFIGURATION Command13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 22 Aug 2001 11:22:22 -0500414_US-ASCII Hi Mathai, You need to attach to the remote instance. HTH Kurt

>>> mathaijoydb2@YAHOO.COM 08/22/01 10:54AM >>>

The command GET DB CFG FOR xxxx is working from my server. However, it gives error when I issued the command from CLP or Command window of DB2 client after connected to that database. The message is SQL1403N The username and/or password supplied is incorrect. SQLSTATE=08004 [...] 3940 24 52_Re: URGENT! Repartitioning a VLDB in OS/390 DB2 V6.110_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 22 Aug 2001 18:27:31 +0200318_us-ascii True, you've to drop/create all views again , but if you've a DDL generator toolit easy to rebuild them, it depends on how many views you have and how views are used by application(s).

I used many times the RENAME features, I don't see so many restrictions . Of course it depends of application's [...] 3965 16 19_Re: DBRM encodings.12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 22 Aug 2001 12:07:15 -0500372_- I designed the DBRM a long time ago and worked on the big redesign in V2R3 and still am working a bit. It is not a design point to end with 00. As Gerald noted, use the macros and lengths of the fields or you're likely to get incorrect answers at times. The macros do include comments, and read the old format DSNXDBRM comments as well as the new ones in DSNXNBRM. [...] 3982 49 35_Re: Unavailable Resource - SYSLGRNX15_Murley, Michael22_Michael_Murley@BMC.COM31_Wed, 22 Aug 2001 12:13:08 -0500402_iso-8859-1 Max,

One more quick clarification: COPY PLUS only invokes the DB2 Copy utility to copy the three 'Special' spaces and to copy the two LOB catalog table spaces in DB2 V7.1. We copy all other catalog and directory tables ourselves.

You are correct that some old versions of COPY PLUS didn't support the catalog and directory. We added that support in V3.4.00 in March 1993. [...] 4032 18 29_Interactive SQL's and Explain8_K.Balaji19_K.Balaji@TARGET.COM31_Wed, 22 Aug 2001 12:15:28 -0500470_iso-8859-1 Hi all, Can I get explain results for the SQLs that I issue from SPUFI or from ISQL option from Platinum? It looks I have to have DBRM or Package or Plan to do this. Am i right here ? Thanks Balaji Ph # (510) 727-3259

================================================ 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. 4051 40 36_Automatic checks for a sane database12_Bill Littman24_blittman@TOMOTHERAPY.COM31_Wed, 22 Aug 2001 12:27:04 -0500566_iso-8859-1 Hi-

I am developing a DB2 database to be embedded in a medical device (for cancer treatment using therapeutic doses of radiation). For FDA reasons, I need to develop an automatic test to check whether DB2, and the data in my database, is in a sane state.

I am an application programmer and ***definitely*** not a DBA, so I can develop tests to check that our data makes sense at an application level. However, I am at a loss at tests which work at the DB2 level. Things like: -has DB2 crashed? -is DB2 in an inconsistent state? -are my [...] 4092 15 20_Re: manual GH21-108312_Roger Miller19_millerrl@US.IBM.COM31_Wed, 22 Aug 2001 12:23:16 -0500526_- That book is almost ten years old, so it's missing DB2 V2R3, V3, V4, ... and similar changes in IMS and other products. Replication changed a lot, for instance. If you get the book, please be very careful, since some of the information is obsolete.

Roger Miller, DB2 fos 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. 4108 36 33_Re: Interactive SQL's and Explain11_David Nance16_DWNance@FHSC.COM31_Wed, 22 Aug 2001 13:33:15 -0400517_US-ASCII Ensure you have a plan table. Then in front of your SQL add the line EXPLAIN PLAN SET QUERYNO=n FOR For n use any nbr. Then you can query your plan_table for that queryno.

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

>>> K.Balaji@TARGET.COM 08/22/01 01:15PM >>> Hi all, Can I get explain results for the SQLs that I issue from SPUFI or from ISQL option from Platinum? It looks I have to have DBRM or Package or Plan to do this. Am i right here ? Thanks Balaji Ph # (510) 727-3259 [...] 4145 177 25_Re: BETWEEN vs. >= and <=12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 22 Aug 2001 20:33:05 +0200409_iso-8859-1 Hi Filter factor is different even though it's logically the same. (DB2 admin. guide part 5, chapter 31 of V7)

Default filter factors for simple predicates

Table 102 lists default filter factors for different types of predicates.DB2 uses those

values when no other statistics exist.

Example:The default filter factor for the predicate C1 ='D'is 1/25 (0.04).If D is [...] 4323 32 33_Re: Interactive SQL's and Explain15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 22 Aug 2001 13:42:24 -0400501_iso-8859-1 Use Explain SQL and then select the rows from the plan_table with queryno

-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Wednesday, August 22, 2001 12:15 PM To: DB2-L@RYCI.COM Subject: Interactive SQL's and Explain



Hi all, Can I get explain results for the SQLs that I issue from SPUFI or from ISQL option from Platinum? It looks I have to have DBRM or Package or Plan to do this. Am i right here ? Thanks Balaji Ph # (510) 727-3259 [...] 4356 38 33_Re: Interactive SQL's and Explain13_Thomas, Janis19_Janis.Thomas@CA.COM31_Wed, 22 Aug 2001 13:41:42 -0400681_iso-8859-1 When you are using the Interactive SQL (ISQL) feature of CA's RC/Update product, you would need CA' SQL-Ease product installed to perform a DB2 EXPLAIN.

Janis Thomas Computer Associates DB2 Product Management 630-505-6247 janis.thomas@ca.com







-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Wednesday, August 22, 2001 12:15 PM Subject: Interactive SQL's and Explain



Hi all, Can I get explain results for the SQLs that I issue from SPUFI or from ISQL option from Platinum? It looks I have to have DBRM or Package or Plan to do this. Am i right here ? Thanks Balaji Ph # (510) 727-3259 [...] 4395 21 30_Re: external stored procedures12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 22 Aug 2001 12:40:36 -0500618_- In addition to the other places, there are several red books on stored procedures.

Cross-Platform DB2 Stored Procedures: Building and Debugging, SG24-5485-01, (May 2001)

DB2 Java Stored Procedures Learning by Example, SG24-5945

Getting Started with DB2 Stored Procedures: Give Them a Call through the Network, SG24-4693

Roger Miller, DB2 for 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. 4417 27 16_Re: DB2 V6 & QMF15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Wed, 22 Aug 2001 18:13:29 +0200340_us-ascii Just a guess here, but I think that the QMF query, although it starts displaying data, has not finished executing. Test this by running the QMF query, while monitoring via whatever software you have (we've found BMC's Apptune quite useful). Check to see when the query has actually finished, and then try doing your form etc. [...] 4445 38 22_UDF story - OS/390 DB212_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 22 Aug 2001 20:57:56 +0200554_windows-1255 Hi, If you're not interested in UDF on OS/390 DB2 - please skip.

Recently we needed a UDF on our OS/390 DB2 V6. It was written in assembler, very clean & compact. Upon execution ( "select udf(cola),udf(colb) ... from .... where key_field=:hv" ) we noticed it takes much longer then without the udf. We opened detailed trace in omegamon/db2 and found the udf to take about 15 ms per invocation (not per row !) - means if you had 5 columns with the udf it was about 70-80ms more per row. We thought that the udf was not coded good [...] 4484 84 36_Re: DSNREXX and FLOAT/DOUBLE columns19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Wed, 22 Aug 2001 14:10:30 -0400631_ISO-8859-1 Smike, Wrap around the CARDF with INTEGER function like INTEGER(CARDF) in your SELECT statement. Hope this helps.

Murari Selvakesavan First Health Services Corp. 804.965.7601





>>> smike.toppins@GWL.COM 08/22/01 10:34AM >>> I do a SELECT on SYSIBM.SYSTABLES to get table name and CARDF; the "Say NAME CARDF" produces ...

ABSDETLT 0.000000004:: ;DSNREXX ::wx::.: : :@: :w:::: ::::::¢.<(+|&:::::::: :!$*);*-/::::::::|,%_>?:::::::::::#@'=":ABCDEFGHI:::::::JKLMNOPQR::::::::STU VWX YZ:::::::::::::::::::::::ABCDEFGHI:::::::JKLMNOPQR::::::::STUVWXYZ::::::0123 456 and continues on ... [...] 4569 19 29_Re: DB2 UDB 7.1 on OS/390 2.512_Roger Miller19_millerrl@US.IBM.COM31_Wed, 22 Aug 2001 13:18:20 -0500546_- I'd guess that you are very likely to find breakage in trying to run back more than three years, and then it's very expensive. I think that your OS is out of service now, with six more current releases. If it's not now, then the date is very soon. A number of the functions in DB2 V6 need later releases than yours. We don't have any design, documentation, testing or support to help you when you crash. If you are trying to run on old machines, they won't work. The announcement has a section about hardware prerequisites that covers the [...] 4589 53 9_Re: SPT0113_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Wed, 22 Aug 2001 13:10:58 -0500401_- We have a test subsystem that has been around since DB2 v2.3. It is very large. SPT01 however, has always remained comfortably on one volume. Since V6, it has pushed its way into another dataset on a second volume. We have been cleaning up on a regular basis, but we cannot seem to stay in a single dataset. Sorry I can't be more specific, but we don't have any dataset statistics from pre V6. [...] 4643 42 15_db2ilist issues15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Wed, 22 Aug 2001 11:45:31 -0700511_us-ascii Db2 gurus..

we are running v6 and v7 on AIX. When I issue "db2ilist" command from the instance of v7, all the instances of v7 are listed but if I issue "db2ilist" from the instance of v6, then one of the instances of v7 is also displayed. pls. let me know the reason. I think(not sure) some one(having root authority) might have created the same instance in both versions. If my assumption is correct how can I remove the instance of v6 from the listing without harming the instance of v7. [...] 4686 191 38_Re: Rejected posting to DB2-L@RYCI.COM13_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Thu, 23 Aug 2001 04:49:17 +1000487_iso-8859-1 Maybe it is because you are not closing the statement. I cannot understand why this is working on the host. I assume that you are running it from with USS.

Do you have VAJ on the other side of connect.

Greg Farquhar

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Sherri Sterling DIGEST NOMIME Sent: Wednesday, August 22, 2001 10:47 PM To: DB2-L@RYCI.COM Subject: Rejected posting to DB2-L@RYCI.COM [...] 4878 37 37_Assistance With SPB from NT to OS/39016_Eichenbaum, Fred19_EichenbaumF@DNB.COM31_Wed, 22 Aug 2001 15:00:49 -0400567_iso-8859-1 > Hi, > > I installed Enterprise Edition 7.2 on my NT pc using the "typical" > installation. I was able to configure the Client Configuration Assistant > to communicate with a subsystem on OS/390, the "test" option on the panel > verified this. When I try to connect through Stored Procedure Builder, I > get the following message: > > [IBM][CLI Driver] SQL0332N There is no available conversion for the source > code page "1208 UNKOWN 3" to the target code page "". Reason Code "". > SQLSTATE=57017 > > I gather something needs to be modified, but I [...] 4916 29 39_Re: Correlated Nested Table Expressions14_Joseph Fuentes17_Joedbadb2@AOL.COM29_Wed, 22 Aug 2001 15:02:12 EDT375_US-ASCII Richard,

I believe you to be wrong on this one.

According to the recent presentation at the IDUG conference in North America - the TABLE keyword is not required for a correlated nested table expression. The presentation was Leading Edge SQL Volume II by Sheryl Larsen - who is your buisness associate, so I would assume you to have reviewed this. [...] 4946 50 19_Re: db2ilist issues13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 22 Aug 2001 14:10:57 -0500400_US-ASCII Hi Rajesh, You can use the db2idrop command. You can use the CLP and the set db2instance= command to set the current instance. Then use the list database directory command to see if you have databases cataloged in that instance. Also issue a list node directory and maybe even a list dcs directory. You could then verify if the instance was inadvertently created in V6 system. HTH Kurt [...] 4997 18 64_REORG TABLESPACE PART X SHRLEVEL REFERENCE and DATA AVAILABILITY14_Donald A Smith18_dsmithj@US.IBM.COM31_Wed, 22 Aug 2001 15:53:18 -0400363_us-ascii Dear List What is the impact of a REORG TABLESPACE PART X SHRLEVEL REFERENCE on any NPI's (Non-partitioning indexes) associated with the table? Are they available to the users or will there be some data unavailability? If they are available, how long do they stay that way? I will be dealing w/ some NPI's that are over 16 gig in size . thanks, Don [...] 5016 24 38_DB2 get database configuration command10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Wed, 22 Aug 2001 13:13:32 -0700628_us-ascii Is anybody can give me where I can issue GET DB CFG FOR databasename and get all the configurations from my clent terminal is connected to db2server in diffrent location.

Thanks,

Mathai Joy

__________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.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. 5041 73 19_Re: db2ilist issues15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Wed, 22 Aug 2001 13:50:03 -0700331_us-ascii Hi Kurt If I issue db2idrop then I think it will remove the complete directory under that instance. It causes problems to the actual instance that is existing in v7. pls let me know if there is any file or registry variable to be updated(or set) so that the instance name will not appear under v6 listing. [...] 5115 23 35_DB2 V6 - IRLM Link List Requirement10_King C. Au15_King_Au@GAP.COM31_Wed, 22 Aug 2001 13:58:39 -0700520_us-ascii We are in the process of upgrading DB2/OS390 to Version 6. Page 67 of the Installation Guide indicates that the IRLM module DXRRL183 must be added to the link list. I believe this is a new requirement for DB2 V6 as we have not had this module in our link list since we first installed DB2 Version 2.1 in our shop. IBM told us this module is needed in the linklist for (1) MVS to do a component trace, and (2) the "event exit" that ARM needs. For customers who are not using ARM and MVS CTRACE on IRLM then [...] 5139 101 39_Re: Correlated Nested Table Expressions14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 22 Aug 2001 15:12:49 -0500422_us-ascii Wow! Talk about opening a can of worms!!!

1 - Sheryl Larsen became disassociated from one of my companies, RYC Inc., a year ago. The material you talk about was NOT certified by me, RYC, or YL&A. 2 - You are referring to untested/un-researched material, and it is incorrect and inaccurate. The TABLE keyword is ABSOLUTELY required. If you run the queries without it, you will get a negative SQL code. [...] 5241 116 27_Copy data from SSID to SSID14_Toppins, Smike21_smike.toppins@GWL.COM31_Wed, 22 Aug 2001 15:08:58 -0600680_- Curious as to how others are copying data from SSID.DB.TS to SSID2.DB2.TS. The options that I can see are to DSNTIAD SELECT * FROM DB.TB to dataset and LOAD INTO DB2.TB or DSN1COPY with OBIDXLAT. Any other options without 3rd party tools?



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



> ---------- > From: JCameron@MSI-INSURANCE.COM[SMTP:JCameron@MSI-INSURANCE.COM] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, August 09, 2001 12:49 PM > To: DB2-L@ryci.com > Subject: Re: Utility To Update Statistics > > There is SQL that generates output SQL in the Administration > Guide for Version 5 (page 5-254) under [...] 5358 217 25_Re: BETWEEN vs. >= and <=14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Wed, 22 Aug 2001 15:18:41 -0500374_iso-8859-1 Issac,

What you represent is true, but for the exception mentioned. When the column name is the same, as of DB2 Os/390 V6, the following are equivalent (just call it internal query rewrite) and use the BETWEEN filter factor. This was an announced feature of that release.

COL_ABC BETWEEN :low AND :high and COL_ABC >= :low AND COL_ABC >= :high [...] 5576 174 31_Re: Copy data from SSID to SSID64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 22 Aug 2001 16:42:02 -0500373_ISO-8859-1 Hi SMike.

(I think you meant DSNTIAUL, not DSNTIAD).

Another option using non-purchased tools DB2 V6 and below is to use the distributed reorg utility and do a REORG UNLOAD ONLY. On the target system, perform a LOAD REPLACE FORMAT UNLOAD. If your OBIDs are the same, no problem. If not, edit the data to change it, manually or with a program. [...] 5751 156 31_Re: Copy data from SSID to SSID0_19_Tim.Lowe@STPAUL.COM31_Wed, 22 Aug 2001 16:36:18 -0500621_us-ascii How about Reorg Unload External, and then load?







"Toppins, Smike" To: DB2-L@RYCI.COM Subject: Copy data from SSID to SSID Sent by: DB2 Data Base Discussion List



08/22/2001 04:08 PM Please respond to DB2 Data Base Discussion List











Curious as to how others are copying data from SSID.DB.TS to SSID2.DB2.TS. The options that I can see are to DSNTIAD SELECT * FROM DB.TB to dataset and LOAD INTO DB2.TB or DSN1COPY with OBIDXLAT. Any other options without 3rd party tools? [...] 5908 137 31_Re: Copy data from SSID to SSID11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Wed, 22 Aug 2001 14:30:21 -0700428_- Mostly UNLOAD/LOAD since we have multiple systems we usually load with the same data. Just to keep it interesting, we do also use a few DSN1COPY with XLAT's.......



Robert Jans Albertson's, Inc. (standard disclaimers apply)

-----Original Message----- From: Toppins, Smike [mailto:smike.toppins@GWL.COM] Sent: Wednesday, August 22, 2001 3:09 PM To: DB2-L@RYCI.COM Subject: Copy data from SSID to SSID [...] 6046 69 23_Re: Foreign key problem13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Wed, 22 Aug 2001 14:53:17 -0700588_us-ascii Sibi,

While a modeling tool would probably recognize the specified dependency, and generate the correct DDL, I believe that DB2 will allow you to be as creative (on purpose or accidently) as you wish as long as the respective columns have similar data types that can be compared.

Tink --- "Philip, Sibimon" wrote: > I had problem last night. I thought I will just > inform every one. > > I have two table table1 and table2 > > table1 is the parent with table1_col1 and > table1_col2 as the primary key in > the order I mentioned. Table2 [...] 6116 93 19_Re: db2ilist issues13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 22 Aug 2001 17:22:53 -0500505_US-ASCII Hi Rajesh, I was under the impression you had 2 identical instances. 1 in V6 and 1 in V7. Why would dropping the V6 instance affect the V7 instances ? The products are installed into different directories right ?. Is the V6 instance obsolete ?. I'm unaware of any files or registry variables that can be updated(or set) so that the instance name doesn't appear under the V6 listing. As long as the instance exists in V6 it will show up in the db2ilist. Am I missing something ? Thanks, Kurt [...] 6210 227 31_Re: Copy data from SSID to SSID13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Wed, 22 Aug 2001 15:50:06 -0700611_us-ascii Todd,

I know you have to re-map the OBIDs with DSN1COPY, but is that necessary with a DSNTIAUL load even with format unload?

Tink --- Todd Burch ***************************************************** wrote: > Hi SMike. > > (I think you meant DSNTIAUL, not DSNTIAD). > > Another option using non-purchased tools DB2 V6 and > below is to use the distributed reorg utility and do > a REORG UNLOAD ONLY. On the target system, perform > a LOAD REPLACE FORMAT UNLOAD. If your OBIDs are the > same, no problem. If not, edit the data to change > it, manually or with [...] 6438 207 36_Re: DSNREXX and FLOAT/DOUBLE columns10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 23 Aug 2001 09:33:45 +1000469_us-ascii Hi Smike,

Can I have a copy of ur full Rexx routine ? Also, what version of DB2 r u on ?

I run a Select from Systables, and get the CARDF values correctly displayed. I dont want to hazard a guess on what cd be the problem.

Cheers, Kals



-----Original Message----- From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] Sent: Thursday, August 23, 2001 12:34 AM To: DB2-L@RYCI.COM Subject: Re: DSNREXX and FLOAT/DOUBLE columns [...] 6646 103 31_Calling all SQL Guru's.........10_Shery Hepp17_schepp@SRPNET.COM31_Wed, 22 Aug 2001 16:43:05 -0700502_iso-8859-1



I have a query(this is a condensed version just for testing purposes) that ultimately will become a view (so I have certain SQL limitations). I have it returning the appropriate data- but because of the nature of the relationships within the result set I'd like to format it differently. Union is not an option since this is a view and we're DB2 OS/390 V6 not V7. I've tried a group by clause but that ultimately won't work because one of the columns is VARCHAR(31000). [...] 6750 15 22_Floating Point Numbers0_20_bjnigh@HOUSEHOLD.COM31_Wed, 22 Aug 2001 16:43:19 -0700505_us-ascii Has anyone used floating-point numbers in OS/390 DB2 tables? I am trying to see if you are able to get a consistent result each time the FLOAT function is used. I have never used this data type before. Does anyone have any experience with this data type?

================================================ 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. 6766 79 84_New Book Released: DB2 Universal Database for OS/390 Certification Guide Version 7.112_Susan Lawson24_susan_lawson@YLASSOC.COM31_Wed, 22 Aug 2001 18:52:57 -0500599_iso-8859-1 Press Release: August 23rd, 2001

New Book Released: DB2 Universal Database for OS/390 Certification Guide Version 7.1

ISBN: 0-13-065076-5 IBM Ordering Number: SC09-4928-00

IBMs DB2 Universal Database Version 7.1 for OS/390 delivers unparalleled performance, scalability, and reliability in today's enterprise e-business environments. Now there's a complete, authoritative guide to deploying, managing, and developing applications with DB2 UDB 7.1 in both OS/390 and z/OS environments - and preparing for IBM's updated DB2 UDB V7.1 for OS/390 certification exam. [...] 6846 133 31_Re: Copy data from SSID to SSID16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Wed, 22 Aug 2001 17:12:47 -0700567_iso-8859-1 I believe you've listed all the options except I think you meant to say DSNTIAUL instead of DSNTIAD.

Duane

-----Original Message----- From: Toppins, Smike [mailto:smike.toppins@GWL.COM] Sent: Wednesday, August 22, 2001 2:09 PM To: DB2-L@RYCI.COM Subject: Copy data from SSID to SSID



Curious as to how others are copying data from SSID.DB.TS to SSID2.DB2.TS. The options that I can see are to DSNTIAD SELECT * FROM DB.TB to dataset and LOAD INTO DB2.TB or DSN1COPY with OBIDXLAT. Any other options without 3rd party tools? [...] 6980 85 9_Re: SPT0113_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Thu, 23 Aug 2001 10:42:31 +1000457_- This could possibly be caused by binding with parallelism (using DEGREE). DB2 will store two copies of the access path , one for parallelism and one without if it has to fallback to non parallel access path.

Are you using DEGREE ?

Regards

Shaun Lombard

-----Original Message----- From: Tanya Haverchak [mailto:Tanya.Haverchak@ANTARESSOLUTIONS.COM] Sent: Thursday, 23 August 2001 1:44 To: DB2-L@RYCI.COM Subject: Re: SPT01 [...] 7066 76 52_Re: URGENT! Repartitioning a VLDB in OS/390 DB2 V6.115_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 23 Aug 2001 10:49:23 +1000447_- Hi Aedan,

Don't have any suggestions as to how to make this easier (apart from organising to be away the weekend it's done) but if you're going to go to all this hassle to cater for growth you might want to think about making the sucker Large. We've got a couple - well, OK; 3 - of tablespaces here with DSSIZE 32G and they work well. Couple of things to watch out for with SMS but nothing not clearly documented in the DB2 manuals. [...] 7143 21 40_Scripts to backup UDB database on NT box8_Henry Li14_hli@ITRWEB.COM31_Wed, 22 Aug 2001 19:43:42 -0500287_- Hi,

I am currently running UDB 7.2 on an NT box and a job scheduler (CA Unicenter) is planning to be applied to kick off the daily backups. I am wondering if anyone has sample NT/PERL backup scripts which can be called by CA Unicenter. I tried db2batch and it didn't work. [...] 7165 98 33_Re: Larry (Oracle) strikes again!15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 23 Aug 2001 10:57:46 +1000597_- Hmmm... wonder if I could have used this to find the location of the V7 .boo manuals.



Raymond

> -----Original Message----- > From: Aurora Dell'Anno [SMTP:Aurora_E_DellAnno@CANDLE.COM] > Sent: Wednesday, August 22, 2001 7:25 PM > To: DB2-L@RYCI.COM > Subject: Re: Larry (Oracle) strikes again! > > well this is funny, because I tried to go to Google but I mis-typed it as > "www.goggle.com" and I got re-routed to.... > > http://www.spy-pal.com/ > > try it for yourselves if you have a moment, it had me doubled up laughing > it seemed so appropriate :-) > > Ciao! > > [...] 7264 57 31_Re: Copy data from SSID to SSID64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 22 Aug 2001 20:18:33 -0500623_ISO-8859-1 Tink,

Using DSNTIAUL when unloading does not require any OBID manipulation on the LOAD side of the equation.

Using REORG UNLOAD ONLY does have OBID considerations, if you are loading into a different table, as REORG produces a format called "UNLOAD". When REORG produces FORMAT(UNLOAD) data, there is a 6 byte header that is prefixed to the unloaded row data. This prefix (the internal record prefix, actually) contains the OBID of the record (among other things) that LOAD uses (LOAD DATA FORMAT(UNLOAD)) to make sure it is directing the input row to the right table. This OBID in the record [...] 7322 54 26_Re: Floating Point Numbers13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Wed, 22 Aug 2001 19:39:38 -0700453_us-ascii Hi,

To answer you question, you should always get consistent results with float, however in rare cases it isn't the answer you want. Of course this can also happen with Decimal or Binary.

If you can get the 2000 IDUG North American or European Conference Proceedings, I gave a presentation which included extensive description of the use of floating point numbers. See "So You Wanna Get The Right Answers - Numeric & Dates". [...] 7377 23 27_Re: DB2 Bookmanager Manuals14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 22 Aug 2001 21:51:24 -0500520_- If you don't have it, you might also be interested in getting a copy of the Bookmanager Index Utility - which builds the bookshelf index.

http://booksrv2.raleigh.ibm.com/homepage/indxutil.html

James Campbell

On Tue, 21 Aug 2001 09:15:06 +1000, Bell, Raymond W wrote:

>Bingo! That's the one. Oh, and Regan, my next step would have been to >borrow the CDs from the Sysprogs. Might have to do that if not all the V7 >manuals are online in .boo format. > [...] 7401 33 20_Recovery information25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Thu, 23 Aug 2001 11:40:29 +0800417_us-ascii Hi,

Would like to check with the group on where does DB2 get the recovery information from. I am interested in the VCATname.

When I do a recovery of a tablespace and index, Db2 deletes and reallocates the LDS. In order to do that it needs the information such as DBname and VCATname. Where does this get it from (I don't think it is from systablepart and sysindexpart or the sysstogroup). [...] 7435 53 24_Re: Recovery information15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 23 Aug 2001 13:48:58 +1000640_- Why don't you think it's from Sysibm.Sysstogroup? There's a column in there called VCATNAME which gives the (and I quote from the CA V6 Quick Reference) 'Integrated Catalog Facility (ICF) catalog name.' I'm pretty sure DB2 gets it from here.



Raymond

> -----Original Message----- > From: Srirengan Venkatesh Kumar > [SMTP:SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG] > Sent: Thursday, August 23, 2001 1:40 PM > To: DB2-L@RYCI.COM > Subject: Recovery information > > Hi, > > Would like to check with the group on where does DB2 get the recovery > information from. I am interested in the VCATname. > > When I do a [...] 7489 22 58_OS390 DB2 V6.1 - Rebuild Index(all) on catalog & Directory9_Ford Wong20_fordie@CONNECT.AB.CA31_Wed, 22 Aug 2001 21:57:31 -0600373_iso-8859-1 If you have user defined indexes on any of the DB2 Catalog / Tables, then REBUILD INDEX ALL will blow up real "GOOD". unless IBM has corrected this problem. The IBM Utilities Guide says that you must explicitly REBUILD each DB2 Catalog Index if you do. (very fine print).

If you don't have any user defined indexes then REBUILD INDEX ALL works fine. [...] 7512 167 31_Re: Copy data from SSID to SSID16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 09:56:35 +0530610_iso-8859-1 If you are with V7, then going for new UNLOAD utility will be a better option. After UNLOAD, traditional LOADing. It makes thing simple.

Regards Sanjeev

> -----Original Message----- > From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] > Sent: Thursday, August 23, 2001 2:39 AM > To: DB2-L@RYCI.COM > Subject: Copy data from SSID to SSID > > Curious as to how others are copying data from SSID.DB.TS to SSID2.DB2.TS. > The options that I can see are to DSNTIAD SELECT * FROM DB.TB to dataset > and > LOAD INTO DB2.TB or DSN1COPY with OBIDXLAT. Any other options without 3rd > party [...] 7680 68 69_Re: REORG TABLESPACE PART X SHRLEVEL REFERENCE and DATA AVAILABI LITY16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 10:02:35 +0530385_- Don,

If i am not wrong then for Final log apply, switch and BUILD2 phase everything is unavailable. So during BUILD2 phase, NPIs are built. V7 have some facility of running them in parallel so that it is unavailable for less time. Look at V7 performance topics for more details.

Regards Sanjeev IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 [...] 7749 21 31_Accessing DB2 on AS/400 from NT13_Viswanathan N29_Viswanathan.N@LNTINFOTECH.COM31_Thu, 23 Aug 2001 10:23:02 +0530282_us-ascii I am trying to access OS/400 from the NT machine .. I am able to connect and query my database . But system table are having code page 937 and whenever I query the system tables then it gives error saying can't convery codepage 937 to 1252 ....

reason code = 1 [...] 7771 97 24_Re: Recovery information25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Thu, 23 Aug 2001 13:05:00 +0800552_us-ascii Hi Raymond,

We have a test subsystem running and I am playing round with that. I modified the VCAT name in the sysstogroup and other catalog tables (systablepart and sysindexpart) and tried to recover to make sure it assign a new high level qualifier for the dataset. But the recovery fails saying that recovery is unable to complete because it is trying to allocate using the old vcat which is not in the three tables mentioned (I do not have access to the old vcat in the new mvs image and decided to change it to the one I have [...] 7869 57 48_Re: It's been real....change in career direction18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 23 Aug 2001 11:28:51 +0530639_- Hi Linda,

Glad to know that you would be branching out soon to climb higher in your career. I wish you the best of luck in your new endevour.

regards, Rajendra.



>From: "Billings, Linda" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: It's been real....change in career direction >Date: Tue, 21 Aug 2001 11:18:19 -0500 > >Hi, all, > I just thought I would let you that I am in the process of >changing >my career direction. I am training to become a full-time project manager >so >I am up to my eyeballs in project management type [...] 7927 19 53_Re: How to set auto commit false from command center.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 23 Aug 2001 11:28:10 +0530416_iso-8859-1 Hi , We are using DB2 Connect for connecting to db2 database(lying on the mainframe). How can we do set auto commit false from command centre.

Regards Praveen

===============================================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. 7947 78 36_Re: DSNREXX and FLOAT/DOUBLE columns17_Massimo Biancucci30_massimo.biancucci@T-SYSTEMS.IT31_Thu, 23 Aug 2001 08:01:26 +0100575_us-ascii This is a piece of REXX working correctly in my shop:

--------------------------------------------------------------------------------- /* REXX */ ARG SUBSYS

SUBSYS = STRIP(SUBSYS)

'SUBCOM DSNREXX' /* DB2 SQL HOST CMD ENV AVAILABLE? */ /* IF NOT, MAKE IT AVAILABLE */ IF RC THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

ADDRESS DSNREXX

"CONNECT "SUBSYS

SQLSTMT = " SELECT CREATOR , NAME , CARDF " !!, "FROM SYSIBM.SYSTABLES " !!, "WHERE NAME LIKE '%' " !!, "WITH UR" "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT" [...] 8026 144 24_Re: Recovery information14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Thu, 23 Aug 2001 16:05:03 +1000423_iso-8859-1 Hi,

Check your ZPARMS too.

Macro DSN6SPRM also has a CATALOG parameter which is used to:

"Specify the alias of the VSAM integrated catalog facility user catalog or the name of the VSAM integrated catalog facility master catalog in which to catalog the DB2 VSAM data sets created during installation."

I assume your VCAT in the BSDS is correct, or your susbsystem wouldn't start. [...] 8171 180 24_Re: Recovery information25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Thu, 23 Aug 2001 14:34:14 +0800384_us-ascii Hi,

Don't think we are using that in the system. There are new stogroups created for apps tablespaces. I have changed the vcat for this stogroup. The Db2 is up and running. I can query the catalog etc. But when I try to recover the apps tablespace that is when the 00D70025 problem is occuring because it is trying to define the LDS with the old high level vcat. [...] 8352 22 35_Re: Unavailable Resource - SYSLGRNX10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 23 Aug 2001 08:42:07 +0200342_us-ascii Hi Michael

Yes I meant that DSNUTILB is used to copy the 'special' tablespace only (ie 'log tablespaces' and LOB tablespaces as you said), not ALL catalog/directory Tablespace. Sorry if I wasn't clear.

Copy+ was able to copy cat/dir tablespace since 1993 ? My goddness am I so old that I remember this fact ? :-(( [...] 8375 84 69_Re: REORG TABLESPACE PART X SHRLEVEL REFERENCE and DATA AVAILABI LITY10_Ali OZTURK27_Ali.OZTURK@PAMUKBANK.COM.TR31_Thu, 23 Aug 2001 09:49:05 +0300311_iso-8859-9 Hi Don,

I think this is a large table. be careful In BUILD2 phase, db2 is using log when building NPI indexes. Therefore, if you want to cancel job or terminate the utility, job will be done recover and it will be rollback. (During the rollback, the utility status will be "TERMINATING") [...] 8460 19 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM31_Thu, 23 Aug 2001 01:44:44 -0600139_us-ascii I will be out of the office from 08/23/2001 until 08/24/2001.

I will respond to your message when I return.



8480 38 53_Re: How to set auto commit false from command center.10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 23 Aug 2001 17:04:14 +1000531_us-ascii Praveen,

U can select "Options" under "Script" (on the top left) of Command Center, select the "Execution" tab, and set the option.

Cheers, Kals



-----Original Message----- From: praveen_kj [SMTP:praveen_kj@INFY.COM] Sent: Thursday, August 23, 2001 3:58 PM To: DB2-L@RYCI.COM Subject: Re: How to set auto commit false from command center.

Hi , We are using DB2 Connect for connecting to db2 database(lying on the mainframe). How can we do set auto commit false from command centre. [...] 8519 110 26_Re: UDF story - OS/390 DB20_24_markus.schaub@SYSTOR.COM31_Thu, 23 Aug 2001 09:41:44 +0200323_us-ascii hi

we are using udfs on our os/390 db2 v6 as well. they are all written in c and very clean & compact as well. we usually use them within stored procedures and therein only in the select-list and not in the where-clause. you inspired me to do a little testing and measurement. so here are our results: [...] 8630 28 55_Michelle D Silverman/TCC/Chrysler is out of the office.0_25_ms163@DAIMLERCHRYSLER.COM31_Thu, 23 Aug 2001 04:02:20 -0400713_us-ascii I will be out of the office starting 08/23/2001 and will not return until 08/24/2001.

I will respond to your message when I return.







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

The information contained in this transmission, which may be confidential and proprietary, is only for the intended recipients. Unauthorized use is strictly prohibited. If you receive this transmission in error, please notify me immediately by telephone or electronic mail and confirm that you deleted this transmission and the reply from your electronic mail system. **************************************************************************** [...] 8659 22 15_SQL Performance11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Thu, 23 Aug 2001 15:32:22 +0700368_- Dear all,

A simple question. Will there be any performance difference during SQL processing on 2 tables if in the 1st SQL process I activate the FK Relationship and the 2nd process I drop the FK relations. Assume on both processes, both tables are Indexed.

T.I.A...

Endy Lambey endy@lambey.net

"Never make decision on empty stomach" [...] 8682 20 32_Generation of unique sequence No10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 23 Aug 2001 14:20:48 +0530412_iso-8859-1 Hi ,

In our project we have an requirement of generating an unique sequence no while inserting a row. Do the db2 have such feature.

Regards Praveen

===============================================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. 8703 19 48_How to store special character in the data base.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 23 Aug 2001 14:29:48 +0530390_iso-8859-1 Hi All,

How can we store special character (i.e. carriage return,new line) into the DB2 database.

Regards, Praveen Kumar Jain

===============================================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 50 52_Re: How to store special character in the data base.14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Thu, 23 Aug 2001 19:14:10 +1000643_iso-8859-1 Hi,

You can enter the HEX value e.g X'13'

Regards,

Greg Palgrave Database Administrator Unisys West

The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). This information may be subject to legal professional or other privilege or may otherwise be protected by work product immunity or other legal rules. It must not be disclosed to any person without our consent. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are not authorised to and must not [...] 8774 28 36_AW: Generation of unique sequence No12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 23 Aug 2001 11:15:11 +0200427_iso-8859-1 Praveen,

did you try a DB2 for OS/390 Version 6 and higher feature called identity column ? This feature allows the automatic generation of sequential numeric keys.

Before using the identity column feature please make sure wether this funcionality has been implemented in your shop (AFAIK a DB2 refresh is needed)...

HTH.

With kind regards - mit freundlichen Grüssen, Georg H. Peter [...] 8803 69 36_Re: Generation of unique sequence No16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 14:53:04 +0530365_- Praveen,

Which version and platform of DB2 you are working with? It is available in V6 and above on OS390. I am also sure about V7 on other platform. This is IDENTITY column. You can either search the archive or go thru' manuals for more details on this.

HTH Regards Sanjeev IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 [...] 8873 65 52_Re: How to store special character in the data base.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 15:03:19 +0530339_- If i am not wrong, i have been storing it in character only. The difference only comes when you are extracting and interprating the data. You need to know that special characters are there.

Others might have better answer than this.

Regards Sanjeev IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 [...] 8939 71 19_Re: SQL Performance16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 14:54:46 +0530620_- Endy,

I think the difference will be very very less if everything is right with RI. It is on case to case basis and the standard answer is IT DEPENDS.

Regards Sanjeev IBM Certified Solutions Expert DB2 V7 Database Administration OS/390



> -----Original Message----- > From: Endy Lambey [SMTP:endyl@CENTRIN.NET.ID] > Sent: Thursday, August 23, 2001 2:02 PM > To: DB2-L@RYCI.COM > Subject: SQL Performance > > Dear all, > > A simple question. > Will there be any performance difference during SQL processing on 2 tables > if in the 1st SQL process I activate the FK Relationship and the [...] 9011 29 49_How to retrieve result set from stored procedure.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 23 Aug 2001 14:59:37 +0530429_iso-8859-1 Hi All,

In our project we are calling stored procedure written in cobol programme from the java Client.

Now the problem is how can we retrieve result set returned from the java stored procedure.

While the calling programme is cobol(i.e. cobol client) what you can do is associate a locator variable to the cursor cl which you will keep open in the stored procedure for retrieving result set. [...] 9041 43 33_Re: Interactive SQL's and Explain18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 23 Aug 2001 15:52:27 +0530622_- Hi Balaji,

You can certainly EXPLAIN any SQL in SPUFI. Make sure you have a table called PLAN_TABLE which stores the result of EXPLAIN. If not then go ahead and create it. If you need the SQL for creating the table let me know.

thanks, Rajendra.



>From: "K.Balaji" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Interactive SQL's and Explain >Date: Wed, 22 Aug 2001 12:15:28 -0500 > >Hi all, >Can I get explain results for the SQLs that I issue from SPUFI or from ISQL >option from Platinum? It looks I have to have DBRM or [...] 9085 95 33_Re: Interactive SQL's and Explain16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 16:09:34 +0530390_- Balaji,

Let's assume you have an SQL :

SELECT C1, C2 FORM T1, You are with your primary authorization id USER1 in the TSO and you can use USERG as your secondary authorization id. Examine if PLAN_TABLE exists or not, if it exists then what is the CREATOR of the table. If it is USER1 then you can simply use the statement in SPUFI/QMF?Anywhere you can run the SQL from [...] 9181 20 42_Precaution regarding special character set10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 23 Aug 2001 16:06:59 +0530467_iso-8859-1 Hi All, The character type has been set for the DB2 database is EBCDIC. Are there some special precautions required to be taken while retreiving or putting the data in the database.

Regards, Praveen Kumar Jain

===============================================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. 9202 60 24_Re: Recovery information16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Thu, 23 Aug 2001 07:22:49 -0400610_iso-8859-1 Hi,

I'm not sure how many of these things you have done, but these are all the steps that must be performed to change VCATs for objects successfully. I am listing them in the order I have used successfully in the past.

1. STOP DATABASE xxxxxxxx SPACENAM(xxxxxxxx) for all the tablespaces/index spaces in the stogroup. 2. ALTER TABLESPACE xxxxxxxx USING VCAT xxxx for all the tablespaces in the stogroup. (This is necessary to remove the dependency on the stogroup.) 3. ALTER INDEX xxxxxxxx USING VCAT xxxx for all the indexes you want to change. 4. DROP STOGROUP xxxxxxxx for the [...] 9263 63 68_Re: REORG TABLESPACE PART X SHRLEVEL REFERENCE and DATA AVAILABILITY18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 23 Aug 2001 17:00:34 +0530604_- Hi Donald,

Pasted below is an excerpt from 'DB2 DBA' article in the recent DB2 MAG issue.

********************** An NPI, if damaged in some way and placed by DB2 in recover pending status, must be recovered in its entirety. In contrast, a partitioning index can be recovered at a partition level. In some cases, recovery of a single partition of the index will resolve a problem. (Note that the enhanced RECOVER INDEX capability introduced with DB2 v.6 - index recovery via image copy restoration and log applies - can significantly reduce the time required to recover a large NPI.) [...] 9327 35 47_CSP V3.3 and DB2 V5 / "optimize for" gives -19916_Jean-Paul Coenen19_jpc_db2@HOTMAIL.COM31_Thu, 23 Aug 2001 13:38:30 +0200319_- Hi folks,

We are using CSP V3.3 and DB2 V5. I'm hardly familiar with CSP and I'm stuck with an sqlerror -199. (found anything in the archive)

In the "Object Selection Conditions" of a query process, I added the optimize clause, like this: GROUP BY A, B, C, D OPTIMIZE FOR 1 ROW ORDER BY 1, 2, 3, 4 [...] 9363 90 51_Re: CSP V3.3 and DB2 V5 / "optimize for" gives -19916_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 23 Aug 2001 17:34:28 +0530369_- If i recall correctly, in CSP3.3 you get a panel where you need to specify the parameters and it generates the query. I still remember (saw 2 years back) that some of the syntax can't be used in CSP3.3. I think OPTIMIZE FOR 1 ROW was one of them but not sure about that now. I had this in my previous installation and am not working on it now for last 1+ years. [...] 9454 27 27_Correlated table expression10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Thu, 23 Aug 2001 07:58:50 -0400583_us-ascii I just saw a post on correlated table expression. I have not seen this functionality before and I was wondering if someone could explain to me how it works.

Typically I think of correlating subqueries. And when you do that the subquery being correlated is in the where clause. When you correlate a table expression, it appears to happen in the FROM clause. Usually when I think of the FROM clause I think of tables being joined. Whether that be inner or outer join. I was wondering with the correlated table expression if there is some type of inner/outter join [...] 9482 91 25_Re: BETWEEN vs. >= and <=13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 23 Aug 2001 06:59:45 -0500440_iso-8859-1 Isaac,

Just as a side note (and ignoring the internal query rewrite as already discussed by Richard), your note implies that you expect the following filter factors:

BETWEEN: 1/10 >= AND <=: 1/3

Whereas each of the individual predicates <= & >= will result in a default of 1/3. These must then be ANDed together to produce the filter factor for the compound predicate, which becomes 1/3 of 1/3, or 1/9. [...] 9574 60 19_Re: SQL Performance13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 23 Aug 2001 07:03:21 -0500452_iso-8859-1 Endy,

The most efficient process is the one that is never executed. Therefore, expect no RI to outperform RI. The cost of the RI is something you will need to test yourself, and will depend on available access path and number of rows processed.

Indexing will allow the best available access, and if you really need RI, then the most efficient place for this is database defined, rather than coded within your application. [...] 9635 136 35_Re: Calling all SQL Guru's.........13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 23 Aug 2001 07:10:06 -0500387_iso-8859-1 Shery,

As for the GROUP BY, you have a DISTINCT, which has the same limitations. So obviously the VARCHAR(31000) column is not within the DISTINCT.

It is difficult to visualize your exact problem without a sample of data, otherwise you are likely to receive responses as to how to code a CASE statement. I'm guessing your problem is more complex than this. [...] 9772 13 42_Re: DB2 get database configuration command10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Thu, 23 Aug 2001 07:09:52 -0500281_- If you are at a client and you want to do a db2 get db cfg for xxxx and the database is on another server, you must first connect to the database from your client and then execute the db2 get db cfg for xxx command. These commands can be executed from a db2 command window. [...] 9786 74 21_Re: OS Comand Scripts14_Craig Ducharme26_craig_ducharme@HOTMAIL.COM31_Thu, 23 Aug 2001 05:34:31 -0700619_- Rolf,

Thanks for the assistance!

Craig



>From: Rolf Kramer >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: OS Comand Scripts >Date: Thu, 16 Aug 2001 11:45:24 +1000 > >When I started using DB2 on NT and 2000 I ran into similar problems of >trying to work out and remember where any commands go. > >I used to keep the following notes handy to remind me what needed to be >done. As an aside, I found db2cc a pain when using different platforms, so >I >nearly exclusively use Command line now. > >for Batch SQL >1. on [...] 9861 29 65_=?ISO-8859-7?Q?The=20big=20black=20box=20=AF-=20Optimizer=3F=20?=12_Susan Carter18_SCARTER@PANTEX.COM31_Thu, 23 Aug 2001 08:18:58 -0500505_US-ASCII My co-worker is doing index tuning and he came across an interesting cause and effect.

Two indexes possible for a query. One index was an index only read (the larger one) the other smaller index had to read the table. Changing a free space on the larger index caused the index not to be used even though it was an index only read. Only thing we can figure out is the optimizer did not pick that index because of the number of pages in the index. More get pages were used as a result. [...] 9891 20 40_JDBCn connectivity to DB2(v6.1) on OS39029_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV31_Thu, 23 Aug 2001 09:28:58 -0400409_us-ascii Earl This error can mean a few different things. Mostly it is a mismatch between db2java.zip files. Make sure that the date on the user's NT db2java.zip file matches the date on the server's db2java.zip file. This file is not downward compatible nor upward compatible. If the dates are the same, then check that the db2jd service is started. Also check that the right port number is being used. [...] 9912 93 29_Stored Procedure Query in DB217_Mohanraj ,S (CTC)18_s.mohanraj@AIG.COM31_Thu, 23 Aug 2001 09:29:26 -0400342_- HI

I want to confirm the Locking logic behind this..

Here i list out 2 queries used in Stored Procedure ..

First one is Update & another one is Select..

i.e. First user update the table field and after that read the same ...

So in between update & select some body may try change the updated value.. [...] 10006 53 39_Re: DB2 V6 - IRLM Link List Requirement12_Kirk Hampton16_khampto1@TXU.COM31_Thu, 23 Aug 2001 08:42:38 -0500330_us-ascii Hello, yes, we are running DB2 V6.1 on four different LPARs, all at OS/390 v2.10, two LPARs with, and two without module DXRRL183 in the linklist. We have seen no difference in the way IRLM or DB2 performs, but we are not using ARM here yet, no datasharing, and I suppose we have never had to run a CTRACE for IRLM. [...] 10060 50 35_Re: The big black box ?- Optimizer?16_Schuchard, Peter34_Peter.Schuchard@STARWOODHOTELS.COM31_Thu, 23 Aug 2001 09:41:27 -0400405_iso-8859-7 If the additional freespace caused the index to add an additional level, db2 factors in the number of levels and can make a not too wise choice in my opinion. I have this problem today and update the catalog setting the index level down 1 for the particular index in question. You might want to try the same to see if it influences the optimizer. I know in my case it fixed a big problem. [...] 10111 118 40_Re: AW: Generation of unique sequence No0_24_db46@DAIMLERCHRYSLER.COM31_Thu, 23 Aug 2001 09:55:34 -0400412_iso-8859-1 You can also use a 'before' trigger with the create unique function as follows:

CREATE TABLE "Test" ( "Test_ID" CHARACTER (13) FOR BIT DATA, "Test_Name" CHARACTER (50) NOT NULL, CONSTRAINT "TESTIDPK" PRIMARY KEY ("Test_ID") );

CREATE TRIGGER TESTID_UNIQUE NO CASCADE BEFORE INSERT ON Test REFERENCING NEW AS NEW_UPD FOR EACH ROW MODE DB2SQL SET NEW_UPD.Test_ID = GENERATE_UNIQUE(); [...] 10230 20 21_DB2 VERSION 6 SUPPORT11_Conn, Linda22_lconn@DOIT.STATE.IN.US31_Thu, 23 Aug 2001 09:04:57 -0500417_iso-8859-1 Hi all,

Can anyone tell me how long DB2 V 6 will be supported?

Thanks, Linda Conn Database Analyst Division of Information Technology State of Indiana

================================================ 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. 10251 143 26_Re: UDF story - OS/390 DB212_Kirk Hampton16_khampto1@TXU.COM31_Thu, 23 Aug 2001 09:05:40 -0500346_us-ascii We are on DB2 v6.1 under OS/390 v2.10, we are not running UDF's yet, but I had some thoughts about performance. Are your programs set up to be reentrant and stay resident, or does the module have to be reloaded from the loadlib every time ? And if so, is it located far down a STEPLIB concatenation so that the lib search is long ? [...] 10395 205 24_Re: Recovery information15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 23 Aug 2001 09:21:08 -0500434_iso-8859-1 DB2 gets the VCAT name from the pageset OBD for the table space or index. You can use the DB2 Diagnose utility do a DIAGNOSE DISPLAY OBD to see the contents of the various OBDs for a table space and its related objects. For a non-partitioned table space, look at offset 0x78 into the pageset OBD. For a partitioned table space, it's a little more complicated, since there is a section that repeats for each partition. [...] 10601 22 12_Db or not Db15_Gary Fahrlander28_gary.fahrlander@ZURICHNA.COM31_Thu, 23 Aug 2001 09:29:54 -0500314_us-ascii That is the question. Whether 'tis nobler to have a batch edit program load a small static table into program "working-storage" (66 rows * 99 bytes) and use an internal search to validate up to 24 million records Or have the program perform a singleton select on the table for each record processed. [...] 10624 49 16_Re: Db or not Db12_McKown, John22_JMckown@HEALTHAXIS.COM31_Thu, 23 Aug 2001 09:51:38 -0500268_- Well, personally, since the table is so small, I'd load it into WORKING-STORAGE and use a SEARCH verb. I'd also try to make the table sorted so that I could use the SEARCH ALL to do a binary search. This assumes that the table is not likely to grow very large. [...] 10674 23 11_DB Artisan.10_Mark Frost23_mark.frost@CITICORP.COM31_Thu, 23 Aug 2001 15:54:28 +0100487_US-ASCII Worthy List members..

Does anyone have any comments regarding the use of DBArtisan 5.4 with a DB2 OS390 V6 server ?

I would be interested in any experiences, good or bad.

many thanks Mark



Citibank UK.

================================================ 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. 10698 215 35_Re: Calling all SQL Guru's.........10_Shery Hepp17_schepp@SRPNET.COM31_Thu, 23 Aug 2001 07:55:15 -0700599_iso-8859-1 Terry- here's a sample of the data when I request the detail info-

VENDOR_ID PO_ID LINE_NBR SCHED_NBR COMMENT_TYPE ---------- ---------- ----------- --------- ------------ 0000010200 0000051275 1 1 - 0000010200 0000066019 1 1 LIN 0000010200 0000066019 1 1 LIN 0000010200 0000066019 2 1 LIN 0000010200 0000066019 2 1 LIN 0000010200 0000066019 3 1 LIN 0000010200 0000066019 3 1 LIN 0000013904 0000062609 1 1 - 0000013904 0000062609 1 2 - 0000013904 0000062609 1 3 - 0000013904 0000062609 1 4 - 0000013904 0000062609 1 5 - 0000013904 0000062609 1 6 - 0000013904 0000062609 1 7 - [...] 10914 35 16_Re: Db or not Db1_.21_db2_dba@BELLSOUTH.NET31_Thu, 23 Aug 2001 11:22:13 -0400337_us-ascii One thing you might want to consider is sorting the input data into an order that you would only need to select a row from a table (assuming using db2) when the value changes. The biggest question that comes to me is now you have a small number of rows. What happens when the number doubles, triples, or increases by ????. [...] 10950 154 19_Re: SQL Performance14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 23 Aug 2001 16:18:57 +0100370_iso-8859-1 Let me paraphrase to make sure I understand your question.

"If I have a table with DB2 managed children will SQL processes work faster WITHOUT this RI than they do WITH it"

If that IS your question, then the answer is YES - it WILL take DB2 'longer' to do the same work (unless it is all SELECT, in which case there will be no difference). [...] 11105 140 31_Re: Copy data from SSID to SSID12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 23 Aug 2001 18:22:43 +0200469_windows-1255 Hi, REORG UNLOAD EXTERNAL & LOAD is the 3rd option (without 3rd party tools or IBM UNLOAD).





Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Toppins, Smike" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, August 22, 2001 11:08 PM Subject: Copy data from SSID to SSID [...] 11246 157 52_DevX.com: Product Review: DB2 Universal Database 7.213_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:40:21 -0600377_- Dear DB2 Interested Parties,

Below is a "product review" of DB2 UDB for UNIX, Windows, OS/2 Version 7.2 by DevX.com (from http://www.devx.com/free/products/pgReview.asp?ReviewID=12642 ). More interesting information and resources on DB2-related topics are found in the "DB2 Zone" hosted by DevX at http://www.devx.com/dbzone/ .

Regards, Mike + + + + + + + [...] 11404 54 52_FW: IBM developerWorks Data Management Theme Website13_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:40:59 -0600708_- Dear DB2 Interested Parties,

IBM developerWorks is featuring a Data Management Theme on its Website right now. Many of you will find this developer-oriented Website a fantastic resource, check it out at http://www-106.ibm.com/developerworks/theme/?l=wud,t=nl,nl=DMtheme

Some highlights (there is more, so see the page itself for all the goodies):

Articles: o The impact of P2P on data management issues for developers (Web architecture) o Mapping objects to relational databases: What you need to know and why (Components) o The object-data divide and EJB (Components) Part 1, Part 2 o Choosing a database management system o Processing database information using Unicode: A case [...] 11459 48 100_FW: DB2 for OS/390 V5 Withdrawal from Marketing (12/31/2001) and from Service (12/31/2002) Announced13_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:41:26 -0600459_- -----Original Message----- From: Michael Swift [mailto:dbswift@us.ibm.com] Sent: Friday, August 10, 2001 2:22 PM To: DB2 Interested Parties Subject: DB2 for OS/390 V5 Withdrawal from Marketing (12/31/2001) and from Service (12/31/2002) Announced



Dear DB2 Interested Parties,

In case you did not see the recent announcements, DB2 Database Server for OS/390 Version 5:

o will be withdrawn from marketing on December 31, 2001 [...] 11508 64 49_FW: DB2 Products on Windows 2000 Server Platforms13_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:41:55 -0600681_- -----Original Message----- From: Michael Swift [mailto:dbswift@us.ibm.com] Sent: Tuesday, August 14, 2001 4:37 PM To: DB2 Interested Parties Subject: DB2 Products on Windows 2000 Server Platforms



Dear DB2 Interested Parties,

IBM is pleased to announce that DB2 Version 7.2 products have earned 12 Windows 2000 Server logo certifications.

Certification means the IBM DB2 Universal Database has passed a controlled, independently verified testing process to assure maximum use of the Windows 2000 operating system. Customers will benefit from a more robust, self-repairing installation procedure, plus enhanced network security and accessibility. [...] 11573 13 26_Re: UDF story - OS/390 DB212_Roger Miller19_millerrl@US.IBM.COM31_Thu, 23 Aug 2001 10:24:05 -0500408_- There are a number of additional UDF performance considerations noted in the red book V6 Technical Update, section 2.2, SG24-6108.

Roger Miller, DB2 for 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. 11587 93 65_FW: New Book : DB2 UDB for z/OS and OS/390 V7 Certification Guide13_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:43:02 -0600464_- Dear DB2 Interested Parties,

"DB2 Universal Database V7.1 for OS/390 Database Administration Certification Guide" by Richard Yevich and Susan Lawson, internationally recognized database consultants and principals of YL&A, Inc., is now available. Below is a press release about this valuable new publication. Check it out!

For more information about IBM DB2 certification programs, go to http://www-4.ibm.com/software/data/db2/skills/cert.html [...] 11681 57 53_Repost: DB2 Products on Windows 2000 Server Platforms13_Morrill, John12_JohnM@VP.NET31_Thu, 23 Aug 2001 09:44:14 -0600470_- Dear DB2 Interested Parties,

IBM is pleased to announce that DB2 Version 7.2 products have earned 12 Windows 2000 Server logo certifications.

Certification means the IBM DB2 Universal Database has passed a controlled, independently verified testing process to assure maximum use of the Windows 2000 operating system. Customers will benefit from a more robust, self-repairing installation procedure, plus enhanced network security and accessibility. [...] 11739 78 16_Re: Db or not Db15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Thu, 23 Aug 2001 11:40:44 -0400625_iso-8859-1 Gary,

Also if your input file is in sequence you only have to search your static file when the key changes not for each record this will even reduce your SEARCH requirements since you already have your resultant lookup.

HTH Bob Lawrence DBA Boscov's Dept Stores LLc





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > McKown, John > Sent: Thursday, August 23, 2001 10:52 AM > To: DB2-L@RYCI.COM > Subject: Re: Db or not Db > > > Well, personally, since the table is so small, I'd load it into > WORKING-STORAGE and use [...] 11818 167 26_Re: UDF story - OS/390 DB212_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 23 Aug 2001 18:48:00 +0200491_iso-8859-1 Hi,

Yes - it is reentrant and defined "stay resident". BTW - we checked it both as main & subroutine and got no measurable difference.

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Kirk Hampton" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 23, 2001 4:05 PM Subject: Re: UDF story - OS/390 DB2 [...] 11986 63 19_Re: SQL Performance11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Thu, 23 Aug 2001 09:55:50 -0600334_iso-8859-1 How about a follow up question on this topic:

Do FKs have an impact on choices that the optimizer makes in determining the access plan? (i.e. is it just the existence of a foreign key index that the optimizer looks at, OR DOES the optimizer give a more favorable "weighting" to FK indexes when doing FK joins?) [...] 12050 40 26_Re: UDF story - OS/390 DB212_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 23 Aug 2001 19:06:04 +0200278_windows-1255 Hi, Thanks Roger, but we are already over that part. There is no SQL in the UDF, it is resident and re-entrant and has a WLM ... and even if it does nothing - it still takes too much time just calling it. I would like to point at the LE but can't prove it ... [...] 12091 112 25_Re: BETWEEN vs. >= and <=12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 23 Aug 2001 19:14:44 +0200441_iso-8859-1 HI, Agreed about the math. I did not remember that it was announced as part of V6.

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Terry Purcell" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 23, 2001 1:59 PM Subject: Re: BETWEEN vs. >= and <= [...] 12204 98 19_Re: SQL Performance14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 23 Aug 2001 17:21:34 +0100297_iso-8859-1 This is turning out to be a good day - another question I can answer!!

NO - the optimiser does NOT (at present) give a hoot about your RI constructs. It WILL use indexes to perform the necessary access (provided that you have the right ones defined of course) but that's it [...] 12303 20 30_Setting isolation level in sqr12_Chris Hoover28_ChrisHoover@SAFETY-KLEEN.COM31_Thu, 23 Aug 2001 12:11:46 -0400314_us-ascii Hi - We have dynamic sqr code that needs to run at isolation level UR. (AIX, db2 5.2, sqr , 4.3.2). Issuing the set isolation level command as a system call within sqr does not reset the isolation level.

Does anybody know how to force the isolation level to UR at the sqr or statement level ? [...] 12324 56 9_Re: SPT0110_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM31_Thu, 23 Aug 2001 11:19:02 -0500498_- We are using DEGREE(ANY) for just about everything. Is this something that changed with V6?

-----Original Message----- From: Shaun.LOMBARD@DEWRSB.GOV.AU [mailto:Shaun.LOMBARD@DEWRSB.GOV.AU] Sent: Wednesday, August 22, 2001 7:43 PM To: DB2-L@RYCI.COM Subject: Re: SPT01

This could possibly be caused by binding with parallelism (using DEGREE). DB2 will store two copies of the access path , one for parallelism and one without if it has to fallback to non parallel access path. [...] 12381 33 34_Re: Setting isolation level in sqr13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 23 Aug 2001 11:34:43 -0500345_US-ASCII Hi Chris, you can code "with ur" at the statement level. HTH Kurt

>>> ChrisHoover@SAFETY-KLEEN.COM 08/23/01 11:11AM >>> Hi - We have dynamic sqr code that needs to run at isolation level UR. (AIX, db2 5.2, sqr , 4.3.2). Issuing the set isolation level command as a system call within sqr does not reset the isolation level. [...] 12415 19 34_Re: Setting isolation level in sqr12_Jim Harrison17_jharrison@GMX.NET31_Thu, 23 Aug 2001 12:46:58 -0400488_us-ascii Don't know what sqr is, but you can add a WITH UR to the end of your SQL statement.

At 12:11 PM 08/23/2001 -0400, Chris Hoover said:

>Does anybody know how to force the isolation level to UR at the sqr or >statement level ?

================================================ 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. 12435 45 34_Re: Setting isolation level in sqr12_McKown, John22_JMckown@HEALTHAXIS.COM31_Thu, 23 Aug 2001 11:48:38 -0500352_- I don't have any books on AIX DB2 version 5.2, but the OS/390 version 6 books says that I can add the 'WITH UR' on the SELECT statement to force the isolation level to UR.

---------------------------------------------------------------------- John McKown HealthAxis

All opinions are my own and are not the opinions of my employer. [...] 12481 21 13_Clarification15_Gary Fahrlander28_gary.fahrlander@ZURICHNA.COM31_Thu, 23 Aug 2001 11:53:31 -0500622_us-ascii I feel that little further clarification wouldn't hurt (much). The table is very static, State/Province/Territory abbreviation/ISO State Number cross reference. The input file is sorted by account information so the State is in random order as the validation/cross reference occurs. Validation occurs on either the ISO State Number to get the 2 char abbreviation or on 2 char abbreviation to get the ISO State Number. I'm wondering if it's more efficient performance wise to use a cursor to load a working storage table and let the program search it for each input record processed or to have the program do [...] 12503 55 64_Store procedures - Using VarChar in parmlist - Calling using ADO14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Thu, 23 Aug 2001 11:55:49 -0500309_iso-8859-1 We are beginning to call DB2 Stored Procedures from our Web pages using ADO. We have no problem with data types other than VARCHAR. When attempting to us VARCHAR in the stored procedure parm list we get -450/39501 (Parameter number # overlayed storage beyond its declared length) on the call. [...] 12559 58 9_Re: SPT0112_Susan Lawson22_lawson_susan@YAHOO.COM31_Thu, 23 Aug 2001 10:04:02 -0700468_us-ascii Greg,

The storing of two access paths is not new to V6. DB2 will establish two access paths we you bind with DEGREE(ANY). So in the event parallelism can be used - it was have a particular access path and if parallelism cannot be used at run time (host variables, no bufferpool space, etc) then DB2 falls back to the sequential plan. You can actually see this if you bind with DEGREE(ANY) do and EXPLAIN and then bind DEGREE(1) and do an EXPLAIN. [...] 12618 48 34_FW: Setting isolation level in sqr12_McKown, John22_JMckown@HEALTHAXIS.COM31_Thu, 23 Aug 2001 12:05:09 -0500398_- Everytime I post to this list, I get the following email sent to me. Does this happen to everybody, or am I special?

---------------------------------------------------------------------- John McKown HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to upset my management. [...] 12667 43 17_Re: Clarification12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 23 Aug 2001 20:05:15 +0200741_iso-8859-1 Hi, In this case - I would load it to memory.

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Gary Fahrlander" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 23, 2001 6:53 PM Subject: Clarification



> I feel that little further clarification wouldn't hurt (much). The table > is very static, State/Province/Territory abbreviation/ISO State Number > cross reference. The input file is sorted by account information so the > State is in random order as the validation/cross reference occurs. > Validation occurs on either the ISO [...] 12711 19 48_How do I call a stored proc during DB2 startup ?14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 23 Aug 2001 13:17:59 -0400388_iso-8859-1 Hi,

Is there a way I can specify a call to a stored proc in the startup script of a DB2 instance ?

Thanks,

Abhijit

================================================ 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. 12731 63 34_Re: Setting isolation level in sqr12_Troy Coleman19_Colematr@MEIJER.COM31_Thu, 23 Aug 2001 13:22:59 -0400319_US-ASCII I have been working with PeopleSoft developers and they have asked me about running reports without locking. I've recommended the WITH UR, but I have also explained to them what implications this has on there report. They made these changes on OS/390 without any problem. The syntax for the "WITH UR" is: [...] 12795 76 31_Re: Copy data from SSID to SSID14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 23 Aug 2001 18:38:12 +0100355_iso-8859-1 Just a small pedantic (but vitally important) point

As from V7 of DB2, the ONLY way to copy data between subsystems without buying SOMETHING from SOMEBODY will be DSN1COPY with OBID translation.

You will still have DSBTIAUL but, unless you cough up dollars (or pounds/euros/yen etc) you will NOT have a LOAD utility......... [...] 12872 45 25_Re: DB2 VERSION 6 SUPPORT10_Hood, John13_JHood@FRK.COM31_Thu, 23 Aug 2001 10:34:47 -0700420_iso-8859-1 Linda:

As of May 2001 in Chris Munson Lightyr presention, IBM had not published an end of support date. Someone can correct me on this if I am wrong.

John Hood Franklin Templeton Technologies Information Management, DB2/IMS DBA 925-875-2734 or x62734 (work), 650-848-0004 (pager), 510-882-0421 (cell) jhood@frk.com (email), SM 5130/4 (mail stop), 6503710040.8480004@pagenet.net (text page) [...] 12918 69 34_Re: Setting isolation level in sqr16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Thu, 23 Aug 2001 11:35:40 -0700642_iso-8859-1 You can also bind the package / plan with ISOLATION(UR) if you don't want to modify each SELECT statement.

Duane

-----Original Message----- From: Troy Coleman [mailto:Colematr@MEIJER.COM] Sent: Thursday, August 23, 2001 10:23 AM To: DB2-L@RYCI.COM Subject: Re: Setting isolation level in sqr



I have been working with PeopleSoft developers and they have asked me about running reports without locking. I've recommended the WITH UR, but I have also explained to them what implications this has on there report. They made these changes on OS/390 without any problem. The syntax for the "WITH UR" is: [...] 12988 149 25_Re: BETWEEN vs. >= and <=13_Olson, Carlos14_COlson@QRS.COM31_Thu, 23 Aug 2001 11:39:03 -0700614_iso-8859-1 I don't want to beat a dead horse here but... if an internal query rewrite is occurring, then why were different access paths chosen for the 2 SQL statements shown below? In the first statement, the optimizer chose the H2IXAPKG index while in the second statement H2IX1PKG was chosen. I would think the optimizer would choose a consistent (not necessarily most efficient) access path given that a query rewrite makes the 2 statements equal underneath the covers. What am I missing here? By the way, if in my EXPLAIN, I replace the START_TIMESTAMP literals with "?" (host variable indicators), then [...] 13138 53 34_Re: Setting isolation level in sqr14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 23 Aug 2001 14:44:46 -0400414_us-ascii I believe isolation at statement level is not supported by db2 for aix/nt until version 7. So binding your plan with UR isolation may be a good solution.



Manas.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Kurt Sahlberg Sent: Thursday, August 23, 2001 12:35 PM To: DB2-L@RYCI.COM Subject: Re: Setting isolation level in sqr [...] 13192 81 16_CAF & Versioning13_Brunner Don J21_Don.J.Brunner@IRS.GOV31_Thu, 23 Aug 2001 14:55:22 -0400565_iso-8859-1 Has anyone used CAF in conjunction with versioning? A developer here is attempting to do this but keeps receiving back a -805. Everything appears to be correct:

1) the Package is in the correct Collection ID that is associated with the Plan being executed 2) a check of the catalog shows the Package with the version that was generated by the precompiler 3) the correct version appears in the load module 4) the timestamps in the DBRM library, DB2 catalog, and the load module match (this is not a CICS environment so no newcopy is required) [...] 13274 27 25_Re: DB2 VERSION 6 SUPPORT13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 23 Aug 2001 15:18:18 -0400718_iso-8859-1 Hi Linda, There is no published date for end of marketing or service support for DB2 v6 for OS/390. For future reference you can get that info at http://www-4.ibm.com/software/data/db2/os390/availsum.html



For windows Etc., it's the end of this year. see

http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/serviced ates.d2w/report

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.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. 13302 31 30_Query Large denormalized Table12_Alan Gredell28_agredell@KEMPERINSURANCE.COM31_Thu, 23 Aug 2001 14:31:14 -0500309_- I have a problem that I hope the list can help solve! My Search table is a denormalized list of companies in the US provided by a vendor. We massage it and add one to many rows for each input name, providing an encoded name by which we conduct the searches. The search table today has 44 million rows. [...] 13334 81 9_Re: SPT0110_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM31_Thu, 23 Aug 2001 14:38:22 -0500578_- Since DEGREE(ANY) is our shop default, it appears that does not explain our sudden growth in SPT01. We've had this as our shop default in V5 and V6.

Susan, wouldn't it be better to let the DB2 optimizer decide when to use parallelism? With the size of our shop, I guess we've decided to let DB2 make the decision, rather than try to analyze each package individually. As improvements are made to the optimizer, we should then be taking advantage as rebinds occur. We have had a few situations where parallelism caused problems and used DEGREE(1) to get around the [...] 13416 51 53_Re: How to retrieve result set from stored procedure.13_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Fri, 24 Aug 2001 05:47:02 +1000536_iso-8859-1 Praveen,

See getMoreResults(), getResultSet() etc at http://java.sun.com/products/jdk/1.2/docs/api/java/sql/Statement.html

Greg Farquhar



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of praveen_kj Sent: Thursday, August 23, 2001 7:30 PM To: DB2-L@RYCI.COM Subject: How to retrieve result set from stored procedure.



Hi All,

In our project we are calling stored procedure written in cobol programme from the java Client. [...] 13468 22 48_db2 v6 trigger calling a cobol stored procedure.16_Proctor, William25_william.proctor@TGSLC.ORG31_Thu, 23 Aug 2001 15:00:38 -0500403_iso-8859-1 Is there anyone out there using a db2 trigger to call a cobol stored procedure? If so could I get an example of the trigger and the stored procedure? It would be greatly appreciated. We have been unable to get this to work. I can create and test the stored procedure and I can create a trigger without the call in it but when I put the call to the stored procedure it gets a bind error. [...] 13491 81 68_Re: Store procedures - Using VarChar in parmlist - Calling using ADO15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 23 Aug 2001 16:52:08 -0400720_iso-8859-1 01 INVOICE-NBR PIC X(15).

This should be

01 INVOICE-NBR. 49 INVOICE-NBR-LEN PIC S9(4) USAGE COMP. 49 INVOICE-NBR-TEXT PIC X(15).



Thanks..sibi



-----Original Message----- From: Zobjeck, A. J. [mailto:Al_Zobjeck@TTX.COM] Sent: Thursday, August 23, 2001 11:56 AM To: DB2-L@RYCI.COM Subject: Store procedures - Using VarChar in parmlist - Calling using ADO



We are beginning to call DB2 Stored Procedures from our Web pages using ADO. We have no problem with data types other than VARCHAR. When attempting to us VARCHAR in the stored procedure parm list we get -450/39501 (Parameter number # overlayed storage beyond its declared length) on the call. [...] 13573 49 52_Re: db2 v6 trigger calling a cobol stored procedure.12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 23 Aug 2001 16:03:32 -0500177_us-ascii Hello,

Here is the SP call snippet of a working AFTER UPDATE trigger we have. There really isn't much to it. What BIND error are you getting?

BEGIN ATOMIC 13623 28 20_Stored pocedure call15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Thu, 23 Aug 2001 14:07:09 -0700266_us-ascii Hi Gurus I wrote SQL stored procedure and created , its successfull my doubt is how can I execute from clp and how do I see the values returned by the stored procedure from clp. We use UDB v7 , unix on Solaris and AIX pls. help me as it is very urgent [...] 13652 83 33_Re: Interactive SQL's and Explain8_K.Balaji19_K.Balaji@TARGET.COM31_Thu, 23 Aug 2001 16:04:03 -0500404_iso-8859-1 Thanks to all experts. Your suggestions helped me.

And also I would like to take this oppurtunity to thank all the experts who helped me with their suggestions for my various questions.

-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: Thursday, August 23, 2001 3:40 AM To: DB2-L@RYCI.COM Subject: Re: Interactive SQL's and Explain [...] 13736 44 18_Partition by Date?0_20_Jones.H@GRAINGER.COM31_Thu, 23 Aug 2001 16:20:00 -0500368_- DB2 OS/390 Version 5

I need some tricks to partition a table by a date column without considering year. Doesn't look to me that it can be done without inventing another column.

1. This is a primer question. Is it possible to partition by month (not year) when keying on a date column? January in partition one,.......December in in partition 12 ? [...] 13781 77 52_Re: db2 v6 trigger calling a cobol stored procedure.10_Shery Hepp17_schepp@SRPNET.COM31_Thu, 23 Aug 2001 14:23:08 -0700565_iso-8859-1 William - here's a few examples we have set up and are successfully executing

SET CURRENT SQLID = 'id'# CREATE TRIGGER schema.RCAPA001 AFTER INSERT ON id.table REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL schema.SP11200A ('7' ,N.col1 ,N.col2 ,CURRENT DATE ,'Y',0) ; END # COMMIT # CREATE TRIGGER schema.RCAPA901 AFTER UPDATE OF col ON id.table REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL WHEN (N.col = 'P' AND O.col <> 'P') BEGIN ATOMIC CALL schema.SP11207 (N.cola, N.colb, CURRENT DATE, 'Y', 0); END # COMMIT # [...] 13859 44 24_Re: Stored pocedure call13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 23 Aug 2001 16:22:36 -0500598_US-ASCII Hi Rajesh, Here is a sample of calling a SP from the CLP. This was a proc I tested. DB2 "CALL SPNAME('INSERT INTO XXXXX.XXXXXXX (WJLOGON,WJJOB,WJJTYPE, WJADDED) VALUES(''XXXXX'',279735,''P'',CURRENT TIMESTAMP)',0)" I'm not sure of your parameters but this should give you an idea on how to do it. HTH Kurt

>>> urajes1@YAHOO.COM 08/23/01 04:07PM >>> Hi Gurus I wrote SQL stored procedure and created , its successfull my doubt is how can I execute from clp and how do I see the values returned by the stored procedure from clp. We use UDB v7 , unix on Solaris and AIX pls. help [...] 13904 17 31_How to find version # of OS/3908_K.Balaji19_K.Balaji@TARGET.COM31_Thu, 23 Aug 2001 16:36:06 -0500381_iso-8859-1 Hi all, How to find the version number of the OS/390 that is installed in my production shop ? Thanks Balaji Ph # (510) 727-3259

================================================ 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. 13922 68 24_Re: Stored pocedure call15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Thu, 23 Aug 2001 14:42:59 -0700418_us-ascii Hi Kurt Here is the procedure I have created

file name: f1

create procedure tntwk.test_proc(in t1 integer , out t2 integer) language sql begin set t2 = t1 + 10 ; end@

I registered by using db2 -td@ -f f1 It is successfully created

Now I got to execute from the CLP. How can I execute and how do i capture the value and how do I see the value that is returned by the procedure [...] 13991 74 25_syscopy & dsvolser column14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Thu, 23 Aug 2001 14:59:47 -0700304_iso-8859-1 Hi listers,

Can someone explain why when I look at row a in syscopy that dsvolser is not populated?

We are DB2 S390 on version 5.1.

Thanks,

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

14066 35 29_Re: syscopy & dsvolser column15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 23 Aug 2001 17:07:15 -0500579_iso-8859-1 Daryl,

The dsvolser column is only populated for non-cataloged image copies.

Michael Murley BMC -----Original Message----- From: Johnson, Daryl [mailto:Daryl.Johnson@PACIFICORP.COM] Sent: Thursday, August 23, 2001 5:00 PM To: DB2-L@RYCI.COM Subject: syscopy & dsvolser column



Hi listers, Can someone explain why when I look at row a in syscopy that dsvolser is not populated? We are DB2 S390 on version 5.1. Thanks, Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719 [...] 14102 61 35_Re: How to find version # of OS/39011_Ioana Masek29_Ioana_Masek@AISMAIL.WUSTL.EDU31_Thu, 23 Aug 2001 17:06:47 -0500708_us-ascii At a MVS console issue the next command: D IPLINFO You'll get all the IPL info, inclusive the OS release. Ioana

















"K.Balaji" on 08/23/2001 04:36:06 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Ioana Masek/Systems/ais)





Subject: How to find version # of OS/390















Hi all, How to find the version number of the OS/390 that is installed in my production shop ? Thanks Balaji Ph # (510) 727-3259 [...] 14164 114 26_DB2 Utility parallel tasks10_Wang.James24_Wang.James@AAA-CALIF.COM31_Thu, 23 Aug 2001 15:57:42 -0700382_iso-8859-1 Did anyone have this problem before? And, what are the options?

We just migrated to DB2 V6.

Same as pre-V6, we have X number of JES2 initiators which is less than the value of IDBACK for DSNZPARM. But now on weekly basis, when our DB2 REORG cycle starts, we had jobs ended with reason code '00F30055' which means no available thread to connect to DB2. [...] 14279 90 30_Re: MONTHNAME function missing13_Neil Courtney19_NCOURTN@CLEAR.CO.NZ31_Fri, 24 Aug 2001 11:21:42 +1200386_- thanks Kals. You hit it in one. We found the relevent installation jobs, and investigated it. However, the cost of the extra bits required (C++ compiler etc.) made it not worth the effort. We will make do with the old CASE statements, as noted by Bill and Peter.

This was another case of RTFM. After all these years ITB, I should have been able to read a manual properly! [...] 14370 88 30_Re: DB2 Utility parallel tasks0_19_Tim.Lowe@STPAUL.COM31_Thu, 23 Aug 2001 18:55:10 -0500514_us-ascii James, Yes I think that quite a few people have run into this problem after DB2 V6 migration. This is a "new feature" in DB2 V6 in order to implement some of the new parallel features. You need to increase your thread limits.

In my opinion, I think that this one was poorly documented. I thought that there was something in the DB2 V6 release guide, but I can't find it now. And, I remember hearing it mentioned at a DB2 technical conference presentation, but that was a year or more ago now. [...] 14459 84 24_Re: Stored pocedure call12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 23 Aug 2001 17:26:45 -0700512_us-ascii in command center issue after the connect to xxxx user xxxx call tntwk.test(10,?) - it'll return the value t2 in the output just like a regular sql statement --- Rajesh Udandrao wrote: > Hi Kurt > Here is the procedure I have created > > file name: f1 > > create procedure tntwk.test_proc(in t1 integer , out > t2 integer) > language sql > begin > set t2 = t1 + 10 ; > end@ > > I registered by using db2 -td@ -f f1 > It is successfully created > > Now I got to execute from the [...] 14544 75 51_Re: CSP V3.3 and DB2 V5 / "optimize for" gives -19930_=?iso-8859-1?q?learndb2=20Db?=19_xeds007@YAHOO.CO.IN31_Fri, 24 Aug 2001 03:48:13 +0100573_iso-8859-1 May be you can check for the sql syntax in the cobol code created while generating your CSP program.

HTH Sundb

> -----Original Message----- > From: Jean-Paul Coenen [SMTP:jpc_db2@HOTMAIL.COM] > Sent: Thursday, August 23, 2001 5:09 PM > To: DB2-L@RYCI.COM > Subject: CSP V3.3 and DB2 V5 / "optimize for" gives -199 > > Hi folks, > > We are using CSP V3.3 and DB2 V5. > I'm hardly familiar with CSP and I'm stuck with an sqlerror -199. > (found anything in the archive) > > In the "Object Selection Conditions" of a query process, I added the > [...] 14620 74 45_Re: DB2 Stored Procedure builders and schemas11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Tue, 21 Aug 2001 13:45:03 -0600394_iso-8859-1 Hi John,

Check for a SPECIFIC phrase in your CREATE PROCEDURE statement. We use the same specific name as our procedure name like the example below:

CREATE PROCEDURE UDP.EW_PS_RECON_A (IN process_oid int) SPECIFIC UDP.EW_PS_RECON_A LANGUAGE SQL

I'm guessing that the schema in the SPECIFIC clause is different than the one in the main clause for your proc. [...] 14695 66 34_Re: Setting isolation level in sqr29_=?iso-8859-1?q?Half=20Eaten?=24_half_beaten@YAHOO.COM.AU31_Fri, 24 Aug 2001 13:56:40 +1000570_iso-8859-1 Hi Chris, I don't know what sqr is either, but ... a) from the shell (eg Korn):

DB2 SET CLIENT CONNECT 2 DB2 CHANGE ISOLATION TO UR

(Read up on these commands before you use 'em)

b) from Windows if sqr uses ODBC: Control Panel -> ODBC Data Sources Select the SYSTEM DSN tab Select the System Data Source Name you wish to change and click 'Configure' Connect to the source (which will be on AIX UDB 5.2) with your userid & PW On the CLI/ODBC settings box, click 'Advanced...' On the Transaction tab you will see Isolation Level Click [...] 14762 116 35_Re: Question on how to implement RI11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Tue, 21 Aug 2001 13:36:23 -0600616_iso-8859-1 I would expect a significant drop in performance for basic referential integrity checks, unless your application has very low transaction volumes. Perhaps someone with more experience in this area could elaborate further, though, as I am guessing. (our environment uses stored procs, but not RI for data warehousing) The reason for my guess is that the stored procs have to be instantiated and run, whereas the RI rules are likely run within the existing DB processes. (please confirm or deny, someone?) Also, for AIX, I recall that stored procs perform a call to the DB for each statement, not just [...] 14879 113 41_CLP return code capure using Linux script11_Rolf Kramer23_RKramer@POWERLAN.COM.AU31_Fri, 24 Aug 2001 14:49:56 +1000341_iso-8859-1 Using Turbo Linux and am now implementing shell scripts scheduled by CRONTAB for nightly DB maintenance.

DB2 UDB for Linux v7.2.

Saw a reference in DB2 Command manual about using shell script to interrogate return code from commands looks like this:

db2 ....command.... if [ "$?" = "0" ] then echo OK fi [...] 14993 80 35_Re: The big black box ?- Optimizer?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 24 Aug 2001 10:47:22 +0530403_- Susan,

Number of getpage is definitely a criteria for choosing the best accesspath. That is the reason why generally all the possible parameters influencing the optimizer is looked at. If then also there is difference between optimizer's and my thinking about the accesspath, we try looking the performance report for the queries with different accesspaths and choose whichever is better. [...] 15074 84 34_Re: Query Large denormalized Table16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 24 Aug 2001 10:52:19 +0530640_- Alan,

You have tried to provide enough details. For giving some comments on this problem, please give the DDL for the table or table structure, query which you have right now, accesspath for the same and indexes if any exist.

Regards Sanjeev IBM Certified Solutions Expert DB2 V7 Database Administration OS/390



> -----Original Message----- > From: Alan Gredell [SMTP:agredell@KEMPERINSURANCE.COM] > Sent: Friday, August 24, 2001 1:01 AM > To: DB2-L@RYCI.COM > Subject: Query Large denormalized Table > > I have a problem that I hope the list can help solve! My Search table is > a > denormalized list of [...] 15159 94 45_Re: CLP return code capure using Linux script29_=?iso-8859-1?q?Half=20Eaten?=24_half_beaten@YAHOO.COM.AU31_Fri, 24 Aug 2001 15:16:04 +1000577_iso-8859-1 Hi Rolf, I'd like to write a fuller explanation (but I'm flat out like a lizard drinkin'), cause there are a few ways of doing this, but one of the things you can do is this:

SQLCODE=$(db2 -ec +o -f"$SQLFILE" -v -z"$LOGFILE")

The above bit of code comes from a Korn shell script (you probably use BASH but should make little difference) I wrote to implement -911 retry logic. The sql is read from a file ($SQLFILE) and the output is appended to the $LOGFILE. The SQL code ends up in variable $SQLCODE, which you can interogate with something like [...] 15254 213 35_Re: Calling all SQL Guru's.........13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 23 Aug 2001 22:53:15 -0500503_iso-8859-1 Shery,

If I am reading this correctly then the change to include header and footer information has introduced duplicates into either of the left outer joins. You may be able to add a predicate to the ON clause to NOT "left join" to the additional tables when the comment type is anything other than 'LIN'. Given the COMMENT_TYPE is from AA, then it may be the join to BBB that is introducing the extra rows. For the left join the rows from the left table will still be preserved. [...] 15468 86 31_Re: Correlated table expression13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 24 Aug 2001 00:24:13 -0500657_us-ascii Toni,

As mentioned previously, this functionality was added in V6.

The simplest way to think of these is combination of a correlated subquery and a nested table expression. A correlated subquery contains a correlated reference and is only executed for the rows which match the correlation predicate (or every outer qualifying row - minused cached), but the outer query cannot retrieve columns from the subquery table(s). A standard nested table expression is non-correlated and therefore executed for every qualifying table row based on local predicates (if any), but all columns in the select list can be retrieved by the outer [...] 15555 106 22_Re: Partition by Date?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 24 Aug 2001 11:06:41 +0530358_- Jones,

I think you can have the partitioning keys in the form of MMDD (Char)

0101 - 0107 0108 - 0114 0115 - 0122 0123 - 0131..............and so on.

Similarly for February, MM portion i.e. 01 will be 02 and last day can be either kept 31 or 29. I am not sure how is it going to create some problem. Could you please clarify this? [...] 15662 38 16_Re: Db or not Db10_john clark20_pak02449@PIXIE.CO.ZA31_Fri, 24 Aug 2001 07:30:32 +0530477_us-ascii Some good points particularly on the possible growth of the samll table, which of course could cause program crashes if table limits were exceeded.

Bearing that in mind though, if you cannot sort the input (maybe even after extracting it) I would definately go with the W/S table route.

A couple of years ago, we did a bench-mark on a similar situation (1M singleton selects vs 1M SEARCH ALLs) and the CPU usage was 60 times higher for the SELECTS. [...] 15701 17 41_Debra Kopecky/PCSHS is out of the office.13_Debra Kopecky23_Debra.Kopecky@PCSHS.COM31_Thu, 23 Aug 2001 23:08:32 -0700503_us-ascii I will be out of the office starting 08/23/2001 and will not return until 08/24/2001.

I am participating in the disaster recovery exercise; there will be no test support during these 2 days. Contact the Command Center with all production issues.

================================================ 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. 15719 148 31_Re: Copy data from SSID to SSID15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Fri, 24 Aug 2001 11:42:56 +1000655_iso-8859-1 Smike,

If performance is any consideration I've found with our medium sized DB we get similar end-to-end timings between DSNTIUAL unload/ DB2 LOAD and the DSN1COPY OBIDXLAT method

regards, Steve





> -----Original Message----- > From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] > Sent: Thursday, August 23, 2001 7:09 AM > To: DB2-L@RYCI.COM > Subject: Copy data from SSID to SSID > > Curious as to how others are copying data from SSID.DB.TS to SSID2.DB2.TS. > The options that I can see are to DSNTIAD SELECT * FROM DB.TB to dataset > and > LOAD INTO DB2.TB or DSN1COPY with OBIDXLAT. Any other options [...] 15868 62 25_AW: DB2 VERSION 6 SUPPORT12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 24 Aug 2001 08:13:22 +0200383_iso-8859-1 Linda,

AFAIK there is no end of support announced at this point in time. For other versions please see the table below.

DB2 Vers Product ID Generally Available Withdrawn from marketing End of service

Version 3 5685-DB2 17 December 1993 29 February 2000 01 January 2001

Version 4 5695-DB2 30 November 1995 01 December 2000 31 December 2001 [...] 15931 17 49_George Koegel/Rochester/IBM is out of the office.13_George Koegel18_gkoegel@US.IBM.COM31_Fri, 24 Aug 2001 02:19:05 -0400521_us-ascii I will be out of the office starting August 23, 2001 and will not return until August 27, 2001.

I will respond to your message when I return. If this is urgent due to DBDC Subsystem Support Problems, contact 1800-446-4722 and request support via Calllist of pager

================================================ 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. 15949 151 40_Re: AW: Generation of unique sequence No10_praveen_kj19_praveen_kj@INFY.COM31_Fri, 24 Aug 2001 12:05:07 +0530514_iso-8859-1 Hi,

When i fired this trigger CREATE TRIGGER T_UNIQUE NO CASCADE BEFORE INSERT ON EMPDEMO9 REFERENCING NEW AS NEW_UPD FOR EACH ROW MODE DB2SQL SET NEW_UPD.EMPID = GENERATE_UNIQUE( );

I am getting this error

During SQL processing it returned: SQL0969N There is no message text corresponding to SQL error "-20100" in the message file on this workstation. The error was returned from module "DSNXORFN" with original tokens "2 -440 42884 FUNCTION,GENERATE_UNIQUE ". SQLSTATE=56069 [...] 16101 55 27_Calling Java Sp from SQL SP0_24_Sridhar_Kondusamy@I2.COM31_Fri, 24 Aug 2001 12:24:50 +0530432_us-ascii Hi Group

Good Morning

Can anyone helpme out in how to call a Java Stored procedure from a SQL stored Procedue in DB2

I am able to call the Java program by creating a functio with an external link to it and excute the same

But I am intersted in calling a Java Stored proc from a SQL SP without creating this extra function... Can any one just provide me with a sample code if it can be done [...] 16157 26 22_Re: Partition by Date?15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Fri, 24 Aug 2001 09:57:47 +0200337_us-ascii Just a thought ... how about using the julian date value for the corresponding partitions?

001 to 007 (1 Jan to 7 jan) 008 to 0014 (8 to 14 Jan) 015 to 022 (15 to 22 jan) 023 to 031 ( 23 to end of month) 032 to 038 (...

only problem may be regards 29 february ... not certain exactly how you'd handle that .. [...] 16184 19 9_Re: SPT0115_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 24 Aug 2001 04:59:08 -0500564_- Greg

Some words to DEGREE(ANY). I'm very surprised that this is your default, because we made very bad experience with this default in the past (I suppose it was V4 and we don't dare to use this default anymore). The optimizer decided to use parallelism for almost every part. table even if the WHERE-condition contains those parts of the part. key with equal- predicates, which made up the limit keys. These access paths gave us very poor performance, so we decided to use only DEGREE(ANY), if we are sure parallelism gives us a better performance. [...] 16204 163 26_DB2 Utility parallel tasks32_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Fri, 24 Aug 2001 13:11:46 +0300501_iso-8859-1 Once we got into the same problem. You might want to have a look at the following apar if you are dealing with RRSAF threads:





APAR NUMBER: PQ44290 RESOLVED AS: PROGRAM ERROR





ABSTRACT:

PQ44290: IDBACK NOT DECREMENTED FOR RRSAF CONNECTIONS



-----Original Message----- From: Wang.James [mailto:Wang.James@AAA-CALIF.COM] Sent: Friday, August 24, 2001 1:58 AM To: DB2-L@RYCI.COM Subject: [DB2-L] DB2 Utility parallel tasks [...] 16368 123 31_Re: Copy data from SSID to SSID12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 24 Aug 2001 13:18:40 +0200415_iso-8859-1 RE: [DB2-L] Copy data from SSID to SSIDHi, Well - you can always use "INSERT" ;-)

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: Grainger, Phil Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Thursday, August 23, 2001 7:38 PM Subject: Re: Copy data from SSID to SSID [...] 16492 26 35_Re: How to find version # of OS/39010_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 24 Aug 2001 12:44:02 +0200453_us-ascii Hi

Try this

/*REXX*/ TRACE O SYSTEM=MVSVAR(SYSOPSYS) SAY SYSTEM EXIT

HTH

Max Scarpa Useless DB2 sysprog & expensive S390 optional Diamonds Platinum Gold Certified beer taster

================================================ 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. 16519 77 25_Re: BETWEEN vs. >= and <=12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 24 Aug 2001 13:44:22 +0200361_iso-8859-1 Hi,

Let's look on the extrapolation done when we have constants vs. having host-vars, may be those can explain. The question is whether the query rewrite (which I don't remember seeing but accepts) affects the interpolation calculations as well or it does not . The chapter dealing with it is in the admin. guide (V6 - section 5.8.3.4.3) [...] 16597 61 40_Re: AW: Generation of unique sequence No10_praveen_kj19_praveen_kj@INFY.COM31_Fri, 24 Aug 2001 16:15:17 +0530519_iso-8859-1 Hi, I want to create a table with a particular column as identity column. I am firing the following query.

create table SML(b INTEGER GENERATED ALWAYS AS IDENTITY ,a integer) in smlcisdb.smlcists;

I am getting the error DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IDENTITY" was found following "". Expected tokens may include: "LOCATOR ". SQLSTATE=42601 [...] 16659 108 60_Re: autoreply messages (was: Setting isolation level in sqr)16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Fri, 24 Aug 2001 12:34:32 +0100386_us-ascii John,

that's because you request a return receipt :-)

HAVE A NICE WEEKEND EVERYBODY, the sun is shining in the south of England, believe it or not...

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...] 16768 228 22_Re: Partition by Date?0_24_Bruce.Williamson@PSIR.IE31_Fri, 24 Aug 2001 12:46:07 +0100304_iso-8859-1 Howzit Hayden?

You'll need to add an additional column to do this. Just a thought, what about partitioning by week, this will give you roughly the same number of partitions. With version 6 you'll be able to use key = WEEK(CURRENT DATE). What are your plans for going to version 6? [...] 16997 142 31_Re: Copy data from SSID to SSID14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 24 Aug 2001 12:43:24 +0100474_iso-8859-1 "INSERT INTO local.creator.tbname SELECT * FROM remote.creator.tbname"

Now THERE's a fun thought for a Friday!!

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com

-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: 24 August 2001 12:19 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Copy data from SSID to SSID [...] 17140 74 60_Re: autoreply messages (was: Setting isolation level in sqr)16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Fri, 24 Aug 2001 12:46:03 +0100469_us-ascii Sorry John,

talking through my neck due to Friday euphoria...

this is some colleague who's set an auto-reply because he/she's away on holiday or something...



Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

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

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK [...] 17215 29 35_Re: How to find version # of OS/39013_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 24 Aug 2001 07:56:24 -0400347_iso-8859-1 Hello Balaji,

In addition to the methods already mentioned, if you are an ISPF user, you can go to Dialog Test from your ISPF primary menu -- assuming your ISPF folks haven't removed it from the menu. From the Dialog test menu, choose 3 for Variables and look for the variable ZOS390RL. To do that, you can use the command: [...] 17245 93 31_AW: Copy data from SSID to SSID12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 24 Aug 2001 14:00:54 +0200534_iso-8859-1 Phil,

hope you are kidding... If there are millions of rows to be inserted DB2 writes millions of log records and log records and log records and log records and ...... ;-))

Have a nice weekend.

With kind regards - mit freundlichen Grüssen, Georg H. Peter

---------------------------------------------------------------------- Heard from another flight attendant's comment on a less than perfect landing: "We ask you to please remain seated as Captain Kangaroo bounces us to the terminal." [...] 17339 33 9_Re: SPT0113_Peter Nuttall22_peter.nuttall@CSFS.COM31_Fri, 24 Aug 2001 13:55:12 +0200384_windows-1252 We also had a problem with DEGREE(ANY) and a CICS transaction which required data from two different DB2 subsystems, one with data sharing and one without. This caused an abend at runtime. I believe the description of the problem was 'Transactions with Ordered dependencies', and as far as I am aware it is not fixed. We now set our default binds to DEGREE(1) also. [...] 17373 13 40_Re: AW: Generation of unique sequence No0_24_db46@DAIMLERCHRYSLER.COM31_Fri, 24 Aug 2001 08:16:34 -0400315_iso-8859-1 Actually it is recommended that you use numeric(6,0) instead of integer ===============================================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. 17387 18 33_Indexes and predicate properties.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 24 Aug 2001 07:08:34 -0500321_- Hi all,

This is a quest to understand the underlying rules of matching cols....

What appears below has been culled from the Admin guide pg 867. ************ The following examples of predicates illustrate general rules. In each case , assume there is an index on columns(C1, C2, C3, C4) of the table [...] 17406 59 20_Re: CAF & Versioning14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 24 Aug 2001 07:10:32 -0500565_- Dom

The precompiler generates two codes - a version code, which can be either a fixed character string or a time based character string. The version code is used to control package replacement during a bind: _it has no direct bearing on which package will be used at execution time_. - a (8 byte hex) consistancy token, which is almost always a time based value. It is also stored in the package, and is also part of the parameter list passed in the call to DSNHLI. (In COBOL, the parameter list contains two PIC S9(9) COMP fields. The initial values [...] 17466 34 52_Re: db2 v6 trigger calling a cobol stored procedure.14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 24 Aug 2001 07:15:28 -0500627_- Perhaps if you provided a copy of the bind error ...

James Campbell



>-----Original Message----- >From: Proctor, William [mailto:william.proctor@TGSLC.ORG] >Sent: Thursday, August 23, 2001 1:01 PM >To: DB2-L@RYCI.COM >Subject: db2 v6 trigger calling a cobol stored procedure. > > >Is there anyone out there using a db2 trigger to call a cobol stored >procedure? If so could I get an example of the trigger and the stored >procedure? It would be greatly appreciated. We have been unable to get >this to work. I can create and test the stored procedure and I can create a >trigger without the call in [...] 17501 156 35_Re: AW: Copy data from SSID to SSID9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Fri, 24 Aug 2001 13:21:55 +0100359_us-ascii

Is this SQL possible at V7 (or V6?) - i.e. multiple locations in same query. Im back at V5 where its not ? Can't remember seeing anything about this in the V6 and V7 'Whats New'. Kind Regards, Andy Hunt - Scottish And Southern Energy











"Peter, Georg" on 24/08/2001 13:00:54 [...] 17658 36 31_indexes and predicate matching.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 24 Aug 2001 07:23:38 -0500367_- Hi all,

This is a quest to understand the underlying rules of matching cols....

What appears below has been culled from the Admin guide pg 867. *********** The following examples of predicates illustrate general rules. In each case , assume there is an index on columns(C1, C2, C3, C4) of the table and that 0 is the lowest value in each column. [...] 17695 44 41_SET TO 13_Harry Woelfer27_Harry_Woelfer@MCCORMICK.COM31_Fri, 24 Aug 2001 09:17:45 -0400602_us-ascii Dear List Members:

We have a mainframe program (DB2 release 6.1) that executes the SQL statement "SET TO ". For example, "SET :HOSTVAR TO CURRENT TIMESTAMP". It appears that this type of SET statement does not exist in DB2/UDB for AIX. Am I right?

Here is our situation. This mainframe program executes a DB2 package that we are trying to BIND on the AIX platform (DB2 release 7.1). The package contains SQL to update various DB2 tables on the AIX platform, as well as a few queries for read-only access to tables back up on the mainframe, [...] 17740 17 69_Re: FW: New Book : DB2 UDB for z/OS and OS/390 V7 Certification Guide14_Donald A Smith18_dsmithj@US.IBM.COM31_Fri, 24 Aug 2001 09:28:33 -0400522_us-ascii I looked for this publication on the IBM publication website and when I fond the publication the site lists it as pending publication. What is up w/ that? Do a getain number have to be orderd before the publication becomes availible? Could someone clue me in? thanks, Don

================================================ 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. 17758 76 34_Re: Query Large denormalized Table12_Alan Gredell28_agredell@KEMPERINSURANCE.COM31_Fri, 24 Aug 2001 08:35:21 -0500635_- OK, Sanjeev, here goes: "create table... (PLC_ID_K DECIMAL(13 , 0 ) ,ORG_PTY_ID_K DECIMAL(13 , 0 ) ,PTYM_ID_K DECIMAL(13 , 0 ) ,REGIS_ID_K DECIMAL(13 , 0 ) ,ORGM_M CHAR(70 ) NOT NULL WITH DEFAULT ,ORGM_USE_C CHAR(3 ) NOT NULL WITH DEFAULT ,ORGE_ORG_LGL_M CHAR(70 ) NOT NULL WITH DEFAULT ,ADDR_LINE1_X CHAR(35 ) NOT NULL WITH DEFAULT ,ORGE_FEIN_N DECIMAL(9 , 0 ) WITH DEFAULT ,ADDR_CITY_M CHAR(20 ) NOT NULL WITH DEFAULT ,ADDR_ST_C CHAR(2 ) NOT NULL WITH DEFAULT ,ADDR_CNTRY_C CHAR(3 ) NOT NULL WITH DEFAULT ,ORGE_CNTY_C CHAR(3 ) NOT NULL WITH DEFAULT ,ADDR_POST_C CHAR(10 ) NOT NULL WITH DEFAULT ,ORGE_PTY_PLC_ID_C CHAR(1 ) NOT [...] 17835 47 35_WebSphere on AIX, WAS DB on OS/390?14_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM31_Fri, 24 Aug 2001 09:56:27 -0400588_us-ascii We're currently running WebSphere Application Server 3.5.4 on a couple of AIX application servers, with the corresponding WAS database repository on a separate AIX database server, under UDB v7.2. We're now in the process of looking at a high-availability solution for our database servers. One alternative is to build an HACMP cluster on AIX for our UDB database servers, and put the WAS database in that environment. Another alternative is to move the WAS database to OS/390, into our 4-way DB2 data-sharing environment, accessible by sysplex distributor via DB2 Connect. [...] 17883 66 13_Arc Solutions22_Varghese, Ajith George32_ajith.varghese@BLR.HPSGLOBAL.COM31_Fri, 24 Aug 2001 19:05:10 +0500570_- Hi Kumaresh Do you feel that BMC ARC/CTL can correctly reposition VSAM datasets. I feel that it does not comply in a situation wherein the user cancels the Job. It is unable to reposition the record. Do you have any explanations for this behaviour?? I encounter a problem only with the repositioning of VSAM and other flat datasets. If you get an answer for this from BMC, kindly reply. DB2 error handlers work very well in this scenario. If the driver is a cursor, then will the cursor be closed after the cancel is issued?? > -----Original Message----- > From: [...] 17950 26 5_Solve22_Varghese, Ajith George32_ajith.varghese@BLR.HPSGLOBAL.COM31_Fri, 24 Aug 2001 19:08:04 +0500657_- Hey Kumaresh Theres one more thing that I would like to ensure. Does the automatic checkpoint issual occur after every read?? Thank you







WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.----------------- [...] 17977 51 5_Files22_Varghese, Ajith George32_ajith.varghese@BLR.HPSGLOBAL.COM31_Fri, 24 Aug 2001 19:09:08 +0500672_- Kumaresh Are all the file writes commited even before the physical checkpoints are issued or will they rollback? Thank you





AGV Ajith George Varghese HCL Perot Systems, #947, Roshini Chambers, 24th Main, 2nd Phase, J.P.Nagar, Bangalore -560078 INDIA Phone: 080 6657309/ 080 6657310 Mobile: 9845155139

> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Thursday, August 23, 2001 8:54 PM > To: DB2-L@RYCI.COM > Subject: Re: UDF story - OS/390 DB2 > > There are a number of additional UDF performance considerations noted in > the > red book V6 Technical Update, section 2.2, SG24-6108. > > Roger Miller, [...] 18029 62 45_Re: SET TO 11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Fri, 24 Aug 2001 15:19:09 +0100605_us-ascii Harry

I think what you need is the VALUES INTO statement e.g.

EXEC SQL VALUES(CURRENT TIMESTAMP) INTO :hostvar;

I hope this helps. Dave

Harry Woelfer wrote:

> Dear List Members: > > We have a mainframe program (DB2 release 6.1) that executes the SQL statement > "SET TO ". For example, "SET :HOSTVAR TO > CURRENT TIMESTAMP". It appears that this type of SET statement does not exist > in DB2/UDB for AIX. Am I right? > > Here is our situation. This mainframe program executes a DB2 package that we > are trying to BIND on [...] 18092 54 35_Re: How to find version # of OS/3900_22_Rohn.Solecki@MTS.MB.CA31_Fri, 24 Aug 2001 09:54:19 -0500480_us-ascii Thanks Max and Dave.

Can anyone interpret the last part, "HBB7703" of the output I got from rexx

OS/390 02.10.00 HBB7703











Max Scarpa @RYCI.COM> on 2001/08/24 05:44:02 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: How to find version # of OS/390 [...] 18147 95 45_Re: SET TO 13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 24 Aug 2001 09:55:23 -0500372_US-ASCII Harry, I'm running V7 FP3 on NT and the sysibm.sysdummy1 table exists. Just an FYI. I'm not sure if this is the case on all platforms. Regards, Kurt



>>> David Hayes 08/24/01 09:19AM >>> Harry

I think what you need is the VALUES INTO statement e.g.

EXEC SQL VALUES(CURRENT TIMESTAMP) INTO :hostvar; [...] 18243 18 27_Federated Database System ?15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 24 Aug 2001 10:57:42 -0400520_iso-8859-1 What is a Federated Database System? Why would I have/use a Federated Database System? I have installed UDB PC 7.1 and in the documentation it mentions using the control center to admin Federated Database System. However I cannot determine how that is done. Were trying to replicate data and are looking at using data joiner with dprop. I stumbled on the Federated stuff and it seems like this may be a way of replicating data from ibm to non-ibm sources. Any enlightenment would be greatly appreciated. [...] 18262 105 30_Re: DB2 Utility parallel tasks16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Fri, 24 Aug 2001 16:04:49 +0100548_us-ascii James,

I recall a thread on this subject (or something very similar) a few months ago.

Because of reasons connected to parallelism, it was convened that when migrating to V6 you would need to increase the number of threads (your thread limits), this is because as you rightly point out each task does amount to one thread +++++ (other reasons for having more threads, but I really cannot recall them, I tried searching the archives but no go - either our internet connection or the list server is not working properly). [...] 18368 25 26_SYSADM AUTHORITY - HELP!!!14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Fri, 24 Aug 2001 10:55:12 -0400580_iso-8859-1 My agency is very new to DB2 with our installation occurring just 8 months ago. We are currently trying to implement RACF security with DB2. We are a very small shop with two fulltime DB2 DBA's and one DB2 systems support person. Given the fact that the DBA's are responsible for all aspects of DB2 including its functionality and accessibility, we have requested the SYSADM authority to be granted to our DBA group. However, our RACF administrators are very hesitant to give us so much power over the system including the access to data. I would like to get some [...] 18394 68 35_Re: How to find version # of OS/39013_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 24 Aug 2001 10:05:33 -0500491_US-ASCII Rohn, I believe that is the FMID. Kurt

>>> Rohn.Solecki@MTS.MB.CA 08/24/01 09:54AM >>> Thanks Max and Dave.

Can anyone interpret the last part, "HBB7703" of the output I got from rexx

OS/390 02.10.00 HBB7703











Max Scarpa @RYCI.COM> on 2001/08/24 05:44:02 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 18463 73 35_Re: How to find version # of OS/39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 24 Aug 2001 11:11:21 -0400436_iso-8859-1 HBB7703 is the SMP/E FMID of the product.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Friday, August 24, 2001 10:54 AM To: DB2-L@RYCI.COM Subject: Re: How to find version # of OS/390



Thanks Max and Dave.

Can anyone interpret the last part, "HBB7703" of the output I got from rexx [...] 18537 17 35_Re: How to find version # of OS/39010_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 24 Aug 2001 17:18:15 +0200328_us-ascii It's the FMID of your OS390.

HTH

Max Scarpa Useless DB2 sysprog

================================================ 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. 18555 18 30_Re: SYSADM AUTHORITY - HELP!!!0_26_truman.g.brown@VERIZON.COM31_Fri, 24 Aug 2001 11:21:53 -0400325_us-ascii DB2 systems programmer should have install SYSADM; create a group ID for your two DBA's that has SYSADM (DBAs come and go, and the group entries can be updated accordingly). If the DBA's have the responsibility you mentioned, SYSADM is definitely a requirement; tell the RACF people to mind their own business. [...] 18574 54 30_Re: SYSADM AUTHORITY - HELP!!!10_Earl Dixon19_Earl_Dixon@BELK.COM31_Fri, 24 Aug 2001 11:25:53 -0400357_us-ascii I think that your DBA's should have DBADM authority. You should always limit the number of people who have sysadm authority as for the amount of damage that could be done by an inexperienced person with sysadm authority. I hope this answers your question. dbadm will give them all the authority that a DBA will need to perform his/her duties. [...] 18629 54 35_Re: indexes and predicate matching.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 24 Aug 2001 10:27:38 -0500517_iso-8859-1 I usually think of it as if I'm looking through the phone book (simplified example full of holes, I know, but usually serves the purpose in cases like this). In the first example, C1 > 5 and C2 = 7, think of it as "LAST NAME > 'SMITH' and FIRST NAME = 'TED'". I can zip right to the page starting with "SMITH" in the phone book, but the only way to find the rows with a first name of 'TED' would be to start scanning through the pages (still using the "index", just not 'matching' on first name since [...] 18684 14 29_Log Suspend & 32k Tablespaces15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM31_Fri, 24 Aug 2001 10:25:15 -0500436_us-ascii Has anyone heard of a problem involving the 'set log suspend' and 32k tablespaces? 32k tablespaces may have to be recovered if they are being updated when a 'set log suspend' is issued?

================================================ 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. 18699 18 37_Assistance With SPB from NT to OS/39032_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Fri, 24 Aug 2001 18:36:22 +0300470_- I have came across the same thing last week wiht admin client V7.2. Though I have downloaded a 160 Meg. fixpack and applied it, it did not solve the problem. Than I looked to find some other version of the product and fortunately after discovering we also have V7.1 in hand, I have installed it and it worked well. There is an apparent bug with V7.2. Some tabs related to Host & AS400 databases are not shown in the CCA screens, though documented in online help. [...] 18718 46 30_Re: SYSADM AUTHORITY - HELP!!!11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET31_Fri, 24 Aug 2001 08:42:51 -0700359_US-ASCII We are a school district with 1 DBA, 1 Sr Database analyst training to be the DBA and 1 systems person. Both the DBA and myself have SYSADM because we have to do everything in DB2 except install it.

Kathy Jones Central Information Services Clark County School District Senior Database Analyst 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...] 18765 34 35_Re: How to find version # of OS/39015_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 24 Aug 2001 11:44:33 -0400628_- We have something like TSO TASID, which give all most everything about the mainframe.

Thanks..sibi

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Friday, August 24, 2001 10:18 AM To: DB2-L@RYCI.COM Subject: Re: How to find version # of OS/390



It's the FMID of your OS390.

HTH

Max Scarpa Useless DB2 sysprog

================================================ 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. [...] 18800 55 30_Re: SYSADM AUTHORITY - HELP!!!12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Fri, 24 Aug 2001 11:47:01 -0400314_US-ASCII I would definitely say that as a DBA, responsible for all aspects of DB2, that you will want and need SYSADM. Other authorities would probably work in most cases, but if there is a problem, you will want to have access to whatever you need - without waiting for someone to grant you the needed auth. [...] 18856 67 35_DB2 V6 - IRLM Link List Requirement10_King C. Au15_King_Au@GAP.COM31_Fri, 24 Aug 2001 08:49:23 -0700426_us-ascii Hello Kirk, Thank you so much for your information. This makes me feel more comfortable not to put the that module (DXRRL183) in the linklist. Thanks again, King Au The Gap, Inc. San Bruno, California





Date: Thu, 23 Aug 2001 08:42:38 -0500 From: Kirk Hampton Subject: Re: DB2 V6 - IRLM Link List Requirement MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" [...] 18924 35 30_FW: SYSADM AUTHORITY - HELP!!!14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Fri, 24 Aug 2001 11:50:18 -0400653_iso-8859-1 Many thanks to all of you who responded so quickly! Your help is greatly appreciated!

-----Original Message----- From: Melissa Rogers Sent: Friday, August 24, 2001 10:55 AM To: 'DB2-L@RYCI.COM' Subject: SYSADM AUTHORITY - HELP!!!



My agency is very new to DB2 with our installation occurring just 8 months ago. We are currently trying to implement RACF security with DB2. We are a very small shop with two fulltime DB2 DBA's and one DB2 systems support person. Given the fact that the DBA's are responsible for all aspects of DB2 including its functionality and accessibility, we have requested the SYSADM authority [...] 18960 64 30_Re: SYSADM AUTHORITY - HELP!!!12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 24 Aug 2001 09:03:48 -0700466_us-ascii First question: Did you do the install of DB2? There is an ID called Install-SYSADM that is identified during the install that is the "GOD" id for DB2. It can do everything and anything. Who has access to this ID? Its critical for certain types of maintenance functions.

Secondly, who will be creating databases? Someone has to have SYSADM to create the database and grant DBADM on that database to others or other lessor authorities to others. [...] 19025 52 30_Re: SYSADM AUTHORITY - HELP!!!0_26_JCameron@MSI-INSURANCE.COM31_Fri, 24 Aug 2001 11:00:19 -0500428_us-ascii I agree with George here. Not only is DBADM not enough authority for the small number of DBA's, I'd make sure that I knew the password for Install SYSADM when your DB2 system programmer is on vacation.

JCameron@msi-insurance.com







truman.g.brown@V ERIZON.COM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: SYSADM AUTHORITY - HELP!!! Discussion List [...] 19078 87 30_Re: SYSADM AUTHORITY - HELP!!!12_Kirk Hampton16_khampto1@TXU.COM31_Fri, 24 Aug 2001 11:04:13 -0500467_us-ascii Our DBA team here has always had full SYSADM authority, and the only major damage I can recall has been dropping a table in production when someone thought they were logged into test, which they could have done with DBADM auth as well. The DBA's, in turn, give DBADM to certain key developers in the test environment to offload some development tasks. This lets 5 DB2 DBA's keep up with 4 production and 10 development subsystems, including Peoplesoft. [...] 19166 88 35_Re: How to find version # of OS/39017_Alessandro Brezzi24_alessandro.brezzi@TIN.IT31_Fri, 24 Aug 2001 18:09:54 +0200349_iso-8859-1 Hi, I use the values returned from DB2 after a succesfull connection: this is a sample code from one of my REXX:

--------------- extract from REXX EXEC on Win2K --------

/* connect to database */ If password= "" THEN CALL SQLEXEC 'CONNECT TO' dbname ELSE CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password [...] 19255 48 33_Re: Log Suspend & 32k Tablespaces12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Fri, 24 Aug 2001 11:18:24 -0500525_us-ascii Yes. You cannot rely on "snapshot" or "timefinder" ("mirror-breaking") backups made with "set log suspend" for 32k pagesize tablespaces.

As I understand it, 32k pagesize pages span multiple tracks. Writes are limited to a single track at a time. So, it would be possible to issue the "set log suspend" between the write of the first part and the write of the second part of a page. This would mean a "mirror-breaking" backup could capture a partial write - the very definition of database inconsistency. [...] 19304 59 35_Re: Calling all SQL Guru's.........10_Shery Hepp17_schepp@SRPNET.COM31_Fri, 24 Aug 2001 09:30:12 -0700349_ISO-8859-1 David- thank you for the query- we have thought about using QMF in order to format this report- however that is really not an option. We need to run this in crystal and we're unable to have it format the report correctly from the result sets which is why we're trying to have the view produce the result sets the way we want it too. [...] 19364 65 40_SET = 13_Harry Woelfer27_Harry_Woelfer@MCCORMICK.COM31_Fri, 24 Aug 2001 12:28:15 -0400414_us-ascii Dear List Members:

Thanks to Kurt Sahlberg and David Hayes, I have learned that SYSIBM.SYSDUMMY1 does exist on AIX (as a view). I too am a dyed-in-the-wool mainframe hack. Even so, I got the format of the mainframe SET statement wrong in my original message.

I'm afraid my original message wasn't clear, so I'm posting it again, with the corrections noted above. The key questions are: [...] 19430 87 35_Re: How to find version # of OS/3908_K.Balaji19_K.Balaji@TARGET.COM31_Fri, 24 Aug 2001 11:50:24 -0500562_iso-8859-1 Thanks guys. Your suggestions helped.

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Friday, August 24, 2001 8:11 AM To: DB2-L@RYCI.COM Subject: Re: How to find version # of OS/390



HBB7703 is the SMP/E FMID of the product.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Friday, August 24, 2001 10:54 AM To: DB2-L@RYCI.COM Subject: Re: How to find version # of OS/390 [...] 19518 88 30_FW: SYSADM AUTHORITY - HELP!!!10_CEI, Bruno30_Bruno.CEI@ANDOVER.SEMA.SLB.COM31_Fri, 24 Aug 2001 17:55:51 +0100385_iso-8859-1 -----Original Message----- From: CEI, Bruno Sent: 24 August 2001 17:24 To: 'DB2 Data Base Discussion List' Subject: RE: SYSADM AUTHORITY - HELP!!!



Melissa,

Regarding the question of a specific person having SYSADM authority, I think that you should avoid it. If that person leaves, removing their ID from the system can be a complicated exercise. [...] 19607 126 24_Re: Stored pocedure call11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Fri, 24 Aug 2001 10:57:58 -0600602_iso-8859-1 In our shop, (AIX; UDB 7.2) we can do what you are interested in the following way. We would not normally collect the output variables from sysout though. Instead we usually put all of the results in the DB or we are calling the proc from a COBOL program.

========================================================================== /dss/home/tvht > db2 drop procedure udp.TEST DB20000I The SQL command completed successfully. /dss/home/tvht > db2 -td@ -vf TEST.db2 CREATE PROCEDURE UDP.TEST ( IN in_create_oid int, INOUT out_message varchar(4000) ) SPECIFIC UDP.TEST LANGUAGE SQL [...] 19734 41 22_Re: Partition by Date?0_20_Jones.H@GRAINGER.COM31_Fri, 24 Aug 2001 12:12:00 -0500509_- Sanjeev, I don't quite understand your solution. The partitioning column is a date. It displays as '2001-01-26'. In your CREATE INDEX statement, you can't say

VALUES ('0131')

or, actually, since we have a partition number column to facilitate partition rolling,

VALUES (4,'0131') where 4 is the partition number.

It seems to me that if you created a separate column (call it MMDD) that I could do what you suggest. If MMDD were a CHAR(4) then you could use that ddl coding. [...] 19776 14 20_How to kill a thread0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 24 Aug 2001 12:18:20 -0500446_us-ascii We have spufi that has run away on us. It has been running since Wed and is tying up more and more DB2 resources. Terminating the thread did not work. Any other suggestions short of stopping DB2?

================================================ 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. 19791 34 24_Re: How to kill a thread16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Fri, 24 Aug 2001 10:34:07 -0700440_iso-8859-1 If it is SPUFI, online under TSO, have you tried canceling the TSO session for this user? Or, just have the user hit ATTN on his/her keyboard.

If you end up bringing DB2 down the fastest way I know is to cancel the IRLM.

Duane

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Friday, August 24, 2001 10:18 AM To: DB2-L@RYCI.COM Subject: How to kill a thread [...] 19826 23 39_Re: WebSphere on AIX, WAS DB on OS/390?15_Claudio Sanchez20_clsanchez@BNL.COM.AR31_Fri, 24 Aug 2001 14:39:36 -0300609_us-ascii Bill: I believe that must ask specialists forum of MVS, certainly they will will have some commentary.

To register you :

For IBM-MAIN subscribe / signoff / archive access instructions, send email to listserv@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html



Regards.

================================================ 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. 19850 31 24_Re: How to kill a thread17_Okronglis, Pete M22_pete.okronglis@NWA.COM31_Fri, 24 Aug 2001 12:39:59 -0500459_iso-8859-1 If it's interactive spufi, did you kill the tso id?

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Friday, August 24, 2001 12:18 To: DB2-L@RYCI.COM Subject: How to kill a thread



We have spufi that has run away on us. It has been running since Wed and is tying up more and more DB2 resources. Terminating the thread did not work. Any other suggestions short of stopping DB2? [...] 19882 43 24_Re: How to kill a thread11_Knust, Paul28_Paul.Knust@MORGANSTANLEY.COM31_Fri, 24 Aug 2001 13:53:10 -0400464_iso-8859-1 Since it is tying up resources I assume it is not a 'select' query (unless you have SPUFI bound with RR). Check your monitor to see if the thread is in rollback processing. If so, I'd hesitate to bounce DB2 - it will not complete restart processing until the rollback is complete anyway. You can use a deferred or norollback restart but then you have to analyze what has to be recovered and recover it. I hope this isn't on your production system. [...] 19926 62 24_Re: How to kill a thread0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 24 Aug 2001 13:01:14 -0500411_us-ascii Yes it was online from TSO. When I try to cancel either of the threads associated with the userid, they both say "THREAD '145915' WAS PREVIOUSLY CANCELED", but they still continue on their merry way.











Duane Lee - ATCX @RYCI.COM> on 2001/08/24 12:34:07 PM

Please respond to DB2 Data Base Discussion List [...] 19989 87 24_Re: How to kill a thread17_Okronglis, Pete M22_pete.okronglis@NWA.COM31_Fri, 24 Aug 2001 13:19:15 -0500500_iso-8859-1 A couple other folks have also asked - have you tried killing the TSO user id (MVS)?

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Friday, August 24, 2001 13:01 To: DB2-L@RYCI.COM Subject: Re: How to kill a thread



Yes it was online from TSO. When I try to cancel either of the threads associated with the userid, they both say "THREAD '145915' WAS PREVIOUSLY CANCELED", but they still continue on their merry way. [...] 20077 84 30_Recovery Point from Image Copy12_Gene Tilelli16_geetee52@USA.NET31_Fri, 24 Aug 2001 14:04:27 -0400329_iso-8859-1 Hi All -

I have the need to recover a table in a TEST system from a Production image copy in an OS390 environment.

Since I can't recover using the image copy dataset, I believe the way to do this is via a LOAD utility.

My recovery point is from about 6 image copies ago... so my question is: [...] 20162 90 24_Re: How to kill a thread0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 24 Aug 2001 13:32:13 -0500466_us-ascii Yes it is a Production subsystem, and wouldn't you know it, we have a major implementation starting tonight! Yes we tried killing the TSO session, also no luck. The spufi was done in CS not RR. The thread is in rollback, but since it ran for almost 2 days before being killed, I don't think we will wait for the rollback to finish. Right now we are just waiting for the final approval to shutdown the subsystem, and we will have to fix up the results. [...] 20253 103 24_Re: How to kill a thread50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Fri, 24 Aug 2001 14:31:58 -0400397_iso-8859-1 I believe this discussion took place about 6 weeks ago. There is an open item with IBM on this. I tried to cancel threads with token, id, etc nothing worked. It is an issue with V6.

Bob Knight

-----Original Message----- From: Okronglis, Pete M [mailto:pete.okronglis@NWA.COM] Sent: Friday, August 24, 2001 2:19 PM To: DB2-L@RYCI.COM Subject: Re: How to kill a thread [...] 20357 124 34_Re: Recovery Point from Image Copy14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Fri, 24 Aug 2001 11:32:55 -0700466_iso-8859-1 If you have BMC's Recover+, you can use the Production imagecopy as your incopy tro recover the TEST table.

I believe Platinum FastRecover will do the same.

OR, you could unload the imagecopy and use it as load data into your test table.

Good luck, DJ

-----Original Message----- From: Gene Tilelli [mailto:geetee52@USA.NET] Sent: Friday, August 24, 2001 11:04 AM To: DB2-L@RYCI.COM Subject: Recovery Point from Image Copy [...] 20482 68 34_Re: Recovery Point from Image Copy24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Fri, 24 Aug 2001 11:33:29 -0700435_us-ascii Platinum provides a tool( Fast Unload) that can unload from Image copy..

Alternately I think you can use DSN1COPY to do this. You have to use DBIXXLAT and OBIDXLAT of source and target for this.

Regards

Maha









Gene Tilelli cc: Sent by: DB2 Subject: Recovery Point from Image Copy Data Base Discussion List [...] 20551 155 34_Re: Recovery Point from Image Copy11_Knust, Paul28_Paul.Knust@MORGANSTANLEY.COM31_Fri, 24 Aug 2001 14:33:39 -0400373_iso-8859-1 I know Platinum FastUnload utility can unload from an image copy. If you don't have that, then.... 1. Use DSN1COPY with the translate obid feature , or 2. At an allowable time (if any) a. Make you prod. table access(RO) or UT b. Image copy it c. Recover the (-6) image copy in prod. d. Unload it e. Recover the (0) image copy in prod f. Load the test table [...] 20707 50 34_Re: Recovery Point from Image Copy15_Murley, Michael22_Michael_Murley@BMC.COM31_Fri, 24 Aug 2001 13:33:30 -0500478_iso-8859-1 Gene,

If the production and test tables have the same definition, you might consider using DSN1COPY with OBIDXLAT.

BMC UNLOAD PLUS can unload from an image copy. I think CA and CDB probably have products with this capabilty, too.



Michael Murley BMC



-----Original Message----- From: Gene Tilelli [mailto:geetee52@USA.NET] Sent: Friday, August 24, 2001 1:04 PM To: DB2-L@RYCI.COM Subject: Recovery Point from Image Copy [...] 20758 50 9_FW: SPT0110_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM31_Fri, 24 Aug 2001 13:18:46 -0500422_- I won't say that we haven't had problems with parallelism, but rather than setting the default back to DEGREE(1), we've opted to work with IBM to get DB2 to pick parallelism at appropriate times. We still think the concept of parallelism is a good thing, but don't have time to adjust it package by package every time the optimizer changes. On just one of our test systems, we have over 275,000 rows in SYSPACKAGE. [...] 20809 193 25_Illegal Password on SCT0213_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Fri, 24 Aug 2001 14:52:27 -0400524_US-ASCII Hi to you all,

I have two questions:

Has anyone else had a problem with passwords on a catalog or directory object?

Were you able to correct the problem by applying APAR PQ47079 and PQ38035?

Were are applying fallback maintenance to DB2 UDB for OS/390 V6.1 so we can migrate to V7. Once we applied maintenance, all production batch jobs using DB2 cursors started failing as did an Imagecopy of DSNDB01.SCT02. We were able to back the maintenance off and get everything going again. [...] 21003 107 29_Re: Illegal Password on SCT0214_Riedel, Marcia26_MRiedel@TUCSONELECTRIC.COM31_Fri, 24 Aug 2001 12:10:52 -0700445_iso-8859-1 Burgess:

I thought I had a password on a Directory object when migrating from V5 to V6 because CATMAINT blew with a RC00C900CF. IBM instructed me to do the following to find passwords:

I. to FIND PASSWORD: (with or without uq51242 on) a) if DB2: - DIAGNOSE DISPLAY OBD DSNDB01.DBD01 ALL - find the FILE PAGESET record - at offset x'1a' for 8 characters, see if that is a value other than hex zeroes x'00' or x'40'. [...] 21111 17 27_Re: DB2 security using RACF9_Jim rohal19_jrohal@NETSCAPE.NET31_Fri, 24 Aug 2001 14:26:03 -0500401_- I have used the RACF exit and like it better than db2 security.

It was easy to implement, we generated all of the racf commands using the db2 tool mentioned in the Red book on using the exit.

I no longer have to worry about performing any grants, the RACF team now controls all the access and since RACF allows wild cards usually no new authoriy is needed when objects are added. [...] 21129 26 26_DB2 Master won't come down9_Chu, Pius14_ChuP@CONED.COM31_Fri, 24 Aug 2001 15:39:32 -0400472_iso-8859-1 All my previously year I have been using "cancel IRLM" to bring down DB2 as my last resort. Last week I did the same to a test DB2 because some hanging distributed threads that "cancel thread" would not do. Well, all DB2 address spaces came down except the Master address space. I used cancel, force and even a Resolve EXIT command without any success. Bottom line, we IPL the machine. Does anyone has this experience how to kill the master address space? [...] 21156 55 30_Re: DB2 Master won't come down12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 24 Aug 2001 13:07:18 -0700425_us-ascii Sounds like you tried all the "last resorts" at your disposal. Every once in a while, you just have to IPL to get the MSTR out of there. I would never kill the IRLM or issue a FORCE or KILL(Omegamon) or EXIT command without the understanding of the Operations department, that this will "probably" work, but if it doesn't we're going to live without whatever they wanted me to kill until they scheduled an IPL. [...] 21212 138 30_Re: DB2 Master won't come down14_Philip Gunning24_philip.gunning@QUEST.COM31_Fri, 24 Aug 2001 13:13:22 -0700577_iso-8859-1 Pius, I have never had to MVS Force the DB2 Master address space. Had to do it with other products but never DB2. Usually as a result of an MVS (or Z/OS) Force, you end up having to IPL anyway. Instead of cancelling IRLM, issue ABEND IRLM instead. There are other things to look at here with your environment,like do you have batch applications that are still running, if so you need to cancel them and let them backout before shutting down, DDF settings, connection pooling, idle thread timeout, etc. I'm sure others will have additional suggestions. HTH Phil [...] 21351 129 34_Re: Recovery Point from Image Copy14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 24 Aug 2001 16:18:26 -0400430_iso-8859-1 Gene:

You will need 3rd party tools (from CA or BMC) or IBM's High Performance unload utility to be able to unload from image copy. Your other option is to use DSN1COPY with OBIDXLAT.

Manas. -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Gene Tilelli Sent: Friday, August 24, 2001 2:04 PM To: DB2-L@RYCI.COM Subject: Recovery Point from Image Copy [...] 21481 100 34_Re: Recovery Point from Image Copy16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Fri, 24 Aug 2001 13:27:40 -0700483_iso-8859-1 Why not use DSN1COPY with OBIDXLAT?

Duane -----Original Message----- From: Gene Tilelli [mailto:geetee52@USA.NET] Sent: Friday, August 24, 2001 11:04 AM To: DB2-L@RYCI.COM Subject: Recovery Point from Image Copy



Hi All -

I have the need to recover a table in a TEST system from a Production image copy in an OS390 environment.

Since I can't recover using the image copy dataset, I believe the way to do this is via a LOAD utility. [...] 21582 75 39_Re: WebSphere on AIX, WAS DB on OS/390?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 24 Aug 2001 16:38:17 -0400438_us-ascii Bill:

We run websphere 3.5.3 with the WAS repository on AIX. I have occassionally monitored SQL activity on the WAS database. The App server, especially the Admin server piece runs tonnes of SQLs against the WAS database. I have not seen any stored procedures in use by WAS either. So, my only concern would be performance degradation that could arise from running the App server in AIX and the repository in OS/390. [...] 21658 114 35_Re: indexes and predicate matching.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 24 Aug 2001 16:23:28 -0500320_iso-8859-1 Mike,

Your description is perfect if the second example was an AND, but instead it's an OR. I don't mean to be pedantic because I know this was a simple oversight rather than you not understanding. Even though it does not relate to why the <> is not indexable, it does impact your description.... [...] 21773 41 38_Re: DB2 security using RACF - Redbook?10_Hood, John13_JHood@FRK.COM31_Fri, 24 Aug 2001 14:27:25 -0700502_iso-8859-1 What is the name of the Red book that details RACF Exit?

John Hood Franklin Templeton Technologies Information Management, DB2/IMS DBA 925-875-2734 or x62734 (work), 650-848-0004 (pager), 510-882-0421 (cell) jhood@frk.com (email), SM 5130/4 (mail stop), 6503710040.8480004@pagenet.net (text page)



-----Original Message----- From: Jim rohal [mailto:jrohal@NETSCAPE.NET] Sent: Friday, August 24, 2001 12:26 PM To: DB2-L@RYCI.COM Subject: Re: DB2 security using RACF [...] 21815 70 38_Re: DB2 security using RACF - Redbook?12_Gene Tilelli16_geetee52@USA.NET31_Fri, 24 Aug 2001 17:43:27 -0400478_iso-8859-1 It sounds like what you're looking for is: Ready for e-business: OS/390 Security Server Enhancements

Here's a link to it.

http://www.redbooks.ibm.com/redbooks/SG245158.html



Gene Tilelli IMS/DB2 DBA Coral Springs, FL



----- Original Message ----- From: "Hood, John" Newsgroups: bit.listserv.db2-l To: Sent: Friday, August 24, 2001 5:27 PM Subject: Re: DB2 security using RACF - Redbook? [...] 21886 77 30_AW: DB2 Master won't come down16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 24 Aug 2001 23:47:03 +0200510_ISO-8859-1 How long did you wait and how long was DB2 up?

On some LPAR (SAP/R3) we run DB2 for 2 or 3 months and it took about 4 hours to get rid of the DB2 master. If you record some SMF type 30 it can take a long time to shutdown DB2 just to build a summary SMF 30. Unfortunally I don't remember the details yet but as far as I remember the interval detail records are move to expanded storage and to build the summary record all of these interval record have to much to central storage to build [...] 21964 59 29_UDB Optimizer (V 7.2) Smokin?10_Umesh Apte19_apteumesh@YAHOO.COM31_Fri, 24 Aug 2001 15:06:45 -0700443_us-ascii Hi,

At the outset, my apologies for the subject line. In part it is to draw your attention - in part I do feel that it is either UDB Optimizer(Version 7.2) or me - one of us is on weeds.

Here is the scenario.

table B has about 200,000 rows - table C about 300,000 rows - DB optimizer level 5, stats are up to date and same in all the three tests. These three tests if repeated give same results and timings. [...] 22024 17 17_Manuals for RXSQL8_Harish G24_hari_gangadhar@YAHOO.COM31_Fri, 24 Aug 2001 17:00:27 -0500434_- Hi,

Could some one post the links to RXSQL manuals for OS/390 (REXX/ DB2 interface prior to DSNREXX). I could not locate any such for OS/390.

Thanks and Regards,

Harish.

================================================ 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. 22042 46 21_Re: Manuals for RXSQL17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Fri, 24 Aug 2001 17:40:08 -0500494_iso-8859-1 Harish,

Check out the DB2 "Application Programming and SQL Guide"

In chapter 9 there is a section named:

"Coding SQL statements in a REXX application"

Hope this helps,

Ken Kornblum BMC Software Sr Product Developer - DB2 Utilities Austin (in the GREAT state of) Texas

-----Original Message----- From: Harish G [mailto:hari_gangadhar@YAHOO.COM] Sent: Friday, August 24, 2001 5:00 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Manuals for RXSQL [...] 22089 130 24_Re: How to kill a thread12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 24 Aug 2001 16:24:53 -0700563_us-ascii My client had the problem several times with DDF threads not being killed when stopping DDF (mode force). For what its worth, we upgraded DB2 maintenance to very very recent (all fixes up to within a month ago + plus some more recent) and since then we haven't had the problem. --- "Knight, Robert A. (Cameron of Pittsburgh at Alcoa)" wrote: > I believe this discussion took place about 6 weeks ago. > There is an open item with IBM on this. I tried to > cancel threads with token, id, etc nothing worked. > It is an issue [...] 22220 18 24_RXSQL for OS/390 manuals8_Harish G24_hari_gangadhar@YAHOO.COM31_Fri, 24 Aug 2001 18:54:32 -0500437_- Hi,

I have been trying to locate manuals for RXSQL for OS/390 (REXX/ DB2 interface prior to DSNREXX). Could someone please post any links to such documents.

Thanks,

Harish.

================================================ 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. 22239 16 21_Re: Manuals for RXSQL8_Harish G24_hari_gangadhar@YAHOO.COM31_Fri, 24 Aug 2001 19:07:05 -0500493_- That chapter deals with programming with DSNREXX interface in mind. I am looking for programming with RXSQL interface, which is different from this interface. I know that this interface will not supported in the future.

Thanks,

Harish.

================================================ 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. 22256 227 31_Re: Copy data from SSID to SSID12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 25 Aug 2001 10:41:20 +0200589_iso-8859-1 RE: [DB2-L] Copy data from SSID to SSIDHI, You can wish for it. What I meant (as a joke) is to UNLOAD in INSERT command format (e.g "select " insert into .... values ....") and run the commands against the target . (certainly good for your mips,logs, .....)

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: Grainger, Phil Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Friday, August 24, 2001 1:43 PM Subject: Re: Copy data from SSID to SSID [...] 22484 44 30_Re: SYSADM AUTHORITY - HELP!!!16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Sat, 25 Aug 2001 08:10:09 -0400461_iso-8859-1 I have been a DB2 Sysprog and occasionally a DBA as well for many years. In my last shop, the DBAs only were allowed DBADM. This meant they had to come to the DB2 sysprogs to create all their databases, views, aliases, etc. I thought it was a real hardship for them to have to wait on us DB2 sysprogs to be available to do their work. In every other place I have worked, all the senior DBAs had SYSADM, and I think this is entirely appropriate. [...] 22529 68 39_Re: WebSphere on AIX, WAS DB on OS/390?11_James Szabo18_jim.szabo@CORE.COM31_Sat, 25 Aug 2001 11:15:56 -0400408_iso-8859-1 You may also want to look at hosting your entire WebSphere server on OS/390. I am told that you can do this with WebSphere 4.0 and DB2 for OS/390 V7.

Jim Szabo

----- Original Message ----- From: "Bill Gallagher" Newsgroups: bit.listserv.db2-l To: Sent: Friday, August 24, 2001 9:56 AM Subject: WebSphere on AIX, WAS DB on OS/390? [...] 22598 94 43_Fw: Re: WebSphere on AIX, WAS DB on OS/390?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 25 Aug 2001 19:31:10 +0200561_iso-8859-1 > Hi, > > We do it and it works ok. > > Last problem we had was someone stepped over the cable going into the OSA card and the > webserv didn't want to work anymore :-) > > Isaac Yassin > DBMS & IT Consultant > IBM Certified Solution Expert > DB2 V7.1 Database Administration for OS/390 > yassini@bezeqint.net > ----- Original Message ----- > From: "James Szabo" > Newsgroups: bit.listserv.db2-l > To: > Sent: Saturday, August 25, 2001 5:15 PM > Subject: Re: WebSphere on AIX, WAS DB on OS/390? > > > > You [...] 22693 18 35_Re: AW: Copy data from SSID to SSID12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 11:53:31 -0500313_- In V7 DB2 for z/OS & OS/390 and the utilities suite or the operational utilities, there is an option to LOAD data from a cursor. That cursor can be at another site. This way you get unit of work management, committing at reasonable intervals. Sometimes we call this the DB2 family cross loader capability. [...] 22712 15 20_Re: CAF & Versioning12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 12:05:57 -0500292_- The Messages and Codes book has about one and a half pages of description on the SQLCODE -805, describing the technique we tend to use for finding out what was done almost right and SQL statements to determine the problem. The reason code is the key.

Roger Miller, DB2 for z/OS [...] 22728 20 25_Re: DB2 VERSION 6 SUPPORT12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 12:18:58 -0500538_- In addition to the pointers, we have a general rule of thumb that a version will be available for five years. So the ROT says probably until 2004. This is not a commitment, but a good guess. Today V6 has by far the largest work load. Many customers will skip from V5 to V7, so that might let us end service early, for instance. For another rule of thumb, there is generally a year between the announcement for end of service and the end of service. The service withdrawal announcements seem to be for many products and a couple of [...] 22749 56 38_Re: DB2 security using RACF - Redbook?12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 13:14:38 -0500369_- That is the first red book, describing the V5 implementation, and it has not been updated. There are significant enhancements in the support for Versions 6 and 7, and we have learned about the function and updated some of the books.

I'm trying to finish up my presentation on this topic for the DB2 Technical Conference today, so here are my suggestions. [...] 22806 31 31_Re: Federated Database System ?12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 13:52:08 -0500382_- Federated is the industry terminology for being able to access many different kinds of data, ranging from flat files to brand x databases to IMS data for instance. Our DataJoiner product built leading technology for federation, and that technology is being incorporated into DB2 for UNIX, Windows, OS/2 V7. The DB2 Connect Relation Connect is an option. Here is a web paste: [...] 22838 16 30_Re: SYSADM AUTHORITY - HELP!!!12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 25 Aug 2001 14:01:32 -0500358_- This is really helpful information about roles and responsibilities for me. The differences in security practice from company to company are huge. One of the key tasks that required SYSADM authority before DB2 V7 was the ability to create a view for other ids. In V7 there is an option to allow those with DBADM authority to create a view for others. [...] 22855 39 31_Re: Federated Database System ?11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Sun, 26 Aug 2001 08:47:45 +0700356_- Stephen,

The simplest reason why you want to consider use FDS is if like to join tables on 2 different databases (can be DB2 or others). By default w/ DB2 v.7.1, you can only connect to other DB2 and SQL Server sources. YOu have to have Relational Connect installed to be able to connect to other Oracle dbs. I'm not so sure about other dbs. [...] 22895 21 25_Re: DB2 VERSION 6 SUPPORT14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 26 Aug 2001 03:24:35 -0500374_- Apropos of this thread, I could not help but idly note that the date for service discontinuence for DB2 for Windows etc V7.2 has already been announced (as 2003/06/30), even though no replacement has yet been announced.

http://www.ibmlink.ibm.com - select United States IBMLink - select SalesManual - enter '5648-D48' as product number - select doc id 20P4233 [...] 22917 55 39_Getting info about your MVS environment12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sun, 26 Aug 2001 20:05:06 +0200917_windows-1255 Hi, During the years I've collected some commands about it. Here it is as a REXX code:

/* rexx */ say "user :" userid(); say "mvs :" mvsvar('sysmvs'); say "sysname :" mvsvar('sysname'); say "opsys :" mvsvar('sysopsys'); say "sysplex :" mvsvar('sysplex'); say "sysclone :" mvsvar('sysclone'); say "DfpLevel :" mvsvar('sysdfp'); say "SMFid :" mvsvar('syssmfid'); say "SMS is? :" mvsvar('syssms'); cvt=c2d(storage(10,4)); csd=c2d(storage(d2x(cvt+660),4)); hid=c2d(storage(d2x(cvt+x2d(42c)),4)); pccavt=c2d(storage(d2x(cvt+764),4)); do cpu=0 to 15; pcc=c2d(storage(d2x(pccavt+(cpu*4)),4)); if pcc=0 then leave; serial=storage(d2x(pcc+4),8); cpuid=storage(d2x(pcc+12),4) say "cpu :" right(cpu,2,'0') " id: " cpuid " serial: " serial end ecvt=c2d(storage(d2x(cvt+140),4)) say "CBID :" storage(d2x(ecvt+000),4) say "HD-name :" storage(d2x(ecvt+336),8) say "LPAR name :" storage(d2x(ecvt+344),8) say [...] 22973 198 25_Re: BETWEEN vs. >= and <=13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 26 Aug 2001 14:12:59 -0500385_iso-8859-1 Carlos,

My apologies since I have misled you regarding the query rewrite. There is none. Unfortunately I only partially quoted Richard. The full quote was "the following are equivalent (just call it internal query rewrite) and use the BETWEEN filter factor". What he was saying was that you could think of it as a rewrite, although it does not physically occur. [...] 23172 127 69_Free Pittsburgh DB2 Users Group Meeting - Sept 7th - Registration due10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Sun, 26 Aug 2001 20:28:16 -0400436_iso-8859-1 If you are planning to attend the upcoming FREE Pittsburgh DB2 Users Group on Sept 7th, please contact Cathy Peck at Cathy.Peck@Highmark.com. For directions, see our website http://www.db2parug.org.

Hope to see you at next week's meeting!











Pittsburgh DB2 Users Group Agenda for September 7th, 2001



8:00 to 8:45 Breakfast, Compliments of Quest Software [...] 23300 92 31_Re: Copy data from SSID to SSID15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Mon, 27 Aug 2001 16:00:16 +1100336_- I don't think you can refer to local and remote objects in the same SQL without third party products such as Datajoiner, etc.

We don't have such a product however we do sometimes cheat by copying tiny tables under QMF something like the following : select * from local.creator.tbname ; save data as remote.creator.tbname; [...] 23393 25 26_DB2DUMP, need help A.S.A.P11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Mon, 27 Aug 2001 12:14:48 +0700292_- Dear all,

A client of us has DB2 EE v.7.1 installed on an AIX box. They are using it for their Data Warehouse. For some reason that we do not know, DB2 save some large files to ..\db2dump directory. We want to know what is the directory for? Can we delete the files from there? [...] 23419 94 59_Re: Solved: CSP V3.3 and DB2 V5 / "optimize for" gives -19916_Jean-Paul Coenen19_jpc_db2@HOTMAIL.COM31_Mon, 27 Aug 2001 08:01:56 +0200474_- Hello all, This is a third try to get this mail to the list. Didn't get any response from the list server so far, and I don't see the mails appear in the archives. I did change the subject of my previous mail, but without succes. Must be something wrong with the server ? I hope I finally manage to tell you what was the simple sollution to our problem. Here is is: --------------------------------------------------- Thanks Sanjeev, Changing sequence did the trick: [...] 23514 31 26_db2 ddf threads priorities12_harbry ariza24_harbry_ariza@HOTMAIL.COM31_Mon, 27 Aug 2001 16:10:28 +1000547_- Hi Guys:

I would like to hear about which approach would be the best one to follow when assigning priorities to a DDF's threads. I've got a lot of DDF's threads most of them Ad Hoc queries and it looks likes WLM is always giving to them a lot of resources (CPU , Memory) even though I assigned them to a services class with low response time. By the way this is also something that make me confuse is the fact that I don't really know which WLM goal (Average Response Time, Response Time with percentile or Execution Velocity) would [...] 23546 74 30_Re: DB2DUMP, need help A.S.A.P0_24_markus.schaub@SYSTOR.COM31_Mon, 27 Aug 2001 09:18:10 +0200358_us-ascii hi

the \db2dump directory contains the db2diag.log file and dump- and trap-files in case of any problems the db2-instance reports. so check the db2diag.log file for the errors/problems occuring. the large dump- and trap-files belongs to problems reported to the db2diag.log and the writing of these is also mentioned in the db2diag.log. [...] 23621 92 30_Re: DB2DUMP, need help A.S.A.P11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Mon, 27 Aug 2001 14:39:29 +0700318_- Hi,

We are now facing some problem with our instancem and we've tried to read the error message created in db2diag.log. But none of us understand how to read it or what do the messages mean. Can anyone help us to determine the cause of the problem? I will send the db2diag.log through your private mail. [...] 23714 115 39_Re: WebSphere on AIX, WAS DB on OS/390?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Mon, 27 Aug 2001 10:01:09 +0200597_iso-8859-1 Bill,

We face a similar problem in datawarehousing; We'll be running Informatica's powercenter on an AIX DB2 Connect 7.1, having the source and target databases on OS/390 DB2 V6. The repository database of the Informatica tool we wanted to have on the OS/390 as well. We had the same frowned faces from IBM and Informatica when we said we wanted to pursue this goal. We also have to use DB2 Connect to get to the repository database. As we are looking into this configuration more closely, we are facing some 'nice' issues. - Probable overhead for conversion ASCII EBCDIC - [...] 23830 21 26_Need another HELP, A.S.A.P11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Mon, 27 Aug 2001 15:58:28 +0700349_- Hi y'all (again)

Is there a way of changing the parameter on a DB without having to connect to the DB itself. I usually do the paramater changing through Control Center, but because now we cannot connect to the DB, can we do it another way?

T.I.A

Endy Lambey endy@lambey.net

"Never make decision on empty stomach" [...] 23852 16 22_DB2 UDB V7.2 download.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Mon, 27 Aug 2001 04:03:43 -0500445_- Hi all,

I have FTPed limited hours version of UDB 7.2 zip file. When I try extracting I get an error msg of 'cannot open file'. I have a NT workstation. Could any of you kindly help.

TIA.

================================================ 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. 23869 17 49_Jose Gomez/Corporate/Wendys is out of the office.11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Mon, 27 Aug 2001 08:01:32 -0400513_us-ascii I will be out of the office starting 08/25/2001 and will not return until 08/30/2001.

I will be at the PeopleSoft 2001 Conference, I will respond to your message by the end of the day. I can also be reached at my pager in case of an emergency 614-520-0839

================================================ 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. 23887 149 29_Re: Illegal Password on SCT0213_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Mon, 27 Aug 2001 08:33:17 -0400392_US-ASCII Marcia:

Thank you for the information. I will check my Diagnose output again and go from there.

Thank you, Burge

>>> MRiedel@TUCSONELECTRIC.COM 08/24/01 03:10PM >>> Burgess:

I thought I had a password on a Directory object when migrating from V5 to V6 because CATMAINT blew with a RC00C900CF. IBM instructed me to do the following to find passwords: [...] 24037 82 29_Re: Illegal Password on SCT0212_Claude Birtz24_Claude.Birtz@CIE.ETAT.LU31_Mon, 27 Aug 2001 15:03:19 +0200436_us-ascii We had the same problem than you, Diagnose showed passwords on DBD01, SCT02 and on the index on SCT02. I was able to remove the password with a repair, thanks to indications from our benelux IBM support, if you're interested in the procedures and jobs I used you can contact me. We haven't reapplied the fallback maintenance yet, so I can't tell you if this manipulation has solved the problems with reason code 00C900CF. [...] 24120 42 32_DISTINCT in Select stmt / DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 27 Aug 2001 09:24:42 -0400530_iso-8859-1 I know this is an open question but here goes; We just upgraded from DB2 os/390 V5 to V6. I have a query that is running against a VIEW. The view was created as a CREATE VIEW ABC SELECT DISTINCT and then multiple column names with Inner joins and Left Outer joins. Now in DB2 V5 they do a specific select against this view and it returns the result set, no problem. When we migrated to V6 when they run the same 'select stmt' against the View NO rows are returned. Nothing in the 'select' stmt has changed and the [...] 24163 75 36_Re: DISTINCT in Select stmt / DB2 V613_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 27 Aug 2001 09:00:56 -0500324_iso-8859-1 Steve,

They should work exactly the same. There is most likely an APAR out there to fix this. I did a quick search and found: "PQ36576: A JOIN OVER TWO TABLES DOES NOT RETURN ANY ROWS WHEN DISTINCT AND ORDER BY CLAUSE ARE IN EFFECT.". May not be exactly your problem since you don't mention ORDER BY. [...] 24239 35 84_IBM DB2 for z/OS and OS/390, Version 7 - Upgrade Transition Price from DB2 Version 512_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 27 Aug 2001 16:11:14 +0200487_iso-8859-1 Hello friends and collegues,

for those of you who are on DB2 for OS/390 Version 5 and thinking about skipping Version 6 and going directly to Version 7 I have found an IBM software announcement that may be interesting for you:

"August 14, 2001 IBM DB2 for z/OS and OS/390, Version 7 - Upgrade Transition Price from DB2 Version 5 A special transition price is now available for a limited time for customers who are upgrading from DB2 for OS/390, Version 5." [...] 24275 104 36_Re: DISTINCT in Select stmt / DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 27 Aug 2001 10:24:19 -0400363_iso-8859-1 Terry: Thanks..we are pretty current with our maintenance. We're sitting at PUT 0105 right now.. There's no 'order by' anywhere that I can see. Just got me baffled for now... The client says it worked fine with DB2 V5 and I have no way to disprove that or not believe them, but sure enough it does not return any rows with Version 6... oh well... [...] 24380 51 24_DB2 6.1 query monitoring19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Mon, 27 Aug 2001 17:27:23 +0200255_iso-8859-7 Hi all, I want to find the access plan for a query generated dynamically from an application. Although I know how to find the plan for a known commnad I don't know how to find the path for dynamic queries. Any help is appreciated.

Thanks 24432 18 28_Re: DB2 6.1 query monitoring15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 27 Aug 2001 09:43:39 -0500358_- Dimitris

There is no access path information in a plan table unless you take the SQL and do an explain by your own. Then you can see, which access path the optimizer will take, if you run your query. But access path can change, if the query would be executed the next time, because it is dynamic and access path can change for every execution. [...] 24451 44 30_Re: Need another HELP, A.S.A.P14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 27 Aug 2001 10:48:35 -0400391_iso-8859-1 You can telnet to the box and issue 'update db cfg for dbname using ....' or 'update dbm cfg using ' depending on what parameters you want to change.

Manas.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Endy Lambey Sent: Monday, August 27, 2001 4:58 AM To: DB2-L@RYCI.COM Subject: Need another HELP, A.S.A.P [...] 24496 110 46_Re: SYSADM AUTHORITY - HELP!!! - Unix addendum14_Jeffrey Benner29_Jeffrey_Benner@NOTES.NTRS.COM31_Mon, 27 Aug 2001 10:18:16 -0500395_us-ascii Under UDB Unix the only way to define SYSADM, SYSMAINT, or SYSCTRL authority is by establishing a single Unix group or user with an ID which is then defined for the instance as having that authority over all subordinate databases. So, under Unix the only practical way to do this is to define individual groups for SYSADM, SYSMAINT, and SYSCTRL and then add users to those groups. [...] 24607 79 29_Re: Illegal Password on SCT0214_Riedel, Marcia26_MRiedel@TUCSONELECTRIC.COM31_Mon, 27 Aug 2001 08:40:28 -0700419_- Burgess:

I hope this helps. Let me know if you have any more questions.

Marcia

-----Original Message----- From: Burgess Evans [SMTP:BMEVANS@AUDITOR.STATE.OH.US] Sent: Monday, August 27, 2001 5:33 AM To: DB2-L@RYCI.COM Subject: Re: Illegal Password on SCT02

Marcia:

Thank you for the information. I will check my Diagnose output again and go from there.

Thank you, Burge [...] 24687 114 36_Re: DISTINCT in Select stmt / DB2 V613_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 27 Aug 2001 10:43:33 -0500309_iso-8859-1 Steve,

If you run the query then with the DISTINCT and without.....and without gives you less rows, then you have a bug. You would expect DISTINCT to give you less since it removes duplicates, but not the other way around.

That's at least a check you can do on V6. Regards Terry [...] 24802 9 11_Unsubscribe13_Hopkins, John20_JMHopkins@KFORCE.COM31_Mon, 27 Aug 2001 12:11:39 -0400244_-

================================================ 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. 24812 18 12_WLM Problem!11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 27 Aug 2001 11:31:05 -0500312_us-ascii We are OS/390 2.9 DB2 6.1 WLM When we submit two batch jobs one sits there in a waiting (a stored procedure using DSNUTILS status in application) the other runs okay using 30% of the CPU (DSNUTILB status in DB2). GRS shows it is not waiting for anything! Does any have any ideas for what to check? [...] 24831 43 16_Re: WLM Problem!12_McKown, John22_JMckown@HEALTHAXIS.COM31_Mon, 27 Aug 2001 11:44:39 -0500420_- Well, just as a SWAG, I'd say that the first job is getting a DB2 lock (on the table, or tablespace?) and the second job is waiting on it.

---------------------------------------------------------------------- John McKown HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to upset my management. [...] 24875 78 16_Re: WLM Problem!11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 27 Aug 2001 12:08:57 -0500515_us-ascii No! They access different tables in different DB... : )







"McKown, John" cc: Sent by: DB2 Subject: Re: WLM Problem! Data Base Discussion List



08/27/01 11:44 AM Please respond to DB2 Data Base Discussion List











Well, just as a SWAG, I'd say that the first job is getting a DB2 lock (on the table, or tablespace?) and the second job is waiting on it. [...] 24954 37 16_Clever Optimizer14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 27 Aug 2001 19:28:29 +0200305_us-ascii One of my clients (using DB2/390 V6) coded this not so clever WHERE condition

WHERE COL1 >= :hv1 AND COL1 = :hv2 AND COL2 >= :hv3 AND COL2 = :hv4

As the Explain showed a matching index scan on (COL1, COL2) apparently the Optimizer ignored the redundant unequal!

Peter

24992 33 12_EXPLAIN this14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 27 Aug 2001 19:32:41 +0200376_us-ascii When I do a "REBIND EXPLAIN(YES)" on a package, I get a Multiple Index Scan. If I do an "EXPLAIN ALL FOR ..." for the same statement, replacing the host variables with ?-marks, I get a different access path!

How on earth can I test the effect of adding indexes and so on, when there is no guarantee that I get the same access path for the EXPLAIN and the BIND? 25026 71 16_Re: EXPLAIN this24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Mon, 27 Aug 2001 10:56:03 -0700419_us-ascii Do you have any host variable hardcoded in your program which you are rebinding and have ? for the same in the explain, then this could happen









Peter Backlund To: DB2-L@RYCI.COM Subject: EXPLAIN this Sent by: DB2 Data Base Discussion List



08/27/01 10:32 AM Please respond to DB2 Data Base Discussion List [...] 25098 31 33_wildcards in Cobol host variables10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Mon, 27 Aug 2001 11:24:05 -0700388_iso-8859-1 I just wanted to find out how wildcarding worked using Cobol Host variables. I seem to recall someone saying that you needed to fill up the whole host variable with percent signs instead of 1 just like in Spufi. How would that work if you were looking for a string in between a string and didn't know how many other characters were in between to put in the percent signs? [...] 25130 48 23_SECQTY space allocation16_Chris Mandeville19_cmandev@INDIANA.EDU31_Mon, 27 Aug 2001 13:15:33 -0500385_US-ASCII On DB2 Version 5 for OS390, I executed DDL for a table and index with the following space specifications:

Table: PRIQTY 216000 SECQTY 216000

Index: PRIQTY 360000 SECQTY 360000



After running the DDL, here are the actual numbers that DB2 used:

Table: PRIQTY : 216,000K SECQTY : 131,068K

Index: PRIQTY : 360,000K SECQTY : 131,068K [...] 25179 64 27_Re: SECQTY space allocation16_Schuchard, Peter34_Peter.Schuchard@STARWOODHOTELS.COM31_Mon, 27 Aug 2001 14:35:46 -0400420_ISO-8859-1 from v5 sql reference If the page size is 4KB and integer is greater than 131068, then n is 131068



-----Original Message----- From: Chris Mandeville [mailto:cmandev@INDIANA.EDU] Sent: Monday, August 27, 2001 2:16 PM To: DB2-L@RYCI.COM Subject: SECQTY space allocation



On DB2 Version 5 for OS390, I executed DDL for a table and index with the following space specifications: [...] 25244 80 27_Re: SECQTY space allocation9_Ray Janes26_ray.h.janes@EXXONMOBIL.COM31_Mon, 27 Aug 2001 13:41:03 -0500389_us-ascii SQTY is defined as SMALLINT. You just found the biggest number you can put in it. V6 has SECQTYI for integer.





Chris Mandeville To: DB2-L@RYCI.COM Subject: SECQTY space allocation Sent by: DB2 Data Base Discussion List





08/27/01 01:15 PM Please respond to DB2 Data Base Discussion List [...] 25325 70 27_Re: SECQTY space allocation14_MIKE FRIEDRICH27_RDBA002@REVENUE.STATE.IL.US31_Mon, 27 Aug 2001 13:45:22 -0500547_US-ASCII Chris,

The maximum SECQTY is 131,068 for a 4K page. See the SQL reference page 279 under CREATE INDEX.

Mike



Michael Friedrich Illinois Department of Revenue Technical Support - Database Administration IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for UNIX, Windows & OS/2 MFriedrich@Revenue.State.IL.US

>>> Chris Mandeville 08/27/01 01:15PM >>> On DB2 Version 5 for OS390, I executed DDL for a table and index with the following space specifications: [...] 25396 88 20_Re: Clever Optimizer13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 27 Aug 2001 13:34:24 -0500413_us-ascii Peter,

All the explain has told you is that DB2 chose a matchcols 2 rather than matchcols 1. Consider 3 of the possible access path alternatives:

Option 1 COL1 >= :hv1 Matchcols 1 (Range predicate), 3 index screening predicates (1 range, 2 equals)

Option 2 COL1 = :hv2 AND COL2 >= :hv3 Matchcols 2 (1 equals, 1 Range predicate), 2 index screening predicates (1 range, 1 equals) [...] 25485 196 27_Re: SECQTY space allocation14_John Strymecki21_jstryme@BELLSOUTH.NET31_Mon, 27 Aug 2001 14:39:37 -0400498_iso-8859-1 Hi Chris,

1) The maximum SECQTY that can be allocated is 131,068. . . .It is documented, I forget where. . .Sorry

2) Yes, this is also normal.

I'm sorry, I don't remember where both situations are documented, but the above is normal operation. . .

Thanks! John Strymecki



-------Original Message-------

From: DB2 Data Base Discussion List Date: Monday, August 27, 2001 02:34:58 PM To: DB2-L@RYCI.COM Subject: SECQTY space allocation [...] 25682 57 37_Re: wildcards in Cobol host variables24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 27 Aug 2001 11:52:06 -0700319_- Hi Vince!

You're almost there. All you need to do is to pad the rest of the COBOL host variable with %. Your code would be where NAME like :host-variable, and you would move one character at a time, the values 'D%,%JOHN%', and continue moving % to each subsequent value until the host variable is filled. [...] 25740 79 27_Re: SECQTY space allocation19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Mon, 27 Aug 2001 14:43:02 -0400558_iso-8859-1 From the manual:

SECQTY integer Specifies the minimum secondary space allocation for a DB2-managed data set.The secondary space allocation is at least n kilobytes,where n is the value of integer except in these cases: If the page size is 4KB and SECQTY and PRIQTY are omitted, then n is 12 If the page size is 32KB and SECQTY and PRIQTY are omitted, then n is 96 If the page size is 4KB and integer is greater than 131068,then n is 131068 If the page size is 32KB and integer is greater than 131040,then n is 131040 If integer is 0,no [...] 25820 117 27_Re: SECQTY space allocation45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Mon, 27 Aug 2001 15:00:08 -0400474_iso-8859-1 The actual value stored in the SECQTY smallint field is 32767, which represents the number of 4k pages.

Thanks, André LeBlanc ICS/Across Database



-----Original Message----- From: Ray Janes [mailto:ray.h.janes@EXXONMOBIL.COM] Sent: August 27, 2001 2:41 PM To: DB2-L@RYCI.COM Subject: Re: SECQTY space allocation



SQTY is defined as SMALLINT. You just found the biggest number you can put in it. V6 has SECQTYI for integer. [...] 25938 165 27_Re: SECQTY space allocation9_Ray Janes26_ray.h.janes@EXXONMOBIL.COM31_Mon, 27 Aug 2001 14:04:55 -0500466_iso-8859-1 Thanks, you are correct





"LeBlanc, André-ITB" cc: Sent by: DB2 Data Base Subject: Re: SECQTY space allocation Discussion List





08/27/01 02:00 PM Please respond to DB2 Data Base Discussion List









The actual value stored in the SECQTY smallint field is 32767, which represents the number of 4k pages. [...] 26104 98 16_Re: EXPLAIN this14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 27 Aug 2001 21:19:04 +0200593_us-ascii The SQL Statement has a combination of host variables and literals.

I replace the host variables with ? marks and leave the literals as is

and get different access paths - confusing!

Peter

Mahadevan Krishnamoorthy wrote:

>Do you have any host variable hardcoded in your program which you are >rebinding and have ? for the same in the explain, then this could happen > > > > > > Peter > Backlund To: DB2-L@RYCI.COM > LOBAL.NET> Subject: EXPLAIN this > Sent by: DB2 > Data Base > Discussion > List > OM> > > > [...] 26203 63 37_Re: wildcards in Cobol host variables12_David Zelmer28_David.Zelmer@I-STRUCTURE.COM31_Mon, 27 Aug 2001 13:16:57 -0600336_iso-8859-1 If one were to RTM, they would find the correct procedure is to set up the host variable as a variable length string. I think this is in the Appl Pgming Guide.

01 HV. 49 HV-LEN PIC S9(04) COMP. 49 HV-TEXT PIC X(99).

Move 'D%,%JOHN%' to HV-TEXT. Move 9 to HV-LEN. EXEC SQL .... WHERE name-col LIKE :HV .... [...] 26267 32 7_Logging7_db user20_dbuser75@HOTMAIL.COM31_Mon, 27 Aug 2001 19:26:29 +0000502_- Hello !

I have a question for the list. For DB2/S390, Admin. Guide gives a list of information 'Logged' by the DB2 log. It states that insertand delete INDEX entries are logged.

Could someone tell me why INDEX entries are logged? My understanding was that only DATA entries should be logged because till V5,log cannot be used to Recover indexes. Whenever you recover indexes, indexesactually get 'Rebuilt' from the data. So, what is DB2 trying to achieve by logging index entries. [...] 26300 24 26_Re: UDF story - OS/390 DB213_Venkat Pillay20_venkat_pillay@ML.COM31_Mon, 27 Aug 2001 14:24:04 -0500489_- Issac

It sounds like LE overheads. I was running a program with tons of working storage variables in CICS the other day and observed huge overhead in calling those programs. Using CEEUOPT helped me reduce the overhead by at least 35%.

There are many LE run time parameters, which could reduce overhead drastically (If LE is an issue), since UDF runs under stored procedure address space you could use CEEUOPT just for your UDF's (without affecting the entire region). [...] 26325 124 16_Re: EXPLAIN this14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 27 Aug 2001 15:36:07 -0400381_US-ASCII Peter:

It is quite possible to get different access path using ? vs using host variables. When you use ? and explain in SPUFI DB2 assumes that the '?' is of right data-type and length. However if your host variable definition in the working-storage is not a compatible data-type or of larger length for char data types, DB2 makes that predicate non-indexable. [...] 26450 131 16_Re: EXPLAIN this13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 27 Aug 2001 14:31:12 -0500465_us-ascii Peter,

Check the bind options, such as DEGREE. This could differ between the program bind, and the dynamic. Then ensure you are binding against the same database (I guess you've already checked this).

If everything is the same, then log a problem with IBM.

Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...] 26582 15 51_John Cameron/MSI-CORP/MSI-INC is out of the office.0_26_JCameron@MSI-INSURANCE.COM31_Mon, 27 Aug 2001 14:46:45 -0500389_us-ascii I will be out of the office starting 08/27/2001 and will not return until 09/06/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. 26598 79 37_Re: wildcards in Cobol host variables10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Mon, 27 Aug 2001 13:00:51 -0700478_iso-8859-1 Fritz,

So it wouldn't matter then that there was only 1 percent sign after the 'D' for the last name? All last names starting with 'D' would still get selected but the whole string just needs to be padded with percent signs?

Thanks

Vince

-----Original Message----- From: Leblanc, Francis C - CNF [mailto:Leblanc.Francis@CNF.COM] Sent: Monday, August 27, 2001 11:52 AM To: DB2-L@RYCI.COM Subject: Re: wildcards in Cobol host variables [...] 26678 76 37_Re: wildcards in Cobol host variables13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Mon, 27 Aug 2001 15:07:45 -0500560_iso-8859-1 One thing to be a little careful with (using either the variable-length string or padded '%') is that the query may give you more than you want. In your example you asked for everyone with a last name starting with "D" and a first name of "JOHN", but you also mentioned that middle name is also concatenated into this column with the pattern "LAST, FIRST MIDDLE" (only delimiter "," between LAST and FIRST name). By using a LIKE with a wildcard, anyone with a MIDDLE name of "JOHN" would also be returned (in addition to anyone with a first or [...] 26755 32 15_IDUG conference12_Juergen Glag21_jglag@GLAG-CONSULT.DE31_Mon, 27 Aug 2001 22:16:05 +0200304_Windows-1252 Dear all, as a member of the planning committee I would like to remind you of one of the premier DB2 events in Europe: The IDUG conference. This year it takes place in Florence, Italy, from October, 8 until October, 11. All information about the conference is provided at www.idug.org. [...] 26788 58 11_Re: Logging12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Mon, 27 Aug 2001 15:32:34 -0500623_us-ascii I don't know for sure, but I think it is to store "undo" data incase of a rollback.

Best regards, John P. Higgins Performance Specialist SAP BASIS Team

Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098

> -----Original Message----- > From: db user [SMTP:dbuser75@HOTMAIL.COM] > Sent: Monday, August 27, 2001 14:26 > To: DB2-L@RYCI.COM > Subject: Logging > > Hello ! > > I have a question for the list. For DB2/S390, Admin. Guide > gives a list of > information 'Logged' by the DB2 log. It [...] 26847 31 39_Downloading APARs - DB2 UDB V7.1 on AIX14_Marshall Fuqua19_marcfuqua@YAHOO.COM31_Mon, 27 Aug 2001 14:03:29 -0700364_us-ascii I've identified an APAR (IY17104) that I'd like to apply to our DB2 UDB V7.1 on AIX environment. The APAR is included in Fixpak 3, however, I'd rather not apply FP 3 just yet for a variery of reasons.

My question - is it possible to download & apply only APAR IY17104 (or any APAR, for that matter) without downloading & applying all of FP 3? [...] 26879 98 37_Re: wildcards in Cobol host variables24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 27 Aug 2001 14:20:03 -0700569_- That's true Vince. The % sign indicates 0 or more characters.

> -----Original Message----- > From: Vinson Lee [SMTP:vlee@SDCCD.CC.CA.US] > Sent: Monday, August 27, 2001 1:01 PM > To: DB2-L@RYCI.COM > Subject: Re: wildcards in Cobol host variables > > Fritz, > > So it wouldn't matter then that there was only 1 percent sign after the > 'D' > for the last name? All last names starting with 'D' would still get > selected but the whole string just needs to be padded with percent signs? > > Thanks > > Vince > > -----Original Message----- > From: Leblanc, [...] 26978 56 11_Re: Logging14_Andy Lankester22_andyl@LANK.DEMON.CO.UK31_Mon, 27 Aug 2001 22:15:59 +0100337_us-ascii Raj,

It's to enable backout to work. Index and data updates both log before/after data. It's also now used to enable index recovery from index image copies as opposed to rebuild.

Andy Lankester ex BMC Software and now available! Contact me on andyl@lank.demon.co.uk +44 (0)20 8876 1370 or +44 (0)77 682 57976 [...] 27035 79 35_Re: Calling all SQL Guru's.........10_Shery Hepp17_schepp@SRPNET.COM31_Mon, 27 Aug 2001 14:40:54 -0700496_iso-8859-1 I've done some additional testing using both CASE and UNION. Unfortunately UNION is the one that returns the data in the desirable format. Does anyone know how I can achieve the same output results using something other than UNION since this query will be a view? I also suggested to the developer having 2 views one with detail and the other with summary and have the tool do the union via a select- but the reporting tool can't generate any sql- therefore I'm back at square 1. [...] 27115 149 36_Re: DISTINCT in Select stmt / DB2 V625_Robinson, Peter [IBM GSA]33_Peter.J.Robinson@TEAM.TELSTRA.COM31_Tue, 28 Aug 2001 10:57:46 +1100419_iso-8859-1 Hi Steve

A long shot but if using DISTINCT produces an an access path that uses an index, and that is out of sync with the data, you could get odd results.

Check the access paths of the queries with and without the distinct. If there is an index that is used for one and not the other, try recovering it. Maybe something was not completed properly in the V5 to V6 conversion. Worth a try. [...] 27265 27 12_DB2 V7 OS39017_Stephenson, Staci28_Staci.Stephenson@ACS-INC.COM31_Mon, 27 Aug 2001 19:14:41 -0500453_iso-8859-1 Hi, We have a client that's considering migrating from V5 to V7 (OS390). I've reviewed the past postings in the listserv archives and the migration itself appears relatively painless. Does anyone have any thoughts (positive or negative) on the stability of V7 at this time? We ran into more problems that usual with V6.1, and I'd like to know if we're in for the same ride with V7 so we can step up our maintenance schedule accordingly. [...] 27293 46 43_SOUTHEASTERN FEDERATED DATABASE VENDOR FAIR13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Mon, 27 Aug 2001 18:37:21 -0700477_us-ascii Hello Vendor Subscribers,

Let me know directly if your company might be interested in the following, and if so who is the contact person, and I'll see that the complete vendor package is sent to them.

Where: Charlotte, NC area When: November 29,2001 Theme: Improving Performance in a Federated Database Environment

Hosted by THE JOINT EFFORTS OF: The Southeastern Inter-Relational Database User Group (SIRDUG) The Charlotte Oracle User Group [...] 27340 15 52_Terry Gawlas/ISG/CORP/Highmark is out of the office.12_Terry Gawlas25_terry.gawlas@HIGHMARK.COM31_Tue, 28 Aug 2001 01:08:38 -0400411_us-ascii I will be out of the office starting 08/27/2001 and will not return until 09/04/2001.

If you have question on CLAS, please contact Linda Etter at X23804.

================================================ 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. 27356 95 22_Re: Partition by Date?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 28 Aug 2001 11:22:41 +0530490_- Hayden,

I think you understood what i was trying to say. In that solution, you have to create the separate column and i understand the difficulty you have in creating that column. I cann't suggest any better solution that this. But i went thru' the recent article from Robert in DB2 Mag yesterday night. I would advice you to look into the same, it describes the similar situation as yours. You can follow the URL http://www.db2mag.com/db_area/archives/2001/q3/db2dba.shtml . [...] 27452 40 28_Re: DB2 6.1 query monitoring19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Tue, 28 Aug 2001 09:27:00 +0200431_iso-8859-7 Hi, The problem is that I don't know the select statement to do manually an explain because I haven't the application code. I'm looking for a way to monitor the SQL statements that application send to DB2 server.

Thanks

-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: Monday, August 27, 2001 4:44 PM To: DB2-L@RYCI.COM Subject: Re: DB2 6.1 query monitoring [...] 27493 39 19_AW: IDUG conference12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 28 Aug 2001 08:43:16 +0200403_iso-8859-1 Guten Morgen, Jürgen.

Ein Reminder so kurz vor der Konferenz ? Habt Ihr (das CPC) noch nicht genug Anmeldungen oder läuft es dieses Jahr (mit dieser Superlocation) gut ?

BTW: Ich selbst bin dieses Jahr nicht dabei, da ich genau zu dieser Zeit beim Bergwandern im Rif-Gebirge (Marokko) bin.

Viel Erfolg bei der Konferenz... und bis man sich mal wieder sieht......... [...] 27533 18 11_Re: Logging15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Tue, 28 Aug 2001 09:11:47 +0200526_us-ascii Isn't this for use with the new Image Copy of Indexes ?

Just a guess ..

Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be those of Sanlam, to whom no liability shall attach whatsoever.

================================================ 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. 27552 136 34_Re: Query Large denormalized Table16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 28 Aug 2001 13:32:01 +0530398_iso-8859-1 Hi Alan,

You have provided enough details but I don't think the things are that simple at your end, otherwise you could have used the query below.

SELECT DISTINCT * FROM TABLE WHERE ORGE_DUN_N IN ( SELECT ORGE_PRNT_HQ_DUN_N FROM TABLE WHERE ORGE_PRNT_HQ_DUN_N IN ( SELECT ORGE_DUN_N FROM TABLE GROUP BY ORGE_PRNT_HQ_DUN_N HAVING COUNT(*) > specified number of times)) [...] 27689 29 27_Re: SECQTY space allocation10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 28 Aug 2001 10:02:05 +0200444_us-ascii 1) As other estimeed listers said, max SECQTY is 4k * 32767 = 131068.

2) As regards the use of PRIQTY when extending in a new volume if your LDS are SMS-managed it depends on the

SMS installation defaults. By default if a dataset extend to a new volume the space allocated is equal to primary quantity. Somewhere (I don't remeber where) there's the possibility to specify the use of PRIMARY or SECONDARY quantity if [...] 27719 113 23_Re: AW: IDUG conference10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 28 Aug 2001 10:06:02 +0200533_iso-8859-1 Hi Georg..

Even if I didn't understand anything, I agree with you......Just kidding !!

Regards

Max Scarpa Useless DB2 sysprog (who ignores german language) Diamonds Platinum Gold Tin certified ISO 696901 plumber















"Peter, Georg" To: DB2-L@RYCI.COM Subject: AW: IDUG conference Sent by: DB2 Data Base Discussion List



28/08/01 08.43 Please respond to DB2 Data Base Discussion List [...] 27833 23 23_AW: AW: IDUG conference12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 28 Aug 2001 10:15:55 +0200415_iso-8859-1 Dear collegues: I apologize for the inconvenience because I have sent a mail in german language.

The mail was thought for Jurgen Glag. I made an error because I do not changed the e:mail address.... May be it was to early in the morning.............. ;-))

With kind regards - mit freundlichen Grüssen, Georg H. Peter ------------------------------------------------------------------- [...] 27857 41 70_Re: FW: New Book : DB2 UDB for z/OS and OS/390 V7 Certification G uide27_Ruarus, Wim WH SITI-GRIE-1225_Wim.H.Ruarus@IS.SHELL.COM31_Tue, 28 Aug 2001 10:25:07 +0200435_iso-8859-1 Don,

I have asked IBM when this book would be available and they answered me you can order it at Fatbrain. The ISBN number is 0130650765. http://www.fatbrain.com

Regards,

Wim Ruarus



-----Original Message----- From: Donald A Smith [mailto:dsmithj@US.IBM.COM] Sent: 24 August 2001 15:29 To: DB2-L@RYCI.COM Subject: Re: FW: New Book : DB2 UDB for z/OS and OS/390 V7 Certification Guide [...] 27899 52 28_Re: RXSQL for OS/390 manuals9_Chris Tee23_chris.tee@UK.ZURICH.COM31_Tue, 28 Aug 2001 09:53:04 +0100588_us-ascii Harish,

Try http://publibz.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/ARIMRR10/CCONTENTS

Chris





Harish G cc: Sent by: DB2 Subject: [DB2-L] RXSQL for OS/390 manuals Data Base Discussion List



25/08/01 00:54 Please respond to DB2 Data Base Discussion List











Hi,

I have been trying to locate manuals for RXSQL for OS/390 (REXX/ DB2 interface prior to DSNREXX). Could someone please post any links to such documents. [...] 27952 28 15_index lookaside18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 28 Aug 2001 14:45:29 +0500316_- Dear listers Index lookaside is reset at commit processing. But is it reset at commit processing even if the package is bound with RELEASE (DEALLOCATE)?. Does index lookaside not behave the same way as IPROC, which does not get destroyed at commit processing if the package is bound with RELEASE (DEALLOCATE) [...] 27981 28 15_index lookaside18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 28 Aug 2001 14:48:17 +0500323_- Esteemed listers Index lookaside is reset at commit processing. Does it get reset at commit processing despite the fact that a package is bound with RELEASE (DEALLOCATE)?. Does it not behave the same way as IPROC which does not get destroyed at commit processing if the package is bound with RELEASE (DEALLOCATE) ?. [...] 28010 77 19_Re: index lookaside16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 28 Aug 2001 15:17:15 +0530719_- Index Lookaside cache is not reset if the package/plan is bound with RELEASE(DEALLOCATE).You need "The Woes of Commitment" by Boonie Baker in http://www.db2mag.com/db_area/archives/1998/q3/98fprog.shtml . Regards Sanjeev IBM Certified Solutions Expert DB2 V7.1 Database Administration OS/390

> -----Original Message----- > From: Pudukotai, Nagaraj [SMTP:Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM] > Sent: Tuesday, August 28, 2001 3:18 PM > To: DB2-L@RYCI.COM > Subject: index lookaside > > Esteemed listers > Index lookaside is reset at commit processing. Does it get reset at > commit > processing despite the fact that a package is bound with RELEASE > (DEALLOCATE)?. Does it not behave the same way as IPROC [...] 28088 17 28_Re: DB2 6.1 query monitoring15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Tue, 28 Aug 2001 04:38:30 -0500282_- Well, I don't know if you have a monitor available like CANDLE. In this monitor you can start an application trace for the plan in question, then ask the user to start a request. In the application plan you can see the miniplan generated by DB2 with the explain information. [...] 28106 22 37_Parititioned tablespace with UDB/UNIX32_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Tue, 28 Aug 2001 12:54:02 +0300526_- Hi all, I want to know if there is a way to partition a table on a UDB/UNIX installation like the way on DB2 OS390? We have a situation where we want to replicate some tables on the UNIX environment. Data will be refreshed on daily or monthly basis. One table daily and one monthly. On the host we designed partitioned tables thus the data to be refreshed is LOAD REPLACEd into the suitable partition of the table. But I could not find a similar partitioning method using partitioning index ranges on UDB/UNIX manuals. [...] 28129 84 19_Re: index lookaside18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 28 Aug 2001 15:52:14 +0500594_- Sanjeev Thanks for that. I have a program which is bound with RELEASE (DEALLOCATE) and it contains a driving cursor which feeds a singleton select. Data is fed to the singleton select in the clustering sequence of the table in its from clause. This switches on dynamic prefetch and the programs runs pretty fast (chewing a considerable amount of CPU) and finishes in about 20 minutes. But when I commit after every FETCH of the driving cursor (instead of 100 FETCHES) the programs runs for about 2 hours, despite the fact that the package is bound with RELEASE(DEALLOCATE). Any thoughts [...] 28214 146 19_Re: index lookaside16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 28 Aug 2001 16:18:57 +0530415_iso-8859-1 Is the cursor declared WITH HOLD or you are in the OPEN FETCH CLOSE cycle. I am sure you must be having it WITH HOLD in this kind of design. And as far as one fetch is concerned neither IPROC/UPROC, Index lookaside or dynamic prefetch would be triggered. It takes some successive fetches to do that as it has to decide the pattern. I hope you have gone thru' the article of Boonie Baker i had sent. [...] 28361 153 19_Re: index lookaside13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 28 Aug 2001 03:43:17 -0700312_us-ascii Nagaraj,

If I remember one of Bonnie Baker's "Things I wish..." correctly, that could be explained by DB2 creating the SPROC. From my memory, it builds the SPROC after the 3rd execution of the SQL statement, and deletes it after a commit. The break even point is 8 executions of that SPROC. [...] 28515 32 19_Re: index lookaside10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 28 Aug 2001 12:47:24 +0200374_us-ascii From G. Wiorkowsky manuals:

"The saved identifiers (in the CUB, my note) are reset each time that a commit work is issued when the plan or

package is bound with RELEASE(COMMIT). We recommend RELEASE(DEALLOCATE) to maximize the benefits of

index lookaside and to gain other performance benefits"

HTH & regards Max Scarpa DB2 sysprog [...] 28548 174 19_Re: index lookaside18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Tue, 28 Aug 2001 16:36:47 +0500550_iso-8859-1 Sanjeev, Tink and Max, Thank you very much for your responses. I did go through the article sent by Sanjeev (Bonnie Baker's in DB2 magazine). The cursor is declared with WITH HOLD. As I said in my mail I issue a commit after 100 FETCHES of driving cursor in which case dynamic prefetch seems to be switched on because I can see the CPU being consumed quite heavily (about 20 -25 % of the box). But when I change the commit frequency to 1 from 100 the dynamic prefetch activity seems to be getting switched off (the CPU consumption is [...] 28723 90 37_Re: wildcards in Cobol host variables16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Tue, 28 Aug 2001 12:17:00 +0100552_us-ascii Yep I subscribe to this too (the original statements had me veeeeery confused).

In a previous lifetime as an application programmer, one of the most yukky things I had to do was a horrible decoding program for users (= bank clerks used to Windows), where I had to strip their "*" signs and put just the one % at the beginning or at the end of the string if present (leaving the exact number in the middle, to build up my dynamic SQL to (basically) kill all access to a very large table because the contracts dept. could not bother [...] 28814 82 28_Re: DB2 6.1 query monitoring16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Tue, 28 Aug 2001 12:20:50 +0100398_us-ascii Dimitris,

if you have a monitoring tool for your DB2 subsystem (like Candle's Omegamon II for DB2), you'll find that you have the possibility to start application traces and such like, together with a view of the miniplan generated.

Alternatively, you'll have to set an IFCID trace running, in order to capture your dynamic SQL (see the manuals for syntax and details). [...] 28897 64 28_Re: DB2 6.1 query monitoring19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Tue, 28 Aug 2001 12:31:36 +0100325_us-ascii The best way to do this is using the DB2 event monitor, by capturing statement level information.

This will produce a lot of data and needs to be trudged through in order to get the info you want.

I started to write Perl scripts to do this, but we eventually went looking for a tool to assist us. [...] 28962 28 56_Re: wildcards in Cobol host variables - related question14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 28 Aug 2001 07:00:15 -0500645_iso-8859-1 Listers,

Can a 'like' predicate be indexable using a host variable without binding as reopt(vars)? If so, how does DB2 know that you won't put a '%' or an '_' in the first byte of the host variable ??







"The sender believes that this E-mail and any attachments were free of any virus, worm, or Trojan Horse when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking remedial action about viruses and other defects. Cendant/RCI is not liable for any loss or [...] 28991 151 35_Re: Calling all SQL Guru's.........11_James Szabo18_jim.szabo@CORE.COM31_Tue, 28 Aug 2001 07:53:03 -0400399_iso-8859-1 Shery,

You can usually change a UNION to a FULL OUTER JOIN:

SELECT A, B, C, ... FROM ... WHERE ... UNION ALL SELECT D, E, F, ... FROM ... WHERE ...

can become

SELECT COALESCE(A, D) , COALESCE (B, E) , COALESCE (C, F) , ... FROM (SELECT A, B, C, ... FROM ... WHERE ... ) AS X FULL OUTER JOIN (SELECT D, E, F, ... FROM ... WHERE ... ) AS Y ON X.??? = Y.??? [...] 29143 69 56_AW: wildcards in Cobol host variables - related question12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 28 Aug 2001 14:29:12 +0200361_iso-8859-1 Scott,

if you have somewhat like "columnname like :hv" this predicate is stage 1 and such a predicate can be satisfied by using an index.

DB2 for OS/390 considers predicates using LIKE with a host variable to be indexable unless the column has a field procedure defined on it or the host variable begins with an underscore _ or %. [...] 29213 67 8_Log full19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Tue, 28 Aug 2001 16:06:10 +0200409_iso-8859-7 Hi all,

I have an replication between two DB2 on NT systems. When I start capture on the source server I got the error : Log full --active log held by appl. handle 162 End this application by commit,ROLLBACK or FORCE APPLICATION. The app 162 is the capture program. Althouht I stop the program and force application I get continiously the same error. Also I cannot stop DB2. Any ideas? [...] 29281 62 16_Re: DB2 V7 OS39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 28 Aug 2001 09:15:16 -0400370_iso-8859-1 We have been on V7 for a couple of months now. 3 prod subsystems, 4 development, several sandbox.

V7 has been very stable. Zero outages caused by V7. No mysterious increase in resource consumption. No noticeable performance impact. New features work as advertised.

Did require us to do more rebinds than we usually do on a version upgrade. [...] 29344 98 16_Re: DB2 V7 OS39016_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 28 Aug 2001 09:26:52 -0400488_us-ascii Eric,

What maintenance level are you at? Thanks, Rick Creech





"Pearson, Eric L," To: DB2-L@RYCI.COM bcc: Sent by: DB2 Subject: Re: DB2 V7 OS390 Data Base Discussion List



08/28/01 09:15 AM Please respond to DB2 Data Base Discussion List











We have been on V7 for a couple of months now. 3 prod subsystems, 4 development, several sandbox. [...] 29443 164 27_Re: SECQTY space allocation14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Tue, 28 Aug 2001 06:42:01 -0700335_iso-8859-1 I have 2 questions.

1. If you have a TS that exceeds 1 volume , is it better to override SMS and get the primary to span vol.'s or use secondary extents?

2. With today's improved DASD, what is a reasonable amount of extents before performance is impaired? Thanks,

Daryl (DJ) Johnson DBA - PaciCorp [...] 29608 121 16_Re: DB2 V7 OS39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 28 Aug 2001 09:53:09 -0400426_iso-8859-1 We have not put on any 'put tapes' per se so the best answer I can give is: Whatever was advised in the PSP bucket June 18 plus about a dozen PTFs. Are you curious about any particular PTF?

Regards, eric pearson





-----Original Message----- From: Charles F Creech [mailto:cfcreech@DUKE-ENERGY.COM] Sent: Tuesday, August 28, 2001 9:27 AM To: DB2-L@RYCI.COM Subject: Re: DB2 V7 OS390 [...] 29730 15 28_Re: RXSQL for OS/390 manuals8_Harish G24_hari_gangadhar@YAHOO.COM31_Tue, 28 Aug 2001 08:50:29 -0500423_- Hi Chris,

Thank you. But the introduction to the manual says that it is for VM, and hence I am not sure whether it would apply equally well for OS/390 too.

- Harish.

================================================ 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. 29746 91 37_Re: wildcards in Cobol host variables14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 28 Aug 2001 08:54:03 -0500553_- Vince

Perhaps working through some examples would help:

1) ... COL LIKE 'ABC%'

Pretty obvious, first three letters are 'A', 'B' and 'C', then zero or more characters which can be anything

2) 01 HV-STRING PIC X(5). MOVE 'ABC%' TO HV-STRING. EXEC SQL .... COL LIKE :HV-STRING

After the move, HV-STRING contains 'ABC% '. Note the space after the percent sign? So this means first three letters are 'A', 'B' and 'C', then zero or more characters which can be anything ***then a single space character at the end*** [...] 29838 124 41_Re: Parititioned tablespace with UDB/UNIX14_Philip Gunning24_philip.gunning@QUEST.COM31_Tue, 28 Aug 2001 07:03:21 -0700389_iso-8859-1 Atalay, No there isn't. In a EEE environmnet data can be partitioned across multiple nodes instead to take advantage of query paralellism. The DB2 UDB Admin Perf Guide discusses this in a bit of detail. HTH Phil

-----Original Message----- From: Atalay Tanis (Garanti Teknoloji) To: DB2-L@RYCI.COM Sent: 8/28/01 2:54 AM Subject: Parititioned tablespace with UDB/UNIX [...] 29963 156 16_Re: DB2 V7 OS39016_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 28 Aug 2001 10:07:56 -0400367_us-ascii No, not curious about any particular PTF, but when someone has found a stable point I like to keep that in mind. Do you know the put-level of the tape that was sent to you?





"Pearson, Eric L," To: DB2-L@RYCI.COM bcc: Sent by: DB2 Subject: Re: DB2 V7 OS390 Data Base Discussion List [...] 30120 111 35_Re: Accessing DB2 on AS/400 from NT14_Philip Gunning24_philip.gunning@QUEST.COM31_Tue, 28 Aug 2001 07:46:32 -0700535_iso-8859-1 Take a look at setting the DB2CODEPAGE environmental variable to one that matches that used by the host. HTH Phil

-----Original Message----- From: Viswanathan N To: DB2-L@RYCI.COM Sent: 8/22/01 9:53 PM Subject: Accessing DB2 on AS/400 from NT Importance: High

I am trying to access OS/400 from the NT machine .. I am able to connect and query my database . But system table are having code page 937 and whenever I query the system tables then it gives error saying can't convery codepage 937 to 1252 .... [...] 30232 148 39_Re: WebSphere on AIX, WAS DB on OS/390?22_Varghese, Ajith George32_ajith.varghese@BLR.HPSGLOBAL.COM31_Tue, 28 Aug 2001 20:00:17 +0500337_iso-8859-1 Good work Mr Yassin I am happy to see that people in this group are able to solve problems with the help of suggestions given by others.

AGV Ajith George Varghese HCL Perot Systems, #947, Roshini Chambers, 24th Main, 2nd Phase, J.P.Nagar, Bangalore -560078 INDIA Phone: 080 6657309/ 080 6657310 Mobile: 9845155139 [...] 30381 19 30_Re: db2 ddf threads priorities12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 28 Aug 2001 10:21:46 -0500476_- The typical setup includes a small response time goal with a good priority for a small amount of work, then a medium response time for a larger amount of work, and then a velocity goal for more work.

There is a section of the Administration Guide, "Using Workload Manager to set performance objectives" that tries to provide an outline. There are a couple of presentations from Share by Glenn Anderson on WLM and WLM with DB2 that might be useful. www.share.org [...] 30401 14 28_Re: DB2 6.1 query monitoring12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 28 Aug 2001 10:29:27 -0500445_- There is a trace record IFCID 0022 that has the same information as EXPLAIN. Those records can be formatted by a number of products like Candle's Omegamon, DB2 PM, ...

Roger Miller, DB2 for 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. 30416 104 17_Query performance19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Tue, 28 Aug 2001 18:44:13 +0200360_iso-8859-7 Hi,

I have the following table table A (c1 char, c2 char, c3 int, c4 varchar)

and a query

select c2,c3,c4 from A where c1=x'a number' order by c2

c2 is the primary key and there is another index on c1.

During the reorg which index must I use for better performance? There is only this select on a huge table. [...] 30521 128 56_Re: wildcards in Cobol host variables - related question14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 28 Aug 2001 10:39:07 -0500524_iso-8859-1 Sorry for not being more specific. I am aware that 'like :HV' can be indexable. The question is, how does DB2 know when the value in :HV makes the predicate not indexable (first byte being a wildcard) unless we reoptimize at run time??

We had large project a short while back (we may have been on V5/s390 then) where inquiry apps using the 'like' predicates were gettting tablespace scans...even though we were searching the first or only column of an index and we did not start the HV w/ a wildcard. [...] 30650 31 27_Re: SECQTY space allocation10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 28 Aug 2001 18:03:31 +0200523_us-ascii Hi Daryl from my experience (of course it's my own experience and it may be useless to other people):

1) It depends (among others things) on how data increase. Using primary may be a waste if you extent in a new volume and you need only few KBs. On the contrary in some heavy load it may be useful (historical table for instance). In red book describing DFSMS v 1.4 they say that you can use space constraint relief (reduce space up to %), if needed. I think it's true for EF and non EF VSAM datasets. [...] 30682 23 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM31_Tue, 28 Aug 2001 11:10:16 -0500617_us-ascii I will be out of the office from 08/28/2001 until 08/30/2001.

I will be checking voice-mail.

Please read carefully for who to page:

For DB2 page page, 1-800-759-8888, PIN # 7270526

For Informix/Sqlserver page 1-800-759-8888, PIN # 7270525,

For Network Infrastructure, page 1-800-759-8352, PINs # 7270063, 7270065, 7270061, 7270300.

================================================ 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. 30706 76 49_IDUG North America 2002 -- Presentations Reminder14_Philip Gunning24_philip.gunning@QUEST.COM31_Tue, 28 Aug 2001 08:57:45 -0700566_- Fellow DB2 professionals, The deadline for abstract submission is just 1 week away, 9/3/01. If you haven't submitted your abstract yet, go to http://www.idug.org, and go down the page and under IDUG North America, select Call for Presentations from the drop down list. If you have any questions on the process or on your idea for a presentation, I would be happy to discuss with you offline. If your abstract is selected the conference fee is waived! So, take that next step and submit an abstract. Remember, if you don't try, you'll never know. Cheers! Phil [...] 30783 114 39_Re: REORG TABLESPACE PART X SHRLEVEL RE64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Tue, 28 Aug 2001 11:27:57 -0500589_ISO-8859-1 Even with IBM REORG SHRLEVEL CHANGE, there are unavailabity issues. With SHRLEVEL REFERENCE, the NPI is unavailable to all during the SWITCH phase. During the BUILD2 phase, the logical partition of the NPI is not available even for READ access (it's in UTUT status).

The other not-so-obvious implications of doing an IBM REORG PART X SHRLEVEL REFERENCE (or CHANGE) with an NPI is that when you are done, you probably need to run a REORG on the NPI by itself. During this type of reorg (IBM REORG PART X SHRLVL REF), the old keys in the logical partition are deleted [...] 30898 55 61_How to remove a Local Database Catalog entry on UDB for AIX ?18_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM31_Tue, 28 Aug 2001 12:40:51 -0400619_us-ascii Good day.

Do any of you listers know how to remove a 'Local Database Entry' for a database whose physical directory structure has been deleted? I have looked through numerous IBM manuals but not found any significant help.



The example is as follows:

On UDB v7 for UNIX there exists 1 instance. In this instance a database has been created using the 'CREATE DB tempdb ON /TEMPDB' command. This command creates a DB that exits in the /TEMPDB path physically. There are also 2 catalog entries created. The first (in no particular order) is the 'System Catalog Entry'. The second [...] 30954 16 27_Re: SECQTY space allocation12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 28 Aug 2001 11:39:41 -0500357_- From the performance data I've been seeing, the performance for allocating and using a data set seems to be about the same for one extent or twenty extents. The performance for allocation and extending data sets is probably measurable, but not big. The improvements from caching in DFSMS 1.5 are helpful, in addition to the caching in control units. [...] 30971 17 16_Re: DB2 V7 OS39012_Roger Miller19_millerrl@US.IBM.COM31_Tue, 28 Aug 2001 11:47:24 -0500350_- The APAR rate for V7 is very low. All of our early customers indicated that this is not anything like V6. For specific stability points, one of the key processes has changed a lot.

You may want to use IBM Consolidated Service Test levels to help. http://ibm.com/servers/eserver/zseries/zos/servicetst/

Roger Miller, DB2 for z/OS [...] 30989 19 28_Re: DB2 6.1 query monitoring13_Judy Brownlow18_judysaxe@M3NET.NET31_Tue, 28 Aug 2001 11:54:19 -0500420_- There is an SQL Monitor available in the Database Attach product from SoftBase Systems. It will report on the application queries for you, with recorded CPU usage and other critical information. The batch Attach facility also has features to eliminate contention on resources. Please call, or visit www.softbase.com if you like for more information. Kind regards, Judy Brownlow SoftBase Tech Support 800-669-7076 [...] 31009 98 65_Re: How to remove a Local Database Catalog entry on UDB for AIX ?0_20_John_Lendman@FPL.COM31_Tue, 28 Aug 2001 13:04:55 -0400414_us-ascii Did you try the "remove" database command. I found this to work in the past, I am on V6.1.

John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413





"'Richard Mccutcheon'" cc: Sent by: "DB2 Data Base Subject: How to remove a Local Database Discussion List" Catalog entry on UDB for AIX ? [...] 31108 16 44_IMS to DB2 conversion for checkpoint/restart13_Judy Brownlow18_judysaxe@M3NET.NET31_Tue, 28 Aug 2001 12:13:56 -0500458_- Preethy, If your IMS application programs have checkpoint/restart logic within them, I can send you information on a bridge for conversion of that type of function. Let me know if you would like this. Regards, Judy

================================================ 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. 31125 43 16_Re: DB2 V7 OS39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 28 Aug 2001 13:33:24 -0400336_iso-8859-1 Amen to that. I've been around for the install of every DB2 version from V1 through V7 (except for V6 which we skipped). V7 is far and away the most stable trouble free install and operation I've encoutered. Whatever IBM did to the QA process for V7 worked superbly.

Regards, eric pearson NS ITO Database Support [...] 31169 29 59_DB2 V5 OS/390 - DB2 PM long report - NOT ACCOUNTed category11_Vijay Kumar26_vijay_kumar_33@HOTMAIL.COM31_Tue, 28 Aug 2001 17:28:41 +0000350_- Greetings,

The DB2 PM long report shows that most of our online DB2 Plans (Allied/DBATs) has more than 50% of Class 2 time under NOT ACCOUNTed category. There are no parallelism, stored procedure, UDFs involved, and the system CPU is at 100% for about 45 mins in the morning and in the afternoon, and about 80-95% the rest of the time. [...] 31199 108 65_Re: How to remove a Local Database Catalog entry on UDB for AIX ?20_Sitaram Vijay kartik21_vijaykartik@YAHOO.COM31_Tue, 28 Aug 2001 10:29:46 -0700337_us-ascii Hi:



UNCATALOG DATABASE is an option to try out. This will remove local catalog entries for the database. If the catalog entry does not exist and still prohibits you from creating a new database with the same name, then do a local CATALOG on the database that does not exist and then an UNCATALOG database .. [...] 31308 62 63_Re: DB2 V5 OS/390 - DB2 PM long report - NOT ACCOUNTed category0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Tue, 28 Aug 2001 13:52:10 -0400463_us-ascii Vijay,

"Not Accounted" time could be swapped out time, paging, wait for CPU time, etc. The higher the dispatching priority the less this number will be. Therefore, a high "Not Accounted" value may indicate a problem with MVS dispatching priority.

R/...Ed







Vijay Kumar @RYCI.COM> on 08/28/2001 01:28:41 PM

Please respond to DB2 Data Base Discussion List [...] 31371 19 22_strange sql error code17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Tue, 28 Aug 2001 12:53:23 -0500267_us-ascii Has anyone ever seen a -sql1022 memory error in their db2diag.log file? We seem to be getting them alot since we installed db2 7.2. We opened up a PMR with IBM but they don't see to have an idea as to what the problem is. Any help would be appreciated. [...] 31391 95 63_Re: DB2 V5 OS/390 - DB2 PM long report - NOT ACCOUNTed category16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Tue, 28 Aug 2001 13:59:59 -0400418_us-ascii Or could simply indicate a very busy machine. If the machine is busy enough, someone must wait.

Rick Creech





Ed_Vetock@NAVYF EDERAL.ORG To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base bcc: Discussion List Subject: Re: DB2 V5 OS/390 - DB2 PM long



08/28/01 01:52 PM Please respond to DB2 Data Base Discussion List [...] 31487 36 21_Peoplesoft SQR to DB212_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Tue, 28 Aug 2001 13:02:06 -0500351_us-ascii Hello,

I know this is the wrong place to address this, but I'm hoping someone can direct me to the peoplesoft listserv/newsgroup, or by chance might know the answer.

I'd like to have a peoplesoft SQR running on a server connect to a non-peoplesoft OS/390 DB2 database (via DB2 Connect) and issue a simple select statement. [...] 31524 110 53_Re: Calling CICS program from a Db2 stored procedure.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 28 Aug 2001 14:07:23 -0400575_iso-8859-1 Thanks to all. Finally I could call a CICS program from DB2 stored procedure. We added CICS.R13TEST.SDFHEXCI in both link step of the program compilation and also in the WLM address space which runs the stored procedure. One of my idea was create a DGTT table in CICS program after reading ADABAS/VSAM etc and returned the control back to stored procedure and stored procedure open the cursor on that and return the data to client/server program. It looks like when I do that I am getting -204 on the DGTT in stored procedure. I can pass data back to calling [...] 31635 92 25_Re: Peoplesoft SQR to DB212_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 28 Aug 2001 14:17:36 -0400322_US-ASCII Hi Steve, When SQR runs on OS/390 in batch you will find a PARMLIB member to run under DSN. In this parm card you will find the database owner or table creator. In the example below the table owner is PSDA just after the DSN/PSDA statement. You will need either an ALIAS or synonyms to point to your tables. [...] 31728 58 25_Re: Peoplesoft SQR to DB213_Thomas, Janis19_Janis.Thomas@CA.COM31_Tue, 28 Aug 2001 14:21:38 -0400369_iso-8859-1 You can subscribe to the PeopleSoft DB2 for OS/390 email forum by addressing an email to: listserv@listserv.ca.com. Include the following command as the text of the email (blank subject): SUBSCRIBE PSDB2OS390

The list is not very active, but it is specific to PS on DB2 for OS/390. I am the list owner, so if you have questions, please email me. [...] 31787 34 46_Archival temporal database & performance issue19_Insixiengmay, Vince22_vinsixieng@STATE.PA.US31_Tue, 28 Aug 2001 14:42:27 -0400584_iso-8859-1 Hello, I need help on archiving temporal database and performance issue: 1. We are currently using UDB 6 in our shop. Are there any Archival Tools or software for temporal database? Are there any issues and problems regarding the archive? i.e. retrieving data, and etc... 2. Is there any performance cost of any significance to having a timestamp in every row of every table? If so, is the cost significant if it is populated using a Default on INSERTS? 3. We also plan to use a constraint to disallow using any other DATETIME, but the CURRENT DATETIME. Does this add [...] 31822 16 25_Re: Peoplesoft SQR to DB212_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Tue, 28 Aug 2001 14:06:35 -0500436_us-ascii Thank you... That sounds perfect for if the SQR were running on the mainframe. How 'bout if it's running on Window's NT (which is where I think all of our PS stuff will run?)

Stg

================================================ 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. 31839 61 25_Re: Peoplesoft SQR to DB212_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 28 Aug 2001 15:29:35 -0400402_US-ASCII This is usually handled by a setup include file called SETUPDB found in the SQC library. The user will be prompted for a Database Ownerid. If you look at this SQC you will see:

ask dbname 'Database ownerid (in uppercase)' !

Begin-SQL SET CURRENT SQLID='{DBNAME}' End-SQL



I hope this helps.







Troy Coleman Coleman Consulting, Inc. [...] 31901 24 80_Insert, delete, select, update access for a id for all the tables in a subsystem11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Tue, 28 Aug 2001 14:44:06 -0500286_iso-8859-1 There is any way to grant to a userid Insert, delete, select, update authority for all the tables in a subsystem ? Not to grant it explicitly at table level but any authority level at which it can be done.

We don't want this id to have any drop authority though. [...] 31926 27 32_DB2 for VSE/ESA and VM/ESA Tools10_Sam Fishel26_sfishel@COURTS.STATE.VA.US31_Tue, 28 Aug 2001 16:12:39 -0400531_US-ASCII Hi,

Our branch is getting ready to convert from DL/I databases to DB2 and I was wondering if there was anyone out there that could send some information about using tools and utilities for VSE/ESA or VM/ESA. We are not sure which platform we will be running DB2 under. We are studying the consequences of running under VSE/ESA or VM/ESA. My job is to find out what tools are out there for these two platforms and compare them to IBM's Control Center. I would really like some input on this if you can help me. [...] 31954 139 85_Re: Insert, delete, select, update access for a id for al l the tables in a subsystem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 28 Aug 2001 21:40:54 +0100323_iso-8859-1 Not with 'native' DB2 security, but using external security options (RACF, ACF for DB2 etc.) you CAN grant all these things to wildcarded tables, so in theory "GRANT INSERT etc etc ON TABLE *.* TO userid" should work.

As an aside, how many people are actually using the RACF DB2 security these days? [...] 32094 144 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 28 Aug 2001 16:51:34 -0400393_iso-8859-1 Phil, You missed an opportunity! Doing what Amit wants is *very* easy with RC/Secure.

Regards, eric pearson NS ITO Database Support

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Tuesday, August 28, 2001 4:41 PM To: DB2-L@RYCI.COM Subject: Re: Insert, delete, select, update access for a id for al l the tables in a subsystem [...] 32239 15 46_Re: Precaution regarding special character set8_Harish G24_hari_gangadhar@YAHOO.COM31_Tue, 28 Aug 2001 15:57:50 -0500361_- Hi,

How are you trying to do the insert/ retrieve ? Is this in a Client/ Server environment ?

- Harish.

================================================ 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. 32255 122 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 28 Aug 2001 23:29:53 +0200816_iso-8859-1 Eric,

*plug* Catalog Manager form BMC does the trick as well.

;-)

Regards,

Jaap Slot

Data Service Center Rabobank ICT



Telefoon 030 215 2220 Fax 030 215 3003 Mobile 06 5374 0167 mailto:J.P.Slot@rf.rabobank.nl

Reserve your AGENDA for next DB2 Users Group Conference IDUG Europe, 8-11 October 2001 in Florence, Italy. http://www.idug.org













-----Original Message----- From: Pearson, Eric L, [ mailto:Eric.Pearson@NSCORP.COM ] Sent: Tuesday, August 28, 2001 10:52 PM To: DB2-L@RYCI.COM Subject: Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem [...] 32378 109 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 28 Aug 2001 23:46:20 +0200334_iso-8859-1 Phil,

We are NOT using RACF on DB2. Normally RACF is done by a RACF-administrator, and not all RACF administrators have knowledge of DB2. Thus potentially a lot of trouble might come over us. (Sorry to all out there who have excellent knowledge of DB2) Besides, RACF still doesn't cover all authorities in DB2. [...] 32488 179 36_Re: DISTINCT in Select stmt / DB2 V612_Kirk Hampton16_khampto1@TXU.COM31_Tue, 28 Aug 2001 17:15:00 -0500309_us-ascii One more thing to try, we ran into at least two situations after migrating to V6 where a problem involving a view disappeared after we dropped and re-created the view. This was recommended by the support center as a workaround for one problem, and it worked for us on another unrelated problem. [...] 32668 39 35_Unusual optimizer behavior - help?!11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Tue, 28 Aug 2001 17:04:18 -0600376_iso-8859-1 We're having a very unusual circumstance with a SQL stored proc that performs an INSERT ... SELECT operation, selecting from our typical database tables and inserting into a global temporary table. We have made some relatively small modifications to this proc, which we expected to reduce the complexity, and have found a gigantic decrease in the performance. [...] 32708 142 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Tue, 28 Aug 2001 19:07:12 -0400422_us-ascii We are using RACF on DB2 and it works very well for our environments. DB2 V6.1 DB2 v 7.1 One racf group has authority to create/drop storge groups/databases. and grant DBADM to another racf group that is set up by database. - we can then connect authorized users to that database racf group. Within the same database RACF group all Grants on selects on tables and execute of plans packages are allowed. TomF [...] 32851 236 19_Re: index lookaside13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 28 Aug 2001 17:29:15 -0700571_us-ascii Nagaraj,

Couldn't there still an SPROC with a SELECT / FETCH only SQL?

Tink --- "Pudukotai, Nagaraj" wrote: > Sanjeev, Tink and Max, Thank you very much for your > responses. I did go > through the article sent by Sanjeev (Bonnie Baker's > in DB2 magazine). The > cursor is declared with WITH HOLD. As I said in my > mail I issue a commit > after 100 FETCHES of driving cursor in which case > dynamic prefetch seems to > be switched on because I can see the CPU being > consumed quite heavily (about > 20 [...] 33088 28 19_DB2 Tech Conference12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Tue, 28 Aug 2001 19:44:48 -0500437_us-ascii Hello, Listers,

I just got my credit card bill for the 2001 DB2 Tech Conference in September in Orlando.

I hope to see many of you there; maybe we can do a Birds-of-a-Feather ?

Best regards, John P. Higgins Performance Specialist SAP BASIS Team

Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098 [...] 33117 91 30_Re: SYSADM AUTHORITY - HELP!!!14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Wed, 29 Aug 2001 11:27:43 +1000485_iso-8859-1 We have SYSADM granted to the RACF DBA group for Dev/Test environments, and have a separate logon for Prod (let's call it FRED) which has SYSADM and also is AUDITED. If we do something in Prod with this logon (FRED), then there must be a corresponding Change Management or Problem entry related to that use, or your neck is on the chopper.... FRED is also SYSADM in Dev & Test, so we can set up & test jobs prior to running in Prod. The Dev/Test access is not audited. [...] 33209 18 30_Re: SYSADM AUTHORITY - HELP!!!25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU31_Tue, 28 Aug 2001 20:43:03 -0500512_iso-8859-1 We've gone the RACF route since DB/2 2.1. It's gotten better along the way, we finally learned not to give access to individuals but to groups and then add/drop userids within the group as required.

It's just a functional tier of which group does what.

================================================ 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. 33228 33 23_Re: DB2 Tech Conference13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 28 Aug 2001 22:58:51 -0400510_iso-8859-1 Great idea John.

(by the way, one should pretend to be cosmopolitan here and recognize that there are multiple inter-galactic occurrences of the DB2 Tech conference and specify the one to which one's referring. I suspect you're referring to the North American(earth) instance.)

I'll be there and would enjoy meeting more of us. I'm sure Scott Database-GUYs Hayes will be there and he's always a great guy to meet. (where the heck you been, Scott? Business been too good or what?) [...] 33262 73 85_Re: Insert, delete, select, update access for a id for all the ta bles in a subsystem16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 29 Aug 2001 10:26:18 +0530655_- If you do not have RACF or any other products mentioned in the replies then try this crude method:

SELECT 'GRANT INSERT.............' || NAME || 'TO userid" FROM SYSIBM.SYSTABLES

Regards Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration OS/390





> -----Original Message----- > From: Sinha, Amit [SMTP:Sinha.Amit@MBCO.COM] > Sent: Wednesday, August 29, 2001 1:14 AM > To: DB2-L@RYCI.COM > Subject: Insert, delete, select, update access for a id for all the > tables in a subsystem > > There is any way to grant to a userid Insert, delete, select, update > authority for all the tables in [...]