1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2000, week 3 2 73 11_DB2 connect8_Tam Tran18_ttran@LASERCOM.COM31_Fri, 15 Dec 2000 09:34:59 +0100352_us-ascii Hello DB2 Experts

About 2 weeks ago, I asked about DB2 Connect without a satisfied reply. Now I try again and hope a good reply for Chrismas and new year.

Here is our problem :

A DB2 session (i.e. DB2 client software on Client talking to the DB2 database on Server) fails if it is idle for a long time (maybe a day). [...] 76 20 13_Assembly code11_Manish Lall25_Manish.Lall@IN.EFUNDS.COM31_Fri, 15 Dec 2000 15:15:49 +0530451_us-ascii Hello,

Can any body send me an assembly program which has static sql embedded in it. We have seen the code of DSNTIAD and DSNTIAUL , but anything beyond that .

Thanks in advance , Manish

================================================ 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. 97 150 17_AW: Assembly code12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 15 Dec 2000 10:56:07 +0100912_iso-8859-1 Manish,

I assume you are searching an assembler program with embedded SQL. Below is an excellent example that I have received from ISAAC YASSIN some time ago...........

MEMBER TMSTMP ================== TITLE 'SQL PROGRAM' ********************************************************************** * WRITTEN BY: ISAAC YASSIN (C) - 1993. * ********************************************************************** * DSNTIACN * EJECT * ********************************************************************** * INITIALIZE DATA BEFORE PROCESSING, HOUSEKEEPING * ********************************************************************** * TMSTMP CSECT CONTROL SECTION NAME SAVE (14,12) ANY SAVE SEQUENCE LR R12,R15 CODE ADDRESSABILITY USING TMSTMP,R12 TELL THE ASSEMBLER * L R5,4(,R1) R5 ON OUTPUT PARMS ADDRESS ST R5,OUTADDR OUTPUT AREA ADDRESS L R6,PROGSIZ GET SPACE FOR USER PROGRAM A R6,SQLDSIZ [...] 248 78 34_Re: Xpediter and Stored Procedures15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 13 Dec 2000 16:17:37 -0500388_- Same with us too. Cost seems to be much higher than what IBM is offering, if it is 13K. We thought of using it for trail sometime to see all features we want are there.





-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Wednesday, December 13, 2000 02:32 PM To: DB2-L@RYCI.COM Subject: Re: Xpediter and Stored Procedures [...] 327 105 44_Re: FW: Evaluating CA/Platinum DB2 Utilities14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 15 Dec 2000 10:26:31 -0000455_iso-8859-1 No worries Linda,

I just wondered if you still had a copy handy. Even though it'd be pretty out of date, it would still have been useful to make sure we are still not missing anything

Thanks anyway though

Phil G

-----Original Message----- From: Billings, Linda [mailto:linda.billings@DOA.STATE.WI.US] Sent: 14 December 2000 17:13 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] FW: Evaluating CA/Platinum DB2 Utilities [...] 433 106 15_Re: DB2 connect11_Larry Cable19_Larry.Cable@NWA.COM31_Fri, 15 Dec 2000 04:34:52 -0600457_iso-8859-1 Speak with your network/systems people on the DB2 side. There is a "TCP/IP keep alive" parameter that could be causing the problem. This parameter is suppose to disconnect idle connections after a specified period of time. Hope this helps.

LarryC

----- Original Message ----- From: "Tam Tran" Newsgroups: bit.listserv.db2-l To: Sent: Friday, December 15, 2000 2:34 AM Subject: DB2 connect [...] 540 46 55_DSNTIAUL: How to create a comma-delimited output file ?12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 15 Dec 2000 12:37:36 +0100397_iso-8859-1 Hello List.

In a normal use DSNTIAUL creates an output file with it's own format, call it DSNTIAUL-format.

Now we have the need to create a comma-delimited file that an other DBMS can accept as input. The goal is to separate each column value per row with a comma.

Can we create such a comma-delimited output file with DSNTIAUL and if so, how ? Any examples ? [...] 587 21 64_Antwort: DSNTIAUL: How to create a comma-delimited output file ?16_Heiko Schmaelzle25_heiko.schmaelzle@SI-BW.DE31_Fri, 15 Dec 2000 13:01:36 +0100480_us-ascii Hi Peter,

you can use a select-statement in the following way and the fields in the result file will be seperated by commas.

'select column1, ',', column2, ',' ... from table;'



greetings h. schmaelzle

================================================ 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. 609 23 69_AW: Antwort: DSNTIAUL: How to create a comma-delimited output fil e ?12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 15 Dec 2000 13:51:37 +0100490_iso-8859-1 Thank you, Heiko.

We have specified a complete SQL statement in SYSIN. And we gave SYSTSIN "PARMS(´SQL´)".

And surprise, surprise - it works... ;-))

With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r

===============================================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. 633 36 18_DB2 Control Centre11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Fri, 15 Dec 2000 12:59:22 -0000362_- All,

I am have just started using DB2 Control Centre and am trying to interegate DB2 objects on our DB2 S/390 system. We have the S390 server defined in the structure, followed by the location but beneath that I have tables (which does a select against systables). How do I get the structures that I see when I expand my DB2 systems on my desktop? [...] 670 56 31_Re: Anyone using Brio with DB2?15_Stacey L Waters23_waterss1@NATIONWIDE.COM31_Fri, 15 Dec 2000 07:33:01 -0600464_us-ascii We are just starting to use Brio with OS390 DB2. If you allow your users to create tables in Brio you do not have control over the tablespace size or where it will be located.

Other then this it has gone pretty good.

Stacey







"Maculsay, Gary R" @RYCI.COM> on 12/14/2000 05:16:58 PM

From: "Maculsay, Gary R" @RYCI.COM on 12/14/2000 05:16 PM [...] 727 141 15_Re: DB2 connect8_Tam Tran18_ttran@LASERCOM.COM31_Fri, 15 Dec 2000 16:07:05 +0100818_us-ascii Thank you very much Larry, the answer we can find in

http://support.microsoft.com/support/kb/articles/Q102/9/73.asp?VOTE=2

Again, many thanks, it is a cadeau for Chrismax

Merry Chrismax and Happy new year

TRANCT



















Larry Cable on 12/15/2000 11:34:52 AM

Please respond to Larry Cable

To: DB2-L@RYCI.COM cc: (bcc: Tam Tran/Geneva/LaserCom) Fax to: Subject: Re: DB2 connect







Speak with your network/systems people on the DB2 side. There is a "TCP/IP keep alive" parameter that could be causing the problem. This parameter is suppose to disconnect idle connections after a specified period of time. Hope this helps. [...] 869 97 58_Re: LIKE predicate available for Oracle and SQL Server ???9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Fri, 15 Dec 2000 08:14:00 -0700475_iso-8859-1 Georg,

Oracle does support Pattern searches using the LIKE predicate. It's format and usage is just like DB2 and returns the same type of answer sets. I've never used SQL Server so I can't answer that one.

Good Luck, Don Alden

-----Original Message----- From: Tennant, Steve [mailto:Steve.Tennant@ATO.GOV.AU] Sent: Thursday, December 14, 2000 4:03 PM To: DB2-L@RYCI.COM Subject: Re: LIKE predicate available for Oracle and SQL Server ??? [...] 967 83 29_Re: DB2 Referential Integrity11_Joseph Link26_joe.link@FIRSTDATACORP.COM31_Fri, 15 Dec 2000 09:34:49 -0600432_us-ascii Actually, there is one RI condition that will invalidate plans and packages, which bit me once before. Quoting the SQL Reference:

"When a referential constraint is defined with a delete rule of CASCADE or SET NULL, all plans and packages that refer to the parent table of the constraint are invalidated. Furthermore, all plans and packages that refer to tables from which deletes cascade are also invalidated." [...] 1051 25 50_Gary Matthews/IS/BNO/SunLife is out of the office.13_Gary Matthews48_Gary_Matthews/IS/BNO/SunLife@SUNLIFEOFCANADA.COM31_Fri, 15 Dec 2000 14:48:25 +0000702_us-ascii --------------------------------------------------------------------------- This E-mail message (including attachments, if any) is confidential and may well also be legally privileged from disclosure. It is only for the person(s) to whom it is addressed. If you have received this e-mail message in error, please do not read beyond the end of this notice, copy it or use it for any purposes, or disclose its contents to any other person. Please notify us immediately of the error by reply e-mail and then delete this message from your system. Thank you for your help in preserving the security of our documents. --------------------------------------------------------------------------- [...] 1077 49 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 15 Dec 2000 11:02:23 -0500571_ISO-8859-1 First the primary thing to cosider is that number of writes overall will not change, when the avg pages/write is low. This will not clog the i/o subsystem.

Secondly, if you saw a cpu spike of 60% (or any large spike) this was NOT from i/o.

Since the i/o does not change, there will be no increase of cpu. Aditionally, if I remember my old calculations, about 160 i/o per SECOND is about 1 MIP of cpu cost.... Increasing or decreasing i/o rarely has any "noticeable" effect on the observed CPU busy rate.... although it does have a cpu cost, [...] 1127 101 29_Re: DB2 Referential Integrity14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 15 Dec 2000 11:05:02 -0500472_iso-8859-1 Depending on the DB2 version, shouldn't these plans be rebound at runtime via the default DSNZPARM (ABIND)?





-----Original Message----- From: Joseph Link [mailto:joe.link@FIRSTDATACORP.COM] Sent: Friday, December 15, 2000 10:35 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Referential Integrity



Actually, there is one RI condition that will invalidate plans and packages, which bit me once before. Quoting the SQL Reference: [...] 1229 50 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 15 Dec 2000 11:10:44 -0500603_ISO-8859-1 Rick, Objects should normally be grouped by access type and working set to optimize the read access (eliminate read i/o). Basically, I don't worry about the writes very much as far as rtying to get more pages/write..... because your workload probably varies more per day in this aspect, and at different times of day, than the read side. In many cases vdwqt "might" be raised slightly for large sequentail batch workloads that have pages/write > 20. And you would have to monitor to see if it increased pages/write.... it might or might not. This is especially relevant looking at large [...] 1280 18 50_SUBASH JOHN/VEND/VA/Bell-Atl is out of the office.11_Subash John23_subash.john@VERIZON.COM31_Fri, 15 Dec 2000 11:01:12 -0500427_us-ascii I will be out of the office from 12/15/2000 until 12/20/2000.



If you still need to contact me urgently, page me at (703) 331 9772

Thanks & Regards, Subash

================================================ 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. 1299 139 29_Re: DB2 Referential Integrity11_Joseph Link26_joe.link@FIRSTDATACORP.COM31_Fri, 15 Dec 2000 10:24:40 -0600368_us-ascii Yes, the plans and packages will auto-rebind the first time they are executed. The mistake I made was to alter the table in the middle of the day, which caused several hundred online packages to attempt to auto-rebind all at once. Aside from the peformance hit, many of the rebinds failed with contention on the catalog causing the transactions to fail. [...] 1439 121 15_Re: DB2 Connect13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Fri, 15 Dec 2000 11:15:37 -0600519_iso-8859-1 good one

-----Original Message----- From: Kline, Wayne [mailto:wayne.kline@INGRAMMICRO.COM] Sent: Thursday, December 14, 2000 3:21 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect



Re: then everyone running the admin client on their PC would almost need to be a DBA.

Isn't that MicroSoft's direction? :-)

-----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: Thursday, December 14, 2000 12:16 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect [...] 1561 99 15_Re: DB2 connect13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Fri, 15 Dec 2000 11:29:40 -0600406_iso-8859-1 If a connection is broken (i.e. TCP/IP) then you're gonna lose the session. Period. End of story. Check the "idle active thread timeout" ZPARM. If you don't want idle threads to timeout, make sure this doesn't have a non-zero value.

-----Original Message----- From: Tam Tran [mailto:ttran@LASERCOM.COM] Sent: Friday, December 15, 2000 3:35 AM To: DB2-L@RYCI.COM Subject: DB2 connect [...] 1661 36 31_FW: Control Center 6.1 question13_Victor, Craig17_CVictor@SANDC.COM31_Fri, 15 Dec 2000 11:53:23 -0600560_iso-8859-1 > Just to let you know. There was an old version of the SQLTABLE exec > sitting on the Reorg serve machine's A disk. Once I renamed it the > procedure worked. > > Craig Victor > cvictor@sandc.com > -----Original Message----- > From: Victor, Craig > Sent: Wednesday, December 13, 2000 10:53 AM > To: 'VMESA-L@LISTSERV.UARK.EDU' > Subject: Control Center 6.1 question > > > Hello all > I'm having a problem with Control Center 6.1 and DB2/VM 6.1. When trying > to add or redefine a column to be not null the procedure fails with an -- > ARI0897E [...] 1698 63 17_DRDA with DB2 4.151_=?iso-8859-1?Q?Edmil=E7on_Alves_de_Oliveira_-_BMS?=19_edmilcon@BMS.COM.BR31_Fri, 15 Dec 2000 16:13:36 -0300263_iso-8859-1 Hi folks, can anyone tell me if exists the possibility to implement DRDA over TCP/IP with DB2 4.1 in an OS/390 2.5 environment ?

TIA, Edmilson Alves de Oliveira BMS - Belgo Mineira Sistemas S/A 55-31-217-4203; 55-31-9116-4362 edmilcon@bms.com.br 1762 20 10_MALAKA UDF19_Mohammad Hazzabigun24_mhazzabigun@NETSCAPE.NET31_Fri, 15 Dec 2000 13:37:29 -0500288_us-ascii Hello,

I am new DB2 DBA. I am having problems with UDFs. I am making my UDF called MALAKA to do some things and it is OK in DB2. But when it is used I get error message for it saying about invalid function pointer. I am calling to IBM but am not getting help for it. [...] 1783 121 15_Re: DB2 connect22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Fri, 15 Dec 2000 13:55:24 -0500648_iso-8859-1 Tran is not connecting to DB2 for OS/390. He's connecting to DB2 for NT database server from a NT client.

Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749



-----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: Friday, December 15, 2000 12:30 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 connect



If a connection is broken (i.e. TCP/IP) then you're gonna lose the session. Period. End of story. Check the "idle active thread timeout" ZPARM. If you don't want idle threads to timeout, make sure this doesn't have a non-zero value. [...] 1905 88 21_Re: DRDA with DB2 4.10_22_BILL_GALLAGHER@PHL.COM31_Fri, 15 Dec 2000 14:02:25 -0500438_iso-8859-1 I believe that direct TCP/IP connectivity to DB2 only works for DB2 v5.1 and later.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







Edmilçon Alves de To: DB2-L@RYCI.COM Oliveira - cc: BMS Subject: DRDA with DB2 4.1 Sent by: DB2 Data Base Discussion List



12/15/00 02:43 PM Please respond to DB2 Data Base Discussion List [...] 1994 13 21_Re: DRDA with DB2 4.10_17_JTonchick@AOL.COM29_Fri, 15 Dec 2000 15:26:06 EST314_US-ASCII Correct... to use TCP, you MUST be DB2 for OS/390 v5 or higher.

================================================ 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. 2008 21 24_Number of tables in Join15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 15 Dec 2000 15:52:36 -0500496_iso-8859-1 We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a query with 19 table join and some of them are view and it has multiple tables. So there was totally 23 base tables in the join. Even though it was slow it was working in DB2 version 5.1. But after the upgrade it is giving -129 SQLcode (too many tables in the query). Based on the V6 features, I thought the 15 tables limit is increased to 225. Is that right? Does anybody had this problem after the upgrade. [...] 2030 47 28_Re: Number of tables in Join12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Fri, 15 Dec 2000 16:01:25 -0500683_us-ascii The enforcement of 15 table joins was reinstated in v6. see apar pq31326







"Philip, Sibimon" @RYCI.COM> on 12/15/2000 03:52:36 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Number of tables in Join



We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a query with 19 table join and some of them are view and it has multiple tables. So there was totally 23 base tables in the join. Even though it was slow it was working in DB2 version 5.1. [...] 2078 128 21_Re: DRDA with DB2 4.113_Andy Seuffert21_aseuffert@NEONSYS.COM31_Fri, 15 Dec 2000 15:04:21 -0600645_us-ascii







Andy Seuffert@NEON 12/15/2000 03:04 PM

Shadow Direct from NEON Systems, where I work, fully supports access to OS/390 and DB2 V4.1 with TCP/IP. The way we accomplish this is by having our own OS/390 address space which accepts inbound TCP/IP requests. We then open a connection to DB2 from our address space passing all client SQL requests to DB2. Shadow Direct originally supported DB2 V2.3 and above. Not only does Shadow Direct support access to DB2, but virtually any other DBMS you have residing on your OS/390 platform, this includes IMS, CICS, Adabas, VSAM, sequential files and more. [...] 2207 72 28_Re: Number of tables in Join15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 15 Dec 2000 16:08:51 -0500294_- Then what is meant by

"DB2 increases the maximum number of tables allowed in a view, and in SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225. The maximum number of base tables that are allowed in a view, tables in a FROM clause, and subqueries in a statement is 15." [...] 2280 50 15_Bufferpool info14_Karthik Ganesh21_karthik_gus@YAHOO.COM31_Fri, 15 Dec 2000 13:15:37 -0800560_us-ascii Hi LIST,

I am a novice to Bufferpool tuning. Had some doubts where I can justify myself before pointing some to others.

In my client's place we have singel Bufferpool BP0 allcoated 16000 pages and HP backed by 27000 PAGES. It seems that all of them are well satified by the performance & HIT RATIO (which is around 87%) always. But I am for mutliple BP srategy where I am not able to justify to my client in detail since I am not familiar and confident. They say that we will loose time in the cross memory services while accessing [...] 2331 86 28_Re: Number of tables in Join16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Fri, 15 Dec 2000 14:16:10 -0700550_- 15 time 15 is 225

> -----Original Message----- > From: Philip, Sibimon [SMTP:SPhilip@CSXLINES.COM] > Sent: Friday, December 15, 2000 2:09 PM > To: DB2-L@RYCI.COM > Subject: Re: Number of tables in Join > > Then what is meant by > > "DB2 increases the maximum number of tables allowed in a view, and in > SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225. The maximum > number of base tables that are allowed in a view, tables in a FROM clause, > and subqueries in a statement is 15." > > in V6 features. I am confused about [...] 2418 74 19_Re: Bufferpool info12_Susan Bowers28_susan.bowers@CPA.STATE.TX.US31_Fri, 15 Dec 2000 15:40:53 -0600447_US-ASCII There are some really good articles on the following website (technical papers about DB2):

http://www.multimania.com/db2usa/earticle.htm

One by Martin Hubel called 'It's Time to Re-think Your DB2 Buffer Pool Strategy' may be very helpful to you. There are several others written specifically about buffer pool tuning also.



On Fri, 15 Dec 2000 13:15:37 -0800 Karthik Ganesh wrote: [...] 2493 101 60_Re: Calling Ex Cobol programmers - how to flush BSAM buffers24_Humphris,Richard P.(NXI)24_Richard.Humphris@CNA.COM31_Fri, 15 Dec 2000 15:59:44 -0600563_iso-8859-1 I hope you've already done what others have already suggested and created a new DB2 table.

Note: Cobol uses QSAM (not BSAM) for sequential files. Even if blocksize = lrecl, QSAM considers the write complete when it puts the data in the buffer and does the equivilent of a "PUT" macro call. It is HIGHLY unlikely that the I/O to the file was even initiated before the cobol program continued to run; nor does it ensure that an extend, if needed, was even started. Even if COBOL had used BSAM (instead of QSAM) the result would be no better. [...] 2595 72 19_Re: Bufferpool info22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Fri, 15 Dec 2000 17:29:01 -0500517_iso-8859-1 Check Joel's papers & presentations at http://www.responsivesystems.com



Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749



-----Original Message----- From: Karthik Ganesh [mailto:karthik_gus@YAHOO.COM] Sent: Friday, December 15, 2000 16:16 To: DB2-L@RYCI.COM Subject: [DB2-L] Bufferpool info



Hi LIST,

I am a novice to Bufferpool tuning. Had some doubts where I can justify myself before pointing some to others. [...] 2668 223 15_Re: DB2 connect14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Fri, 15 Dec 2000 15:00:44 -0800438_iso-8859-1



Tam, Would you really want a 24 hour delay in processing from sign-on until a transaction is entered? This would seem to be contrary to Auditing concerns, after 24 hours of inactivity, how can you safely assume that the statement is being submitted by the same user that originally logged on? Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 2892 33 18_Need Info on "UDB"13_Sanjay Thakur17_infodb2@YAHOO.COM31_Fri, 15 Dec 2000 17:08:57 -0800343_us-ascii Hi List,

Today only I joined to this list. I am working as a DB2 developer since 3-4 yrs.

Since long time I am hearing lot about "UDB". But I don't have exact idea about it, what exactly it is? and How it is different from DB2 or is it same as DB2V6? Some body told me that it is new name for DB2V6. Is that right? [...] 2926 87 19_Re: Bufferpool info12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Sat, 16 Dec 2000 02:06:33 -0600471_- Ganesh:



You'll see the memory is getting exhausted when the number of writes failures and read failures in the hiperpool is high. (your hiperpool must be define with CASTOUT=YES to allow the system still these pages if needed). Check if your hiperpools were define with castout=yes , if not change and check the hiperpool statistics. If your hiperpool were define define with castout=no you'll never get writes and read failures onto the hiperpool, [...] 3014 19 22_Re: Need Info on "UDB"8_Bob Abad16_BobRabad@AOL.COM29_Sat, 16 Dec 2000 13:50:24 EST442_ISO-8859-1 Sanjay,

Here's the UDB url for V6 & V7:

http://www-4.ibm.com/cgi-bin/software/db2www/library/pubs.d2w/report#UDBPUBS

Hope this gives you clear picture of UDB in UNIX...

================================================ 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. 3034 90 19_Re: Bufferpool info14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sat, 16 Dec 2000 17:24:40 -0500377_ISO-8859-1 One pool has NOT been the way to get decent performance for 15 years. You give no indication of a level of system activity... getpages/hour would be useful

