1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 1999, week 2 2 41 25_Stored Procedures and ADO14_Peter Schwarcz17_peters@HDS.COM.AU30_Mon, 8 Nov 1999 05:41:53 -0800403_- I have been working on creating various stored procedures to run on OS/390, that will be called from a Visual Basic application using ADO.

Looking at the samples provided by IBM there is a sample RSSP.FRM with the following comment:

' ADO will not let us attempt to get the median until after the ' result set is closed .rs is closed after all records ' are retreived or on a close [...] 44 48 32_DB2 Connect & MS Access Problems30_CHRISTOPHER.KELLER@Vereinte.de30_CHRISTOPHER.KELLER@VEREINTE.DE30_Mon, 8 Nov 1999 13:47:00 +0100403_iso-8859-1 --- Received from BG1.KELLERC 6785-2761 08-11-99 13.47

-> db2-l@ryci.com

Hello, another Problem with DB2 Connect and MS Access about which I have found no mails or other information. I would be interested if other sites have had this problem.

I am using DB2 Connect Personal Edition 5.2 FP8, and MS Access 97 under NT 4.0 connecting to DB2 for OS/390 V5 via TCP/IP. [...] 93 123 34_Re: Raw Devices for DB/2 Log files18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM30_Mon, 8 Nov 1999 06:57:50 -0600578_iso-8859-1 Wayne, thanks for adding the point about UNIX and NT operating system file handling. Now, let me try to understand this point. If I placed the DB2 UDB log in a UNIX or NT operating system handled file, and should a COMMIT be issued, I've no guarantee that UNIX or NT will force that write of the log before telling the application/transaction that the COMMIT was successful? Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this [...] 217 116 65_Re: A Reference Guide for DB2 OS/390 by PLATINUM technology, inc.14_Huang, Eugenia16_HuangE@AETNA.COM30_Mon, 8 Nov 1999 09:00:00 -0500369_- Sincere thanks to both Rob and Janis for prompt and professional reply! I am looking forward to hear from you when it's available. Thanks again!

-----Original Message----- From: Janis Thomas [mailto:thomasj@PLATINUM.COM] Sent: Friday, November 05, 1999 5:41 PM To: DB2-L@RYCI.COM Subject: Re: A Reference Guide for DB2 OS/390 by PLATINUM technology, inc. [...] 334 45 25_Re: DB2 Service Task Wait20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 8 Nov 1999 09:24:17 -0500334_- Phase 2 commit is also recorded under DB2 SERVICE TASK WAIT. If you have read only job then this looks bit weird.

But I have seen a CICS task (read only) having huge DB2 SERVICE WAIT. I digged down more and found that TOKENE was NO , I guess that must have been the reason (still does not explain so high SERVICE WAIT). [...] 380 34 27_large table for OAM objects12_Kirk Hampton16_khampto1@TXU.COM30_Mon, 8 Nov 1999 08:25:12 -0600571_us-ascii Hi all, we are DB2 v5 on OS/390 v2.6. We are a long-time user of OAM, having had an ImagePlus installation in place since 1990. We also have OnDemand/390 running on a different LPAR (not data-sharing). We have always used both of these OAM object applications where the objects are only held in OAM's GROUPnn.* tables for a short time and are then written to an optical storage library by OAM's management cycle (similar to an HSM migrate under SMS). Now we have a prospective OnDemand user who wants their report data to remain in the DB2 tables for 30-45 [...] 415 15 5_VISIO17_Janice L. Manners19_MANNEJL@THIOKOL.COM30_Mon, 8 Nov 1999 07:44:05 -0700364_- Hello

I am evaluation Visio for Data Modeling. Is anyone using this product ? If so, how is it ?

thank you

================================================ 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. 431 127 65_Re: A Reference Guide for DB2 OS/390 by PLATINUM technology, inc.15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Mon, 8 Nov 1999 09:56:17 -0500335_iso-8859-1 Janis or Rob,

Rob mentioned the version 6 posters are out. I have recently ordered version 6 and will be installing it soon. How can I obtain a V6 catalog poster as I use my V4 alot and really appreciate it!

Thanks in advance, Susan Loria Nielsen Media Research loriasc@tvratings.com (727) 738-3000 x2754 [...] 559 16 34_OS/390 memory requirements for DB212_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Mon, 8 Nov 1999 10:03:15 -0500391_iso-8859-1 Does anyone know of guidelines or formula that can be used to determine the amount of memory a DB2 instance will require.

TIA, Andy

================================================ 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. 576 57 9_Re: VISIO26_Lindsay, William (SOM400A)24_WLindsay@EXCHANGE.ML.COM30_Mon, 8 Nov 1999 10:03:26 -0500458_iso-8859-1 Janice,

I like visio for its ease of diagramming. Data Models could be easily represented with it. It does not connect to a back end database without some programming (VBA) that I'm aware of. I use the TECHNICAL Version 5.0 for Windows.

One feature I especially like is the conversion to HTML. We take our process, and data flow drawings to the web. The drawings become images which you can HOTSPOT with client side image maps. [...] 634 132 73_FW: A Reference Guide for DB2 OS/390 by PLATINUM technology, inc. -RESEND15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Mon, 8 Nov 1999 10:30:43 -0500412_iso-8859-1 Resending as I received message was rejected message:





Janis or Rob,

Rob mentioned the version 6 posters are out. I have recently ordered version 6 and will be installing it soon. How can I obtain a V6 catalog poster as I use my V4 alot and really appreciate it!

Thanks in advance, Susan Loria Nielsen Media Research loriasc@tvratings.com (727) 738-3000 x2754 [...] 767 54 9_Re: VISIO17_Harold L. Trammel19_hlt@NAPCC.ASPCA.ORG30_Mon, 8 Nov 1999 09:36:21 -0600487_us-ascii I have used the Visio 5.0 Enterprise Edition for data modeling with some good results.

Good Points ________________

The connection lines are "smart" in that they show a hump when they cross another line making it easier to follow. They also avoid tables whenever possible.

I have been able to reverse engineer Informix and IBM DB2 databases satisfactorily. Generation of a DDL from the model is not very good for DB2. I have not tried it for Informix. [...] 822 21 36_Can utilities run across sub systems8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Mon, 8 Nov 1999 09:43:19 -0600357_us-ascii Hello List, I have a requirement if running DB2 utilities across DB2 subsystems/LPARS. I know that DML's can be used against DB2 tables across sub systems using 3 part names, if we set up the DDF. Is it possible to run utilities across DB2 sub systems?? Anybody done any research on this?? Any help in is highly appreciated. We are in DB2 V5. [...] 844 60 36_Re: DB2 Connect & MS Access Problems14_Metivier, Bill21_Bill.Metivier@FMR.COM30_Mon, 8 Nov 1999 10:45:51 -0500654_- Chris, I had talked to IBM support about this and they had me include "PATCH2=27" in the CLI parameter list ( DB2CLI.INI ). The TABLETYPE parameter worked fine after that. We are using DB2 Connect V6. I am not sure if this will work for V5.2.

Bill Metivier bill.metivier@fmr.com

> -----Original Message----- > From: CHRISTOPHER.KELLER@Vereinte.de [SMTP:CHRISTOPHER.KELLER@VEREINTE.DE] > Sent: Monday, November 08, 1999 7:47 AM > To: DB2-L@RYCI.COM > Subject: DB2 Connect & MS Access Problems > > --- Received from BG1.KELLERC 6785-2761 08-11-99 13.47 > > -> db2-l@ryci.com > > Hello, > another Problem with DB2 Connect and MS Access [...] 905 20 32_DB2 Connect & MS Access Problems14_Metivier, Bill21_Bill.Metivier@FMR.COM30_Mon, 8 Nov 1999 10:57:38 -0500314_- Chris, I had talked to IBM support about this and they had me include "PATCH2=27" in the CLI parameter list ( DB2CLI.INI ). The TABLETYPE parameter worked fine after that. We are using DB2 Connect V6. You might want to call support to see what is needed for V5.2.

Bill Metivier bill.metivier@fmr.com [...] 926 164 39_Re: External RACF security bind problem11_rick creech18_ykcirc@HOTMAIL.COM28_Mon, 8 Nov 1999 07:58:27 PST577_- Well..... The situation to which I am referring is as follows (perhaps it does not apply to what you are seeing): Using secondary auth i.d.s (based upon the RACF groups to which users are assigned); grants are made to the group and then the user automatically picks up any authorization for sql statements that were made to the group. For example, USER1 is not granted anything on table1, but the RACF group US is granted SELECT on table1. Therefore, when USER1 does a select on table1, he is authorized. USER1 has no bind privileges, but bind privileges are granted to [...] 1091 37 9_Re: VISIO11_Mayo Arthur22_Arthur.Mayo@M1.IRS.GOV30_Mon, 8 Nov 1999 11:00:40 -0500403_- Janice, I think you'll find this product is good for documentation only. If you want to document an ERD in Bachman, Crow's Foot, IDEF1X or Object Role, VISIO will provide you the necessary stencils to create this document. There is also a Database Wizard which will generate an ERD database, but only if the database was created in Microsoft Access 7.0 or some other program compliant with ODBC. [...] 1129 18 40_Re: Can utilities run across sub systems12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Nov 1999 09:58:58 -0800370_- Utilities run on a specific subsystem. In a data sharing group, the utility runs on one member of the group, but the result of the run is accessible across the group.

If you submit a requirement, please explain the problem thoroughly. The solution can help a little, but not nearly as much as the problem, what you've tried, and the trouble it causes you. [...] 1148 55 38_Re: OS/390 memory requirements for DB210_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Mon, 8 Nov 1999 07:50:53 -0800519_us-ascii In the DB2 Installation Guide, version 5 beginning on page 2-36 is a discussion (with formulas) of the virtual storage requirements of a DB2 subsystem (instance is not an OS/390 term).

Briefly, the Database Services Address Space (xxxxDBM1) is the largest user of virtual storage. Plan on 14M as a minimum with 1,334K below the 16M line. You will use additional memory depending on how big you make the bufferpools, sort pool, RID pool, and EDM pool. Additional memory is required for each thread. [...] 1204 15 38_Re: OS/390 memory requirements for DB212_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Nov 1999 10:05:39 -0800509_- There is a set of planning formulas in the Installation Guide. For Version 5, chapter 2-2, in the hard copy the pages you want are 2-36 to 2-50. The real formulas would be about the size of the full book, so the practice is generally to measure.

Roger Miller

================================================ 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. 1220 45 40_Re: Can utilities run across sub systems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 8 Nov 1999 11:11:17 -0500626_- Utilities can run using stored procedure address space. You could call them from different subsystem using DDF.

IBM has a stored procedure "DSNUTITLS" , which you could use it.

Does this helps ?

Regards Pillay

> -----Original Message----- > From: Kurian B [SMTP:Bejoy_Kurian@MAY-CO.COM] > Sent: Monday, November 08, 1999 10:43 AM > To: DB2-L@RYCI.COM > Subject: Can utilities run across sub systems > > Hello List, > I have a requirement if running DB2 utilities across DB2 subsystems/LPARS. > I > know that DML's can be used against DB2 tables across sub systems using 3 > part > names, if [...] 1266 30 38_Re: OS/390 memory requirements for DB220_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 8 Nov 1999 11:13:25 -0500648_- Is it for OS/390 ? You mentioned "DB2 instance" ?

> -----Original Message----- > From: Andy Wheeler [SMTP:Andy.Wheeler@GRIZZARD.COM] > Sent: Monday, November 08, 1999 10:03 AM > To: DB2-L@RYCI.COM > Subject: OS/390 memory requirements for DB2 > > Does anyone know of guidelines or formula that can be used to determine > the > amount of memory a DB2 instance will require. > > TIA, > Andy > > ================================================ > 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. [...] 1297 20 5_VISIO11_Mayo Arthur22_Arthur.Mayo@M1.IRS.GOV30_Mon, 8 Nov 1999 11:22:59 -0500454_- > Janice, I think you'll find this product is good for documentation only. > If you want to document an ERD in Bachman, Crow's Foot, IDEF1X or Object > Role, VISIO will provide you the necessary stencils to create the > document. There is also a Database Wizard which will generate an ERD > from an existing database, but only if the database was created in > Microsoft Access 7.0 or some other program compliant with ODBC. > > Hope this helps. > [...] 1318 48 40_Re: Can utilities run across sub systems8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Mon, 8 Nov 1999 10:28:59 -0600443_us-ascii Thanks Roger.

Actually i am looking for a solution to run a DB2 utility from LPARA, subsystem (DSNA), against a table space/table if LOAD in another subsystem (DSNB) in LPARB in os390 and DB2 V5 environment. Hope this helps to understand. Thanks Bejoy







Roger Miller on 11/08/99 11:58:58 AM



Please respond to DB2 Data Base Discussion List [...] 1367 19 31_Re: large table for OAM objects12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Nov 1999 10:29:05 -0800509_- While segmented table spaces can be much larger than 4 GB, very large tables don't perform as well and can't be reorganized. In short they are missing the benefits of partitioning that are often needed for large table spaces.

There is a section in the OAM Planning, Administration and Storage Administrationg Guide for Object Support (OAM PISA) that discusses segmented and partitioned tables spaces used for OAM, including recommendations for the different object directory and storage tables. [...] 1387 56 31_Re: large table for OAM objects11_Stefan Geus25_stefan.geus@HUK-COBURG.DE30_Mon, 8 Nov 1999 18:15:39 +0100545_us-ascii Kirk Hampton wrote: > > Hi all, > we are DB2 v5 on OS/390 v2.6. We are a long-time user of OAM, > having had an ImagePlus installation in place since 1990. We also have > OnDemand/390 running on a different LPAR (not data-sharing). We have > always used both of these OAM object applications where the objects are > only held in OAM's GROUPnn.* tables for a short time and are then written > to an optical storage library by OAM's management cycle (similar to an HSM > migrate under SMS). > Now we have a prospective OnDemand user [...] 1444 35 40_Re: Can utilities run across sub systems16_salhany, michael23_salhany_michael@EMC.COM30_Mon, 8 Nov 1999 12:05:24 -0500555_iso-8859-1 -----Original Message----- From: Kurian B [mailto:Bejoy_Kurian@MAY-CO.COM] Sent: Monday, November 08, 1999 10:43 AM To: DB2-L@RYCI.COM Subject: Can utilities run across sub systems



Hello List, I have a requirement if running DB2 utilities across DB2 subsystems/LPARS. I know that DML's can be used against DB2 tables across sub systems using 3 part names, if we set up the DDF. Is it possible to run utilities across DB2 sub systems?? Anybody done any research on this?? Any help in is highly appreciated. We are in DB2 V5. [...] 1480 106 29_Re: using Stogroups and RAMAC11_rick creech18_ykcirc@HOTMAIL.COM28_Mon, 8 Nov 1999 09:19:13 PST646_- We have all RVA devices, and let SMS place everything. The MVS DASD people tell me that there are no hot spots in our buisiest DB2 applications and the average I/O time is 6 ms.



>From: a.jayatilaka@HIC.GOV.AU >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: using Stogroups and RAMAC >Date: Mon, 8 Nov 1999 09:05:25 +1100 > >Michelle, > >We, at the Health Insurance Commission, have not experienced any problem >with SMS and DB2. Our tablespaces and indexspaces share volumes without >hinderance to performance. We also have a mixture of physical 3390s, RAMAC >and RVA volumes. We [...] 1587 25 21_Can't connect with cc15_Steven Gertiser20_steveg@IDPC-INTL.COM30_Mon, 8 Nov 1999 10:57:55 -0700353_us-ascii Hi folks,

Was wondering if anyone had a tip for the following error which occurs when I try to view from with the Control Center on Windows the tables in a database residing in a DB2 (Linux) instance:

