1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2001, week 3 2 46 38_Re: How to indicate delete intention ?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 15 Aug 2001 00:18:44 -0500429_- If the DELETE WHERE CURRENT OF is static SQL, then the cursor is not ambiguous.

It's only if the DELETE is dynamic that the cursor can be ambiguous - or more correctly, if there is an EXECUTE/EXECUTE IMMEDIATE in the program, then DB2 cannot know if the executed statement is a DELETE/UPDATE WHERE CURRENT OF - and will leave the cursor as ambiguous. A static DELETE/UPDATE WHERE CURRENT OF resolves the ambiguity. [...] 49 60 50_AW: 'SELECT AS' help (here: maximum column length)12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 15 Aug 2001 08:28:25 +0200418_iso-8859-1 Jim,

no chance at this point in time. The restriction of 18 bytes for a table column is still alive...

And do not trust the rumours: The increase in name lengths in DB2 has not been announced by IBM as to when or in what version/release it might be...

With kind regards - mit freundlichen Grüssen, Georg H. Peter ------------------------------------------------------------------- [...] 110 101 60_Re: DDF TCPIP insert Performanace & Blocking or not blocking15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Wed, 15 Aug 2001 08:50:26 +0200320_iso-8859-1 Alex,

We have a similar problem using DB2 Connect v7 on AIX and DB2 v6 on OS/390 v 2.10. We used a 100 Mbit Ethernet with a dedicated line and encountered the same problem as you describe. I've not yet located the exact source of the problem, neither do I have a solution but here are some hints; [...] 212 80 60_Re: regarding DSN1COPY with CHECK option.... immediate help.15_Philippe Herman20_pherman@LANDMARK.COM31_Wed, 15 Aug 2001 03:09:51 -0400627_us-ascii Hi Kumar, Marc,

If you QUIESCE the object, then you don't need to stop it

Philippe







Praet Marc on 08/15/2001 06:47:26 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Philippe Herman/LSC)

Subject: Re: regarding DSN1COPY with CHECK option.... immediate help.





Hi Kumar,

Yes, you need to stop the spaces involved. Why ? Simply, to flush the DB2 buffers, meaning the pages in the bufferpool to be written to disk, so that DSN1COPY can see them, if not... [...] 293 51 25_Re: What is a drain lock.13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Wed, 15 Aug 2001 02:44:01 -0500558_ISO-8859-1 Hi !

This is right from the IBM Redbook "Locking in DB2 for MVS/ESA Environment":

A drain is the action that DB2 utilities and commands perform to wait for claimers; that is, to wait for the SQL transaction actively using the object to quit actively using them, that is, release their claim. The drain aquires a special lock, wich is known as a drain lock. It is a real IRLM lock, located in the page set, and blocks new claimers. It then waits for all current claims on the object to be released. The effect of the drain is to [...] 345 128 33_Re: Managing Stored Procs/Schemas10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 15 Aug 2001 18:50:09 +1000353_us-ascii Hi Jaap,