You must be sure you are using the CORRECT system hit ratio formula; (getpages - Sum of All Pages Read)/Getpages where sum of all pages read includes pages read into the pool by prefetch functions. [...] 3125 75 59_Re: DSNTIAUL: How to create a comma-delimited output file ?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sun, 17 Dec 2000 15:03:07 +1100352_iso-8859-1 There are a couple of slights catch with the method below:

1) DSNTIAUL includes lengths on variable length columns. One such variable length column are the "','" constants. For these, you will need to use "SUBSTR(',',1,1)" to get fixed length - without the x'0001' length. You will need to use similar methods on other varchars. [...] 3201 73 14_Re: MALAKA UDF14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sun, 17 Dec 2000 15:29:40 +1100568_iso-8859-1 Mohammad

There are a few things we need to know:

- what platform (eg OS/390, NT, AIX, etc) and version (eg V6, V7) of DB2 are you using - what is the actual SQL used to create the UDF - what SQL are you using to invoke the UDF, what are the definitions of any host variables used - what is the parameter definition used in your UDF code. For example: COBOL Linkage Section and Procedure Division statement; C procedure parameters and their specification - what is the _actual_ error message you are getting. If it refers to anything, can [...] 3275 96 22_Re: Need Info on "UDB"14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sun, 17 Dec 2000 16:09:07 +1100475_iso-8859-1 Once upon a time MVS (as it was named in those days) had "DB2", and OS/2 had "DBM" (Data Base Manager). And AS/400 had a relational data base engine which was just another bit of that operating system.

Then the powers that be decided that as DB2 had such a good brand image, that as they expanded the scope of OS/2's DBM into Windows and *IX, they would rename it "DB2". But the two DB2s were totally different internally. And AS/400 stayed as it was. [...] 3372 51 40_Re: Evaluating CA/Platinum DB2 Utilities10_Alan Smith25_alancsmith@BTINTERNET.COM31_Sun, 17 Dec 2000 11:33:35 +0000553_US-ASCII Phil, If you're genuinely interested in what people think about Platinum products, I could probably give you a few dozen issues. I am the main installer/support for the products at our shop. The main problem as I see it is the lack of any sort of human interface design in the ISPF dialogs. I'm very familiar with platinum stuff in general but when I go into Plan Analyzer, I just think 'I haven't got the time to spend trying to learn how this works' - it's just too unintuitive. Very few people here will go anywhere near it. I'm pretty [...] 3424 63 22_Re: Language Interface10_Alan Smith25_alancsmith@BTINTERNET.COM31_Sun, 17 Dec 2000 11:33:32 +0000604_US-ASCII This is an enormous problem for us too when we use dynamically-called subroutines. Various hideous solutions have been used at our shop, including linking all such routines as IMS, (so the top-level programs always have to be IMS whether its is used or not), and putting a subroutine through the compile/change procedure twice, so you get two different versions of the same program. Couldn't resolution of the module be put off until runtime, with different versions in the IMS and DB2 libraries so that the one that gets picked up depends on your concatenation? Or is that too simplistic? [...] 3488 49 59_Re: DSNTIAUL: How to create a comma-delimited output file ?10_Alan Smith25_alancsmith@BTINTERNET.COM31_Sun, 17 Dec 2000 11:39:43 +0000656_US-ASCII Under V6 you can use CHAR rather than SUBSTRING and DIGITS.

Alan Smith





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

Date: Sun, 17 Dec 2000 15:03:07 +1100 From: James Campbell Subject: Re: DSNTIAUL: How to create a comma-delimited output file ?

There are a couple of slights catch with the method below:

1) DSNTIAUL includes lengths on variable length columns. One such variable length column are the "','" constants. For these, you will need to use "SUBSTR(',',1,1)" to get fixed length - without the x'0001' length. You will need to use similar methods on other varchars. [...] 3538 24 23_CA/Platinum LRECL Usage12_Billy Shears23_bill_shears@HOTMAIL.COM31_Sun, 17 Dec 2000 11:18:56 -0600358_- I have a question to all CA/Platinum utility users:

We need to extend our LRECL beyond what Pt/Unload would normally generates. It seems there is a LRECL parameter in the utility that can do the trick. Questions: Are there any users using this feature? How does it work? Are there any things I should know (e.g. what comes in the extra bytes)? [...] 3563 27 18_MAD MAX goes again12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Mon, 18 Dec 2000 21:49:19 +0200497_iso-8859-1 Hi, As my PC went down with all mail, I have not kept up with everything, yet. Even now the "reply" does not work... Any way - the query was meant to check the price one pays for the limited 4kb table, this should be changed either by enlarging it or allowing syntax like the one allowed in the PC/UNIX DB2 version. As for the "distinct...group by" - it takes 2 minutes and 4 seconds of CPU time which is more than the "join" method. Thanks y'all for participation in the "game" - [...] 3591 91 59_Re: DSNTIAUL: How to create a comma-delimited output file ?11_Phil Castle20_phil@ECNETWORK.CO.NZ31_Mon, 18 Dec 2000 09:18:55 +1300527_iso-8859-1 if you would consider using a non-mainframe based tool, then there are a number of other options. The tool I use all the time is one I've written myself. Not surprisingly it does all the things that I, as a DBA/developer, would like a tool to do. This has an option to output a table / query as a comma-delimited file. I use this all the time and it works fine. It has many options to cope with all the sort of things which can go wrong with an export-to-file - for instance if any of your char columns contain [...] 3683 85 59_Re: DSNTIAUL: How to create a comma-delimited output file ?14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Mon, 18 Dec 2000 08:03:03 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 3769 156 44_Re: FW: Evaluating CA/Platinum DB2 Utilities14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Mon, 18 Dec 2000 08:37:30 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 3926 73 31_DB2 v.2 to DB2 v.7.1 on RS/600011_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Mon, 18 Dec 2000 08:09:04 -0800419_us-ascii Dear all,

We are trying to upgrade DB2 v.2 to DB2 7.1. EE on RS/6000, but our main problem is that none of us know anything about RS/6000 and DB2 v.2 What are the requirements that we need to know ? Is there any specific problem that we would encounter and should beware of ? If anyone can tell us where to find all the necessary information, or even steps, we would be very thankful. Best Regards [...] 4000 73 31_DB2 v.2 to DB2 v.7.1 on RS/600011_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Mon, 18 Dec 2000 08:09:01 -0800419_us-ascii Dear all,

We are trying to upgrade DB2 v.2 to DB2 7.1. EE on RS/6000, but our main problem is that none of us know anything about RS/6000 and DB2 v.2 What are the requirements that we need to know ? Is there any specific problem that we would encounter and should beware of ? If anyone can tell us where to find all the necessary information, or even steps, we would be very thankful. Best Regards [...] 4074 83 18_UDB test doubts...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Sun, 17 Dec 2000 20:07:19 -0800482_us-ascii Hi !

I was attempting UDB test paper on IBM website and was confused by following questions:

1. Given the following: CREATE TABLE tab1 (col1 INT CONSTRAINT notnul CHECK(col1 IS NOT NULL), col2 CHAR(10))

Which of the following will enforce uniqueness of col1 which currently does NOT contain duplicate values?

a. Create primary key on col1 b. Create unique index on col1 c. Create a cluster index on col1 d. Create unique constraint on col1 [...] 4158 128 22_Re: UDB test doubts...14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Mon, 18 Dec 2000 16:01:46 +1100395_iso-8859-1 Ritu

Hazading some guesses

1. While col1 might not _currently_ have any null occurances, the definition of the column does allow them. All that is needed is to drop the "notnul" constraint and col1 can have them. But if you've created the unique constraint then you have done more than just enforced uniqueness, you've forced additional steps to allow null values. [...] 4287 179 22_Re: UDB test doubts...16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 18 Dec 2000 10:47:28 +0530557_iso-8859-1 I agree with James for the 1st point but i would like to add few more thing, Can we have more than one constraints clause coded in the single column in the definition of CREATE or ALTER table ? If the CONSTRAINTS clause was not coded for NOT NULL function the it could have been coded for UNIQUE .DB2 for OS/390 doesn't have create constraints statement but it does have UNIQUE clause which requires Unique index to be created.Infact i went thru' V7 books for UDB and it also do not have any CREATE CONSTRAINTS statements.I hope i have seen [...] 4467 167 22_Re: UDB test doubts...13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Sun, 17 Dec 2000 23:16:37 -0600336_iso-8859-1 Ritu,

I have to agree with James on the first question. The best answer is b. Create Unique Index. It is the actual unique index which enforces uniqueness. Both creating a primary key or a unique constraint will actually create a unique index. So there are 3 answers which are correct, but b. is the best answer. [...] 4635 77 22_Re: Need Info on "UDB"16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 18 Dec 2000 10:55:19 +0530523_- There were good discussion on the naming of UDB and DB2 after one of the forwarded mail (by IBM) from Morill John in which IBM requested the DB2 family databases to be called as DB2 UDB for "platform name".James explained most of the things in this thread but do search in the archive for some more stuffs on this. Now a days DB2 is available on most of the platforms. I have also been asked these kind of questions many times in the place i have worked and many people never believes when i say I am running DB2 on [...] 4713 52 35_Fw: DB2 v.2 to DB2 v.7.1 on RS/600011_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Mon, 18 Dec 2000 00:23:22 -0500318_us-ascii V2 to V7 isn't a supported migration path. Two options you might explore include: 1) V2 to V5.2 (assumes you can get V5 code), then V5.2 to V7. 2) Rebuild your database(s) in V7 by exporting your tables from V2 then importing into V7. If you need/can't find your DDL, try IBM supplied program 'db2look'. [...] 4766 96 19_Re: Bufferpool info16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 18 Dec 2000 11:28:42 +0530516_- Hi Karthik, First of all i am concerned about the 40% active pages. I think in any workload scenario, it is very high. It should be lowered down. Considering out of the 40% ,even 5% of the pages are queued for write then it is approx. 800 pages which is high. I think you have to lower the VDWQT to either 0 or get some number if you are using V6. By separating it to multiple BP, the writes can be distributed. Another thing which i have seen that in maintaining the single BP, the main problem is to assign [...] 4863 83 59_Re: DSNTIAUL: How to create a comma-delimited output file ?7_Kevin K18_kfosler@EXECPC.COM31_Mon, 18 Dec 2000 02:04:38 -0600669_- **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 4947 81 59_Re: DSNTIAUL: How to create a comma-delimited output file ?0_22_The-Thanh.Luong@AGF.BE31_Mon, 18 Dec 2000 09:22:55 +0100633_- Hi,

I use this query with delimiters to feed some Excel spreadsheets (we run DB2 V4, OS/390) to follow up the size of my tables:

********************************* Top of Data ******************* SELECT ';'||SUBSTR(A.CREATOR,1,4)||';'||SUBSTR(A.NAME,1,8)||';', ';'||SUBSTR(DIGITS(B.PARTITION),4,2)||';'||DIGITS(B.CARD)||';', ';'||DIGITS(A.RECLENGTH+8)||';' FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLEPART B WHERE A.CREATOR LIKE 'ssid%' AND A.TYPE = 'T' AND A.DBNAME NOT LIKE '____01_%' AND A.DBNAME = B.DBNAME AND A.TSNAME = B.TSNAME ORDER BY 1, 2 ; ******************************** Bottom of Data ***************** [...] 5029 160 15_Re: DB2 connect8_Tam Tran18_ttran@LASERCOM.COM31_Mon, 18 Dec 2000 09:53:19 +0100615_us-ascii Michael,

Thank you very much. Harry cABLE gave me a solution, you can find it in :

http://support.microsoft.com/support/kb/articles/Q102/9/73.asp?VOTE=2

It was very nice cadeau for Chrismax and New Year Thanks again Harry.

TRANCT





















"Piontkowski, Michael L" on 12/15/2000 07:55:24 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Tam Tran/Geneva/LaserCom) Fax to: Subject: Re: DB2 connect [...] 5190 66 75_Re: DB2 Referential Integrity - Do Plans & Packages have any ref erences ??16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Mon, 18 Dec 2000 14:44:21 +0530574_- Hi All, In response to the affect of dropping the RI on Rebinding the plans and packages. I am not sure why it is specifically mentioned that plans and packages need to be rebound if some particular kind of RIs(mentioned below the mail from Joe Link containing text from SQL Reference ) are dropped. Does this raise some question : Do plans and packages have any references mentioned if objects containing RI is used ? I am sure all the delete/insert/update statements are not included in the object module (plans) but there must be something which tells the plan to [...] 5257 203 15_Re: DB2 connect8_Tam Tran18_ttran@LASERCOM.COM31_Mon, 18 Dec 2000 10:47:07 +0100861_us-ascii

Hi Wayne,

Thank you very much for reply. I got a solution for that. You can find solution on :

http://support.microsoft.com/support/kb/articles/Q102/9/73.asp?VOTE=2

Solution is given by Harry Cable

Regards

TAM















Wayne Driscoll on 12/16/2000 12:00:44 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Tam Tran/Geneva/LaserCom) Fax to: Subject: Re: DB2 connect











Tam, Would you really want a 24 hour delay in processing from sign-on until a transaction is entered? This would seem to be contrary to Auditing concerns, after 24 hours of inactivity, how can you safely assume that the statement is being submitted [...] 5461 138 15_Re: DB2 connect14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Mon, 18 Dec 2000 09:16:03 -0000511_iso-8859-1 "make sure this doesn't have a non-zero value."

Sounds like a JCL COND statement!

-----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: 15 December 2000 17:30 To: DB2-L@RYCI.COM Subject: Re: DB2 connect



If a connection is broken (i.e. TCP/IP) then you're gonna lose the session. Period. End of story. Check the "idle active thread timeout" ZPARM. If you don't want idle threads to timeout, make sure this doesn't have a non-zero value. [...] 5600 54 35_Re: DB2 v.2 to DB2 v.7.1 on RS/600010_CEI, Bruno20_Bruno.CEI@SEMA.CO.UK31_Mon, 18 Dec 2000 11:37:03 -0000601_ISO-8859-1 Endy, I think that most of the information is included in the Quick Beginnings manual, which is available online on the IBM website . From this manual I understand that migrating to V7 is only supported from V5.0 or later versions, which in your case appears to be a problem. I reckon that you will have to migrate to V6 first and then move to V7, but I think that you should contact IBM for advise on this. Regards Bruno -----Original Message----- From: Endy Lambey [mailto:endyl@mitrainfosarana.co.id] Sent: 18 December 2000 16:09 To: DB2-L@RYCI.COM Subject: DB2 v.2 to DB2 v.7.1 on [...] 5655 45 32_ICF Catalog Aliases - Multilevel9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Mon, 18 Dec 2000 11:32:03 +0000382_us-ascii Hi, DB2 5.1 on OS390 2.8

Does anybody use '3' (or more) level ICF Catalog aliases for any DB2 datasets ? We currently use '2' level for our active and archive logs but I would like to change this to '3' (as part of conversion to datasharing).

Our OS390 Sysprogs are wary of using '3' (or more) level aliases (historically we have not used these before). [...] 5701 39 59_Re: DSNTIAUL: How to create a comma-delimited output file ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Mon, 18 Dec 2000 07:15:13 -0600637_ISO-8859-1 The-Thanh Luong, Please use a internet supported character set -- I never open attachments from listservers and would hate to think I'm missing something important.

Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other [...] 5741 93 22_Re: Language Interface20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 18 Dec 2000 08:32:53 -0500310_us-ascii You are right. It is simple. I have done this by customizing the DSNHLI and got good performance too by following few ROT. Two easy ways to achieve this.

1. Write DSNHLI and follow LLE-CDE chain to find, which attachment is loaded. 2. Check for the SQLCODE (Let DB2 chase LLE-CDE for you) [...] 5835 103 28_Re: Number of tables in Join15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 18 Dec 2000 09:28:36 -0500656_- Thanks to all those who responded. Now I understand the meaning of 225 tables. This list is great.

Regards..sibi

-----Original Message----- From: Duane Lee - ATCX [mailto:DLee@MAIL.MARICOPA.GOV] Sent: Friday, December 15, 2000 03:16 PM To: DB2-L@RYCI.COM Subject: Re: Number of tables in Join



15 time 15 is 225

> -----Original Message----- > From: Philip, Sibimon [SMTP:SPhilip@CSXLINES.COM] > Sent: Friday, December 15, 2000 2:09 PM > To: DB2-L@RYCI.COM > Subject: Re: Number of tables in Join > > Then what is meant by > > "DB2 increases the maximum number of tables allowed in a view, and in > SELECT, UPDATE, [...] 5939 158 15_Re: DB2 connect8_Tam Tran18_ttran@LASERCOM.COM31_Mon, 18 Dec 2000 15:43:50 +0100708_us-ascii Thanks to all who reply my question about DB2 connect.

Harry CABLE gave me a solution, you can find it in :

http://support.microsoft.com/support/kb/articles/Q102/9/73.asp?VOTE=2



TRANCT





















"Piontkowski, Michael L" on 12/15/2000 07:55:24 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Tam Tran/Geneva/LaserCom) Fax to: Subject: Re: DB2 connect







Tran is not connecting to DB2 for OS/390. He's connecting to DB2 for NT database server from a NT client. [...] 6098 221 28_Re: Number of tables in Join14_Scott Trometer22_scott.trometer@RCI.COM31_Mon, 18 Dec 2000 09:50:18 -0500488_iso-8859-1 Well...now I'm not followin' ya...

Are we saying that joining 15 views, each containing 15 base tables, is how the new max of 225 is achieved ?







-----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Monday, December 18, 2000 9:29 AM To: DB2-L@RYCI.COM Subject: Re: Number of tables in Join



Thanks to all those who responded. Now I understand the meaning of 225 tables. This list is great. [...] 6320 225 28_Re: Number of tables in Join15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Mon, 18 Dec 2000 10:10:43 -0500545_iso-8859-1 Yes. Basically in the From clause you cannot have more than 15 tables/views. This is my understanding until somebody tells otherwise.





thanks..sibi

-----Original Message----- From: Scott Trometer [mailto:scott.trometer@RCI.COM] Sent: Monday, December 18, 2000 08:50 AM To: DB2-L@RYCI.COM Subject: Re: Number of tables in Join





Well...now I'm not followin' ya...