SQL0901N The SQL statemetn failed because of non-severe system error... (Reason "Invalid Request type for DB2 Admin Server) [...] 1613 37 35_Securing Access through DB2 Connect14_Philip Trbovic19_TRBOVICP@BCBSIL.COM30_Mon, 8 Nov 1999 12:10:29 -0600330_US-ASCII I have run into a small problem in relation to security using DB2 Connect. The setup in our shop is thus:

NT 4.0 clients with DB2 Connect EE 5.2 (fixpack 8) connecting to an AIX server 4.3 with the same release of DB2 Connect which connects to a LU on the mainframe which we have pointing to a DB2 subsystem. [...] 1651 42 9_Re: VISIO14_Susan Birgeles25_Susan.Birgeles@ALLTEL.COM30_Mon, 8 Nov 1999 13:12:00 -0600409_us-ascii anice, I think you'll find this product is good for documentation only. If you want to document an ERD in Bachman, Crow's Foot, IDEF1X or Object Role, VISIO will provide you the necessary stencils to create this document. There is also a Database Wizard which will generate an ERD database, but only if the database was created in Microsoft Access 7.0 or some other program compliant with ODBC. [...] 1694 71 39_Re: Securing Access through DB2 Connect16_Michael McCarthy24_Michael_McCarthy@DTC.ORG30_Mon, 8 Nov 1999 13:54:05 -0500621_us-ascii Phil,

Make sure the database manager configuration parm AUTHENTICATION is set to SERVER on the DB2 Connect gateway. Also check SYSIBM.USERNAMES (SYSUSERNAMES for V4 and below) on OS/390 to be sure the userid is not getting translated to an authorized id. You have said that the client is NT with DB2 Connect going to an AIX DB2 Connect gateway. The NT client doesn't need, and shouldn't have DB2 Connect installed. Just the CAE (runtime client in V6). If the NT client is running DB2 Connect itself, make sure the local directories are cataloged appropriately (Node, Database, and DCS). I'm assuming [...] 1766 32 29_Re: using Stogroups and RAMAC14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Mon, 8 Nov 1999 14:43:46 -0500568_ISO-8859-1 Message text written by DB2 Data Base Discussion List >We have all RVA devices, and let SMS place everything. The MVS DASD people tell me that there are no hot spots in our buisiest DB2 applications and the average I/O time is 6 ms.<

Now the way the dasd folks usually report the avg is across every deivice the entire dasd pool. If you are truly getting this type of performance from your DB2 packs, and specifically during your busy periods I'd love to see the performance data to verify this - because you may be the first..... Regards, Joel [...] 1799 30 50_SQL Question : Finding Missing Entries in Sequence13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Mon, 8 Nov 1999 21:02:14 +0000283_us-ascii I've got a problem I'd like to solve using an SQL statement.

A column in a table holds an integer value which is incremented between entries. Sometimes an entry is missing, and what I'd like to do for a given range of values is get a list of the missing values. [...] 1830 51 39_Recovering from out-of-space conditions15_David A. Zelmer25_david.zelmer@CITICORP.COM30_Mon, 8 Nov 1999 14:47:00 -0600586_us-ascii Hello all,

In working with Db2 utilities on OS/390, DB2 5.1, I have been testing recovery scenarios. My test is with the work data sets used by the utilities and out-of-space conditions. The Utility Guide and Reference on page 2-30 tells me to "1. Copy the output data set to a temporary data set, using the same DCB parameters. Use MVS utilities that do not reblock the data set during the copy operation (i.e. DFDSS ASRDSSU, DFSORT ICEGENER)." Step 2 is to delete/redefine the original dataset, giving it more space. Step 3 says to copy the temporary to the new, [...] 1882 79 54_Re: SQL Question : Finding Missing Entries in Sequence10_Mann, Tony19_TMann@UTILICORP.COM30_Mon, 8 Nov 1999 14:53:40 -0600326_- Rather than worry about a list of values, why not just report the rows where there is NOT a row with the next higher number?

ie

select number from numbertable t1 where number between low_end and high_end and not exists (select * from numbertable t2 where t2.number = t1.number + 1)

or, equivalent: [...] 1962 121 54_Re: SQL Question : Finding Missing Entries in Sequence14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Mon, 8 Nov 1999 16:19:36 -0500414_iso-8859-1 You can create a dataset with ALL possible integer values within a range and LOAD it into a table. You can use a spreadsheet software or REXX or some other scripting utility to generate the numbers.

Then you can run a query as:

select c.number1 from ( select a.number1, b.number2 from temptable a left outer join numbertable b on a.number1 = b.number2) as c where c.number2 is null ; [...] 2084 140 54_Re: SQL Question : Finding Missing Entries in Sequence0_24_DB46@DAIMLERCHRYSLER.COM30_Mon, 8 Nov 1999 16:48:01 -0500463_us-ascii Good idea Tony, but what if there are two missing entries in a row?

Doing what you want to do Philip requires either another table or temp table with all the values of those integers.

Select AV.integer_col from all_values AV where AV.integer_col between :low_value and :high_value and AV.integer_col not in ( select MV.integer_col from missing_values MV where MV.integer_col between :low_value and :high_value) order by AV.integer_col [...] 2225 15 16_Re: Catalog copy12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Nov 1999 15:52:41 -0800549_- Catalog statistics are discussed in the Administration Guide, Chapter 5-9 in V5. It includes a section about querying the catalog for statistics on page 5-247 and one on modelling your production system on page 5-254. Note the additional caveats of processor models & bufferpool sizes.

Roger Miller

================================================ 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. 2241 19 48_How we can do requests for improvements in DB2 ?22_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM28_Mon, 8 Nov 1999 14:15:06 PST447_- The official mechanism is to have an IBM representative enter your requirement on the REQUESTS system. You can do this through an account rep or over the phone with IBM service. These requests get addressed by development and receive a formal response.

On an informal basis, several members of the DB2 development community monitor this list and others, and take note of recurring requests, such as the REORG CHANGELEVEL copy issue. [...] 2261 12 48_How we can do requests for improvements in DB2 ?22_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM28_Mon, 8 Nov 1999 14:26:17 PST291_- Sorry for the typo...REORG SHRLEVEL!

Dan

================================================ 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. 2274 82 54_Re: SQL Question : Finding Missing Entries in Sequence11_Mark Ediger15_mediger@CME.COM30_Mon, 8 Nov 1999 16:29:10 -0600542_us-ascii Philip,

Here is my try:



-- FIND SINGLE MISSING VALUE SELECT T1.COLB FROM (SELECT COLB + 1 AS COLB FROM TABLE1) AS T1 FULL OUTER JOIN (SELECT COLB FROM TABLE1) AS T2 ON T1.COLB = T2.COLB WHERE T2.COLB IS NULL AND T1.COLB BETWEEN :low-value and :high-value UNION

-- FIND 2D CONTINUOUS MISSING VALUE SELECT T1.COLB FROM (SELECT COLB + 2 AS COLB FROM TABLE1) AS T1 FULL OUTER JOIN (SELECT COLB FROM TABLE1) AS T2 ON T1.COLB = T2.COLB WHERE T2.COLB IS NULL AND T1.COLB BETWEEN :low-value and :high-value [...] 2357 75 40_Re: Can utilities run across sub systems8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Mon, 8 Nov 1999 14:28:43 -0600354_us-ascii Hi Venkat, Can you please explain in detail how this stored procedure "DSNUTILS" works with multiple subsystems.

Actually i am looking for a solution to run DB2 utilities from LPARA, subsystem (DSNA), against a table space/table if LOAD in another subsystem (DSNB) in LPARB in os390 and DB2 V5 or may be V6 environment. Thanks Bejoy [...] 2433 98 40_Re: Can utilities run across sub systems0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 8 Nov 1999 17:24:50 -0600465_us-ascii Venkat, how about a work around. Use your job scheduling tools to achieve that. As a matter of fact, our OS390 scheduler (ESP) can't even be FORCED to recognize the difference between LPARS (well, at least according to the people here who are supposed ;-} to know). It is a real pain in the ... when a developer forgets to manually change the JCL JOB name and ends up accidentally triggering production jobs that he actually has no rights to execute. [...] 2532 96 54_Re: SQL Question : Finding Missing Entries in Sequence14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Tue, 9 Nov 1999 12:04:32 +1000491_us-ascii Philip, Not being one of the SQL gurus on the list I thought I'd have a fiddle with this one and think I might have found a solution for you. While the solution doesn't provide all the missing numbers, it provides the "bottom" end of the gap and the size of the gap: eg. for 1, 2,3,6,7,9,10 it will return one row for every gap with two columns 4,2 -- meaning there is a gap starting at 4 for a gap size of 2 (ie 4,5) 8,1 -- meaning there is a gap starting at 8 for a gap size [...] 2629 14 16_Bug in DSNUTILB?22_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM28_Mon, 8 Nov 1999 13:59:55 PST416_- Yes, this is a cosmetic bug, introduced by PQ19897 in version 5 when a similar message (U253) was created for TABLEs. Thanks for pointing it out.



Dan Courter

================================================ 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. 2644 69 36_Antwort: large table for OAM objects0_20_antoon.rodoe@DKV.COM30_Tue, 9 Nov 1999 09:45:47 +0100352_us-ascii Kirk,

as Stefan Geus said before, the limit for tablespaces is 64 gigabytes, either partioned or not

We had a non-partitioned tablespace wit 62 GB. For a year we changed our structure and today we have 31 databases with the standard OAM-Tablespaces (each about 10-16 GB). In total we keep about 400 GB in these tablespaces. [...] 2714 100 14_Applet-Problem11_Alfred Moos21_moos@FH-HEIDELBERG.DE30_Tue, 9 Nov 1999 10:06:09 +0100504_iso-8859-1 Hello all,

has someone a good advice to resolve my problem: My Java-applet can't access to DB2 on my server, the corresponding Java-application is successful.

On my NT4-server (ATLAS) runs DB2, Microsoft Internet Server and the JDBC applet server.

For both my application and my applet I use the driver Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");

The application runs without any problem. The following applet terminates with a security access exception. [...] 2815 30 0_13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR30_Tue, 9 Nov 1999 11:42:50 +0200339_windows-1254 Hello, I am trying to change a password using DB2 Connect with OS/390 DB2 5.1. I recieve a message "SQL30083N Attempt to change password for user id "db2admin" failed with security reason "23" ("CHGPWD_SDN IN DCS ENTRY NOT CONFIGURED"). SQLSTATE=08001"

What do I, have to do on OS/390 to enable password changing. [...] 2846 88 3_Re:16_Michael McCarthy24_Michael_McCarthy@DTC.ORG30_Tue, 9 Nov 1999 09:09:38 -0500552_us-ascii Hello Murat,

The CHGPWD_SDN is a parm in the DCS Database that you set when cataloging the DCS entry. It must be set to the symbolic destination name for the host password expiration management program. Your MVS security group should be able to help with this. You will also need to set up an additional LU for this parm. Your VTAM group should be able to help with this. On the SNA side, you will need to define the LU and a service TP name of x'06F3F0F1'. A fully detailed description of what needs to be done to accomplish this [...] 2935 148 40_Re: Can utilities run across sub systems20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 9 Nov 1999 09:56:48 -0500372_iso-8859-1 Rohn

I agree with you, this is a better solution (if only LPARs are the issue). Even "/*ROUTE XEQ " on the JCL will be easy way to run utilities on different LPAR.

Bejoy

However utilities can now run from work-stations, DDF etc. using stored procedure. There was a recent "Tech conference" presentation by Cathy Drummond (session O6). [...] 3084 20 28_DB2 Service Wait (V5 OS/390)14_Martin Gingras30_Martin.Gingras@CCRA-ADRC.GC.CA30_Tue, 9 Nov 1999 09:05:05 -0800446_- We are currently recovering a number of databases on a weekly basis (8 databases). We submit 3-5 job concurrently and they usually all start fairly well.

After a certain amount of time, they will start going into a "Waiting For DB2 Services" status. The jobs will go in and off this status but they will be waiting most of the time. This results into a recovery time of 4-5 hours for a database that can be backed up in 20 minutes. [...] 3105 69 19_GETHOSTBYADDR error0_14_mtdage@ATT.NET30_Tue, 9 Nov 1999 15:28:34 +0000401_- Hi,

We have a requirement to build DB2 on our WEBS LPAR that will be accessing data from our ASYS LPAR using DRDA. I have the DB2 subsystem DSSJ built on the WEBS except when I tried bringing it up I get the following error.

15.52.20 STC00328 DSNL003I ! DDF IS STARTING 15.52.21 STC00328 DSNL512I ! DSNLILNR TCP/IP GETHOSTBYADDR FAILED WITH RETURN CODE=1 AND REASON CODE=00000000 [...] 3175 55 18_Twin Table Concept11_Ken Liberty26_KLiberty@CONSULTEC-INC.COM30_Tue, 9 Nov 1999 10:38:24 -0500566_US-ASCII I would to take a consensus of how many of you have encountered a need to design Twin Tables in which the only difference is the Primary Key/Clustering IX. I have a situation in which one of our most active tables, ie Inserts, Updates, Selects, which is accessed via CICS and Batch seems to be I/O bound. This only happens when certain large resource intensive jobs are executed in the Batch Cycle. These Batch jobs need to access this table multiple times throughout it's processing both by the Primary IX and Secondary IX using a MAX on the 3rd Key. [...] 3231 82 0_0_15_leon@CA.IBM.COM30_Tue, 9 Nov 1999 11:14:25 -0500323_us-ascii



What the error is saying is that you are using SNA to connect to DB2 for OS/390. To use "Change password" function over SNA you do need to have Password Expiration Management (PEM) set up on the host. Instructions for doing this are in the DB2 Connect documentation. Just do a search on line. [...] 3314 20 23_Re: GETHOSTBYADDR error11_Kenneth Lam14_klam@BTMNA.COM30_Tue, 9 Nov 1999 11:35:54 -0500359_us-ascii I got this message before when I was setting up DDF. DB2 generates these names by issuing GETHOSTBYADDR() to get the host IP address. I suggest you should look into IBM RED BOOK "DB2 For DB2 UDB DRDA TCP/IP Support" . This book has lots of information for you to troubleshoot the problem. You should check with your CDB tables and VTAM defined. [...] 3335 105 22_Re: Twin Table Concept0_19_Tim.Lowe@STPAUL.COM30_Tue, 9 Nov 1999 10:36:47 -0600366_us-ascii Ken, I have never needed to create a "twin table" for this purpose. Instead of creating a "twin table", could you create a parent table of this one which would have a primary key of Field A and Field B, with another column which represented the "max value" of Field C? This table could have 2 indexes on it, with Field A and Field B in both sequences. [...] 3441 15 11_Archive URL14_Rebecca A Bond22_rebecca_a_bond@UHC.COM30_Tue, 9 Nov 1999 10:43:48 -0500372_us-ascii My PC crashed and all my browser bookmarks are gone. Can someone post the URL for the archives again (please).

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. 3457 40 23_Re: GETHOSTBYADDR error0_14_mtdage@ATT.NET30_Tue, 9 Nov 1999 17:16:49 +0000418_- Hi Kenneth,

Thanks for your reply. I got the problem resolved. Here's what happened.

DSSJDIST was accessing a file called TCPIP.HOSTS.SITEINFO in a volume that is inaccessible on the WEBS LPAR. I uncatalog this dataset and cataloged the correct one.

Restarted DB2 and the error message was gone.

I found the redbook and I think I will spend my time reading this for DRDA connection. [...] 3498 34 16_Authid Switching9_Sally Mir16_smir@BCBS-GA.COM30_Tue, 9 Nov 1999 12:17:53 -0500379_us-ascii It's me again. Hope you all don't get tired of my asking for help all the time. I promise I'll go back to lurking soon...

I have searched the archives and haven't found the answer, so here goes:

Using DB2 Connect and MS Access, I am trying to read data that has SELECT authority granted to a RACF group id. The ID I'm using does belong to that group. [...] 3533 15 24_-811 on SELECT statement15_Stovall, Martin16_MStovall@USG.COM30_Tue, 9 Nov 1999 11:14:31 -0600496_- Does DB2 materalize the whole result table when a non cursor SELECT statement (when no sorting involved) retrieves more than one row in returning a -811 return code? Also, should programmers depend on the data returned when a -811 return code is given?

================================================ 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. 3549 52 0_14_Nancy Westover22_nwestover@NETSCAPE.NET28_Tue, 9 Nov 1999 12:50:02 EST574_US-ASCII Hi!

I'm trying to setup my desktop NT Workstation to administer our DB2 UDB servers. We have both AIX and NT servers. Using Client Configuration Assistent I am able to set up the connections to both the aix servers and the NT servers. This is where the problem comes in. When I connect to the databases residing on AIX I am able to view all information, i.e. Tables, Views, Indexes, etc. When I connect to the databases on NT, I am only able to see information on the tables and replication sources and replication subscriptions. It doesn't allow me to [...] 3602 35 28_Re: -811 on SELECT statement0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 9 Nov 1999 11:53:28 -0600536_us-ascii I don't know about the materialization, but I would guess yes.

What do you mean by "depend on"? Yes, the data returned is valid. BUT, there is no guarantee that you will always get the same row, ie after a table or index re-org).

We have some code that allows -811 (because data they want is same on every row returned by the WHERE clause), but we've realized it's a bad idea. In production we now get lots of -811's logged but we can't easily identify when -811 is "allowed" or a true data or coding error. [...] 3638 59 0_16_salhany, michael23_salhany_michael@EMC.COM30_Tue, 9 Nov 1999 13:00:28 -0500445_iso-8859-1 Hi Leon:

I am also trying to use SNA because I need it to support SPM (synch. point manager) when updating db2 on UNIX from an IMS BMP via DRDA. SPM is not supported with TCP/IP.

Perhaps the originator of this question has the same issue.

I am using DB2 Connect Enterprise Edition for UDB V5.2 Anything you can suggest to make this config. process easier or any "gotchas" you can point out would be helpful. [...] 3698 86 0_16_salhany, michael23_salhany_michael@EMC.COM30_Tue, 9 Nov 1999 13:10:07 -0500383_iso-8859-1 Hi:

Try reading SG24-2212-00 Wow! DRDA Supports TCP/IP: DB2 Server for OS/390 & UDB

To confirm that you have everything set up corretcly go to CLP and connect to your NT database: connect to nt_db user xxxx using yyyy now issue: LIST DCS DIRECTORY list DATABASE DIRECTORY LIST NODE DIRECTORY

and confirm value settings of all fields are correct. [...] 3785 52 32_Re: DB2 Service Wait (V5 OS/390)16_salhany, michael23_salhany_michael@EMC.COM30_Tue, 9 Nov 1999 13:18:49 -0500376_iso-8859-1 The first thing that comes to mind for starters is to check the Zparms value for IDBACK. It should be large enough to handle all the concurrent threads and then some.

Also check for locks: -DIS DB(dbname) spacenam(tsname) LOCKS while the jobs area running. You may also have to run a lock trace and look for claims & drains on the tablespaces involved. [...] 3838 73 28_Re: -811 on SELECT statement16_Michael McCarthy24_Michael_McCarthy@DTC.ORG30_Tue, 9 Nov 1999 13:43:37 -0500381_us-ascii Martin,

Rohn is correct for the OS/390 platform. In the UDB arena, IBM explicitly states that the data is not returned upon receiving a -811 SQLCODE. If you are just checking for existence, I agree with Rohn on the COUNT(*). However, if the first row is needed, I would declare a cursor and fetch the first row. You can also use the optimize for 1 row clause. [...] 3912 60 32_Re: DB2 Service Wait (V5 OS/390)10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Tue, 9 Nov 1999 10:53:51 -0800244_us-ascii You didn't mention what kind of recovery you're doing. If your recovery involves DB2 having to read the log for all the tablespaces you are recovering simultaneously, this could be the bottleneck you're waiting for.

Bill