We have lots of environments on the same subsystem (including test and development). We have had individual procedures abend,, but they havent brought down the address space itself. I guess it depends on the actual procedure code, but ideally it shd not bounce the address space. But u never know. It can still happen :-(. [...] 474 59 30_The optimiser mad a bad choice11_neil briggs25_N.Briggs@CANON-EUROPA.COM31_Wed, 15 Aug 2001 11:15:57 +0200390_iso-8859-1 Dear List

os390 2.7 - V5.1 DB2

I am hoping that somebody can shed a bit of light on my problem. I decided for better or for worse (please give opinions) that I would perform a reorg, runstats and bind on all the plans in one of our production databases. Rebinds had been done but not a bind since 1998. The verision of DB2 has also changed during this period. [...] 534 36 19_Re: IBM Redbook URL12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Wed, 15 Aug 2001 07:23:17 -0400699_iso-8859-1 http://www.redbooks.ibm.com





-----Original Message----- From: adamsrob@WELLSFARGO.COM [mailto:adamsrob@WELLSFARGO.COM] Sent: Tuesday, August 14, 2001 10:15 PM To: DB2-L@RYCI.COM Subject: IBM Redbook URL



SORRY to burden the list with this yet again, but will someone kindly send me the URL for the IBM redbook site?

Off-list would be fine!

Rob Adams-Kane Wells Fargo adamsrob@wellsfargo.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. [...] 571 154 26_Re: Oracle gateway and DB213_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Wed, 15 Aug 2001 08:23:02 -0400438_US-ASCII

We are also running DB2 v6 and Oracle 8i.

We have some of those problems too. Our gateway thread interferes with anyone trying insert or update any tables accessed by Oracle. It also interferes with utilities such as Load and Imagecopy. We run reorg's and other maintenance utilities on the weekend and all Oracle threads are gone by then so I do not know if they would interfere with those utilities or not. [...] 726 18 19_DB2 Windows Utility18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 15 Aug 2001 08:25:55 -0400319_iso-8859-1 Does anyone know the name of the DB2 windows utility which will calculate tablespace sizes? I can't for the life of me remember it. I would like to download it from IBM's site.

Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2 [...] 745 43 23_Re: DB2 Windows Utility22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Wed, 15 Aug 2001 09:34:34 -0300727_iso-8859-1 Would it be DB2 Estimator?

Claudio ********************************************** * Claudio Meluzzi Mendes * claudio@beg.com.br * Banco do Estado de Goias * Goiania - GO - BRASIL **********************************************

----- Original Message ----- From: "Krawetzky, Peter J" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, August 15, 2001 9:25 AM Subject: DB2 Windows Utility



> Does anyone know the name of the DB2 windows utility which will calculate > tablespace sizes? I can't for the life of me remember it. I would like to > download it from IBM's site. > > Peter J. Krawetzky, DBA > IBM Certified Solutions Expert > DB2 UDB [...] 789 57 42_Re: Enabling Parallelism in Cognos Queries11_Jeff Schade21_JSCHADE@MAIL2.PCH.COM31_Wed, 15 Aug 2001 08:50:17 -0400468_US-ASCII We are using DB2 Connect Enterprise edition on a Unix server to connect to OS/390. I have checked out the Client Configuration Assistant and I cannot determine which parameter controls DEGREE. Can you be more specific.

The other option I was looking into was rebinding all the DB2 Connect packages in collection NULLID to DEGREE ANY. I realize that as I install new fixpacks or releases of DB2 Connect I would have to remember to do this re-bind. [...] 847 132 17_Re: Mine's a half14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 15 Aug 2001 14:11:17 +0100470_iso-8859-1 Don't forget

WHERE X <= Y is only the same as WHERE X BETWEEN 0 and Y when X can only be positive......

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: Marcus Davage [mailto:marcus.davage@LLOYDSTSB.CO.UK] Sent: 14 August 2001 15:39 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Mine's a half [...] 980 95 42_Re: Enabling Parallelism in Cognos Queries9_Chris Tee23_chris.tee@UK.ZURICH.COM31_Wed, 15 Aug 2001 14:17:11 +0100311_us-ascii Jeff,

In CCA, highlight the database and click on the Properties button. In the CLI/ODBC box click on the Settings button. Then click on the Advanced button. Click on the Optimization tab and highlight Parallelism Level. You can now choose the degree you want. Click on OK all the way out. [...] 1076 119 34_Re: The optimiser mad a bad choice16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Wed, 15 Aug 2001 09:22:57 -0400568_us-ascii Hi, In order to give a more specific answer to your questions, you will need to give more specific information, i.e.: the exact definitions of the indexes, the sql involved, the statistics. Generally, the better that the sql matches a particular index, that index will be chosen. The optimizer calculates the cost of each access path and chooses the path that has the least cost. The cost is made up of both a cpu cost and an io cost. The vast majority of times the optimizer makes the best choice. There are several things that can be done to influence [...] 1196 92 48_Doubt in performance of the following statements0_24_Sridhar_Kondusamy@I2.COM31_Wed, 15 Aug 2001 18:57:50 +0530398_us-ascii Good Morning

I would like to know which of the following statements could be more efficient as far as performance and access plan is considered

Say the table (table_a) contains 100,000 records in it

FOR cur_for AS SELECT col_1 FROM table_a WHERE col_2 = x1 AND col_3= x2 AND col_4 IS NULL AND col_5 IS NULL DO SET v_col = cur_for.col_1; LEAVE cur1_loop; END FOR; [...] 1289 17 0_14_subrata mondal25_subratamondal@HOTMAIL.COM31_Wed, 15 Aug 2001 13:49:23 +0000 1307 149 34_Re: The optimiser mad a bad choice11_David Nance16_DWNance@FHSC.COM31_Wed, 15 Aug 2001 09:49:18 -0400405_US-ASCII Another thing to look at is a suggestion Terry Purcell made within the last day or two, about using the FREQVAL NUMCOLS COUNT options on your statistics. I was having a similar problem about a month ago, reran the stats with the above options and finally got the access path that I was trying to get DB2 to use. The more info optimizer has about your tables the better the choices it makes. [...] 1457 75 12_Use of Index13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM31_Wed, 15 Aug 2001 11:13:08 -0400433_us-ascii Hello all , I have provided the details of a Table , Index , queries and some observations . Please let me know your thoughts / explanations for the following .

-------------------------------- There is an index : INDEX1 , on column DIGITS_DIAL . The column DIGITS_DIAL is defined as Decimal ( Length 10 , Scale 0 ). Colcardf for DIGITS_DIALED : 748105 . The table has approx. 65 million rows . DB2 V6 on OS/390 [...] 1533 95 16_Re: Use of Index24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Wed, 15 Aug 2001 08:46:02 -0700306_- Not having the skills of other members of the list, I can't say why the index is chosen for some queries but not for the others. However, since the column is defined as decimal, the values in the WHERE clause should include the decimal point. Otherwise, DB2 may feel the need to convert data types. [...] 1629 222 16_Re: Use of Index12_Troy Coleman19_Colematr@MEIJER.COM31_Wed, 15 Aug 2001 11:53:32 -0400617_US-ASCII Take a look at the cardinality of column DIGITS_DIAL.

SELECT DIGITS_DIAL, count(*) FROM WOM.TERM_CALL_DETAIL GROUP BY DIGITS_DIAL

You will probably find that a high percentage of your data contains the DIGITS_DIAL value of 8774982562.

If you did the explain with DIGITS_DIAL = ? you will probably see index access because the optimizer does not know what the value is. However, with a literal value the optimizer can determine how many pages it will need to scan at the index level and data level. The optimizer is telling you in the explain that it will be cheaper to scan all the [...] 1852 20 37_DB2 security management tool required10_Len Kremer24_Tddbadb2_Tddbadb2@TD.COM31_Wed, 15 Aug 2001 12:37:51 -0400389_us-ascii Hello, After acquiring Platinum, CA decided not to support PROSECURE (DBSECURE, which was also acquired) product and switch to RCSECURE. Not to bother you with a great deal of details we do not find this product quite suitable in our huge test environment and would appreciate if you share with us your knowledge of other main frame existing products to manage DB2 security. [...] 1873 17 23_stored procedure survey10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Wed, 15 Aug 2001 12:26:09 -0500410_- We are in the process of figuring out how to successfully implement SPs using WLM in our shop. We interesting in seeing how other companies have implemented SP using WLM in their shop. If you have implemented SPs using WLM at your shop and are interested in participating in a quick phone or email survey, please send your email and/or phone number(which ever you prefer) to Toni_Addie@progressive.com. [...] 1891 28 68_Help ! drowning !, Dr Watson on DB2 Connect 7.2/7.1 on W2K platfo rm22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Wed, 15 Aug 2001 11:20:38 -0700610_iso-8859-1 DB2 Gurus, I have been facing sporadic Dr Watson's on my DB2 Connect EE development server(7.2, fixpack 3) and my production server (7.1) running on W2K (fix pack 1). The DB2-DB2 service stops in the server and I have to manually restart it.

1) I run into Dr Watson, if I change any of the connection pooling parameters (MAXAGENTS, INITAGNETS MAXLOGICAGENTS etc) 2) If I go into Performance monitoring in the Control center. 3) I am constantly getting SQl3282(invalid credentials) on db2diag.log when i try to start and stop monitoring (even though the my monitoring id is my install ID [...] 1920 91 38_MQSeries on a CICS Mainframe platform.29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV31_Wed, 15 Aug 2001 14:36:16 -0400489_us-ascii ----- Forwarded by Sherri Sterling/OIT/BPD on 08/15/01 02:36 PM -----



Jeffrey Little 08/15/01 02:21 PM



To: Sherri Sterling/OIT/BPD@BPD, gerald.brookman@ATOSORIGIN.COM cc: Subject: MQSeries on a CICS Mainframe platform.

Garry;

There are two methods of using MQSeries for a CICS mainframe application - the bridge and the adapter. We are in the process of using both methods and here are a few limitiations that we have encountered. [...] 2012 59 60_Re: regarding DSN1COPY with CHECK option.... immediate help.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Wed, 15 Aug 2001 13:39:36 -0500347_- Hi Folks,

Thanks for your help, i started the database in RO status, then ran the utility QUIESCE UTILITY with WRITE YES. then i ran the DSN1COPY with CHECK. so it went thru and then i started the database with RW staus.

acually just we wanted to check the data consistency. i don't want to copy from from subsystem to other. [...] 2072 26 21_Phonetic search tools13_James Sargent28_james.sargent@RCMP-GRC.GC.CA31_Wed, 15 Aug 2001 14:53:48 -0400463_US-ASCII Hi all:

Does anyone know of any Phonetic search tools for DB2?? I think Oracle has one.

Please advise

Thanks Regards

James A Sargent DB2 Support 613-993-8824 james.sargent@rcmp-grc.gc.ca

===============================================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. 2099 104 24_Re: DDL generation tools15_Neff, Stephen R16_NeffSR@STATE.GOV31_Wed, 15 Aug 2001 15:03:58 -0400651_iso-8859-1 Was this posted? I tried following the instructions below and could not get it to work.

-----Original Message----- From: Richard Yevich [mailto:Richard_Yevich@YLASSOC.COM] Sent: Friday, August 10, 2001 7:06 AM To: DB2-L@RYCI.COM Subject: Re: DDL generation tools



Use DB2 Connect, V7, run Control Center against the OS/390, and use the facility to unload the entire DDL for any object or database. Uses the DB2 Windows/Unix tool, DB2LOOK against the OS/390 Catalog to do exactly what you require. I do not have all the required notes in front of me, but those are the basics. DB2look has been doing this for years [...] 2204 49 25_Re: Phonetic search tools13_Olson, Carlos14_COlson@QRS.COM31_Wed, 15 Aug 2001 12:28:57 -0700544_iso-8859-1 James, Using the text extender feature of OS390 DB2 V6 and above, you can perform fuzzy searches of data which would include a phonetically misspelled word. See the Text Extender Administration and Programming Book at: http://www-4.ibm.com/software/data/db2/os390/v6books.html

Carlos Olson Database Administrator QRS Corporation



-----Original Message----- From: James Sargent [mailto:james.sargent@RCMP-GRC.GC.CA] Sent: Wednesday, August 15, 2001 11:54 AM To: DB2-L@RYCI.COM Subject: Phonetic search tools [...] 2254 171 24_Re: DDL generation tools12_Susan Lawson22_lawson_susan@YAHOO.COM31_Wed, 15 Aug 2001 12:34:46 -0700624_us-ascii Stephen,

There are some requirements for this first...

You must be V7 on the mainframe. For the best information on this refer to pg 192 of the DB2 for z/OS and OS/390 Version 7 Performance Topics redbook.

Regards, Susan YL&A, Inc --- "Neff, Stephen R" wrote: > Was this posted? I tried following the instructions > below and could not get > it to work. > > -----Original Message----- > From: Richard Yevich > [mailto:Richard_Yevich@YLASSOC.COM] > Sent: Friday, August 10, 2001 7:06 AM > To: DB2-L@RYCI.COM > Subject: Re: DDL generation tools > > > Use DB2 Connect, V7, [...] 2426 29 36_DB2 Administration client on Win200011_Judy Kibler21_judith.kibler@TRW.COM31_Wed, 15 Aug 2001 14:36:57 -0500537_us-ascii Hello all,

I have installed the Administration client on my Dell C600 Pentium III notebook (256 MG RAM/20 GB hard drive), with Windows 2000 (5.00.2195 Service pack 1). There is not a DB2 CD with software that specifically says it is for Windows 2000, but DB2 Support has assured me that the NT version will work. I have problems with it freezing up my PC every time I use it. It comes up OK (albeit very slow), but if I have to minimize it or switch to another session, it will not come back and I have to user Task [...] 2456 19 37_Migrating from Ver 5 to Ver 7. OS/3909_Bill Long22_billlong14@HOTMAIL.COM31_Wed, 15 Aug 2001 19:41:58 +0000584_- Has anyone on the list had any experience with migrating directly to Version 7 from Version 5 ? Is this a recommended step or should one take them, one at a time. Thanks for any input.



_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

================================================ 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. 2476 28 39_Where are DIAnnnnn messages documented?12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 15 Aug 2001 12:54:36 -0700373_us-ascii I've searched the whole IBM site for message DIA3208E with a socket code of 48 and an errno of 76.

Does anyone know where the DIA messages and the socket and errno codes for UDB on AIX v7.1 are located? I remember running into this before, and that they're not in the usual place but I can't find my notes on where to find them. Thanks in advance- Tim [...] 2505 23 51_DB2 Connect on Unix/Need help configuring please!!!13_Jodi Murawski27_Jodi.Murawski@METAVANTE.COM31_Wed, 15 Aug 2001 16:00:09 -0500446_us-ascii We have many DB2 Connect installs on NT platforms. I need to get DB2 Connect installed on a Unix box, (Lotus Domino server) to talk to DB2 on OS/390. I had a Unix admin do the install, but it does not work. I am not sure what is not working. If there is anyone whoe has done such a DB2 Connect Enterprise Edition install, I would really appreciate it if you can contact me off-list. I have a need to get this connection going asap! [...] 2529 66 43_Re: Where are DIAnnnnn messages documented?11_Judy Kibler21_judith.kibler@TRW.COM31_Wed, 15 Aug 2001 16:32:35 -0500360_us-ascii Hi Tim,

I called about a DIA msg once and the DB2 support guy I spoke with said that they (or at least the one I was calling about at the time) were undocumented messages that were really there for the support folks to help debug problems. He was able to help me, so I guess they have some sort of internal documentation for those errors. [...] 2596 56 23_Re: DB2 Windows Utility15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 15 Aug 2001 16:36:18 -0500605_us-ascii I think you need DB2 Estimator and you can download from ibm's site.

Thanks & Regards Nayeem







"Krawetzky, Peter J" To: DB2-L@RYCI.COM Subject: DB2 Windows Utility Sent by: DB2 Data Base Discussion List



08/15/01 07:25 AM Please respond to DB2 Data Base Discussion List











Does anyone know the name of the DB2 windows utility which will calculate tablespace sizes? I can't for the life of me remember it. I would like to download it from IBM's site. [...] 2653 111 30_Re: Access path change with v624_Humphris,Richard P.(NXI)24_Richard.Humphris@CNA.COM31_Wed, 15 Aug 2001 16:59:32 -0500604_- Thanks Terry,

I'm glad you brought this apar to my attention and the fact that I am now providing obsolete information. It looks like PQ43846 was designed to fix the very performance issues I was complaining about in V6 when I set the STARJOIN parameter. In fact, the apar description for PQ43846 explicitly said that it's intent was to "Minimize the possibility of the performance degradation of queries when star join is enabled, compared with the cases when star join is disabled." and it also talked about strengthening the performance of the STARJOIN code as well (when it does decide [...] 2765 21 17_OS Comand Scripts14_Craig Ducharme26_craig_ducharme@HOTMAIL.COM31_Wed, 15 Aug 2001 18:30:20 -0500416_- I'm running UDB v7.1 on NT 4.0. I've been trying to create a OS Command script in Script Center to execute file test.bat in directory c:\foo. Every time I run the script I get the same error: "DBA2078E An unexpected error occurred. There is no job output".

In the script window I have C:\FOO\TEST.BAT. I've tried: Enclosing it in quotes, both single and double. Setting the working directory to C:\FOO [...] 2787 148 21_Re: OS Comand Scripts11_Rolf Kramer23_RKramer@POWERLAN.COM.AU31_Thu, 16 Aug 2001 11:45:24 +1000330_- 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. [...] 2936 19 0_22_Preethy B, J16 Chennai25_preethyb@MSDC.HCLTECH.COM31_Thu, 16 Aug 2001 09:41:24 +0530280_iso-8859-1 Hi List,

I am in the look out for sites or documents which can help me out in the conversion of IMS to DB2. Can anybody give me some info on this? I have got an application which needs to b converted from IMS to DB2. It has CICS as the transaction manager. [...] 2956 34 9_( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 00:12:31 -0400483_US-ASCII I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 00:11 >>>

Hi List,

I am in the look out for sites or documents which can help me out in the conversion of IMS to DB2. Can anybody give me some info on this? I have got an application which needs to b converted from IMS to DB2. It has CICS as the transaction manager. [...] 2991 79 29_Re: AW: What is a drain lock.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 16 Aug 2001 10:16:06 +0530686_- Peter,

Thanks for the reply. The example was wonderful...helped me get a clear understanding.

thanks, Rajendra Deshpande.





>From: "Peter, Georg" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: AW: What is a drain lock. >Date: Tue, 14 Aug 2001 15:36:04 +0200 > >Rajendra, > >drains are required when a resource is first accessed. A drain acquires a >resource by quiescing claims against the resource. Drains can be requested >by commands and utilities. > >To more fully understand the concept of draining, think back to the last >time that you went to a movie theater. Before anyone is [...] 3071 68 47_Re: Mass deletes in Seg/partitioned tablespace.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 16 Aug 2001 10:27:28 +0530767_- Mike,

Can we not have the same technique, as in seg tablespace, employed for mass deleting a partitioned tablespace.

thanks, Rajendra Deshpande.





>From: mike.holmans@BT.COM >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Mass deletes in Seg/partitioned tablespace. >Date: Tue, 14 Aug 2001 15:01:55 +0100 > >Mass delete in a segmented tablespace is achieved by updating the space >management pages, marking all the non-space-management pages as being >empty. >Deleting from a partitioned tablespace deletes the actual rows, which means >visiting every active page. > >Mike Holmans >Database Consultant >Computing Partners, part of Affinitis BT >mike.holmans@bt.com > >The opinions [...] 3140 98 39_Re: AW: What is a drain lock. ( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 00:50:35 -0400705_ISO-8859-1 I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 00:46 >>>

Peter,

Thanks for the reply. The example was wonderful...helped me get a clear understanding.

thanks, Rajendra Deshpande.





>From: "Peter, Georg" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: AW: What is a drain lock. >Date: Tue, 14 Aug 2001 15:36:04 +0200 > >Rajendra, > >drains are required when a resource is first accessed. A drain acquires a >resource by quiescing claims against the [...] 3239 16 51_David Ayers/ISG/CORP/Highmark is out of the office.11_David Ayers24_david.ayers@HIGHMARK.COM31_Thu, 16 Aug 2001 01:01:12 -0400452_us-ascii I will be out of the office starting 08/15/2001 and will not return until 08/17/2001.

For DB2 OS/390 assistance contact your support DBA. For Datajoiner / DB2 connect support contact Chuck Krall

================================================ 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. 3256 15 33_John Ordman is out of the office.0_29_John_Ordman@READERSDIGEST.COM31_Thu, 16 Aug 2001 01:01:58 -0400389_us-ascii I will be out of the office starting 08/13/2001 and will not return until 08/20/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. 3272 45 25_Re: Phonetic search tools15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 16 Aug 2001 15:01:09 +1000601_- James,

I think there's a tool out there called something like SSA Name-3 (don't know the vendor) which can search phonetically for names, etc. Not sure if it's any good, but it's a start.



Raymond

> -----Original Message----- > From: James Sargent [SMTP:james.sargent@RCMP-GRC.GC.CA] > Sent: Thursday, August 16, 2001 4:54 AM > To: DB2-L@RYCI.COM > Subject: Phonetic search tools > > Hi all: > > Does anyone know of any Phonetic search tools for DB2?? > I think Oracle has one. > > Please advise > > Thanks > Regards > > James A Sargent > DB2 Support > 613-993-8824 [...] 3318 29 61_David Ayers/ISG/CORP/Highmark is out of the office. ( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 01:02:44 -0400421_US-ASCII I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 01:01 >>>

I will be out of the office starting 08/15/2001 and will not return until 08/17/2001.

For DB2 OS/390 assistance contact your support DBA. For Datajoiner / DB2 connect support contact Chuck Krall [...] 3348 28 43_John Ordman is out of the office. ( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 01:02:44 -0400358_US-ASCII I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 01:01 >>>

I will be out of the office starting 08/13/2001 and will not return until 08/20/2001.

I will respond to your message when I return. [...] 3377 86 57_Re: Mass deletes in Seg/partitioned tablespace. ( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 01:02:44 -0400343_US-ASCII I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 00:57 >>>

Mike,

Can we not have the same technique, as in seg tablespace, employed for mass deleting a partitioned tablespace. [...] 3464 61 35_Re: Phonetic search tools ( -Reply)9_Bill Boyd22_BBOYD@CO.LOUDOUN.VA.US31_Thu, 16 Aug 2001 01:13:00 -0400414_US-ASCII I will be out of the office until Monday August 20th. I will respond to your message when I return. If you require immediate assistance please call 703 777-0199.

>>> DB2-L 08/16/01 01:01 >>>

James,

I think there's a tool out there called something like SSA Name-3 (don't know the vendor) which can search phonetically for names, etc. Not sure if it's any good, but it's a start. [...] 3526 106 47_Re: Mass deletes in Seg/partitioned tablespace.14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Thu, 16 Aug 2001 15:21:20 +1000353_iso-8859-1 Rajendra,

If you need to empty a partitioned tablespace (in fact any type of TS) quickly and with minimal logging, you can use LOAD REPLACE LOG YES from an empty input file. By using LOG YES, no image copy is forced afterwards.

The only side effect is that the tablespace will be briefly unavailable while in utility mode. [...] 3633 15 59_Derez D. Lusk/Household International is out of the office.13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Thu, 16 Aug 2001 01:49:41 -0500389_us-ascii I will be out of the office starting 08/16/2001 and will not return until 08/20/2001.

any issues from DB2 please contact Marsupport

================================================ 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. 3649 105 31_Re: SQL procedure probelm again9_john king24_john_king@REDIFFMAIL.COM31_Thu, 16 Aug 2001 07:13:10 -0000440_- Yes David , I made a mistake that I forgot to register it in system tables. No I did that and getting the name added in system tables. But when I call it from a trigger it gave me error saying it should be called from a program. I could not understand why. It is clearly mentined that from trigger we can call sql procedure and for this I got the as sql code as -444. Could you share soem of your knowledges or experiences for this? [...] 3755 59 18_using collectionid20_Aysel COSKUN ISBILEN36_Aysel.COSKUNISBILEN@PAMUKBANK.COM.TR31_Thu, 16 Aug 2001 10:15:21 +0300446_iso-8859-1 Hi all,

We have 10000 packages approximately. We don't use collectionid in plan bind and package bind commands.

Example:

BIND PACKAGE(FDFOTU0) - MEMBER(FDFOTU0) - ACT(REP) - ... ... ...

BIND PLAN(FDFOPL01) - PKLIST( FDFOTU0.FDFOTU0, - FDFOTU1.FDFOTU1, - ... .. ) VALIDATE(RUN) ISOLATION(CS) ... ... ... ... We want to use collectionid at bind package time and then bind plan with the following format. [...] 3815 58 31_Re: SQL procedure problem again10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 16 Aug 2001 17:32:22 +1000507_us-ascii John,

-444 (as u might have found out) indicates that MVS is not able to find the external program defined for the procedure. From what I can see, the problem is NOT due to it being called from a trigger.

Can u verify that the External Name for the procedure in SYSROUTINES table is defined correctly, and also that the module is present in a dataset which is concatenated in the stored procedure address space ? Ur JCL might have created the load module in a different dataset. [...] 3874 40 27_Re: stored procedure survey17_jennifer jennifer22_jenni_jeni@HOTMAIL.COM31_Thu, 16 Aug 2001 07:32:57 +0000645_- Hello , yes I m much interested as I m facing probelm here. The mail id is jenni_jeni@hotmail.com. Thanks in advance. Jennifer



>From: Toni Addie >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: stored procedure survey >Date: Wed, 15 Aug 2001 12:26:09 -0500 > >We are in the process of figuring out how to successfully implement SPs >using WLM in our shop. We interesting in seeing how other companies have >implemented SP using WLM in their shop. If you have implemented SPs using >WLM at your shop and are interested in participating in a quick phone or [...] 3915 109 22_Re: using collectionid14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT31_Thu, 16 Aug 2001 09:43:56 +0100498_ISO-8859-1 Hi Aysel.

I really don't know the problem with the performance because our collections are very small (less than 5000 packages!) but in my company we are used to divide the collections according to the following standard:

For each project we have 3 collections collection-1 for batch; collection-2 for TP/CICS; collection-3 for datalayers, a datalayer is a called program specialized in DB2 access, in the last releases of DB2 they were promoted to stored procedures. [...] 4025 13 14_this is a test20_Aysel COSKUN ISBILEN36_Aysel.COSKUNISBILEN@PAMUKBANK.COM.TR31_Thu, 16 Aug 2001 11:23:57 +0300300_windows-1254 sory, this is a test message. Please disregard.

===============================================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. 4039 74 42_Re: Enabling Parallelism in Cognos Queries13_Edward Vassie20_E.Vassie@BIGFOOT.COM31_Thu, 16 Aug 2001 10:01:22 +0100538_iso-8859-1 If you are planning to use DEGREE=ANY, then you should also set the PARAMDEG ZPARM value. The value you need will be site-dependant, but will be in the range 1.5 to 2.5 times the number of CPU engines available to your DB2 LPAR.

The object of setting PARAMDEG is to prevent DB2 wanting to create an excessive number of tasks for parallelism. If too many tasks are created, your CPU costs go up a long way, and your throughput goes down. Using PARAMDEG will tame the beast and make parallelism work in your benefit. [...] 4114 63 38_FW: dwlist: DB2 OS/390 Row Id Question15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Thu, 16 Aug 2001 11:04:49 +0200152_iso-8859-1 Repost on behalf of Casey Gwozdz, a fellow datawarehouse lister. ---------------------------------------------------------------------------- 4178 48 40_Princeton - Move For DB2 - Extract Limit9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 16 Aug 2001 10:05:13 +0100567_us-ascii Hi, Princeton 'Move For DB2' V4.0.

We are using the product to extract a sub-set of data for a test system. We would like to be able to use the product so that a limitation is placed on the number of rows extracted from EACH table.

For example, we extract a sub-set based on a number of Customers from a Customer table. Each Customer may have multiple Contracts. Normally there is only one or a small number of Contracts per Customer, but occasionally a very large Customer will have hundreds of Contracts. We would like to set a limit on [...] 4227 157 16_Re: Use of Index16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 14:51:08 +0530328_- Jaydeep,

My thoughts goes on the same line as Troy has mentioned. This can be the only reason which describes the logic behind difference in accesspath mentioned by you. Just to add one more point, look if the FREQVAL stats are mentioned in the SYSIBM.SYSCOLDIST. Looks like it is.....

HTH, Regards Sanjeev [...] 4385 63 0_16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 14:54:36 +0530550_- Preethy,

Few i can find searching in www.google.com . Look at the same, it can be helpful.

Regards Sanjeev

> -----Original Message----- > From: Preethy B, J16 Chennai [SMTP:preethyb@MSDC.HCLTECH.COM] > Sent: Thursday, August 16, 2001 9:41 AM > To: DB2-L@RYCI.COM > Subject: > > Hi List, > > I am in the look out for sites or documents which can help me out > in > the conversion of IMS to DB2. Can anybody give me some info on this? I > have got an application which needs to b converted from IMS to DB2. It has > CICS as [...] 4449 111 34_Re: The optimiser mad a bad choice16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 15:06:03 +0530591_- Neil,

Please provide the definition of both the indexes and your queries. RUNSTAT informations before and after the bind and bind parameters will also be helpful to hit at the right reason for the problems.

Regards Sanjeev

> -----Original Message----- > From: neil briggs [SMTP:N.Briggs@CANON-EUROPA.COM] > Sent: Wednesday, August 15, 2001 2:46 PM > To: DB2-L@RYCI.COM > Subject: The optimiser mad a bad choice > > Dear List > > os390 2.7 - V5.1 DB2 > > I am hoping that somebody can shed a bit of light on my problem. I decided > for better or for worse (please [...] 4561 101 44_Re: Princeton - Move For DB2 - Extract Limit9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Thu, 16 Aug 2001 10:48:14 +0100474_ISO-8859-1 Hi Andy,

A few of options:

1. You could set rowlimit (on the table list) this will limit the number of rows per table

2. You could use SQL option (on the table list) to get a sort of 'top ten' contracts for a customer

3. You could create a table which lists all the customers you want to extract which would exclude 'large' customers, then use the tool to create an RI relationship between that table and the real customer table. [...] 4663 83 44_Re: Princeton - Move For DB2 - Extract Limit10_Alan Johns27_ajohns@PRINCETONSOFTECH.COM31_Thu, 16 Aug 2001 06:17:40 -0400576_ISO-8859-1 Hi Andy. This is probably an obvious question, but does the individual table row limit field on the 'Select Tables/Views for AD' screen not provide you with this functionality. (It should be noted that this limit is what IU call a soft limit, in that if extra rows above the defined limit are required in order to maintain referential integrity, as defined by the selected referential links, then they will be extracted, even if the table row limikt would be exceeded. The trick here is to tune the extract so that the table in question is being accessed as a [...] 4747 56 50_Freeing allocated resources in a stored procedure.13_Nyheim, Arild19_arild.nyheim@DNB.NO31_Thu, 16 Aug 2001 12:41:43 +0200513_iso-8859-1 One of our programmers has come up with this question, when calling a stored procedure from another.

Procedure A calls procedure B To get a resultset from B, proc A has to do the following, before fetch can be done:

1. EXEC SQL ASSOCIATE LOCATORS (:LOC) WITH PROCEDURE B END-EXEC

2. EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :LOC END-EXEC The problem: The first time A calls B it works OK, but if B is called a second time in the same unit of work, we receive a sqlcode -499 [...] 4804 29 11_LSN Commits16_Serge Brigljevic29_Serge.Brigljevic@SWISSCOM.COM31_Thu, 16 Aug 2001 12:51:29 +0200493_iso-8859-1 Can anybody give some more explanation about the LSN Commit, we know it occurs at the end of a Stored Procedure though the parm "NO COMMIT AT RETURN" is set up.

What is it ? What happens when it occurs ?

Best Regards

Serge Brigljevic Head of Database Services

Swisscom AG Telefon +41 31 342 54 88 Corporate Information & Technology Telefax +41 31 342 63 47 System Engineering & Operations E-Mail: serge.brigljevic@swisscom.com Postfach CH-3050 Bern [...] 4834 23 16_Websphere vs db28_duam lee20_duam_lee@HOTMAIL.COM31_Thu, 16 Aug 2001 10:56:12 +0000416_- Hello Can any body point me out to know about webs sphere's compattibility with db2. I mean db2 version 6 would be great with websphere 3.5 or db2 v7 would be great. Forgive me if this gives trouble to any body for finding out the site. I mean my question is based on the the websphere version and its comptibility with db2 version 6 and 7. Is there any probelm if we go for db2 version 6 with websphere 3.5. [...] 4858 38 22_About substr on column18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Thu, 16 Aug 2001 13:16:28 +0200350_iso-8859-1 I saw some week ago a question about substr on column, i just read a solution on a book that is interesting

SUBSTR(col,1,n) = "string" ===> col BETWEEN "string " AND "string99999" to respect the length of the column we add space and 9

SUBSTR(col,n,x) = "string" ===> col LIKE "____string%" put n underscore "_" at start [...] 4897 33 20_Re: Websphere vs db218_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Thu, 16 Aug 2001 13:18:35 +0200470_iso-8859-1 We are currently with WAS 3.5 and we use db2 6.1 (production)

And we are migrating to db2 7.1 and for the moment we have no problem we our test platform

...

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 [...] 4931 89 26_Re: About substr on column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 17:15:52 +0530294_iso-8859-1 Good, I think it is a good solution for not to make the predicate as stage 2 one which is there with SUBSTR. But for every code like this programmer needs to be explicitly told and every programs should document just with the query as comment that it is a substitue for SUBSTR. [...] 5021 16 29_which calatog for index cols?18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 16 Aug 2001 06:29:39 -0500403_- Hi Guys,

How to find out the col names that comprise an index. I tried SYSIBM.SYSINDEXES but did not find there. Looking for answer.



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. 5038 187 22_Re: using collectionid16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 17:27:03 +0530384_iso-8859-1 Aysel,

I think collection is purely logical. Whatever gets loaded in the EDM pool is either packages or plans. So even if you are using same collection id, only the required packages will be loaded during the execution time. Different collection is required when you are using the same packages in the different plans either in the same or different subsystem. [...] 5226 124 33_Re: which calatog for index cols?0_24_Bruce.Williamson@PSIR.IE31_Thu, 16 Aug 2001 12:58:33 +0100625_iso-8859-1 SYSKEYS

-----Original Message----- From: Rajendra Deshpande [mailto:rajendra_deshpande@HOTMAIL.COM] Sent: 16 August 2001 12:30 To: DB2-L@RYCI.COM Subject: which calatog for index cols?



Hi Guys,

How to find out the col names that comprise an index. I tried SYSIBM.SYSINDEXES but did not find there. Looking for answer.



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. [...] 5351 68 33_Re: which calatog for index cols?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 16 Aug 2001 17:34:46 +0530630_- Join SYSINDEXES and SYSKEYS on index names and creator.

SELECT * FROM SYSIBM.SYSINDEXES T1, SYSIBM.SYSKEYS T2 WHERE T2.IXNAME = T1.NAME AND IXCREATOR = T1.CREATOR AND T1.TBNAME = ? AND T1.TBCREATOR = ? AND T1.NAME = ? AND T2.CREATOR = ?

HTH Regards Sanjeev

> -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Sent: Thursday, August 16, 2001 5:00 PM > To: DB2-L@RYCI.COM > Subject: which calatog for index cols? > > Hi Guys, > > How to find out the col names that comprise an index. I tried > SYSIBM.SYSINDEXES but did not find there. Looking for answer. > > [...] 5420 32 33_Re: which catalog for index cols?13_Lynne Flatley17_LFlatley@NEFN.COM31_Thu, 16 Aug 2001 07:58:33 -0400633_us-ascii SYSIBM.SYSKEYS

> -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Sent: Thursday, August 16, 2001 7:30 AM > To: DB2-L@RYCI.COM > Subject: which calatog for index cols? > > Hi Guys, > > How to find out the col names that comprise an index. I tried > SYSIBM.SYSINDEXES but did not find there. Looking for answer. > > > 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. [...] 5453 40 20_Re: Websphere vs db213_Lynne Flatley17_LFlatley@NEFN.COM31_Thu, 16 Aug 2001 08:00:43 -0400552_us-ascii If you mean DB2 on NT or UNIX, version 6 is going off of support December 31, 2001 so I would strongly suggest that you go with DB2 version 7.

> -----Original Message----- > From: duam lee [SMTP:duam_lee@HOTMAIL.COM] > Sent: Thursday, August 16, 2001 6:56 AM > To: DB2-L@RYCI.COM > Subject: Websphere vs db2 > > Hello Can any body point me out to know about webs sphere's compattibility > with db2. I mean db2 version 6 would be great with websphere 3.5 or db2 v7 > would be great. Forgive me if this gives trouble to any body for [...] 5494 28 17_URGENT: SMS & DB212_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Thu, 16 Aug 2001 08:04:09 -0400436_us-ascii I need a response ASAP on this question from anybody,

With DB2 running under SMS, what happens when a create tablespace is executed and the primary amount is not available on a single volume. I am told by the SMS guys that SMS will allocate the space across multiple volumes but how does DB2 perceive that,since under normal operations for NPTS DB2 will try to make a new dataset when going to a second volume..... [...] 5523 118 33_Re: which catalog for index cols?21_Sean M O'Keefe/AC/VCU15_sokeefe@VCU.EDU31_Thu, 16 Aug 2001 08:12:16 -0400388_us-ascii Rajendra, You need three catalog tables to find out the columns in an index for DB2 OS/390. SYSTABLES to determine the table, SYSINDEXES to determine the index data for each table, SYSKEYS to determine the columns defined to each index.

There is a query in Mr. Yevich's book, "DB2 Answers!" that shows the joins. I can send this to you directly later if you need it. [...] 5642 53 33_AW: which calatog for index cols?12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 16 Aug 2001 14:06:20 +0200622_iso-8859-1 Rajendra,

SYSIBM.SYSKEYS contains one row for each column of an index key.

Here is an example:

SELECT IXCREATOR, XNAME, COLNO, COLNAME FROM SYSIBM.SYSKEYS WHERE IXNAME = yourindexname ORDER BY COLNO ASC for read only with ur ;

HTH.

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

-----Ursprüngliche Nachricht----- Von: Rajendra Deshpande [mailto:rajendra_deshpande@HOTMAIL.COM] Gesendet am: Donnerstag, 16. August 2001 13:30 An: DB2-L@RYCI.COM Betreff: which calatog for index cols? [...] 5696 107 16_Re: Use of Index13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 15 Aug 2001 14:49:21 -0500463_us-ascii Jaydeep,

Check the explain, such as page_range etc. to see that it is not performing a scan of a single partition. I'm guessing that would explain why (what appears to be) a tablespace scan of a 65 million row table can perform as well as a matching index scan.

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 [...] 5804 32 26_JDBC Drivers - App vs. Net14_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM31_Thu, 16 Aug 2001 08:27:55 -0400319_us-ascii Hi,

Can anybody explain to me the difference between a JDBC App driver and a JDBC Net driver? I've looked in the UDB v7.2 manuals for information, and all I've been able to find is that the "app" driver is an "application" driver (type 2), and that the "net" driver is an "applet" driver (type 3). [...] 5837 119 37_Re: AW: which calatog for index cols?9_Chris Tee23_chris.tee@UK.ZURICH.COM31_Thu, 16 Aug 2001 13:24:01 +0100401_iso-8859-1 It would probably be more useful to ORDER BY COLSEQ ( the order of the column in the index not the table)

Chris





"Peter, Georg" To: DB2-L@RYCI.COM Subject: [DB2-L] AW: which calatog for index Sent by: DB2 cols? Data Base Discussion List



16/08/01 13:06 Please respond to DB2 Data Base Discussion List [...] 5957 23 28_DB2 for AIX command Question14_Curran, John F29_john.f.curran@UK.FID-INTL.COM31_Thu, 16 Aug 2001 13:30:39 +0100525_iso-8859-1 Hi

I came working in a DB2 AIX environment and came across the following command db2icfg. The command resides in the install instance directory. Can anybody tell me what it does

Thanks in advance



John Curran

Fidelity Investments Limited

================================================ 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. 5981 37 37_AW: AW: which calatog for index cols?12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 16 Aug 2001 14:38:26 +0200452_iso-8859-1 Ooops... my fault..

You are right. Here is the corrected query:

SELECT IXCREATOR, XNAME, COLSEQ, COLNAME FROM SYSIBM.SYSKEYS WHERE IXNAME = yourindexname ORDER BY COLSEQ ASC for read only with ur ;.

Georg



-----Ursprüngliche Nachricht----- Von: Chris Tee [mailto:chris.tee@UK.ZURICH.COM] Gesendet am: Donnerstag, 16. August 2001 14:24 An: DB2-L@RYCI.COM Betreff: Re: AW: which calatog for index cols? [...] 6019 65 34_Odp: which calatog for index cols?15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL31_Thu, 16 Aug 2001 13:55:52 +0200533_iso-8859-2 Try SYSIBM.SYSKEYS





Rajendra Deshpande DW: (UDW: Piotr Wysłane przez: DB2 Tarnowski/GDYNIA/PROKOM/PL) Data Base Discussion Temat: which calatog for index cols? List



16-08-01 13:29 Odpowiedz do DB2 Data Base Discussion List











Hi Guys,

How to find out the col names that comprise an index. I tried SYSIBM.SYSINDEXES but did not find there. Looking for answer. [...] 6085 133 26_JDBC Drivers - App vs. Net18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Thu, 16 Aug 2001 14:57:34 +0200300_iso-8859-1 That mean for a driver of type you need on the client an installation of the native db2 drivers.

For type 3 (net) the drivers are send to the client they are in java and native format. But you don't need to install the native library.

A type 4 is written in pur Java... [...] 6219 46 32_Re: DB2 for AIX command Question19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 16 Aug 2001 13:59:35 +0100666_iso-8859-1 John,

I reckon it's used for configuring instances - but I'm sure you'd worked that out for yourself.

The DB2 documentation is silent on the subject.

Phil

On Thu, 16 Aug 2001 13:30:39 +0100, Curran, John F wrote:

>Hi > >I came working in a DB2 AIX environment and came across the following >command db2icfg. The command resides in the install instance directory. Can >anybody tell me what it does > >Thanks in advance > > > John Curran > >Fidelity Investments Limited > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at [...] 6266 15 43_Re: Where are DIAnnnnn messages documented?13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Thu, 16 Aug 2001 08:02:56 -0500489_- I ran the DIA3208E thru the "Search Knowledge Base" and here is a link I found. Not sure it relates to you, but it might.

http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/aparlib. d2w/display_apar_details?aparno=IY15212

================================================ 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. 6282 41 41_Filter factors and other optimizer stuff.15_Smith, Dennis J22_dennis.j.smith@EDS.COM31_Thu, 16 Aug 2001 08:18:57 -0500576_iso-8859-1 There used to be a "blue book" (old IBM internal) that gave a brief descriptions of the optimizer default filter factors, filter factors in general and other such nice things to know about the optimizer. I was wondering if there is anything like that now. I am not looking for detail internal guts, just information to help better understand access path selections, or (do I dare) predict what the optimizer may do for a SQL that is being developed. Rules and such for the different joins and stuff like that. An over view of what the optimizer looks for (at) [...] 6324 22 45_00E70005 - Any Help Would be Appreciated !!!!16_Michael McKinney16_mmckinne@CSC.COM31_Thu, 16 Aug 2001 09:14:33 -0400520_us-ascii Can anyone shed some light on an 00E70005 ?

According to the Message and Code Manual, a 00E70005 means: A relational data system (RDS) subcomponent internal inconsistency was detected.

Anyone experienced this before ?

Thanks in advance,

Mike

================================================ 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. 6347 56 32_Re: DB2 for AIX command Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 16 Aug 2001 14:39:25 +0100406_iso-8859-1 John

Hi. From what I can see (like everyone else) it is for config of the instance ...

I've just run it and it did nothing ... vanished ..

I'll dig some more here.

Leslie

-----Original Message----- From: Curran, John F [mailto:john.f.curran@UK.FID-INTL.COM] Sent: Thursday, August 16, 2001 1:31 PM To: DB2-L@RYCI.COM Subject: DB2 for AIX command Question [...] 6404 54 49_Re: 00E70005 - Any Help Would be Appreciated !!!!23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 16 Aug 2001 14:40:31 +0100439_- Mike

we had this (I'm almost certain) a while back. The last set of maintenance we rolled out fixed the problem ...

Sorry I cannot be of much more use. (we rolled out a lot of maintenance a month or so ago)

Leslie

-----Original Message----- From: Michael McKinney [mailto:mmckinne@CSC.COM] Sent: Thursday, August 16, 2001 2:15 PM To: DB2-L@RYCI.COM Subject: 00E70005 - Any Help Would be Appreciated !!!! [...] 6459 113 30_Re: JDBC Drivers - App vs. Net13_Tzachi Nissim21_tzachi@ATTUNITY.CO.IL31_Thu, 16 Aug 2001 16:44:06 +0200453_iso-8859-1 I believe what you say is not entirely accurate. Type 3 are also pure Java drivers. Its just that their network protocol is not the database vendor's network protocol, so there needs to be some server component which talks to the database.

Type 3 and 4 are pretty comparable in terms of performance and maintenance. I would say that you never want a type 2 driver if you can get a type 3 or 4 one - even if you are on the server. [...] 6573 45 49_Re: 00E70005 - Any Help Would be Appreciated !!!!12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 16 Aug 2001 09:57:26 -0400583_us-ascii What CSECT and offset??







Michael McKinney @RYCI.COM> on 08/16/2001 09:14:33 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: 00E70005 - Any Help Would be Appreciated !!!!



Can anyone shed some light on an 00E70005 ?

According to the Message and Code Manual, a 00E70005 means: A relational data system (RDS) subcomponent internal inconsistency was detected. [...] 6619 35 30_Re: JDBC Drivers - App vs. Net18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Thu, 16 Aug 2001 15:55:38 +0200304_iso-8859-1 When I write something I verify it before.

And what I said about type 3 is the specifiation. You can check it by your self unzipping the content of the db2 drivers. And decompile it with jad. I've already do that because of a problem with visual age and the support can't help me. [...] 6655 112 44_Re: Princeton - Move For DB2 - Extract Limit9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 16 Aug 2001 14:57:31 +0100438_us-ascii Hi Alan, Obviously I mis-interpreted the one sentence describing this in the manual :-)

The Child Limit parameter appears to be the one we were looking for (limits the number of rows extracted from a child table).

Cheers, Andy











Alan Johns on 16/08/2001 11:17:40

Please respond to DB2 Data Base Discussion List [...] 6768 74 33_WebLogic - DB2 Connection Problem0_24_Sridhar_Kondusamy@I2.COM31_Thu, 16 Aug 2001 19:43:15 +0530370_us-ascii I am trying to connect to a DB2 database on a NT machine from weblogic 6.0 running on the AIX machine.I have set the path for the DB2 jdbc driver in startWeblogic.sh.When trying to connect it said that it needs libdb2jdbc.so files.So I set the environment in the .profile file foe the LIBPATH pointing to the lib folder of db2 which consists of this file. [...] 6843 185 37_Re: AW: which calatog for index cols?0_24_Sridhar_Kondusamy@I2.COM31_Thu, 16 Aug 2001 19:49:32 +0530334_iso-8859-1 Try

select tabname,colnames from syscat.indexes

Sridhar Kondusamy i2 Technologies Bangalore - 560 025

Ph : 2217990 (Extn : 140)

Dream what you want to dream; go where you want to go; be what you want to be, because you have only one life and one chance to do all the things you want to do. [...] 7029 53 30_Know when REORG last performed20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM31_Thu, 16 Aug 2001 10:20:39 -0400115_ISO-8859-1 Can anyone tell me how to tell when the last REORG was performed on a tablespace?

thank you, Lisa 7083 47 52_JAVA/JDBC at WIN/2000 try to access DB2 R6 at OS/39017_Islam Abdel-Aleem17_aleemi@EG.IBM.COM31_Thu, 16 Aug 2001 17:09:23 -0700626_us-ascii Hello , thanks for your time and help . I had the following case ==================== JAVA program running at WIN/2000 connected to ( S/390 OS/390 R2.9 , JDK R1.3 , UNIX/OpenEdition , DB2 R6 and TCP/IP ) via LAN / TCP/IP --------------------------------------------------------------- Could you please answer the following . 1- Are there a special port at OS/390 TCP/IP I can use to access DB2/JDBC through it ? 2- Could you please guide me to the right documentation for that setting or presentation ? 3- Are there a setting at WIN/2000 (C:\WINNT\SYSTEM32\DRIVERS\ETC\SERVICES ) to enable a port for JDBC or at [...] 7131 107 38_Re: dwlist: DB2 OS/390 Row Id Question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 16 Aug 2001 09:12:38 -0500488_iso-8859-1 There are a few issues I can see with using ROWID to identify a dimension:

1. The datatype is stored as a VARCHAR(17), and therefore can takeup a maximum of 19 bytes of storage in the Fact table. That is purely for one dimension. If you have 6 dimensions then multiply by 6. If your dimension can have less than 32,768 values then use SMALLINT (2 bytes) or for 2 billion, use INTEGER (4 bytes). You can use identity columns to guarantee dimension table uniqueness. [...] 7239 74 25_Re: Phonetic search tools0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 16 Aug 2001 09:26:33 -0500315_us-ascii I don't remember, has anyone mentioned the Soundex algorithm. It was created for the US census, long before (1890's?) computers, but still works reasonably well. It is a method of compressing (last) names into code groups that sound alike. Do a web search there are several sites with articles on it. [...] 7314 76 30_Re: JDBC Drivers - App vs. Net0_24_db46@DAIMLERCHRYSLER.COM31_Thu, 16 Aug 2001 10:27:52 -0400451_us-ascii Basically, the 'app' driver is for applications on your host machine (PC or work station) where the 'net' driver is for databases on a network server.

Dean







Bill Gallagher cc: Sent by: DB2 Data Subject: JDBC Drivers - App vs. Net Base Discussion List



08/16/01 08:27 AM Please respond to DB2 Data Base Discussion List [...] 7391 40 34_Re: Know when REORG last performed9_Ray Janes26_ray.h.janes@EXXONMOBIL.COM31_Thu, 16 Aug 2001 09:35:36 -0500446_us-ascii It is recorded in SYSIBM.SYSCOPY. However, your MODIFY utility deletes these records, so you will only have a finite period of time recorded.





"Westcott-Dryer, Lisa" cc: Sent by: DB2 Data Base Subject: Know when REORG last performed Discussion List





08/16/01 09:20 AM Please respond to DB2 Data Base Discussion List [...] 7432 38 34_Re: Know when REORG last performed11_patty rizzi19_patty_db2@YAHOO.COM31_Thu, 16 Aug 2001 07:41:10 -0700425_us-ascii Hi Lisa,

to know when a Reorg was performed you neeed to look the catalog table : sysibm.syscopy if the field ictype='w' or ictype='x' means a reorg was performed , w with log(no), x with log(yes).

Bye Patrizia

--- "Westcott-Dryer, Lisa" wrote: > Can anyone tell me how to tell when the last REORG > was performed on a tablespace? > > thank you, > Lisa > [...] 7471 28 34_Re: Know when REORG last performed11_Jeff Schade21_JSCHADE@MAIL2.PCH.COM31_Thu, 16 Aug 2001 10:39:14 -0400430_US-ASCII Lisa,

The information is kept in SYSIBM.SYSCOPY as long as you have not use the MODIFY utility to clean-up this info. You want to look at the rows with the appropriate DBNAME and TSNAME and ICTYPE of W (Reorg Log No) or X (Reorg Log Yes).

Jeff Schade PCH

>>> Lisa_Westcott-Dryer@FOREMOST.COM 08/16/01 10:20AM >>> Can anyone tell me how to tell when the last REORG was performed on a tablespace? [...] 7500 22 30_Know when REORG last performed19_José Ignacio Ossuna16_jiossuna@ATCA.ES31_Thu, 16 Aug 2001 16:46:54 +0200429_iso-8859-1 If registered, look for field ICTYPE in SYSIBM.SYSCOPY. You will find W when Reorg Log (NO) and X when Reorg Log (YES)

Regards, José Ignacio Ossuna







===============================================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. 7523 26 34_Re: Know when REORG last performed13_Hanne Lyssand10_han@VPS.NO31_Thu, 16 Aug 2001 16:36:49 +0200678_us-ascii Check: SYSIBM.SYSCOPY When you preform reorg there will be and entry with ICTYPE : W or X W : reorg log no X: reorg log yes

best regards Hanne





********************************************************************** For Your service, we have checked this message by the virus-programs used by the VPS Virusadm. http://www.vps.no **********************************************************************

================================================ 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. 7550 49 43_Re: Where are DIAnnnnn messages documented?20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Thu, 16 Aug 2001 16:47:10 +0200473_iso-8859-1 I can't really answer your question. But for the specific DIA error you are getting I can tell you it's not a real problem. It's a message that you always get when working on aix on a SMP machine. I read it was fixes by a AIX fix (not DB2 but AIX) but I can't remember or find the APAR number . However everything is working fine even when you get that message it's only a bother to have alot of them in your diag. If I'll find the APAR number I'll tll you [...] 7600 222 52_Re: Doubt in performance of the following statements13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 16 Aug 2001 09:47:34 -0500509_us-ascii *** This post was rejected due to exceeding 250 lines (which I can't believe). So I am resending *****

Sridhar, They are the same SQL, except one is a cursor and the other is a singleton select (that is missing a FROM clause). Is that correct?

The only differences are that FETCH 1 ROW ONLY on OS/390 implies OPTIMIZE FOR 1 ROW, and that may encourage DB2 to disable list and sequential prefetch and multi-index access. On non-OS/390 they are considered separate for optimization. [...] 7823 153 0_0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 16 Aug 2001 09:52:02 -0500348_us-ascii Here is my 'standard' response to that question. Also check the list archive, this question has been asked many times. *************** We did a similar migration a few years ago. It wasn't from IMS, but from an IMS like DB, so I can't suggest a tool. However I would like to suggest some points you and your conversion team consider. [...] 7977 43 41_Deadlocks on SYSCOPY : Suggestions Please19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Thu, 16 Aug 2001 15:50:44 +0100398_iso-8859-1 We've hit a situation where we are experiencing a deadlock on SYSCOPY during our overnight image copy processing.

We have two databases DPOPR001 and DPOPR101 (these follow our naming standards, and everything means something ).

The image copy job follows the traditional pattern -

MODIFY RECOVERY COPY QUIESCE

for all (5) tablespaces in the database. [...] 8021 82 34_Re: Know when REORG last performed11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 16 Aug 2001 09:58:55 -0500516_iso-8859-1 Image Copy Types

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



ICTYPE Type of operation: A - ALTER B - REBUILD INDEX D - CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility) F - COPY FULL YES I - COPY FULL NO P - RECOVER TOCOPY or RECOVER TORBA (partial recovery point) Q - QUIESCE R - LOAD REPLACE LOG(YES) S - LOAD REPLACE LOG(NO) T - TERM UTILITY command (terminated utility) W - REORG LOG(NO) X - REORG LOG(YES) [...] 8104 136 25_Re: Phonetic search tools13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Thu, 16 Aug 2001 10:06:32 -0500661_iso-8859-1 DB2 Now has a Soundex Function. See Below From the SQL Reference: Phil Jackson -------------------------- SQL Reference

SOUNDEX >>-SOUNDEX--(--expression--)----------------------------------->< The schema is SYSFUN. Returns a 4 character code representing the sound of the words in the argument. The result can be used to compare with the sound of other strings.

The argument can be a character string that is either a CHAR or VARCHAR not exceeding 4 000 bytes. The result of the function is CHAR(4). The result can be null; if the argument is null, the result is the null value. The SOUNDEX function is useful for finding strings [...] 8241 22 28_Find out all connected users10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Thu, 16 Aug 2001 08:04:00 -0700556_us-ascii Can anybody tell me how to find out all the users connected to a udb database from the control center.

Thanks,

Mathai

__________________________________________________ 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. 8264 135 20_Re: Websphere vs db214_Philip Gunning24_philip.gunning@QUEST.COM31_Thu, 16 Aug 2001 08:09:31 -0700362_iso-8859-1

Duam, DB2 UDB V7.2 is compatible with Webshpere 3.5 and can be purchased with DB2 UDB bonus pack. Check the DB2 UDB V7.2 announcment,201-128 .I believe it supports Webshere 4.0 but have not verified that. HTH Phil



-----Original Message----- From: duam lee To: DB2-L@RYCI.COM Sent: 8/16/01 3:56 AM Subject: Websphere vs db2 [...] 8400 51 32_Re: Find out all connected users17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Thu, 16 Aug 2001 10:15:48 -0500402_us-ascii click on the instance to highlight it then right click on it and choose list applications. It will give you the application that is currently connected and the database they are connected to. Assuming that you have more than one database per instance. To determine which user is what process you are going to have to do some snapshots to determine what user ID goes with what process id. [...] 8452 209 27_Re: SQL Request (was blank)13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 16 Aug 2001 10:05:52 -0500434_iso-8859-1 Subrata,

Yes this can be done in a SQL statement. You don't say what release or platform. This is valid SQL for all platforms, although I would prefer to use a correlated table expression rather than a join, but these have serious performance issues in V6 OS/390 DB2. If there are only a small number of rows per group, then the minor cartesian product introduced by the join should not be a performance issue. [...] 8662 200 0_12_Carl Shinkle24_carl.shinkle@MANTECH.COM31_Thu, 16 Aug 2001 11:31:37 -0400672_us-ascii If you'd like to contact me off-line, our organization does conversions/migrations for a living. Carl Shinkle



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Rohn.Solecki@MTS.MB.CA Sent: Thursday, August 16, 2001 10:52 AM To: DB2-L@RYCI.COM Subject:

Here is my 'standard' response to that question. Also check the list archive, this question has been asked many times. *************** We did a similar migration a few years ago. It wasn't from IMS, but from an IMS like DB, so I can't suggest a tool. However I would like to suggest some points you and your conversion team consider. [...] 8863 47 0_13_Harry Woelfer27_Harry_Woelfer@MCCORMICK.COM31_Thu, 16 Aug 2001 11:35:10 -0400322_us-ascii We eliminated IMS using a software product called DL/2. It allows you to convert your databases from IMS to DB2 without changing your programs. DL/2 emulates IMS, so the programs still think they're reading an IMS database. DL/2 is marketed by the Circle Group in England. Here is a URL from their web site: [...] 8911 120 25_Re: Phonetic search tools0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 16 Aug 2001 10:42:04 -0500634_iso-8859-1 Cool ... which version(s) is it available in. I just searched my OS390 V6 manuals (including Text Extender) and only found 1 reference to it, in a list of ODBC functions as "SQL_FN_STR_SOUNDEX".

Although a built in function is nice, I think I still prefer the do-it-yourself version for this. Soundex was originally designed for primarily for names of "English" derivation. It has problems with names from other ethnic backgrounds. Since the original algorithm was defined there have been several extensions defined. Coding your own allows you to pick the extensions you want/need and any new ones you may want [...] 9032 124 20_FW: Last reorg entry20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM31_Thu, 16 Aug 2001 11:50:37 -0400474_ISO-8859-1 Thank you everyone, for the information. I was able to figure out the last REORG and move forward. Nice.

Lisa

-----Original Message----- From: Yau, Jeremy [mailto:Jeremy_Yau@bmc.com] Sent: Thursday, August 16, 2001 10:48 AM To: Westcott-Dryer, Lisa Subject: Last reorg entry



Lisa,

Do the following query against SYSIBM.SYSCOPY table, you should be able to get all the reorgs performed on the object you are interested in. [...] 9157 78 31_IBM Reorg with PREFORMAT option14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Thu, 16 Aug 2001 08:53:15 -0700363_iso-8859-1 Hi list-mates,

Has anyone used the PREFORMAT option and could you share your experience?

Did you find any benefit for high insertion tables?

Were there any down sides to preformating?

Thanks,

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

9236 45 58_Re: Visual Studio, DB2 Connect, and DB2 on OS/390 anybody?15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Thu, 16 Aug 2001 08:58:09 -0700539_- Greg,

Benchmark your connections under the Client Configuration Assistant using the TEST button or from the Command Line Processor by keying in the full connect string. That would eliminate your Visual Studio and any metadata from suspicion. I believe you will find your connection time will be fast. Steve

-----Original Message----- From: Greg DiGiorgio [mailto:gdigior@CI.NEWPORT-NEWS.VA.US] Sent: Wednesday, August 01, 2001 8:02 AM To: DB2-L@RYCI.COM Subject: Visual Studio, DB2 Connect, and DB2 on OS/390 anybody? [...] 9282 191 25_Re: Phonetic search tools0_19_csutfin@AMSOUTH.COM31_Thu, 16 Aug 2001 11:11:00 -0500494_iso-8859-1 Rohn:

I have the SOUNDEX conversion routine coded in COBOL that can be called from another program. It is a rewrite of the old CICS DFHPHN function. I would be happy to send you the routine off list if you wish.

Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214









Rohn.Solecki@ MTS.MB.CA To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: Phonetic search tools Discussion List [...] 9474 153 25_Re: Phonetic search tools13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Thu, 16 Aug 2001 11:21:56 -0500442_iso-8859-1 This is found in the documentation that comes with the DB2 UDB Client software V 7.0 I have on my PC. I assume that the Soundex Function is valid both for NT and OS/390.

Phil J. --------------







Cool ... which version(s) is it available in. I just searched my OS390 V6 manuals (including Text Extender) and only found 1 reference to it, in a list of ODBC functions as "SQL_FN_STR_SOUNDEX". [...] 9628 33 35_Re: IBM Reorg with PREFORMAT option12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 16 Aug 2001 11:16:00 -0400349_- There is a presentation on DB2-L DOC: http://jupiter.ryci.com/cgi/wa.exe?A2=ind0008&L=DB2-L-DOCUMENTS&P=R2 which touches on the subject. We used it to try to help a long running insertion job, but it didn't make any difference in the runtime. We didn't think it would, because the job was mostly waiting for SYNC/IO, not for page formatting. [...] 9662 84 35_Re: IBM Reorg with PREFORMAT option12_Susan Lawson22_lawson_susan@YAHOO.COM31_Thu, 16 Aug 2001 09:22:05 -0700548_us-ascii Daryl,

I have used PREFORMATting at several accounts with high insert volumes. We often see an 8-10% performance improvement for high volume batch inserts.

The time for doing the PREFORMATting (either LOAD or REORG) was minimal. However, when we had preformatting occurring for objects that were on the same volumes as objects with online activity - the I/O performance for the access against those objects suffered because the device was 'busy'. However, depending on your environment, this may or may not be an issue. [...] 9747 32 33_Re: which calatog for index cols?16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Thu, 16 Aug 2001 09:29:06 -0700645_iso-8859-1 Check SYSKEYS

-----Original Message----- From: Rajendra Deshpande [mailto:rajendra_deshpande@HOTMAIL.COM] Sent: Thursday, August 16, 2001 4:30 AM To: DB2-L@RYCI.COM Subject: which calatog for index cols?



Hi Guys,

How to find out the col names that comprise an index. I tried SYSIBM.SYSINDEXES but did not find there. Looking for answer.



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.. [...] 9780 57 0_12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 16 Aug 2001 19:26:09 +0200440_windows-1255 Hi, We are now at the final stages of a project like that. We use DL2 by CIRCLE ( --- I don't work for them).

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "S, Sanjeev (CTS)" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 16, 2001 11:24 AM [...] 9838 74 0_14_Dean Montevago18_DMonteva@VNSNY.ORG31_Thu, 16 Aug 2001 12:36:21 -0400614_windows-1255 signoff

-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: Thursday, August 16, 2001 1:26 PM To: DB2-L@RYCI.COM Subject:



Hi, We are now at the final stages of a project like that. We use DL2 by CIRCLE ( --- I don't work for them).

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "S, Sanjeev (CTS)" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, August 16, 2001 11:24 AM [...] 9913 28 49_SQL Stored Procedures vs. Cobol Stored Procedures16_Richard Gassaway19_rgassaway1@IWON.COM31_Thu, 16 Aug 2001 13:16:11 -0400531_ISO-8859-1 Hello list,

Has anyone come across any performance statistics or any other documentation that lists which type of stored procedure is more efficient(Cobol vs SQL procedures)? I understand the benefit of Cobol stored procedures having the ability to access VSAM but any other benefits?

Thanks for any input. Rick





Visit iWon.com - the Internet's largest guaranteed cash giveaway! Click here now for your "Thank You" gift: http://www.iwon.com/giftcenter/0,2612,,00.html?t_id=20157 [...] 9942 90 45_Re: Filter factors and other optimizer stuff.10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Thu, 16 Aug 2001 12:21:20 -0500371_us-ascii Dennis,

Try Frank Ingrassia's High Performance Optimization DB2 for OS/390 class. I took his class twice & he has the very best filter factor & CPU cost estimate section I have seen anywhere. I have since used these calculations & they have proven true & a great help in my 'what have you saved the company' notes my manager requires every so often. [...] 10033 53 38_problems with Unicode in DB2 OS/390 V60_19_Tim.Lowe@STPAUL.COM31_Thu, 16 Aug 2001 12:30:35 -0500675_us-ascii Can anyone help me?

I have an application that has a CHAR(100) column defined "FOR BIT DATA" that they are trying to use to store UNICODE data in. The data appears to be stored in the DB2 table correctly, but when they query the data through JDBC through DB2 connect, they get a string of hex characters. For example, they send: set n_cc_account ='C7QdKNKKsJ1VPwgKex+lI1+taSHOQSIi' And, when they select from this column, they get back a value of: x'C3F7D884D2D5D2D2A2D1F1E5D7A687D285A74E93C9F14EA381E2C8D6D8E2C98940404040404040 40404040404040404040404040404040404040404040404040404040404040404040404040404040 404040404040404040404040404040404040404040' [...] 10087 57 33_Re: which calatog for index cols?16_Bhogale, Pushkar24_Bhogale.Pushkar@MBCO.COM31_Thu, 16 Aug 2001 12:33:36 -0500465_iso-8859-1 Also KEYSEQ column on the table SYSIBM.SYSCOLUMNS lets you know if it's a part of the primary key which is most often defined as a Unique index anyway.

Pushkar Bhogale I. B. S. (For Miller Brewing Company) Database Administration, Xn. - 2048



-----Original Message----- From: Duane Lee - ATCX [mailto:DLee@MAIL.MARICOPA.GOV] Sent: Thursday, August 16, 2001 11:29 AM To: DB2-L@RYCI.COM Subject: Re: which calatog for index cols? [...] 10145 42 20_Re: Websphere vs db213_Villa Horacio17_hvilla@TTI.COM.AR31_Thu, 16 Aug 2001 14:57:34 -0300539_iso-8859-1 We're working OK with WS 3.5 & DB2 7.2 on NT & AIX. > -----Original Message----- > From: duam lee [SMTP:duam_lee@HOTMAIL.COM] > Sent: Thursday, August 16, 2001 6:56 AM > To: DB2-L@RYCI.COM > Subject: Websphere vs db2 > > Hello Can any body point me out to know about webs sphere's compattibility > with db2. I mean db2 version 6 would be great with websphere 3.5 or db2 v7 > would be great. Forgive me if this gives trouble to any body for finding > out > the site. I mean my question is based on the the websphere version [...] 10188 63 45_Re: Deadlocks on SYSCOPY : Suggestions Please14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Thu, 16 Aug 2001 13:16:34 -0500326_us-ascii We have had to move our MODIFY RECOVERY jobs outside of the batch cycle (when imagecopies and reorgs are done) and do them during the day when reorgs and imagecopies are not attacking the SYSCOPY and SYSLGRNX tables. This was to avoid all the contention done by logging the utility activities in the two tables. [...] 10252 68 25_Re: Phonetic search tools9_Judy Nall21_judynall@TELEPORT.COM31_Thu, 16 Aug 2001 13:49:16 -0400665_us-ascii James,

The company name is SearchSoftwareAmerica, the product is called: SSA Name3. www.searchsoftware.com

Seeya,

Judy Nall CBI Training and Consulting 350 S. Center Street Suite 500 Reno, NV 89501 866.CBI.4YOU (866.224.4968) www.cbi4you.com

Bell, Raymond W wrote:

> James, > > I think there's a tool out there called something like SSA Name-3 (don't > know the vendor) which can search phonetically for names, etc. Not sure if > it's any good, but it's a start. > > Raymond > > > -----Original Message----- > > From: James Sargent [SMTP:james.sargent@RCMP-GRC.GC.CA] > > Sent: Thursday, August 16, 2001 4:54 AM > > [...] 10321 28 38_Abended Reorg utility - Help required.24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Thu, 16 Aug 2001 12:51:07 -0700539_us-ascii Hi ,

We have one Reorg utility abended because of space problem ( SB37) in SYSUT1 .

But one of our fellow DBA's accidentally terminated the utility. The tablespace dont have an image copy as well . But we have the SYSREC catalogued in the reorg step and it exist. Is there anyway we can load the data back to the table from the SYSREC output. The format of the data in the SYSREC1 is a lot different from the usual DSNTIAUL format and hence LOAD utility cannot be used. The data is in its own encrypted format. [...] 10350 50 42_Re: Abended Reorg utility - Help required.16_Pranav M. Sampat21_PSampat@PACIFICEX.COM31_Thu, 16 Aug 2001 13:06:22 -0700483_iso-8859-1 You can use LOAD utlity. One of the syntax variations "FORMAT UNLOAD" will load your output from REORG utility

Thanks, Pranav

-----Original Message----- From: Mahadevan Krishnamoorthy [mailto:mahadevan.krishnamoorthy@FARMERSINSURANCE.COM] Sent: Thursday, August 16, 2001 12:51 PM To: DB2-L@RYCI.COM Subject: Abended Reorg utility - Help required.



Hi ,

We have one Reorg utility abended because of space problem ( SB37) in SYSUT1 . [...] 10401 22 46_Number of Tables Allowed in Join for Version 714_Johnson, Julie23_julie.johnson@UCONN.EDU31_Thu, 16 Aug 2001 16:37:19 -0400352_iso-8859-1 Hi,

I've looked through the v7 manuals and I can't find any explicit reference to the number of tables allowed in a join. I need to know if the 15 table limit in v5 (via UQ39732) is to be carried forward in future versions of DB2. If anyone knows the answer, or where to point me to find it, I would appreciate hearing from you! [...] 10424 81 42_Re: Abended Reorg utility - Help required.24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Thu, 16 Aug 2001 13:59:57 -0700499_us-ascii LOAD with FORMAT UNLOAD worked well.

Thanks for all suggestions

Maha





"Jeff A L'Italien" To: cc: Subject: Re: Abended Reorg utility - Help 08/16/01 01:14 PM required.













Maha,

Yes, there is a way you can use the load utility to get the data back. By using the FORMAT UNLOAD option, this should work. Give it a try. [...] 10506 28 20_Recruiter needs help12_Marc Sennett23_msennett@TEKSYSTEMS.COM31_Thu, 16 Aug 2001 17:07:57 -0500517_us-ascii All,

Good afternoon.

This may or may not be an arena in which you can help me, but I figure it is worth a try. I am a recruiter for Maxim Group, and we have a client with an urgent need for an IDMS DB2 DBA in the greater Cincinnati area. Do you know of any people with this skillset looking for work, or of any resources where I might find them? If so, I hope you'll call, or just write back, whatever is easiest for you. Thanks for taking the time to read this. I look forward to hearing [...] 10535 154 42_Re: problems with Unicode in DB2 OS/390 V613_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Fri, 17 Aug 2001 07:01:21 +1000342_iso-8859-1 Hello Tim,

I am very interested in this subject and would like to promote a string on the subject of Unicode and Java. I have been experimenting here (on OS/390 only) for about a year now. My interest is performance and only OS/390 or Z/OS. My problem or discussion topic is presented after a solution to your problem. [...] 10690 95 30_Re: JDBC Drivers - App vs. Net13_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Fri, 17 Aug 2001 07:01:26 +1000567_iso-8859-1 Bill,

The redbook SG24-6257 is a good place to look, although this is targeted at OS/390.

The answers to your questions on use are, as usual, it depends.

Basically, the type 1 driver was a JDBC/ODBC bridge. Thus an ODBC driver is necessary. The type 2 driver removed the ODBC dependency. For example the OS/390/DB2 V7 driver (and V6 driver for about 18 months) is a type 2 driver where the JDBC calls are passed via JNI (Java native interface) to a C layer that sits on top of the RRS api or in fact, the CICS/DB2 or IMS/DB2 api. [...] 10786 31 21_Re: URGENT: SMS & DB215_Philippe Herman20_pherman@LANDMARK.COM31_Thu, 16 Aug 2001 17:08:27 -0400342_us-ascii Hi Stan

DB2 only see one logical extend, physicaly spead over multiple volumes.

This construction is pure SMS and DB2 does not feel the difference.

But be aware that your PQTY will be used on all the volumes

Example : PQTY= 500 Tracks and SMS allows 3 volumes means an allocation of 3 X 500 tracks ! [...] 10818 32 35_db2 on Windows 2000, one more time.12_Kirk Hampton16_khampto1@TXU.COM31_Thu, 16 Aug 2001 16:42:01 -0500290_us-ascii I am looking for some official clarification, as I cannot seem to find it on IBM's web site:

What is the minimum level of these products that is "certified" ( I read this to be 'supported by IBM' ) to run on Windows 2000 ?

1) DB2 UDB EE on a Windows 2000 server [...] 10851 198 42_Re: problems with Unicode in DB2 OS/390 V60_19_Tim.Lowe@STPAUL.COM31_Thu, 16 Aug 2001 17:28:40 -0500344_us-ascii Greg, Thanks for the ideas! I have passed them on to the application programmer.

I should have said that they encrypting credit card numbers, and then store them in Unicode. The encryption is not Unicode. I do not like this and think it is a very bad idea. But, I am told it is a package, and that is what they need to do. [...] 11050 64 21_Re: URGENT: SMS & DB210_Moden, Jim17_jim.moden@EDS.COM31_Thu, 16 Aug 2001 19:25:51 -0500392_iso-8859-1 Stan:

Our experience is that you just get multiple data set extents and that has not been a problem as long as the DASD/Storage Management people set the allocation for getting a primary extent of a data set on a pack at 25% of the total requested space for the tablespace being built/allocated. All the datasets ended up as I0001.A001 data sets with multiple extents. [...] 11115 63 0_13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Thu, 16 Aug 2001 17:56:00 -0700573_us-ascii Hi Preethy,

Rohn.Solecki gave you an excellent answer to your question. The only thing I would add, or further emphasize, is that the data modeling for efficient relational database processing is definitely not the same as for hierarchical database processing.

I went through an IMS to DB2 conversion project, and our CICS response times are better than the old IMS system. We did have to take a second look at some batch jobs, but when the module was redesigned to incorporate a fewer but more complex SQL statements, they performed well also. [...] 11179 78 28_Page Temporarily Unavailable13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Thu, 16 Aug 2001 18:23:38 -0700623_us-ascii Hi,

We got "Page Temporarily Unavailable" message twice on the same page 2hrs & 15 mins apart. Same job on a full volumn test system.

This was on the 44th partition of a 60 partition tablespace, and running DSN1COPY with the CHECK option on that partition returned normal.

This data was loaded about 1 month ago (for full volume testing) using DSN1COPY vsam to vsam copy from production, making the appropriate OBID etc changes. Production is read only with locksize tablespace, and is appended daily with a load resume. Everything has been running fine until yesterday when this happen. [...] 11258 112 32_Re: Page Temporarily Unavailable16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Thu, 16 Aug 2001 23:10:14 -0400432_iso-8859-1 Tink,

The DSNI011I message is issued when a page is logically broken and has been scheduled for recovery. There should have been an SVC dump issued with a 00C90105 abend, followed by an automatic attempt to recover the page. After that, you should have seen either a DSNB215I (successful recovery of the page) or a DSNI012I (failure to recover the page). If the DSNI012I was issued, the page would be broken. [...] 11371 68 50_Re: Number of Tables Allowed in Join for Version 713_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 16 Aug 2001 22:24:07 -0500426_iso-8859-1 Julie,

As far as I am aware, the 15 table limit applies (V6 & V7) to a single FROM clause unless the following occurs:

- The query qualifies as a star join, in which case up to 225 tables are allowed. - The query is detected as a SIEBEL query (not sure about other vendors). - Customer requests the 15 table limit parameter to be altered - NOTE: I am not sure that IBM will support this request. [...] 11440 16 42_No such thing as a free Platinum reference10_John Hardy22_john_j_hardy@YAHOO.COM31_Thu, 16 Aug 2001 22:22:50 -0500495_- A nice lady from CA promised me a 'Platinum Reference' (as I'm sure most of us will call it for a while) for V6 a while ago. On the strength of this I discarded by old (Version 4) one. Unfortunately, the new one didn't turn up, and now I feel as if my right arm has fallen off (or at least a couple of fingers). Please could someone at CA tell me how I can acquire one. I am willing to pay with cold hard cash, drugs, weapons, or sex (doubtful there would be any takers for the last one, [...] 11457 38 46_Re: No such thing as a free Platinum reference15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 17 Aug 2001 14:01:44 +1000575_- John,

The 'Platinum Reference' guides are good, but they're not THAT good...



Raymond

> -----Original Message----- > From: John Hardy [SMTP:john_j_hardy@YAHOO.COM] > Sent: Friday, August 17, 2001 1:23 PM > To: DB2-L@RYCI.COM > Subject: No such thing as a free Platinum reference > > A nice lady from CA promised me a 'Platinum Reference' (as I'm sure most > of > us will call it for a while) for V6 a while ago. On the strength of this I > discarded by old (Version 4) one. Unfortunately, the new one didn't turn > up, and now I feel as if [...] 11496 38 46_Re: No such thing as a free Platinum reference29_=?iso-8859-1?q?Half=20Eaten?=24_half_beaten@YAHOO.COM.AU31_Fri, 17 Aug 2001 14:43:48 +1000519_iso-8859-1 Mr Hardy, We haven't met before, but I'm prepared to help you regain the use of one of your limbs. Meet me at the corner of Kent & Market on Monday at 5pm. Have at least $150 in your pocket and a smile on your face.



>>A nice lady from CA promised me a 'Platinum >>Reference' (as I'm sure most of >>us will call it for a while) for V6 a while ago. On >>the strength of this I >>discarded by old (Version 4) one. Unfortunately, the >>new one didn't turn >>up, and now I feel as if my right [...] 11535 39 46_Re: No such thing as a free Platinum reference11_Grant Allen22_grant@TOWERSOFT.COM.AU31_Fri, 17 Aug 2001 14:54:47 +1000412_- Half Eaten[SMTP:half_beaten@YAHOO.COM.AU] wrote: > > Mr Hardy, > > We haven't met before, but I'm prepared to help you > > regain the use of one of your limbs. Meet me at the > > corner of Kent & Market on Monday at 5pm. Have at > > least $150 in your pocket and a smile on your face. > Mmm ... under the watchful gaze of the IBM tower on Sussex street. Do I sense an alterior motive? :-) :-) :-) :-) :-) [...] 11575 23 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM31_Fri, 17 Aug 2001 01:17:31 -0500621_us-ascii I will be out of the office from 08/17/2001 until 08/20/2001.

I will not 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. 11599 21 46_Re: No such thing as a free Platinum reference13_Peter Zippert22_peterzippert@LYCOS.COM31_Fri, 17 Aug 2001 01:12:06 -0500477_us-ascii I joined this list to learn about DB2! NOT to get unsolicited emails (SPAM)! Hardy is obviously an idiot.

Thankyou, Peter



Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/

================================================ 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. 11621 53 50_AW: Number of Tables Allowed in Join for Version 712_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 17 Aug 2001 10:04:28 +0200731_iso-8859-1 Julie,

basically in ONE From clause you cannot have more than 15 tables/views.

HTH.

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

-----Ursprüngliche Nachricht----- Von: Johnson, Julie [mailto:julie.johnson@UCONN.EDU] Gesendet am: Donnerstag, 16. August 2001 22:37 An: DB2-L@RYCI.COM Betreff: Number of Tables Allowed in Join for Version 7

Hi,

I've looked through the v7 manuals and I can't find any explicit reference to the number of tables allowed in a join. I need to know if the 15 table limit in v5 (via UQ39732) is to be carried forward in future versions of DB2. If anyone knows the [...] 11675 30 25_Supply of Data Propogator11_Carl Newton21_newtonc@UK.TIGPLC.COM31_Fri, 17 Aug 2001 10:18:49 +0100715_iso-8859-1 All,

A colleague is starting to use UDB on AIX and has discovered that Data Propogator is bundled in with it. Our question is whether this also applies to the mainframe 'big' UDB or is it a chargeable option?

TIA

Carl.



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ This message and any attachments are confidential to the intended recipient at the e-mail address to which it has been addressed. If you are not the intended recipient you may not copy, forward, disclose or use any part of this message or its attachments. If received in error please notify the sender immediately by e-mail or contact The [...] 11706 79 21_Re: URGENT: SMS & DB212_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Fri, 17 Aug 2001 07:32:53 -0400377_us-ascii Philippe, I am somewhat confused here, The important thing here is that on a request(Create tabelspace or reorg) SMS is not able to supply the primary qty on one volume, What does happen is the question, from or info I would think that it creates a smaller primary on the first volume and another on the second? I know it doesn't matter for the secondary extent. [...] 11786 53 46_Re: No such thing as a free Platinum reference14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 17 Aug 2001 06:31:00 -0500407_iso-8859-1 John,

I didn't receive mine either...fell through the cracks, I'd guess. I re-sent an email to mailto:Janis.Thomas@ca.com. She indicated as much, confirmed the address that I had originally provided, and she's getting it out to me.

Give her an email, I'm sure she can help you out...You might leave any mention of drugs, sex, and/or weapons out of it, but cash is always good. [...] 11840 111 21_Re: URGENT: SMS & DB212_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Fri, 17 Aug 2001 07:46:42 -0400320_us-ascii Jim, First of all Thanks for you input, we are very concerned at this point and the customer is sitting somewhat on Pins and Needles.

Your statement about '25% of total space requested' confused the SMS guys here. They said they are running the pools at 80/20. But was not sure about the 25% thing. [...] 11952 36 46_Re: No such thing as a free Platinum reference13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 17 Aug 2001 08:46:46 -0400464_iso-8859-1 >I joined this list to learn about DB2! NOT to get unsolicited emails >(SPAM)! Hardy is obviously an idiot. >Thankyou, >Peter

Easy there, Peter. John Hardy is not an idiot. You'd know that if you'd seen his prior contributions here.

If you stick around long enough here, I suggest you'll find lots of good information, some things you don't care about, occasional junk, and once in awhile an attempt at humor mixed in with the above. [...] 11989 84 43_Re: Where are DIAnnnnn messages documented?12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 17 Aug 2001 06:47:14 -0700347_us-ascii Thanks Folks- Apparently they're "IBM Internal Use Only error messages", and can only be accessed by the Big Blue Help Desk. Everything else seems to be ok in my system, but I hate to wade through the thousands of these messages I keep getting in diaglog to see if anything else is amiss. Guess I'll plan a round of AIX maint... Tim [...] 12074 70 42_Re: FW: dwlist: DB2 OS/390 Row Id Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 17 Aug 2001 08:51:53 -0500440_- I think Casey is trying to use rowid for something that it not intended for. The intention of rowid is to enable direct re-retrieval of a row without needing an index or tablespace scan. There is a minor exception in that using a rowid that was obtained before a reorg will require using an index or doing a tablespace scan. This is because the value of a row's rowid changes after a re-org - but the pre-reorg rowid is still valid. [...] 12145 18 25_Industry Naming Standards23_SUBSCRIBE DB2-L aam aam19_aam_db2@HOTMAIL.COM31_Fri, 17 Aug 2001 08:56:27 -0500526_- All, Can anyone send me a link or a document of Industry's naming standard for SQL PRocedures ? Also can anyone recommend a book or link to website for someone to learn Java Stored Procedures (Beginner's guide would be good)? Any type of help would be appreciated.

Thanks AAM

================================================ 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. 12164 29 29_Re: Industry Naming Standards18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Fri, 17 Aug 2001 16:17:51 +0200415_iso-8859-1 go to borie.org my web site and you have some useful link about db2. One is a complete library link about db2 book.

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 [...] 12194 126 27_Re: SQL Request (was blank)11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 17 Aug 2001 10:13:23 -0500410_- Subrata - the query supplied by Terry is really cool! But it probably needs one minor correction. In the case where duplicates can exist for colc e.g.

cola colb colc 10 10 100 10 10 200 10 10 200

the existing query will not work - the "200" value will be completely missing. Changing the <= to < also will not work - this will drop off the lowest seq no. Here is an alternative I suggest: [...] 12321 170 21_Re: URGENT: SMS & DB210_Moden, Jim17_jim.moden@EDS.COM31_Fri, 17 Aug 2001 10:20:42 -0500586_iso-8859-1 Stan:

The 25% of total space requested refers to an allocation threshold that is set by the storage management folks when SMS is set up.

For example, if your tablespace requests an allocation of 100 cylinders as its primary allocation, SMS will scan the storage pool looking for space on all the volumes in the pool. If the allocation threshold is 25, if the first volume scanned has 25 free cylinders, your tablespace will get an extent of 25 cylinders and SMS will continue to scan the storage pool looking for more space to allocate your tablespace on. [...] 12492 215 21_Re: URGENT: SMS & DB212_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Fri, 17 Aug 2001 11:35:20 -0400292_us-ascii Jim, I do appreciate your info, but we are not talking about extends, but the initial Primary allocation. From what I have heard it look like that regardless of what SMS has setup , DB2 insist that the primary allocation be on one volume unless setting up a 'Large' Tablespace. [...] 12708 28 46_Re: No such thing as a free Platinum reference9_Rob Crane22_racrane@CONCENTRIC.NET31_Fri, 17 Aug 2001 09:38:08 -0600488_us-ascii Give me your address and I will ship you one out.

-Rob

John Hardy wrote: > > A nice lady from CA promised me a 'Platinum Reference' (as I'm sure most of > us will call it for a while) for V6 a while ago. On the strength of this I > discarded by old (Version 4) one. Unfortunately, the new one didn't turn > up, and now I feel as if my right arm has fallen off (or at least a couple > of fingers). Please could someone at CA tell me how I can acquire one. I am [...] 12737 25 45_Re: Filter factors and other optimizer stuff.12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Aug 2001 10:36:46 -0500293_- The old blue book was a Version 1 level, and it's completely out of date. This information does change on every release, and quite a bit of the information is in the Administration Guide. For example, in V7, chapters 31, 32 and 33 are on SQL tuning, maintaining statistics and explain. [...] 12763 55 41_Re: Migrating from Ver 5 to Ver 7. OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Aug 2001 10:49:06 -0500544_- I can't resist. It depends. I'll try sending you a pdf on migration to V6 and V7 - my Share presentation, but it's 600K, so hotmail may refuse it. Here is a summary that I extracted.

Q: Should I skip over Version 6 to Version 7?

A: When we announced V7, the ability to skip from V5 to V7 was included. Skipping from V5 to V7 means encountering the Version 6 incompatible changes and a few more. Getting in all of the prerequisite changes from other vendors and making the needed changes can take the majority of time for a [...] 12819 20 50_Re: Number of Tables Allowed in Join for Version 712_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Aug 2001 10:55:40 -0500337_- I decided to check and think I found it. Most of these limits are in the Limits Appendix of the SQL Reference. Here are a couple of pasted lines.

Table 63. DB2 limits on SQL statements

Maximum number of base tables in a view, SELECT, UPDATE, INSERT, or DELETE 225

Maximum number of tables in a FROM clause 15 [...] 12840 13 10_WLM survey22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Fri, 17 Aug 2001 09:17:09 -0700334_iso-8859-1 We are using WLM with no problems....email or phone is fine (503)373 8477. ramesh

================================================ 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. 12854 13 25_Re: Phonetic search tools12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Aug 2001 11:09:42 -0500425_- Sorry, but SOUNDEX is not in DB2 for z/OS and OS/390, even in V7. There are a couple of challenges with encodings as well as those mentioned above.

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. 12868 12 29_Re: Supply of Data Propogator13_Ellen Richter19_e2richte@HEWITT.COM31_Fri, 17 Aug 2001 11:29:31 -0500287_us-ascii Its chargeable on Version 7 - 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. 12881 103 41_Re: Migrating from Ver 5 to Ver 7. OS/39012_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 17 Aug 2001 11:36:06 -0500662_- I don't know if this is the presentation that you are talking about, but I found one at:

http://www.lightyr.com/presentations/LYTeleconf_DB2_V7_Migration_Charts.PDF

or perhaps:

http://www.share.org/proceedings/sh91mo/pprbyses/pprbyses200.html this seems to be V5

or most likely:

http://www.share.org/proceedings/sh96/pprbyses/pprbyses98.html#583

---------------------------------------------------------------------- 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. [...] 12985 15 42_Re: problems with Unicode in DB2 OS/390 V612_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Aug 2001 11:43:55 -0500288_- For dramatically improved performance with Java, you'll want to have DB2 for z/OS and OS/390 V7 with APAR PQ48383. It fixes other problems as well, but we've seen these changes reduce cpu time by half. If the code page is something other than 500 or 37, the improvement is larger. [...] 13001 50 45_Re: Filter factors and other optimizer stuff.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 17 Aug 2001 11:45:55 -0500461_iso-8859-1 If you are looking for more information than what IBM can fit into the manuals, then be sure to checkout Patrick Bossman's (from IBM) presentation at the upcoming DB2 Tech Conference.

Regards Terry



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Roger Miller Sent: Friday, August 17, 2001 10:37 AM To: DB2-L@RYCI.COM Subject: Re: Filter factors and other optimizer stuff. [...] 13052 21 49_Calling CICS program from a Db2 stored procedure.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 17 Aug 2001 13:28:22 -0400466_iso-8859-1 Does anybody calling a CICS program from a stored procedure. To do this I started testing calling Cics program from a batch program first. I looked at the Red book Cross platform DB2 stored procedures chapter 7. Called CICS support people and created whatever necessary for calling Cics program from external. I wrote a program with EXEC Cics link. I compiled batch program with CBL XOPTS(EXCI,COBOL2). But I am getting link error 88 with resp2 203. [...] 13074 135 17_Re UNLOAD UTILITY13_Harry William27_harry_williamtk@HOTMAIL.COM31_Fri, 17 Aug 2001 18:01:29 +0000330_- Thanks for the response. Since I am not too sure about this utility,I am pasting one of the job we run.This might tell us something. Regarding the DASD, I have to check it with the concerned team. Once again thanks for the help. Regards,

Harry PTFU001 - PLATINUM FASTUNLOAD VERSION 2.1.2 - DB2 SSID: DB2P - 10/25/98 [...] 13210 13 21_Re: URGENT: SMS & DB212_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Fri, 17 Aug 2001 12:59:23 -0500498_- In our SMS environment, the primary quantity for a new object, it it is new or being reallocated for a load or REORG, can be created in a maximum of 5 extents. Each extend could be on a differant volume. All of our stogroups are created with volume('*').

================================================ 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. 13224 27 54_Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC12_Walker, Rick18_RWalker@SFBCIC.COM31_Fri, 17 Aug 2001 13:28:57 -0500617_iso-8859-1 Dear List,

I have a new application system that is being developed using Visual Basic and accessing DB2 data on the OS/390 mainframe via a DB2 CONNECT ODBC connection. Transactions from this system seem to create threads that hang around even after the transaction is completed, and I am worried about thread maximums once this system goes into production. This is new ground for us. In the past all of our application systems were CICS based. I would appreciate any suggestions to reduce the threat of performance problems that this type of application may cause once it goes into production. [...] 13252 154 20_Backup Utility Error0_29_sflindsey@HIGHLIGHTS-CORP.COM31_Fri, 17 Aug 2001 14:31:19 -0400340_iso-8859-1 Environment: OS/390 DB2 V5. Sorry for the long post but I was trying to give as much information as possible.

I was trying to backup two tablespaces before doing a DSN1COPY from my production environment to my production sized development environment. There is one table per tablespace. The tablespace is compressed. [...] 13407 123 58_Re: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Fri, 17 Aug 2001 13:41:34 -0500621_iso-8859-1 Rick, Be sure the VB program is cleaning up i.e. if rst is your recordset, be sure and : rst.Close Set rst = Nothing explicitly

Phil Jackson Texas Farm Bureau ------------------------

Dear List,

I have a new application system that is being developed using Visual Basic and accessing DB2 data on the OS/390 mainframe via a DB2 CONNECT ODBC connection. Transactions from this system seem to create threads that hang around even after the transaction is completed, and I am worried about thread maximums once this system goes into production. This is new ground for us. In the past all [...] 13531 78 24_Re: Backup Utility Error12_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 17 Aug 2001 13:41:42 -0500650_- > -----Original Message----- > From: sflindsey@HIGHLIGHTS-CORP.COM [SMTP:sflindsey@HIGHLIGHTS-CORP.COM] > Sent: Friday, August 17, 2001 1:31 PM > To: DB2-L@RYCI.COM > Subject: Backup Utility Error >

> Here is the DSNB207I message along with other pertinent messages that I > retrieved from the system log: > DSNB207I - DYNAMIC ALLOCATION OF DATA SET FAILED. 429 > REASON=02100000. DSNAME=DB2P.DSNDBC.DHIB02F.S0070F.I0001.A001 > Dynamic allocation reason code 0210 indicates that the data set named (DB2P.DSNDBC.DHIB02F.S0070F.I0001.A001) is currently allocated to another user or job. You might want to enter the OS/390 command: [...] 13610 43 58_Re: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC16_Tanguay, Richard22_RichardTan@DEP.NYC.GOV31_Fri, 17 Aug 2001 14:44:05 -0400605_- Set the ZPARM for Idle Thread Timeout (IDTHTOIN) to a reasonable number for your application. We use 2700 (45 minutes) for an IIS application.

> -----Original Message----- > From: Walker, Rick [SMTP:RWalker@SFBCIC.COM] > Sent: Friday, August 17, 2001 2:29 PM > To: DB2-L@RYCI.COM > Subject: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC > > Dear List, > > I have a new application system that is being developed using Visual Basic > and accessing DB2 data on the OS/390 mainframe via a DB2 CONNECT ODBC > connection. Transactions from this system seem to create threads that hang > [...] 13654 35 53_Re: Calling CICS program from a Db2 stored procedure.0_17_JTonchick@AOL.COM29_Fri, 17 Aug 2001 15:00:04 EDT571_US-ASCII In a message dated 08/17/2001 12:31:26 PM Central Daylight Time, SPhilip@CSXLINES.COM writes:

<< Does anybody calling a CICS program from a stored procedure. To do this I started testing calling CICS program from a batch program first. I looked at the Red book Cross platform DB2 stored procedures chapter 7. Called CICS support people and created whatever necessary for calling CICS program from external. I wrote a program with EXEC CICS link. I compiled batch program with CBL XOPTS(EXCI,COBOL2). But I am getting link error 88 with resp2 203. >> [...] 13690 82 58_Re: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC13_Andy Seuffert25_Andy.Seuffert@NEONSYS.COM31_Fri, 17 Aug 2001 14:50:49 -0500716_iso-8859-1 Rick, not only should you ensure that you close your resultsets, but you should also do a .close on the connection object and then additionally set the ADO connection object to NOTHING. Example, below is a test program which I have which simulates opening and closing a thousand connections:

Dim obConn(10000) As New ADODB.Connection Dim obRS As New ADODB.Recordset Dim i As Integer Dim x As Integer For i = 1 To 1000 obConn(i).ConnectionString= "DSN=NEONClient;" obConn(i).CursorLocation = adUseClient obConn(i).Open obRS.Open "Select * from q.staff", obConn(i), adOpenDynamic, adLockBatchOptimistic obRS.MoveLast obRS.Close Debug.Print "I= " & i obConn(i).Close Set obConn(i) = Nothing next [...] 13773 50 53_Re: Calling CICS program from a Db2 stored procedure.13_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Sat, 18 Aug 2001 05:52:04 +1000611_iso-8859-1 Sibi,

This is eminently possible, both from batch and from a stored procedure. My guess is that you are not pointing the linkage editor at SDFHEXCI.

I have a test compile link and go sample if necessary.

I translate as follows: //TRANS EXEC PGM=DFHECP1$, // PARM='SOURCE,COBOL3,NOSEQ,EXCI' then compile and link.

Greg Farquhar

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Philip, Sibimon Sent: Saturday, August 18, 2001 3:28 AM To: DB2-L@RYCI.COM Subject: Calling CICS program from a Db2 stored procedure. [...] 13824 73 53_Re: Calling CICS program from a Db2 stored procedure.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 17 Aug 2001 16:22:49 -0400407_- I have couple of reason to do this.

1. We have ADABAS, so if we want to retrieve data from ADABAS, we need to code this as a CICS program. So the client program should go though Direct connect to retrieve data from ADABAS and then go through DB2 connect to get DB2 data. Instead of doing that we thought we will call the CICS program from stored procedure and pass the whole data in one call. [...] 13898 29 21_Re: URGENT: SMS & DB215_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Fri, 17 Aug 2001 20:43:01 +0200485_us-ascii We're doing exactly that (using Vol('*') for sms managed stogroups). One caution on this as a wide-spread, default standard:

Please note that this can cause some adminstrative problems. When datasets (tablespace or index) go into extents, and your extents are across unknown volumes. It makes trying to ensure that indexes and tablespaces are not on the same volume, and trying to ensure that 2 (or more) high use datasets aren't on the same volume, more complex. [...] 13928 64 53_Re: Calling CICS program from a Db2 stored procedure.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 17 Aug 2001 16:28:46 -0400485_iso-8859-1 Greg,

In my batch execution JCL I have DSN=CICS.R13TEST.SDFHEXCI in the steplib.

-----Original Message----- From: Greg Farquhar [mailto:Greg_Farquhar@BIGFOOT.COM] Sent: Friday, August 17, 2001 02:52 PM To: DB2-L@RYCI.COM Subject: Re: Calling CICS program from a Db2 stored procedure.



Sibi,

This is eminently possible, both from batch and from a stored procedure. My guess is that you are not pointing the linkage editor at SDFHEXCI. [...] 13993 216 24_Re: Backup Utility Error15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 17 Aug 2001 15:28:43 -0500438_iso-8859-1 Look like its drain lock , but I am not sure , re-check whether any of the transactions might be looping in CICS region(if you shop uses).

Thanks & Regards Nayeem







sflindsey@HIGHLIGHT S-CORP.COM To: DB2-L@RYCI.COM Sent by: DB2 Data cc: Base Discussion Subject: Backup Utility Error List



08/17/01 01:31 PM Please respond to DB2 Data Base Discussion List [...] 14210 65 21_Re: URGENT: SMS & DB214_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 17 Aug 2001 15:52:14 -0500663_iso-8859-1 What happens when you define your DB2 stogroup w/ specific SMS managed volumes...

For example Stogroup TSPACES Volume ('DB2001','DB2002','DB2003') Stogroup IXSPACES Volume ('DB2004','DB2005')

Will SMS manage the vols specified within your Stogroup definition as long as they meet the higher level SMS filtering rules?? In other words, will SMS treat my 3 tablespace volumes just as they would '*', only for my smaller pool ?? And, if so, wouldn't this allow me to take some advantage of SMS and also keep my tablespaces and indexes seperated (as well as my keepeing heavy hitting tablespaces seperated from other tablespaces, etc.)? [...] 14276 60 58_Re: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Fri, 17 Aug 2001 14:08:00 -0500294_us-ascii We have had the same problem using applications that are written in VB. The thread continues to run even after the user closes out of the application. IBM says that it is a problem with the exit routine in the operating system. The following is an excerpt that IBM put in our PMR [...] 14337 69 58_Re: Visual Basic accessing DB2 OS/390 via DB2 Connect ODBC9_Rob Crane22_racrane@CONCENTRIC.NET31_Fri, 17 Aug 2001 15:46:22 -0600318_us-ascii There are products out there that can terminate threads based on a variety of conditions. You could at least automate this process and have the tool kill threads tied to your VB app that have run over x amount of time or done x amount of getpages, etc..

one example is Thread Terminator from CA.. [...] 14407 73 56_Re: JAVA/JDBC at WIN/2000 try to access DB2 R6 at OS/39015_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Fri, 17 Aug 2001 15:40:35 -0700403_iso-8859-1 Something is missing in your description. You can't talk JDBC directly to your DB2 DDF port unless you are using a type 4 driver. It's unlikely that you are running with a type 4.

Where does your JDBC driver on the WIN/2000 platform coming from? If from DB2 Connect, then you need to be pointing to your application to your DB2 Connect environment with either a type 2 or type 3. [...] 14481 115 21_Re: URGENT: SMS & DB218_Michael L McCawley20_mike_dba@HOTMAIL.COM31_Sat, 18 Aug 2001 03:20:52 +0000438_- Scott -

It depends:

If the SMS Management class specifies Guaranteed Space (yes), then the DB2 storage group volume list will be followed and the space must be available on the listed vols.

If the SMS Management class has Guaranteed Space (no), then SMS will look at the DB2 stogroup vol list, say "that's nice", then allocate the datasets anywhere it wants (following the SMS Storage Group rules, of course). [...] 14597 85 53_Re: Calling CICS program from a Db2 stored procedure.13_Greg Farquhar25_Greg_Farquhar@BIGFOOT.COM31_Sat, 18 Aug 2001 19:31:36 +1000433_iso-8859-1 Sibi,

You need it on your SYSLIB at linkedit time.

Greg

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Philip, Sibimon Sent: Saturday, August 18, 2001 6:29 AM To: DB2-L@RYCI.COM Subject: Re: Calling CICS program from a Db2 stored procedure.



Greg,

In my batch execution JCL I have DSN=CICS.R13TEST.SDFHEXCI in the steplib. [...] 14683 135 24_Re: Backup Utility Error12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 17 Aug 2001 14:34:00 -0400447_- Have you looked at the system log to view the IDCAMs message?

>>> DB2-L@RYCI.COM@inter2 08/17/01 01:31PM >>> Environment: OS/390 DB2 V5. Sorry for the long post but I was trying to give as much information as possible.

I was trying to backup two tablespaces before doing a DSN1COPY from my production environment to my production sized development environment. There is one table per tablespace. The tablespace is compressed. [...] 14819 28 12_Re: DB2 TMON15_Philippe Herman20_pherman@LANDMARK.COM31_Sat, 18 Aug 2001 07:59:16 -0400322_us-ascii Hi Jeff,

Sorry for the delay btu a was on the road.

I have contacted some of my US colleagues and asked them to take a look at this issue.

I'll take 2 weeks of now and will be back in September.

If by that time you are still experiencing problems, don't hesitate to yell at me... [...] 14848 20 41_Re: Migrating from Ver 5 to Ver 7. OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Sat, 18 Aug 2001 12:34:54 -0500414_- Those are a bit older than the one I sent. The latest presentation on the web now is the Share one from February, just before V7 become generally available. Now, after four and a half months of general availability, Version 7 is looking good. I made twenty or thirty changes for the Share presentation in July, and that should show up on the web fairly soon. The July Minneapolis version is the one I sent. [...] 14869 25 19_Re: IBM Redbook URL12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 18 Aug 2001 12:48:24 -0500509_- The short form for most of the URLs is easier to remember and type for me

ibm.com/redbooks

If you want DB2 for z/OS and OS/390 red books, then we have done some editing and provided another way to look.

Start at the main DB2 for z/OS and OS/390 web page

ibm.com/software/db2os390

Click on Support, then click on DB2 Redbooks about two thirds of the way down the screen. Today we had 54 books listed, but there is some editing to do and a few more to add to the list. [...] 14895 44 27_Invalid Page Header Warning16_Mark Baskerville26_mark.baskerville@PEPSI.COM31_Mon, 20 Aug 2001 02:43:29 -0500374_iso-8859-1 Greetings All,

We are running a DB2 UDB EEE V7.2 database with 9 logical partitions on AIX V4.3. The catalog node resides on an IBM Winterhawk II and logical nodes 1 through 8 reside on two 18 way IBM S80's. I occasionally encounter the following messages in the DB2DIAG.LOG : buffer_pool_services sqlbSMSDirectWrite Probe:3 Database: [...] 14940 48 46_Re: No such thing as a free Platinum reference10_John Hardy22_john_j_hardy@YAHOO.COM31_Sun, 19 Aug 2001 22:58:26 -0500599_- Mr Zippert,

I am not an idiot, and I would thank you to refrain from abusing me in a public forum. Although my query was intentionally couched in a humorous style, the substance of my request was serious, and in my opinion, pertinent to this discussion list.

If you consider that my posting, or any other posting on this list is not relevant or appropriate, then the appropriate course of action is to contact the List Administrators. Please note that it is a condition of this Discussion List that contributors refrain from posting inflammatory remarks such as those which you [...] 14989 39 26_MONTHNAME function missing13_Neil Courtney19_NCOURTN@CLEAR.CO.NZ31_Mon, 20 Aug 2001 16:41:44 +1200381_iso-8859-1 Hi all, I am using DB2 v6.1, OS390.

I am trying to execute the following SQL, to get the month name for a date, via QMF v6.1 and/or SPUFI. According to the SQL Reference manual, MONTHNAME is a valid function.

SELECT LM949_RT_MM_END, MONTHNAME(LM949_RT_MM_END) FROM P.LM949T00

This results in the message "The function FUNCTION was not found.". [...] 15029 68 30_Re: MONTHNAME function missing10_teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 20 Aug 2001 15:13:43 +1000354_us-ascii Hi Neil,

The MONTHNAME is a Userdefined function supplied as sample source (in C++ code) with DB2. The default functions u can use r the ones listed in the SQL Reference manual in chapter 4.

U might have noticed, but MONTHNAME is under Appendix F and the description says that u have to have run one of the installation jobs. [...] 15098 63 30_Re: MONTHNAME function missing15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 20 Aug 2001 15:04:03 +1000288_- Neil,

I can see a function to return the month number (i.e. a value between 1 and 12) but not the actual name of the month. Is this maybe a V6 for 'little DB2's' function? Don't think it's on OS/390 V6, unfortunately. But quite happy to be called an idiot and proved wrong. [...] 15162 23 46_Re: No such thing as a free Platinum reference10_John Hardy22_john_j_hardy@YAHOO.COM31_Mon, 20 Aug 2001 00:46:26 -0500336_- Thanks to all (but one!) of the replies to my posting, including the many offers of CA/Platinum (and other) references. I trust that I will soon have my fingers mataphorically stitched back on again, and hereby officially bring this short but intense thread to a close.

To all: thanks again for your help and forbearance. [...] 15186 16 26_Matchcols and index usage.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Mon, 20 Aug 2001 00:59:06 -0500418_- Hi,

I have a table TABLE-A having an index IXTBL-A on col1, col2, col3 and col4. Following select is executed....

Select * from TABLE-A where col2 = 'something'

================================================ 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. 15203 15 13_out of office18_William Mitzmacher34_William_Mitzmacher@NEWYORKLIFE.COM31_Mon, 20 Aug 2001 02:16:59 -0400411_us-ascii I will be out of the office starting 08/20/2001 and will not return until 08/21/2001.

Please contact Lou Salesky or Richie Schmidt for any urgent matters

================================================ 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. 15219 25 29_Index usage when MATCHCOLS=0.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Mon, 20 Aug 2001 01:07:34 -0500430_- Hi,

Sorry for the incomplete query posted earlier. It was an accident. Here is a complete one.

I have a table TABLE-A having an index IXTBL-A on col1, col2, col3 and col4. Following select is executed....

Select ... from TABLE-A where col2 = 'ABC' and col3 > 'XYZ'

Matchcols for the above is zero. Will the query execute a full indexspace scan looking for rows that satisfy the above condition. [...] 15245 69 33_Re: Index usage when MATCHCOLS=0.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 20 Aug 2001 12:07:58 +0530630_- Index screening is a highly effective technique in this kind of queries. So scanning the complete index may not be required. However, index screening is not shown in Explain.

Regards Sanjeev

> -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Sent: Monday, August 20, 2001 11:38 AM > To: DB2-L@RYCI.COM > Subject: Index usage when MATCHCOLS=0. > > Hi, > > Sorry for the incomplete query posted earlier. It was an accident. > Here is a complete one. > > I have a table TABLE-A having an index IXTBL-A on col1, col2, col3 and > col4. Following select is executed.... [...] 15315 74 33_Re: Index usage when MATCHCOLS=0.15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Mon, 20 Aug 2001 16:31:49 +1000382_us-ascii Sorry, but it depends.

If the query is selecting just columns from the index, then I would be surprised if it did other than scan the index space.

If the query is retrieving columns from the table, then I would expect that the cluster ratio of the index could have an impact (as you are looking for a range of values) as well as the cardinality of col2. [...] 15390 138 33_Re: Index usage when MATCHCOLS=0.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 20 Aug 2001 07:40:32 +0100464_iso-8859-1 If it doesn't do a tablespace scan, Yes - if you think about it, what else can DB2 do?

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: Rajendra Deshpande [mailto:rajendra_deshpande@HOTMAIL.COM] Sent: 20 August 2001 07:08 To: DB2-L@RYCI.COM Subject: [DB2-L] Index usage when MATCHCOLS=0. [...] 15529 24 23_DB2 Bookmanager Manuals15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Mon, 20 Aug 2001 16:41:24 +1000542_- OK, so I've had a moderately extensive look around the usual IBM manual haunts and, even though Roger Miller kindly gave a few manual URLs recently, I can't find the DB2 bookmanager manuals anywhere. A month or so ago I downloaded most of the OS/390 V7 manuals but I believe Roger mentioned recently that some of the manuals may have changed. Unfortunately I now can't find the link. Anyone know where I can find the .BOO versions? PDFs look good but are just a bit awkward to get around - for me, anyway - and HTML just doesn't do it [...] 15554 18 27_Re: DB2 Bookmanager Manuals15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Mon, 20 Aug 2001 09:08:03 +0200416_us-ascii I had the same struggle for the v6 books. They turned out to be on the regularly released set of CD that are sent out with OS upgrades and patches etc. . Check to see with your OS/390 guys for these CD's.

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. [...] 15573 196 27_Re: DB2 Bookmanager Manuals0_24_Bruce.Williamson@PSIR.IE31_Mon, 20 Aug 2001 08:13:04 +0100916_windows-1252 Howzit Raymond?

Try http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves/DSNSHHA0 Let me know if you need V6.

Cheers Bruce

__________________________________________________

Bruce Williamson DB2 Database Administrator

perotsystems information resource(tm)

Computer Centre Cabinteely Dublin 18 Ireland

Bruce.Williamson@psir.ie







-----Original Message----- From: Bell, Raymond W [mailto:Raymond.W.Bell@TEAM.TELSTRA.COM] Sent: 20 August 2001 07:41 To: DB2-L@RYCI.COM Subject: DB2 Bookmanager Manuals



OK, so I've had a moderately extensive look around the usual IBM manual haunts and, even though Roger Miller kindly gave a few manual URLs recently, I can't find the DB2 bookmanager manuals anywhere. A month or so ago I downloaded most of the OS/390 V7 manuals but I believe Roger mentioned recently [...] 15770 111 21_Re: URGENT: SMS & DB215_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Mon, 20 Aug 2001 10:01:28 +0200302_us-ascii I can't say ... from what I've seen, the only time it uses the 2nd volume is when it can't find space on the 1st, and so on. In other words it uses them sequentially. It certainly would stop it from "mixing" the TS's and IX's, but *may* lead to unefficient space usage - I'm not certain. [...] 15882 60 46_Re: No such thing as a free Platinum reference23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 20 Aug 2001 09:39:01 +0100519_- Peter

There is no need for this ... Hardy is no idiot.

The DB2 world is a very small and tightly knit family of people that help each other - I do believe you have just marked your cards with an awful lot of those people .....

"Happy job hunting when the day comes", however, you may wish to remove your foot from your mouth - I do believe there is a new tool on the market called the "foot extraction tool" (refer to a previous mail on this group) - supplied by a major third party vendor. [...] 15943 63 30_Index advisor for UDB DB2 V7.18_duam lee20_duam_lee@HOTMAIL.COM31_Mon, 20 Aug 2001 09:02:41 +0000323_- Hello all This is a question regarding index advice on a database for a query. The database alias name is pdmtest. I used to logon as DBA userid and password.The user id is pdm005 .It is okay. The objects are defined with schema as pdm003. When I go for db2advis for a particular query it throws me error like this. [...] 16007 99 34_AW: Index advisor for UDB DB2 V7.112_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 20 Aug 2001 11:23:56 +0200494_iso-8859-1 Duam,

behind the SQLSTATE 08004 can be two different SQL Codes: -30060 and -30061.

SQLCODE -30060: authorization failure encountered for RDB, SQLCODE -30061: Invalid or non-existent RDB specified.

HTH.

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

-----Ursprüngliche Nachricht----- Von: duam lee [mailto:duam_lee@HOTMAIL.COM] Gesendet am: Montag, 20. August 2001 11:03 An: DB2-L@RYCI.COM Betreff: Index advisor for UDB DB2 V7.1 [...] 16107 47 31_Question on how to implement RI15_Wayne Arrington33_WAYNE.ARRINGTON@CUSTOMS.TREAS.GOV31_Mon, 20 Aug 2001 06:37:01 -0400347_us-ascii I have a question for the group......

There has been a suggestion at my site that we remove Referential Integrity rules from our DDL statements and implement them through Stored Procedures and/or Triggers.

Has anyone attempted this ( How well does it function?) or What do you think are the Pros and Cons of this idea? [...] 16155 69 35_AW: Question on how to implement RI12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 20 Aug 2001 13:16:00 +0200446_iso-8859-1 Wayne,

from my point of view the old sentence is still true: Never change a well performing application. If you have referential integrity - with all the constraints and foreign keys inside - already installed why drop them and write implement Stored Procedures and/or Triggers to do the same work ? If your application runs only on DB2 for OS/390 subsystems I do not know an advantage if you are changing the application. [...] 16225 27 25_Re: Phonetic search tools13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Mon, 20 Aug 2001 06:39:12 -0500505_ISO-8859-1 Hi James !

We use a sotware called Name/IT from Human Inference (just check out www.humaninference.com) for our phonetic search purposes. I don´t know if it´ll work for english-spelled names, but for german-spelled names it seems to work :-) A nice feature in my opinion is the hit-ratio for the input, i.e. if you enter "Mouth" you´ll get let´s say for about 96% correctness for "Mouse". So you are able to build your result table out of entries with match for at least 80% or so. [...] 16253 27 11_LOB problem12_Hamid Alvani22_alvani@DPI2.DPI.NET.IR31_Mon, 20 Aug 2001 16:33:51 +0330520_US-ASCII Hello listener; I have a problem with DB2 Version 6.1.0.IVP Sample job(DSNTEJ71) in step PH071S04 end with this message:

CEE3250C The system or user abend S04E R=00C90101 was issued. From entry point buildPSEGcol at compile unit offset +000006BE at address 15725246.

Source program is DSN8DLPL in DSN610.SDSNSAMP and pre-compile, compile,pre-link,link,bind performed by cc=00,but run is ended by cc=08 . Has anyone seen anythings like this before?any idea would be appreciated. THANKS; Hamid [...] 16281 39 37_V7 and the number of tables in a join14_Randy Ebersole19_ebersole@US.IBM.COM31_Mon, 20 Aug 2001 08:02:58 -0400403_us-ascii Hi Julie, The number of tables in a join was increased prior to V7 to allow for more flexibility in writing joins. SPRMMXT(name changed in V7) macro was introduced to allow for the number of tables in a FROM to be increased above the standard 15 tables. This kind of functionality was delivered to allow some of the ERP and CRM vendors to deliver specific kind of SQL with their products. [...] 16321 71 33_Re: Index usage when MATCHCOLS=0.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 20 Aug 2001 07:11:47 -0500487_iso-8859-1 Sanjeev,

If a non-matching index scan is chosen (rather than a tablespace scan), then all index leaf pages will be scanned; regardless of how effective index screening is. If all qualifying rows are fetched, then only index matching predicates can limit the amount of index page I/O.

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 [...] 16393 73 35_Re: Question on how to implement RI0_22_blyons2@CSCPLOENZKE.DE31_Mon, 20 Aug 2001 14:29:09 +0200693_iso-8859-1 Wayne,

in my opinion, stored procedures and/or Triggers are more suitable for implementing 'non-standard' business logic in the database. I feel that, since referential integrity and its associated behaviour ist well-defined and well-understood by the db2 community, that it makes sense to implement these standard rules using standard RI. This makes the physical database design more easily understandable. Using stored procedures to implement something like this seems like overkill, and, in my opinion, it could result in people wasting time trying to figure out what a stored procedure does instead of simply interpreting an RI rule. Furthermore, most data modeling [...] 16467 118 33_Re: Index usage when MATCHCOLS=0.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 20 Aug 2001 18:15:53 +0530642_- Thanks for the correction. I had written "scanning may not be required" which was not correct. But as mentioned page I/O can be minimized for predicates. This is fundamental and sorry for the mistakes.

Thanks again Terry.

Regards Sanjeev

> -----Original Message----- > From: Terry Purcell [SMTP:Terry_Purcell@YLASSOC.COM] > Sent: Monday, August 20, 2001 5:42 PM > To: DB2-L@RYCI.COM > Subject: Re: Index usage when MATCHCOLS=0. > > Sanjeev, > > If a non-matching index scan is chosen (rather than a tablespace scan), > then > all index leaf pages will be scanned; regardless of how effective index > screening is. [...] 16586 36 29_Re: Supply of Data Propogator11_Carl Newton21_newtonc@UK.TIGPLC.COM31_Mon, 20 Aug 2001 14:27:55 +0100509_iso-8859-1 Thanks, anybody know about V6?

-----Original Message----- From: Ellen Richter [mailto:e2richte@HEWITT.COM] Sent: 17 August 2001 17:30 To: DB2-L@RYCI.COM Subject: Re: Supply of Data Propogator



Its chargeable on Version 7 - 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. [...] 16623 65 29_Re: Supply of Data Propogator12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 20 Aug 2001 06:46:33 -0700762_us-ascii Its chargeable on OS/390 V6 as well. --- Carl Newton wrote: > Thanks, anybody know about V6? > > -----Original Message----- > From: Ellen Richter [mailto:e2richte@HEWITT.COM] > Sent: 17 August 2001 17:30 > To: DB2-L@RYCI.COM > Subject: Re: Supply of Data Propogator > > > Its chargeable on Version 7 - 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. > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > This message and any attachments are confidential to [...] 16689 29 38_European DB2 Technical Conference 200212_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 20 Aug 2001 16:02:01 +0200342_iso-8859-1 Hello european DB2 listers,

the last european DB2 Technical Conference was held in Hamburg in March 2001.

What I want to know: When and where this conference will be held in the year 2002.

Someone able to give me a good advice ?

Thanks in advance for any information you are willing to pass along. [...] 16719 126 30_Re: MONTHNAME function missing14_William Gannon33_wgannon@EMAIL.PALMBEACH.K12.FL.US31_Mon, 20 Aug 2001 10:11:10 -0400325_us-ascii Hi all !

I kept this response to a very similar question asked on this list a long time ago - see below - maybe you can use it to satisfy your needs = thank the original author - I am just the record keeper ;-)

Bill G. ************** Pasted thread ************** "Peter Schwarcz (Bigpond)" wrote: [...] 16846 51 42_Re: European DB2 Technical Conference 200213_de Jonge, Jan20_Jan_De_Jonge@BMC.COM31_Mon, 20 Aug 2001 09:19:28 -0500563_iso-8859-1 Georg,

it will be Nice march 18-22. Take a look at http://www-3.ibm.com/services/learning/conf/futureconf.html.

Regards,

Jan

-----Original Message----- From: Peter, Georg [mailto:G.Peter@DZBW.DE] Sent: Monday, August 20, 2001 4:02 PM To: DB2-L@RYCI.COM Subject: European DB2 Technical Conference 2002



Hello european DB2 listers,

the last european DB2 Technical Conference was held in Hamburg in March 2001.

What I want to know: When and where this conference will be held in the year 2002. [...] 16898 61 15_Re: LOB problem10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Mon, 20 Aug 2001 10:15:58 -0500474_iso-8859-1 Hamid,

Rc=00C90101 is an inconstant index. In the past when I have seen that return code we rebuild the index(s) on the table your referencing. In other words there is an entry in the index and NOT in the table or entry in the table and NOT in the index.







______________________________ Reply Separator _________________________________ Subject: LOB problem Author: owner-db2-l@RYCI.COM at INTERNET Date: 8/20/01 8:19 AM [...] 16960 52 27_Open Position in Cincinnati12_Marc Sennett23_msennett@TEKSYSTEMS.COM31_Mon, 20 Aug 2001 10:46:24 -0500375_us-ascii Hello again, all.

Marc Sennett at Maxim Group/Teksystems. Still looking for a DBA with IDMS and DB2 experience for a fortune 500 company in Cincinnati..

This person will deal with various performance issues and system optimization. The data will eventually move to Oracle databases. At that point the client will offer training on Oracle as well. [...] 17013 34 42_AW: European DB2 Technical Conference 200212_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 20 Aug 2001 16:32:56 +0200563_iso-8859-1 Thanks Jan.

I have marked my calendar for attending the conference....

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

-----Ursprüngliche Nachricht----- Von: de Jonge, Jan [mailto:Jan_De_Jonge@BMC.COM] Gesendet am: Montag, 20. August 2001 16:19 An: DB2-L@RYCI.COM Betreff: Re: European DB2 Technical Conference 2002

Georg,

it will be Nice march 18-22. Take a look at http://www-3.ibm.com/services/learning/conf/futureconf.html. [...] 17048 50 71_Swap space / Paging space vs. physical memory - DB2 UDB Solaris and AIX14_Jeffrey Benner29_Jeffrey_Benner@NOTES.NTRS.COM31_Mon, 20 Aug 2001 09:45:44 -0500552_us-ascii It is not really clear in IBM documentation how much swapping / paging space to assign to a Unix host running DB2 UDB. I just came from a UDB 5.1 shop under AIX 4.3 where paging space was exactly equal to physical memory under IBM recommendations. At this current location, running UDB 7.2 under Solaris 8, we have been told by some IBM reps to set swapping space equal to 75% - 100% of physical memory. A TechNote about solving a memory related DB2 error under Solaris states that swapping space should be set to 100% - 200% of physical [...] 17099 34 31_Re: Invalid Page Header Warning0_25_pierre.evans@AUTOZONE.COM31_Mon, 20 Aug 2001 09:48:15 -0500528_us-ascii Mark, The last time I experienced 'Invalid Page Header' messages in EEE, we had some corrupted pages caused by faulty hardware;db2dart was able to diagnose the relevant corrupted indexes. Perhaps you can give that a try. HTH Pierre ---------------------------------------------------------------------------------------------------------

Pierre Evans Database Administrator, Data Warehouse Operations Autozone 123 South Front St, (901) 495-7851 Memphis TN 38103 Fax (901) 495-8300 pierre.evans@autozone.com [...] 17134 25 35_Re: Question on how to implement RI15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Mon, 20 Aug 2001 17:07:29 +0200296_us-ascii Also, horrible though it may be to comtemplate, at some point in time, you may want ( or be forced) to migrate from DB2 to another DBMS. In that case, it's going to be a WHOLE lot more effort to convert stored proc's and etc. from one dbms to another - whereas RI is really simple. [...] 17160 16 16_IMS List Address9_Bob Crail15_r.crail@ATT.NET31_Mon, 20 Aug 2001 16:01:51 +000064_- Doea anyone happen to know the name of the IMS discussion list 17177 42 41_OS/390 memory abend - Reorg w/ partitions10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Mon, 20 Aug 2001 11:03:21 -0500343_us-ascii OK. I have a 34 partition tablespace I am reorging a single partition at a time. My reorg statement is: REORG TABLESPACE MYDATABASE.MYTABLESPACE PART 19 SHRLEVEL CHANGE KEEPDICTIONARY SORTDEVT SYSDA SORTNUM 32 STATISTICS TABLE(ALL) INEX(ALL) REPORT YES UPDATE ALL

So, at random intervals (1 out of 5 runs) I get an abend: [...] 17220 66 45_Re: OS/390 memory abend - Reorg w/ partitions10_Bruce, Mae26_BruceM@MAIL.OA.STATE.MO.US31_Mon, 20 Aug 2001 11:15:22 -0500277_iso-8859-1 If I remember correctly, we ran into a syncsort problem on our reorgs when we first went to DB2 V6. We either ended up with a syncsort fix or had to go to a new release of syncsort (can't remember which.) But there was definetly a problem on the syncsort side. [...] 17287 39 20_Re: IMS List Address16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Mon, 20 Aug 2001 12:15:34 -0400443_us-ascii IMS-L you can subscribe at listproc@lists.missouri.edu in the normal fashion Subscibe IMS-L your name







Bob Crail on 08/20/2001 12:01:51 PM

Please respond to DB2 Data Base Discussion List

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





Doea anyone happen to know the name of the IMS discussion list 17327 24 36_MS Access data exported to DB2 OS3909_B.Boshoff21_b.boshoff@UTORONTO.CA31_Mon, 20 Aug 2001 12:26:07 -0400513_US-ASCII Hi, I am struggling to move data from MS Access to DB2 on OS390. Everytime I export data from MS Access it defines the table on the mainframe as well. I cannot figure out how to move JUST the data to a predefined table in DB2. Do I have to export it as a text file and then use the load utility to get it into DB2? Is there any way to export the data directly without using the load utility? Am I hoping for the impossible? I know very little (nothing) about MS Access. I would really appreciate it [...] 17352 56 40_Re: MS Access data exported to DB2 OS39013_Andy Seuffert25_Andy.Seuffert@NEONSYS.COM31_Mon, 20 Aug 2001 11:56:59 -0500458_iso-8859-1 Brenda, what you need to do is attach the DB2 table you wish to append data into, to your MS-Access database. Then in MS-Access you write an Append query to insert the data from the other table into the DB2 table. You can do this by using the MS-Access Query wizard to select the table you wish to copy from and the table you wish to copy to. Then select the Query menu option and select APPEND Query to change your query to an APPEND query. [...] 17409 64 40_Re: MS Access data exported to DB2 OS3909_B.Boshoff21_b.boshoff@UTORONTO.CA31_Mon, 20 Aug 2001 13:49:19 -0400545_US-ASCII Thanks, I will do that.

On Mon, 20 Aug 2001 11:56:59 -0500 Andy Seuffert wrote:

> Brenda, > what you need to do is attach the DB2 table you wish to append data into, > to your MS-Access database. Then in MS-Access you write an Append query to > insert the data from the other table into the DB2 table. You can do this by > using the MS-Access Query wizard to select the table you wish to copy from > and the table you wish to copy to. Then select the Query menu option and > select APPEND [...] 17474 78 30_Re: Load problem - please help16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Mon, 20 Aug 2001 11:14:41 -0700392_iso-8859-1 If you were able to load the file using a different product, then the file is apparently okay, so the next question is, are you sure your JCL is right? You have INDDN SYSREC40, is that DDNAME referring to the file that you're looking at?

Tony Provenzola Nike Database Services BEST Consulting * Phone: 503-532-0772 * Fax: 503-532-2618 * Email: Tony.Provenzola@nike.com [...] 17553 19 32_DSNREXX and FLOAT/DOUBLE columns14_Toppins, Smike21_smike.toppins@GWL.COM31_Mon, 20 Aug 2001 12:40:42 -0600426_- Does anyone have an example of a REXX module retrieving and displaying a FLOAT and/or DOUBLE column?



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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17573 21 34_Assembler and FLOAT/DOUBLE columns13_Harry Woelfer27_Harry_Woelfer@MCCORMICK.COM31_Mon, 20 Aug 2001 14:57:16 -0400367_us-ascii I was inspired to post this by the similar question just posted about REXX. Does anyone have an example of an OS/390 assembler module for displaying a FLOAT and/or DOUBLE column? That is, code to translate the internal floating point format to something like "3.6019E+04".

Thanks.

Harry Woelfer McCormick & Co. Harry_Woelfer@mccormick.com [...] 17595 80 30_Re: Automatic Increment Column0_24_db46@DAIMLERCHRYSLER.COM31_Mon, 20 Aug 2001 15:19:38 -0400403_us-ascii We tried to use identity columns with visual age for Java and reversed engineered EJB's. But we are having the following problems.

The table must have a primary key. If not the EJB will have an error in VA. But if you make the identity column the primary key, you must supply the primary key value when inserting a row with the EJB. This defeats the purpose of the identify column. [...] 17676 19 19_QUERYNO from a DBRM8_Harish G24_hari_gangadhar@YAHOO.COM31_Mon, 20 Aug 2001 14:14:53 -0500312_- Hi,

Is it possible to find out the statement number, which appears as the QUERYNO in the PLAN_TABLE for a SQL from the DBRM ? (DB2 for OS/390 V 5.1). Is so, could someone point to a technique for the same ? Everything except the SQLs and the IDs seems encrypted in the DBRM. Any REXX shortcuts ??? [...] 17696 105 30_Re: Load problem - please help10_Elsie Tama24_elsie.m.tama@VERIZON.COM31_Mon, 20 Aug 2001 15:38:55 -0400460_us-ascii It also looks like your Control card and your LoadLib or Sysrec does not match. Ensure that the data you're loading and the Control card are in thesame DDL. ---------------------- Forwarded by ELSIE M. TAMA/EMPL/VA/Bell-Atl on 08/20/2001 03:37 PM ---------------------------



"Provenzola, Tony" @RYCI.COM> on 08/20/2001 02:14:41 PM

Please respond to "DB2 Data Base Discussion List" [...] 17802 53 35_Re: Question on how to implement RI13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Mon, 20 Aug 2001 14:04:08 -0700595_us-ascii Wayne,

You will loose functionality. Stored procedures and triggers are not executed on a load, where part of the load process can be to check data. Also, if you worried about data integrity at some future point in time, Check data will only be of help if there is RI.

Tink --- Wayne Arrington wrote: > I have a question for the group...... > > There has been a suggestion at my site that we > remove Referential > Integrity rules from > our DDL statements and implement them through Stored > Procedures and/or > Triggers. > > Has [...] 17856 17 15_DBRM encodings.8_Harish G24_hari_gangadhar@YAHOO.COM31_Mon, 20 Aug 2001 15:55:09 -0500419_- Hi,

Where can I find information about the different character encodings and the significance of the character positions in a DBRM ?

Thanks much..

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. 17874 45 19_Re: DBRM encodings.12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 20 Aug 2001 16:51:49 -0500509_iso-8859-1 Harish:

What are you trying to do? We do a lot of work with the DBRM and not everything is where you might expect. Go the sysmacs dataset and look at dsnxdbrm and dsnxnbrm to see the macro mappings. The features change with release.

Gerald Hodge HLS Technologies, Inc. 281-265-3004

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Harish G Sent: Monday, August 20, 2001 3:55 PM To: DB2-L@RYCI.COM Subject: DBRM encodings. [...] 17920 13 46_Demetris Kasheris/LT/CPB is out of the office.17_Demetris Kasheris19_DKasheris@LAIKI.COM31_Tue, 21 Aug 2001 01:00:26 +0300336_us-ascii I will be out of the office starting 08/20/2001 and will not return until 09/03/2001.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17934 19 19_Re: DBRM encodings.8_Harish G24_hari_gangadhar@YAHOO.COM31_Mon, 20 Aug 2001 17:03:06 -0500291_- Thanks.

I am in the process of writing a tool which maps information from a DBRM to the PLAN_TABLE. Without going to SDSNMACS, I found this : A DBRM statement (just the program SQL part of it) can be construed to end with X'00'. Is this a true statement ?

Thanks much.. [...] 17954 100 27_Re: DB2 Bookmanager Manuals15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Tue, 21 Aug 2001 09:15:06 +1000389_- 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.

By the way, Bruce, itz fine. Nice Tri-nations game in the weekend. Pity you had to settle for a draw. You know the old (in my country, anyway) adage about supporting NZ and any team playing Oz... [...] 18055 40 36_Re: DSNREXX and FLOAT/DOUBLE columns10_teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 21 Aug 2001 09:41:27 +1000395_us-ascii Hi Smike,

If u have a REXX module which retrieves other type of columns, u shd be able to retrieve FLOAT/DOUBLE columns in the same way. R u getting any errors ?

Cheers, Kals



-----Original Message----- From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] Sent: Tuesday, August 21, 2001 4:41 AM To: DB2-L@RYCI.COM Subject: DSNREXX and FLOAT/DOUBLE columns [...] 18096 72 30_Re: MONTHNAME function missing14_Peter Schwarcz21_pschwarcz@AAMI.COM.AU31_Tue, 21 Aug 2001 09:37:12 +1000638_iso-8859-1 Just to provide an over the top version of the Month Name, the following SQL will return the a long form of the today's date



SELECT CASE DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7 WHEN 0 THEN STRIP('Sunday') WHEN 1 THEN STRIP('Monday') WHEN 2 THEN STRIP('Tuesday') WHEN 3 THEN STRIP('Wednesday') WHEN 4 THEN STRIP('Thursday') WHEN 5 THEN STRIP('Friday') WHEN 6 THEN STRIP('Saturday') END CONCAT ' the ' CONCAT STRIP(DIGITS(DAY(CURRENT DATE)),L,'0') CONCAT '''' CONCAT CASE DAY(CURRENT DATE) WHEN 1 THEN 'st' WHEN 2 THEN 'nd' WHEN 3 THEN 'rd' WHEN 21 THEN 'st' WHEN 22 THEN 'nd' WHEN 23 THEN 'rd' WHEN 31 [...] 18169 51 28_DB2 crash - Urgent Help reqd24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Mon, 20 Aug 2001 16:53:59 -0700803_us-ascii Hi,

One of the DB2 in our sysplex crashed with the following message

15.54.26 STC01004 DSNL027I -DB2P SERVER DISTRIBUTED AGENT WITH LUWID=GAB003E7.F0BB.037620191334=15185 THREAD-INFO=IDAGTDSH:hm2uxapa:idagtdsh:java RECEIVED ABEND=04E FOR REASON=00E20003 15.54.26 STC01004 DSNL028I -DB2P GAB003E7.F0BB.037620191334=15185 ACCESSING DATA FOR LOCATION 10.176.3.231 IPADDR 10.176.3.231

The explanation is like this ******************************************************************************** 00E20003

Explanation: In order to satisfy an unconditional request for storage, the get variable storage function needed to obtain storage (from GETMAIN) to expand a storage pool. However, GETMAIN indicated that sufficient storage in the private area was not available. [...] 18221 62 41_Re: V7 and the number of tables in a join8_K.Balaji19_K.Balaji@TARGET.COM31_Mon, 20 Aug 2001 18:57:53 -0500364_iso-8859-1 Hi Randy, In my production shop I tried to execute a SQL with 11 or 12 joins in test environment but it failed with error saying 'temporary storage space is insufficient'. Can you suggest me any ideas anything to overcome this problem ?

And how will the performace be for so many table joins ? I am using matching index columns for my join. [...] 18284 84 45_Re: OS/390 memory abend - Reorg w/ partitions14_Jack O'Connell26_jack.oconnell@MCMASTER.COM31_Mon, 20 Aug 2001 19:20:10 -0500287_iso-8859-1 Dumb question probably, but do you have a region card on the job or the step limiting you below the mainsize you are requesting? Did you try with a REGION=0M ? The mainsize you specify applies to each one of the sort subtasks in DFSORT ( if that is what you are using ). [...] 18369 55 19_Re: DBRM encodings.12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 20 Aug 2001 20:18:56 -0500484_iso-8859-1 Harish:

The statement in the DBRM begins DBRM. Positions 13 through 16 are the statement number in full word format. If you are dealing with old DBRMs then the small init issue comes up which is the statement number resets to zeros if the small init overflows. As I recall the end of the statement is a half word of 00 so it would be x'0000'. I caution anyone in trying to map the DBRM without the mapping macros. We have found the DBRM interesting to dissect. [...] 18425 60 30_Re: DB2 OS390/Z/OS Opportunity12_C.L.Phillips25_clphillips@MINDSPRING.COM31_Mon, 20 Aug 2001 21:31:59 -0400327_us-ascii Hi! I would be very interested in speaking to you about your clients' needs. I have 10+ years expereience in DB2 on mainframes and 5+ years on other platforms.

If you would mind giving me a call at 770-971-9492, I would appreciate your time. I look forward to speaking with you and exchanging information. [...] 18486 30 45_Re: OS/390 memory abend - Reorg w/ partitions10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 21 Aug 2001 09:02:41 +0200590_us-ascii Hi Missy.

It 's strange but when I worked with CA utilities & toolz I met the same error using syncsort. I heard that many people using syncsort has memory problems and/or abends during sort phases in reorg/load executions.

I think that Bruce Mae is right, it was suggested to use a new release when we met the same memory problems. Contact syncsort and beat them. IBM's solution is a short-term solution as, soon or later, you'll meet again this problem as it depends, among others, on the 'traffic' in your storage area (if I remember well there were problems [...] 18517 35 9_SQL Query20_Subramaniam Baskaran27_subramaniam.baskaran@DB.COM31_Tue, 21 Aug 2001 12:57:25 +0530383_us-ascii Hello List Users,

I have a requirement to count the distinct occurrence of two columns.

eg select count (distinct(name , code)) from tablea

When I tried the above query, it resulted in a sql error with sql code -104. But, when I tried the following query, it works

select count (distinct(name)) from tablea

Is this a SQL limitation?? [...] 18553 57 13_Re: SQL Query10_teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 21 Aug 2001 17:48:59 +1000445_us-ascii Hi Baskar,

There might be other options, but one immediate method I cd think of was ,

select count(distinct name||code) from tablea.

The -104 u got was due to the fact that distinct expects only one argument.

Cheers, Kals



-----Original Message----- From: Subramaniam Baskaran [SMTP:subramaniam.baskaran@DB.COM] Sent: Tuesday, August 21, 2001 5:27 PM To: DB2-L@RYCI.COM Subject: SQL Query [...] 18611 64 13_Re: SQL Query14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Tue, 21 Aug 2001 17:49:35 +1000430_iso-8859-1 Hi Baskar,

Provided both columns are CHAR data, this will work:

select count (distinct(name||code)) from tablea

It's not crystal clear in the doco, but the COUNT function takes an expression as it's argument, and it appears that does not allow you to use multiple columns. If I had a spare lifetime to follow it through the various manuals, I might be able to give a more definitive answer :) [...] 18676 86 13_Re: SQL Query16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 21 Aug 2001 13:15:58 +0530617_- Baskar,

I hope this is what you are looking for.

--- select count(*) from (Select distinct name, code from tablea) ;

Hope this works as well....

Regards Sanjeev

> -----Original Message----- > From: Subramaniam Baskaran [SMTP:subramaniam.baskaran@DB.COM] > Sent: Tuesday, August 21, 2001 12:57 PM > To: DB2-L@RYCI.COM > Subject: SQL Query > > Hello List Users, > > I have a requirement to count the distinct occurrence of two columns. > > eg > select count (distinct(name , code)) from tablea > > When I tried the above query, it resulted in a sql error with sql code > -104. [...] 18763 47 23_Re: QUERYNO from a DBRM10_teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 21 Aug 2001 18:07:13 +1000445_us-ascii Harish,

Based on subsequent postings abt the DBRM encodings, I presume that u already know abt the mapping macro of the DBRM in SDSNMACS. U cd then find the lines containing the word 'DBRM' in col. 1, from which the cols. 13 to 16 give the statement no. and cols. 21-24 give u the length of the statement, based on which u can extract the statements. U cd similarly extract other pieces of infm u want, like host variables. [...] 18811 90 32_Re: DB2 crash - Urgent Help reqd23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 21 Aug 2001 10:15:47 +0100321_- yes we had a similar thing occur recently.

In order to get sufficient storage in the private area we had to apply some maintenance ... I do not have the fixes to hand .. our problem was with the fact that storage was not being freed correctly ... have you logged this with IBM? searched the APAR databases? [...] 18902 46 41_DB2 Stored Procedure builders and schemas14_Curran, John F29_john.f.curran@UK.FID-INTL.COM31_Tue, 21 Aug 2001 10:54:21 +0100418_iso-8859-1 Hi

I may be missing something obvious here but I would be grateful for any help



We are running Version 7.1 of DB2 on AIX

and I have installed Version 7 of stored procedure builder on a windows 2000 client

I have a lot of pre-written SQL stored procedures already written

I log on as user1 and the tables I have created are all qualified by the schema schema1 [...] 18949 23 16_manual GH21-10839_jane mike18_ibm390@HOTMAIL.COM31_Tue, 21 Aug 2001 09:56:02 +0000542_- hi list,

where i can find this manual ?

GH21-1083 "IMS-DB and DB2 Migration and Coexistence Guide".

Have a nice day.

mike.

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

================================================ 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. 18973 79 20_Re: manual GH21-108316_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 21 Aug 2001 11:33:58 +0100495_us-ascii You can find it at

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNMCH10/BIBLIOGRAPHY

HTH.

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 [...] 19053 31 24_ZPARM settings in DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 21 Aug 2001 07:10:37 -0400351_iso-8859-1 Has anyone run into having to change the SORT POOL & RID POOL size settings in the zparm when going from DB2 V5 to DB2 V6?? What I'm hearing is that it can affect performance. Seems DB2 V5 does some calculation and DB2 V6 does not, so our system programmers are going to set the zparm settings back to what we had them at with DB2 V5. [...] 19085 81 32_Re: DB2 crash - Urgent Help reqd16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Tue, 21 Aug 2001 07:25:13 -0400359_iso-8859-1 Maha,

Just as a place to start, I saw two APARs in the APAR database for the 00E20003 abend that were specifically related to storage leaks affecting DDF/DRDA: PQ37926 and PQ41833. Also, informational APAR II18817 has a list of APARs for storage-related errors.

Did you have an SVC dump with this abend? What was the dump title? [...] 19167 60 32_IDUG in Italia (Italian version)14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT31_Tue, 21 Aug 2001 13:47:50 +0100626_ISO-8859-1 ------------------------------------------------------------------- Name: Massimo Ceraso E-mail: Massimo.Ceraso@tlsoft.it Phone: +39-081-7564798 Date: 08/21/2001 Time: 13:41:32

International DB2 User Group European Conference Planning Committee (web site: www.idug.org) 1999-2000-2001

DB2 List serve IBM Certified Solution Expert DB2 UDB V5 Application Development IBM Certified Solution Expert DB2 S390 V7 Database Administration

President of GIUDiCA (Gruppo Italiano Utenti DB2 Tools in Computer Associates - Italian DB2 User Group) (web site: www.ca.com/offices/italy/local/db2-ug.htm) [...] 19228 15 26_external stored procedures10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Tue, 21 Aug 2001 07:50:32 -0500506_- Can anybody point me to documentation on using external(non SQL language) stored procedures. The redbook I have only deals with the SQL language.

I am must interested in the program preparation information for these.

Thanks in advance for you help.

================================================ 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. 19244 91 41_Re: V7 and the number of tables in a join11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Aug 2001 08:55:16 -0400539_US-ASCII Balaji, Since you do not give the error message, I can see this happening in a couple of places, depending on how you are executing the query. The first one that comes to mind is does this query perform a sort, if so, how many sortwork spaces do you have and how large are they? Second, would be the dataset being used for your output. In QMF, you have the dsqspill dataset. In spufi, you have whatever dataset you have specified. The number of tables shouldn't be a problem as long as you have good access paths to the data [...] 19336 34 30_Re: external stored procedures14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 21 Aug 2001 09:08:21 -0400505_iso-8859-1 There is a redbook called: DB2 Java Stored Procedures: Learning By Example. This deals with Java stored procedures, including SQLJ.

Abhijit

-----Original Message----- From: Toni Addie [mailto:Toni_Addie@PROGRESSIVE.COM] Sent: Tuesday, August 21, 2001 8:51 AM To: DB2-L@RYCI.COM Subject: external stored procedures



Can anybody point me to documentation on using external(non SQL language) stored procedures. The redbook I have only deals with the SQL language. [...] 19371 48 30_Re: external stored procedures18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Tue, 21 Aug 2001 09:18:16 -0400609_iso-8859-1 For what environment? You can also have COBOL stored procedures...

-----Original Message----- From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] Sent: Tuesday, August 21, 2001 9:08 AM To: DB2-L@RYCI.COM Subject: Re: external stored procedures



There is a redbook called: DB2 Java Stored Procedures: Learning By Example. This deals with Java stored procedures, including SQLJ.

Abhijit

-----Original Message----- From: Toni Addie [mailto:Toni_Addie@PROGRESSIVE.COM] Sent: Tuesday, August 21, 2001 8:51 AM To: DB2-L@RYCI.COM Subject: external stored procedures [...] 19420 62 30_Re: external stored procedures14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 21 Aug 2001 09:27:38 -0400490_iso-8859-1 UNIX, NT and OS/390.

-----Original Message----- From: Krawetzky, Peter J [mailto:KrawetzkyPJ@AETNA.COM] Sent: Tuesday, August 21, 2001 9:18 AM To: DB2-L@RYCI.COM Subject: Re: external stored procedures



For what environment? You can also have COBOL stored procedures...

-----Original Message----- From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] Sent: Tuesday, August 21, 2001 9:08 AM To: DB2-L@RYCI.COM Subject: Re: external stored procedures [...] 19483 30 24_Stored Procedure Builder0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 21 Aug 2001 09:27:45 -0400725_us-ascii Is the stored procedure builder a separate piece of software?

Is it built-in to say a UDB version 7.1 client on a Unix box?

Dean









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

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. **************************************************************************** [...] 19514 26 94_Is there a vendor tool/utility which can extract Data from an Ima ge Copy on DB2 UDB AIX EEE ?16_Serge Brigljevic29_Serge.Brigljevic@SWISSCOM.COM31_Tue, 21 Aug 2001 15:29:09 +0200372_iso-8859-1 Is there a vendor tool/utility which can extract Data from an Image Copy on DB2 UDB AIX EEE ?

Regards,

Serge Brigljevic Head of Database Services

Swisscom AG Telefon +41 31 342 54 88 Corporate Information & Technology Telefax +41 31 342 63 47 System Engineering & Operations E-Mail: serge.brigljevic@swisscom.com Postfach CH-3050 Bern [...] 19541 62 36_Re: DSNREXX and FLOAT/DOUBLE columns14_Toppins, Smike21_smike.toppins@GWL.COM31_Tue, 21 Aug 2001 07:56:46 -0600607_- Not getting errors but the data is not coming back in a readable format or a format that I can decipher off the top of my head.



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: Monday, August 20, 2001 5:41 PM > To: DB2-L@ryci.com > Subject: Re: DSNREXX and FLOAT/DOUBLE columns > > Hi Smike, > > If u have a REXX module which retrieves other type of columns, u shd be > able to retrieve FLOAT/DOUBLE columns in the same way. R u getting any [...] 19604 19 31_Unload & Object Comparison tool12_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM31_Tue, 21 Aug 2001 09:52:26 -0400436_- Hi List - We are on DB2 V 7.1 OS/390. We are looking for a high performance unload and an object comparison tool. Has anyone any suggestions ? Thanks



Regards,

Anil Mathur

================================================ 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. 19624 52 98_Re: Is there a vendor tool/utility which can extract Data from an Ima ge Copy on DB2 UDB AIX EEE ?12_Cook, Nicole19_Nicole_Cook@BMC.COM31_Tue, 21 Aug 2001 09:03:20 -0500630_iso-8859-1 BMC Software will have a tool available next month that will be able to unload data from a backup file. The product is called Recovery for DB2 Universal Database. Check out http://www.bmc.com/products/distdata/index.html !!

Nicole Cook DB2 UDB Platform Specialist Product Management bmcsoftware Austin, Texas 512.340.6817 800.841.2031 ncook@bmc.com

-----Original Message----- From: Serge Brigljevic [mailto:Serge.Brigljevic@SWISSCOM.COM] Sent: Tuesday, August 21, 2001 8:29 AM To: DB2-L@RYCI.COM Subject: Is there a vendor tool/utility which can extract Data from an Ima ge Copy on DB2 UDB AIX EEE ? [...] 19677 142 35_Re: Unload & Object Comparison tool14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 21 Aug 2001 15:32:47 +0100310_iso-8859-1 Wow

An easy question - can I be first to answer??

Anyway, to start with Unloads, you have a ridiculous amount of choice. For starters there are at least three from IBM (depending on how you count them) and one from each of BMC, CA, CDB and Infotel (notice - alphabetical order!!) [...] 19820 80 35_Re: Unload & Object Comparison tool16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Tue, 21 Aug 2001 15:49:35 +0100584_us-ascii Hello Anil,

there are a whole lead of Object Comparison tools from a variety of vendors, sometimes the same vendors make tools that can be coupled with Unload tools, others (like ourselves) do not.

For what concerns the Unload, we do not offer a solution in that field, apart from the now-separately-priced IBM solutions you'll find that BMC or CA or CDB offer you a choice of things. There are also other tools from other vendors, however there's so many people on the market I hope they'll all accept my apologies for mentioning only the BIG guys... ;-) [...] 19901 98 30_Re: external stored procedures14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Tue, 21 Aug 2001 07:53:42 -0700514_iso-8859-1 If you looking for information on DB2 for OS/390, look at the Application Programing and SQL Guide manual. For DB2 for Windows and Unix, look in the Application Development Guide. Hope this helps Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

-----Original Message----- From: Toni Addie [mailto:Toni_Addie@PROGRESSIVE.COM] Sent: Tuesday, August 21, 2001 7:51 AM To: DB2-L@RYCI.COM Subject: [DB2-L] external stored procedures [...] 20000 17 19_Re: DBRM encodings.8_Harish G24_hari_gangadhar@YAHOO.COM31_Tue, 21 Aug 2001 09:48:35 -0500525_- Thanks a lot, Gerald. I am using DB2 5.1. Upon my manual verification of some of the DBRMs, I found that every statement in a DBRM ends with X'00'. Definitely DBRM mapping is interesting, and writing tools oneself makes one learn so much more.

Thanks again.

- 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. 20018 117 32_Re: DB2 crash - Urgent Help reqd14_Hickman, Mindy29_Mindy.Hickman@DOA.STATE.WI.US31_Tue, 21 Aug 2001 10:07:53 -0500559_iso-8859-1 We've also had this occur. Unfortunately, the fixes that we found and applied did not work. I'd be curious to know which ones you applied. We do have a problem open with IBM on this.



Mindy Hickman State of Wisconsin - DOA DB2 DBA IBM Certified Solutions Expert 608-261-5059 mindy.hickman@doa.state.wi.us





-----Original Message----- From: Pendlebury-Bowe, Leslie [mailto:Leslie.Pendlebury-Bowe@EU.SONY.COM] Sent: Tuesday, August 21, 2001 4:16 AM To: DB2-L@RYCI.COM Subject: Re: DB2 crash - Urgent Help reqd [...] 20136 135 15_Re: LOB problem12_Hamid Alvani22_alvani@DPI2.DPI.NET.IR31_Tue, 21 Aug 2001 19:42:34 +0330620_ISO-8859-1 00C90101 Explanation: The data manager detected an internal error within DB2. This error may indicate inconsistent data or an error in internal DB2 control structures or code. This abend reason code is issued by the following CSECT: DSNnnnnn 'nnnnn' is the CSECT name identifier given in VRARRK5 of the VRA in the SDWA (see the Problem Determination section of this message). 'DSNnnnnn' is the full CSECT name given in the dump title. Possible causes of this error include: . Improper migration or fall back procedures, . DB2 directory and DB2 Catalog restored to different points in time, . Table space [...] 20272 44 31_UDB on Windows restore question0_20_John_Lendman@FPL.COM31_Tue, 21 Aug 2001 10:56:26 -0400304_us-ascii I am a mainframe DB2 who also has a DB2 UDB on Windows to maintain. I am trying to restore a production database from a backup copy using the "Create New" option on the control center. It appears that the command it creates is good, But every time I run it DB2 can not find the image copy. [...] 20317 155 32_Re: DB2 crash - Urgent Help reqd24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Tue, 21 Aug 2001 08:30:53 -0700434_us-ascii We have sent the dump to IBM and awaiting their response on this. I will let you know the APAR number once confirmed.

Regards

Maha





"Hickman, Mindy" cc: Sent by: DB2 Data Subject: Re: DB2 crash - Urgent Help reqd Base Discussion List



08/21/01 08:07 AM Please respond to DB2 Data Base Discussion List [...] 20473 71 58_IDUG North America 2002 Call for Presentations -- Reminder14_Philip Gunning24_philip.gunning@QUEST.COM31_Tue, 21 Aug 2001 08:35:21 -0700567_- Fellow DB2 professionals, The deadline for abstract submission is just 2 weeks 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 [...] 20545 19 13_UDB V7 Client14_Toppins, Smike21_smike.toppins@GWL.COM31_Tue, 21 Aug 2001 09:49:18 -0600420_- Can anyone tell me if UDB V7 (client) is backward compatible to attach to a UDB V5.2 database?



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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20565 60 13_Re: SQL Query16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Tue, 21 Aug 2001 09:05:12 -0700435_iso-8859-1 I haven't tried this myself but maybe you could do something like this:

select count (distinct(name||code)) from tablea

Concat NAME and CODE, if same data type, together and see how many unique occurrences you have.

Duane

-----Original Message----- From: Subramaniam Baskaran [mailto:subramaniam.baskaran@DB.COM] Sent: Tuesday, August 21, 2001 12:27 AM To: DB2-L@RYCI.COM Subject: SQL Query [...] 20626 175 35_Re: Unload & Object Comparison tool16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Tue, 21 Aug 2001 12:17:52 -0400287_iso-8859-1 Phil

With a lead in like that (or lack of) how can I not 'chip in'

First of all Compuware has the ONE DBA tool that includes complex object comparisons as well as other DBA functions. (note the ONE). It also can compare DDL to DDL or DDL to object etc. [...] 20802 28 44_It's been real....change in career direction15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Tue, 21 Aug 2001 11:18:19 -0500515_iso-8859-1 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 stuff. I haven't had much time to monitor the List but have been checking it from time to time. I will continue to do so until all of my DB2 brains shrivel up and die. It's been fun corresponding with the list and I have learned plenty. I hope that I was able to pass along a little knowledge as [...] 20831 99 13_Re: SQL Query0_19_Tim.Lowe@STPAUL.COM31_Tue, 21 Aug 2001 11:20:13 -0500498_us-ascii Will this method work for you? SELECT COUNT(*) FROM (SELECT DISTINCT PLNAME,NAME FROM SYSIBM.SYSDBRM) A;







Duane Lee - ATCX To: DB2-L@RYCI.COM Subject: Re: SQL Query Sent by: DB2 Data Base Discussion List



08/21/2001 11:05 AM Please respond to DB2 Data Base Discussion List











I haven't tried this myself but maybe you could do something like this: [...] 20931 106 13_Re: SQL Query0_26_JCameron@MSI-INSURANCE.COM31_Tue, 21 Aug 2001 11:20:34 -0500547_us-ascii How about :

select count(*) from (select distinct name, code from tablea) as tab1 ;





John Cameron JCameron@msi-insurance.com







Duane Lee - ATCX To: DB2-L@RYCI.COM Subject: Re: SQL Query Sent by: DB2 Data Base Discussion List



08/21/2001 11:05 AM Please respond to DB2 Data Base Discussion List











I haven't tried this myself but maybe you could do something like this: [...] 21038 84 35_Re: UDB on Windows restore question0_24_markus.schaub@SYSTOR.COM31_Tue, 21 Aug 2001 18:26:27 +0200337_us-ascii for the restore udb is expecting a given path on your c drive to find the file to be used. as far as i can remember the path at your c drive should be:

c:\whspro.0\db2\node0000\catn0000\date when the backup was taken in the form of yyyymmdd

but to be sure check out the backup path of your production system. [...] 21123 92 58_OS390 DB2 V6.1 - Rebuild Index(all) on catalog & Directory12_Aedan Molony20_Aedan.Molony@PSIR.IE31_Tue, 21 Aug 2001 17:35:36 +0100719_iso-8859-1 Hi list...

At our DR test next week, we were thinking of doing the above as part of cat/dir recovery, instead of explicitly rebuilding each index separately... i.e. SYSINDEXES etc is not availiable...is this do-able???

Thanks in advance

Aedan Molony DB2 DBA



******************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately at MailManager@psir.ie and delete this E-mail from your system. Thank you. It is possible for data transmitted by email [...] 21216 181 13_Re: SQL Query18_Jean-Jacques Borie18_jjborie@TEKORA.COM31_Tue, 21 Aug 2001 18:35:13 +0200482_iso-8859-1 Another way cheaper because he avoid a sort operation caused by the distinct keyword SELECT count(*) FROM (SELECT name, code FROM tablea GROUP BY name, code) as alias





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 [...] 21398 15 30_DSNTPSMP - Procedure processor10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Tue, 21 Aug 2001 12:50:36 -0400379_us-ascii Is anybody using this successfully in their shop? We are getting a -194 when trying to run the IVP IBM delievered.

Thanks

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 21414 134 35_Re: UDB on Windows restore question0_20_John_Lendman@FPL.COM31_Tue, 21 Aug 2001 13:04:43 -0400439_us-ascii Thanks for the reply. but we have put the backup in the exact same directory as it came from in production.

Like this C:\wprodbkup\whspro.0\db2\node0000\catn0000\20010815\230001.001

Where we just gave the control center c:\wprodbkup and let it figure out the rest.

We also tried to give it the whole path.

Neither one seems to work we still get the error message. Not able to find the image copy. [...] 21549 69 23_Re: QUERYNO from a DBRM12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 21 Aug 2001 20:03:31 +0200476_iso-8859-1 Hi, I've posted a rexx to analyze the dbrm sometime ago. It handles the stmt. numbers as well. Should be on the document list

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "teldb2kals" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, August 21, 2001 10:07 AM Subject: Re: QUERYNO from a DBRM [...] 21619 86 36_Re: DSNREXX and FLOAT/DOUBLE columns12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 21 Aug 2001 20:06:20 +0200559_windows-1255 Hi, Try to use DECIMAL( ) on it . e.g.: "select decimal(float_number,p,q) ...." where p,q are the scale & precision. While decimal is not as accurate it may solve your rexx interface problem.

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: Tuesday, August 21, 2001 3:56 PM Subject: Re: DSNREXX and FLOAT/DOUBLE columns [...] 21706 126 13_Re: SQL Query12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 21 Aug 2001 20:09:46 +0200437_iso-8859-1 Hi, As the friendly SQL manual (V7) says :

The schema is SYSIBM.

The COUNT function returns the number of rows or values in a set of rows or

values.

The argument values can be of any built-in data type other than a BLOB,CLOB,or

DBCLOB.If DISTINCT is used,the resulting expression cannot have a maximum

length greater than 255 for a character column and 127 for a graphic column. [...] 21833 126 48_Re: It's been real....change in career direction12_Troy Coleman19_Colematr@MEIJER.COM31_Tue, 21 Aug 2001 13:19:35 -0400705_US-ASCII Hi Linda, I've always enjoyed your correspondence. We will miss you. Good luck with the new career move.



Troy Coleman Coleman Consulting, Inc.

IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 DB2 UDB V5 Database Administration IBM Certified Advanced Technical Expert DB2 -DRDA

Voice: (847) 722-2698 Email: troycci@colemanconsulting.com



>>> linda.billings@DOA.STATE.WI.US 08/21/01 12:18PM >>> 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 stuff. I haven't had much [...] 21960 171 47_DB2 UBD DBA w/Linux or AIX needed in Boston, MA13_Hopkins, John20_JMHopkins@KFORCE.COM31_Tue, 21 Aug 2001 13:28:23 -0400506_- Still seeking at experience DB2/DBA for a position Metro-West of Boston. The position is located about 1/2 hr West of Boston, MA.

I am recruiting for a DB2-UDB expert database administrator - with UNIX AIX and Linux Administration capabilities.

Programming experience with C, C++ and Java would be a plus.

MUST BE ABLE TO understand and contribute to the design of an object restore utility for the UBD DB2 Linux/AIX platforms and oversee the actual implementation of the tool. [...] 22132 30 24_00C9008E on hash anchors15_Hans Schuermann18_hschuermann@DIS.CH31_Tue, 21 Aug 2001 12:24:21 -0500379_- We are getting 00C9008E (timeout) errors from time to time. The resource type is 00000903 (hash anchor within DSNDB01.DBD01). These errors did not occure before migration to V6.1. We have two-way data sharing and in all cases we have seen until now, the lock was being held by a batch job on member one and the timed out processes (jobs, started tasks) run on member two. [...] 22163 178 32_Re: DB2 crash - Urgent Help reqd12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 21 Aug 2001 20:28:26 +0200495_iso-8859-1 Hi, What's the situation with CSA/ECSA - can it be that you are short over there ? Just a guess :-(

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Mahadevan Krishnamoorthy" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, August 21, 2001 5:30 PM Subject: Re: DB2 crash - Urgent Help reqd [...] 22342 35 31_Unavailable Resource - SYSLGRNX11_Dale Miller15_dmiller@UGI.COM31_Tue, 21 Aug 2001 13:43:42 -0400581_us-ascii Listers, I have run into a problem and am trying to explain why it happened. While doing an Image copy of the DB2 catalog and Directory, the SYSLGRNX Tablespace was put into RO mode and the DB2 subsystem started issuing "DSNT500I -DSNP DSNUAHR2 RESOURCE UNAVAILABLE" , REASON 00C90080 messages to the master console. It flooded the console to the point where no one could log on to the system or get any response from DB2. Once SYSLGRNX was started for RW, everything went back to normal, but I am having problems pinpointing the true cause. The Image Copy was being [...] 22378 48 28_Re: Stored Procedure Builder12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 21 Aug 2001 10:53:16 -0700630_us-ascii The stored procedure builder is part of UDB, but is in the application tools portion. Its not installed as part of the basic install of UDB. But can be. Same with the client. --- db46@DAIMLERCHRYSLER.COM wrote: > Is the stored procedure builder a separate piece of software? > > Is it built-in to say a UDB version 7.1 client on a Unix box? > > Dean > > > > > > **************************************************************************** > > The information contained in this transmission, which may be > confidential and proprietary, is only for the intended recipients. > Unauthorized use is strictly prohibited. [...] 22427 22 15_Newbie question14_Wells, Rosanne34_rosanne.wells@COUNTRYFINANCIAL.COM31_Tue, 21 Aug 2001 12:45:44 -0500330_iso-8859-1 8/21/01

Hello,

I just joined this list yesterday and I made the mistake:-) of subscribing to the regular list instead of the Digest (I'm not sure what the Index list is). I have gone back and subscribed to the Digest list. Do I need to unsubscribe to the regular list to stop all the emails? Thanks. [...] 22450 174 35_Re: UDB on Windows restore question15_Stacey L Waters23_WATERSS1@NATIONWIDE.COM31_Tue, 21 Aug 2001 13:04:31 -0500442_us-ascii RESTORE DATABASE whspro user db2admin using db2admin FROM C:\wprodbkup TO C: INTO whspro2 WITH 2 BUFFERS BUFFER 1024 WITHOUT ROLLING FORWARD WITHOUT PROMPTING

Try making the change that is in bold.

Hope this helps! Stacey









John_Lendman@FPL.CO M T To: DB2-L@RYCI.COM Sent by: DB2 Data cc: Base Discussion List bcc: Subject: Re: UDB on Windows restore question [...] 22625 75 35_Re: Unavailable Resource - SYSLGRNX15_Murley, Michael22_Michael_Murley@BMC.COM31_Tue, 21 Aug 2001 13:08:19 -0500580_iso-8859-1 Dale,

Were you doing a SHRLEVEL reference copy of the catalog and directory ?

The correlation id you saw is a DB2 system agent correlation id. These are documented in the DB2 Diagnosis Reference (starting on page 499 in the DB2 V6 book). The O21.OPNLGR00 agent performs the update to SYSLGRNX when a pageset is opened for update. If SYSLGRNX is unavailable for update, this agent will end with a Resource Unavailable message. It sounds like someone was trying to open a pageset for update while a SHRLEVEL REFERENCE copy of SYSLGRNX was in progress. [...] 22701 71 35_Re: Unavailable Resource - SYSLGRNX13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 21 Aug 2001 11:13:23 -0700488_iso-8859-1 From Utility guide:

"To run on DSNDB01.SYSUTILX, COPY must be the only utility in the job step. Further, if SHRLEVEL REFERENCE is specified, the COPY job of DSNDB01.SYSUTILX must be the only utility running in the Sysplex."

Are you conforming to this?

Thanks. Ashish.



-----Original Message----- From: Dale Miller [SMTP:dmiller@UGI.COM] Sent: Tuesday, August 21, 2001 10:44 AM To: DB2-L@RYCI.COM Subject: Unavailable Resource - SYSLGRNX [...] 22773 189 35_Re: UDB on Windows restore question10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Tue, 21 Aug 2001 11:45:28 -0700601_us-ascii John,

Since you are trying to resore db into diffrent server you have to resore database with redirect command which is in the command reference book on page 486.

Thanks,

Mathai Joy --- John_Lendman@FPL.COM wrote: > Thanks for the reply. but we have put the backup in > the exact same > directory as it came from in production. > > Like this > C:\wprodbkup\whspro.0\db2\node0000\catn0000\20010815\230001.001 > > Where we just gave the control center c:\wprodbkup > and let it figure out > the rest. > > We also tried to give it the whole path. > > Neither one seems [...] 22963 106 41_Re: V7 and the number of tables in a join8_K.Balaji19_K.Balaji@TARGET.COM31_Tue, 21 Aug 2001 14:01:34 -0500337_iso-8859-1 Dave, The exact error I am getting is 'Temporary storage insufficient (Reason Code - 00C900A5, Resource type - 00000230)'.

Since you mentioned about the sort I will again make sure that my joins go thro matching index scan and will communicate to you soon.

Thanks for you suggestion. It did gave me a clue. [...] 23070 137 41_Re: V7 and the number of tables in a join11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Aug 2001 15:49:14 -0400509_US-ASCII Balaji, You're problem is your sort/work files in DSNDB07 are either too small or your query is possibly returning a lot more rows than you are expecting(maybe a cartesian product? easy to do when you start joining tables). The matching index columns on the join wouldn't neccessarily mean anything about the sort/work files. It would be more that you are having some type of interim materialization or sorting of data. Take a look at your explain it would give you more info to determine this. [...] 23208 74 54_Re: Freeing allocated resources in a stored procedure.8_K.Balaji19_K.Balaji@TARGET.COM31_Tue, 21 Aug 2001 15:50:33 -0500503_iso-8859-1 Hi, IBM has some documentation on recursive stored procedure calls in the following web site : http://www-4.ibm.com/software/data/db2/udb/ad/ Hope this help you. Balaji

-----Original Message----- From: Nyheim, Arild [mailto:arild.nyheim@DNB.NO] Sent: Thursday, August 16, 2001 3:42 AM To: DB2-L@RYCI.COM Subject: Freeing allocated resources in a stored procedure.



One of our programmers has come up with this question, when calling a stored procedure from another. [...] 23283 43 27_Correlated table expression11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Aug 2001 17:33:51 -0400522_US-ASCII OS/390 DB2/V6

I'm trying to write a statement that has a table expression that I would like to have correlated to another table expression, but it keeps resulting in a -206 column doesn't exist in any table in the query. The sql for ease of understanding goes something like this:



select x.cola from (select a.cola from a, b, c, d, e, f where ....) as x ,(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 ..... [...] 23327 80 31_Re: Correlated table expression13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 21 Aug 2001 15:09:07 -0700588_us-ascii David,

I am not sure we have enough information to do what you need but what is wrong with the following?

select a.cola , sum(b.colb) from a,b,c,d,e,f where.... group by a.cola having ....

Bet I missed the point!

Tink --- David Nance wrote: > OS/390 DB2/V6 > > I'm trying to write a statement that has a table > expression that I would like to have correlated to > another table expression, but it keeps resulting in > a -206 column doesn't exist in any table in the > query. The sql for ease of understanding goes > something [...] 23408 95 31_Re: Correlated table expression11_David Nance16_DWNance@FHSC.COM31_Tue, 21 Aug 2001 18:21:00 -0400353_US-ASCII The reason for the two table expressions is I need to compare some data against itself to get the final result set.

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

>>> tink@BLTYSOR.COM 08/21/01 06:09PM >>> David,

I am not sure we have enough information to do what you need but what is wrong with the following? [...] 23504 39 8_CPUSPEED17_Fernando Manching17_Kiwibisoy@AOL.COM29_Tue, 21 Aug 2001 18:38:22 EDT254_US-ASCII All,

How much does the cpuspeed parameter in the DBM config influence the performance of the database. Does it influence the access path the optimizer takes ? Is there someone out there that can explain this to me.



Fernando 23544 41 25_DB2 UDB 7.1 on OS/390 2.517_Fernando Manching17_Kiwibisoy@AOL.COM29_Tue, 21 Aug 2001 18:44:05 EDT357_US-ASCII All,

I am wondering if UDB 7.1 will run on OS/390 2.5 I know that the manual says it will only run on OS 390 2.7 at minimum, but would want to know if any installation has been able to run it on O/390 2.5. We are currently upgrading to UDB 7.1 but our OS is back level. If it would run on a back level OS, we could save a lot of money. [...] 23586 52 46_Re: No such thing as a free Platinum reference29_=?iso-8859-1?q?Half=20Eaten?=24_half_beaten@YAHOO.COM.AU31_Wed, 22 Aug 2001 08:44:28 +1000566_iso-8859-1 Mr Hardy, I'm sorry you weren't able to make it. I was so excited at the thought of meeting you 'in the flesh'.

Your in DB2land, Half Eaten

--- John Hardy wrote: > Thanks to all (but one!) of the replies to my > posting, including the many > offers of CA/Platinum (and other) references. I > trust that I will soon have > my fingers mataphorically stitched back on again, > and hereby officially > bring this short but intense thread to a close. > > To all: thanks again for your help and forbearance. > > John [...] 23639 121 31_Re: Correlated table expression13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Tue, 21 Aug 2001 16:36:08 -0700536_us-ascii For example? --- David Nance wrote: > The reason for the two table expressions is I need > to compare some data against itself to get the final > result set. > > Dave Nance > First Health Services, Corp. > (804)527-6841 > > >>> tink@BLTYSOR.COM 08/21/01 06:09PM >>> > David, > > I am not sure we have enough information to do what > you need but what is wrong with the following? > > select a.cola , sum(b.colb) > from a,b,c,d,e,f > where.... > group by a.cola > having .... > > Bet I missed the point! [...] 23761 17 63_Invoking a DB2 program from a REXX exec in the fore-ground mode8_Harish G24_hari_gangadhar@YAHOO.COM31_Tue, 21 Aug 2001 18:49:06 -0500404_- Hi,

How can I invoke a COBOL/ DB2 program from a REXX exec in the fore-ground mode ? Could someone post the needed syntax ?

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. 23779 65 31_Re: Correlated table expression11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Tue, 21 Aug 2001 18:27:55 -0600314_iso-8859-1 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) [...] 23845 40 35_Re: UDB on Windows restore question11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Wed, 22 Aug 2001 07:47:10 +0700478_- John,

Are the backup come from the same DB2 version? i.e. you backed up from DB2 7 then want to restore it on DB2 6, if yes you need different approach.

If not, in DB2 V.6 Control Center you need to fill in the name of the Backed Up Database Name, I don't know why because this wasn't need it it DB2 5, but in DB2 6 it's needed although there's no documentation about this, and in DB2 7 you have to fill in this or the 'OK' button won't be able to click... [...] 23886 130 36_Re: DSNREXX and FLOAT/DOUBLE columns10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 22 Aug 2001 11:11:19 +1000425_us-ascii Hi Smike,

I have 2 cols. in a table, one as FLOAT, and the other as DOUBLE.

I do a SELECT, and SAY, and it displays correctly.

"CONNECT" subsystem

SQLSTMT = , "SELECT COL1, COL2 FROM tablea"

EXECSQL "DECLARE C1 CURSOR FOR S1" EXECSQL "PREPARE S1 FROM :SQLSTMT" EXECSQL "OPEN C1"

do until ( sqlcode ¬= 0 ) EXECSQL "FETCH C1 INTO :HVcol1, :HVCol2 " say hvcol1 hvcol2 end [...] 24017 65 21_BETWEEN vs. >= and <=13_Olson, Carlos14_COlson@QRS.COM31_Tue, 21 Aug 2001 18:11:47 -0700760_iso-8859-1 I recently explained the following 2 SQL statements and was surprised to find that the optimizer chose different access paths:

SELECT PROGRAM_NAME , SUM ( CPU_TIME ) FROM CANDLE.H2ACCTPKG WHERE START_TIMESTAMP >= '2001-08-13-00.00.00.000000' AND START_TIMESTAMP <= '2001-08-18-00.00.00.000000' AND PROGRAM_NAME LIKE 'ECX%' GROUP BY PROGRAM_NAME ; SELECT PROGRAM_NAME , SUM ( CPU_TIME ) FROM CANDLE.H2ACCTPKG WHERE START_TIMESTAMP BETWEEN '2001-08-13-00.00.00.000000' AND '2001-08-18-00.00.00.000000' AND PROGRAM_NAME LIKE 'ECX%' GROUP BY PROGRAM_NAME ; I remembered reading in the 'DB2 Developer's Guide' (page 72 of 4th edition) that the optimizer now recognizes the BETWEEN predicate as equivalent to the combination of (<=) AND (>=). [...] 24083 177 35_Re: UDB on Windows restore question14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Wed, 22 Aug 2001 11:44:16 +1000340_iso-8859-1 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 the directory structure, but that may just be our site. [...] 24261 32 29_Larry (Oracle) strikes again!29_=?iso-8859-1?q?Half=20Eaten?=24_half_beaten@YAHOO.COM.AU31_Wed, 22 Aug 2001 12:17:46 +1000377_iso-8859-1 Did a search for 'DB2' on Google(www.google.com)... The first entry was:

DB2 - Oracle9i. Think Fast. Think Simple. Think Smart. www.Think9i.com Think e-business infrastructure. Sponsored Link

Regards, Half'



_____________________________________________________________________________ http://shopping.yahoo.com.au - Father's Day Shopping