Are we saying that joining 15 views, each containing 15 base tables, is how the new max of 225 is achieved ? [...] 6546 62 27_Re: CA/Platinum LRECL Usage9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 18 Dec 2000 08:48:26 -0700557_iso-8859-1 The intended benefit of using the LRECL-USER value is to allow the unloaded data (sysrec dd) to have a specified length greater than what fast unload would have calculated (based on # of columns and length of those columns). Probably tied over to the old vsam days when everyone wanted to unload DB2 tables to a flat file and then process the flat file instead of the DB2 table. Having the ability to have a set LRECL in a program and the unload file would allow the vsam program to not change if the table added columns or deleted columns, [...] 6609 148 28_Re: Number of tables in Join13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Mon, 18 Dec 2000 09:56:19 -0600439_iso-8859-1 RE: Number of tables in JoinSibi,

You are correct. The limits are:

- 15 table max in a FROM clause - 15 table max in a view definition - 14 subquery max in statement.

The combined total of these cannot exceed 225, however there were some issues with this implementation in V6, of which APAR PQ31326 (mentioned by Jeff Frazier) was supposed to solve. I have not tested the limits with the APAR applied. [...] 6758 95 27_Re: CA/Platinum LRECL Usage16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 18 Dec 2000 15:59:19 -0000636_iso-8859-1 If you need to extend the UNLOAD LRECL created by Batch Processor Unload, so the unloaded records fit into ONE-RECORD-PER-ROW - this can be done too:

ROWLEN ( [F,V] ) B The ROWLEN keyword directs the UNLOAD to unload the table one row per record. Specify F for fixed blocked records, or V for variable blocked records. The LRECL and BLKSIZE will be determined for you. The LRECL will equal the maximum length of the row. An example of the ROWLEN keyword being used is shown below. .CALL UNLOAD .DATA DSN(TSUSER.TSUSER.SYSTABLE.DATA) UNIT(SYSDA) ALLOCATE(100,60) ROWLEN(F) SELECT * FROM SYSIBM.SYSTABLES FOR FETCH [...] 6854 25 8_00c901010_27_Bud.Greenman@ONONDAGA.NY.US31_Mon, 18 Dec 2000 11:42:42 -0500523_- I previously wrote about a problem with s0c7 abends in SPUFI when trying to read some tables in db2 v6.1 for os390. There is a ptf that we need to apply to fix this, but it will take awhile. In the meantime I found a way around. All the tables in question have a timestamp column. By updating the timestamp to the current timestamp (which is not a problem in this case) I was able to make the tables available for viewing. However 3 of my tables I was unable to update. The error message 00c90101 came back with the [...] 6880 49 12_Re: 00c9010122_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Mon, 18 Dec 2000 09:10:09 -0800657_iso-8859-1 We have found that a reorg will usually correct the 00c90101.

HTH Marty.

-----Original Message----- From: Bud.Greenman@ONONDAGA.NY.US [mailto:Bud.Greenman@ONONDAGA.NY.US] Sent: Monday, December 18, 2000 8:43 AM To: DB2-L@RYCI.COM Subject: 00c90101



I previously wrote about a problem with s0c7 abends in SPUFI when trying to read some tables in db2 v6.1 for os390. There is a ptf that we need to apply to fix this, but it will take awhile. In the meantime I found a way around. All the tables in question have a timestamp column. By updating the timestamp to the current timestamp (which is not a problem in this [...] 6930 34 28_DB2 Connect concurrent users13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Mon, 18 Dec 2000 11:15:09 -0600347_US-ASCII Hello, We are running DB2 v5 OS/390 and DB2 Connect V5...we are upgrading to V6 and V7 respectively. My question is... we have Peoplelsoft and we are trying to understand how many users are using DB2 Connect concurrently is there a log or something that I can check within DB2 Connect to give me this information? Thanks in advance. [...] 6965 61 32_Re: DB2 Connect concurrent users11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 18 Dec 2000 11:39:44 -0600723_us-ascii If you have DB2 PM Statistics reports, Check the "Statistics Long" search for "DBATS"... : )







Mike Lawrence cc: Sent by: DB2 Data Subject: DB2 Connect concurrent users Base Discussion List



12/18/00 11:15 AM Please respond to DB2 Data Base Discussion List











Hello, We are running DB2 v5 OS/390 and DB2 Connect V5...we are upgrading to V6 and V7 respectively. My question is... we have Peoplelsoft and we are trying to understand how many users are using DB2 Connect concurrently is there a log or something that I can check within DB2 Connect to give [...] 7027 142 32_Re: DB2 Connect concurrent users14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Mon, 18 Dec 2000 10:12:48 -0800525_iso-8859-1 Mike, You can use the license mgr to turn on soft(you can also use hard which will stop connections when you concurrent user limit is reached) monitoring from the control center -- this will show you # of concurrent users. Also using list applications show details will display connected applications. From IBM, a user is considered a person, so if you have a client with multiple connections from the same user, it is only considered one concurrent user. I'm sure Leon can expand on this if needed. HTH Phil [...] 7170 145 15_BUFFERPOOL INFO14_Karthik Ganesh21_karthik_gus@YAHOO.COM31_Mon, 18 Dec 2000 11:07:28 -0800364_us-ascii List,



Thanks a lot for all the information. still trying to get more informations from my side to dig further. I will keep you posted as I get one by one.

Joel & list ,

As you have asked for I am enlcosing some of the informations I have as below:

(These all are collected @ 5 minute intervals AT DIFFERENT TIMES) [...] 7316 47 34_SQL0987N -- Memory overdeployment?14_Spiegel, Barry21_barry.spiegel@EDS.COM31_Mon, 18 Dec 2000 15:07:00 -0500591_iso-8859-1 > Working with DB2 V5.2 on AIX 4.3.2.1. We've been monitoring our memory > utilization for a bit and have been gradually increasing SHEAPTHRES. The > last change to this DBM configuration parameter took place on Friday and > took effect with a db2stop/db2start on Sunday night. > > When the first user tried to connect, he got an SQL0987N error -- usually > indicative of a failure to allocate application global memory. The > related setting -- app_ctl_heap_sz -- is 1024K and hasn't been changed in > months. However, the slight increase to SHEAPTHRES may have taken the > [...] 7364 133 22_Re: Need Info on "UDB"13_Sanjay Thakur17_infodb2@YAHOO.COM31_Mon, 18 Dec 2000 12:18:32 -0800605_us-ascii I am really glad to get all these information on "UDB". These helped me to clear all my doubts.

Thanks lot to James, Sanjeev and Abad Bob for helping me to understand "UDB' better and contributing their suggestions and valuable information to me.

Regards, Sanjay Thakur

--- "S, Sanjeev (CTS)" wrote: > There were good discussion on the naming of UDB and > DB2 after one of the > forwarded mail (by IBM) from Morill John in which > IBM requested the DB2 > family databases to be called as DB2 UDB for > "platform name".James explained > most [...] 7498 66 32_Re: DB2 Connect concurrent users13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Mon, 18 Dec 2000 14:34:16 -0600694_iso-8859-1 Does this imply that there will only be one thread created in total for all connections connecting with the same ID?

[McHugh, Gerry] ----Original Message----- From: Philip Gunning [mailto:Philip.Gunning@QUEST.COM] Sent: Monday, December 18, 2000 1:13 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect concurrent users





Mike, You can use the license mgr to turn on soft(you can also use hard which will stop connections when you concurrent user limit is reached) monitoring from the control center -- this will show you # of concurrent users. Also using list applications show details will display connected applications. From IBM, a user is considered [...] 7565 69 12_Re: 00c9010117_Aldrin Jayatilaka19_aldrinj@AU1.IBM.COM31_Tue, 19 Dec 2000 07:58:29 +1100394_us-ascii 00C90101 problem with DB2 v6.1 can also be caused by a BUG with BMC's Reorg Plus v 5.3.01 which is ment to be DB2 v6 tolerant.

If you are using BMC's Reorg Plus you might want to check this problem out. You can search for the problem (Failure ID: 61422) at the BMC web site for further details. This problem can be resolved by running an IBM reorg on the effected indexes. [...] 7635 78 32_Re: DB2 Connect concurrent users13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Mon, 18 Dec 2000 15:19:04 -0600469_iso-8859-1 No, there will be multiple threads, but for licensing purposes they will not (or at least should not) add to concurrent thread totals.

-----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: Monday, December 18, 2000 2:34 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect concurrent users



Does this imply that there will only be one thread created in total for all connections connecting with the same ID? [...] 7714 96 12_Re: 00c9010114_Mohammed Ahmed22_Mohammed.Ahmed@UAL.COM31_Mon, 18 Dec 2000 15:40:13 -0600337_- Running Rebuild Index and Check Index hopefully will take care of this problem.

---------- From: aldrinj Sent: Monday, December 18, 2000 2:58 PM To: DB2-L Cc: aldrinj Subject: Re: 00c90101

00C90101 problem with DB2 v6.1 can also be caused by a BUG with BMC's Reorg Plus v 5.3.01 which is ment to be DB2 v6 tolerant. [...] 7811 16 12_Re: 00c9010115_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Mon, 18 Dec 2000 15:41:31 -0600377_us-ascii But if Tablespace is in RECP then first run RECOVER then REBUILD , CHECK INDEX , CHECK DATA respectively.

Thanks Nayeem

================================================ 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. 7828 20 22_Re: DB2 Control Centre12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Mon, 18 Dec 2000 15:55:09 -0600298_- To get all the options under DB2 Control Center, we had to apply the FMID JDB661D to our DB2 for OS/390. Then run the install job DSNTIJCC that created the new stored procedure. You might check with you OS/390 people and see if this FMID is applied. The following is the install job we ran. [...] 7849 12 22_Re: Need Info on "UDB"11_Mark Harmon17_mhinnyc@YAHOO.COM31_Mon, 18 Dec 2000 16:33:39 -0600298_- James,

Thanks for the excellent history lesson.

================================================ 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. 7862 65 22_Re: Language Interface14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Tue, 19 Dec 2000 11:47:14 +1100461_iso-8859-1 But how much simpler it would be if, for non-CICS attachments, the attachment had already loaded/IDENTIFYed common entry names (for HLI and WLI). Then all your common routine would need to do is - check TCBCAUF, if used then follow the same chain used by DSNCLI (http://bama.ua.edu/cgi-bin/wa?A2=ind0011&L=ibm-main&P=R38980 was not denied - not even by any of the IBMers on that list) - if not used, then you have the fixed entry names to call. [...] 7928 180 19_Re: BUFFERPOOL INFO14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 18 Dec 2000 20:55:30 -0500591_ISO-8859-1 Since these are 5 minute intervals (?), your Synch I/O rate/sec is > 1200, and adding prefetchj I/Os would drive this even higher.. This extremely high, and means there can probably be truly dramatic performance improvements from pool tuning. Again based on a 5 minute interval, this is a very large system.... it's totally foolish to try running a workload like this from one pool. Pool tuning should easily save several million I/Os per day, and maybe $250K per year in reduced CPU costs for I/O. Now tuning and saving like this does not necessarily mean the CPU busy rate [...] 8109 95 15_SQL Access Path11_Richard Tan19_richtan@AU1.IBM.COM31_Tue, 19 Dec 2000 15:06:00 +1100630_us-ascii Dear all,

I have an SQL join between two tables, say table A and B. I am weighting whether to using UNION ALL for the following situation: TableA has 40,000 rows Non Unique IndexA1: ADDRESS_ID, SUMM_ID

TableB has 63,000 rows Primary Key IndexB1: DISTRICT, SUMM_ID Unique IndexB2: DISTRICT, SUMM_ID, STATUS

SELECT ..... FROM TableA A , TableB B WHERE A.ADDRESS_ID = :ADDRESS-ID AND B.DISTRICT = :DISTRICT AND B.SUMM_ID = A.SUMM_ID AND B.STATUS = 'D' AND B.ORDER_NUM = ' ' UNION ALL SELECT ..... FROM TableA A , TableB B WHERE A.ADDRESS_ID = :ADDRESS-ID AND B.DISTRICT = :DISTRICT AND B.SUMM_ID = [...] 8205 138 19_Re: SQL Access Path13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Mon, 18 Dec 2000 23:05:11 -0600471_iso-8859-1 Richard,

The simple answer is that one pass through the data is more efficient than doing two passes through the same (similar) data. Therefore use the IN list rather than the UNION ALL.

Not only is their CPU reduction in reducing the number of SQL calls, but with the single statement DB2 has greater capabilities to optimize the query utilizing parallelism of the IN list (in V6 if IN list is inner table of join, or V7 less restriction). [...] 8344 166 19_Re: SQL Access Path16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 19 Dec 2000 11:52:41 +0530519_iso-8859-1 Hi Richard, First of all it is nice to see that the EXPLAIN report is in good format in the mail otherwise it become very painful to interpret it.

Coming back to your analysis, i would like to ask did you weigh the estimated cost between the two queries ?. I believe using the UNION ALL will cost more in this case because if we look at the MCROWS (in total) then it is same for the join and UNION ALL and repeated for the UNION ALL because of two join queries. If at all the read pages from the [...] 8511 222 19_Re: SQL Access Path11_Richard Tan19_richtan@AU1.IBM.COM31_Tue, 19 Dec 2000 17:44:23 +1100366_us-ascii Sanjeev,

Thanks very much for your response. This is the first time I am posting in DB2-L. I am suprise with the quick response. I am convince that without UNION ALL is a better SQL, however, to convince our Design team without backup info is not an easy task. that is why I am seeking support from the wider DB2 communities to confirm my view. [...] 8734 71 59_Re: DSNTIAUL: How to create a comma-delimited output file ?0_22_The-Thanh.Luong@AGF.BE31_Tue, 19 Dec 2000 08:31:06 +0100336_- Hi Rick,

Sorry for any inconvenience my previous post may have caused.

In my SELECT statement, I use the characters "vertical bar" to concatenate my fields unloaded by DSNTIAUL, including my semi-colon delimiters. There are also underscores in my DBNAME LIKE clause. The rest of the query is quite normal, I think. [...] 8806 24 13_Can't select!10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 19 Dec 2000 11:18:19 +0330297_US-ASCII Hi all, I'm using DB2-UDB V5 on NT as client and my bank is placed on a VSE machine(DB2-udb V5). I try to run a very simple select statement: SELECT * FROM TEST; from db2 command center and I get the following error: SQL0805N Package ".NULLID.SQLC28N3" was not found. SQLSTATE=51002 [...] 8831 77 17_Re: Can't select!0_31_greg.palgrave@UNISYSWEST.COM.AU31_Tue, 19 Dec 2000 16:05:20 +0800344_us-ascii In the DB2 Client Configuration Assistant, highlight the target database, and click on the BIND button and accept the defaults. You will need to have authority to perform the BIND, or else get your DBA to do it.

Once that is done, you should be able to connect.

Regards, Greg Palgrave Technical Services Unisys West [...] 8909 19 4_ADO!10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 19 Dec 2000 11:44:02 +0330407_US-ASCII Hi all,

Who knows any valuable documents or sample codes about using ADO/RDO on DB2 UDB (for NT or specially VSE machines).

Regards Navid Khan

================================================ 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. 8929 45 19_Db2 connect problem14_Bilal YILDIRIM25_byildirim@HALKBANK.COM.TR31_Tue, 19 Dec 2000 11:19:45 +0200428_windows-1254 I am using db2 connect v5.0 and connecting to OS/390 db2 5.1 via Sna server but in the client side I connect to db2 succesfully but when I select it says :

connect to raptor user db2sys1 ------------------------------------------------------------------------

Database Connection Information

Database product = DB2 OS/390 5.1.1 SQL authorization ID = DB2SYS1 Local database alias = RAPTOR [...] 8975 40 34_V6 OS/390 Distinct(case gives -1275_VCEI,14_VCEI@CEGEKA.BE31_Tue, 19 Dec 2000 10:28:42 +0100440_iso-8859-1 Hello List(en)ers,

DB2 V6 on OS/390

This query returns a -127 sqlcode (more than 1 distinct in a query).

SELECT COL1 FROM TABLEX WHERE COL1 <> ` ` GROUP BY COL1 HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) > 1

Very simple question: why ? I suppose it `s got something to do with how the distinct - case combination is "rewritten" by the optimizer, but nevertheless... [...] 9016 84 23_Re: Db2 connect problem16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Tue, 19 Dec 2000 15:09:38 +0530650_iso-8859-9 Are u missing Semi Colon !! ??

Regards Sanjeev

> -----Original Message----- > From: Bilal YILDIRIM [SMTP:byildirim@HALKBANK.COM.TR] > Sent: Tuesday, December 19, 2000 2:50 PM > To: DB2-L@RYCI.COM > Subject: Db2 connect problem > > I am using db2 connect v5.0 > and connecting to OS/390 db2 5.1 via Sna server > but in the client side I connect to db2 succesfully but when I select > it says : > > connect to raptor user db2sys1 > ------------------------------------------------------------------------ > > Database Connection Information > > Database product = DB2 OS/390 5.1.1 > SQL authorization ID = DB2SYS1 > Local [...] 9101 18 38_Re: V6 OS/390 Distinct(case gives -12715_Matthias Pohlig27_matthias.pohlig@T-ONLINE.DE31_Tue, 19 Dec 2000 04:12:20 -0600437_- Funny...

I tried excactly your statement on a newly created, empty table (no runstats) -just changed the apostrophes to my codepage-

Works flawlessly !

Also a V6 OS/390 ...

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9120 35 16_Db2 reorg online25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Tue, 19 Dec 2000 19:07:57 +0800558_us-ascii Hi,

We are OS390 2.3, DB2 V5.1

I have two questions regarding db2 online reorg

1. I was attempting to reorg a table of 14mil records (online reorg with shrlevel change). I cancelled the job after about 90 mins. The time I cancelled the job the utility was in reload phase and the count was about 4.2 mil. My question is that why is DB2 online reorg taking so long. I compared with BMC reorg of the same table when it was about 8 mil records and took only about 17 mins (shrlevel none). There are no structure changes since [...] 9156 68 39_3 Way left outer join - is it possible?12_Sams, Debbie13_dsams@UFL.EDU31_Tue, 19 Dec 2000 07:45:48 -0500643_iso-8859-1 We have 3 tables that I would like to join - directory, address and phone. I want a left outer join on the directory and would prefer one table scan and one query if possible

The following works for addresses:

SELECT DIR_ID_NO, DIR_NAME, COALESCE(ADDR_DELIVERY_AD,'**N/A**'), COALESCE(ADDR_CITY_NM,'**N/A**'), COALESCE(ADDR_STATE_CD,'**N/A**'), COALESCE(ADDR_ZIP_CD,'**N/A**'), COALESCE(ADDR_ZIP_EXT_CD,'**N/A**') FROM (SELECT DIR_ID_NO, DIR_NAME FROM BADBADM.AA_DIRECTORY) AS X LEFT OUTER JOIN BADBADM.AA_ADDRESS ON DIR_ID_NO = ADDR_ID_NO; ============================================ and this works for phones: [...] 9225 103 43_Re: 3 Way left outer join - is it possible?11_David Nance16_DWNance@FHSC.COM31_Tue, 19 Dec 2000 07:56:14 -0500564_US-ASCII Debbie, Try something like the following: SELECT DIR_ID_NO, DIR_NAME, COALESCE(PH_PHONE_NO,'**N/A**'), COALESCE(PH_EXT_CD,'**N/A**') COALESCE(ADDR_DELIVERY_AD,'**N/A**'), COALESCE(ADDR_CITY_NM,'**N/A**'), COALESCE(ADDR_STATE_CD,'**N/A**'), COALESCE(ADDR_ZIP_CD,'**N/A**'), COALESCE(ADDR_ZIP_EXT_CD,'**N/A**') FROM BADBADM.AA_DIRECTORY LEFT OUTER JOIN BADBADM.AA_ADDRESS ON DIR_ID_NO = ADDR_ID_NO LEFT OUTER JOIN BADBADM.AA_PHONE ON DIR_ID_NO = PH_ID_NO;

Do you really need the left outer join? Will a name not always have a phone and address? [...] 9329 85 23_Re: Db2 connect problem15_Stacey L Waters23_waterss1@NATIONWIDE.COM31_Tue, 19 Dec 2000 07:34:34 -0600656_iso-8859-1 Have you tried putting single or double quotes around your select statement?









Bilal YILDIRIM @RYCI.COM> on 12/19/2000 03:19:45 AM

From: Bilal YILDIRIM @RYCI.COM on 12/19/2000 03:19 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Db2 connect problem

I am using db2 connect v5.0 and connecting to OS/390 db2 5.1 via Sna server but in the client side I connect to db2 succesfully but when I select it says : [...] 9415 186 22_Re: Language Interface20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 19 Dec 2000 08:37:38 -0500416_iso-8859-1 Doesn't have to be that complicated.

If the issue is to use same load module between TSO/CAF or RRSAF then there is never a problem. But if CICS is involved even then it could be as simple as the logic below, for a customized DSNHLI

Call WS-DSNCLI (contains value DSNCLI) and check for SQLCODE to check the attachment If not then Call WS-DSNELI or WS-DSNWLI2, whichever is appropriate. [...] 9602 76 59_Re: DSNTIAUL: How to create a comma-delimited output file ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 19 Dec 2000 07:40:20 -0600654_ISO-8859-1 The-Thanh, whatever character set you used below worked just fine, thank you.

Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 9679 108 43_Re: 3 Way left outer join - is it possible?0_24_db46@DAIMLERCHRYSLER.COM31_Tue, 19 Dec 2000 09:08:03 -0500717_us-ascii This should work

SELECT COALESCE(DIR_ID_NO,'N/A'), COALESCE(DIR_NAME, 'N/A'), COALESCE(ADDR_DELIVERY_AD,'**N/A**'), COALESCE(ADDR_CITY_NM,'**N/A**'), COALESCE(ADDR_STATE_CD,'**N/A**'), COALESCE(ADDR_ZIP_CD,'**N/A**'), COALESCE(ADDR_ZIP_EXT_CD,'**N/A**'), COALESCE(PH_PHONE_NO,'**N/A**'), COALESCE(PH_EXT_CD,'**N/A**') FROM (SELECT DIR_ID_NO, DIR_NAME FROM BADBADM.AA_DIRECTORY) AS X LEFT OUTER JOIN BADBADM.AA_PHONE PH ON X.DIR_ID_NO = PH.PH_ID_NO LEFT OUTER JOIN BADBADM.AA_ADDRESS AD ON X.DIR_ID_NO = AD.ADDR_ID_NO;









"Sams, Debbie" @RYCI.COM> on 12/19/2000 07:45:48 AM

Please respond to DB2 Data Base Discussion List [...] 9788 17 28_WLM Application Environments16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Tue, 19 Dec 2000 09:28:16 -0500491_iso-8859-1 In reading the Doc on Application Environments, it states that you can have multiple APPLENV's. How do you specify them? In the DSNWLM proc, it looks like you can only specify one APPLENV name

Michael Dempsey UNC Health Care System

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9806 179 33_VIEW Performance and VIEW EXPLAIN22_Bertolino, Salvatore S26_SBertolino@MAIL.SBIC.CO.ZA31_Tue, 19 Dec 2000 16:20:55 +0200403_iso-8859-1 Hi All,

I have encountered an interesting scenario. We have a package installed on db2 V6 that creates a view where the SELECT FROM clause contains more than 15 tables (before the apar was applied reducing max to 15 tables), after the view is created then all the data from the view is selected after which the view is dropped. The select runs for about 5 mins before completing. [...] 9986 49 32_Re: WLM Application Environments12_Kirk Hampton16_khampto1@TXU.COM31_Tue, 19 Dec 2000 08:33:38 -0600569_us-ascii APPLENV is an OS/390 Workload Manager entity, they are defined in the WLM policy by your OS/390 sysprog. You can set up as many APPLENV's as you wish, but you can only point to one APPLENV per Stored Proc address space. You set up multiple copies of the DSNWLM proc, and name them all differently. We named ours ssssWLPn, where ssss is the SSID of the DB2 that the stored procedures run in, WLP stands for Work Load manager established stored Procedure, and n is a number to separate multiple environments within the same DB2. The APPLENV's are named the [...] 10036 81 54_Bind Package from MVS with MVS and AIX SQL in same pgm12_Doug Kestner23_dhsts57@DHS.STATE.IL.US31_Tue, 19 Dec 2000 08:32:27 -0600571_- Hello all, we have been experiencing problems with BIND PACKAGE commands from MVS to a remote AIX server when the cobol program has both MVS and AIX SQL within the program. More detail below.

To start with, we have Cobol programs that have SQL calls to OS/390 tables AND UDB tables on AIX and are having trouble getting the PACKAGE bound, locally or remotely. We have done tests where we have a cobol program with only SQL calls referencing the UDB tables on AIX. We are able to BIND the remote package successfully and run the program, returning rows from [...] 10118 129 19_Re: SQL Access Path13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Tue, 19 Dec 2000 09:12:49 -0600407_iso-8859-1 Richard,

If you are still having problems convincing the design team, then you may be able to sway them with old school logic. If I was to read a sequential file into a batch program, would I apply all criteria against the data in one pass, or apply some to the first pass and do a second pass to apply the remainder? Simple answer : 1 pass. The SQL is the same, just not as extreme. [...] 10248 109 58_Re: Bind Package from MVS with MVS and AIX SQL in same pgm11_David Nance16_DWNance@FHSC.COM31_Tue, 19 Dec 2000 10:16:00 -0500551_US-ASCII Doug, There may be a better method, but we have always done a bind on both systems for the package using sqlerror(continue). You will get errors for the objects that do not exist on that DB instance, but that's as it should be.

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

>>> dhsts57@DHS.STATE.IL.US 12/19/00 09:32AM >>> Hello all, we have been experiencing problems with BIND PACKAGE commands from MVS to a remote AIX server when the cobol program has both MVS and AIX SQL within the program. More detail below. [...] 10358 54 37_Binding from OS/390 to DB2 UDB on AIX14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Tue, 19 Dec 2000 07:42:47 -0800316_iso-8859-1 Doug, What version of DB2 UDB on AIX are you using? V7.1 is the release you

need that supports sqlerror continue. V6.1 and below do not. If you are

6.1 and below you can use dynamic sql or create copies of the mvs tables on

AIX to get the bind to work.





HTH Phil 10413 99 32_Re: WLM Application Environments14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Tue, 19 Dec 2000 07:45:28 -0800451_iso-8859-1 Michael, In WLM You need to specify them via the WLM TSO panels. There is a good redbook out on this. I believe it is something like Implementing WLM. I don't have my old notes with me but you should be able to find it at www.redbooks.ibm.com. -Phil

-----Original Message----- From: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Sent: Tuesday, December 19, 2000 9:28 AM To: DB2-L@RYCI.COM Subject: WLM Application Environments [...] 10513 106 8_Re: ADO!14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Tue, 19 Dec 2000 07:53:32 -0800315_iso-8859-1 David, There is a book out entitled, DB2 Universal Database in Application Environments, ISBN Tetsuya Shirai, et al / Prentice Hall / 2000 / 0130869872. The Application Development Cert guide is due to be published any day. There also used to be some samples on the web off the db2 udb page. --Phil [...] 10620 62 38_Re: V6 OS/390 Distinct(case gives -12712_Kirk Hampton16_khampto1@TXU.COM31_Tue, 19 Dec 2000 09:57:44 -0600499_us-ascii Is your "TABLEX" actually a view containing another DISTINCT ?











"VCEI," on 12/19/2000 03:28:42 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Kirk Hampton/Texas Utilities) Subject: V6 OS/390 Distinct(case gives -127





Hello List(en)ers,

DB2 V6 on OS/390

This query returns a -127 sqlcode (more than 1 distinct in a query). [...] 10683 16 41_Re: Binding from OS/390 to DB2 UDB on AIX12_Doug Kestner23_dhsts57@DHS.STATE.IL.US31_Tue, 19 Dec 2000 09:58:59 -0600298_- we are using os/390 ver 6 as the application requestor and udb version 6.1 as the application server. we are fortunate enough to have udb version 7.1 EEE installed on another AIX node. I think we will try to connect to that node and see if everything works.

thanks for the lead. doug [...] 10700 17 32_DDL Generation via DB2 Estimator14_Raja, Shital S19_shital.raja@EDS.COM31_Tue, 19 Dec 2000 11:00:31 -0500482_iso-8859-1 I imported all Tables, indexes and related stats into DB2 Estimator. It is not readily apparent to me, how to go about generating DDL for export. Does anybody have any insight? Your reply is highly appreciated.

Shital Raja

================================================ 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. 10718 140 38_Re: V6 OS/390 Distinct(case gives -1275_VCEI,14_VCEI@CEGEKA.BE31_Tue, 19 Dec 2000 17:50:40 +0100575_iso-8859-1 Excellent idea ! But, no it isn`t. It is a basic view on one table (select all columns from TABLEX).

Still, brilliant idea because, (hadn`t thought of this myself...) when I replace the view by the table... It works !! At least... It `s working better. We`re not home, yet:

DOESN`T WORK---(SQLcode -127)---------------: SELECT COL1 FROM VIEWX WHERE COL1 <> ` ` GROUP BY COL1 HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) > 1 (I can only see one DISTINCT, and believe me, I`ve triple checked the view) [...] 10859 39 0_13_Steve Higgins23_Steve_Higgins@BCBST.COM31_Tue, 19 Dec 2000 12:09:43 -050024_iso-8859-1 Signoff DB2-L 10899 117 58_Re: Bind Package from MVS with MVS and AIX SQL in same pgm13_Vikram Khatri23_Vikramk@RENEWAL-IIS.COM31_Tue, 19 Dec 2000 12:12:52 -0500393_iso-8859-1 I do not know much about DB2 on OS/390 but we did similar POC work on UDB on Sun and NT and we had used Type 2 connection for distributed computing (bind).

Vikram

-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Tuesday, December 19, 2000 10:16 AM To: DB2-L@RYCI.COM Subject: Re: Bind Package from MVS with MVS and AIX SQL in same pgm [...] 11017 73 23_Re: Db2 connect problem14_Bilal YILDIRIM25_byildirim@HALKBANK.COM.TR31_Tue, 19 Dec 2000 20:08:41 +0200478_iso-8859-1 I Have tried putting single or double quotes around my select statement.















To: DB2-L@RYCI.COM cc: Subject: Db2 connect problem

I am using db2 connect v5.0 and connecting to OS/390 db2 5.1 via Sna server but in the client side I connect to db2 succesfully but when I select it says :

connect to raptor user db2sys1 ------------------------------------------------------------------------ [...] 11091 50 24_Simple buffer pool doubt8_ritu zee22_ritu_98_2000@YAHOO.COM31_Tue, 19 Dec 2000 10:19:46 -0800370_us-ascii Hi !

I have a simple question about buffer pools. In this wealth of information provided below, how can I find out no. of 'Synchronous random reads'. The information provided below is from CA Insight.

Getpage total reqs 12434791 Getpage seq access reqs 16430 Synch read reqs 10948 Synch read via seq access 303 Getpage/sync read ratio 1135.8 [...] 11142 53 21_OS/390 DB2 V6.1 parms12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Tue, 19 Dec 2000 12:13:00 -0500518_- Any bad experiences with new parms, these or some not listed, at your shop? Panel Descriptions: CONTRACT THREAD STG===> NO Periodically free unused thread stg LIMIT BACKOUT ===> AUTO Limit backout processing. AUTO, YES, NO BACKOUT DURATION ===> 5 Checkpoints processed during backout if LIMIT BACKOUT = AUTO or YES. 0-255. RO SWITCH CHKPTS ===> 5 Checkpoints to read-only switch. 1-32767 RO SWITCH TIME ===> 10 Minutes to read-only switch. 0-32767 LEVELID UPDATE FREQ===> 5 Checkpoints between updates. 0-32767 [...] 11196 51 44_db2connect ee trusted connections to mvs/db213_Olson, Carlos14_COlson@QRS.COM31_Tue, 19 Dec 2000 10:40:39 -0800402_iso-8859-1 I found the note below in the archives regarding trusted client connections to OS390 DB2 using DB2 Connect; however, I need to know how this can be implemented if using TCP/IP as your protocol in the Client Configuration Assistant. Any help would be greatly appreciated,

Carlos Olson Database Administrator

QRS Corporation 1400 Marina Way South Richmond, California 94804 [...] 11248 22 68_Binding CLI Packages for Distributed Applications Using Dynamic S QL13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Tue, 19 Dec 2000 13:09:39 -0600692_iso-8859-1 Trying to confirm whether an assumption I'm under is correct. We're using DB2 Connect to connect to the mainframe. It's my understanding that when binding the CLI packages in DB2/OS390, you cannot specify "DYNAMICRULES(BIND)" as a bind option, because (according to the Command Reference Manual) this option cannot be used for binding packages that would include SQL statements that cannot be dynamically prepared. This would include, amoung other things, "SET CURRENT PACKAGESET" and "CONNECT". Unless I'm missing something, any web or client server application program accessing DB2/OS390 is going to execute the CONNECT statement, thereby eliminating the option of binding [...] 11271 102 23_Re: Db2 connect problem14_Scott Saunders20_ssaunders@SIEBEL.COM31_Tue, 19 Dec 2000 11:14:15 -0800647_iso-8859-1 Have you tried specifying columns rather than using the *?

Scott Saunders

-----Original Message----- From: Bilal YILDIRIM [mailto:byildirim@HALKBANK.COM.TR] Sent: Tuesday, December 19, 2000 10:09 AM To: DB2-L@RYCI.COM Subject: Re: Db2 connect problem



I Have tried putting single or double quotes around my select statement.















To: DB2-L@RYCI.COM cc: Subject: Db2 connect problem

I am using db2 connect v5.0 and connecting to OS/390 db2 5.1 via Sna server but in the client side I connect to db2 succesfully but when I select it says : [...] 11374 31 18_Expanding a column11_Eric Robida22_Eric_Robida@BCBSME.COM31_Tue, 19 Dec 2000 14:13:43 -0500546_us-ascii I have a bunch of questions regarding expanding a column for this exalted forum. Here is what I need to do: expand a char(10) column to char(13). Any existing data can remain as char(10) with spaces. This column exists in about 30 or so tables. The tables range from 100,000 - 45 million rows each. I am looking for an approach which will take a minimum of application changes. This is what I think will work: 1) DBA does the drop/create table. 2) We load the old data back into the tables using the load utility. I assume that the [...] 11406 31 32_cleaning up obsolete packages...15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Tue, 19 Dec 2000 13:34:18 -0600556_iso-8859-1 Dear List,

I have sizable number of obsolete packages and need to rid my catalog/directory off them. I have been musing with the following four steps and wonder if there are any holes in it...

STEP 1) prepare rebind statements for all packages (rows that show up) in sysibm.syspackage where operative = 'Y' and valid in ('A', 'N') STEP 2) execute above rebind statements STEP 3) prepare free package statements for all packages in sysibm.syspackage where operative = 'N' and valid = 'N' STEP 4) execute above free statements. [...] 11438 49 45_SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.011_Jakobs, Jef18_JJakobs@HARTIC.COM31_Tue, 19 Dec 2000 13:34:29 -0600379_iso-8859-1 I've run into a strange problem running a very simple sql-statement on a DB2 UDB 6.1 database (fixpak 6 on NT 4.0):

select * from sysibm.sysdummy1 where (1 = 1 or 1=2) and (1 = 1 or (1=2 and 2=2))



The problem is that this query retrieves no rows... Selecting from sysibm.sysdummy1 itself (without the where clause) does work. I get one row. [...] 11488 52 28_Re: Simple buffer pool doubt14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 19 Dec 2000 14:40:12 -0500449_ISO-8859-1 I haven't used Isight for a while, but if the names shown below are correct, Subtract B from A below. Your guess for Random Getpages is correct.

Regards, Joel



Message text written by DB2 Data Base Discussion List > I have a simple question about buffer pools. In this wealth of information provided below, how can I find out no. of 'Synchronous random reads'. The information provided below is from CA Insight. [...] 11541 66 36_Re: cleaning up obsolete packages...16_Peddycoart, Judy22_peddycoa@UILLINOIS.EDU31_Tue, 19 Dec 2000 13:45:47 -0600598_iso-8859-1 Tonmoy,

I am in the process of testing a program right now that will do what you are discussing. I approached it from a little different angle, though. We are also concerned with controlling the number of packages that are created in our development environment during testing, as well as the number of "old" packages that may exist in production. We decided that three versions of any package should be sufficient to retain in any of our environments (that allows you to move in a new version, retain the current version for fall back, and one more for insurance purposes). [...] 11608 71 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 19 Dec 2000 14:41:32 -0500397_iso-8859-1 This works correctly on DB2/390 V5.1. I will try it at home on UDB PDE V7 running on Win2K and UDB EEE Beta on Linux.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Jakobs, Jef [mailto:JJakobs@HARTIC.COM] Sent: Tuesday, December 19, 2000 2:34 PM To: DB2-L@RYCI.COM Subject: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0 [...] 11680 178 22_Re: Expanding a column14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 19 Dec 2000 14:46:54 -0500535_iso-8859-1 OS/390?

Continue Else Exit End



a) DB2 Load Utility will not do as you expect unless you change the 'syspunch' dataset (data map) to account for the extra bytes. This file can be created during the unload via DSNTIAUL and modified before the load process.

b) Inserts should be fine. In fact, some say this is a good way to perform the change... 1) Create table like original(copy) 2) load or insert to copy 3) Drop original and make change 4) Insert to New table, Values(select * from copy) [...] 11859 44 22_Re: Expanding a column11_Eric Robida22_Eric_Robida@BCBSME.COM31_Tue, 19 Dec 2000 15:11:57 -0500554_us-ascii ER:Yes it is on OS/390 V5.2.



a) DB2 Load Utility will not do as you expect unless you change the 'syspunch' dataset (data map) to account for the extra bytes. This file can be created during the unload via DSNTIAUL and modified before the load process.

ER: So the it sounds like I will need to run the files through a program to pad it. What happens when the load parm specifies that the data field in the load file is char(10) and the table column is defined as char(13)? I am not a DBA btw, so forgive my ignorance. [...] 11904 52 44_$125 Sessions at the DB2 Symposium Jan 29-3112_Susan Lawson22_lawson_susan@YAHOO.COM31_Tue, 19 Dec 2000 12:21:13 -0800623_us-ascii $125 1-day sessions at the DB2 Symposium!!!

In celebration of our new Springfield, IL - USA office, YL&A is offering all full day sessions at the DB2 Symposium in Springfield, IL - January 29-31st at 125$ per student per full day session.

1-Day Sessions include:

* Adv Prg: Triggers,UDFs & Stored Procs - Richard Yevich * SQL Design, Performance and Tuning - Terry Purcell * DB2 UDB Unix/NT/Linux Design and Performance - Scott Hayes * Data Warehousing: Design, Performance and Tuning - Richard Yevich * Advanced and Complex SQL - Terry Purcell * DB2 V6/V7 Impacts on Performance - Susan [...] 11957 30 22_Re: Expanding a column16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Tue, 19 Dec 2000 13:22:02 -0700504_- ========= > a) DB2 Load Utility will not do as you expect unless you change the > 'syspunch' dataset (data map) > to account for the extra bytes. This file can be created during the > unload via DSNTIAUL and > modified before the load process. =========== > ER: So the it sounds like I will need to run the files through a program > to pad > it. What happens when the load parm specifies that the data field in the > load > file is char(10) and the table column is defined as char(13)? I am not a [...] 11988 197 22_Re: Expanding a column13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Tue, 19 Dec 2000 14:26:02 -0600454_us-ascii On running the DB2 Load Utility, it will run successfully if the jcl you have set up defines the limits of the character column either by defining the starting and ending position or by defining the starting position and the length of the character input field. We have done this many times. When the programmer has changed the load file to increase the length of the input data, we then change the jcl to account for the increased length. [...] 12186 229 22_Re: Expanding a column14_Scott Trometer22_scott.trometer@RCI.COM31_Tue, 19 Dec 2000 15:31:24 -0500441_iso-8859-1 Right. You have to account for the three extra bytes somewhere before you load it to the changed table. Another alternative is to use SQL in the unload to pad the column with 3 spaces. This creates an unload file that will be in 'load' format.

-----Original Message----- From: Helen Johnson [mailto:helen_johnson@RAC.RAY.COM] Sent: Tuesday, December 19, 2000 3:26 PM To: DB2-L@RYCI.COM Subject: Re: Expanding a column [...] 12416 94 36_Re: cleaning up obsolete packages...15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Tue, 19 Dec 2000 14:47:10 -0600478_iso-8859-1 Judy,

I am doing the rebind in the begining of the process for a reason. Through the rebinds, I want to force the packages to get a value of 'N' in the column "OPERATIVE" of sysibm.syspackage when the SQL statements in the package does not tally with the existing database. We have our zparms set for autorebind...and by these rebinds I am targeting those packages which never was allocated (executed) after being invalidated. I hope I am making sense... [...] 12511 174 22_Re: Expanding a column17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Tue, 19 Dec 2000 14:55:20 -0600475_iso-8859-1 There seems to be some confusion here.

Let's say your LOAD command looks like:

LOAD REPLACE INTO TABLE MY.TABLE (FLD1 POSITION(1:15) CHAR (15) ,FLD2 POSITION(16) VARCHAR ,FLD7 POSITION(100:109) CHAR (10) )

If the field in question is FLD7 it will load JUST FINE into a CHAR(13) column. The LOAD command describes the INPUT DATA, not the column in the table. The LOAD utility figures out that the column is CHAR(13) and pads on the right. [...] 12686 152 36_Re: cleaning up obsolete packages...13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 19 Dec 2000 13:07:15 -0800506_iso-8859-1 Tonmoy,

Before doing anything, you might want to check the VALIDATE option on your existing packages and ensure it is VALIDATE(BIND). If your packages are bound with VALIDATE(RUN), I suspect that after you do a Rebind, all your Invalid packages will actually become valid and operative (rather than becoming invalid and inoperative as you hope them to be) even if the underlying database objects do not exist. This obviously defeats your entire purpose of carrying out the exercise. [...] 12839 93 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.012_Kirk Hampton16_khampto1@TXU.COM31_Tue, 19 Dec 2000 15:29:57 -0600425_us-ascii I got this to return one result row on both UDB for OS/390 v6.1, and UDB EEE v6.1 on Win/NT 4.0 at FP3.









"Pearson, Eric L," on 12/19/2000 01:41:32 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Kirk Hampton/Texas Utilities) Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0 [...] 12933 172 36_Re: cleaning up obsolete packages...15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Tue, 19 Dec 2000 15:51:10 -0600539_iso-8859-1 Ashish,

My FREE statements would not get generated for VALIDATE(RUN) cases you cite ...which would have been OK with me...however I will incorporate your suggestion into the predicate of by REBIND generating SQL to save on rebind time (almost 1% of my invalidated packages have VALIDATE = 'R').

Appreciate your input.

-----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Tuesday, December 19, 2000 3:07 PM To: DB2-L@RYCI.COM Subject: Re: cleaning up obsolete packages... [...] 13106 32 36_Re: DDL Generation via DB2 Estimator13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Wed, 20 Dec 2000 09:34:25 +1100513_iso-8859-1 As far as I know DB2 Estimator does not generate DDL, at least not the version I have.

-----Original Message----- From: Raja, Shital S [mailto:shital.raja@EDS.COM] Sent: Wednesday, 20 December 2000 3:01 To: DB2-L@RYCI.COM Subject: DDL Generation via DB2 Estimator



I imported all Tables, indexes and related stats into DB2 Estimator. It is not readily apparent to me, how to go about generating DDL for export. Does anybody have any insight? Your reply is highly appreciated. [...] 13139 58 36_Re: cleaning up obsolete packages...15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 20 Dec 2000 09:35:13 +1100429_- Hi Tonmoy,

Every now and then I do what you're thinking of. Most of the places I've worked try to avoid validate(run) so I've always quickly found the packages that can safely be freed. If it's only 1% at your place you could always take the position that a validate(run) package that's valid and operative, even though it might refer to objects that don't exist, is acceptable. After all, remember the 80/20 rule. [...] 13198 54 36_Re: DDL Generation via DB2 Estimator13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 19 Dec 2000 14:47:47 -0800569_us-ascii DB2LOOK might be a utility to look at for generating DDLs.

Thanks. Ashish.

-----Original Message----- From: LOMBARD,Shaun [SMTP:Shaun.LOMBARD@DEWRSB.GOV.AU] Sent: Tuesday, December 19, 2000 2:34 PM To: DB2-L@RYCI.COM Subject: Re: DDL Generation via DB2 Estimator

As far as I know DB2 Estimator does not generate DDL, at least not the version I have.

-----Original Message----- From: Raja, Shital S [mailto:shital.raja@EDS.COM] Sent: Wednesday, 20 December 2000 3:01 To: DB2-L@RYCI.COM Subject: DDL Generation via DB2 Estimator [...] 13253 101 36_Re: cleaning up obsolete packages...13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Tue, 19 Dec 2000 14:56:52 -0800594_iso-8859-1 Probably my response didn't make it so I am sending it again...

One more thing actually.... If the owner of the package 'looses' authority (ie authority has been revoked by someone) to execute any static SQL in the package, the package will immediately become invalid and then, if the package is bound with VALIDATE(BIND), as in your case, a Rebind of the package will make it inoperative. Probably you would not want to free such packages as the underlying database object is still there; it is only an authority problem and I think your rebinds in such cases will fail [...] 13355 122 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.011_Jakobs, Jef18_JJakobs@HARTIC.COM31_Tue, 19 Dec 2000 16:55:15 -0600361_iso-8859-1 Thanks Eric,

I've narrowed it down a bit.

Getting results with DB2 UDB 6.1 (NT 4.0) without fixpak Also getting results with fixpak 3, 4 and 5 (don't have 1 & 2).

As soon as I install fixpak 6 I'm dead. Must be an optimizer thingy.

Will inform IBM (and fallback to FP5) So far I've only hurt our development group... [...] 13478 118 36_Re: cleaning up obsolete packages...13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Wed, 20 Dec 2000 10:12:18 +1100417_iso-8859-1 At another site we tackled the package cleanup process by looking for the contoken in the load library. If the contoken can not be found then there is no way the package can run - so it gets free'd.

Shaun

-----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Wednesday, 20 December 2000 9:57 To: DB2-L@RYCI.COM Subject: Re: cleaning up obsolete packages... [...] 13597 71 36_Re: DDL Generation via DB2 Estimator14_Raja, Shital S19_shital.raja@EDS.COM31_Tue, 19 Dec 2000 18:23:30 -0500401_iso-8859-1 Thanks Ashish, You know if DB2Look is a downloadable utility or what? if yes, from where can I download ? Thanks Shital

-----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Tuesday, December 19, 2000 4:48 PM To: DB2-L@RYCI.COM Subject: Re: DDL Generation via DB2 Estimator



DB2LOOK might be a utility to look at for generating DDLs. [...] 13669 60 59_Re: Bind Package from MVS with MVS and AIX SQL in same pg m14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 20 Dec 2000 11:13:43 +1100527_iso-8859-1 Another, off the top, possibility is to create suitable objects so that the binds do work. You don't have to populate them or have indexes - just the objects.

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



-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Wednesday, December 20, 2000 2:16 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Bind Package from MVS with MVS and AIX SQL in same pgm [...] 13730 91 66_FW: New Redbook Release! : SG24-6128-00 DB2 UDB V7.1 Porting Guide10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Tue, 19 Dec 2000 18:29:54 -0600646_- > ** Original Subject: FW: New Redbook Release! : SG24-6128-00 DB2 UDB V7.1 Porting Guide > ** Original Sender: "Redbooks at IBM" > ** Original Date: 19 Dec 00 17:17:45 -0500

> ** Original Message follows...

>Form Number: SG24-6128-00 > > Title: DB2 UDB V7.1 Porting Guide > > > Can now be downloaded as a PDF file and ordered in hardcopy two weeks > following the Publish Date! (see > http://ibm.com/redbooks/abstracts/sg246128.html for online access) > > This IBM Redbook is intended to help database administrators and system > designers perform database and application conversion from Sybase to DB2 [...] 13822 101 99_FW: New Redbook Release! : SG24-6130-00 Integrating XML with DB2 XML Extender and DB2 Text Extender10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Tue, 19 Dec 2000 21:00:14 -0600631_- > ** Original Subject: FW: New Redbook Release! : SG24-6130-00 Integrating XML with DB2 XML Extender and DB2 Text Extender > ** Original Sender: "Redbooks at IBM" > ** Original Date: 19 Dec 00 20:48:18 -0500

> ** Original Message follows...

>Form Number: SG24-6130-00 > > Title: Integrating XML with DB2 XML Extender and DB2 > Text Extender > > > Can now be downloaded as a PDF file and ordered in hardcopy two weeks > following the Publish Date! (see > http://ibm.com/redbooks/abstracts/sg246130.html for online access) > > This IBM Redbook shows how to use XML technology efficiently in [...] 13924 40 19_BIND NOT SUCCESSFUL12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Tue, 19 Dec 2000 20:56:12 -0600620_- Hi guys:



I got a question. I just trying to execute a bind but failed because it try to reference one csect DSNHSMS1 and it couldn't find it. Please if someone has seen this problem before I would appreciate your help about it. Thanks and regards,

harbry



DSNT201I # BIND FOR PLAN DSQ6BSQL NOT SUCCESSFUL DSN BIND PLAN(DSQ6BINS) MEMBER(DSQ0BINS) ISOLATION(CS) DSNX200I # BIND SQL ERROR USING DBAMHA AUTHORITY PLAN=DSQ6BINS DBRM=DSQ0BINS STATEMENT=1188 SQLCODE=-104 SQLSTATE=42601 TOKENS=QSTRING : CSECT NAME=DSNHSMS1 RDS CODE=0 DSNT201I # BIND FOR PLAN DSQ6BINS NOT SUCCESSFUL [...] 13965 62 23_Re: BIND NOT SUCCESSFUL10_Rob Wright17_rwright@LIC.CO.NZ31_Wed, 20 Dec 2000 16:17:17 +1200632_us-ascii Did your pre-compile work successfully, or did you get SQL errors?









HARBRY ARIZA on 12/20/2000 02:56:12 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Rob Wright/ham/LIC)

Subject: BIND NOT SUCCESSFUL





Hi guys:



I got a question. I just trying to execute a bind but failed because it try to reference one csect DSNHSMS1 and it couldn't find it. Please if someone has seen this problem before I would appreciate your help about it. Thanks and regards, [...] 14028 61 23_Re: BIND NOT SUCCESSFUL13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Wed, 20 Dec 2000 14:21:20 +1100298_- Harbry,

What this message is trying to tell you is that you got an SQLCODE -104. So statement number 1188 in DBRM DSQOBINS has an illegal symbol "QSTRING" and DB2 is expecting the ":" symbol. I would say you have a program that does not have a colon(:) before the host variable name. [...] 14090 77 23_Re: BIND NOT SUCCESSFUL16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 20 Dec 2000 09:41:40 +0530571_- It is simply the Sql Syntax error for the sql written in statement number 1188. Please check the sql for its syntax.

HTH Regards Sanjeev > -----Original Message----- > From: HARBRY ARIZA [mailto:harbry_a@HOTMAIL.COM] > Sent: Wednesday, 20 December 2000 13:56 > To: DB2-L@RYCI.COM > Subject: BIND NOT SUCCESSFUL > > > Hi guys: > > > I got a question. I just trying to execute a bind but failed because > it try to reference one csect DSNHSMS1 and it couldn't find it. Please if > someone has seen this problem before I would appreciate your help about > it. [...] 14168 98 19_Re: SQL Access Path16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 20 Dec 2000 10:43:41 +0530523_- Hi Terry, Thanks for pointing out some wrong messages delivered by me. I overlooked few things like INDEX B1 is used for the 2nd query, i thought INDEX B2 is used with matchcols of 2. However INDEX B1 is more logical. Considering INDEX B2 is used, there is one thing which i want to clarify on the basis of which i gave few suggestions. What if the STATUS value D and V are closely located and there are a lot of records containing both ? Is there any chances that the dynamic prefetch be kicked of on the indexes ? [...] 14267 95 37_Re: VIEW Performance and VIEW EXPLAIN16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 20 Dec 2000 11:50:18 +0530342_- Hi Sal,

> 1.) When I create the view and run the select from the view, the select > statement runs for about 5 mins again.(which is expected, interesting > thing is that in the omegamon the statement is using quite a lot of CPU > and only does about 350 getpages) Answer : It depends on the select statement of the create view. [...] 14363 23 23_Re: BIND NOT SUCCESSFUL12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM31_Wed, 20 Dec 2000 00:17:45 -0600387_- Hi Guys:



Thanks for your help but I found the reason why my bind was failing.I'm installing QMF (one of the older version v2r4) on one of my db2 subsystem (v6r1) and now you must specify colon(:) on all your host variables. Before v6 you would be able to do it but since db2 version 6 and version 7 you must use colon with your host variables. Thanks and regards , [...] 14387 97 22_Re: Expanding a column15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Wed, 20 Dec 2000 09:32:04 +0100585_iso-8859-1 Just putting in my two cents..

All of my previous listers touched the ins and outs of the load and unload. I do have a design question, does this expansion have to do with expanding use of the attribute? Or is this attribute used in another way. You mentioned hte fact that applications will still use 10 character strings, this looks to me like extra functionality is added int this column. Recently we had a lot of trouble in coding and SQL because two attributes were combined in one attribute by expanding this particular attribute. Take good care you don't [...] 14485 120 22_Re: Expanding a column14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Wed, 20 Dec 2000 09:43:54 +0100323_us-ascii Hi Eric,

in your original mail you mentioned Drop & create being done by the DBAs; before I'd fiddle around with Load-Statements I'd rather go and ask them if they have some Change-Tools, be it BMC ChangeManager or other tools, because these tools automatically do just what you intend to do manually. [...] 14606 74 22_Re: Expanding a column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Wed, 20 Dec 2000 14:25:59 +0530537_- Jaap, You made a very valid point but this is applicable when you are combining two different business attribute to a single one or adding a new business attribute to the existing one. We are not sure what is the situation Eric have related to this thread. Still i need to say some good amount of impact analysis required for both DB design as well as application design. If this column is also part of some key then the impact on data modelling can be there. Another point which i would like to make that check all the WHERE and [...] 14681 32 53_REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Dec 2000 12:12:41 +0100419_us-ascii One year ago I posted a mail regarding REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME,

i.e. I was searching a method to execute a reorg a tablespace with SHRLEVEL REFERENCE option WITHOUT

executing a concurrent image copy via SYSCOPY DDNAME in the same jobstep.

One year ago it was not possible, but now is it still true ? I tried to find if there's any new PTF to correct this annoyance, [...] 14714 25 43_Web access to DB2 on S390 in the real world14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 20 Dec 2000 12:50:50 -0000459_iso-8859-1 Hi all,

This is really only aimed at subscribers in the UK.

CA are starting work on the DB2 seminars for 2001 and will be kicking off with one considering performance of DB2 applications in the new world where everything starts with an "e-".

I'd be interested to talk/email with any UK users who are actively web enabling access to data held in DB2 on S390 with a view to participating (even anonymously) in this seminar. [...] 14740 127 22_Re: Expanding a column11_Eric Robida22_Eric_Robida@BCBSME.COM31_Wed, 20 Dec 2000 08:28:28 -0500549_us-ascii No, we are not combining two values. What is happening is we are going through a system consolidation. The new system uses a 13 character string as an identifier. However the systems being replaced could be stored in 10 characters. All systems are not being phased out at once. So we will have a mix of 10 character and 13 character data. What the data represents is not changing, just the amount of space it will take up. The other option is to not expand the tables and store a 10 character dummy value and use some x-ref lookups to [...] 14868 52 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 20 Dec 2000 07:54:17 -0600620_ISO-8859-1 Hi Max, Still true. See DB2 UDB for OS390 Utility Guide, 2.16.1.2, Option Descriptions: "COPYDDN(SYSCOPY) is assumed, and a DD statement for SYSCOPY is required if: You specify REORG SHRLEVEL REFERENCE or CHANGE, and do not specify COPYDDN"

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 [...] 14921 21 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Dec 2000 15:07:51 +0100472_us-ascii Hi Rick, thanks for your reply.

I read the manual, but I hoped to find a new PTF developed by IBM to bypass the 'problem' (vain hope..)

For my job it's a real annoyance.

Kind Regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 14943 73 20_Re: Db2 reorg online16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 20 Dec 2000 09:21:01 -0500573_iso-8859-1 Why are you so concerned about the elapsed time? That is not the problem Online Reorg attempts to solve. BMC, Platinum, CDB (and others) minimize the required outage by more efficient I/O etc. They still have an outage (17 minutes in your case). Online Reorg does not seek to minimize the outage by reducing elapsed time, but by reducing the time data is not available. The 'not available' time is typically a few seconds or so (maybe even a minute) of RO during the last log apply, and a few seconds (1-5 seconds per dataset) while the datasets and shadow [...] 15017 124 36_Re: cleaning up obsolete packages...15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Wed, 20 Dec 2000 08:44:15 -0600482_iso-8859-1 Ashish,

You have a valid point and fortunately I had considered to leave the packages with -551 alone among several others. After doing the rebinds I gathered all the sqlcodes received to a file for analysis. I found that 99.95% of 50,000 odd negative sqlcodes were related to obsolosence (-204, -206). For those packages I got dicey SQLCODES I left alone. My frees last night got rid of 20% of the total packages. I wish losing weight was this easy :-) .... [...] 15142 118 36_Re: DDL Generation via DB2 Estimator13_Adrian Savory27_Adrian.Savory@WORKTHING.COM31_Wed, 20 Dec 2000 14:56:03 -0000417_iso-8859-1 Shital,

DB2LOOK is a command that you can use with DB2 UDB (Unix/NT, etc.) to generate table definitions.

On a similar note, how does everybody compare UDB database structures in different instances? So far I've tried comparing db2look output or comparing the results of catalog queries. Neither is really very satisfactory.

Come back Migrator/Change Manager, all is forgiven :-( [...] 15261 145 36_Re: DDL Generation via DB2 Estimator15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Wed, 20 Dec 2000 09:37:53 -0600334_iso-8859-1 Well there IS a Change Manager for UDB! Similar function to the OS/390 version (including compare) but with beefed up ability to propagate changes to multiple (eg 100s) of targets.

Andy Lankester BMC Sofwatre Ltd

PATROL DB CHANGE MANAGER for UDB for xxxx where xxxx is major Unixes and NT [...] 15407 139 27_UDB Compare/Migrate Utility9_Bob Lewis23_BLewis@MICROGENERAL.COM31_Wed, 20 Dec 2000 07:38:46 -0800461_iso-8859-1 We've been using DB Artisan Change Manager. It's a little buggy with v7.1 of DB2/UDB on AIX, but for a thousand dollars or so, it's fantastic.

http://www.embarcadero.com/products/products.htm



Bob Lewis Scheming Contract DBA

-----Original Message----- From: Adrian Savory [mailto:Adrian.Savory@WORKTHING.COM] Sent: Wednesday, December 20, 2000 8:56 AM To: DB2-L@RYCI.COM Subject: Re: DDL Generation via DB2 Estimator [...] 15547 139 19_Re: SQL Access Path9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Wed, 20 Dec 2000 08:37:45 -0700615_iso-8859-1 Sanjeev,

I didn't get a chance to to analyze your query thourougly, but at a glance, based on you requirements I thought this might be a candidate for a possible rewrite using UNION ALL but as Terry mentioned this would bounce the query into "Stage-2'land" and perform the nasty sort. Another solution (if your shop is at least V5) is to use the CASE command. This would significantly improve performance because you would only make one pass through the predicate forest and remain in "Stage-1'land". This means you would probably invoke the wonderfull Matching Index scan and NOT invoke the [...] 15687 35 8_Re: ADO!15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Wed, 20 Dec 2000 07:43:42 -0800468_iso-8859-1 Check the box where UDB is installed. You should have a \SQLLIB\SAMPLES directory that has ADO & RDO subdirectories with sample code. Regards, Steve

-----Original Message----- From: Navid Khan [mailto:navid@DPI2.DPI.NET.IR] Sent: Tuesday, December 19, 2000 12:14 AM To: DB2-L@RYCI.COM Subject: ADO!



Hi all,

Who knows any valuable documents or sample codes about using ADO/RDO on DB2 UDB (for NT or specially VSE machines). [...] 15723 73 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_19_Tim.Lowe@STPAUL.COM31_Wed, 20 Dec 2000 10:23:52 -0600305_us-ascii Max, I talked to Jim Ruddy from IBM DB2 development about this at the 1999 DB2 Tech conference, and followed up via email just afterword, but I have not heard anything about this since then. I have assumed that my voice was only 1 in a crowd of other requests, and it has not gone anywhere. [...] 15797 22 54_Timeouts / Deadlocks on resource type D01.(DBID, OBID)12_Brian Picard24_brian_picard@HOTMAIL.COM31_Wed, 20 Dec 2000 10:22:01 -0600499_- Hi, We seem to have a number of occurrences of timeouts / deadlocks in our application. the reason code is C9008E or C90088 type is D01 Name is 362.289(DBID.OBID)

While I understand what this is, I am interested in knowing why this happens. When does a timeout or deadlock happen with reference to DBID and OBID?. The application is a Dynamic SQL application. Is it related to the shortage of space in EDM pool?. How do we fix this?. Would appreciate any feedback / suggestions. Brian [...] 15820 41 71_Re: Binding CLI Packages for Distributed Applications Using Dynamic SQL13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 20 Dec 2000 10:59:51 -0600322_US-ASCII Your assumption is correct. You must explicitly grant privileges to users running the CLI packages. Before DB2 UDB V6 OS390 and 3 part names we used CLI packages to access remote NT databases. We only use DYNAMICRULES(BIND) for static bound packages that have dynamic SQL embedded within them on OS390. Kurt [...] 15862 44 31_Loading/Logging/workfile errors15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU31_Wed, 20 Dec 2000 11:47:55 -0500370_iso-8859-1 UDB DBA's - We are trying to LOAD a Data Warehouse table from the mainframe onto UDB 7.1(AIX). The table holds about 1,000,000 rows (800 chars per row) and we continue to get errors when using the apply program.

Is it possible to issue COMMITS every Nth rows in UDB 7.1 ? (This should eliminate the Temp log files?) or Can we turn off logging(??) [...] 15907 155 36_Re: cleaning up obsolete packages...13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Wed, 20 Dec 2000 11:06:38 -0600426_iso-8859-1 I have used SAS to generate the rebind statements on our large development systems. If I rebind all invalid packages, the ones that fail become inoperative. After the rebind, all invalid and inoperative packages are freed. This way there is no need for a version check. In our environment, there isn't a number of versions we can apply. We have a sysadm do the rebind, so auth problems aren't a consideration. [...] 16063 96 35_Re: Loading/Logging/workfile errors21_Sean M O'Keefe/AC/VCU15_sokeefe@VCU.EDU31_Wed, 20 Dec 2000 12:09:55 -0500302_us-ascii Dick, No, you can't turn logging off. We are on UDB EE V5.2, but we are doing something similar, loading a datawarehouse (or datamarts) from legacy OS/390 DB2 tables. Since this is read only data, I'm also not archiving logs, BUT, I did size the logs large enough to accomdate the loads. [...] 16160 78 35_Re: Loading/Logging/workfile errors13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 20 Dec 2000 09:17:58 -0800317_iso-8859-1 No, I don't think you can COMMIT after every Nth row; but you have the SAVECOUNT option (expressed in number of records) which means that if you load fails in the LOAD phase and you restart it (after correcting the reason for it's failure) , it will restart from the last point it did a savecount on. [...] 16239 94 35_Re: Loading/Logging/workfile errors13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 20 Dec 2000 09:23:48 -0800651_iso-8859-1 Just a correction to my previous mail; interpretted it wrongly; NONRECOVERABLE turns logging off 'after' the load and not during it.

Thanks.

Ashish.

-----Original Message----- From: Mohan, Ashish Sent: Wednesday, December 20, 2000 9:18 AM To: 'DB2 Data Base Discussion List' Subject: RE: Loading/Logging/workfile errors

No, I don't think you can COMMIT after every Nth row; but you have the SAVECOUNT option (expressed in number of records) which means that if you load fails in the LOAD phase and you restart it (after correcting the reason for it's failure) , it will restart from the last point it did a [...] 16334 18 24_REORG SHRLEVEL REFERENCE12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 21 Dec 2000 20:10:59 +0200377_iso-8859-1 Hi Max, When you find the PTF - please let me know.... ;o))

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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16353 31 38_FW: DB2 71 on NT Authorization Problem13_Doctor, Nadir24_Nadir.Doctor@BGEHOME.COM31_Wed, 20 Dec 2000 13:49:17 -0500503_iso-8859-1 ndr

-----Original Message----- From: Dimitris Margaritis [mailto:margardi@NOVABANK.GR] Sent: Sunday, October 15, 2000 7:10 AM To: DB2-L@RYCI.COM Subject: DB2 71 on NT Authorization Problem



Hi all, I have just installed DB2 v 7.1 on my NT workstation using a local account1 from Administration Group. When I log on using another account2 on another domain I have no the authorization to backup the sample database. How can I give to account2 the SYSADM role? Thanks [...] 16385 98 43_Contents of package - local vs. remote bind11_James Szabo18_jim.szabo@CORE.COM31_Wed, 20 Dec 2000 13:45:29 -0500560_iso-8859-1 Environment: multiple DB2 UDB for OS/390 V6 systems, application programs written in COBOL.

We are trying to understand why the contents of SYSIBM.SYSPACKSTMT is different when you do a local bind vs. a remote bind.

If you bind a package into the same subsystem that you are running the bind command on, SYSIBM.SYSPACKSTMT contains all statements that need to be optimized (e.g. DEFINE CURSOR and singleton SELECT/INSERT/UPDATE/DELETE), as well as other executable statements that appear in the program (OPEN, FETCH, CLOSE, SET). [...] 16484 34 29_quarterly history of DB2 data0_22_Thomas_Abbott@HESC.COM31_Wed, 20 Dec 2000 14:14:37 -0500595_us-ascii We have in-house auditors, customers, etc that need a point-in-time snapshot of the database on a quarterly basis (going back 5 years). Our DBA's are responsible for COBOL programs that pull data off the DB2 tables and write each row to a flat file. Customers then use Passport for information retrieval and report writing against the flat files. Oracle is dropping support for the Passport products in the near future and we as DBA's would like to get out of the business of maintaining COBOL programs. We've had some demos by SAS, Cognos, and others but I'm getting signals that [...] 16519 43 8_DDF hung9_Colin Fay13_cfay2@CSC.COM31_Wed, 20 Dec 2000 14:37:06 -0500482_us-ascii Hi,

Re: OS/390 Db2 V 5.1

Situation:

- STO DDF MODE(FORCE) followed by -STA DDF completed sucessfully.

Db2mstr shows

DSNL006I -DB2P DDF STOP COMPLETE DSNL003I -DB2P DDF IS STARTING DSNL519I -DB2P DSNLILNR TCP/IP SERVICES AVAILABLE FOR DOMAIN ip3270.biw.com AND PORT 446 DSNL519I -DB2P DSNLIRSY TCP/IP SERVICES AVAILABLE FOR DOMAIN ip3270.biw.com AND PORT 5020

The second time around the STO command hung and will not complete. [...] 16563 76 12_Re: DDF hung15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 20 Dec 2000 13:45:19 -0600401_iso-8859-1 Hi, Colin,

Perhaps you didn't allow DDF enough time to complete its startup. Did you see this message anywhere?

DSNL004I @xxxx DDF START COMPLETE

Perhaps it is confused? You should be able to start and stop DDF without affecting the other STCs.

Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin [...] 16640 81 12_Re: DDF hung15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 20 Dec 2000 14:05:13 -0600444_iso-8859-1 After a little more research, it seems that DDF takes longer to restart after a STOP FORCE because it must resolve any indoubt threads. Also, another method to stop DDF is below:

"Stopping DDF using VTAM commands: Another way to force DDF to stop is to issue the VTAM VARY NET,INACT command. This command makes VTAM unavailable and terminates DDF. VTAM forces the completion of any outstanding VTAM requests immediately. [...] 16722 32 23_Buffer pool question...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Wed, 20 Dec 2000 12:26:27 -0800503_us-ascii Hi!

Have a basic question. I know that Virtual buffer pools occupy space in DBM1 address space and hiperpools occupy 'expanded storage'. Someone around here says that 'CMOS' storage is the latest type of storage and storage for virtual pools should be allocated in CMOS storage.

Could someone explain to me what 'CMOS' is and how is it least expensive? Also, 2 GB is the maximum buffer pool size; does transferring our virtual buffer pools to CMOS still have that 2GB limit? [...] 16755 57 27_Re: Buffer pool question...16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 20 Dec 2000 15:25:28 -0500519_iso-8859-1 CMOS is the chip architecure of the machine, not a type of memory. CMOS machines typically have more mips/$ and cost less (electricity, etc.) to run than their predecessors.

You will have the 2GB limit until there is an OS architecture upgrade (Z/OS?)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: ritu zee [mailto:ritu_98_2000@YAHOO.COM] Sent: Wednesday, December 20, 2000 3:26 PM To: DB2-L@RYCI.COM Subject: Buffer pool question... [...] 16813 59 33_Re: quarterly history of DB2 data19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Wed, 20 Dec 2000 14:32:21 -0600577_ISO-8859-1 Tom, I was going to try to suggest some things to you but after reading your email I realized I wasn't too sure what is where. You mention VSAM, DSNTIAUL, Oracle, COBOL, QMF for Windows, DB2 and flat files. These could be quite a mixed bag on several platforms. Could you be more specific about what is where? One question that can be asked is how are you establishing or determining the point-in-time? This would normally be a considerable effort for, what appears to be, an entire system unless, of course, you have the luxury of shutdown everything down and [...] 16873 31 12_SQLCODE -9040_22_Rohn.Solecki@MTS.MB.CA31_Wed, 20 Dec 2000 14:44:35 -0600494_us-ascii DB2 V6.1 OS390

We got the following -904 error recently in our MSTR log.

13:59:29 DSNT501I " DSNILMCL RESOURCE UNAVAILABLE 13:59:29 CORRELATION-ID=PT04S160 13:59:29 CONNECTION-ID=CORTEL 13:59:29 LUW-ID=* 13:59:29 REASON 00C9008E 13:59:29 TYPE 00000302 13:59:29 NAME x .00000834.X'000005'

Can anyone suggest how to translate the Name "x .00000834.x'000005'. I have it partially translated x is our DB name 00000834 = ???? x'000005' = table space data page (?) [...] 16905 36 39_DB2 Connect EE as Gateway Configuration16_Maculsay, Gary R28_Maculsay.Gary@EMERYWORLD.COM31_Wed, 20 Dec 2000 20:47:04 -0000365_iso-8859-1 I'm running into a problem that I'm sure is silly, but I can't seem to find any specific documentation to help me.

Important Facts:

Very little DB2/Unix knowledge is available.

We have installed DB2 Connect EE on a Solaris. After cataloging the necessary host information, I can connect to DB2/OS390 from the unix command line. [...] 16942 22 18_Table space % used17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US31_Wed, 20 Dec 2000 16:01:58 -0500541_iso-8859-1 Hello,

I am working with DB2 V6.1 on AIX. I am looking for a way to have DB2 notify me if a DMS table space reaches 90% capacity. Does anyone out there already do this? If so, how?

Regards,

John L. Breidenstine Computer Programmer 1 Legislative Data Processing Center

================================================ 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. 16965 74 16_Re: SQLCODE -90410_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 20 Dec 2000 14:57:54 -0600344_us-ascii Rohn,

Try converting the x .00000834. to Decimal - it worked out to 2100 for me. That should get you on the right track. Your deadlock may be because of escalation or utilities, that's usually when I've bothered to notice that the DBID.OBID were in the messages in my MSTR.

Thanks. Missy Case FDR TMDBA 701-275-6358 [...] 17040 69 12_Re: DDF hung12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 20 Dec 2000 13:04:23 -0800570_us-ascii You didn't show a "DDF has started" message before stopping it the second time.

Tim --- Colin Fay wrote: > Hi, > > Re: OS/390 Db2 V 5.1 > > Situation: > > - STO DDF MODE(FORCE) followed by -STA DDF > completed sucessfully. > > Db2mstr shows > > DSNL006I -DB2P DDF STOP COMPLETE > DSNL003I -DB2P DDF IS STARTING > DSNL519I -DB2P DSNLILNR TCP/IP SERVICES > AVAILABLE > FOR DOMAIN ip3270.biw.com AND > PORT 446 > DSNL519I -DB2P DSNLIRSY TCP/IP SERVICES > AVAILABLE > FOR DOMAIN ip3270.biw.com AND > PORT 5020 > > The second time around [...] 17110 71 36_OS/390 DB2 and WLM Stored Procedures12_David Booher22_David.Booher@QUEST.COM31_Wed, 20 Dec 2000 12:53:57 -0800577_iso-8859-1 Hello All,

My question pertains to the WLM Stored Procedure address space. I have a WLM Application setup for running DSN1WLM in an address space. I can start this address space manually and it stays up for about 5 minutes and then terminates gracefully. I can't figure out how to get this address space to start automatically when a DB2 Stored Procedure needs it. How is this supposed to be "kicked off"? I can't seem to find anything in the manuals besides the Environment variable which we have set to WLMENV1 and this matches what is defined in WLM. [...] 17182 33 58_Re: Bind Package from MVS with MVS and AIX SQL in same pgm10_Alan Smith25_alancsmith@BTINTERNET.COM31_Wed, 20 Dec 2000 21:13:53 +0000559_US-ASCII We get round this by using VALIDATE(RUN) in the bind. You just get warnings then.

Alan Smith



> ------------------------------ > > Date: Tue, 19 Dec 2000 10:16:00 -0500 > From: David Nance > Subject: Re: Bind Package from MVS with MVS and AIX SQL in same pgm > > Doug, > There may be a better method, but we have always done a bind on both = > systems for the package using sqlerror(continue). You will get errors for = > the objects that do not exist on that DB instance, but that's as it should = > be. [...] 17216 49 16_Db2 reorg online10_Alan Smith25_alancsmith@BTINTERNET.COM31_Wed, 20 Dec 2000 21:13:52 +0000608_US-ASCII I gather that there have been problems in V5 with the build of the mapping table/index for shrlevel change if the tablespace is badly clustered. In V5 the mapping rows are inserted, in V6 they are loaded, which is supposed to improve things.

Alan Smith



> ------------------------------ > > Date: Tue, 19 Dec 2000 19:07:57 +0800 > From: Srirengan Venkatesh Kumar > > Subject: Db2 reorg online > > Hi, > > We are OS390 2.3, DB2 V5.1 > > I have two questions regarding db2 online reorg > > 1. I was attempting to reorg a table of [...] 17266 75 21_OS/390 DB2 V6.1 parms10_Alan Smith25_alancsmith@BTINTERNET.COM31_Wed, 20 Dec 2000 21:13:53 +0000422_US-ASCII I wouldn't use VARCHAR FROM INDEX, as this means sometimes you will get a varchar, sometimes a string padded with blanks, depending on the access path. This could affect your program logic.

If you use 3-part names in DDF, beware of DATABASE PROTOCOL ===> DRDA. Suddenly you will find that you need packages bound into your remote subsystem as well as your local one (should be more efficient though). [...] 17342 76 27_Re: Buffer pool question...11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Wed, 20 Dec 2000 13:36:15 -0800453_iso-8859-1 Also with the new CMOS machines all memory is the same, main, no expanded. In the os/390/MVS set up you can allocate some of your main memory as expanded, but it is not required. I've removed all hypepools from my bufferpool allocations. cliff:-)

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Wednesday, December 20, 2000 12:25 PM To: DB2-L@RYCI.COM Subject: Re: Buffer pool question... [...] 17419 94 16_Re: SQLCODE -90413_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Thu, 21 Dec 2000 08:51:44 +1100408_- Rohn,

There seems to be some information missing. The TYPE 302 should provide a Database name . Space name . Page number. Your error message only has two parts, perhaps a bug. Is your Db2 maintenance current?

Shaun

-----Original Message----- From: Missy Case [mailto:Missy.Case@FIRSTDATACORP.COM] Sent: Thursday, 21 December 2000 7:58 To: DB2-L@RYCI.COM Subject: Re: SQLCODE -904 [...] 17514 131 27_Re: Buffer pool question...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Wed, 20 Dec 2000 14:07:21 -0800533_us-ascii All main memory…no expanded !! Well what about the maxim ' You can allocate a maximum of 2GB for virtual buffer pool but up to 8GB for hiperpools in expanded storage'.

Do all these maxims cease to be true now with CMOS? Of course as I understand, 2 GB limit for virtual buffer pools is still true. So, we would still want hiperpools if we want to 'back' the buffer pools up with additional storage. If you do choose to go for hiperpools, we would have to allocate some of our main memory as expanded. Isn't it ? [...] 17646 16 27_Re: Buffer pool question...0_26_truman.g.brown@VERIZON.COM31_Wed, 20 Dec 2000 17:13:11 -0500449_us-ascii In V5 OS/390 that's 2MB for DBM1 address space (includes Rid pool, sort pool(s), EDM pool, VBP's) and 2GB (one hiperspace) total for all hiperpools in one DB2 subsystem, as I recall...

George

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17663 86 27_Re: Buffer pool question...22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 20 Dec 2000 17:15:16 -0500452_iso-8859-1 Before CMOS machines, central storage memory was more expensive ($) than expanded storage memory.

On CMOS machines, central storage memory & expanded storage memory are the same price and the same physical memory. Hence, the ROT on CMOS machines is to configure most of the memory for central storage. I believe there are some OS/390 functions that require expanded storage. Not a good idea to configure 0MB of expanded storage. [...] 17750 94 16_Re: SQLCODE -9040_22_Rohn.Solecki@MTS.MB.CA31_Wed, 20 Dec 2000 16:43:11 -0600344_us-ascii Shaun: The 3 parts are there, I changed the DB name to 'x' (clearly an unclear choice for the substitution ). I just can't figure out how to translate the Table space portion of the name it gave in the message '00000834', which is definitely no a table or tablespace name in our system, to the appropriate text representation. [...] 17845 112 16_Re: SQLCODE -90413_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Thu, 21 Dec 2000 10:06:46 +1100607_- Rohn,

Have you tried looking for the 834 in SYSINDEXES ?

Shaun

-----Original Message----- From: Rohn.Solecki@MTS.MB.CA [mailto:Rohn.Solecki@MTS.MB.CA] Sent: Thursday, 21 December 2000 9:43 To: DB2-L@RYCI.COM Subject: Re: SQLCODE -904



Shaun: The 3 parts are there, I changed the DB name to 'x' (clearly an unclear choice for the substitution ). I just can't figure out how to translate the Table space portion of the name it gave in the message '00000834', which is definitely no a table or tablespace name in our system, to the appropriate text representation. [...] 17958 120 16_Re: SQLCODE -90422_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 20 Dec 2000 18:38:51 -0500270_- Look up the description of the type and format of the name in the Messages & Codes Appendix B. TYPE 302 is a table space page. Name is DB.SP.PG where DB is database name, SP is space name and PG is hexadecimal page number. Search for 834 in PSID in SYSTABLSPACE. [...] 18079 121 16_Re: SQLCODE -90422_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Wed, 20 Dec 2000 18:41:31 -0500286_- Not sure if the 1st attempt worked. Resending...

Look up the description of the type and format of the name in the Messages & Codes Appendix B. TYPE 302 is a table space page. Name is DB.SP.PG where DB is database name, SP is space name and PG is hexadecimal page number. [...] 18201 36 47_Paging :- QMF & Stored Proc guru (& guru-ettes)12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 21 Dec 2000 11:07:26 +1100427_- Hi, I just got posed a curly question by a developer.

"How do you call a Stored Proc from QMF?"

My short answer was that you can't. As I don't believe that QMF is equipped to pass the parameters or interpret the results ...

... but as per all things, just because you don't believe it can be done doesn't mean that it can't. So if anybody out there has knowledge to the contrary please let me know. [...] 18238 26 27_Re: Buffer pool question...10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Wed, 20 Dec 2000 18:13:13 -0600355_- At www.s390.ibm.com the LSPR number use 3:1 for 9672 CMOS technology, 1:4 for 3090 bipolar benchmarks. It's a good place to start for a "general workload". Hiperpools come out of ADMF (Asynchronous Data mover Facility). There's 8Gig per CEC. The newer processors(I think G5 or higher) have improved hardware and ADMF comes out of Expanded storage. [...] 18265 83 38_Re: SQL0987N -- Memory overdeployment?11_Bruce Allen17_ballen@SPOTON.COM31_Thu, 21 Dec 2000 11:32:38 +1100334_- My understanding is that if INTRA_PARALLEL is on, SHEAPTHRES is going to be allocated at database startup. In a 32 bit model (like AIX DB2 v5.2), I think SHEAPTHRES is going to be allocated from the same 256 Mbyte segment as LOCKLIST and DBHEAP (which of course is going to be large if you have large buffer pools and estore). [...] 18349 39 26_castout engine unavailable7_Soo Lee17_soo7lee@YAHOO.COM31_Wed, 20 Dec 2000 18:30:18 -0600455_- Hi , everbody

There are too many 'castout engine unavailable' in Datasharing envionment. I founded them in the statistics report. When castout engine unavailable occurs, I should modify parameters (such as castout class threshold, GBP castout threshold, GBP checkpoint interval...) to invoke castout processing more frequently. But, Is there another method to resolve this problem? and... Is this problem related with DASD I/O performance ? [...] 18389 53 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 20 Dec 2000 18:46:47 -0600345_US-ASCII Hi Nick, You are right. See doc II12610 I had the same question and had IBM update the doc. 10. QMF V6 does not support Stored Procedures and Functions. Regards, Kurt

>>> Nick.Cianci@TEAM.TELSTRA.COM 12/20/00 06:07PM >>> Hi, I just got posed a curly question by a developer.

"How do you call a Stored Proc from QMF?" [...] 18443 75 27_Re: Buffer pool question...14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 21 Dec 2000 11:57:55 +1100448_iso-8859-1 Perhaps this will clear up some confusion that seems to exist:

Up to OS/390 V2.9, the memory addressing used allowed 2Gb "real" memory. Real memory is memory that can be used in normal machine instructions. An additional type of memory, called "expanded" memory was also available. However, the only machine instructions that can reference expanded memory are a set of instructions to copy 4kb pages to and from real memory. [...] 18519 92 27_Re: Buffer pool question...13_Daley, Norman18_NDaley@CINERGY.COM31_Wed, 20 Dec 2000 20:15:46 -0500729_iso-8859-1 Thank you James. As always, a very articulate and informative reply.

Norm Daley Cinergy

-----Original Message----- From: James Campbell [mailto:James.Campbell@HANCORP.COM.AU] Sent: Wednesday, December 20, 2000 7:58 PM To: DB2-L@RYCI.COM Subject: Re: Buffer pool question...



Perhaps this will clear up some confusion that seems to exist:

Up to OS/390 V2.9, the memory addressing used allowed 2Gb "real" memory. Real memory is memory that can be used in normal machine instructions. An additional type of memory, called "expanded" memory was also available. However, the only machine instructions that can reference expanded memory are a set of instructions to copy 4kb pages to [...] 18612 24 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.013_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 20 Dec 2000 20:18:13 -0500556_iso-8859-1 Hi Jef, From the inconsistent results I see in the follow-on posts, it appears to be a bug or maybe just a new feature.

But I have to ask:

what is the objective of SQL like that?

thanks

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 18637 58 27_Re: Buffer pool question...11_Jon Nolting25_jrn20@DUTS.CCC.AMDAHL.COM31_Wed, 20 Dec 2000 17:48:10 -0800503_us-ascii ADMF is a hardware instruction that moves data between main and expanded storage. It does not define main or expanded storage. It is only an asynchronous instruction used to minimize the CPU expense of moving pages between main and expanded by offloading that function to the IOP. ADMF was introduced specifically to reduce CPU consumption by DB2 in managing virtual buffers and hiperpools. Use of ADMF is selectable in the partition activation profile and will be used by DB2 if enabled. [...] 18696 19 27_Re: Buffer pool question...10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Wed, 20 Dec 2000 20:24:28 -0600523_- Thanks for the update, I was trying to get out of Dodge before the weather closed in and didn't look at my notes (or my R26).



Edward(Ed) J. Finnell,III eSystems Proj. Mgr. www.ua.edu



Download the Farscape Browser at http://www.scifi.com/farscape/

================================================ 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. 18716 73 27_Re: Buffer pool question...10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Wed, 20 Dec 2000 20:48:05 -0600742_- Not sleeting yet.... From the text of PQ38174:

PROBLEM SUMMARY: **************************************************************** * USERS AFFECTED: All DB2 users using hiperpools. * **************************************************************** * PROBLEM DESCRIPTION: Currently, DB2 does not allow the use * * of hiperpools unless the Asynchronous * * Data Mover Facility is installed. In * * order to support future hardware, this * * restriction must be changed. * **************************************************************** * RECOMMENDATION: * **************************************************************** Note: Microcode Driver 26 must be on to allow DB2 hiperpools to be used by the Fast Sync Data Mover Facility. [...] 18790 36 27_Re: Buffer pool question...10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Wed, 20 Dec 2000 21:14:11 -0600768_- This is it it! I'm outta here.....PQ38174 has been superceded (SUP'd) by PQ42722.

* Synchronous Data Mover. * **************************************************************** * PROBLEM DESCRIPTION: ABEND04E RC00C200CD in DSNB1GHP, when * * using hiperpools with bufferpools with * * a page size of 16K or 32K. * **************************************************************** * RECOMMENDATION: * **************************************************************** When the Fast Synchronous Data Mover facility (FSDM) is available, DB2 avoids the use of the Asynchronous Data Mover Facility (ADMF) by setting the ADMF threshold to 255. However, this threshold represents 4K blocks and not pages, so for 16K and 32K buffer pools there is a chance that a set [...] 18827 20 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)11_Matt Kelley18_mgk333@HOTMAIL.COM31_Wed, 20 Dec 2000 22:06:02 -0600398_- On Thu, 21 Dec 2000 11:07:26 +1100, Cianci, Nick wrote:

>"How do you call a Stored Proc from QMF?"

You cannot do so from QMF running on the mainframe, but QMF for Windows allows you to write a CALL statement in a query window, and invokes the specified stored procedure, allowing for input and output parameters, as well as multiple result sets. [...] 18848 44 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 21 Dec 2000 16:04:12 +1100342_iso-8859-1 On DB2 V6 yet? If so, you could write a table function that invokes the SP and returns whatever from the SP via columns in the function.

The only caveat is that QMF V3.3.1 (which is what QMF V6 _really_ is) produces its own error messages - so the tokens in any DB2 V6 errors will be mapped in an "interesting" manner. [...] 18893 98 43_Contents of package - local vs. remote bind11_James Szabo18_jim.szabo@CORE.COM31_Wed, 20 Dec 2000 13:45:29 -0500560_iso-8859-1 Environment: multiple DB2 UDB for OS/390 V6 systems, application programs written in COBOL.

We are trying to understand why the contents of SYSIBM.SYSPACKSTMT is different when you do a local bind vs. a remote bind.

If you bind a package into the same subsystem that you are running the bind command on, SYSIBM.SYSPACKSTMT contains all statements that need to be optimized (e.g. DEFINE CURSOR and singleton SELECT/INSERT/UPDATE/DELETE), as well as other executable statements that appear in the program (OPEN, FETCH, CLOSE, SET). [...] 18992 67 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 21 Dec 2000 10:57:16 +0530464_- Hi Brain,

Have you tried looking at this DBID.OBID corresponds to which object ?. I am sure shortage in the space of EDM pool will not give you deadlock and timeout. Infact nothing, except the exclusive locks on DBD and SYSIBM.SYSPLANAUTH and SYSPACKAUTH will cause deadlock and timeout which is somewhat related to EDM pool ,in that case also no message is given which refers to EDM pool. And locks in DBD (exclusive) are under specific conditions. [...] 19060 72 48_Re : Contents of package - local vs. remote bind16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 21 Dec 2000 11:11:03 +0530554_- > James, > It looks that it has something to do with Deferred Embedded Sqls. I > read it sometime back, So some guesses : > All the remote access embedded sqls through the DRDA is called as the > deferred embedded sqls which are neither fully static nor fully dynamic. > It is ofcourse embedded in the application like static sqls but it is > prepared at the execution time. Many of its processings are done at the > bind time also, like authorization id and qualifier determination with the > owner. > It looks that SYSIBM.SYSPACKSTMT keeps only [...] 19133 75 33_Re: quarterly history of DB2 data16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 21 Dec 2000 11:27:36 +0530580_- Starting from point by point. You get the flat file data given by cobol programs using DB2. Now you have VSAM, DB2 and Flat files as the data storage in your installation. It seems you need something which can give your auditors the quearterly data....But in what kind of format, from which all data source, for what kind of analysis....it it OLAP kind of analysis stuffs you require. For this kind of requirement, i can write any number of programs, cunsomize everything in my Windows NT using my own written programs(don't have any now), replications and may be the OLAP [...] 19209 15 52_Terry Gawlas/ISG/CORP/Highmark is out of the office.12_Terry Gawlas25_terry.gawlas@HIGHMARK.COM31_Thu, 21 Dec 2000 01:07:12 -0500390_us-ascii I will be out of the office starting 12/20/2000 and will not return until 01/02/2001.

I will be out of the office 12/20 through 1/2.

================================================ 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. 19225 90 42_450+ links about DB2 for OS/390 (12/20/00)6_DB2usa18_db2usa@HOTMAIL.COM31_Thu, 21 Dec 2000 00:36:47 -0600494_ISO-8859-1 Hi,

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

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

Last update on Wednesday, December 20th 2000 ============================================

- Links about DB2 http://db2usa.hypermart.net/eliste.htm IBM broadcasts an e-seminar named Siebel eBusiness Solutions and DB2 for OS/390 (free registration) with Ken Llacera, Director Product marketing, IBM products. [...] 19316 18 53_Debra Jordan/Riverwoods/BT/DFSI is out of the office.15_Debra D. Jordan33_debrajordan@DISCOVERFINANCIAL.COM31_Thu, 21 Dec 2000 01:02:15 -0600473_us-ascii I will be out of the office starting 12/20/2000 and will not return until 01/08/2001.





I will respond to your message when I return. If this is an emergency, please contact Jang Lee at (847) 405-1281.

================================================ 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. 19335 18 28_Re: REORG SHRLEVEL REFERENCE10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 08:31:57 +0100368_us-ascii BE SURE !!!!

But I think mother IBM will build this PTF for DB2 V 69..... ;-)

Regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19354 26 30_Re: castout engine unavailable10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 08:50:36 +0100361_us-ascii I noted that 'engines unavailable' condition often depends on slow devices (i.e. without enough cache or without all fast

read/write features (DFW etc) enabled) AND a high number of concurrent executing batch jobs in each DS member.

It may be you have some other 'engines unavailable condition' (write engine, prefetch engine), too. [...] 19381 24 33_Parameter Markers in visual Basic14_Adrian Collett27_adriancollett@TISCALINET.IT31_Thu, 21 Dec 2000 09:13:23 +0100496_iso-8859-1 Dear List,

Is it possible to use parameter markers with Visual Basic ADO connecting to DB2 OS390 ?

And if so, is it advisable from a VB programming point of view ?

Thanks,

Adrian Collett DB2 Consultant Milan, Italy.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19406 22 12_Re: DDF hung10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 09:17:31 +0100458_us-ascii Hi Colin

Take a look at (if not yet done)

http://techsupport.services.ibm.com/390/psd-adv.html

and search for 'DDF hung', there are some interesting entries.

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19429 68 34_Stored Procedure with IMS - Sample12_Jacob Ganzel19_jacobg@SEMECH.CO.IL31_Thu, 21 Dec 2000 10:19:01 +0200365_x-user-defined Hello list.

I need a sample of a Stored Procedure that reads DB2 data and updates IMS/DB.

Thanks a lot. --------------------------------------------------------------- Jacob Ganzel

SEMECH Software Marketing Ltd.

3b Netanyahu St., OR YEHUDA, ISRAEL 60376 Email: jacobg@semech.co.il Tel.: +972-3-5333144 Fax.: +972-3-5333132 19498 92 37_Re: Parameter Markers in visual Basic11_Dimoka Popy24_dimokap@INTERAMERICAN.GR31_Thu, 21 Dec 2000 10:31:22 +0200756_iso-8859-7 Hi Adrian,

recently we have been making some tests with VB6+ADO+DB2 V5 for OS/390 basically to establish a new security policy by capturing static sql statements and creating "host" package lists,on which we can apply security rules (grant execute on mypkglist.* to public).We used prepard sql statements and parameter markers because we wanted to produce "generall" sql statements. Here is a program sample : ---------------------------------------------------------------------------- ------------- Set cmdupd = New ADODB.Command Set cmdupd.ActiveConnection = cnSample cmdupd.CommandType = adCmdText cmdupd.CommandText = "UPDATE DSN8510.EMP " & _ "SET LASTNAME =?,FIRSTNME =?" & _ " WHERE EMPNO =?" cmdupd.Prepared = True Set prm1 [...] 19591 156 36_Re: cleaning up obsolete packages...13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Wed, 20 Dec 2000 11:06:38 -0600426_iso-8859-1 I have used SAS to generate the rebind statements on our large development systems. If I rebind all invalid packages, the ones that fail become inoperative. After the rebind, all invalid and inoperative packages are freed. This way there is no need for a version check. In our environment, there isn't a number of versions we can apply. We have a sysadm do the rebind, so auth problems aren't a consideration. [...] 19748 34 46_DB2 Replicaton using CAPTURE & APPLY (Urgent!)15_Amitabh Akolkar23_oxynet@MAILCITYASIA.COM31_Thu, 21 Dec 2000 15:33:44 +0530419_us-ascii Hi,

It would be really helpful if anyone could help me at the earliest!

We are trying to replicate from DB2 PE to DB2 WorkGroup over the internet using DB2 provided Apply and Capture programs.

For this we are bound to add the remote system in the Control Center and then replicate. But we are trying to access the Remote System through a proxy server over the LAN. Our network is NT. [...] 19783 109 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_18_mebert@AMADEUS.NET31_Thu, 21 Dec 2000 12:37:13 +0100560_us-ascii Max

REORG changes the physical locations of data rows. Because DB2 identifies rows by their location (the RID), you lose recoverability after a REORG. With LOG NO, this leaves the TS in COPY Pending, and you have to take an IC. Because SHRLEVEL REF & CHANGE are meant to reduce downtime, this would be unacceptable; that's why DB2 forces you to take an Inline IC. For a read-only TS, leaving the TS in COPY would be ok - but who needs to REORG a read-only TS?? It's probably safe to say that you'll wait forever for this change. It serves [...] 19893 56 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 13:46:52 +0100515_us-ascii Hi mebert.

Really ? During a reorg RIDs are changed ? After a reorg tablespaces are in copy pending ? Incredible !!! ;-)

Sorry but I disagree with you.

In my opinion it's an annoying feature to permit a SHRLEVEL REFERENCE (NOT SHRLEVEL CHANGE) with an high degree of safety AND force users to execute an inline image copy. It's more & more & more clever let the user decide what kind of reorg she/he desire. MAYBE she/he knows what she/he's doing, even if she/he's not certified. [...] 19950 41 29_Sqlcode -904, reason 00c2003115_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT31_Thu, 21 Dec 2000 14:00:32 +0100318_iso-8859-1 We use DB2 V5R1(OS/390). Some programs receive Sqlcode -904 with Reason 00c20031; the insterested DB2 objects are index . These programs are very old and some of these run daily more than one hundred. Several execution of these programs are OK, and the rerun of these thatreceived -904 is generaly OK. [...] 19992 30 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_18_mebert@AMADEUS.NET31_Thu, 21 Dec 2000 14:04:01 +0100573_us-ascii ...BUT I'm obliged to waste a lot of DASDs space to execute an inline image copy for each tablespace (with additional & potential B37 abends for image copy files that I can avoid with a wonderful scheduler and an automated tape library). In this case (it's not rare) it's better to have all (or a group) reorged tablespaces in COPY PENDING (and you can read the tables - not all transactions in an application update the tablespace) and execute an image copy on one (or more) tapes, which is cheaper and I don't loose any recoverability. BMC reorg did [...] 20023 57 40_Re: OS/390 DB2 and WLM Stored Procedures11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Thu, 21 Dec 2000 08:20:14 -0500444_iso-8859-1 I did not understand "Environment variable" as this applies to OS/390. The WLM proc definition is in several places.

1. DB2: ZPARM (WLMENV in V6, n/a in V5) 2. OS/390: WLM environment definition 3. DB2: Each stored procedure definition (either a column in sysibm.sysprocedures; V5 or sysibm.sysroutines; V6)

You can use SQL to check the DB2 definitions: Here is a sample operator command to check the OS/390 part: [...] 20081 218 40_Re: OS/390 DB2 and WLM Stored Procedures12_David Booher22_David.Booher@QUEST.COM31_Thu, 21 Dec 2000 05:21:52 -0800368_iso-8859-1 I discovered the answer to my problem later last evening. I did a D WLM,APPLENV=WLMENV1 and discovered it in a STOPPED state. Apparently, this occurred when I was testing the JCL for the address space and the STOPPED state appears to have carried-over thru multiple IPLs. Once a VARY WLM,APPLENV=WLMENV1,RESUME was issued, everything appears to be OK. [...] 20300 22 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 14:37:29 +0100486_us-ascii ***

I don't quite understand this. You can direct an OLR Inline IC to tape as well (you can even use SL=n and RETAIN). So what's the problem?

***

Did you ever tried it with 10 reorgs in parallel ?

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20323 21 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 14:46:45 +0100524_us-ascii I don't quite understand this. You can direct an OLR Inline IC to tape as well (you can even use SL=n and RETAIN). So what's the problem?

***

Did you ever try it with 10 (or more) reorgs of tablespaces differing in size running in parallel ?

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20345 44 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 21 Dec 2000 08:47:03 -0500445_iso-8859-1 Max, We run > 10 Reorgs (SHRLEVEL CHANGE) in parallel every day. All we had to do to accomodate this is increase the BP used for the mapping table indexes.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Thursday, December 21, 2000 8:47 AM To: DB2-L@RYCI.COM Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? [...] 20390 24 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 15:02:50 +0100374_us-ascii Max, We run > 10 Reorgs (SHRLEVEL CHANGE) in parallel every day. All we had to do to accomodate this is increase the BP used for the mapping table indexes.

**** We run >> 10 reorgs in parallel,too. And we are happy.

But do you mean you execute inline IC using only 1 tape drive (with LBL =1..n and RETAIN) for > 10 tablespaces ? Is it pratical ? [...] 20415 46 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)12_Brian Picard24_brian_picard@HOTMAIL.COM31_Thu, 21 Dec 2000 08:33:25 -0600578_- Hi Sanj, The application team is trying to run about 10 concurrent jobs.(same program and difft plans each using difft qualifier). All the 9 jobs went down, except job1. Subsequent restarts also failed because job1 had not completed at that time. There were no DDL executed at this database at that time. This is a dynamic application and we have to use the delivered program. We cannot change the commit frequency as it is next to impossible. It is not a static program where you can analyze and commit periodically(thats what I was told when I asked the same question, [...] 20462 91 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Thu, 21 Dec 2000 15:45:47 +0100488_iso-8859-1 Brian,

Do you you have any utils running at that time, theycan spoil it too..

Kind regards,

Jaap Slot

Phone 0031 (0)30 215 2220 Mobile 0031 (0)6 5374 0167 e-Mail

Reserve your AGENDA for next DB2 Users Group Conference IDUG Florence - 8/11 October 2001 -

No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 20554 36 22_Re: Table space % used13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 21 Dec 2000 08:51:57 -0600541_us-ascii We have a C program that we run daily that tells us which tablespaces are over a certain percent use. We typically use 90%. I'm sorry I don't have access to the C program. It predates me. But another way you can easily tell what percent is used is to use the Control Center. I run Control Center on Windows 95 and connect it to DB2 on AIX. Both are version 6.1. When you list the tablespaces, you get the values for percentage used, size allocated and size used. You can sort on those columns ascending or descending. That way [...] 20591 63 22_Re: Table space % used18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 21 Dec 2000 09:59:24 -0500416_iso-8859-1 Watch out for this number. My experience is that the percent used will not decrease until you reorg the tables in the tablespace. I know this happens on the Solaris environment but I'm not sure about AIX. Just an FYI!

-----Original Message----- From: Helen Johnson [mailto:helen_johnson@RAC.RAY.COM] Sent: Thursday, December 21, 2000 9:52 AM To: DB2-L@RYCI.COM Subject: Re: Table space % used [...] 20655 77 47_FW: Web access to DB2 on S390 in the real world14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Dec 2000 14:58:16 -0000434_iso-8859-1 Jaap is right,

Perhaps what I should have asked for were experiences in accessing S390 DB2 data from a Web browser. Whether it's Intranet, extranet or Internet doesn't really matter

Thanks

Phil Grainger

-----Original Message----- From: Slot, JP (Jaap) [mailto:J.P.Slot@rf.rabobank.nl] Sent: 20 December 2000 16:15 To: Grainger, Phil Subject: RE: Web access to DB2 on S390 in the real world [...] 20733 61 43_Computing percent of transactions that sort14_Spiegel, Barry21_barry.spiegel@EDS.COM31_Thu, 21 Dec 2000 10:01:03 -0500379_iso-8859-1 I've seen a few different methods for computing the percent of transactions that perform sorts. Some recent statistics I've collected in a data warehouse environment that I support make me wonder if there is a consensus.

A recent seminar noted that you should use:

(TOTAL SORTS * 100) / (COMMIT STATEMENTS ATTEMPTED + ROLLBACK STATEMENTS ATTEMPTED) [...] 20795 60 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)11_rick creech18_ykcirc@HOTMAIL.COM31_Thu, 21 Dec 2000 08:57:43 -0600599_- Hi,

I know you can write rexx in qmf as a qmf proc...so maybe you can call a stored proc from one of the rexxes that you write.

hth,

Rick Creech



>From: "Cianci, Nick" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Paging :- QMF & Stored Proc guru (& guru-ettes) >Date: Thu, 21 Dec 2000 11:07:26 +1100 > >Hi, > I just got posed a curly question by a developer. > >"How do you call a Stored Proc from QMF?" > >My short answer was that you can't. As I don't believe that QMF is equipped [...] 20856 14 58_Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)12_Brian Picard24_brian_picard@HOTMAIL.COM31_Thu, 21 Dec 2000 08:58:06 -0600473_- No I don' think so. What type of utils, you are so specific about Slot?. Not any COPY, REORG or RECOVER etc were running at that time. The other stand alone utils, no body runs without involving sys administrators first. Brian...

================================================ 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. 20871 53 72_Re: Binding CLI Packages for Distributed Applications Using Dynam ic SQL13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Thu, 21 Dec 2000 09:11:07 -0600594_iso-8859-1 Kurt,

Thanks.

-----Original Message----- From: Kurt Sahlberg [mailto:Kurt.Sahlberg@EXPERIAN.COM] Sent: Wednesday, December 20, 2000 12:00 PM To: DB2-L@RYCI.COM Subject: Re: Binding CLI Packages for Distributed Applications Using Dynamic SQL



Your assumption is correct. You must explicitly grant privileges to users running the CLI packages. Before DB2 UDB V6 OS390 and 3 part names we used CLI packages to access remote NT databases. We only use DYNAMICRULES(BIND) for static bound packages that have dynamic SQL embedded within them on OS390. Kurt [...] 20925 28 47_Dynamic SQL from Business Objects, Brio, etc...14_Jessen Michael27_JessenMichael@JOHNDEERE.COM31_Thu, 21 Dec 2000 09:15:02 -0600451_us-ascii I'm curious what other companies have put in place to combat what I'll call runaway queries from a client application.

Someone writes a query in Business Objects, Brio, etc., then decides it's taking too long and cancels the application/query, leaving the thread active in DB2 V5 (OS/390). We have a limit set on CPU seconds... but is there anything else available that would cancel the thread before our site limits are reached? [...] 20954 79 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Dec 2000 15:17:33 -0000511_iso-8859-1 Maybe I am missing the point of the question, but CALL "stored procedure" is just an SQL statement like anything else

So, in a QMF query you can code CALL "storedprocedure" etc etc etc.

Maybe that isn't what the question means - if not, sorry

Phil Grainger Computer Associates

-----Original Message----- From: rick creech [mailto:ykcirc@HOTMAIL.COM] Sent: 21 December 2000 14:58 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Paging :- QMF & Stored Proc guru (& guru-ettes) [...] 21034 40 0_11_Mayo Arthur19_Arthur.Mayo@IRS.GOV31_Thu, 21 Dec 2000 10:31:20 -050032_iso-8859-1 Signoff DB2-L

21075 91 22_Re: Expanding a column16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 21 Dec 2000 16:23:35 +0100312_us-ascii want my TL 2? (very little worth I am afraid)...

well actually no, you only have to change your existing applications to SELECT a CHAR(13) item only if you want to avoid truncation of the exceeding bytes at the end (right) of your column, otherwise if you did not care you wold not have to. [...] 21167 74 22_Re: Table space % used13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Thu, 21 Dec 2000 09:38:45 -0600511_us-ascii I was somewhat confused by your message because I was sure that when I connected to Control Center this morning that the change I had made earlier had been reflected in Control Center. So I did some testing. I picked a tablespace that was 89% full. Altered it to add an extent. Then I went back to Control Center - no change. I disconnected and reconnected to the database - still no change. I closed Control Center and opened it again - the change was now visible. Thanks for the heads up Peter. [...] 21242 32 13_Signoff DB2-L10_Leo Robles25_roblesl@CELLULARONEPR.COM31_Thu, 21 Dec 2000 11:44:13 -0400 21275 58 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 21 Dec 2000 10:41:43 -0500284_iso-8859-1 Max, What we do is to have each REORG job use one tape drive. We stack multiple REORG executions within the same step to the same tape drive. For smaller databases we may REORG dozens of tablespaces within one job step and stack their inline copies to the same drive. [...] 21334 120 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.011_Jakobs, Jef18_JJakobs@HARTIC.COM31_Thu, 21 Dec 2000 10:17:07 -0600936_iso-8859-1 David,

Maybe I should have explained that this query I mailed out is a simplified version of the real thing. What's behind it is the following query:

The real thing looks like: SELECT REFUNDS.DATE_RECEIVED, REFUNDS.RECEIPT_NUMBER, REFUNDS.REFUND_TYPE, REFUNDS.REFUND_AMOUNT, REFUNDS.REFUND_ISSUED_DATE, REFUNDS.ADDRESS1, REFUNDS.REFUND_PAYEE, REFUNDS.ADDRESS2, REFUNDS.REFUND_REASON, REFUNDS.REFUND_CHECK_NO, REFUNDS.ADDRESS1, REFUNDS.ADDRESS2, REFUNDS.CITY, REFUNDS.STATE, REFUNDS.ZIP , RECEIPT.USER_ID FROM REFUNDS , RECEIPT WHERE REFUNDS.DATE_RECEIVED = RECEIPT.DATE_RECEIVED AND REFUNDS.RECEIPT_NUMBER = RECEIPT.RECEIPT_NUMBER AND (REFUNDS.DATE_RECEIVED >= :start_date AND REFUNDS.DATE_RECEIVED <= :end_date) AND ( :user_arg = 9999 OR RECEIPT.USER_ID = :user_arg ) AND ( :refund_arg = 'A' OR REFUNDS.REFUND_TYPE = :refund_arg) AND ((:paid_flag = 1 AND REFUNDS.REFUND_CHECK_NO IS NULL) OR (:paid_flag = [...] 21455 47 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 21 Dec 2000 17:19:32 +0100500_us-ascii Max, What we do is to have each REORG job use one tape drive.

*** AhAh ! 1 job 1 tape drive !. AH, you lucky man who can steal > 10 tape drives to production batch... As I said I cannot steal (the fews) tape drives to production batch jobs, so i cannot use 1 drive for EACH reorg and I am obliged to execute IC on disk. AND using 1 tape drive for > 10 reorgs running in parallel it not possible. BUT If I could execute reorgs without inline IC I'd be able to avoid this annoying [...] 21503 48 44_ESS Shark and Disaster Recovery through PPRC16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 21 Dec 2000 17:10:23 +0100594_us-ascii hello list,

those of you who have Shark DASD (ESS)... AND who use a PPRC technique for their Disaster Recovery strategies.... how do you do this? do you have the double Sharks or wot?

to make my question clear I'll have to explain things a bit:

I am working at a new client's and since it's a big outsourcing biz, relatively new in taking over, we are still trying to customise all the different DR scenarios... during a meeting today we discussed using the ESS we have, basically somebody heard from reliable sources :-) that Shark could greatly improve our [...] 21552 76 48_Re: ESS Shark and Disaster Recovery through PPRC15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Thu, 21 Dec 2000 10:33:30 -0600457_iso-8859-1 New Redbook:

Form Number: SG24-5680-00 Title: Implementing ESS Copy Services on S/390

Can now be downloaded as a PDF file and ordered in hardcopy two weeks following the Publish Date! (see http://ibm.com/redbooks/abstracts/sg245680.html for online access)

This is very readable and also includes an excellent appendix discussing disaster recovery mirroring/shadowing using combinations of GDPS, PPRC, XRC and FlashCopy. [...] 21629 53 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)11_David Nance16_DWNance@FHSC.COM31_Thu, 21 Dec 2000 11:40:03 -0500376_US-ASCII Not speaking from experience, but should be able to call from a QMF proc. Since your proc is a rexx routine, you can get anything done there that you can do in any other rexx procedure.

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

>>> Nick.Cianci@TEAM.TELSTRA.COM 12/20/00 07:07PM >>> Hi, I just got posed a curly question by a developer. [...] 21683 36 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Dec 2000 10:48:01 -0600585_us-ascii The only thing that really bothered me about this was the different replies to questions, and the lack of a response.

When a question was asked during a DB2 tech conference presentation (1999?) about why an in-line image copy was required for a SHRLEVEL REFERENCE REORG, the answer was that this was a coding mistake and should not be required. I don't remember who was talking, but they want on to say that almost all of their testing was with SHRLEVEL CHANGE, and that they did not put much time into SHRLEVEL REFERENCE because they did not anticipate much need [...] 21720 65 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 21 Dec 2000 17:12:31 -0000437_iso-8859-1 Of course,

It's just struck me - it's the return parameters that are the problem.

Obviously I am in Christmas go-slow mode already

Apologies, but the Rexx suggestions look like possibilities

Phil G

-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: 21 December 2000 16:40 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Paging :- QMF & Stored Proc guru (& guru-ettes) [...] 21786 80 28_Question about Sql Procedure10_Asha Jijen19_asha.jijen@ACTA.COM31_Thu, 21 Dec 2000 09:08:18 -0800386_- Can someone help. I am new to Db2 and have a question regarding "CREATE PROCEDURE" command. I have Db2 7.1 and the following script I try to execute

CREATE PROCEDURE aaww() BEGIN DECLARE i INTEGER DEFAULT 1; ins_loop: LOOP IF i>100 THEN LEAVE ins_loop; ELSEIF i<=100 THEN ITERATE ins_loop; END IF; INSERT INTO TAB1 VALUES (i,'FIRST VALUES'); SET i = i + 1; END LOOP; END@ [...] 21867 65 12_Re: DDF hung9_Colin Fay13_cfay2@CSC.COM31_Thu, 21 Dec 2000 12:19:29 -0500560_us-ascii Hi,

Linda, Tim,Phillip,Max , thaks for your suggestions

Max, there are indeed a lot of related apars.

The one most closly resemeblling what happened here is

PQ14382: DSNL511I RC140 AND RSN12EE0291 DB2/MVS SERVER NO LONGER LISTEN TO THE CLIENT CONNECT.



This inability to re-recycle DDF occurred in conjunction with this message in MSTR

DSNL511I -DB2P DSNLIENO TCP/IP CONVERSATION FAILED TO LOCATION 167.150.57.226 IPADDR=167.150.57.226 PORT=1104 SOCKET=WRITEV RETURN CODE=140 REASON CODE=745A7242 [...] 21933 93 32_Re: Question about Sql Procedure15_Benjamin Reches24_Benjamin.Reches@MSDW.COM31_Thu, 21 Dec 2000 12:25:03 -0500544_us-ascii Asha,

Try labeling the BEGIN block and specify the label on the END statement.

Ben

Asha Jijen wrote:

> Can someone help. I am new to Db2 and have a question regarding "CREATE > PROCEDURE" command. > I have Db2 7.1 and the following script I try to execute > > CREATE PROCEDURE aaww() > BEGIN > DECLARE i INTEGER DEFAULT 1; > ins_loop: > LOOP IF i>100 THEN > LEAVE ins_loop; > ELSEIF i<=100 THEN > ITERATE ins_loop; > END IF; > INSERT INTO TAB1 VALUES (i,'FIRST VALUES'); > SET i = i + 1; > END LOOP; > [...] 22027 68 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 21 Dec 2000 11:42:34 -0600626_us-ascii Hi Tim

For B37 errors in reorgs, assign high space to all working sort files can sove problem where their DISP=(MOD,DELETE,CATLG),

SPACE=(CYL,(2000,2000),,,ROUND)

Thanks Nayeem





---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000 11:40 AM ---------------------------



Tim.Lowe@STPAUL.COM on 12/21/2000 10:48:01 AM

Please respond to DB2 Data Base Discussion List

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

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? [...] 22096 163 22_Re: Expanding a column14_Laredo, Andrew19_Andy_Laredo@BMC.COM31_Thu, 21 Dec 2000 11:52:25 -0600518_iso-8859-1 This is a nit, but may bite you. Notice the WHERE below, using LIKE, only returns one row. Once your new column is padded with blanks any existing queries you have, if you do, that look like this may not operate as expected...

SELECT NAME,CREATOR FROM "SYSIBM".SYSDATABASE WHERE NAME LIKE 'DSNDB04' OR NAME LIKE 'DSN8D61A' ---------+---------+---------+--------- ---------+---------+---------+--------- NAME CREATOR ---------+---------+---------+--------- DSN8D61A JKS NUMBER OF ROWS SELECTED 1 [...] 22260 28 37_DB2 UDB 6.1 on AIX 4.3.3 Archive Logs10_Robert Ord21_robertord@HOTMAIL.COM31_Thu, 21 Dec 2000 18:14:25 -0000680_- To anyone who understands UDB on AIX,

How do you list the archive log history that UDB holds ? I assume it must be stored in SQLOGCTL.LFH, but which of the many undocumented commands unlocks the information ?

Regards

Rob











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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22289 140 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Dec 2000 12:22:45 -0600581_us-ascii Nayeem, That is NOT a good universal solution for all possible B37 problems in reorgs! It would certainly be a simple world if that solved all B37 errors. Perhaps it would help if I described the types of B37 errors that I am trying to solve.

Some reorgs are for very small tablespaces, in which case allocating thousands of cylinders of dasd can be a huge waste. Some reorgs are for very large tablespaces, in which case you may need to go to tape instead of dasd. Some tablespaces experience a very large unexpected growth (surprise!) and create problems. I [...] 22430 40 22_Re: Table space % used11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Thu, 21 Dec 2000 18:33:48 +0000327_us-ascii John,

Another option for this is to look at the Space-GUY tool from Database Guys. This can be set up to fire off an alaram when the file systems get to a certain percent full (or file systems if you were interested in SMS). It can also estimate (if you keep history) when the file system can become full. [...] 22471 44 41_Re: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs9_Bob Lewis23_BLewis@MICROGENERAL.COM31_Thu, 21 Dec 2000 10:22:00 -0800421_iso-8859-1 In the sample USEREXIT for archive logging you can specify where the archive 'log' is placed. I believe the name for the file is cleverly defaulted to ARCHIVE.LOG. You can also specify a location for an error log.

-----Original Message----- From: Robert Ord [mailto:robertord@HOTMAIL.COM] Sent: Thursday, December 21, 2000 12:14 PM To: DB2-L@RYCI.COM Subject: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs [...] 22516 41 42_Re: FW: DB2 71 on NT Authorization Problem11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Thu, 21 Dec 2000 18:39:21 +0000633_us-ascii As the account1 you need to update the dbm cfg with appropriate groups for sysadm_group etc. You can then assign your other users into those groups. They will then have the authorisation.

Jeremy



"Doctor, Nadir" wrote:

> ndr > > -----Original Message----- > From: Dimitris Margaritis [mailto:margardi@NOVABANK.GR] > Sent: Sunday, October 15, 2000 7:10 AM > To: DB2-L@RYCI.COM > Subject: DB2 71 on NT Authorization Problem > > Hi all, > I have just installed DB2 v 7.1 on my NT workstation using a local account1 > from Administration Group. When I log on using another account2 on another > [...] 22558 75 41_Re: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs0_22_BILL_GALLAGHER@PHL.COM31_Thu, 21 Dec 2000 13:32:18 -0500332_us-ascii Rob,

If you are archiving the logs through the db2uext2 user exit to ADSM, then you can use the "db2adutl query" command to see which logs are archived in ADSM. Also, the exit will write a record of each log that it archives in the ARCHIVE.LOG file, which is located in the directory that is coded in the exit. [...] 22634 97 38_Re: SQL0987N -- Memory overdeployment?11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Thu, 21 Dec 2000 18:41:43 +0000324_us-ascii Bruce,

Whilst I agree with you about the 7 memory segments etc., I don't believe that you can get around it even using estore. My understanding is that it is a hard and fast limit with 32 bit application on AIX. You certainly couldn't prior to v5.2 - although I will admit to not having tried it since. [...] 22732 20 39_Re: Importing data into UDB DB2 tables.11_Jeff Kalman15_jkalman@CSC.COM31_Thu, 21 Dec 2000 13:44:09 -0500336_us-ascii Hi all, I've completed exporting data from DB2 tables on the OS 390 into IXF files on the Windows NT server. I then created the UDB DB2 tables on the server and am now working on populating the UDB DB2 tables. I can use some assistance on the "IMPORT" syntax to import the data from the IXF files into the DB2 UDB tables. [...] 22753 48 41_Re: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs18_Gert van der Kooij15_geko@WANADOO.NL31_Thu, 21 Dec 2000 19:48:55 +0100667_iso-8859-1 The 'list history' command will help you. It's described in the Command Reference.

----- Original Message ----- From: "Robert Ord" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, December 21, 2000 7:14 PM Subject: DB2 UDB 6.1 on AIX 4.3.3 Archive Logs



> To anyone who understands UDB on AIX, > > How do you list the archive log history that UDB holds ? I assume it must be > stored in SQLOGCTL.LFH, but which of the many undocumented commands unlocks > the information ? > > Regards > > Rob > > > > > > > _________________________________________________________________________ > Get [...] 22802 51 39_Re: Importing data into UDB DB2 tables.15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 21 Dec 2000 12:45:33 -0600469_us-ascii Hi Jeff

I appreciate if you can give full syntax of the command used for exporting data from from DB2 tables on the OS 390 in in IXF file. How u did ??

Thanks Nayeem



---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000 12:44 PM ---------------------------



Jeff Kalman on 12/21/2000 12:44:09 PM

Please respond to DB2 Data Base Discussion List [...] 22854 18 39_Re: Importing data into UDB DB2 tables.11_Jeff Kalman15_jkalman@CSC.COM31_Thu, 21 Dec 2000 14:00:50 -0500402_us-ascii Export syntax is:

export to d:\vcstst11\exports\t002x.ixf of ixf messages d:\vcstst11\exports\t002x.imp select * from vcstst11.t002x

Jeff

================================================ 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. 22873 166 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 21 Dec 2000 12:57:27 -0600437_us-ascii I mean if you do not wanted to retain these files then you can specify that way and any how those sort file will be deleted once the job sucessfully ends.

Thx Nayeem ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000 12:57 PM ---------------------------



Tim.Lowe@STPAUL.COM on 12/21/2000 12:22:45 PM

Please respond to DB2 Data Base Discussion List [...] 23040 69 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 21 Dec 2000 14:18:54 -0500471_iso-8859-1 So maybe consider going to REORG SHRLEVEL CHANGE so you can spread these out over 24 hrs rather than fighting a relatively small window? Or do you have apps which refuse to COMMIT?

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Thursday, December 21, 2000 11:20 AM To: DB2-L@RYCI.COM Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? [...] 23110 69 43_Re: DB2 Connect EE as Gateway Configuration13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Thu, 21 Dec 2000 13:21:00 -0600340_- Have you tried the "Search the Network" selection on the Add function under Client Configuration Assistant?

We are using an AIX box. In our case, our host name was the name of the server. Can you connect to the OS/390 directly from the server? Is this working? Have you set up connection between the server and the mainframe? [...] 23180 60 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)17_Alessandro Brezzi28_brezzi.isac@INTERBUSINESS.IT31_Thu, 21 Dec 2000 20:55:25 +0100531_us-ascii Hi all, I'm just curious about this statement "...Since your proc is a rexx routine, you can get anything done there that you can do in any other rexx procedure." This sound the same as what is stated in the "Developping QMF Appl" manual... I try to use some ISPF services inside a procedure with logic: VGET, VPUT and DISPLAY. Only the DISPLAY service work, as I can get back a RC of 8 in VGET and even in VPUT ("This variable not exist", in a PUT ?!?!). Somebody here have written a PWL in QMF using ISPF services? [...] 23241 98 43_Re: DB2 Connect EE as Gateway Configuration16_Maculsay, Gary R28_Maculsay.Gary@EMERYWORLD.COM31_Thu, 21 Dec 2000 20:11:27 -0000406_iso-8859-1 The connection between the unix server and the mainframe works.

The svcename is blank. The admins did an install, but did not fully utilize all the niceties of the install program, so we ended up creating the instance later.

I'm just guessing (I have to find a moment to rtfm) but I think we need to set up /etc/services to reserve port(s) and update svcename on the dbm cfg. [...] 23340 45 22_Re: Table space % used11_Larry Cable19_Larry.Cable@NWA.COM31_Thu, 21 Dec 2000 12:43:39 -0600408_iso-8859-1 I'm currently doing it now with a Korn shell script on AIX UDB 6.1. I send out mail when the tablespace is 85% full. A "page" when the tablespace is 95% full.

LarryC

----- Original Message ----- From: "John Breidenstine" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, December 20, 2000 3:01 PM Subject: Table space % used [...] 23386 16 33_James Drewe is out of the office.11_James Drewe20_James.Drewe@AEXP.COM31_Thu, 21 Dec 2000 01:04:09 -0700470_us-ascii I will be out of the office from 12/20/2000 until 01/02/2001.

I will respond to your message when I return. For DBA assistance, please contact Debbie Russell at 602-766-5035 or Duane Foss at 602-766-5662. 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. 23403 15 55_Thanks for the T-shirt Jennifer Moody, CDB Software Inc10_Rob Wright17_rwright@LIC.CO.NZ31_Fri, 22 Dec 2000 09:29:29 +1200401_us-ascii Just to say thanks to Jennifer Moody of CDB Software Inc for the t-shirt. A very nice Christmas present, all the way to New Zealand.

Rob Wright

================================================ 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. 23419 22 38_Anyone using DSNUTILS Stored Procedure13_Scott Goodell22_scott.goodell@WCOM.COM31_Thu, 21 Dec 2000 15:21:35 -0600568_us-ascii I have been attempting to use the DSNUTILS stored procedure to run a RUNSTATS utility on DB2 V6 for OS390. The documentation is rather limited on this and the WLM address space received an S0C1 abend with an error indicating no SYSIN was allocated. According to the doc this is supposed to be allocated by the DSNUTILS procedure. There is another small paragraph of doc that mentions needing SYSUT1 and SYSPRINT DDs in the WLM JCL but does not explain how to allocate them. Has anyone done this and if so can you supply your WLM DD definitions for these [...] 23442 24 5_Reorg15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 21 Dec 2000 15:25:08 -0600561_us-ascii Hi

I am getting hell of time in sort phase when running REORG against partitioned TS and I am using DB2 Utility : REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I terminated util and ran REBUILD INDEX ALL

Why and how?

Thx Nayeem

================================================ 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. 23467 25 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Thu, 21 Dec 2000 14:29:52 -0600359_- I agree with Tim. Where would I find 2000 cylinders laying around for every sortwork DS in every reorg? Thankfully, I have the luxury of BMC reorg that already has dynamic file allocation and thresholding for disk or tape allocation. Just my 2c. Speaking of reorg, how does everyone handle the mapping table and its TS? One common TS? One per DB? Etc? [...] 23493 47 9_Re: Reorg15_Hernandez, Hugo22_Hugo_Hernandez@BMC.COM31_Thu, 21 Dec 2000 15:39:05 -0600456_iso-8859-1 Few questions : 1) Do you want to reorg all the partitions for the tablespace or just few ones. 2) How many non-partitioned indexes you have for the tablespace 3) How many rows have the table

This give me more details to give you and answer instead a guess.



-----Original Message----- From: Mohammed Nayeem [mailto:Mohammed_Nayeem@MOMED.COM] Sent: Thursday, December 21, 2000 3:25 PM To: DB2-L@RYCI.COM Subject: Reorg [...] 23541 66 9_Re: Reorg0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Dec 2000 15:41:55 -0600312_us-ascii Nayeem, At the last DB2 tech conference, one of the DB2 developers tried to make this as obvious as possible. If I remember correctly, he said: "If your tablespace needs to be reorged, you should use sortdata. If your tablespace does not need to be reorged, then why are you running reorg on it?". [...] 23608 125 42_Re: Anyone using DSNUTILS Stored Procedure12_David Booher22_David.Booher@QUEST.COM31_Thu, 21 Dec 2000 13:59:27 -0800415_iso-8859-1 I would be interested also. Scott is right, the IBM manuals do not show any examples of what additions should be made to the WLM started task.

Dave Booher Systems Programmer Quest Software



-----Original Message----- From: Scott Goodell [mailto:scott.goodell@WCOM.COM] Sent: Thursday, December 21, 2000 3:22 PM To: DB2-L@RYCI.COM Subject: Anyone using DSNUTILS Stored Procedure [...] 23734 96 9_Re: Reorg15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Thu, 21 Dec 2000 16:20:31 -0600807_us-ascii Yep I am using this way

REORG TABLESPACE XXXXXXXX.YYYYYYYY SORTDATA SORTKEY

How many Sort files is required ? We have 38 partitions.







---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000 04:19 PM ---------------------------



Tim.Lowe@STPAUL.COM on 12/21/2000 03:41:55 PM

Please respond to DB2 Data Base Discussion List

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

Subject: Re: Reorg





Nayeem, At the last DB2 tech conference, one of the DB2 developers tried to make this as obvious as possible. If I remember correctly, he said: "If your tablespace needs to be reorged, you should use sortdata. If your tablespace does not need to be [...] 23831 17 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Rob Wright17_rwright@LIC.CO.NZ31_Fri, 22 Dec 2000 11:41:44 +1200534_us-ascii We don't specify sortwork datasets in our utilities (SWnnWKnn) - SORT allocates them as required and automatically recovers from B37 etc. We use SYNCSORT, but DFSORT supports this too - see your nearest friendly Systems Programmer or the SORT doco for further information.

Rob

================================================ 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. 23849 143 27_Re: Buffer pool question...8_ritu zee22_ritu_98_2000@YAHOO.COM31_Thu, 21 Dec 2000 14:54:29 -0800395_us-ascii James, Ed, Michael, Cliff,

Thanks for the reply; though my ordinary senses responded most to James's 'down to earth' replay. Things seem to be getting clearer now. But one last question …..in this scheme of real and expanded storage, where does 'virtual storage' come into picture? I know buffer pools reside in virtual storage but is this real storage or expanded storage. [...] 23993 127 48_Re: ESS Shark and Disaster Recovery through PPRC16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 21 Dec 2000 23:55:14 +0100446_us-ascii Andy,

thanks a bunch, I'm trying to download the stuff as I write.

merry x-mas btw.

Aurora Emanuela Dell'Anno Systems Engineer Sistemi Informativi S.p.A. e-mail: aurora.dellanno@sistinf.it tel. +39 335 7429486

no trees were killed in the sending of this message. however - a large number of electrons were seriously inconvenienced.

*std disclaimer* - my opinions are my own (and not my company's) [...] 24121 27 11_BUFFER POOL14_Karthik Ganesh21_karthik_gus@YAHOO.COM31_Thu, 21 Dec 2000 15:01:24 -0800624_us-ascii Hi list, joel & sanjeev,

Thanks for the responses; Things seem to be getting clearer now.

Will get back to you guys once I get into these more. Appreciate your inputs.



KArthik



__________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 24149 62 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Dec 2000 17:47:33 -0600373_us-ascii Rob, We dynamically allocate our sortworks. (doesn't everyone today?) But that is not where the B37s typically cause a problem, the problems occur with SYSREC, SYSUT1 and SORTOUT. Obviously, using shrlevel reference, it is these datasets (and the B37s associated with them) that can go away in most cases (unless you don't have an explicit clustering index). [...] 24212 94 57_Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?10_Rob Wright17_rwright@LIC.CO.NZ31_Fri, 22 Dec 2000 13:04:09 +1200365_us-ascii Hi Tim

Don't have the post I responded to, but it talked about sort work, which is why I posted this. WRT to the other datasets, sometimes the best way around it is to use smaller primary/secondary allocations and expect datasets to take extents. Sometimes you will have 2000 cylinders available but the actual cylinders are in small 'chunks'. [...] 24307 191 27_Re: Buffer pool question...22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 21 Dec 2000 19:03:18 -0500395_iso-8859-1 Virtual storage is an operating system concept. Virtual storage is abstract. OS/390, Unix, Linux, Windows implement it.

Real storage is the physical memory; like the RAM chips in your PC. Expanded storage is also physical memory. In the "pre CMOS" days it was physically separate from real memory. "Post CMOS" expanded storage is the same physical memory as real storage. [...] 24499 59 30_Re: castout engine unavailable7_Soo Lee17_soo7lee@YAHOO.COM31_Thu, 21 Dec 2000 16:14:56 -0800390_us-ascii Thanks Max...

But there are no engine unavailable conditions(write engine, prefech engine) in db2 subsystem. And volumes in system are very fast (below 10 ms).

Total GBP size is 69% of total local buffer pools size (VP+HP). Is GBP size too large? Does large GBP size raise a problem(such as castout engine unavailable) ? and DB2 version is 5. please, help me... [...] 24559 86 47_Re: Contents of package - local vs. remote bind15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Fri, 22 Dec 2000 11:40:30 +1100402_- James,

Our Local and Remote SYSPACKSTMTS appear to be the same.

We do our Remote Bind Copy as a 2 phase process, mainly due to RACF restrictions ( not having an appropriate secondary authid for the DB on both systems we use a primary authid instead ). The remote job is really just a normal REBIND so maybe you could try rebinding on your remote subsystem, if you haven't already. [...] 24646 153 9_Re: Reorg0_19_Tim.Lowe@STPAUL.COM31_Thu, 21 Dec 2000 18:54:41 -0600421_us-ascii Nayeem, When reorganizing very large tablespaces, I generally use the SORTNUM 32. But, I think that your sort program can override this. For example, I know that even if I specify SORTNUM 32, it can use fewer sortworks based on the number of rows and the rowsize that reorg passes it. Obviously, I dynamically allocate my sortworks. And, if sort uses fewer sortwork datasets, then it must make them larger. [...] 24800 97 27_Re: Buffer pool question...14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 22 Dec 2000 12:06:05 +1100605_iso-8859-1 Real memory is the physical place that memory is stored. It is addressed by a 31-bit address. (I'll ignore zArchitecture and 64-bit addresses for this reply. The discussion is the same, just a few details different).

In a virtual storage system (of which MVS, OS/390, z/OS is but one) the address that is supplied in an instruction is not (necessarily, some are) the real address that will be used. The address goes through a process called 'translation' that determines the real address to be used. In MVS (Multiple VS), each address space has its own set of translation tables to [...] 24898 110 51_Re: Paging :- QMF & Stored Proc guru (& guru-ettes)14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 22 Dec 2000 12:09:19 +1100329_iso-8859-1 No, QMF cannot handle an SQL CALL. Even with no parameters. Even QMF 3.3.1 (aka V6)

I suspect Nick might even have problems calling a non-QMF rexx program that invokes the rexx-db2 interface. QMF uses CAF and rexx-db2 uses RRSAF (at least it does in DB2 V6) - and they cannot be mixed in an address space. [...] 25009 22 15_I/O Parallelism0_20_bjnigh@HOUSEHOLD.COM31_Thu, 21 Dec 2000 17:07:11 -0800507_us-ascii Is it possible to get I/O parallelism on a LOAD RESUME YES job that loads a partitioned tablespace? If CPU usage is over 70%, does the benefit of CPU parallelism get diminished?

We currently load our data warehouse by date and thus we partition by date. Would there be any advantage to partitioning our tablespaces by using a random key (hash) to enable the partitions to be loaded equally? Does this help out the load job or just enable queries to complete in a faster manner or both? [...] 25032 43 31_SHRLEVEL Change - Inline copies25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Fri, 22 Dec 2000 10:35:19 +0800771_us-ascii Hi,

I have two questions

1. I was trying to dsn1copy from an image copy taken during a shrlevel change reorg and I hit the following problem

DSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS: CHECK/NO PRINT/ 4K/FULLCOPY /NON-SEGMENT/NUMPARTS = 0/ OBIDX DSN1998I INPUT DSNAME = TINLWA2X.INLWA1R1.$BUSDET.REORG , SEQ DSN1997I OUTPUT DSNAME = TINLWA2X.DSNDBC.INLWA1R1.$TEST.I0001.A001 , VSAM DSN1984I UNEXPECTED PAGE NUMBER, EXPECTING: 000012.. 0000 1003956F B2C38F00 00000130 01AC0004 01A80005 00000011 00000000 00000002 0020 0014C0FF FF000000 03001480 FFFF0000 00040014 80FFFF00 00000000 1480FFFF .... LINES ARE ALL ZERO. 0F20 00000000 00000000 40000000 00000000 00000000 00000000 00000000 00000000 .... LINES ARE ALL ZERO. 0FE0 [...] 25076 91 35_Re: SHRLEVEL Change - Inline copies14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 22 Dec 2000 14:11:34 +1100491_iso-8859-1 1) try INLCOPY instead of FULLCOPY 2) ah, the ways of DB2 are mysterious indeed. You could try shouting, screaming and submitting a change request to IBM. But it will probably be met with "yes, it's a pity that you can't change these, but there are ways [such as you mentioned] to handle this situation, it should only happen in test, our resources are limited, we have more important (as measured by customer feedback) things to do - we'll put in our 'to do someday' list." [...] 25168 18 52_What if recovery fails after reorg/load with log no?9_J Sullens21_pleniumcorp@YAHOO.COM31_Thu, 21 Dec 2000 23:18:39 -0600425_- Dear List,

If reorg/load with log no was done followed by a full imagecopy on a tablespace and subsequently after couple of days (during which only incremental imagecopies were taken) a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other than ........proceeding on a sudden and emergent medical leave :-( . [...]