3973 72 28_Re: -811 on SELECT statement13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 9 Nov 1999 13:04:55 -0600588_iso-8859-1 I have been down this path too. 1. The manual says the data returned from a -811 is unpredictable (OS390. That was enough to avoid using data returned from a -811 SELECT. See SQL Reference SC26-8966-00 p444 SELECT statement 2. Executing a count(*) to check existence can be very inefficient because DB2 has to perform the count. If it returns a count of 10,000 then it has done considerable work to do that. 3. For existence checking trap the -811 or use a cursor. The topic of efficient methods for existence checking has been canvassed previously. The archives may have [...] 4046 92 32_Re: DB2 Service Wait (V5 OS/390)15_Gingras, Martin30_Martin.Gingras@CCRA-ADRC.GC.CA30_Tue, 9 Nov 1999 14:13:44 -0500365_- Sorry for not mentionning it, it is a recovery to RBA that does not use the log at all (We verified). The RBA is taken from a quiesce after a backup and there was no activity on any of the tablespace (They were all in read only).

This problem has also been seen in the recover index portion of the recovery which does not need the logs or SYSLOGRNGX. [...] 4139 80 41_Re: stored procedures vs. DDF (on OS/390)30_Humphris,Richard,CNA Insurance24_Richard.Humphris@CNA.COM30_Tue, 9 Nov 1999 13:00:07 -0600453_- Hi John,

Just a wild guess: could you have bound the static sql differently (say using repeatable read, for example)? The only thing I'd think multiple commit's would relate to is locking of some sort. Also, how many locks can you take before you do table lock escalation?

In addition, if you're release 5 or better and you already have dynamic sql caching enabled; maybe the difference between dynamic and static isn't that large? [...] 4220 118 32_Re: DB2 Service Wait (V5 OS/390)12_Myron Miller24_myron_miller@HOTMAIL.COM28_Tue, 9 Nov 1999 11:33:18 PST349_- I'm not sure if its related, but are you recovering a lot of Tablespaces or Indexes. I've seen instances at some shops where there were a lot of tablespaces or indexes being recovered and it took a lot of time in MVS allocation for all of these objects to be allocated. In some shops the allocation time can be considerable, even with REUSE. [...] 4339 30 15_Re: Archive URL13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 9 Nov 1999 13:34:09 -0600605_iso-8859-1 http://www.deja.com/home_ps.shtml



-----Original Message----- From: Rebecca A Bond [mailto:rebecca_a_bond@UHC.COM] Sent: Tuesday, November 09, 1999 9:44 AM To: DB2-L@RYCI.COM Subject: Archive URL



My PC crashed and all my browser bookmarks are gone. Can someone post the URL for the archives again (please).

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. [...] 4370 85 0_0_15_leon@CA.IBM.COM30_Tue, 9 Nov 1999 14:26:17 -0500383_us-ascii Michael, just because your host applications need to connect to DB2 on UNIX does not mean you want to use SNA to connect from UNIX to DB2 on the mainframe. These are completely separate functions and code paths. I would encourage you and everybody else to use TCP/IP for the up-link to the host from UNIX and NT no matter what Host--to-UNIX down-link requirements are. [...] 4456 69 28_Re: -811 on SELECT statement0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 9 Nov 1999 13:56:15 -0600423_us-ascii Glenn,

point 3 - I think you missed part of the point I was trying to make. Even if you trap the -811 in your application (which we did) the SQLCODE code is still logged by DB2 in the MSTR log as an 'Error'. So occurrences of 'allowable' -811 will tend to swamp the -811's that are truly code or data errors that we would like to investigate and correct. That is why we now avoid 'intentional' -811's. [...] 4526 155 46_DB2 Service Wait (V5 OS/390) strikes again !!!14_Max 'Guderian'16_mocion@LIBERO.IT30_Tue, 9 Nov 1999 20:54:48 +0100356_us-ascii Hi all collegues.

I experimented a similar behaviour this week and it was for all utilities running (runstats, image copy,

reorg...). All start with a big increasing in page-ins, mainly due to some batch jobs updating some

tables and then , when utilities start, they remain in a waiting state. If you execute a -DISPLAY [...] 4682 92 28_Re: -811 on SELECT statement13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 9 Nov 1999 14:10:03 -0600442_iso-8859-1 Rohn, What is the MSTR log you are referring to, is it xxxxMSTR addressspace?. What DD statement is it? I have never seen any DB2 errors reported here, I was assuming the program could get some control over when to log or not. Glenn

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Tuesday, November 09, 1999 1:56 PM To: DB2-L@RYCI.COM Subject: Re: -811 on SELECT statement [...] 4775 29 15_Re: Archive URL22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM30_Tue, 9 Nov 1999 15:20:21 -0500608_- The db2-l archive url is listed on http://www.ryci.com/db2-l

> ---------- > From: Rebecca A Bond[SMTP:rebecca_a_bond@UHC.COM] > Sent: Tuesday, November 09, 1999 10:43 AM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Archive URL > > My PC crashed and all my browser bookmarks are gone. Can someone post the > URL for the archives again (please). > > 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. > [...] 4805 104 0_16_salhany, michael23_salhany_michael@EMC.COM30_Tue, 9 Nov 1999 15:22:54 -0500397_iso-8859-1 Hi Leon: I am trying to get DB2 on MVS to update data on UNIX using two-phase-commit. This means I need SPM. Someone at IBM told me that SPM used in this fashion is not supported with TCP/IP. They said I must use SNA.

Mike.

-----Original Message----- From: leon@CA.IBM.COM [mailto:leon@CA.IBM.COM] Sent: Tuesday, November 09, 1999 2:26 PM To: DB2-L@RYCI.COM Subject: [...] 4910 42 16_Year 2000 'hype'0_23_Mike_Levine@TEKHELP.NET30_Tue, 9 Nov 1999 15:35:45 -0500599_us-ascii Hi, I thought this was an interesting, albeit slightly off-topic item. I did not personally read the article mentioned. I'm sending this because several months ago I wondered 'aloud' why there were no Y2K disaster movies. (So please don't flame me !)

It's a fact that most large enterprise-wide software projects wind up either late, canceled or worse. Given this fact, do we have a sense of how many of these large Y2K projects are really on schedule? In many cases this is the largest software maintenance project ever undertaken by the IT org. We are reading about how large [...] 4953 141 32_Re: DB2 Service Wait (V5 OS/390)0_23_Mike_Levine@TEKHELP.NET30_Tue, 9 Nov 1999 15:50:06 -0500422_us-ascii Hi,

We used to use an OEM VSAM SMS like product that would read the VTOC of every volume in the pool looking for a 'best fit'. This would take one or two minutes even for empty tablespaces. We changed it to 'first fit' and processing time improved significantly (we don't use the product anymore). Also, I don't believe that the tablespace close option would added significantly to the recovery time. [...] 5095 132 32_Re: DB2 Service Wait (V5 OS/390)10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Tue, 9 Nov 1999 12:53:54 -0800308_us-ascii The next place I would look is at RMF reports for the time these RECOVER jobs were running to check for DASD contention, tape drive contention if any tapes are used, CPU shortage, and memory shortage (paging). I would also check the DB2 monitor reports for page-ins for I/O in the bufferpools. [...] 5228 155 0_16_salhany, michael23_salhany_michael@EMC.COM30_Tue, 9 Nov 1999 16:01:08 -0500298_iso-8859-1 Leon:

Here is the response I got from IBM support. Can you tell me if, in my circumstances, I am getting the correct response? Can I continue using TCP/IP going out of unix and use SNA only going into it from an MVS (IMS) transaction which they say needs SNA to support SPM? [...] 5384 54 46_DB2 Service Wait (V5 OS/390) strikes again !!!14_Max 'Guderian'16_mocion@LIBERO.IT30_Tue, 9 Nov 1999 22:00:43 +0100358_iso-8859-1 Hi all collegues.

I experimented a similar behaviour this week and it was for all utilities running (runstats, image copy,

reorg...). All start with a big increasing in page-ins, mainly due to some batch jobs updating some

tables and then , when utilities start, they remain in a waiting state. If you execute a -DISPLAY [...] 5439 23 15_monotoring tool50_Karina Chong-Sing - Cesercomp(Jefe Dpto. Sistemas)26_kchong@GOLIAT.ESPOL.EDU.EC30_Tue, 9 Nov 1999 17:00:14 +0500342_US-ASCII Hi All !!!

I'm going to install a new database in an AIX 4.3.3 systems. But before doing that, I would like to measure some parameters (memory, paging, etc.) in the AIX system using a monotoring tool. I tried to use monitor (a freeware) but it did not work in 4.3.3. Do you know any other tool? Where can I find it/them? [...] 5463 23 17_DB2 UDB on NT BDC13_Dustin Reiner29_Dustin.Reiner@INGRAMMICRO.COM30_Tue, 9 Nov 1999 17:12:20 -0500392_iso-8859-1 I am installing DB2 UDB v 6.1 on a Windows NT Backup Domain Controller. When I get to the portion of setup which asks for the account that you want the DB2 to use, it will not accept any existing accounts, (and says they do not exist), if you tell it to create one, it errors out saying that it could not create it. Has anyone else seen this, or have any suggestions? Thanks. [...] 5487 23 4_ODBC10_DB2 Review21_db2review@HOTMAIL.COM28_Tue, 9 Nov 1999 17:14:40 EST622_- Question -

What are the security implications of accessing DB2 via ODBC. What represents a transaction (that phase between commits), and what are its implications pertaining to creating plans (or not) and providing grants (or not).

THX JAL



______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5511 41 21_Re: DB2 UDB on NT BDC14_Philip Gunning20_pgunning@BOSCOVS.COM30_Tue, 9 Nov 1999 17:35:52 -0800344_us-ascii Are you sure you are on a BDC or a member server. Go to server manager and look to see if the server is really a BDC. It should work if it is. HTH Phil

-----Original Message----- From: Dustin Reiner [SMTP:Dustin.Reiner@INGRAMMICRO.COM] Sent: Tuesday, November 09, 1999 2:12 PM To: DB2-L@RYCI.COM Subject: DB2 UDB on NT BDC [...] 5553 61 24_-811 on SELECT statement14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Tue, 9 Nov 1999 10:23:29 +1000429_us-ascii A lot of repliers have missed the point here completely, I feel.

When no sorting occurs DB2 does NOT need to materialize the whole result set (on OS/390 and I presume same on all platforms). Suppose there are 1000 qualifying rows. As soon as DB2 has found the second qualifying row, it already knows that it must return -811. So no need to obtain (materialize) the other 998 rows. That would be quite silly! [...] 5615 35 30_Refertenial Integrity problems0_19_csutfin@AMSOUTH.COM30_Tue, 9 Nov 1999 17:31:43 -0600489_- I hope someone can help me here. DB2 V4.1 on MVS 5.2.2

We have a child table that we are trying to load. 1. The parent table (there is only one) is already loaded. 2. When I load the child using ENFORCE CONSTRAINT all of the row reject with the standard message FOREIGN KEY HAS NO PRIMARY KEY FOR RELATIONSHIP .......... 3. I have verified both the primary key of the parent and the foreign key defined for the child. everything looks fine. Definitions of the columns match. [...] 5651 21 43_Migrating Databases from S390 and UDB on NT10_DB2 Review21_db2review@HOTMAIL.COM28_Tue, 9 Nov 1999 18:38:39 EST549_- Question:

Other than essentially doing an extended REORG, ie. Unload - Transmit - Load from DB2 V6 on the MF to UDB on NT, is there an easier way to accomplish this ?

THX JAL

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5673 65 15_Re: Archive URL0_29_Greg.Palgrave@BANKWEST.COM.AU31_Wed, 10 Nov 1999 08:47:52 +0800593_us-ascii Rebecca,

Here's the 2 URLs you may need:

The RYC DB2-L page: http://www.ryci.com/db2-l

and the Archives: http://jupiter.ryci.com/archives/db2-l.html

Regards, Greg Palgrave Database Administration Group Information Services, Level 10, Tower Bank of Western Australia eMail : greg.palgrave@bankwest.com.au



Rebecca A Bond on 09/11/99 23:43:48

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Greg Palgrave/SDG/SS/BankWest) [...] 5739 56 28_Re: -811 on SELECT statement14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Wed, 10 Nov 1999 11:06:06 +1000549_us-ascii Uh-Oh, I'm getting a sense of Deja-vu...the 'Existence Wars' of '97 are on their way in '99...

Paul.

Michael Hannan wrote:

>>>>>>>>> Diverting to Existance Checks for a moment.

Therefore Singleton SELECT is a good method for existance checks where access path is O.K. (OPTIMIZE FOR 1 ROW not needed) and not too much work required to find out if a second row qualifies. It considerably out performs the SELECT COUNT(*) method where a significant no. of rows (> 2 or 3) qualify like my example of 1000 rows. [...] 5796 55 28_Re: -811 on SELECT statement0_24_DB46@DAIMLERCHRYSLER.COM30_Tue, 9 Nov 1999 13:28:50 -0500345_us-ascii It depends, if indexes are used there is usually no materialization. But may materialize with table scans.

Basically with -811's you cannot predict if it will the first, second, third, last, or now rows or that the data will materialize.

In short, you can't depend on the data retrieved or if it materializes or not. [...] 5852 168 54_Re: SQL Question : Finding Missing Entries in Sequence12_Brad Kiemann31_Brad.Kiemann@DCB.DEFENCE.GOV.AU31_Wed, 10 Nov 1999 11:08:46 +1100341_US-ASCII Here's my solution.

This is one place where a cartesian join is useful. Use it to generate your list of possible numbers using a case statement and catalog tables. This table can then be used as the driver for a not exists test. The select from SYSTABLES should be repeated in the join for as many digits in you number. [...] 6021 140 0_0_15_leon@CA.IBM.COM31_Wed, 10 Nov 1999 01:36:50 -0500519_us-ascii You are correct in saying that DB2 UDB for UNIX and NT require SNA connection from DRDA requesters like DB2 for OS/390 for 2-phase commit function. What I am recommending has nothing to do with DB2 UDB for UNIX and NT. I am talking about DB2 Connect connectivity to DB2 for OS/390. DB2 Connect will connect your applications to DB2 for OS/390 over either SNA or TCP/IP (and yes it does support 2-phase commit over TCP/IP). TCP/IP is a much better option for connecting from DB2 Connect to DB2 for OS/390. [...] 6162 187 0_0_15_leon@CA.IBM.COM31_Wed, 10 Nov 1999 01:42:46 -0500457_us-ascii This is no way contradicting what I said earlier. The note clearly states that:

"When we (DB2 UDB for UNIX and NT) are accessed by a host or AS/400 application, then if two phase commit is required (that is, the SPM is needed) then only SNA can be used."

The key phrases here is "DB2 UDB for UNIX and NT accessed by a host or AS/400 application". My recomendation for TCP/IP applies to connecting TO DB2 for OS/390 and not FROM. [...] 6350 133 18_Re: Applet-Problem13_Deepak Kini M21_deepakk@WIPHYD.GE.COM31_Wed, 10 Nov 1999 14:45:27 +0530545_iso-8859-1 Hi Moos,

Pls try this String url = "jdbc:db2://atlas:8889/G7218;user=yourid;password=yourpassword";

Hope this would help you

Deepak

----- Original Message ----- From: Alfred Moos Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, November 09, 1999 2:36 PM Subject: Applet-Problem



Hello all,

has someone a good advice to resolve my problem: My Java-applet can't access to DB2 on my server, the corresponding Java-application is successful. [...] 6484 169 18_Re: Applet-Problem11_Alfred Moos21_moos@FH-HEIDELBERG.DE31_Wed, 10 Nov 1999 10:21:58 +0100415_iso-8859-1 Hallo Deepak,

thank you very much for your help and your advice.

By chance I have found the reason of my problem. I think it is strange but true:

In the classpath environment variable of my local client I had specified explicidly the name of the subdirectory where my classes in construction and those for local tests are situated. It contains also the class of my test-applet. [...] 6654 29 12_OAM Problems0_18_mebert@AMADEUS.NET31_Wed, 10 Nov 1999 10:23:12 +0100396_us-ascii Hello all,

can somebody tell me where we can get infos about OAM (Objects Archive Manager, an IBM HW/SW that stores "Objects" (Records) on Optical disks)? One of our applications is having severe problems with this because OAM increasingly gives an error "Object length=0". In many cases, a later re-run succeeds, and nobody including the IBM CE knows what happened, or why. [...] 6684 60 47_Re: Migrating Databases from S390 and UDB on NT14_Julian Stuhler20_stuhlej@TRITON.CO.UK31_Wed, 10 Nov 1999 09:50:39 -0000547_iso-8859-1 Hi.

Depends how big your table is. Another option (for small tables only!) would be to code an INSERT....SELECT than runs on DB2 UDB for NT, but this would require some sort of host connectivity such as DB2 Connect to be present.

regards,

Julian

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

Julian Stuhler DB2 Specialist, IBM Gold Consultant Triton Consulting

E-mail: stuhlej@triton.co.uk Web: http://www.triton.co.uk Mobile: +44 (0)7768 446927 Office Tel: +44 (0)1603 693517 Office Fax: +44 (0)1603 693531 [...] 6745 95 0_14_Julian Stuhler20_stuhlej@TRITON.CO.UK31_Wed, 10 Nov 1999 09:36:26 -0000405_iso-8859-1 Hi Nancy.

I've seen this problem from time to time - its caused by using the CCA to catalog everything rather than the control centre.

Try removing the system from within the Control Centre (right click on system and select the remove option) then (still within Control Centre) add it back in again. Everything should now be OK and you should be able to see all objects etc. [...] 6841 19 17_Regarding DB2 UDB8_DVRAMESH16_DVRAMESH@INF.COM31_Wed, 10 Nov 1999 15:27:59 +0530467_- Hello Everybody,

I am new to DB2 UDB ver 5.1 i want to know how to create a userid for a particular instance and granting DBADM to that particular userid. Eagerly waiting for the reply.

Thanx in Advance Eshwar

================================================ 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. 6861 49 16_Re: OAM Problems32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Wed, 10 Nov 1999 12:01:00 +0200748_- Can you describe the problem more. We use EDM Suite. May be I can help you...

Cuneyt GOKSU GARANTİ TEKNOLOJİ Istanbul / TURKEY Tel : 0090-212-657 0404 Ext: 1218 mailto:Cuneytg@garanti.com.tr







-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: Wednesday, November 10, 1999 11:23 AM To: DB2-L@RYCI.COM Subject: OAM Problems



Hello all,

can somebody tell me where we can get infos about OAM (Objects Archive Manager, an IBM HW/SW that stores "Objects" (Records) on Optical disks)? One of our applications is having severe problems with this because OAM increasingly gives an error "Object length=0". In many cases, a later re-run succeeds, and nobody [...] 6911 54 39_Q, about logs and about setting OS time37_=?iso-8859-1?Q?Rasmus_Emil_M=F8ller?=17_AER@TOPDANMARK.DK31_Wed, 10 Nov 1999 11:14:11 +0100445_iso-8859-1 Hello to the group.

I am new to the list. We run 5 UDB V5.2.1 on Win NT 4.0 Enterprise Edition SP5 for our SAP 4.5B.

I have two questions:

1. The time on our UDB servers is behind. How should I set the time in a safe way? Can the time be set regularly? I guess there is no problem advancing the time, but if I set the time back, I assume UDB could get confused about timestamps for transactions in the logs? [...] 6966 50 31_Re: large table for OAM objects13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR31_Wed, 10 Nov 1999 14:06:34 +0200646_windows-1254 -----Original Message----- From: Kirk Hampton [mailto:khampto1@TXU.COM] Sent: Monday, November 08, 1999 4:25 PM To: DB2-L@RYCI.COM Subject: large table for OAM objects



Hi all, we are DB2 v5 on OS/390 v2.6. We are a long-time user of OAM, having had an ImagePlus installation in place since 1990. We also have OnDemand/390 running on a different LPAR (not data-sharing). We have always used both of these OAM object applications where the objects are only held in OAM's GROUPnn.* tables for a short time and are then written to an optical storage library by OAM's management cycle (similar to an HSM migrate under [...] 7017 62 21_Re: Regarding DB2 UDB14_Julian Stuhler20_stuhlej@TRITON.CO.UK31_Wed, 10 Nov 1999 12:11:55 -0000378_iso-8859-1 Eshwar,

you don't say what platform you're using, but DB2 UDB relies upon the underlying operating system for userid definition.

You therefore need to define the new userid to NT or whatever OS you're using, then use the "GRANT DBADM ON DATABASE xxxxx TO USER yyyyyyy" SQL statement (from a suitably authorised user) to give DBADM to the new user. [...] 7080 18 16_Re: OAM Problems0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 10 Nov 1999 07:18:26 -0500507_us-ascii Hi,

OAM is part of DFSMS/MVS and hence any documentation is under this label. If it is an OAM error, you could look at the OAM start of task for errors. Also, OAM errors are described in "DFSMS/MVS DFSMSdfp Diagnosis Reference Manual".

R/,,Ed

================================================ 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. 7099 77 36_db2 udb control center configuration14_Nancy Westover22_nwestover@NETSCAPE.NET29_Wed, 10 Nov 1999 07:17:37 EST792_US-ASCII [017] Unable to deliver user mail due to a full mailbag. Message was not delivered to TELECOM/VMCSNAT1/SStemmet (TELECOM/VMCSNAT1/SStemmet)



---------------------------------------------------------------------------- -- Microsoft Mail v3.0 (MAPI 1.0 Transport) IPM.Microsoft Mail.Note From: Nancy Westover To: DB2-L@RYCI.COM Date: 1999-11-10 04:50 Priority: 3 Message ID: 4E2401A6CB96D311AE790008C724ADD2







Hi!

I'm trying to setup my desktop NT Workstation to administer our DB2 UDB servers. We have both AIX and NT servers. Using Client Configuration Assistent I am able to set up the connections to both the aix servers and the NT servers. This is where the problem comes in. When I connect to some databases residing on AIX [...] 7177 86 31_Re: large table for OAM objects11_David Ayers24_david.ayers@HIGHMARK.COM31_Wed, 10 Nov 1999 07:28:23 -0500509_us-ascii Kirk, We are a pretty large image shop (2+ terabytes). We are currently storing 200 days in DB2, using segmented TS (the 4 gig limit pertains to a vsam limit, the size restriction on a segmented ts is 64 GB (DB2 v5). Some of our ts are in excess of 50 GB. This does pose some problems with backup / recovery / reorgs , but can be done. A word of caution on partitioning (and I am a big fan of partitioning) 1) choose the correct key - an ascending key may not be practical due to oam process of [...] 7264 46 28_Re: -811 on SELECT statement0_20_ddlusk@HOUSEHOLD.COM31_Wed, 10 Nov 1999 06:55:07 -0600282_us-ascii Martin: It's been my experience to not to depend on the data returned, but analyze the data, and write a better query, or if there are cases where one would know about more than one row, change the select to a cursor to analyze the data and process what is needed.... [...] 7311 121 32_Re: DB2 Service Wait (V5 OS/390)18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Wed, 10 Nov 1999 07:06:24 -0600590_iso-8859-1 Hi all, I once had a problem with utilities and discovered, after working with level 2, an unpublished, internal utility table or locking structure of some kind, forgot its name, was the problem. It could be serialization on this table/structure that is causing your problem. Maybe Roger or someone from that group can help out here. Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named [...] 7433 184 32_Re: DB2 Service Wait (V5 OS/390)15_Gingras, Martin30_Martin.Gingras@CCRA-ADRC.GC.CA31_Wed, 10 Nov 1999 08:49:04 -0500387_iso-8859-1 Hi Mike,

We are in fact using SMS and you may be right. The only thing is that we are using the REUSE option which should eliminate the need to allocate the file.

I will do some testing with the REUSE option to see if it works as described in the IBM books. Without the REUSE option, I agree that we could be experiencing this problem (specially with SMS). [...] 7618 30 16_RESTRICT ON DROP9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Wed, 10 Nov 1999 15:29:26 +0100529_us-ascii Hi All,

In our shop we have several databases and some of them have tables with restrict on drop. In which catalog table is registred which tables have restrict on drop, and which column is used? We want to use this for duplication reasons. The 'production' database is migrated to a test platform with BMC Change Manager. The previous test database is 'deleted' with a DROP DATABASE command. Therefore we want to find out before the DROP DATABASE which tables need to be altered with DROP RESTRICT ON DROP. [...] 7649 49 20_Re: RESTRICT ON DROP20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 10 Nov 1999 09:37:06 -0500620_- CLUSTERTYPE on SYSIBM.SYSTABLES

Blank means NO RESTRICT Y means RESTRICT ON DROP





> -----Original Message----- > From: Slot J.P. [SMTP:J.P.Slot@RF.RABOBANK.NL] > Sent: Wednesday, November 10, 1999 9:29 AM > To: DB2-L@RYCI.COM > Subject: RESTRICT ON DROP > > Hi All, > > In our shop we have several databases and some of them have tables > with restrict on drop. In which catalog table is registred which tables > have restrict on drop, and which column is used? > We want to use this for duplication reasons. The 'production' database > is migrated to a test platform with BMC Change [...] 7699 66 20_Re: RESTRICT ON DROP0_18_mebert@AMADEUS.NET31_Wed, 10 Nov 1999 15:36:35 +0100840_us-ascii It's column CLUSTERTYPE (sic) in SYSIBM.SYSTABLES. Values ' ' (blank) if no restrict, 'Y' if restrict.

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







From: "Slot J.P." on 10/11/99 14:29 GMT





Please respond to J.P.Slot@rf.rabobank.nl



To: DB2-L@RYCI.COM



cc: (bcc: Michael Ebert/MUC/AMADEUS)







Subject: RESTRICT ON DROP







Hi All,

In our shop we have several databases and some of them have tables with restrict on drop. In which catalog table is registred which tables have restrict on drop, and which column is used? We want to use this for duplication reasons. The 'production' database is migrated [...] 7766 13 45_Benchmark between MS SQL Server and UDB on NT12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Wed, 10 Nov 1999 08:38:36 -0600333_iso-8859-1 Is there a benchmark or whitepaper out there someplace comparing these two DBMS?

================================================ 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. 7780 63 20_Re: RESTRICT ON DROP0_24_filip.vanparys@SIDMAR.BE31_Wed, 10 Nov 1999 15:38:23 +0100485_us-ascii Hi Jaap,

on DB2 v5 for OS/390 the column CLUSTERTYPE of SYSIBM.SYSTABLE indicates if there is a restrict on drop on the table (blank = NO, Y = yes)

Best regards,

Filip Van Parys DB2 System Engineer Sidmar nv +32 9 347 30 57









J.P.Slot@rf.rabobank.nl on 10/11/99 15:29:26

Please respond to J.P.Slot@rf.rabobank.nl

To: DB2-L@ryci.com cc: (bcc: Filip VAN PARYS/ISM/SIDMAR/BE) Subject: RESTRICT ON DROP [...] 7844 83 20_Re: RESTRICT ON DROP9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Wed, 10 Nov 1999 15:54:56 +0100521_us-ascii Chuck, Larry, Venkat Thank you for your quick replies. Excuse my lack of knowledge on the columns of the DB2 catalog tables. We do have catalog manager and change manager from BMC. We are on version 5.3.03 of catalog manager and 5.3.03D of change manager. Indeed Change Manager handles drop restrict on drop when dropping a table. However, within the catalog manager it is not possible to select all tables that have a restrict on drop. If any1 can show me how to do that in catalog manager please tell me. [...] 7928 28 49_Re: Benchmark between MS SQL Server and UDB on NT11_Steve Mazer17_smazer@FMTUSA.COM31_Wed, 10 Nov 1999 09:50:57 -0500576_us-ascii Paul, Check out the presentation that Richard Yevich did at this year's DB2 Tech Conference, titled DB2 UDB 5.2 vs MS SQL Server 7.0

Regards, Steve Mazer Fourth Millennium Technologies

At 08:38 AM 11/10/99 -0600, you wrote: >Is there a benchmark or whitepaper out there someplace comparing these two >DBMS? > >================================================ >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. [...] 7957 59 34_Re: Refertenial Integrity problems13_Horacio Villa17_hvilla@TTI.COM.AR31_Wed, 10 Nov 1999 12:23:48 -0300479_iso-8859-1 Carol,

are you sure the relationship you're checking is the one DB2 is complaining? may be there's another one defined on the table?

Horacio Villa

-----Mensaje original----- De: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]En nombre de csutfin@AMSOUTH.COM Enviado el: Martes 9 de Noviembre de 1999 20:32 Para: DB2-L@RYCI.COM Asunto: Refertenial Integrity problems



I hope someone can help me here. DB2 V4.1 on MVS 5.2.2 [...] 8017 120 21_FYI: RESTRICT ON DROP9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Wed, 10 Nov 1999 16:40:16 +0100592_us-ascii Comments : Hello all, Thank you all for your swift replies. I got the messages and used the answers.

Regards,

Jaap Slot ------------------------[ Original Message ]-------------------- To : Slot J.P.@tps2@rabofacet Cc : From : "Horton, Libby" Date : Wednesday, November 10, 1999 15:31:05 F00

Hello - BMC Catalog Manager will show you the tables that have restrict on drop if you use the Search command. On the Table Search screen (that you can get to by entering S TB on the command line) use the WHERE clause WHERE clustertype = 'Y' [...] 8138 40 18_Re: Applet-Problem11_Alfred Moos21_moos@FH-HEIDELBERG.DE31_Wed, 10 Nov 1999 16:43:36 +0100343_iso-8859-1 Dear Steven,

by chance I have found the reason of my problem and could solve it.

In the classpath environment variable of my local client I had specified explicidly the name of the subdirectory where my classes in construction and those for local tests are situated. It contains also the class of my test-applet. [...] 8179 26 21_regarding alter table17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Wed, 10 Nov 1999 08:10:23 PST324_- Hello Everybody,

i have a doubt regarding alter table,

we have a table which is already existing, and i need to add one more column to this existing table. once added a new column to this existing table do i need to rebind the package and plan which is accessing this table. can anybody clear my doubt. [...] 8206 35 23_OS390 to UDB 5.2 on AIX12_Janet Rolsma12_janet@NM.NET31_Wed, 10 Nov 1999 09:07:50 -0700439_us-ascii Hi there.

I'm hoping someone out there can help me. I'm supposed to get a full copy (I think they'll be using an unload to create it) of a database that exists on an OS390 version of DB2. My problem is that the unload seems to coming over in EBCDIC. I need the files converted to ascii. Unfortunately, I can't tell if our utilities are converting it correctly because it still has a bunch of control characters in it. [...] 8242 94 28_Re: -811 on SELECT statement11_rick creech18_ykcirc@HOTMAIL.COM29_Wed, 10 Nov 1999 08:16:38 PST637_- Optimize for 1 row does affect the number of rows retrieved. It may have an affect on the access path, but the number of rows actually acccessed is the same.



>From: "Mackey, Glenn" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: -811 on SELECT statement >Date: Tue, 9 Nov 1999 13:04:55 -0600 > >I have been down this path too. >1. The manual says the data returned from a -811 is unpredictable (OS390. >That was enough to avoid using data returned from a -811 SELECT. See SQL >Reference SC26-8966-00 p444 SELECT statement >2. Executing a count(*) [...] 8337 13 32_Re: DB2 Service Wait (V5 OS/390)0_22_Bob_Yoder@ILLINOVA.COM31_Wed, 10 Nov 1999 10:23:39 -0600387_us-ascii I had the same problem with reorgs, you may want to check you dfdsms maintenace. There was a ptf that was needed to correct the problem.

================================================ 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. 8351 43 25_Re: regarding alter table19_Balaji.D (Exchange)20_Balaji.D@DHCMAIL.COM31_Wed, 10 Nov 1999 10:42:57 -0600636_iso-8859-1 Once you have altered the table, you need to rebind the package. Plan rebind is not required.

balaji

-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Wednesday, November 10, 1999 10:10 AM To: DB2-L@RYCI.COM Subject: regarding alter table



Hello Everybody,

i have a doubt regarding alter table,

we have a table which is already existing, and i need to add one more column to this existing table. once added a new column to this existing table do i need to rebind the package and plan which is accessing this table. can anybody clear my doubt. [...] 8395 62 25_Re: regarding alter table20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 10 Nov 1999 11:53:27 -0500631_- Normally after alter table there is no need to rebind packages or plans. But if you are adding column with time/timestamp/date then you need to rebind the packages involved. I am assuming, you just want to add new columns

> -----Original Message----- > From: Balaji.D (Exchange) [SMTP:Balaji.D@DHCMAIL.COM] > Sent: Wednesday, November 10, 1999 11:43 AM > To: DB2-L@RYCI.COM > Subject: Re: regarding alter table > > Once you have altered the table, you need to rebind the package. Plan > rebind > is not required. > > balaji > > -----Original Message----- > From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] > Sent: [...] 8458 54 25_Re: regarding alter table16_Michael McCarthy24_Michael_McCarthy@DTC.ORG31_Wed, 10 Nov 1999 11:55:47 -0500473_us-ascii Ravi,

If you add a datetime column (DATE, TIME, TIMESTAMP), and use the CURRENT special register as the default value, you will need to rebind all dependent packages.

-Mike.









ravi kumar hassan on 11/10/99 11:10:23 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Michael McCarthy/DTC) Subject: [DB2-L] regarding alter table [...] 8513 69 27_Re: OS390 to UDB 5.2 on AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 10 Nov 1999 12:06:55 -0500382_iso-8859-1 Janet:

You can FTP your EBCDIC dataset to AIX using ASCII option and it will bring the file in ASCII format. But the problem you'll face is with numeric (INTEGER, SMALLINT and DECIMAL) fields. You may unload these fields in the external format by using DIGITS function and then should be able to FTP using ASCII option (I haven't tried this, but should work) [...] 8583 25 29_Stored procedures development14_James R. Brown25_brownjr@CI.RICHMOND.VA.US31_Wed, 10 Nov 1999 12:30:25 -0500550_iso-8859-1 Hi group!

One quick survey question. Who is responsible for writing the stored procedures in your shop (DBAs or Developers)? Feel free to offer any justification you feel is revelant for either case.

Thank you in advance



Jim Brown DB2 DBA City of Richmond Richmond, VA

================================================ 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. 8609 40 58_DB2 v4.1 - Reorg of SYSCOPY and SYSLGRNX - Can it be done?14_Roybal, Adam A19_adam.roybal@EDS.COM31_Wed, 10 Nov 1999 11:34:37 -0600415_iso-8859-1 I have request from a DBA to reorg SYSCOPY and SYSLGRNX. My questions is can this be done and does anyone have sample JCL/suggestions?

I know I can do SYSCOPY with no problem. But, according to my DB2 v4.1 manual, we can NOT do SYSLGRNX!

Now, I have tested out reorging SYSLGRNX on a sandbox DB2 and it appears to handle it okay. But, this is only a SANDBOX region and not Production. [...] 8650 22 28_Re: -811 on SELECT statement0_19_Tim.Lowe@STPAUL.COM31_Wed, 10 Nov 1999 11:37:18 -0600406_us-ascii Since an "order by" clause is not allowed in a "singleton select", it would seem that sorts would not be required unless this is actually a join of 2 or more tables. Therefore, I think the issue of using a singleton select versus opening a cursor is not a performance or "row materialization" issue at all. But, using a count(*) could be a performance issue. So, I agree with Michael Hannan. [...] 8673 95 27_Re: OS390 to UDB 5.2 on AIX22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM31_Wed, 10 Nov 1999 17:39:47 -0000355_- I've never done this before & I was wondering what other folks thought about using DSNTIAUL to unload an EBCDIC DB2 for OS/390 table to tape specifying the DCB parm OPTCD=Q (converts EBCDIC to ASCII when writing to a tape) on the output DD; FTPing the tape in Binary to AIX. I'm assuming FTP on OS/390 can read an input data set that is on a tape. [...] 8769 85 28_Re: -811 on SELECT statement15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 10 Nov 1999 13:08:00 -0500311_iso-8859-1 Is -811 an error? (I asked for something in the SQL and it is there, but there is more) I think it should be warning and hence +811 like +100 for not found condition. We use singleton select for existence and it is lot more easier than opening, fetching and closing a cursor inside the program. [...] 8855 40 27_Migration of 5.2 DAS to 6.10_22_Rich.Elia@AUTOZONE.COM31_Wed, 10 Nov 1999 12:04:57 -0600371_us-ascii Has anyone experienced problems with running the migration command "db2imigr" on their 5.2 DAS to release 6.1? The error that comes up when we try it here is:

DBI1124E Instance dwadmin cannot be migrated.

Explanation: An attempt was made to migrate an instance. This instance cannot be migrated because:

o The instance is still active. [...] 8896 113 28_Re: -811 on SELECT statement20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 10 Nov 1999 13:17:50 -0500436_iso-8859-1 To me, this is an error. I use INTO clause thinking only one set of value would qualify but there are more and my program can't handle it (without cursor) so this is an error. Most of the time performance is an issue not the ease of coding. Since "Singleton select" and cursor with optimize for 1 row could have different accesspath hence it could make difference (unless CICS program -due to sub-task switch overhead). [...] 9010 12 4_Test25_Vijay.Sankaran (Exchange)26_Vijay.Sankaran@DHCMAIL.COM31_Wed, 10 Nov 1999 12:12:43 -0600265_iso-8859-1 test message

================================================ 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. 9023 55 33_Re: Stored procedures development0_24_lightsey@ITS.STATE.MS.US31_Wed, 10 Nov 1999 12:34:52 -0600429_us-ascii Developers - with the restriction that no developer can put one into production. Only their QA guru or a member of the group I work in can put stuff into the loadlib where the production SPs live ( os/390 environment ).







"James R. Brown" cc: Sent by: DB2 Data Subject: Stored procedures development Base Discussion List [...] 9079 103 33_Re: Stored procedures development26_Lindsay, William (SOM400A)24_WLindsay@EXCHANGE.ML.COM31_Wed, 10 Nov 1999 13:43:52 -0500456_iso-8859-1 We have had this capability for long time now. However, and I'm sure this is a reflection on us. We as developers are not allowed to build Stored Procedures with DB2. According to the DBA's they are too hard for us to debug. Personally speaking I am pretty comfortable with analysis without a GUI, or breakpoints. I should be, I was around with Grace Hopper. I knew Grace Hopper, she was a friend of mine.......And you are no Grace Hopper. [...] 9183 118 28_Re: -811 on SELECT statement14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Wed, 10 Nov 1999 13:57:38 -0500402_us-ascii IMHO, it's reasonable to consider -811 as an error beause the condition it indicates is erroneous when the singleton select is being used for its intended purpose. The problem is that SQL doesn't appear to have an operation designed specifically for existence testing, leaving developers to choose from a variety of ill-fitting kludges. The best solution would be a language enhancement. [...] 9302 133 33_Re: Stored procedures development14_Philip Gunning20_pgunning@BOSCOVS.COM31_Wed, 10 Nov 1999 14:04:51 -0800622_us-ascii Stored Procedures(SP) should be requested by and written by application programmers and review by DBAs. With V5.1 of DB2 for OS390, we urge (nice word for push) application developers to use them wherever possible for distributed processing to gain the perormance benefits offered. Approval of the SP, definition in the catalog, SQL review, and production catalog definition must be done by DBAs. Your change management software should be how the procedure gets moved to production since an SP is just another type of program source. I agree they are harder to debug, but that's what you have unit test for, [...] 9436 56 33_Re: Stored procedures development12_John Cameron47_John_Cameron/MSI-CORP/MSI-INC@MSI-INSURANCE.COM31_Wed, 10 Nov 1999 13:17:41 -0600313_us-ascii Developers. Most of them are old COBOL subroutines that are now being used to satisfy new Visual Basic web application data needs. The explain is looked at by a DBA like any other subroutine. Only the DBA's can insert the row into sysprocedures.

This is also my test of posting to this list. [...] 9493 168 33_FW: Stored procedures development13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 10 Nov 1999 13:30:48 -0600363_iso-8859-1 Hi, The application team should be responsible for writing Stored Procedures (SP)

We basically do as Phil does.

We get the developers to write the stored procedures. To debug the programs we get them to initially develop standard Cobol program and do as much testing as they can before actually "turning it into a stored procedure". [...] 9662 30 29_Visual Explain SQLSTATE=4250315_Zander, Barb J.19_bzander@STATE.ND.US31_Wed, 10 Nov 1999 13:28:25 -0600401_iso-8859-1 We are running Visual Explain V5 and getting a -567 SQLSTATE=42503. This error occurs when we signon to DB2 with an ID(ABC) that has BINDAGENT authority for another ID(XYZ). When we try and run a dynamic explain on a package for user(XYZ) we get this error. Its seems like it is not picking up the BINDAGENT authority. Any ideas on how to get this to work? I would sure appreciate it. [...] 9693 33 41_Handling security within distributed apps13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 10 Nov 1999 14:44:31 -0500537_- I know we've discussed this several times but I have a slight twist on the subject. One of our development groups wants to store user IDs in a table (DB2 for OS/390) within their VB app along with the role(s) of the user. In other words, they want to code into their app, the security of the app/data. I've told them they can't store the role and need to incorporate security by using 2 RACF groups, one which will contain the IDs which can select and the another which will contain IDs which can update. Don't even tell me about [...] 9727 12 4_TEST12_John Cameron47_John_Cameron/MSI-CORP/MSI-INC@MSI-INSURANCE.COM31_Wed, 10 Nov 1999 13:47:08 -0600255_us-ascii TEST

================================================ 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. 9740 224 33_Re: Stored procedures development26_Lindsay, William (SOM400A)24_WLindsay@EXCHANGE.ML.COM31_Wed, 10 Nov 1999 14:49:12 -0500421_iso-8859-1 Venkat,

Nice to hear from you. I appreciate the detailed information. When I approached the idea of using them, I was given the simple "Too hard to debug" retort. We workaround the restriction by writing Shadow for now.

Best regards,

Bill Lindsay

Project Manager - Web Development Merrill Lynch Global Data Sourcing and Delivery 400 Atrium Drive - 5S51A Somerset NJ 08873-2861 [...] 9965 75 62_Re: DB2 v4.1 - Reorg of SYSCOPY and SYSLGRNX - Can it be done?18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Wed, 10 Nov 1999 13:52:50 -0600614_iso-8859-1 Adam, here's a quote directly out of the OS390 V4 DB2 Utility Guide, from the Cat/Dir reorg section: "You can run REORG TABLESPACE on the table spaces in the catalog database (DSNDB06) and the SCT02, SPT01, and DBD01 table spaces in the directory database (DSNDB01)." Now, there may be a PTF that would allow reorging SYSLGRNX. You'll need to check that out with your DB2 installer. Finally, it would be best if you could perform reorgs when needed, not on some daily/weekly/monthly schedule. Perform RUNSTATS frequently on the Catalog (DSNDB06) tables to determine when a reorg is due. Then reorg [...] 10041 40 48_DSN3@SGN security exit from CICS to enable RACF.15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 10 Nov 1999 15:22:35 -0500577_iso-8859-1 We tried to install security exit routine DSN3@SGN to enable RACF security checking for CICS transaction. Currently we are using character string in the AUTH parameter of RCT entry. But this character string is defined to RACF as group without any user-id associated with it(Is this a problem?). We need to implement one transaction with AUTH=GROUP, but others should work as it is earlier. So we thought of using DSN3@SGN and implemented it. The transaction with AUTH=GROUP started working as we thought, but all other transactions started abending with error [...] 10082 25 35_Triggers with DB2 UDB for OS/390 V612_Mabel Blanco23_mblanco@BANCORIO.COM.AR31_Wed, 10 Nov 1999 17:23:26 -0300313_us-ascii Hi list members !

We are thinking of migrating our DB2 for OS/390 V5 to DB2 UDB for OS/390 V6, one of the reason is that we need to use triggers. The question is: What happens with the performance if we use triggers for same tables, which are used for a very important number of transaction? [...] 10108 50 33_Re: Stored procedures development14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM31_Wed, 10 Nov 1999 12:38:22 -0800313_iso-8859-1 Developers. But ... jump in and do the first prototypes. That way you will set the standard (official or unofficial). Developers tend to clone off of each other. It's perfectly logical, why reinvent the wheel. So at least they will be cloning off of good samples. The areas that will benefit are: [...] 10159 25 36_DB2 Connect and AS/400 Client Access12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 10 Nov 1999 13:24:37 -0800331_us-ascii Can I use the same version of DB2 CONNECT (5.2++) that I use to connect to OS/390 to connect to an AS/400? If so, how?

I know the AS/400 uses software called Client Access which uses an ODBC 32bit driver. The shop I'm at would just as soon not have to install Client Access and DB2 connect for each user's PC. [...] 10185 100 39_Re: Securing Access through DB2 Connect14_Philip Trbovic19_TRBOVICP@BCBSIL.COM31_Wed, 10 Nov 1999 15:46:01 -0600637_US-ASCII Mike, I changed the authentication on the DB2 Connect server to SERVER. SYSIBM.USERNAMES is empty. Do I need to populate the CDB tables in order for the security to work?

Thanks for the help.

>>> Michael McCarthy 11/8/1999 12:54:05 PM >>> Phil,

Make sure the database manager configuration parm AUTHENTICATION is set to SERVER on the DB2 Connect gateway. Also check SYSIBM.USERNAMES (SYSUSERNAMES for V4 and below) on OS/390 to be sure the userid is not getting translated to an authorized id. You have said that the client is NT with DB2 Connect going to an AIX DB2 Connect [...] 10286 58 46_DB2 Service Wait (V5 OS/390) strikes again !!!14_Max 'Guderian'16_mocion@LIBERO.IT31_Wed, 10 Nov 1999 22:48:11 +0100356_us-ascii Hi all collegues.

I experimented a similar behaviour this week and it was for all utilities running (runstats, image copy,

reorg...). All start with a big increasing in page-ins, mainly due to some batch jobs updating some

tables and then , when utilities start, they remain in a waiting state. If you execute a -DISPLAY [...] 10345 51 31_Re: Migration of 5.2 DAS to 6.111_Steve Mazer17_smazer@FMTUSA.COM31_Wed, 10 Nov 1999 17:06:21 -0500599_us-ascii Rich, Are you sure that you need to migrate the admin server? Its not really a DB2 instance. Does it still run without this command? Is is functional?

Regards, Steve Mazer Fourth Millennium Technologies

At 12:04 PM 11/10/99 -0600, you wrote: >Has anyone experienced problems with running the migration command >"db2imigr" > on their 5.2 DAS to release 6.1? The error that comes up when we try it >here is: > > DBI1124E Instance dwadmin cannot be migrated. > > Explanation: An attempt was made to migrate an instance. This > instance cannot be migrated because: > > o The [...] 10397 140 39_Re: Securing Access through DB2 Connect16_salhany, michael23_salhany_michael@EMC.COM31_Wed, 10 Nov 1999 17:16:59 -0500448_iso-8859-1 Mike:

You need to populate the CDB tables. First decide if you want in-bound and/or out-bound authid translation. Here are some examples: -

INSERT INTO SYSIBM.IPNAMES (LINKNAME, SECURITY_OUT, USERNAMES, IBMREQD, IPADDR) VALUES ('LUDB21', 'A', ' ', 'N', '160.159.7.120') ;

--INSERT INTO SYSIBM.IPNAMES --(LINKNAME, SECURITY_OUT, USERNAMES, IBMREQD, IPADDR) --VALUES ('LSW1087', 'P', ' ', 'O', '168.159.21.87') ; [...] 10538 30 46_DB2 Connect Performance - SNA connection LU6.215_Michael Bancale21_mbancale@TXFB-INS.COM31_Wed, 10 Nov 1999 16:41:34 -0600342_iso-8859-1 We have a programmer which is retreiving data into an MS Access database using a query which is coded as an insert at the MS Access level. This query runs in 25 minutes on the mainframe in QMF. When we run the Access query it takes 6 hours to pass 660,000 rows down to the PC. We are on DB2 4.1 and using DB2 Connect EE v6.0. [...] 10569 42 28_Re: -811 on SELECT statement14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 10 Nov 1999 09:40:05 +1000622_us-ascii ORDER BY is not the only thing that can cause a sort. Hybrid and Merge Scan Joins often have DB2 Sorts, and List Prefetch also. Sometimes OPTIMIZE FOR 1 ROW needed to avoid these access paths.

However original problem specified no DB2 sorts involved.

From: Michael Hannan

>From: Tim.Lowe@STPAUL.COM >Subject: Re: -811 on SELECT statement >To: DB2-L@RYCI.COM > >Since an "order by" clause is not allowed in a "singleton select", it would seem >that sorts would not be required unless this is actually a join of 2 or more >tables. >Therefore, I think the issue of using a singleton select [...] 10612 115 28_Re: -811 on SELECT statement14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 10 Nov 1999 09:45:35 +1000455_us-ascii Optimize For n Rows does NOT effect the number of rows retrieved. Its up to program to fetch as many as desired. Its merely telling the optimizer how many we intend to fetch (although we may lie to it - not advisable).

It has an effect on some access paths including changing the number of rows that are scanned and possibly materialised before the the first fetch happens. Thats why different access paths perform quite differently. [...] 10728 73 28_Re: -811 on SELECT statement14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 10 Nov 1999 09:50:34 +1000344_us-ascii Its possible to determine materialisation cases from EXPLAIN output. TS Scan does not necessarily imply materialisation of full result set, although it may imply bad performance. A TS scan can still stop as soon as a second qualifying row is found, for a Singleton SELECT, and terminate ignoring the remainder of qualifying rows. [...] 10802 102 28_Re: -811 on SELECT statement0_19_Tim.Lowe@STPAUL.COM31_Wed, 10 Nov 1999 17:36:19 -0600347_us-ascii Michael, Since I already said that there would be no sort "unless this is actually a join of 2 or more tables", and I think we agree that there cannot be an "order by" clause on a "singleton select", then what do you think would cause a sort in this case? And, why would you think that list prefetch would cause a sort in this case? [...] 10905 61 34_Re: Refertenial Integrity problems14_Linda Claussen18_lindafc@NETINS.NET31_Wed, 10 Nov 1999 17:40:30 -0600505_ISO-8859-1 Carol,

If you have verified that the PK in the parent table column definitions exactly match the FK column definitions and sequence then you may want to check the Unique Index on the Parent tables Primary Key to make sure it is valid.

Linda F. Claussen Claussen & Assoc. lindafc@netins.net http://www.netins.net/showcase/lclaussen

---------- From: csutfin@AMSOUTH.COM To: DB2-L@RYCI.COM Subject: Refertenial Integrity problems Date: Tuesday, November 09, 1999 5:31 PM [...] 10967 56 50_Re: DB2 Connect Performance - SNA connection LU6.224_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Wed, 10 Nov 1999 17:47:40 -0800401_us-ascii Michael, Both Lockwood Lyon and Leon Katsnelson have presented DRDA tuning sessions at IDUG (and, I presume, DB2 Tech.). Think about the path length between DB2 and QMF on the host. Now think about the path length - and all of the intervening hardware and software - between DB2 and Access on the PC. You have to performance tune each of these components. If you need help, let me know. [...] 11024 61 49_Re: Benchmark between MS SQL Server and UDB on NT13_MARTIN, Keith33_Keith.MARTIN@SUNCORPMETWAY.COM.AU31_Thu, 11 Nov 1999 10:19:53 +1000671_- Steve,

For those of us not in the US, where can we get a copy of the presentation?

Keith Martin Application Specialist Services Database Management +617 383 55246 mailto:keith.martin@suncorpmetway.com.au

The views expressed herein are the views of the writer and may not necessarily be the views of SUNCORP-METWAY.



> -----Original Message----- > From: Steve Mazer [SMTP:smazer@FMTUSA.COM] > Sent: Thursday, November 11, 1999 12:51 AM > To: DB2-L@RYCI.COM > Subject: Re: Benchmark between MS SQL Server and UDB on NT > > Paul, > Check out the presentation that Richard Yevich did at this year's DB2 Tech > Conference, titled DB2 [...] 11086 125 21_FYI: RESTRICT ON DROP14_Kona, Srikiran26_Srikiran.Kona@FRITOLAY.COM31_Wed, 10 Nov 1999 09:40:16 -0600585_- Comments : Hello all, Thank you all for your swift replies. I got the messages and used the answers.

Regards,

Jaap Slot ------------------------[ Original Message ]-------------------- To : Slot J.P.@tps2@rabofacet Cc : From : "Horton, Libby" Date : Wednesday, November 10, 1999 15:31:05 F00

Hello - BMC Catalog Manager will show you the tables that have restrict on drop if you use the Search command. On the Table Search screen (that you can get to by entering S TB on the command line) use the WHERE clause WHERE clustertype = 'Y' [...] 11212 41 36_Buffer Manager I/O error during read0_21_dbasupport@WILLIS.COM31_Thu, 11 Nov 1999 08:49:26 +0000454_us-ascii Hi,

We have had three errors this week on our main DB2 system similar to the following

12.04.29 STC15550 DSNB225I -DB2Q DSNB5RDP - BUFFER MANAGER I/O ERROR DURING READ DBNAME=DBSS2PRD SPACENAME=SUPOSTS DATA SET NUMBER=3 MM ERROR CODES=X'00301114' DB2 REASON CODE=X'00C200C0' 12.04.55 STC15550 DSN3201I -DB2Q ABNORMAL EOT IN PROGRESS FOR USER=A880402 CONNECTION-ID=DB2CALL CORRELATION-ID=UQMFRBZP JOBNAME=UQMFRBZP TCB=009AA488 [...] 11254 89 62_=?iso-8859-1?Q?R=E9f._:_Buffer_Manager_I/O_error_during_read?=14_Denzil Coulter26_Denzil.Coulter@UNICIBLE.CH31_Thu, 11 Nov 1999 10:11:01 +0100419_iso-8859-1 We had a similar problem in our acceptation environment 2 days ago

DSNB225I -DBGA DSNB5RDP - BUFFER MANAGER I/O ERROR DURING READ DBNAME=DGAOSI46 SPACENAME=I0003923 DATA SET NUMBER=1 MM ERROR CODES=X'00140408' DB2 REASON CODE=X'00C200A4'

A recover of the index solved our problem.

I have not yet determined what caused the problem.

We are DB2 V5 PTF level 9918 , OS/390 V2.6 [...] 11344 84 33_Re: Stored procedures development9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Thu, 11 Nov 1999 10:23:38 +0100378_us-ascii Hi all,

Most of the replies I read said the developers had to write the stored procedures. But the DBA should stay involved in reviewing and advising about the code. As the DBA also is reviewing and advising about other programs that are entering the database. Other shops don't allow the stored procedures (yet) for technical or other (political) reasons. [...] 11429 22 12_Table Rename20_Mastan, Shabbir (S.)16_smastan@FORD.COM31_Thu, 11 Nov 1999 10:14:52 -0000476_iso-8859-1 We have a DB2 (ver 5) system on OS/390 which needs to be up and running while we load up data from an Oracle database. One of the avenues we are exploring is the DB2 table rename facility. We would have mirror tables on the DB2 system and load up the mirror tables using BMC load, then once that was done, flip over the tables by using the table rename facility. I was wondering if anybody out there had experience with this. Any suggestions would be welcome. [...] 11452 35 8_Triggers12_Mead ,Duncan36_duncan.mead@LIVERPOOL-VICTORIA.CO.UK31_Thu, 11 Nov 1999 10:35:00 -0000325_- Hi, I am just getting to know DB2 in more detail, and was rather suprised to discover that triggers are not supported on OS/390 until version 6. What have people been doing before this? Do they manage with logic in application programs, or does version 5 have the equivalent function somewhere, called something else? [...] 11488 38 44_DB2/SQL application sharing tables with CICS17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Thu, 11 Nov 1999 12:20:40 +0100573_us-ascii Hi. Here is a beginner question. Please, don't laugh! I want to access to some particular DB2 databases (in a client's OS/390 system) normally accessed to by CICS tasks. I don't want to use CICS to perform the read/write process (for the damned good reason that I know nothing about CICS); my C application will read/write DB2 tables by SQL queries. I already know how to write C/SQL applications like that, but the question is: should I take particular cares in the process, so that I can be sure that my update process on the tables will not interfere with [...] 11527 25 28_Re: -811 on SELECT statement14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 11 Nov 1999 05:00:45 -0600392_ISO-8859-1 My comment is related more to the issue of doing existence checks in the first place.

If the purpose is only to check for existence of a row before processing it - why not just do without the existence check completely and handle the ROW NOT FOUND condition. I assume that in most cases the row does exist, so testing for existence every time is an avoidable overhead. [...] 11553 24 3_DBD0_25_Julian_Peacock@SWEB.CO.UK31_Thu, 11 Nov 1999 11:23:00 +0000409_ISO-8859-1 Esteemed colleagues,

Quick question: When and what causes a DBD to be placed in the EDM pool?

Julian

DB2 DBA SWEB/LE Plymouth UK Europe

================================================ 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. 11578 67 48_Re: DB2/SQL application sharing tables with CICS8_georgewu27_george.wu@CHINATRUST.COM.TW31_Thu, 11 Nov 1999 19:23:19 +0800681_iso-8859-1 Hi CICS and DB2 attachment connection thru threads ,CICS support transaction DSNC ,and application transaction name,plan name define in CICS RCT.



George Wu george.wu@chinatrust.com.tw





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On > Behalf Of > Giorgio De Nunzio > Sent: Thursday, November 11, 1999 7:21 PM > To: DB2-L@RYCI.COM > Subject: DB2/SQL application sharing tables with CICS > > > Hi. Here is a beginner question. Please, don't laugh! > I want to access to some particular DB2 databases (in a > client's OS/390 > system) normally accessed to by CICS tasks. > I don't want [...] 11646 220 7_Re: DBD12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Thu, 11 Nov 1999 06:05:45 -0600360_us-ascii Julian:

If the DBD is not in the EDM Pool when the DB2 Object is opened it is then loaded into the EDM Pool. The attached gif shows how the measurement data should look on a production system. The Load for the DBDs should be flat, that is they are in the EDM Pool, and the curved line shows the reference activity against the loaded DBDs. [...] 11867 28 28_Re: -811 on SELECT statement14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 11 Nov 1999 06:54:09 -0600458_ISO-8859-1 << If you received this post twice - apologies. >>



My comment is related more to the issue of doing existence checks in the first place.

If the purpose is only to check for existence of a row before processing it - why not just do without the existence check completely and handle the ROW NOT FOUND condition. I assume that in most cases the row does exist, so testing for existence every time is an avoidable overhead. [...] 11896 73 33_Re: Stored procedures development14_James R. Brown25_brownjr@CI.RICHMOND.VA.US31_Thu, 11 Nov 1999 08:00:55 -0500419_iso-8859-1 Many thanks to all that replied to the "survey" question. This is exactly what I am looking for to present to management.

Jim Brown DB2 DBA City of Richmond Richmond, VA

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Steven Camitta Sent: Wednesday, November 10, 1999 3:38 PM To: DB2-L@RYCI.COM Subject: Re: Stored procedures development [...] 11970 28 27_Recommend Books for UDB/AIX0_15_sokeefe@VCU.EDU31_Thu, 11 Nov 1999 08:02:07 -0500350_us-ascii Esteemed List: I have been asked to recommend manuals/books for DB2 newbies, UDB(V5.2)/AIX environment. As I am not new to DB2, I never purchased any UDB (V5.2) books for myself, lots of MVS books, and a one V2.1 for AIX book when it was new to us. These folks have NO DB2 background, just some Oracle. All replies will be appreciated. [...] 11999 71 48_Re: DB2/SQL application sharing tables with CICS18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 11 Nov 1999 07:07:23 -0600346_iso-8859-1 Dr. De Nunzio, What do you mean by your statement: "so that I can be sure that my update process on the tables will not interfere with CICS treatment of data?" Unless you have a window of time to run your process when the CICS workload isn't running, you should consider your process as competition for access to the data in DB2. [...] 12071 53 28_Re: -811 on SELECT statement18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 11 Nov 1999 07:11:52 -0600609_iso-8859-1 I knew someone would come up with the right answer about existence checks if the subject stayed alive long enough.

SQL with an attitude, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, [...] 12125 121 39_Re: Securing Access through DB2 Connect16_Michael McCarthy24_Michael_McCarthy@DTC.ORG31_Thu, 11 Nov 1999 08:23:30 -0500615_us-ascii Phil,

The only tables you need to populate are SYSIBM.LUNAMES, and SYSIBM.LOCATIONS. If you indicate user translation in LUNAMES by setting the USERNAMES column to I, O, or B, you will need to populate SYSIBM.USERNAMES. The USERNAMES table can be used to limit access to certain users through a particular LU, and optionally switch their authorization id by populating the NEWAUTHID column. On the DB2 Connect gateway, make sure the database alias for the DCS database was cataloged with AUTHENTICATION DCS. This should be all you need to do. If you still have problems, you may send me a copy [...] 12247 52 16_Re: Table Rename20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 11 Nov 1999 09:21:47 -0500592_- Mastan

Table rename is very handy to avoid down time in a busy system (with the restriction that the qualifier can not be changed, only the name change of the table is allowed).

Below is one practical scenario -

I have Large table with 300 million rows. Someone wanted to refresh the data from VSAM+IMS world (there were not happy with the present data). We created a shadow table, loaded the data derived from VSAM+IMS world, then renamed the original table and created an alias for the shadow table (same as name as original table) and rebound all the packages. [...] 12300 61 16_Re: Table Rename0_18_damcon2@US.IBM.COM31_Thu, 11 Nov 1999 09:21:43 -0500321_us-ascii Shabbir Mastan,

Be aware that the tablespace and index names don't change. Depending on naming conventions and what's being done, this can cause confusion and potential mistakes. You might look into using aliases to point to one set, then when ready point them to the new set. This might be cleaner. [...] 12362 14 30_Access DB2 UDB OS/390 From AIX0_18_DB2DBAinTX@AOL.COM29_Thu, 11 Nov 1999 09:25:26 EST346_us-ascii Can you access DB2 on OS/390 from AIX with DB2 Connect EEE without having DB2 installed on AIX?

================================================ 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. 12377 43 7_Re: DBD20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 11 Nov 1999 09:28:24 -0500348_- DBD has to be in EDM pool for any SQL to execute for the database. But the rules on taking S lock on DBD differs.

- Static DML sqls take S lock only when DBD is brought to EDM pool the first time. - Dynamic sqls take S lock during each execution unless EDM Pool DYNAMIC CACHE is on and dynamic sql statement is found in the EDM POOL. [...] 12421 69 7_Re: DBD13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Thu, 11 Nov 1999 08:42:39 -0600613_ISO-8859-1 A simple display command (-DIS DATABASE(yourdb) SPACENAM(*)) will cause the DBD to be placed into the EDM POOL as well.

Ken McDonald BMC Software

>-----Original Message----- >From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] >Sent: Thursday, November 11, 1999 8:28 AM >To: DB2-L@RYCI.COM >Subject: Re: DBD > > >DBD has to be in EDM pool for any SQL to execute for the >database. But the >rules on taking S lock on DBD differs. > >- Static DML sqls take S lock only when DBD is brought to EDM >pool the first >time. >- Dynamic sqls take S lock during each execution unless EDM [...] 12491 15 19_Reorgchk in DB2/UDB12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Thu, 11 Nov 1999 08:52:28 -0600371_us-ascii Does anyone have a method for automatically generating reorgs as a result of the report produced by REORGCHK?

GSH

================================================ 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. 12507 36 34_Re: Access DB2 UDB OS/390 From AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 11 Nov 1999 09:56:28 -0500649_iso-8859-1 Did you mean DB2 Connect EE? Yes, you can using either SNA or TCP/IP.

Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > DB2DBAinTX@AOL.COM > Sent: Thursday, November 11, 1999 9:25 AM > To: DB2-L@RYCI.COM > Subject: Access DB2 UDB OS/390 From AIX > > > Can you access DB2 on OS/390 from AIX with DB2 Connect EEE > without having DB2 > installed on AIX? > > ================================================ > 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 [...] 12544 54 31_Re: Recommend Books for UDB/AIX13_Horacio Villa17_hvilla@TTI.COM.AR31_Thu, 11 Nov 1999 12:33:32 -0300406_iso-8859-1 Sean,

you can download SQL COOKBOOK from http://ourworld.compuserve.com/homepages/Graeme_Birchall/. It covers "how to do it in SQL". There are 2 versions, for DB2 5.2 & 6.1. It's very good and it's free. Another book that I've found very good is The Universal Guide to DB2 UDB (may be the title is not correct) by Don Chamberlain, though I think it covers DB2 UDB 5.1. Horacio Villa [...] 12599 24 18_Sequence of Cursor13_Scott Lindsey29_sflindsey@HIGHLIGHTS-CORP.COM31_Thu, 11 Nov 1999 10:23:17 -0500356_us-ascii This is really more of an applications question than a DBA question but here I go. If a table is clustered and a cursor is opened without an "ORDER BY" clause, are the rows of the cursor returned in the clustering sequence??? I have seen some explains where the order by is ignored if the "ORDER BY" clause specifies the clustering sequence. [...] 12624 139 28_Re: -811 on SELECT statement15_Patrick Bossman24_pbossma1@TAMPABAY.RR.COM31_Thu, 11 Nov 1999 10:26:03 -0500505_iso-8859-1 Hi Tim,

Even for single table access, list prefetch can be used with single and multiple index access. When list prefetch is used the list of RIDS are generated, the RIDS are sorted in ascending order by page number, then the pages are prefetched using the sorted RID list.

Pat

----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, November 10, 1999 6:36 PM Subject: Re: -811 on SELECT statement [...] 12764 48 22_Re: Sequence of Cursor20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 11 Nov 1999 10:30:18 -0500333_- If there is no list prefetch, i/o parallelism and simple tablespace scan there may be a possibility of data being returned in the physical order. But even a clusterratio of 100 does not guarantee perfect order by.

use of index without list prefetch might also bring data in index order but again there is no guarantee. [...] 12813 33 60_Re: :VIRUS ALERT! IN ATTACHMENT~RE: -811 ON SELECT STATEMENT12_Sue Janowitz18_SJanowitz@NEFN.COM31_Thu, 11 Nov 1999 10:35:43 -0500569_- A warning to all - McAfee detected a virus in an attachment to mail from Patrick Bossman - see subject and details below.

Sue Janowitz New England Financial Information Services 501 Boylston Street Boston, MA 02116 email: SJanowitz@nefn.com



> -----Original Message----- > From: Patrick Bossman [SMTP:pbossma1@TAMPABAY.RR.COM] > Sent: Thursday, November 11, 1999 10:21 AM > To: DB2-L@RYCI.COM > Subject: EMAIL SCAN:VIRUS ALERT! IN ATTACHMENT~ RE: -811 ON SELECT > STATEMENT > > Attachment file : Happy99.exe > Virus name : W32/Ska.exe > > > [...] 12847 88 46_Virus Detected on RE: -811 on SELECT statement14_Ernst Keith RK30_keith.ernst@ZCSWILM.ZENECA.COM31_Thu, 11 Nov 1999 10:42:03 -0500692_- Members,

Please be advised!

My firewall detected a virus on a response to the -811 on SELECT Statement.

I do not know whom it came from.

R. Keith Ernst Phone - 302.886.3997 or 1.800.456.3669 ext. 3997 Fax - 302.886.4749 Email - keith.ernst@zcswilm.zeneca.com AstraZeneca





> ---------- > From: DB46@DAIMLERCHRYSLER.COM[SMTP:DB46@DAIMLERCHRYSLER.COM] > Sent: Tuesday, November 09, 1999 1:28 PM > To: DB2-L@RYCI.COM > Subject: Re: -811 on SELECT statement > > It depends, if indexes are used there is usually no materialization. But > may > materialize with table scans. > > Basically with -811's you cannot predict if it will the first, [...] 12936 42 38_Virus Alert - ScanMail for Lotus Notes0_22_BILL_GALLAGHER@PHL.COM31_Thu, 11 Nov 1999 10:52:13 -0500447_us-ascii Our email system detected an infected file that was attached to an email from Patrick Bossman. Please be aware of this, and discard any emails with the "Happy99.exe" file attachment, or you may be at risk of getting infected as well.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT

---------------------- Forwarded by BILL GALLAGHER/Phoenix Home Life Mutual Insurance on 11/11/99 10:50 AM --------------------------- [...] 12979 128 28_Re: -811 on SELECT statement14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 11 Nov 1999 03:00:57 +1000369_us-ascii Tim,

We are mainly in agreement it seems. So sorry if I seem to be difficult on this issue.

The choice between Cursor with Optimize and a Singleton Select would be purely a performance issue for me since neither are difficult to code. I was trying to point out that the optimum choice is not a simple one and varies depending on situations. [...] 13108 69 12_Re: Triggers0_24_DB46@DAIMLERCHRYSLER.COM31_Thu, 11 Nov 1999 11:14:49 -0500749_us-ascii Mostly used programmatic methods or referential integrity.

Dean









"Mead ,Duncan" on 11/11/99 05:35:00 AM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc:





Subject: Triggers















Hi, I am just getting to know DB2 in more detail, and was rather suprised to discover that triggers are not supported on OS/390 until version 6. What have people been doing before this? Do they manage with logic in application programs, or does version 5 have the equivalent function somewhere, called something else? [...] 13178 118 50_Re: Virus Detected on RE: -811 on SELECT statement26_Singh, Robi R SSI-TSEA-34225_Robi.R.Singh@IS.SHELL.COM31_Thu, 11 Nov 1999 17:00:17 +0100446_iso-8859-1 It came from Patrick Bossman. It was detected in a happy99.exe file attached to the mail. My firewall detected it but was unable to disinfect and quarantined the offending file.

Patrick, you will have disinfect yourself !

Robi Singh .

DB2 and IMS Support Team. Shell Services International, The Netherlands.

Tel (+ 31) 70 303 4536 Fax (+31) 70 303 4011 Home (+31) 71 562 1182 Robi.R.Singh@IS.shell.com [...] 13297 49 18_Sequence of Cursor14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 11 Nov 1999 03:16:46 +1000374_us-ascii This question seems to mix a logical concept with physical access paths.

Simply if you need the rows in order, you must code ORDER BY to guarantee it and there is no penalty for doing so, other than not being permitted to update (DB2 sort may be avoided).

If you don't really need the rows in order, do not code ORDER BY under any circumstances. [...] 13347 68 16_Re: Table Rename13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 11 Nov 1999 10:20:53 -0600538_iso-8859-1 Look at the restrictions on the rename, table cannot be referenced in a view and others, there maybe some implications if the tables use RI. gm

-----Original Message----- From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Sent: Thursday, November 11, 1999 8:22 AM To: DB2-L@RYCI.COM Subject: Re: Table Rename



Mastan

Table rename is very handy to avoid down time in a busy system (with the restriction that the qualifier can not be changed, only the name change of the table is allowed). [...] 13416 27 35_Loading from a delimited input file13_John Arbogast16_jfarbo@YAHOO.COM31_Thu, 11 Nov 1999 08:23:24 -0800288_us-ascii DB2 V5, OS/390

Is there a way to use delimited input to a load utility? Either IBM or vendor?? We have input data from another platform that is column delimited. I cannot find any mention of this input type in either the IBM V5 Utility Guide or with BMC loadplus 4.2 [...] 13444 43 24_Re: Existance Checks ???14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 11 Nov 1999 03:17:37 +1000638_us-ascii I couldn't agree more.

Existance Checks are really for those situations when the programmer cannot be persuaded out of doing an existance check. Mostly they should not be needed.

From: Michael Hannan

>From: "Clayton, Colin" >Subject: Re: -811 on SELECT statement >To: DB2-L@RYCI.COM > >My comment is related more to the issue of doing existence checks in the >first place. > >If the purpose is only to check for existence of a row before processing it >- why not just do without the existence check completely and handle the ROW >NOT FOUND condition. I assume that in most cases [...] 13488 43 22_Re: Sequence of Cursor9_Mark Ruhe15_MRuhe@QUEST.COM31_Thu, 11 Nov 1999 08:05:19 -0800478_iso-8859-1 The rows would be returned in clustering order but you can't count on anything being 100% clustered unless it was just reorg. So if order matters you need to specify ORDER BY. This assumes the table has insert and update activity.

Mark Ruhe Senior Developer Quest Software

-----Original Message----- From: Scott Lindsey [mailto:sflindsey@HIGHLIGHTS-CORP.COM] Sent: Thursday, November 11, 1999 9:23 AM To: DB2-L@RYCI.COM Subject: Sequence of Cursor [...] 13532 126 33_FW: Stored procedures development13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 11 Nov 1999 10:33:46 -0600552_iso-8859-1 Jaap, I would use the same rules inhouse currently used to write common modules. The SP example is also a common routine, but delivered as an SP. I would say shops have been writing generic modules for years now.

Who develops the SP? ... Again, it depends. If a development team identifies a need for common module (in this case an SP) they may develop it, but hand it over to a department, or another team responsible for common code, if it is a small shop, I would guess the team who developed it will continue to maintain it. [...] 13659 35 26_DB2 courses in the Benelux9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Thu, 11 Nov 1999 17:37:03 +0100329_us-ascii Hi all,

In my shop we have several DBA's who are all hungry for extra knowledge. Can anyone provide me with an overview of all available courses on DB2 and DB2 related courses? I'm also interested in institutions and shops that can provide me with their courses lists (also Tandem(sorry)courses are needed). [...] 13695 15 20_Question on locksize12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM31_Thu, 11 Nov 1999 11:38:58 -0500337_iso-8859-1 Does anyone know what the locksize default is in version 6?

TIA, Andy Wheeler

================================================ 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. 13711 59 39_Re: Loading from a delimited input file18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 11 Nov 1999 10:42:13 -0600655_iso-8859-1 John, Simply ignore the delimiters. Given this input file record: 11,aa,33 where comma is the delimiter, LOAD INTO qualified_tablename col1 position(1:2) smallint, col2 position(4:5) char(02), col3 position(7:8) smallint HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your [...] 13771 52 24_Re: Question on locksize12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Thu, 11 Nov 1999 10:50:29 -0600377_us-ascii Here are the defaults from the manual:

| 1 CROSS MEMORY ===> NO Local storage and cross memory use | | 2 MAXIMUM ECSA ===> 6M Control block storage (1M-999M) | | 3 LOCKS PER TABLE(SPACE)===> 1000 Maximum before lock escalation | | 4 LOCKS PER USER ===> 10000 Maximum before resource unavailable | | 5 DEADLOCK TIME ===> 5 Detection interval in seconds | | [...] 13824 72 18_Stumped outer join13_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 11 Nov 1999 16:52:50 -0000403_iso-8859-1 Esteemed list,

It is a humbling (yet, so I'm told, strong) thing for one to acknowledge one's defeat. Well, I've faced it here. Attached is a left outer join which is incomplete. Somehow, I have to join Z.EFFECTDTE onto :EFFECTDTEP80 in results table X. Yes, I know that "AND :EFFECTDTEP80 BETWEEN A.ACTIVFROM AND A.ACTIVTO" is awful, but I haven't got around to changing it yet. [...] 13897 28 24_Re: Question on locksize20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 11 Nov 1999 11:52:59 -0500542_- LOCKSIZE ANY.

> -----Original Message----- > From: Andy Wheeler [SMTP:Andy.Wheeler@GRIZZARD.COM] > Sent: Thursday, November 11, 1999 11:39 AM > To: DB2-L@RYCI.COM > Subject: Question on locksize > > Does anyone know what the locksize default is in version 6? > > TIA, > Andy Wheeler > > ================================================ > 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. [...] 13926 14 24_Re: Question on locksize12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Thu, 11 Nov 1999 10:52:46 -0600333_us-ascii The manuals are online at:

http://www.s390.ibm.com/bookmgr-cgi/bookmgr.cmd/

================================================ 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. 13941 90 30_Re: DB2 courses in the Benelux9_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Thu, 11 Nov 1999 17:54:27 +0100634_us-ascii Excuse me fellow DB2-Listers, I forgot that I live in a tiny country; the Benelux stands for the countries; Belgium (BE) Netherlands (NE) Luxemburg (LUX)

Regards,

Jaap Slot.



----------------------[Reply - Original Message]----------------------

Sent by:"Arnold, Richard" Jaap,

Please pardon my ignorance, but what is the Benelux?

Richard Arnold DBA Frito Lay Dallas, Texas, USA







"Slot J.P." 11/11/99 10:37 AM To: DB2-L@RYCI.COM@SMTP@Exchange cc: Subject: DB2 courses in the Benelux [...] 14032 152 28_Re: -811 on SELECT statement14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Thu, 11 Nov 1999 12:32:24 -0500592_us-ascii I can't say I agree with the principle that because some condition has been classified by someone as an "error" that it should never be treated as a normal case. This is why MVS provides, for example, the ESPIE service and the ERRET operand on LINK and LOAD.

It's sometimes simpler and more efficient to code for the expected case and handle the unusual "error" case when it arises rather than spend resources (and coding time) anticipating the unusual case. And sometimes there's no choice because no easy means has been provided to anticipate the error ( e.g., how can [...] 14185 45 52_Re: DSN3@SGN security exit from CICS to enable RACF.15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 11 Nov 1999 12:36:42 -0500582_iso-8859-1 Since I did not see any reply, I am just re-sending it again. Please ignore if you received it twice.

> We tried to install security exit routine DSN3@SGN to enable RACF security > checking for CICS transaction. Currently we are using character string in > the AUTH parameter of RCT entry. But this character string is defined to > RACF as group without any user-id associated with it(Is this a problem?). > We need to implement one transaction with AUTH=GROUP, but others should > work as it is earlier. So we thought of using DSN3@SGN and implemented it. > [...] 14231 84 39_Re: Loading from a delimited input file14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 11 Nov 1999 12:43:21 -0500652_iso-8859-1 I thought in a delimited file the location of the delimiter may not be the same for all rows. Please correct me if I'm wrong.

Manas.





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > DAVIS, RICK (SWBT) > Sent: Thursday, November 11, 1999 11:42 AM > To: DB2-L@RYCI.COM > Subject: Re: Loading from a delimited input file > > > John, > Simply ignore the delimiters. > Given this input file record: 11,aa,33 where comma is the > delimiter, > LOAD INTO qualified_tablename > col1 position(1:2) smallint, > col2 position(4:5) char(02), > col3 position(7:8) [...] 14316 81 24_Re: Existance Checks ???14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Thu, 11 Nov 1999 12:51:02 -0500615_us-ascii An existence check is most usually appropriate when significant resources that are not required for the null case must be expended before the first access attempt, or where actions that are inappropriate for the null case must be executed before the first access attempt. For example, if a program needs to open a file or a remote connection only when there is data to send, an existence check is the cleanest method. (An alternate approach might be to use a "priming read", but this produces ugly, hard-to-maintain structure where the first execution of a loop's input operation is arbitrarily split [...] 14398 110 39_Re: Loading from a delimited input file18_DAVIS, RICK (SWBT)21_RD8246@MOMAIL.SBC.COM31_Thu, 11 Nov 1999 12:04:42 -0600640_iso-8859-1 Manas, Woops, I did figure the unload utility, or what ever they're using, would handle variable length columns/fields similar to DSNTIAUL. Thanks, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, [...] 14509 80 52_Re: DSN3@SGN security exit from CICS to enable RACF.15_Webster, Murray30_Murray.Webster@CCRA-ADRC.GC.CA31_Thu, 11 Nov 1999 13:52:00 -0500483_iso-8859-1 We are a TSS user and had to modify the source for DSN3SSGN supplied by IBM to allow a mixture of AUTH=GROUP and AUTH=userid in the RCT. Basically, the exit as written was not picking up the list of secondary authids under these circumstances. We made this change when we were at DB2 V2.3 and still require it with DB2 V5. I had assumed at the time that it was a TSS problem but if you are having the same problem with RACF, it might be something to pursue with IBM. [...] 14590 87 39_Re: Loading from a delimited input file13_John Arbogast16_jfarbo@YAHOO.COM31_Thu, 11 Nov 1999 11:21:37 -0800606_us-ascii I forgot to mention there are VARCHAR columns....

--- "DAVIS, RICK (SWBT)" wrote: > John, > Simply ignore the delimiters. > Given this input file record: 11,aa,33 where > comma is the delimiter, > LOAD INTO qualified_tablename > col1 position(1:2) smallint, > col2 position(4:5) char(02), > col3 position(7:8) smallint > HTH, > Rick Davis > "This e-mail and any files transmitted with it are > the property of SBC, > are confidential, and are intended solely for the > use of the individual > or entity to whom this e-mail is addressed. If you > are not one of [...] 14678 115 39_Re: Loading from a delimited input file13_John Arbogast16_jfarbo@YAHOO.COM31_Thu, 11 Nov 1999 11:26:32 -0800553_us-ascii Yes, there are VARCHARs involved.

--- Manas Dasgupta wrote: > I thought in a delimited file the location of the > delimiter may not be the > same for all rows. Please correct me if I'm wrong. > > Manas. > > > > > -----Original Message----- > > From: DB2 Data Base Discussion List > [mailto:DB2-L@RYCI.COM]On Behalf Of > > DAVIS, RICK (SWBT) > > Sent: Thursday, November 11, 1999 11:42 AM > > To: DB2-L@RYCI.COM > > Subject: Re: Loading from a delimited input file > > > > > > John, > > Simply ignore the [...] 14794 30 16_Re: Rename table14_Max 'Guderian'16_mocion@LIBERO.IT31_Thu, 11 Nov 1999 21:21:51 +0100622_iso-8859-1 I made a somewhat similar work:

1) new tables in the same tablespace, same table name but a different owner. It was a our requirement , but you can create new tablespaces and database + tables using LIKE in CREATE TABLE ; somewhat similar to owner.tableyyy_new. 2) Load of all new tables (if few rows insert into select * from) (2a) rebind all packages with new owner. in our shop) 3) Rename table owner.tableyyy to owner.tableyyy_old *** all package invalidated !! No application online !! *** 4) rename owner.tableyyy_new to owner.tableyyy 5) rebind all package. Old table remains accessible with [...] 14825 47 12_Federated DB16_salhany, michael23_salhany_michael@EMC.COM31_Thu, 11 Nov 1999 15:58:39 -0500139_iso-8859-1 I just installed DB2 UDB V6 on MVS and was wondering if anyone had any experience setting up a Federated DB?

Any gotchas? 14873 14 16_Re: Federated DB0_21_william.poston@DB.COM31_Thu, 11 Nov 1999 15:17:48 -0600418_us-ascii Could some please explain to me how RACF security works for DB2 CICS tranctions using plans... specifically defining how AUTH values work on RCT Entry Macro Parameters

================================================ 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. 14888 97 39_Re: Loading from a delimited input file13_Leo Conchello18_lconche@USWEST.COM31_Thu, 11 Nov 1999 14:29:13 -0700272_us-ascii John,

I had the same problem a few weeks ago and I couldn't find any utility to do it. I used SAS to reformat and then used a standard DB2 Load utility. If you have SAS, here is an example that assumes 5 columns, all converting to a fix length of 20: [...] 14986 22 20_Compression Checking15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 11 Nov 1999 16:35:10 -0500363_iso-8859-1 If PAGESAVE = 0 in sysibm.systablepart and PCTROWCOMP=100 in sysibm.systables, what is this means. The RUNSTATS is current and hardware compression is used on the tablespace. Is there any utility like DSN1COMP(This only work if the table space is not compressed) to check the compression we got is correct or not without re-orging the tablespace. [...] 15009 22 5_Virus14_Philip Gunning20_pgunning@BOSCOVS.COM31_Thu, 11 Nov 1999 17:10:40 -0800536_us-ascii I have deleted the subscription for the subscriber that accidentally sent the virus in a post to the list. He is installing VIRUS detection software on his pc. We should be back to normal list operation. Regards, Phil

Cheers!



Phil Gunning DB2 DBA Assoc List Owner

================================================ 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. 15032 37 24_Re: Compression Checking10_Leo Flores23_leoflores@EARTHLINK.NET31_Thu, 11 Nov 1999 02:36:20 -0800610_us-ascii Sibimon,

Has a REORG or a LOAD been run after the COMPRESS=YES alter was performed? I believe this is the only way the data can be compressed. After this then run RUNSTATS again and you should see the changes in PAGESAVE.

HTH Leo Flores

Philip, Sibimon wrote:

> If PAGESAVE = 0 in sysibm.systablepart and PCTROWCOMP=100 in > sysibm.systables, what is this means. The RUNSTATS is current and hardware > compression is used on the tablespace. Is there any utility like > DSN1COMP(This only work if the table space is not compressed) to check the > compression we got is [...] 15070 62 24_Re: Compression Checking15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Thu, 11 Nov 1999 17:41:42 -0500450_iso-8859-1 We have created this tablespace with compress=Y. We partitioned one time and unload and loaded this table for many field size changes.

Thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com



-----Original Message----- From: Leo Flores [mailto:leoflores@EARTHLINK.NET] Sent: Thursday, November 11, 1999 4:36 AM To: DB2-L@RYCI.COM Subject: Re: Compression Checking [...] 15133 19 19_Re: RCT Entry Macro0_21_william.poston@DB.COM31_Thu, 11 Nov 1999 16:58:28 -0600517_us-ascii Could someone please explain to me how RACF security works for DB2 CICS tranctions using plans... specifically defining how AUTH values work on RCT Entry Macro Parameters

Also can you give examples of when you would favor one choice over another

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. 15153 31 28_Conversion from SUPRA to DB20_20_gabriele@NZ1.IBM.COM31_Fri, 12 Nov 1999 13:14:52 +1300531_us-ascii Hi,

I am looking for contacts, suggestions, tips or experiences in converting data and application from SUPRA database and MANTIS application environment to DB2 and CICS on OS/390 or MVS.

Any info will be much appreciated.

Thanks in advance

Kind regards Gabriele Gambassi ------------------------------------------------------- IBM Global Service Strategic Outsourcing - New Zealand

Phone: (+64 9) 359 8875 Fax: (+64 9) 359 8833 Mobile: (+64) 021 305 100 Mail: gabriele@nz1.ibm.com [...] 15185 19 32_MODIFY SYSLGRNG IN DB2 OS/390 V522_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM29_Thu, 11 Nov 1999 15:11:13 PST307_- As I recall, version 5 of DB2 UDB for OS/390 retains a toleration of DSNDB01.SYSLGRNG for data sharing environments. V4 utility information may be visible from a V5 system (via a retained SYSUTIL record) so SYSLGRNG must be known to V5. The MODIFY behavior, and a couple of others, are side affects. [...] 15205 26 16_DB2 V5 Migration13_Myoungsoo Huh18_huhm@FARMERS.CO.NZ31_Fri, 12 Nov 1999 13:38:41 +1300500_us-ascii Hi all , I'm planing for upgrading from DB2 v3 to DB2 v5. In DB2 v5 migration says that DB2 v4 is the only release from which you can migrate to DB2 for OS/390 V5. If you try to migrate from a release other than DB2 MVS/ESA V4 , unpredictable results can occur after migration. Should I migrate DB2 V4 first , and migrate again from V4 to V5 or are there any short cut or bypassing db2 v4 for migration v5 ? Anyone has any ideas of it or any good recommendation ? ( Sys env : OS390 2.4 [...] 15232 82 22_Re: Sequence of Cursor14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 12 Nov 1999 10:00:12 +1000466_us-ascii If you had just REORGed but the SELECT was being processed using parallel I/O you couldn't guarantee the order. Bottom line, as Mark points out, if order matters you MUST use and ORDER BY.

Paul. ---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 12/11/99 10:00 ---------------------------



Mark Ruhe on 12/11/99 02:05:19

Please respond to DB2 Data Base Discussion List [...] 15315 67 40_Deciphering Hex representation of FLOAT.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 12 Nov 1999 12:10:27 +1000513_us-ascii Hi Folks, There was a small discussion a while back on internal/external representations of the FLOAT datatype. I posted how I believed they were stored and seemed to get a general agreement and was happy that I fully understood it. There was another post to this list recently with regard this topic and I thought I'd help them offline as it would be no trouble to solve the problem....well now I'm confused as well and am hoping someone else can explain it for my understanding and their problem. [...] 15383 93 28_Re: -811 on SELECT statement11_Suresh Sane21_data_arch@HOTMAIL.COM29_Thu, 11 Nov 1999 20:48:37 CST373_- I have to agree with Michael. This has been discussed several times before and I covered it in my presentation at IDUG San Francisco - showing actual benchmarks. -811 may not be the best in all cases but to have a shop standard that diallows it is archaic.

Reagrding the data retured: beware that host variables may or may not contain values in case of -811. [...] 15477 54 39_Re: Loading from a delimited input file0_24_ssethi@LOT.TATASTEEL.COM31_Fri, 12 Nov 1999 09:19:07 +0530291_us-ascii Hi , I think you can do this if u design your punch like that either manually or write some tools to check the position of the end of any column from SYSIBM.SYSCOLUMNS i.e by knowing the length of the column and the same punch can be used to load the data in the usual manner. [...] 15532 53 39_Re: Loading from a delimited input file0_24_ssethi@LOT.TATASTEEL.COM31_Fri, 12 Nov 1999 09:19:36 +0530291_us-ascii Hi , I think you can do this if u design your punch like that either manually or write some tools to check the position of the end of any column from SYSIBM.SYSCOLUMNS i.e by knowing the length of the column and the same punch can be used to load the data in the usual manner. [...] 15586 37 16_Re: Table Rename13_Marcel Lepage13_iml@COLBA.NET31_Thu, 11 Nov 1999 23:07:11 -0700551_iso-8859-1 Hi,

I did use rename table facilities and I find it very useful for something similar to what you want to do and I've never had any problem.

Don't be shy, use it.

Marcel Lepage DBSYS Technologies Inc.

"Mastan, Shabbir (S.)" a écrit :

> We have a DB2 (ver 5) system on OS/390 which needs to be up and running > while we load up data from an Oracle database. One of the avenues we are > exploring is the DB2 table rename facility. We would have mirror tables on > the DB2 system and load up the mirror [...] 15624 84 24_Re: Compression Checking10_Leo Flores23_leoflores@EARTHLINK.NET31_Thu, 11 Nov 1999 22:55:48 -0800550_us-ascii Sorry Sibimon, I was confusing the PCTROWCOMP Column with the COMPRESS Column.

Maybe this is what may be happening. You are compressing 100% of the rows, BUT, there is no savings in the NUMBER OF PAGES required to store these rows. The DB2 dataset requires a minimum amount of tracks(depending on segsize and priqty) and all of the compressed rows and the DICTIONARY fit in this allocation. When more rows are inserted to the table, then maybe you will begin to see a savings in the NUMBER of PAGES required to store these rows. [...] 15709 101 24_Re: Compression Checking14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Fri, 12 Nov 1999 17:52:49 +1000334_us-ascii Sibimon

It is possible that after compression there is not enough additional free space on any page to insert any more rows than can fit if the rows were uncompressed. (eg, if you had 4000 byte rows and 40% compression, the compressed length is 2400 bytes and there still isn't enough room for 2 rows in a page.) [...] 15811 75 20_Re: DB2 V5 Migration14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Fri, 12 Nov 1999 18:18:41 +1000542_us-ascii Myoungsoo

Don't try to install V5 over V3. There are just too many things to go wrong.

The only way of skipping V4 is to:-

- install a clean V5 - run all DDL sql - either DSN1COPY/XLATE or Unload/Reload data from V3 to V5 system, rebuilding indexes - Rebind all plans and packages. You can bind off the source DBRMs or, if using packages and you have a DDF link from the V3 system, BIND COPY. There was a posting a few weeks ago about a REXX which would rebuild the source DBRM from the catalog - check the [...] 15887 111 44_Re: Deciphering Hex representation of FLOAT.14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Fri, 12 Nov 1999 18:48:28 +1000300_us-ascii Paul,

0.233000001E+39 = 0.684725459354 * (16 ** 32) (( I think this is the step you forgot)) = (10 * (1/16) + 15 * (1/16**2) + 4 * (1/16**3) + 10 (1/16**4) + ... ) * 16 ** (96-64) = (x'A' * (1/16) + x'F' * (1/16**2) + x'4' * (1/16**3) + x'A' (1/16**4) + ... ) * 16 ** (x'60'-64) [...] 15999 76 32_Re: Conversion from SUPRA to DB213_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 12 Nov 1999 09:07:27 -0000527_iso-8859-1 Hi Gabriele,

What do you want to know, specifically? I was a Supra and DB2 DBA and Systems Programmer for a while, where DB2 was their strategic platform and Supra their legacy platform.

Certain Supra files were mapped one-to-one onto DB2 tables, and loaded directly from the BSAM file. Others had programs to unload Supra into the IBM DB2 LOAD utility. For a while we used CICS transaction routing to get data from one (DB2 or Supra) region to another (Supra or DB2) during the transition phase. [...] 16076 15 0_12_Guru, Bala X17_bala.guru@EDS.COM31_Fri, 12 Nov 1999 04:17:40 -0500398_- P.BALAGURU, EDS -Electronic Data Systems (India) Pvt. Ltd. Chennai, India * 91-44-8213801 : Ext. No. - 2173 * bala.guru@eds.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. 16092 141 16_Re: OAM Problems0_18_mebert@AMADEUS.NET31_Fri, 12 Nov 1999 11:04:49 +0100585_us-ascii Hi,

thanks to Ed for getting me started in the right direction. I found some things, though our documentation seems to be somewhat out of date. It seems there is a variety of problems: Once the job failed with OAM RC=0C, Reason=6C030B66. I could not find this reasoncode in our docu, but in the OAM STC JESMSGLG there are entries saying that at this time, the CDROM Jukebox was in CE Maintenance mode. One failure gave RC=0C, Reason=6C030E05. In the manual I only found RC=10 with this reasoncode. I assume the RC was changed. In any case, the description matches [...] 16234 102 39_Re: Loading from a delimited input file14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Fri, 12 Nov 1999 08:16:59 -0500613_us-ascii Or if you don't have SAS - you could write a REXX to Parse the input dataset and write an output dataset in the DB2 load format. Something along the lines of: **************** /* REXX */ .. inputd = 'comma.dilimtd.file' "alloc fi(inputd) da('"||inputd||"') shr" /* allocate input file */ "alloc fi(outputd) da('"||outputd||"') shr" /* allocate output file */ ... "execio * diskr inputd (stem recin. finis" /* the above stmt. places coma delimited records into stem variables for further processing */ ... do i = 1 to dsnsin.0 parse var recin.i flds1.i ',' fld2.i ',' fld3.i ',' fld4.i '.' ...fldn.i [...] 16337 96 45_DYNcache db2/os390 v5.1 with powerbuilder 6.515_Pierre Dagenais19_pdagenai@UOTTAWA.CA31_Fri, 12 Nov 1999 08:32:06 -0500504_us-ascii Hi everyone,

We have just migrated from db2 v4.1 to db2v5.1 put tape 9901. We have turned on dyncache. Unfortunately , It seems from the statistic below that we are not avoiding any prepare of sql statements. the application is written in powerbuilder 6.5.

Is this situation normal? In the present situation is dyncache worth it? Is there something we can do in the powerbuilder program to avoid preparing an sql statements? Is there another site experiencing the same thing? [...] 16434 62 20_Re: DB2 V5 Migration50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Fri, 12 Nov 1999 09:04:55 -0500335_- I migrated from 3 to 4, in (4) subsystems. Took 6+ months. Each subsystem migration should be verified and tested to give some time for shaking out problems. The v4 to v5 in (4) subsystems in 6 months.

Iff you go from 3 to 5 Think of the following that must be done just for 3 to 4 migration that you possibly will miss. [...] 16497 54 20_Re: DB2 V5 Migration14_Marg Gozdowski27_marg.gozdowski@LA-Z-BOY.COM31_Fri, 12 Nov 1999 09:55:31 -0500529_iso-8859-1 I went from db2 3.1 to 4.1 to 5.1 in production last January. I installed 4.1 & 5.1 on different smpe files. I ran through the install & ivps for 4.1 using 5.1's erly code & right after that, installed 5.1 & ran ivps & rebound all plans & then asked others to test the system (different lpar than production). The only problems that I remember having were with adjusting the buffers & there was a problem with one of the 4.1 ivp jobs. It was recommended to test 4.1 for awhile before going to 5.1, but, I did not [...] 16552 110 22_Re: Stumped outer join17_Linda F. Claussen18_lindafc@NETINS.NET31_Fri, 12 Nov 1999 08:56:32 -0600514_ISO-8859-1 Marcus,

Why don't you put :EFFECTDTEP80 in the select list that builds X and assign it a corr-ic?

ie. SELECT A.ADDRREF, ....., :EFFECTDTEP80 AS EFFCTDT .....)) X ON Z.CLIENT = X.CLIENT AND Z.COMPANY = X.COMPANY AND Z.EFFECTDTE = X.EFFCTDT

I hope this is what you were looking for.

Cheers,

Linda F. Claussen Claussen & Associates DB2 Consulting and Training lindafc@netins.net http://www.netins.net/showcase/lclaussen/ Phone: (319) 343-3216 Office: (319) 847-1985 [...] 16663 13 56_Re: DB2-L Digest - 11 Nov 1999 to 12 Nov 1999 (#1999-39)14_Donovan, Peggy22_peggy.donovan@SSMB.COM31_Fri, 12 Nov 1999 09:43:45 -0500318_- virus detected in happy99.exe Peggy Donovan Database Support (212)723-2472

================================================ 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. 16677 15 16_virus in happy9914_Donovan, Peggy22_peggy.donovan@SSMB.COM31_Fri, 12 Nov 1999 09:53:19 -0500330_- > virus detected in happy99.exe > Peggy Donovan > Database Support > (212)723-2472 > >

================================================ 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. 16693 27 62_DB2 UDB DBA Contract Opportunity - NYC ( 8 mo. to 1 year min.)0_18_jtulman@MAAINC.COM31_Fri, 12 Nov 1999 10:48:46 -0500531_us-ascii Ladies and Gentlemen, A client of mine located in the New York City area is looking for a highly qualified UDB DB2 DBA for a minimum of 8 months on a consulting basis. The client is looking for this person to help them migrate from DB2 Common Server to DB2 UDB ver 5.2 on the RS/6000. Experience with SQL and EEE as well as Certification from IBM would be a tremendous plus, but not required. Client is looking for this person to start ASAP. My firm can work with both Corp. to Corp. as well as with W2 employees. We [...] 16721 32 76_Is there anyway to tell the order of Loading 255 tables with RI into DB2/UDB16_Christine Sawyer20_cosawyer@HOTMAIL.COM29_Fri, 12 Nov 1999 10:58:38 EST445_- Hello everyone,

I am relatively new to DB2. We run DB2 OS/390 and DB2/UDB. I have a UDB Database with about 255 tables with a pretty good web of RI among these tables. I want to determine the order in which these tables can be loaded in one clean sweep. I tried to use a rexx code written by a long-gone member of our shop and I find that some of the tables are loaded and the message file for some reveals a -530 SQLCODE problem. [...] 16754 85 20_Re: DB2 V5 Migration11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM31_Fri, 12 Nov 1999 11:34:21 -0800329_- We went from 2.3 to 3.1 to 4.1 to 5.1 in one shot. No problem whatsoever. All DB2 packs were backed up using DFDSS for fall back, if required. Users were given 4 hrs to test everything in production for a go/no-go decision. After that, every problem would be resolved as a production problem. We did not have any problem. [...] 16840 169 80_Re: Is there anyway to tell the order of Loading 255 tables with RI into DB2/UDB0_19_Tim.Lowe@STPAUL.COM31_Fri, 12 Nov 1999 10:37:59 -0600580_us-ascii Christine, I am assuming that you know that you should load the parent tables before the child tables, otherwise you would need to run the checkdata utility.

With that assumption, and also assuming that your 255 tables are not "nested" too deep in referential integrity, I offer you this query that has helped me determine the proper order. I offer nobody any guarantees that this would work for them, but it does work for me in most cases. If I get any 'level 5' tablespaces, then I check to ensure that these tablespaces have no children. (If the number of [...] 17010 19 21_Net.Data and SCO Unix11_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Fri, 12 Nov 1999 12:13:16 -0500469_us-ascii Please respond privately or to the list if you have any experience running Net.Data on SCO UnixWare opsys. We have an IVR application which may benefit from Net.Data but we have no experience using the product, much less with SCO UnixWare. Thanks in advance.

Joe Luthman DBA Information Technology Services Phone: (419)372-7750 260 Hayes Hall FAX: (419)372-7723 Bowling Green State University E-Mail: jluthma@bgnet.bgsu.edu Bowling Green, OH 43403 [...] 17030 51 16_Re: Rename table11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM31_Fri, 12 Nov 1999 11:40:38 -0800438_- Max,

If you creat new table in the same tablespace and load it, doesn't the old table become unavailable during the load and afterwards due to copy pending status on the tablespace.

Sanjay Jain Pro Sol Inc.

-----Original Message----- From: Max 'Guderian' Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Thursday, November 11, 1999 12:39 PM Subject: Re: Rename table [...] 17082 33 51_LOAD utility running with DSNUTILS stored procedure8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Fri, 12 Nov 1999 11:04:39 -0600622_us-ascii Hello List members,

Thanks to Venkat for suggesting the DSNUTILS stored procedure for running utilities from different subsystems.

Now i have a different issue. Our shop wants to load mass data to different partitions, say 50 part. Our batch window is very short to fit sequentially. We need to control our LOAD utility jobs. This means we don't want to check each job out put. We have multiple input datasets sorted according to the partition and information's about this datasets are available in another dataset. I need to start this LOAD activity by triggering from a program and write the [...] 17116 66 20_Re: DB2 V5 Migration16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Fri, 12 Nov 1999 10:34:34 -0700536_- You could bypass going to DB2 v4 if you follow a scenario similar to this.

Install DB2 v5 Do imagecopy of the data in the DB2 v3 system Extract (unless you already have it somewhere) the SQL required to create the DB2 objects (stogroups, databases, tablespaces, tables, indexes, etc.) Create the DB2 objects in the DB2 v5 system that are to be populated with the data from the DB2 v3 system. Use DSN1COPY to populate the new tablespaces with the data from the imagecopy taken above. Recover the indexes for all the tables [...] 17183 115 39_Re: Loading from a delimited input file13_Leo Conchello18_lconche@USWEST.COM31_Fri, 12 Nov 1999 11:23:49 -0700435_us-ascii Be careful using REXX, before using SAS to reformat the files, I wrote a similar REXX to what Bill suggests. Once you go over a few thousand rows, you may end using lots of CPU and your elapsed time may be up to 40 times higher. In my case, one of the tables had 12,500,000 million rows. Using REXX in batch took hours as opposed to SAS that took only about 12 minutes. Easytrieve may also provide similar functionality. [...] 17299 24 27_DO NOT USE/OPEN HAPPY99.EXE16_Richard A Yevich15_ryevich@IBM.NET31_Fri, 12 Nov 1999 13:28:49 -0500345_iso-8859-1 There was a program attached to a message on Thursday, called HAPPY99.EXE! It contains a VIRUS and will potentially affect your systems. This has happened to one corporation already that is having to shut down it's mail server.

I am sure more details will follow, but as a general practice, do not open blind attachments! [...] 17324 50 39_Mergecopy Utility and Disaster Recovery0_22_BILL_GALLAGHER@PHL.COM31_Fri, 12 Nov 1999 14:47:40 -0500477_us-ascii Hi,

I'm looking at some potential solutions for shortening the batch window of one of our larger applications. Currently, the last couple of jobs of their batch cycle consists of full image copies (local and recovery site), followed by a quiesce of the entire database (157 tablespaces). They are currently running three concurrent image copy jobs, each doing about a third of the tablespaces, and this whole procedure takes around 1 1/2 hours each night. [...] 17375 136 33_Re: Strange access path selection20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 12 Nov 1999 14:52:02 -0500410_iso-8859-1 Mike

I happen to have a similar case so I looked back at your email again. With a little careful review I realised why db2 is choosing that path.

Q.1 ->Why is DB2 not picking TI1CAFAC index despite index only access ? Ans -> you are updating column TIME_STAMP, which part of index TI4CAFAC. This means that - if DB2 is forced to choose TI1CAFAC then # of pages visited would be - [...] 17512 67 16_Re: Rename table14_Max 'Guderian'16_mocion@LIBERO.IT31_Fri, 12 Nov 1999 20:58:38 +0100318_us-ascii No if you use LOG YES.....You do not need an Image copy. Of course load is longer, but usually it's not a constraint in such a type data administration. You may need, for safe, a low transaction activity But it's not real necessary to create a table in the same tablespace, it was a request in OUR shop. [...] 17580 287 7_missing13_Purnima Patel18_ppatel@METLIFE.COM31_Fri, 12 Nov 1999 15:16:43 -0500361_us-ascii





I hope I have covered enough East Coast, Midwest, and West Coast people to spreadout the search for this little girl. It only takes 2 seconds to "forward" this on. If it was your child, you would want all the help you could get. Please. I am asking you all, begging you to please forward this email to anyone and everyone. [...] 17868 96 43_Re: Mergecopy Utility and Disaster Recovery20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 12 Nov 1999 15:21:59 -0500494_- Bill

Mergecopy only helps recover faster. Without mergecopy DB2 recovery takes a bit longer but if you keep the volumes mounted then this should not be too much. If recovery site has full imagecopy and all subsequent incremental imagecopies available then everything is fine.

I have large tablespaces and everyday full imagecopy is impossible. I take full copy once a week and incremental every day. These copies are xmitted to remote site each day. I don't use mergecopy. [...] 17965 316 11_Re: missing12_Janis Thomas20_thomasj@PLATINUM.COM31_Fri, 12 Nov 1999 14:34:46 -0600514_us-ascii Please ignore this email, it is not true:

http://urbanlegends.miningco.com/culture/beliefs/urbanlegends/library/blmiss3. htm

Whether true or not, this was an off topic post, and should not have posted to DB2-L, according to our FAQ. Also, attachments are not allowed either.

Janis Thomas Associate list owner









Purnima Patel on 11/12/99 02:16:43 PM

Please respond to DB2 Data Base Discussion List [...] 18282 88 20_Re: DB2 V5 Migration11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM31_Fri, 12 Nov 1999 16:33:07 -0800461_- Duane,

Its a good strategy if the database is small. For large shops where database could be 100s of GB to Terrabytes, moving data will require enormous down time, excluding the (unlikely ) time to fall back.

Sanjay Jain

-----Original Message----- From: Duane Lee - ATCX Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Friday, November 12, 1999 9:56 AM Subject: Re: DB2 V5 Migration [...] 18371 87 43_Re: Mergecopy Utility and Disaster Recovery11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM31_Fri, 12 Nov 1999 17:01:45 -0800549_- Bill,

This is from IBM manual verbatim. "You can also use MERGECOPY RECOVERYDDN to create recovery site full copies, and merge local incrementals into new recovery site full copies."

Seems to me that you can use local copies to create recovery site full copy.

HTH

Sanjay Jain -----Original Message----- From: BILL_GALLAGHER@PHL.COM Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Friday, November 12, 1999 11:49 AM Subject: Mergecopy Utility and Disaster Recovery [...] 18459 41 24_Re: Question on locksize8_georgewu27_george.wu@CHINATRUST.COM.TW31_Sat, 13 Nov 1999 12:11:20 +0800735_iso-8859-1 Hi SQL reference volume 2 (v6) page 558 create tablespace ....locksize ,but page 568 locksize any,tablespace,table,page,row,lob not default value written .

George Wu george.wu@chinatrust.com.tw





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On > Behalf Of > Andy Wheeler > Sent: Friday, November 12, 1999 12:39 AM > To: DB2-L@RYCI.COM > Subject: Question on locksize > > > Does anyone know what the locksize default is in version 6? > > TIA, > Andy Wheeler > > ================================================ > To change your subscription options or to cancel your > subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners [...] 18501 115 43_Re: Mergecopy Utility and Disaster Recovery10_Ven Ilagan18_vilagan@ONE.NET.AU31_Sat, 13 Nov 1999 15:22:29 +1100639_us-ascii Like Vencat, we use incrementals too....I wrote some Rexx to gen the recovery JCL to keep the full & incremental tapes mounted (with unit=aff=...) using the volser of recoverysites as read from Syscopy.

We do MWF as incrementals and the rest of the week as Full.

HTH, Ven Ilagan



At 15:21 12/11/99 -0500, Pillay, Venkat (PCA) wrote: >Bill > > Mergecopy only helps recover faster. Without mergecopy DB2 recovery >takes a bit longer but if you keep the volumes mounted then this should not >be too much. If recovery site has full imagecopy and all subsequent >incremental imagecopies available then [...] 18617 110 15_Re: CRUD Matrix0_28_brian.p.fay@BELLATLANTIC.COM31_Sat, 13 Nov 1999 09:59:07 -0500366_us-ascii John,

I'm a retired DBA and I remember using Platinum RC/Query to get this info also. Let's see if I can remember.

1) go to Packages. 2) go to Tables. Fill in the CollectionID/Package name or wildcard them. I believe this should do it. You may want to use the EXPANDED QUERY option to handle any sorting or other custom options you want. [...] 18728 128 22_Re: Stumped outer join14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sun, 14 Nov 1999 08:18:57 +1000666_us-ascii Linda is right but I would alter slightly to guarantee matching datatype:

SELECT A.ADDRREF, ....., DATE(:EFFECTDTEP80) AS EFFCTDT

Don't forget to fix your BETWEEN predicates to stage 1 while you are about it if they do significant filtering.

From: Michael Hannan

>From: "Linda F. Claussen" >Subject: Re: Stumped outer join >To: DB2-L@RYCI.COM > >Marcus, > >Why don't you put :EFFECTDTEP80 in the select list that builds X and assign >it a corr-ic? > >ie. SELECT A.ADDRREF, ....., :EFFECTDTEP80 AS EFFCTDT > .....)) X > ON Z.CLIENT = X.CLIENT AND Z.COMPANY = X.COMPANY > AND Z.EFFECTDTE = X.EFFCTDT > >I [...] 18857 145 44_Re: Deciphering Hex representation of FLOAT.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Mon, 15 Nov 1999 09:15:20 +1000744_us-ascii James, Thanks very much for that.

Paul. ---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 15/11/99 09:17 ---------------------------



Campbell James on 12/11/99 18:48:28

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Paul A Redhead/CorpServ/qdot/au) Subject: Re: Deciphering Hex representation of FLOAT.







Paul,

0.233000001E+39 = 0.684725459354 * (16 ** 32) (( I think this is the step you forgot)) = (10 * (1/16) + 15 * (1/16**2) + 4 * (1/16**3) + 10 (1/16**4) + ... ) * 16 ** (96-64) = (x'A' * (1/16) + x'F' * (1/16**2) + x'4' * (1/16**3) + x'A' (1/16**4) [...] 19003 57 66_Re: DB2 UDB DBA Contract Opportunity - NYC ( 8 mo. to 1 year min.)13_Charlie Duffy23_charlie_duffy@YAHOO.COM31_Sun, 14 Nov 1999 16:57:12 -0800542_us-ascii Thanks for contacting me regarding this opportunity - sounds tempting but I am planning to stay local for now. Thanks, Charlie Duffy

--- jtulman@MAAINC.COM wrote: > Ladies and Gentlemen, > A client of mine located in the New York City > area is looking for a highly > qualified UDB DB2 DBA for a minimum of 8 months on a > consulting basis. The > client is looking for this person to help them > migrate from DB2 Common Server to > DB2 UDB ver 5.2 on the RS/6000. Experience with SQL > and EEE as well as > Certification [...] 19061 13 51_LOAD utility running with DSNUTILS stored procedure22_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM29_Sun, 14 Nov 1999 19:27:49 PST395_- No, DSNUTILS runs *ONE* utility control statement at a time. To get all 50 partitions done in the shortest clock time, run 50 separate jobs.

Dan

================================================ 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. 19075 24 24_Waiting for DB2 Services22_Dan Courter (543-3870)27_daniel_courter@VNET.IBM.COM29_Sun, 14 Nov 1999 19:39:03 PST346_- Last week I saw a couple of list members mention DB2 Utilities experiencing some type of "wait" for DB2 Services. Nothing showed up on -DIS UTIL but did who up on -DIS THREAD.

Could someone give me more information about what specific "wait" is being seen and where or how it is seen? What message or state is being seen and where? [...] 19100 37 56_Re: DB2-L Digest - 13 Nov 1999 to 14 Nov 1999 (#1999-41)19_Karthik Subramaniam26_karthik.subramaniam@DB.COM31_Mon, 15 Nov 1999 09:16:25 +0530608_us-ascii Hi all.... I am involved in developing an online application involving CICS, VS-COBOL II and DB2. Further it involves Dynamic SQLs to process queries from the CICS screen. I had no problems in displaying CHAR, TIMESTAMP,VARCHAR except for data types DECIMAL(m,n) since they require a corresponding s9(m)v(n) comp-3 declarations for recieving them. I can somehow solve the problem of deciding upon the length ie. the values of m and n, but I am finding it difficult to convert them to corresponding values to X(m+n) so that it can be diplayed on the CICS screen. Can anybody throw some light on [...]