1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2001, week 1 2 156 49_Intermittent APPC concersation failures. Pls help13_Mike Drummond19_mike@SERVICERUS.COM30_Mon, 1 Jan 2001 10:18:07 -0600568_- Hello everyone,

We are having a strange problem.... A client application (SQR at Win NT client) intermittently fails with following message:

>>>> SQL Error Message Description <<<<< SQL Status = *****, SQL Error = [IBM][CLI Driver][DB2] SQL30081N A communication error has been detected. Communication protocol being used: "APPC". Communication API being used: "CPI-C". Location where the error was detected: "USWXYZ01.PS43LU02". Communication function detecting the error: "cmrcv". Protocol specific error code(s): "20", "*", "*". SQLSTATE=08001 [...] 159 24 18_MAD MAX goes again12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Tue, 2 Jan 2001 20:41:31 +0200295_iso-8859-1 Hi Arli, You are correct in yourr assumptions about the index and the performance gain it can contribute. If I could have built an index per each query .... As a ROT I try to add indexes as needed and as long as they don't disrupt "inset/update/delete" activity. Happy New Year. [...] 184 20 19_when to do Runstats14_frankzhang200023_frankzhang2000@SINA.COM30_Mon, 1 Jan 2001 23:13:34 -0600414_- Hi all, I have a question about when to do runstats?Should I do it before I bind program/plan/package after the change of database structure?I remember an error(Perhaps,I am not sure) that I made after modifying the structure of many tables,I don't do runstats before bind programs,and the result is the same as I have not binded the programs,I am new to DB2,so anyone here pls give me some recommendation. [...] 205 86 23_Re: when to do Runstats16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 11:01:40 +0530519_iso-8859-1 Victor, I think there should the installation standard for running the RUNSTAT i.e weekly or something like this. Other than this there should be some special occasion i.e after REORG or before Bind/Rebind or Alter object etc. RUNSTATS are used for giving the optimizer the correct information to determine the access paths. There are exceptions when RUNSTATS are not run for some particular set of objects when we want the optimizer the use the one time best information for access path determination. [...] 292 19 23_Re: when to do Runstats14_frankzhang200023_frankzhang2000@SINA.COM30_Tue, 2 Jan 2001 17:50:41 +0800399_- Hi, Thanks for your reply,from your answer,that means runstats only has something to do with optimizing application access path to db2 objects,if I don't do runstats after the modification of db2 structure,ie. change some table structure,drop and recreate tablespace,and bind the application program,there will be no harm except that will make application run not at the optimal performance? [...] 312 53 28_Re: Problem with ADO update.11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Tue, 2 Jan 2001 12:14:31 +0200346_iso-8859-7 Hi Navid and Happy New Year! I have faced that problem too when making some tests.It has to do with authorization (i think on system catalog tables).Testing the ADO application with my own userid (=sysadm) i had no problem.But with other userids,that problem arose. "Play" with authorities and you will find the way out! Regards, [...] 366 92 23_Re: when to do Runstats16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 16:04:16 +0530463_iso-8859-1 Hi Victor,

Major reason is ofcourse performance but there is one more thing which DBAs have to look is the proper documentation and the current catalog statistics give the DBAs good information for analysing the data and the database of the installation. Other thing is there are many inhouse developed tools/utilities which use catalog information to give some reports or take some action like Conditional Reorg, Data Skewing pattern etc. [...] 459 83 23_Re: when to do Runstats5_elibs17_elibs@NETEASE.COM30_Tue, 2 Jan 2001 19:16:36 +0800418_iso-8859-1 Hi, Sanjeev:

I don't quit understand the meaning of 'There are cases when catalog gets updated automatically and there are cases when you need to run Reorg and then RUNSTATS.'

What I understand is that once the DB2 updates its catalog automatically, we need to REORG ,RESTATS and BIND? Is it right? But how can we know when the DB2 do it except the cases that the DB2 tables are altered? [...] 543 28 12_Ado problem!10_Navid Khan21_navid@DPI2.DPI.NET.IR30_Fri, 2 Jan 1998 15:14:32 +0330374_US-ASCII "Best wishes in new year for all list members!" Hi Popy, Happy new year! I want to thank Popy for his help. But I steel have my problem, after some tests I find that if I use adUseClient as my courser option (in ado.connection) I'll have that problem (I vcan't update any record) and if I use adUseServer type cursor I can't do some thing like move previouse. [...] 572 212 23_Re: when to do Runstats16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 17:15:38 +0530337_iso-8859-1 Hi Elbis,

I think that the sentence was not quite clear. I meant to say that if you alter something or drop something, catalog gets affected. Either entry is done or removed. But at the same time many columns of the catalog tables are not updated corresponding to that entry and RUNSTATS is used to updated those. [...] 785 47 36_REPAIR DBD complains about DB in use0_18_mebert@AMADEUS.NET30_Tue, 2 Jan 2001 12:45:40 +0100541_us-ascii Hello List,

I just discovered this little (?) problem in one of our development databases (DB2 V5, OS390):

DSNT360I + *********************************** DSNT361I + * DISPLAY DATABASE SUMMARY * GLOBAL DSNT360I + *********************************** DSNT362I + DATABASE = DDS1J1 STATUS = RW DBD LENGTH = 1928626453

Obviously something is shot (see the DBD Length). I tried to run the REPAIR Utility with the TEST DBD keyword, after starting the DB (and later all TSs as well) in UT mode. This is what I got: [...] 833 163 39_Re: Foreign Key indexes - when and how?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 17:30:44 +0530333_iso-8859-1 Hi Don, Thanks for pointing out. If i can recall the Admin Guide context related to indexes on foreign key then it says : "Although not required but it is strongly recommended if the rows from the parent table are deleted very often and the delete rule is defined".I think tThis is mainly for the performance reason. [...] 997 198 38_R: Foreign Key indexes - when and how?20_Carlo Petrone WZBKD627_carlo.petrone@ISTRUZIONE.IT30_Tue, 2 Jan 2001 13:27:18 +0100537_iso-8859-1 you can create an index if the dependent table do not have on first position of primary key the coulumn of relation.

es. parent table pk Col1, Col2, Col3

fk Col1, Clo2, Col3

dependent Table pk Col1, Col2, Col3, Col4

in this case you connot create an index



-----Messaggio originale----- Da: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]Per conto di S, Sanjeev (CTS) Inviato: martedì 2 gennaio 2001 13.01 A: DB2-L@RYCI.COM Oggetto: Re: Foreign Key indexes - when and how? [...] 1196 158 39_FW: Foreign Key indexes - when and how?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 18:37:37 +0530531_iso-8859-1 ** Resending this one as message got rejected because it exceeded 250 lines.

> Hi Carlo, > In your example : fk Col1, Clo2, Col3, which are the part of Pk > of dependent table is referenced by the parent table.I think the index on > the foreign key already exists , however, it is the primary(unique) index. > Most importantly , it will be used because the first few columns are same. > Col4 is at the end. > > Please correct me if i am wrong. > > Regards. > Sanjeev > > > -----Original Message----- > From: [...] 1355 160 17_Re: 4GB partition16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 2 Jan 2001 17:24:15 +0530282_iso-8859-1 1) Change the UNION ALL query to FULL OUTER JOIN with value clause. 2) Wait for V7 for UNION ALL in view to come.(Raised in end 1999 or begining 2000). If archive is there, check out Richard Yevich's reply of my mail) 3) Compress the TS as suggested by Dr. Michael. [...] 1516 17 39_Re: Foreign Key indexes - when and how?13_Henry J. Cobb14_hcobb@SLIP.NET30_Tue, 2 Jan 2001 07:25:58 -0600435_- I usually find that the foreign key is used for certain joins.

So when it isn't the prefix of the primary key I find myself defining an index on the foreign key eventually.

-HJC

================================================ 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. 1534 39 47_FW: Web access to DB2 on S390 in the real world14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 2 Jan 2001 14:02:26 -0000338_iso-8859-1 I'm reposting the attached question as I received NO responses AT ALL.

I find this rather surprising when you consider how much everyone is telling us that e-Business is driving DB2.

Maybe it is, but not just in the UK - anyway, please drop me a line (phil.grainger@ca.com) if the following sounds like you! [...] 1574 31 23_Re: when to do Runstats10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 2 Jan 2001 16:09:30 +0100659_us-ascii Hi Victor,

take a look at Gabrielle Wiorkowski's site

http://www.gabrielledb2.com/

There's a PDF document with guidelines for RUNSTATS & REORGs at the URL:

http://www.gabrielledb2.com/rr.pdf



HTH

Regards

Max Scarpa DB2 sysprog Happy new year....without any DB2 Odyssey .... :-))))

All disclaimers in HAL9000's 64-furlongbit Central Storage apply

================================================ 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. 1606 22 40_Re: REPAIR DBD complains about DB in use10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 2 Jan 2001 16:19:39 +0100462_us-ascii Hi mebert

You didn't post any error message DSNxxxx, but take a look at :

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

and search for PQ39794 or II11173.

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. 1629 187 56_Intermittent APPC concersation failures. Pls help -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM30_Tue, 2 Jan 2001 10:24:31 -0500393_US-ASCII Hello Mike,

I've seen this error when a client application executes some SQL and then does some other (non-DB2) processing for a period of time sufficient to reach the idle thread timeout limit. When the client application returns to execute more SQL, it assumes the thread to DB2 is still connected but it has timed out. The result is the connection errors you describe. [...] 1817 13 16_CSP and DB2 V6.112_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Tue, 2 Jan 2001 09:36:00 -0500382_- Anyone with CSP/370/AD V4.1 and CSP370/RS V2.1 have any feedback, either positive or negative, with V6.1 of DB2?

Thanks in advance.

================================================ 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. 1831 57 40_Re: REPAIR DBD complains about DB in use12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Tue, 2 Jan 2001 09:23:00 -0500369_- We've used DBD rebuild about a year ago in one of our busiest production DB2s (not for your exact problem) and it worked fine with no problems found as of today. We only did this under recommendation from IBM. We also did the REPAIR DBD DIAG on the problem database to collect more info on the problem. >>> DB2-L@RYCI.COM@inter2 01/02/01 05:45AM >>> Hello List, [...] 1889 37 6_DSNRLI15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Tue, 2 Jan 2001 10:43:13 -0500612_iso-8859-1 I am extremely new to CAF processing - Can someone help me understand this?

I am trying to use DSNRLI to connect to DB2 so I can access a stored procedure in a WLM environment.

I get an sqlcode of: -991 CALL ATTACH WAS UNABLE TO ESTABLISH AN IMPLICIT CONNECT OR OPEN TO DB2. RC1= rc1 RC2= rc2 Programmer Response: Verify that the application intended to use the call attachment facility (CAF) as the mechanism to connect to DB2. For stored procedures running in the WLM-established stored procedure address space the application must be link-edited with or dynamically allocate the [...] 1927 16 14_Top Secret/DB215_Steven Morrison33_steven.morrison@FIRSTDATACORP.COM30_Tue, 2 Jan 2001 09:44:50 -0600329_- We are looking for any body who has implemented or taken a look at Computer Associates Top Secret for DB2 product to replace DB2 grants and control access to all DB2 objects. Any issues or problems. If you like, you can repond directly to me at steven.morrison@firstdatacorp.com.

Thankyou in advance Steve Morrison [...] 1944 39 10_Re: DSNRLI13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 2 Jan 2001 11:01:35 -0500584_iso-8859-1 Hi Rick,

I'm not sure I'll be able to help.

Is it your calling program that gets the -991?

It sounds like you might be confusing 2 different attachment facilities. You reference CAF and the -991 does also. But you also mention DSNRLI.

There are 2 separate attachment facilities here (there are others too TSO attach, IMS, CICS, ...) - CAF is the older Call Attach Facility & uses Entry DSNALI - RRSAF is the newer Recoverable Resources Manager Services Attach Facility and is required for WLM-mangaged Stored Procedures & uses Entry DSNRLI [...] 1984 63 10_Re: DSNRLI16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 2 Jan 2001 10:58:37 -0500454_iso-8859-1 Rick, Add //DSNTRACE DD SYSOUT=* to your JCL and you will get better CAF diagnostics. Some are quite approachable (reason codes), and some require access to the dread Diagnosis Reference.





regards,

eric pearson NS ITO Database Support



-----Original Message----- From: McClendon, Rick [mailto:RMcClendon@ADMIN.FSU.EDU] Sent: Tuesday, January 02, 2001 10:43 AM To: DB2-L@RYCI.COM Subject: DSNRLI [...] 2048 26 18_Re: Top Secret/DB212_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Tue, 2 Jan 2001 10:17:00 -0500570_- We looked at it here, but our security folks found it did not do everything they wanted it to do. Sorry, I don't have any details. In the beginning of implementation, it caused ABENDS on DB2 startup (C6s). They have fixed this problem. >>> DB2-L@RYCI.COM@inter2 01/02/01 09:44AM >>> We are looking for any body who has implemented or taken a look at Computer Associates Top Secret for DB2 product to replace DB2 grants and control access to all DB2 objects. Any issues or problems. If you like, you can repond directly to me at steven.morrison@firstdatacorp.com. [...] 2075 38 22_Index creation problem15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Tue, 2 Jan 2001 10:47:53 -0600526_us-ascii Good Morning and wish you happy and prosperous new year.

I was trying to modify one of the index's by recreating index after dropping index.

Following message I was getting :

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL 00000230, AND RESOURCE NAME 4K SQLSTATE = 57011 SQLSTATE RETURN CODE SQLERRP = DSNXICX2 SQL PROCEDURE DETECTING ERROR SQLERRD = 130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION SQLERRD = X'00000082' X'00000000' X'00000000' X'FFFFFFFF' X'000 INFORMATION [...] 2114 68 40_Re: REPAIR DBD complains about DB in use13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Tue, 2 Jan 2001 10:52:00 -0600498_US-ASCII Hello Michael, There was a PTF for the incorrect DBD length. Sorry I can't recall the fix #. Kurt



>>> mebert@AMADEUS.NET 01/02/01 05:45AM >>> Hello List,

I just discovered this little (?) problem in one of our development databases (DB2 V5, OS390):

DSNT360I + *********************************** DSNT361I + * DISPLAY DATABASE SUMMARY * GLOBAL DSNT360I + *********************************** DSNT362I + DATABASE = DDS1J1 STATUS = RW DBD LENGTH = 1928626453 [...] 2183 21 26_Re: Index creation problem10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 2 Jan 2001 17:55:34 +0100473_us-ascii HI.....

I think your index is too big and your workfiles are not big enough. Try CREATE INDEX ........DEFER YES and use

RECOVER/REBUILD INDEX with enough sort files (SORTNUM > 12)

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. 2205 133 26_Re: Index creation problem21_Helio Pimpinato Rocha20_hrocha@TELESP.COM.BR30_Tue, 2 Jan 2001 14:59:17 -0300439_us-ascii This occurs because the temporary store is small. Then create the index wich "defer = yes ", after you'll make the recover of this index.

Ok

Helio







Mohammed Nayeem Enviado Por: DB2 Data Base Discussion List 02/01/01 13:47 Responder a DB2 Data Base Discussion List



Para: DB2-L@RYCI.COM cc: Assunto: Index creation problem [...] 2339 70 26_Re: Index creation problem15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Tue, 2 Jan 2001 10:57:48 -0600434_iso-8859-1 Hello, Mohammed,

The 00000230 indicates that a temporary workfile is not available. The SQLSTATE indicates "..Virtual storage or database resource is not available.". Something is being dynamically allocated by your SQL, probably in DSNDB07. Check to see if there is a problem there.



Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin [...] 2410 83 26_Re: Index creation problem14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Tue, 2 Jan 2001 10:59:08 -0600315_iso-8859-1 You may be running out of space in the sortwork database (DSNDB07).

Look in the JES log for the MSTR address space. If you have run out of sort space you will see some messages.

There are two ways to get round this.

1) Increase the size or number of your sort work tablespaces. [...] 2494 78 26_Re: Index creation problem10_CEI, Bruno20_Bruno.CEI@SEMA.CO.UK30_Tue, 2 Jan 2001 17:14:41 -0000508_ISO-8859-1 Mohammed,

I think that you are running out of space on your work database (DSNDB07).

You can choose either to increase the size of the work space available to DB2 or, which is probably best, create the index with the DEFER option and then use the REBUILD utility to populate it.

Bruno





-----Original Message----- From: Mohammed Nayeem [mailto:Mohammed_Nayeem@MOMED.COM] Sent: 02 January 2001 16:48 To: DB2-L@RYCI.COM Subject: Index creation problem [...] 2573 122 23_Re: when to do Runstats10_CEI, Bruno20_Bruno.CEI@SEMA.CO.UK30_Tue, 2 Jan 2001 17:23:26 -0000443_ISO-8859-1 Hi Victor,



I agree with Sanjeev that the worst problem is performance. Anyway, I'd like to add that "not optimal" in some cases can mean that performance is absolutely unacceptable. Ona last thing : after you have run a RUNSTATS utility, you need to REBIND the affected plan/packages using the new information available in the catalog. Only after doing this you will be using the newly optimised access to data. [...] 2696 14 26_Re: Index creation problem15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Tue, 2 Jan 2001 11:39:41 -0600335_us-ascii Thanks to all of you for responding quickly.

Yes I am using DEFER NO option .

================================================ 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. 2711 21 26_Re: Index creation problem15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Tue, 2 Jan 2001 11:42:21 -0600429_us-ascii CREATE TYPE 2 UNIQUE INDEX INX1 ON tableX (COL1 ASC, COL2 ASC, COL3 ASC, COL4 ASC) USING STOGROUP XXXXXXXX PRIQTY 200000 SECQTY 100000 ERASE NO BUFFERPOOL BP0 CLOSE YES DEFER NO;

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2733 15 26_Re: Index creation problem15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Tue, 2 Jan 2001 11:53:37 -0600457_us-ascii I am using db2 batch job for index creation and If I use sort files , will it solve problem. I am using DEFER NO option only. Yep I need to run REBUILD INDEX once index is created for populating this index.

================================================ 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. 2749 22 36_REPAIR DBD complains about DB in use12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 3 Jan 2001 21:27:42 +0200494_iso-8859-1 Hi,

I did it few times, no problem. Try to stop and start it before that, to see if the "test" will give you better response. I didn't get a dbsize like you got.



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. 2772 20 43_Web access to DB2 on S390 in the real world12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 3 Jan 2001 21:33:06 +0200426_iso-8859-1 Hi Phil, I've sent you a private response. If you didn't get it - it has to do with your local mail.



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. 2793 102 10_AW: DSNRLI16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE30_Tue, 2 Jan 2001 22:48:33 +0100641_iso-8859-1 DSNTRACE is for CAF (DSNALI) For DSNRLI you need //DSNRRSAF DD DUMMY but this will provide more debug-info in a SYSDUMP

Roland







> -----Ursprüngliche Nachricht----- > Von: Pearson, Eric L, [SMTP:Eric.Pearson@NSCORP.COM] > Gesendet am: Dienstag, 2. Januar 2001 16:59 > An: DB2-L@RYCI.COM > Betreff: Re: DSNRLI > > Rick, > Add //DSNTRACE DD SYSOUT=* to your JCL and > you will get better CAF diagnostics. > Some are quite approachable (reason codes), > and some require access to the dread Diagnosis Reference. > > > > regards, > > eric pearson > NS ITO Database Support > > > -----Original [...] 2896 65 10_AW: DSNRLI16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE30_Tue, 2 Jan 2001 22:51:11 +0100604_iso-8859-1 Please provide infos about your Binder/Linkage-editor statements

roland

> -----Ursprüngliche Nachricht----- > Von: McClendon, Rick [SMTP:RMcClendon@ADMIN.FSU.EDU] > Gesendet am: Dienstag, 2. Januar 2001 16:43 > An: DB2-L@RYCI.COM > Betreff: DSNRLI > > I am extremely new to CAF processing - Can someone help me understand > this? > > I am trying to use DSNRLI to connect to DB2 so I can access a stored > procedure in a WLM environment. > > I get an sqlcode of: > -991 CALL ATTACH WAS UNABLE TO ESTABLISH AN IMPLICIT CONNECT OR OPEN TO > DB2. RC1= rc1 RC2= rc2 > Programmer [...] 2962 13 29_DB2 Technical Conference 20010_20_bjnigh@HOUSEHOLD.COM30_Tue, 2 Jan 2001 16:37:27 -0800340_us-ascii Does anyone know where the IBM DB2 Technical Conference is being held in the US for 2001?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2976 27 33_Re: DB2 Technical Conference 200114_Jack O'Connell26_jack.oconnell@MCMASTER.COM30_Tue, 2 Jan 2001 18:46:33 -0600578_- I believe it is in Orlando in Oct.

-----Original Message----- From: bjnigh@HOUSEHOLD.COM [mailto:bjnigh@HOUSEHOLD.COM] Sent: Tuesday, January 02, 2001 6:37 PM To: DB2-L@RYCI.COM Subject: DB2 Technical Conference 2001



Does anyone know where the IBM DB2 Technical Conference is being held in the US for 2001?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 3004 18 33_Re: DB2 Technical Conference 200113_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 2 Jan 2001 19:51:04 -0500450_iso-8859-1 See http://www-3.ibm.com/services/learning/conf/us/index.html

Oct. 1-5, 2001 Orlando, Florida

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. 3023 27 22_DB2 Certification Cost15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Wed, 3 Jan 2001 12:11:34 +1100385_iso-8859-1 Colleagues (especially those of you certified),

I've just been in touch with IBM Learning Services in Melbourne. They tell me that to take exams 509 and 510 to be a certified DB2 UDB V6 DBA on Windoze etc. costs AUD185 each, or about £70 in real money. Does anyone know of another, cheaper, way to take the exams? Or am I just being a tight illegitimate person? [...] 3051 81 26_Re: DB2 Certification Cost15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 3 Jan 2001 09:30:17 +0100433_iso-8859-1 Raymond,

You might want to visit IDUG or the IMS/DB2 Tecchnical Conference. During these conferences you can take a lot of the Certification exams for free. Besides these confernces are execellent opportunities to learn all the ins and outs on DB2!

Kind regards,

Jaap Slot, DB2 DBA, Rabobank ICT

Phone 0031 (0)30 215 2220 Mobile 0031 (0)6 5374 0167 e-Mail [...] 3133 69 26_AW: DB2 Certification Cost12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 3 Jan 2001 10:05:11 +0100341_iso-8859-1 Raymond,

come to one of the IDUG (International DB2 Users Group) conferences and you will have the chance to get the most certification examinations FOR FREE.

The last Annual Asia Pacific Conference in your area (I believe you are downunder in Australia, true ?) was held from 15-17 November 2000 in Melbourne. [...] 3203 15 60_Re: Intermittent APPC concersation failures. Pls help -Reply13_Mike Drummond19_mike@SERVICERUS.COM30_Wed, 3 Jan 2001 04:35:22 -0600356_- Thanks John for your reply. Isn't IDTHTOIN the timeout parameter? We have set it to Zero. So connection timeout should not occur,Correct?

Does it have anything to do with Microsoft SNA Server? Pattern has been DRDA send is issued to DB2 and DRDA Receive gets this error, with the SNA message saying 'TP_SUBSYSTEM_ABENDED' on DB2 client side. [...] 3219 11 40_Re: REPAIR DBD complains about DB in use18_Sandra Lakenburger34_sandra.lakenburger@MAIL.SPRINT.COM30_Wed, 3 Jan 2001 08:31:18 -0600337_- I think the REPAIR DBD utility may require the database to now be stopped to allow it to run.

================================================ 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. 3231 25 33_Create Global Temp Table question15_Amy C Whitehead14_B06ACW@FDS.COM30_Wed, 3 Jan 2001 09:43:18 -0500422_us-ascii Hi, Due to being on v5 in production, we have to use the non-indexable 'create global temp table' syntax for GTTs. Eventhough this execution is successfull, we always get a -601. A -601 says that the table already exists. How do we prevent this error message? Here is the syntax that is embedded in the cobol program: EXEC SQL CREATE GLOBAL TEMPORARY TABLE SESSION.TEMPTAB (cola CHAR(20) NOT NULL) END-EXEC. [...] 3257 120 45_Can't get rid of "Prefetch Queue Full" error!14_Scott, Matthew18_MScott@ALFAINS.COM30_Wed, 3 Jan 2001 08:33:25 -0600530_iso-8859-1 I'm new to DB2 on NT and am running an imaging application. I get the following error on a consistent basis. I have increased the num_ioservers parm to twice what the manuals suggest (I have 3 physical disks and 12 ioservers). I have made sure that the prefetch size matches or is a factor of the extent size (both are 32k) . I have increased the size of my buffer pools to where my hit ratio is acceptable (above 85%). I have also increased the number of page cleaners. I don't seem to be able to get rid of this [...] 3378 60 39_DSN1COMP-Utility and the SIZE-parameter12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 3 Jan 2001 15:51:30 +0100714_iso-8859-1 First a happy and successful New Year 2001 to all of you out there.

Second a question: When we're using the DSN1COMP-Utility we declare

//SYSIN DD * DSN1COMP SIZE(1024) ROWLIMIT(20000)

That works very well with "normal" tablespaces because most of our tablespaces are residing in 4K-bufferpools.

If we ran the utility against a tablespace that is residing in a 32K-bufferpool we received a CC8 and the messages: IGD104 I - DSNDBCAT.DSNDBC.LEWDBDIG.LEWTSDIG.I0001.A001 RETAINED DDNAME=SYSUT1 and a lot of DSN1990I DSN1COMP BROKEN PAGE. PAGENUMBER = 00000000. " DSN1990I DSN1COMP BROKEN PAGE. PAGENUMBER = 00000001. " DSN1990I DSN1COMP BROKEN PAGE. PAGENUMBER = 00404040. [...] 3439 77 37_Re: Create Global Temp Table question0_26_JCameron@MSI-INSURANCE.COM30_Wed, 3 Jan 2001 08:49:08 -0600415_us-ascii Amy,

You only need to define the global temporary table once. Your COBOL program does not need to re-create it. Only the definition is stored in the catalog. Only when your program is using it does the GTT actually "exist." If your GTT has been defined, remove the Create GTT DDL from your program. If multiple users are running the same program, each user will have their own copy of the GTT. [...] 3517 34 37_Re: Create Global Temp Table question10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Wed, 3 Jan 2001 08:48:52 -0600602_- Although the actual data only exists during the current unit of work, the Table (unlike the V6 Declared Temporary Table) is defined on the Catalogue. You must either Drop it before the Create or Reuse it.



On Wed, 3 Jan 2001 09:43:18 -0500, Amy C Whitehead wrote:

>Hi, >Due to being on v5 in production, we have to use the non-indexable 'create >global temp table' syntax for GTTs. Eventhough this execution is >successfull, we always get a -601. A -601 says that the table already >exists. How do we prevent this error message? >Here is the syntax that is [...] 3552 56 37_SV: Create Global Temp Table question25_Heidorn Sven IT Sundsvall19_Sven.Heidorn@CSN.SE30_Wed, 3 Jan 2001 15:57:12 +0100342_iso-8859-1 Amy,

you only need to create the GTT once when using created GTT's, like any other table the definition is stored in the catalog. The physical table is created when you refer to it in your Cobol program (INSERT INTO) and deleted when you commit. You get the -601 because the table definition is already in the catalog. [...] 3609 89 43_Re: DSN1COMP-Utility and the SIZE-parameter15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 09:00:27 -0600344_iso-8859-1 Hello, Georg, I cannot find the SIZE parameter in the utility manual but I have used PAGESIZE(32K) to indicate that I am using a tablespace with 32K pages. The default page size is 4K. Perhaps this will help.

Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin [...] 3699 22 43_Re: DSN1COMP-Utility and the SIZE-parameter10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 3 Jan 2001 16:01:07 +0100463_us-ascii Hi all.....

There's a parameter 32K to declare if SYSUT1 dataset is 32K size in DSN1COMP, as in DSN1COPY.

HTH

Max Scarpa Db2 sysprog

Of course all disclaimer from here to 3C-273 apply.

================================================ 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. 3722 24 13_LOAD Question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Wed, 3 Jan 2001 09:08:07 -0600326_us-ascii Hi

Is LOAD DATA INTO TABLE1 PART integer RESUME yes REPLACE ........... replaces data from that particular partition or it replaces entire data from the Tablepsace???? I need to replace data only in one partition and data exist in other partitions too which I wanted it to be remain as it is. Any Idea??? [...] 3747 121 17_Re: LOAD Question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Wed, 3 Jan 2001 09:20:14 -0600529_us-ascii







Thanks Philip



I am using following Syntax for Load command :

LOAD DATA LOG NO INDDN SYSREC00 SORTKEYS SORTDEVT SYSDA SORTNUM 4 INTO TABLE TTTTTTTT PART 27 RESUME YES REPLACE

Will this syntax replaces part 27 or entire TS???



Regards Nayeem ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 01/03/2001 09:17 AM ---------------------------



Philip Gunning on 01/03/2001 09:15:10 AM [...] 3869 52 17_Re: LOAD Question10_Olle Nyman23_olny01@HANDELSBANKEN.SE30_Wed, 3 Jan 2001 16:23:45 +0100459_us-ascii Hi Mohammed!

It only replaces data in that particular partition! However, you can ignore the RESUME yes parameter, since it takes a lock on the entire tablespace and not only on the partition you want to replace.

Hope this helps!

/Olle









Mohammed Nayeem @RYCI.COM> on 2001-01-03 16:08:07

Please respond to DB2 Data Base Discussion List [...] 3922 50 17_Re: LOAD Question15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Wed, 3 Jan 2001 09:43:12 -0600302_iso-8859-1 A slight change to the syntax to make it clearer.

LOAD DATA RESUME YES INTO TABLE1 PART nnn REPLACE ........

As with anything you do, test it first in a test environment. Destroying a production table because you did not test it first is no way to get ahead in a company. [...] 3973 23 15_DB2 and Freeway12_Kirk Hampton16_khampto1@TXU.COM30_Wed, 3 Jan 2001 09:44:02 -0600309_us-ascii We are running DB2 v6.1 on OS/390 V2.8, on one G5 and one G6 CMOS processor (sysplexed, but no datasharing). We are about to install a 2064 (Freeway) processor as a replacement for the G5 box. Anyone else running DB2 for OS/390 on a 64-bit machine yet ? If so, any strangeness to look out for ? [...] 3997 236 49_Re: Can't get rid of "Prefetch Queue Full" error!14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Wed, 3 Jan 2001 08:01:49 -0800420_iso-8859-1 Matthew, Sounds like your problem could be due to a few possibilities:

1. You say you have a buffer hit ratio of 85%. This isn't bad but I would shoot for high 90's if you have the memory. I would increase the size of the bufferpool and see if it takes care of the problem. What is the size of it now? How big are the objects? Do you have tables and indexes broken out into separate table spaces? [...] 4234 77 49_Fw: Can't get rid of "Prefetch Queue Full" error!11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Wed, 3 Jan 2001 11:09:38 -0500369_us-ascii Matthew,

Set your NUM_IOSERVERS to 4 (disks + 1). 12 is way excessive for this hardware configuration.

Alter the tablespace and set the PREFETCHSIZE = Number of Containers X Extentsize.

I am guessing your tablespace is SMS and that there is a single container on one of your 3 disks. If this is the case, try using PREFETCHSIZE = 64. [...] 4312 186 17_Re: LOAD Question13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM30_Wed, 3 Jan 2001 10:27:49 -0600415_iso-8859-1 Aren't Resume and Replace opposites? Code one or the other, not both. A load replace of a partition deletes the data from that partition only.



-----Original Message----- From: Chris_Blaicher@BMC.COM [mailto:Chris_Blaicher@BMC.COM] Sent: Wednesday, January 03, 2001 9:43 AM To: DB2-L@RYCI.COM Subject: Re: LOAD Question



A slight change to the syntax to make it clearer. [...] 4499 79 17_Re: LOAD Question16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Wed, 3 Jan 2001 09:30:21 -0700596_- Here is the syntax I use and have used with no problems:

LOAD DATA LOG NO ENFORCE NO INDDN SYSREC00 INTO TABLE tablename PART 1 REPLACE

This will load partition 1 only and not effect the others.

Duane

> -----Original Message----- > From: Blaicher, Chris [SMTP:Chris_Blaicher@BMC.COM] > Sent: Wednesday, January 03, 2001 8:43 AM > To: DB2-L@RYCI.COM > Subject: Re: LOAD Question > > A slight change to the syntax to make it clearer. > > LOAD DATA RESUME YES INTO TABLE1 PART nnn REPLACE ........ > > As with anything you do, test it first in a test environment. [...] 4579 40 26_DSMAX - Number Of Datasets9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Wed, 3 Jan 2001 16:30:19 +0000322_us-ascii Hi, At OS390 2.8 DB2 V5.1...

Do any sites out there have >10000 of open datasets allocated to DB2 (i.e. with UQ22244 on - allowing >10000 dataset allocation - and SWA defined above 16Mb line) ?

If so, how many datasets ? Also, have you experienced any problems/issues with allocations > 10000? [...] 4620 24 27_TCP/IP Address: How to get?14_Richard Arnold24_Richard.Arnold@CHASE.COM30_Wed, 3 Jan 2001 10:41:51 -0600549_us-ascii Hello list:

Would someone please tell me the easiest way to get a subsystem's TCP/IP address?

Thanks in advance for your help.

Richard Arnold JPMorganChase

PS: This message is for list serve purposes ONLY, and MUST NOT be quoted by any VENDORS, such as BMC and CDB !!!!!

================================================ 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. 4645 52 31_Re: TCP/IP Address: How to get?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 10:54:47 -0600562_iso-8859-1 Hi, Richard,

You could execute DSNJU003 to get the port number from the BSDS. See the IBM DB2 Utility Guide and Reference for details, chapter 3.

Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde







-----Original Message----- From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] Sent: Wednesday, January 03, 2001 10:42 AM To: DB2-L@RYCI.COM Subject: TCP/IP Address: How to get? [...] 4698 42 31_Re: TCP/IP Address: How to get?14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Wed, 3 Jan 2001 10:53:50 -0600466_iso-8859-1 If TCP/IP is working, look in the JES log of the MSTR address space. During DB2 startup the TCP/IP addresses are displayed.

From Edward Vassie...



-----Original Message----- From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] Sent: 03 January 2001 16:42 To: DB2-L@RYCI.COM Subject: TCP/IP Address: How to get?



Hello list:

Would someone please tell me the easiest way to get a subsystem's TCP/IP address? [...] 4741 26 51_Stored Procedure interface after a severe DB2 error0_39_robert.j.hartmann@US.ARTHURANDERSEN.COM30_Wed, 3 Jan 2001 10:54:47 -0600661_us-ascii Can the passed parameters be kept intact after a severe error occurs? We what to use the ROLLBACK command within a SP but also pass back the reason for the request. Can it be done using DB2 5.1 level? *******************Internet Email Confidentiality Footer*******************



Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately [...] 4768 54 31_Re: TCP/IP Address: How to get?11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Wed, 3 Jan 2001 11:08:21 -0600489_us-ascii Browse the MASTER Task under SDSF ( ie SSIDMSTR)... : )







Richard Arnold cc: Sent by: DB2 Subject: TCP/IP Address: How to get? Data Base Discussion List



01/03/01 10:41 AM Please respond to DB2 Data Base Discussion List











Hello list:

Would someone please tell me the easiest way to get a subsystem's TCP/IP address? [...] 4823 107 19_Re: DB2 and Freeway13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM30_Wed, 3 Jan 2001 10:29:39 -0600597_- We are building a sub system on our new freeway box to join a 2 way ds system. Anyone doing something similar?

-----Original Message----- From: khampto1@TXU.COM [mailto:khampto1@TXU.COM] Sent: Wednesday, January 03, 2001 9:44 AM To: DB2-L@RYCI.COM Subject: DB2 and Freeway



We are running DB2 v6.1 on OS/390 V2.8, on one G5 and one G6 CMOS processor (sysplexed, but no datasharing). We are about to install a 2064 (Freeway) processor as a replacement for the G5 box. Anyone else running DB2 for OS/390 on a 64-bit machine yet ? If so, any strangeness to look out for ? [...] 4931 53 31_Re: TCP/IP Address: How to get?13_Olson, Carlos14_COlson@QRS.COM30_Wed, 3 Jan 2001 09:18:27 -0800541_iso-8859-1 Try the command:

TSO NETSTAT HOME

Carlos Olson Database Administrator

QRS Corporation 1400 Marina Way South Richmond, California 94804

510.215.3873 voice 510.621.3873 fax colson@qrs.com



-----Original Message----- From: Richard Arnold [mailto:Richard.Arnold@CHASE.COM] Sent: Wednesday, January 03, 2001 8:42 AM To: DB2-L@RYCI.COM Subject: TCP/IP Address: How to get?



Hello list:

Would someone please tell me the easiest way to get a subsystem's TCP/IP address? [...] 4985 66 31_Re: TCP/IP Address: How to get?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 11:22:06 -0600472_iso-8859-1 Oooooops! That should have read DSNJU003 not DSNJU004. Sorry. Also, upon further reflection it will only list the port number that DB2 will use. The location name that is listed may not be a TCP/IP address. That address is for the machine or LPAR in general. The port numbers identify the application, in this case, DB2. I looked in our STC for a MSTR and couldn't find any address, only the location name that I think is resolved by a domain name server. [...] 5052 22 25_CORRUPTED DATA DICTIONARY15_Amy C Whitehead14_B06ACW@FDS.COM30_Wed, 3 Jan 2001 12:17:09 -0500433_us-ascii I have encountered a problem where I could not load, reorg, or unload a table using the Platinum/CA products. CA suggested running the IBM Reorg to fix this problem, and it did fix the problem. CA was not able to give a root cause of a corrupted data dictionary or why their product could not fix it. Also CA did say that they have seen the reverse, where CA REORG resolved the problem when an IBM Reorg would not..... [...] 5075 37 31_Re: TCP/IP Address: How to get?11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Wed, 3 Jan 2001 13:00:34 -0500310_us-ascii If you have TSO you can issue a TSO PING command to the DB2 Sub-system. TSO PING subsystem name. If your network folks have added the DB2 host name to the Host name definition table you can ping it from a MS-DOS prompt also. Your DB2 system programmer can get you the IP address and the port#... [...] 5113 107 25_-904 error on alter index13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG30_Wed, 3 Jan 2001 12:43:33 -0600523_ISO-8859-1 Happy New Year DB2ers!

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in process of cleaning up all type 1 indexes and have run into a snag. I have 4 indexes that will not convert... SELECT * FROM SYSIBM.SYSINDEXES WHERE INDEXTYPE <> '2'; ---------+---------+---------+---------+-- NAME CREATOR TBNAME ---------+---------+---------+---------+-- DSNDXX01 SYSIBM SYSINDEXES DSNKDX02 SYSIBM SYSPACKDEP DSNPPH01 SYSIBM SYSPLAN DSNGGX01 SYSIBM SYSPLANDEP DSNE610I NUMBER OF ROWS DISPLAYED IS 4 [...] 5221 154 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 12:55:17 -0600351_iso-8859-1 Hi, Mike, My suspicion is that you are the one who is using the indexes when you issue the alter and locking yourself out. CATMAINT should take care of that for you when you run it as part of the catalog conversion.

Linda Billings Enterprise Systems Programmer Info-Tech Services Department of Administration State of Wisconsin [...] 5376 16 22_Re: DB2 UDB data types11_Jeff Kalman15_jkalman@CSC.COM30_Wed, 3 Jan 2001 13:58:17 -0500331_us-ascii Hey everybody, Quick question, Is XML a valid data type in UDB?

TIA. 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. 5393 108 50_SQL Procedures Processor (?) in WLM-managed SP JCL13_Lockwood Lyon19_Lockwool@MEIJER.COM30_Wed, 3 Jan 2001 14:01:31 -0500436_US-ASCII List,

We're about to implement WLM-managed Stored Procedures (We're DB2 V6 on OS/390 2.8). In the JCL "generated" as part of DB2 subsystem install/migration we've got some "sample" JCL for the WLM address space(s). Regrettably, there's lots more in the JCL than appears in the samples in the "Getting Started with DB2 Stored Procedures" Redbook; specifically, there's a mention of ... well, see for yourself below. [...] 5502 166 29_Re: -904 error on alter index13_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG30_Wed, 3 Jan 2001 13:01:07 -0600532_ISO-8859-1 Great! Thanks Linda! I wondered if that would fix it or not.. Have a Great Day! Mike

Michael S. Lawrence Systems Programmer MLawrence@Ascensionhealth.org Wk # - (812) 228-2092 Fax # - (812) 228-2065



>>> linda.billings@DOA.STATE.WI.US 12:55:17 PM Wednesday, January 03, 2001 >>> Hi, Mike, My suspicion is that you are the one who is using the indexes when you issue the alter and locking yourself out. CATMAINT should take care of that for you when you run it as part of the catalog conversion. [...] 5669 153 29_Re: -904 error on alter index14_Toppins, Smike21_smike.toppins@GWL.COM30_Wed, 3 Jan 2001 11:50:22 -0700622_iso-8859-1 If memory serves me correctly, you need to run a CATMAINT utility to convert the catalog/directory indexes to be TYPE 2 indexes.



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



> ---------- > From: Mike Lawrence[SMTP:mlawrence@ASCENSIONHEALTH.ORG] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, January 03, 2001 11:43 AM > To: DB2-L@RYCI.COM > Subject: -904 error on alter index > > Happy New Year DB2ers! > > We are preparing to go to DB2 v6 on OS390 from V5.1. I am in process of > cleaning up all type 1 indexes and have run into a snag. > [...] 5823 212 29_Re: -904 error on alter index0_19_Tim.Lowe@STPAUL.COM30_Wed, 3 Jan 2001 13:22:35 -0600436_iso-8859-1 Linda, According to my DB2 V5 Installation Guide, you should be able to use the catmaint job to convert the indexes to type 2, or to use ALTER INDEX ....CONVERT TO TYPE 2. (topic 2.7.26) Is the manual wrong?

Thanks, Tim





"Billings, Linda" cc: Sent by: DB2 Data Base Subject: Re: -904 error on alter index Discussion List [...] 6036 155 54_Re: SQL Procedures Processor (?) in WLM-managed SP JCL12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 3 Jan 2001 11:57:28 -0800359_us-ascii Lockwood, 1) The SQL Procedures Processor is an address space that executes the DSNTPSMP stored procedure. This is the stored procedure that actually processes the output from the Stored Procedure Builder. 2) You need to know where DSNTPSMP is located if you're planning on using SPB on your pc's. If not, then you don't have to worry about it. [...] 6192 25 49_Triggers calling stored procedures passing rowids10_Tod Julius21_Tod_Julius@JBHUNT.COM30_Wed, 3 Jan 2001 13:22:24 -0600360_us-ascii IN DB2 Ver 6 OS390

I am trying to have an insert trigger call a stored procedure passing a rowid from the inserted table to the stored procedure. The result is a SOC4 in the stored procedure. Any suggestions as to what I maybe doing incorrect?

A side note I can call the stored procedure passing a rowid from a program sucessfully. [...] 6218 207 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 13:40:51 -0600542_iso-8859-1 You are correct, Tim. The V5 Installation Guide does say that. I don't have an answer for that except that it doesn't make sense to me that you can alter an index when the process may be using the very same index that you are trying to alter. If Mike is using SPUFI and the dynamic bind that SPUFI performs when it executes the SQL decides to use those indexes it will take a lock on those indexes. It is possible that those tables needed to be updated because of the alter. Plans and packages may have become invalid because [...] 6426 16 29_Re: -904 error on alter index15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Wed, 3 Jan 2001 13:46:37 -0600419_us-ascii Same error I am also facing currently in test region when I am trying to re-create index after dropping. Looklike temp files are running out of space.

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. 6443 39 53_Re: Triggers calling stored procedures passing rowids13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Wed, 3 Jan 2001 13:49:57 -0600521_US-ASCII Tod, You cannot pass parameters from a trigger to a stored procedure, you must use a UDF. This was my under standing from the DB2 Tech Conference. I'm sure someone will correct me if im wrong. HTH Kurt

>>> Tod_Julius@JBHUNT.COM 01/03/01 01:22PM >>> IN DB2 Ver 6 OS390

I am trying to have an insert trigger call a stored procedure passing a rowid from the inserted table to the stored procedure. The result is a SOC4 in the stored procedure. Any suggestions as to what I maybe doing incorrect? [...] 6483 26 45_getting field names from DB2 stored procedure7_nag gan27_pavan_rameswara@HOTMAIL.COM30_Wed, 3 Jan 2001 14:42:13 -0500404_- Hi, We are testing DB2 stored procedures for UDB on NT. I tested the example stored procedure in IBM site and tested on stored procedure builder. But when I called this SP from VB it is failing because the resultset is not returning field names even though i specified in the select statement "field1 AS FIELD1" instead it is passing sequence number. Can anybody help me in this regard. TIA Pavan [...] 6510 41 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 3 Jan 2001 14:06:41 -0600519_iso-8859-1 Hello, Nayeem,

Yes, the message does look very similar to the one that you got for your problem but there are two differences. First, Mike got a reason code, 00C900C6, that indicates a very different reason for the resource unavailable. Also, Mike was only altering the index. You had already dropped your index and was in the process of reloading all the keys. Mike hadn't gotten that far yet. It is very important to check out all reason codes and SQLSTATE codes when reading an error message. [...] 6552 56 29_Re: CORRUPTED DATA DICTIONARY16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM30_Wed, 3 Jan 2001 20:08:32 -0000371_iso-8859-1 Hi Amy,

In a past life (before working for CA) I got the same kind of problem : we used Fast Unload/Load from Platinum, Master Reorg from Infotel and indeed, running another reorg utility in that case the standard IBM reorg solved the problem.

This was related to the different way of coding the Call to Compression Instruction (CMPSC B263) [...] 6609 16 27_Database middleware "Dr.Da'10_Hill, Dave14_DHill@FSCO.COM30_Wed, 3 Jan 2001 15:16:29 -0500450_iso-8859-1 Hi there folks- Any information on products that operate like Shadow Direct/Connect Premium for DB2,ect? We will be Setting up new DB2 system/s and are looking at the "best" options. dhill@fsco.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. 6626 197 54_Re: SQL Procedures Processor (?) in WLM-managed SP JCL13_Lockwood Lyon19_Lockwool@MEIJER.COM30_Wed, 3 Jan 2001 15:37:23 -0500348_US-ASCII Thanks, Myron. We don't plan on using the SP Builder immediately, so I'll worry about that part later.

Meanwhile, another newbie question: We've implemented DB2 Data Sharing. We have a four-way DS group with 4 (surprise) DB2s: DSP1, DSP2, DSP3, and DSP4. We want to implement WLM-managed SPs. How many WLM JCL procs do I need? [...] 6824 55 29_Re: CORRUPTED DATA DICTIONARY16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Wed, 3 Jan 2001 15:47:34 -0500341_us-ascii Amy,

I have seen both BMC and Platinum reorg utilities corrupt the DB2 compression dictionary and the general response I received from both venders was, "....run IBM's reorg; that will fix it.". To their credit, IBM's reorg did always fix the corrupted dictionary.

I never heard or saw the inverse being true.... [...] 6880 34 20_DDF SRB time is high9_Chu, Pius14_ChuP@CONED.COM30_Wed, 3 Jan 2001 15:49:16 -0500533_iso-8859-1 The data is taken from our monitor TMONDB2 for DB2 for 24 hrs.



ADDRESS SPACE PAGEIN PAGEOUT TCB TIME SRB TIME --------------- -------- --------- ------------ ------------ SYSTEM SERVICES 0 0 1:46.8680 1:20.6355

DATABASE SERVICE 1,370 0 5:54.4345 25:10.1993 IRLM 0 0 .0033 31.8346 DDF 1 0 23.8841 9:23:30.8062



Does anyone know why the SRB time in the distributed address space is so high (9 hrs and 23 minutes) compared to the database SRB time (25 minutes). Thanks for the input. [...] 6915 17 34_Anyone using Arkeia with DB2 UDB ?19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET30_Wed, 3 Jan 2001 14:57:31 -0600366_us-ascii Anyone out there using Arkeia as backup software with DB2 UDB (especially on Linux) ?

TIA

Phil Nelson

================================================ 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. 6933 26 29_Problems with DB2 JDBC Driver30_=?iso-8859-1?Q?Tiago_Ant=E3o?=14_tra@FCT.UNL.PT30_Wed, 3 Jan 2001 21:08:12 +0000424_US-ASCII Hi!

Does anyone has had some problem with DB2 JDBC Driver (V7.1)? I think that it is returning metadata incorrectly, example:

if I do: alter table freguesia add foreign key (DISTRITO, CONCELHO) references CONCELHO(DISTRITO,CODIGO)

It returns a structure which could be mapped to this format: alter table freguesia add foreign key (DISTRITO, CONCELHO) references CONCELHO(CODIGO, DISTRITO) [...] 6960 66 35_Foreign Key indexes - when and how?15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 3 Jan 2001 15:51:08 -0500543_us-ascii Thanks to all who responded. Sounds like the answers are... yes, they are needed no matter what the rule, and no, they don't have to be the only columns in the index, just the first ones.

Happy New Year, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works ----------------------------------------------------- [...] 7027 31 71_Is HDDL feature of Catalog Manager (BMC) available in Platinum To ols ?11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Wed, 3 Jan 2001 15:11:45 -0600400_iso-8859-1 Hi Everyone,

First Happy New Year To All Of You

I was working on BMC Tools and in Catalog Manager you can go to the table and say hddl and it used to generate the ddl for all the dependent objects like views, alias..

In my current shop they have Platinum Dependency Analyser, can you generate the DDL for the dependent object using Platinum Dependency Analyser ? [...] 7059 26 44_FW: field names from stored procedure in UDB13_Morrill, John12_JohnM@VP.NET30_Wed, 3 Jan 2001 14:20:18 -0700561_- -----Original Message----- From: nag gan [mailto:pavan_rameswara@hotmail.com] Sent: Wednesday, January 03, 2001 12:35 PM To: DB2-L-request@RYCI.COM Subject: field names from stored procedure in UDB



Hi, We are testing stored procedures for UDB on NT. I am trying the example from IBM site. I created a stored procedure and tested in Stored procedure builder. When a started accesing from VB its is giving error because in the record set the filed name are not passed insted there are numbers. Can any body help me in this regard. TIA Pavan [...] 7086 48 74_Re: Is HDDL feature of Catalog Manager (BMC) available in Platinum Tools ?9_Rob Crane22_racrane@CONCENTRIC.NET30_Wed, 3 Jan 2001 14:25:26 -0700426_us-ascii Dependency Analyzer is now called Index Expert.

You can generate DDL using RC/Query, type DDL next to the object. If you want the entire chain then type QMIGRATE next to the object and it will allow you to generate the DDL that HDDL command produces in BMC's cat manager.

You can also use RC/Migrator to quickly generate the DDL in either a Quick Migration Strategy or a normal Migration Strategy. [...] 7135 110 29_Re: CORRUPTED DATA DICTIONARY15_Amy C Whitehead14_B06ACW@FDS.COM30_Wed, 3 Jan 2001 16:28:39 -0500446_us-ascii Philippe, This helps me understand how this can happen. What non-IBM methods/products can build a DB2 compression dictionary? I want to make sure this does not happen again and I am not sure how to approach/find a permanent resolution. We are only using CA and IBM DB2 utilities. Could there be a non-DB2 product doing this? In addition, this same error is causing us possible problems with extracting log records via Log Analyzer. [...] 7246 75 53_Re: Triggers calling stored procedures passing rowids16_Richard A Yevich21_ryevich@BELLSOUTH.NET30_Wed, 3 Jan 2001 15:38:47 -0600540_iso-8859-1 Kurt --> Actually you can pass paramters to a Stored Procedured. What you cannot do is have a stored procedure update a transition variable to be used by the Trigger upon return. You need to use a UDF in the trigger to do that.

Tod --> regarding your OC4. How is the parameter defined? I am assuming that you are talking about an AFTER INSERT stored procedure, and you are passing the NEW.COLNAME as a parm to the SP. There could be some issues with that. And curious but what are you doing with the ROWID in the SP? [...] 7322 73 75_Re: Is HDDL feature of Catalog Manager (BMC) available in Platinu m Tools ?11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Wed, 3 Jan 2001 16:00:56 -0600462_iso-8859-1 We don't have these tools Will do it from Catalog table. Any way Thanks for the quick response.

Amit

Tel:414-931-4815







-----Original Message----- From: Rob Crane [mailto:racrane@concentric.net] Sent: Wednesday, January 03, 2001 3:25 PM To: DB2-L@RYCI.COM Subject: Re: Is HDDL feature of Catalog Manager (BMC) available in Platinum Tools ?



Dependency Analyzer is now called Index Expert. [...] 7396 181 24_FW: DDF SRB time is high14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Wed, 3 Jan 2001 14:05:27 -0800383_iso-8859-1



-----Original Message----- From: Philip Gunning To: 'Chu, Pius ' Sent: 1/3/01 2:04 PM Subject: RE: DDF SRB time is high



Pius, Are you using WLM to manage the enclaves? If not I believe the time shows up under DDF. -- Phil -----Original Message----- From: Chu, Pius To: DB2-L@RYCI.COM Sent: 1/3/01 12:49 PM Subject: DDF SRB time is high [...] 7578 26 37_Data Sharing members, different times16_Richard A Yevich21_ryevich@BELLSOUTH.NET30_Wed, 3 Jan 2001 16:37:49 -0600618_iso-8859-1 Does anyone know of anyone who is running multiple data sharing members with one member being on a different time than the other members?? Please, don't ask me why, I am opposed. But there is a client with the need.

Regards, Richard

+===+===+===+===+====+ Richard Yevich richard_yevich@ylassoc.com www.ylassoc.com 1-217-744-0000 +===+===+===+===+====+

================================================ 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. 7605 106 26_Re: DB2 Certification Cost15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Thu, 4 Jan 2001 09:41:12 +1100472_iso-8859-1 Hi Jap/Georg(e),

Thanks for your comments. Yes the tests at these conferences are often free, but attending the conference isn't. As a scumbag money-grabbing contractor I usually have to pay for my own attendance at courses, conferences, etc., plus have the time away from work as non-chargeable. That's the main reason I didn't go to IDUG Melbourne this year, even though for the first time ever it was held in a city I was working in at the time. [...] 7712 141 26_Re: DB2 Certification Cost24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Wed, 3 Jan 2001 14:49:24 -0800348_iso-8859-1 Hi Raymond!

As a "scumbag money-grabbing contractor", you probably have a fairly wide range of experiences. You might want to consider being a presenter at a conference. This would cover the conference costs, although you'd still need to handle the rests of the costs. Then again, perhaps $75 would seem cheap in comparison. [...] 7854 55 28_Re: DB2 UDB data types / XML11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Wed, 3 Jan 2001 18:09:40 -0500428_us-ascii From the V7 "What's New" Book:

"DB2 now allows you to store eXtended Markup Language (XML) documents as a new column data type. You also have the ability to decompose and store XML in its component parts as well as to search on the decomposed fields of an XML document. This function, along with enhancements to Net.Data, provides you with a simpler way of exchanging and storing documents electronically." [...] 7910 113 44_Balt./Wash. DB2 Users' Group Mtg. -- 3/13/0117_Valerie J Traurig18_valeriet6@JUNO.COM30_Wed, 3 Jan 2001 18:03:51 -0500658_- SPECIAL DB2 COURSE WITH FRANK INGRASSIA Sponsored by the Baltimore/Washington DB2 Users' Group

When? Tuesday, March 13, 2001, 9:00 am-3:30 pm (registration at 8:30)

Where? Sheraton Columbia Inn 10207 Wincopin Circle Columbia, MD (410) 730-3900

Who? Frank J. Ingrassia is an internationally-recognized DB2 consultant and educator. He has spent most of the last 14 years studying the DB2 optimizer in detail. Outside of IBM and the people who design and write the code, he probably has a better handle on the actual processes and cost estimations than just about anybody else out there. He has also published and presented numerous [...] 8024 37 29_Re: -904 error on alter index0_19_Tim.Lowe@STPAUL.COM30_Wed, 3 Jan 2001 17:53:54 -0600589_us-ascii Linda, I don't understand. Now, I have even more questions. In order to accomplish the ALTER INDEX ..CONVERT TO TYPE 2 command, DB2 would have to update the row in sysindexes, but the indexes on sysindex would not need to be changed. (unless the update process deletes and readds each of the associated index records??) The dynamic bind and any locks on this index would not seem to change the index. Invalidating plans and packages do not appear to have anything to do with updating indexes on sysindex. Therefore, this would not appear to me to create an uncommitted write [...] 8062 36 31_Re: TCP/IP Address: How to get?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 4 Jan 2001 10:40:16 +0530979_- Hi, MSTR address space probably gives the information when message given below is issued.

DSNL511I + DSNLIENO TCP/IP CONVERSATION FAILED TO LOCATION 11.22.111.555 IPADDR=11.22.111.555 PORT=2222 SOCKET=READ RETURN CODE=1121 REASON CODE=00000000

Is this true ?

Regards, Sanjeev ----------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------- This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action [...] 8099 61 31_Re: TCP/IP Address: How to get?10_teldb2kals22_teldb2kals@TELSTRA.COM30_Thu, 4 Jan 2001 15:44:40 +1000435_ISO-8859-1 Sanjeev,

I think that IP address is the address of a client machine which tried to communicate with the host.

Cheers, Kals -----Original Message----- From: S, Sanjeev (CTS) [SMTP:SSanjeev@CAL.COGNIZANT.COM] Sent: Thursday, January 04, 2001 4:10 PM To: DB2-L@RYCI.COM Subject: Re: TCP/IP Address: How to get?

Hi, MSTR address space probably gives the information when message given below is issued. [...] 8161 96 31_Re: TCP/IP Address: How to get?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 4 Jan 2001 11:26:11 +0530618_- Kals, Thanks for your comment. But is this not the address and with its port(2222) DB2 subsystem is interacting ? I am not sure as i have no concrete idea for this.

Regards, Sanjeev

> -----Original Message----- > From: teldb2kals [SMTP:teldb2kals@TELSTRA.COM] > Sent: Thursday, January 04, 2001 11:15 AM > To: DB2-L@RYCI.COM > Subject: Re: TCP/IP Address: How to get? > > Sanjeev, > > I think that IP address is the address of a client machine which tried to > communicate with the host. > > Cheers, > Kals > -----Original Message----- > From: S, Sanjeev (CTS) [SMTP:SSanjeev@CAL.COGNIZANT.COM] > [...] 8258 33 29_Working with Remote Databases15_Amitabh Akolkar23_oxynet@MAILCITYASIA.COM30_Thu, 4 Jan 2001 11:24:01 +0530391_us-ascii Hi,

Can anyone help me out here!

I am using DB2 PE on Win'98. I want to use the DB2 APPLY and CAPTURE program to replicate data from PE to Workgroup. To do this APPLY & CAPTURE require that the remote Workgroup database be mapped in PE. But the problem is,

How to map the Remote Database through a proxy server? Is it technically possible through IBM DB2? [...] 8292 162 49_Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.014_Duncan Gardner23_dgardner@GESB.WA.GOV.AU30_Thu, 4 Jan 2001 15:03:25 +0800632_iso-8859-1 Jef,

Thanks for spotting this. FixPack6 on Solaris also introduced this problem. Anything more I say is likely to be libel .....

Not very happy Duncan Gardner DBA Team, Infrastructure Support Group Western Australian Government Employees Superannuation Board (08) 9263 4164 All my own views etc ....



> ---------- > From: Jakobs, Jef[SMTP:JJakobs@HARTIC.COM] > Reply To: DB2 Data Base Discussion List > Sent: 22 December 2000 00:17 > To: DB2-L@RYCI.COM > Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0 > > David, > > Maybe I should have explained that this query I mailed out [...] 8455 82 24_Re: DDF SRB time is high16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 4 Jan 2001 12:40:33 +0530335_- Is not this good to have high SRB time with respect to TCB time ?....not sure , someone please explain !! Philip's reply looks more logical and derived from experience but my guess goes to the block prefetch which i think is charged thru' SRB.

It may be wrong unless until confirmed by some experts out here in the list. [...] 8538 146 43_Re: DSN1COMP-Utility and the SIZE-parameter16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 4 Jan 2001 12:55:46 +0530616_iso-8859-1 Hi, I think replies from Max and Linda explained both the options for specifying the pagesize. It can either be given by option 32K (for 32K pages only) or by option PAGESIZE(32K) something like this i think : DSN1COMP 32K ROWLIMIT(20000) or

DSN1COMP PAGESIZE(32K) ROWLIMIT(20000)

Another point which i think the Util guide says that preferred option is using PAGESIZE, not sure why !!.One more interesting thing i found in the util guide which i think most of you have gone through : If you omit PAGESIZE,DSN1COMP tries to determine the page size from the input data set.DB2 issues an [...] 8685 152 26_Re: DB2 Certification Cost14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT30_Thu, 4 Jan 2001 09:13:46 +0100491_ISO-8859-1 ------------------------------------------------------------------- Name: Massimo Ceraso E-mail: Massimo.Ceraso@tlsoft.it Phone: +39-081-7564798 Date: 01/04/2001 Time: 09:13:46

International DB2 User Group European Conference Planning Committee

IBM Certified Solution Expert DB2 UDB V5 Application Development

"Long ago, when knights were bold, dragons walked the earth, and both DB2 and I were young, I had a real job. I was a programmer." BONNIE BAKER [...] 8838 181 43_AW: DSN1COMP-Utility and the SIZE-parameter12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 4 Jan 2001 09:59:22 +0100435_iso-8859-1 Linda & Max & Sanjeev,

thanks for your advice.. With using //COMPEVAL EXEC PGM=DSN1COMP,PARM='32K,REORG,ROWLIMIT(50000)' the utility works.

With kind regards - mit freundlichen Grüssen, G e o r g ----------------------------------------------------------------------

"Long ago, when knights were bold, dragons walked the earth, and both DB2 and I were young, I had a real job. I was a programmer." [...] 9020 83 34_DB2 Replication & Data Warehousing11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Thu, 4 Jan 2001 15:58:35 -0800556_us-ascii Dear all,

I like to know if DB2 Replication is the right tool to do Data Warehousing, or Visual Warehouse (Warehouse Manager) is the best tool? The environment that we have right know is 1 HQ with 7 branches. In the system that we are developing right now, each branches has to Replicate their data to the HQ. The problem that we have is that the communication line between from each branches to the HQ are slow, and the average data to be replicate daily is around 1000 rows from 50 tables. Does anybody have an idea about this? Best [...] 9104 150 29_Re: CORRUPTED DATA DICTIONARY16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM30_Thu, 4 Jan 2001 09:44:50 -0000413_iso-8859-1 Amy,

All the Load Replace or Reorg Utilities (IBM or non-IBM) could rebuild the dictionary.

Before running an IBM reorg try to IBM unload or access the data just to check if the object is really corrupted or not. If your are running into the same problem as I, then the object is not corrupted at all. Only the Platinum Utilities ,due to the checking they do, think it is corrupted. [...] 9255 67 29_Re: -904 error on alter index10_Wim Ruarus18_w.h.ruarus@KPN.COM30_Thu, 4 Jan 2001 11:20:27 +0100391_- Tim,

what I don't understand is why you would bother altering and recovering each catalog index, when you have CATMAINT to take care of all. In my opinion Linda is right when she says that you can't alter an index when the process you are running uses the same index you are trying to alter. When DB2 wants to update a row in SYSINDEXES, it needs the index to access that row. [...] 9323 56 30_Re: DSMAX - Number Of Datasets14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Thu, 4 Jan 2001 04:36:19 -0600573_iso-8859-1 Andy,

I have no personal experience of using >10000 open datasets, but I have heard a few comments about this at IDUG and elsewhere.

The general consensus is that it may be better to treat the number of open datasets in the same manner as any other resource, and plan how you want to exploit it. A popular view is to use CLOSE YES everywhere, and set DSMAX to about 6 to 7K. The justification behind this is that this value of DSMAX allows all active datasets to remain open, plus a reasonable cushion of inactive datasets. The storage needed [...] 9380 56 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 4 Jan 2001 06:53:25 -0600370_iso-8859-1 Hi, Tim, I don't know either. I am only guessing. Perhaps there is someone who knows more than I do. I only know that we used CATMAINT and that worked.

Linda

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Wednesday, January 03, 2001 5:54 PM To: DB2-L@RYCI.COM Subject: Re: -904 error on alter index [...] 9437 69 55_DB2 crash remedied -- Was > indexable global temp table13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 4 Jan 2001 08:17:35 -0500421_iso-8859-1 A couple weeks ago, I mentioned in reply to a question from Amy about indices on declared temp tables that we encountered an abend in using them.

This post is merely to update any who might be concerned that there is a fix for the abend.

(For a quick review, I had issued a declare global temp table, created an index on it, attempted inserting data into the table and caused DB2 to abend). [...] 9507 45 46_SQL1652N when executing db2start from a script37_=?iso-8859-1?Q?Odd_Bj=F8rn_Andersen?=19_Odd.Andersen@SDS.NO30_Thu, 4 Jan 2001 14:33:17 +0100580_iso-8859-1 I have just migrated a database from DB2 version 5.2 to version 6.1 (fixpack 6) on Windows NT. After migration I experienced something strange. I have some scripts used for different purposes, and these scripts worked all right under version 5.2. But now some of the commands in the scripts returns an error: SQL1652N File I/O error occured. For instance will db2start and db2dart return that error, while db2stop executes ok. BUT: this only occurs when I try to execute the commands from within a script. When they are run from a DB2 Command Window, they run ok. [...] 9553 74 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 4 Jan 2001 08:21:56 -0600430_iso-8859-1 Tim,

After a bit of thought it seems reasonable that an S-lock would still be taken on the index for SYSINDEXES if the ALTER still used it to locate the row to be updated in SYSINDEXES. If Mike had any software that accessed the catalog and used SYSINDEX for some reason then some plans and packages may still be invalidated as a result of the alter and would need to be rebound. Does that sound plausible? [...] 9628 28 29_V6 - Cursor names with dashes11_Suresh Sane21_data_arch@HOTMAIL.COM30_Thu, 4 Jan 2001 08:28:30 -0600454_- Hello list,

I recall reading that V6 (DB2 UDB OS/390)would disallow cursor names with dashes. The SQL Ref manual states that it can a "long identifier" containing alphabetic, digits and underscore (only?). We are on May Refresh tape and finding that cursor names with dashes are indeed allowed - precompile of new sql as well as bind of an existing dbrm still work. Was this fixed along the way somewhere or was my orifinal info incorrect? [...] 9657 117 24_Re: DDF SRB time is high12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 4 Jan 2001 08:40:27 -0600333_us-ascii SRB time in the DDF address space will always be high in comparison to SRB time in the other address spaces. All work done by the DDF address space on behalf of remote clients is executed in OS/390 Enclaves, whether or not you are using WLM. And Enclaves are a pool of "preemptable" SRB's, thus the charge to SRB time. [...] 9775 143 31_Re: TCP/IP Address: How to get?12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 4 Jan 2001 08:35:59 -0600351_us-ascii The IP address and port number listed in the DSNL511I message is the DB2 Connect client or the gateway with which that DB2 OS/390 subsystem is communicating. The numeric IP address of the DB2 for OS/390 subsystem itself does not appear in the MSTR log, (at least not at our shop) but it does display its own port number when DDF starts. [...] 9919 27 6_DSNRLI15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Thu, 4 Jan 2001 09:55:36 -0500612_iso-8859-1 Thank you all very much for the DSNRLI instructions - All of the contributions helped. Here is what we ended up with:

*Stored Procedure compiled with ATTACH(RRSAF)- This translates the calls to use DSNHLIR/DSNRLI *Regular CAF compile of the calling program using DSNALI

My question now is - Why does it work & what Plan etc.... is RRSAF connection running under? The Cobol program runs CAF and connects under DSNALI, executes some SQL and then calls the WLM stored procedure (that must use DSNRLI - not compatible with DSNALI threads) - yet it executes just fine. Thank you for any [...] 9947 21 14_need help asap0_23_edie.richardson@SRS.GOV30_Thu, 4 Jan 2001 09:50:09 -0500 9969 36 19_Monitoring Question14_Richard Arnold24_Richard.Arnold@CHASE.COM30_Thu, 4 Jan 2001 09:06:02 -0600466_us-ascii Hello List,

I just got a request from a developer. He wants to monitor all of the changes to a table over time. Basically, he wants a report of all the inserts, updates, and deletes that happen to the table daily.

Outside of putting count reports in all of the programs that manipulate the table, is there an easy way to extract this information. We have DB2 PM, though I am not yet familiar with it's batch capabilities. Can PM do this? [...] 10006 25 21_Online Reorg with NPI13_Johnson, Bill18_bjo23@ALLSTATE.COM30_Thu, 4 Jan 2001 09:52:15 -0600568_- All, We are DB2 V6 in a data sharing environment. We are implementing online reorg and are looking for guidance or ideas from anyone that has experienced the same issues. If we have a partitioned tablespace(8 partitions) and have 1 or more NPI's defined to it, is it better to online reorg at the partition level or at the tablespace level? Lets say for examples sake that each partition contains 7-8 million rows with a large amount of update activity. Any gotchas that we need to look out for using either scenario? Would the build2 phase and switch phase of [...] 10032 60 23_Re: Monitoring Question14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 4 Jan 2001 09:08:32 -0700613_- If you're on V6, you can add triggers to accomplish the trick.



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



> ---------- > From: Richard Arnold[SMTP:Richard.Arnold@CHASE.COM] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, January 04, 2001 8:06 AM > To: DB2-L@RYCI.COM > Subject: Monitoring Question > > Hello List, > > I just got a request from a developer. He wants to monitor all of the > changes to > a table over time. Basically, he wants a report of all the inserts, > updates, and > deletes that happen to the table daily. > > Outside [...] 10093 51 18_Re: need help asap14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Thu, 4 Jan 2001 10:10:30 -0600414_iso-8859-1 Is the load LOG YES or LOG NO? If it is LOG YES the termination is taking a long time due to backout. Cancelling DB2 in this situation could result in a very long restart time.



From Edward Vassie...





-----Original Message----- From: edie.richardson@SRS.GOV [mailto:edie.richardson@SRS.GOV] Sent: 04 January 2001 14:50 To: DB2-L@RYCI.COM Subject: need help asap [...] 10145 13 25_Re: Online Reorg with NPI15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM30_Thu, 4 Jan 2001 10:10:11 -0600334_us-ascii With that many rows I would think you would want to keep it at the partition level.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10159 64 18_Re: need help asap15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 4 Jan 2001 10:20:28 -0600563_iso-8859-1 Hi, Edie,

My first thought is that it is that your utility is still rolling back. If the workload is very great right now rolling back could take some time. Also, if your active logs have archived and those archives are on tape then DB2 is constantly waiting for tape mounts. Bouncing DB2 will not help and may even hurt your situation. DB2 is very stubborn where corrupted data is concerned. It will not stop until it is satisfied that it has gotten your tablespace back to a state of consistency. I think that your only option is to wait [...] 10224 57 18_Re: need help asap16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Thu, 4 Jan 2001 08:19:16 -0800270_iso-8859-1 Is it a LOAD LOG YES? If so, it's best to just let it back out. If you bounce DB2, the whole subsystem could be unavailable until the backout is complete. As a guesstimate, expect the backout time to be twice as long as the run time prior to the cancel. [...] 10282 77 23_Re: Monitoring Question13_Terry Shockey30_Terry.Shockey@OCSE.STATE.AR.US30_Thu, 4 Jan 2001 10:24:51 -0600404_iso-8859-1 richard: ca-platinums log anlayzer product will do that and so much more for you, it will also tell you when the change was made.

-----Original Message----- From: Toppins, Smike [mailto:smike.toppins@GWL.COM] Sent: Thursday, January 04, 2001 10:09 AM To: DB2-L@RYCI.COM Subject: Re: Monitoring Question



If you're on V6, you can add triggers to accomplish the trick. [...] 10360 13 18_Re: need help asap0_26_truman.g.brown@VERIZON.COM30_Thu, 4 Jan 2001 11:26:22 -0500337_us-ascii Is this a DB2/OS390 DB2 load, and did you use LOG YES? LOG YES will cause a backout...

================================================ 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. 10374 96 18_Re: need help asap13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Thu, 4 Jan 2001 08:25:39 -0800652_us-ascii Or bounce back and do a cold start (Only if it is TEST subsystem). And when you try LOAD the next time, use LOG(NO).

Ashish. /*std. Disclaimers*/

-----Original Message----- From: Billings, Linda [SMTP:linda.billings@DOA.STATE.WI.US] Sent: Thursday, January 04, 2001 8:20 AM To: DB2-L@RYCI.COM Subject: Re: need help asap

Hi, Edie,

My first thought is that it is that your utility is still rolling back. If the workload is very great right now rolling back could take some time. Also, if your active logs have archived and those archives are on tape then DB2 is constantly waiting for tape mounts. Bouncing DB2 [...] 10471 98 30_Re: DSMAX - Number Of Datasets13_Adrian Savory27_Adrian.Savory@WORKTHING.COM30_Thu, 4 Jan 2001 16:26:20 -0000384_iso-8859-1 At previous (SAP) sites I worked at we did exactly that, mainly because of concerns about virtual storage. I can't remember the storage overhead per dataset, but when you run into the 1000s it is significant. We didn't have any problems letting CLOSE YES close inactive datasets, but make sure you monitor your DS high water mark and give yourself a bit of a cushion. [...] 10570 23 23_Re: Monitoring Question10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 4 Jan 2001 17:33:38 +0100537_us-ascii But if I remeber well (I saw log manager years ago) you need DATA CAPTURE YES in the table ...or is it only to rebuild inserts, updates...statements ???

THX



Max Scarpa

'If your programmer doesn't understand what he must do, beat him.'

Bluto Blutarsky

================================================ 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. 10594 36 29_Re: -904 error on alter index0_19_Tim.Lowe@STPAUL.COM30_Thu, 4 Jan 2001 10:45:00 -0600568_us-ascii Linda, I can see how an alter of any other index would have a problem once the index on sysindexes was altered, and was therefore placed into recovery pending status. Likewise, until the index on sysindexes was rebuilt, I can see how any rebind would have a problem. (And, a lot of other software would have problems until this was done). But, I do not understand how invalidating plans or packages would be related to the index on sysindexes. And, since the reason code indicated that there was a "pending write" to the index on sysindex, then I do not [...] 10631 47 51_Urgent! Problem with LOAD utility UDB 6.1 NT EE FP613_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 4 Jan 2001 11:47:05 -0500623_us-ascii Has anyone seen this type of error before and if so, what can one do to avoid it?!

2001-01-04-09.34.22.343000 Instance:DB2 Node:000 PID:1311(db2syscs.exe) TID:1051 Appid:AC131587.FB04.010104131956 buffer_pool_services sqlbTestSetDatapoolState Probe:633 Database:E8412TLA DIA9999E An internal error occurred. Report the following error code : "FFFF8138".

2001-01-04-09.34.22.343001 Instance:DB2 Node:000 PID:1311(db2syscs.exe) TID:1051 Appid:AC131587.FB04.010104131956 buffer_pool_services sqlbTestSetDatapoolState Probe:633 Database:E8412TLA

oldState = 0, newState = 2, currentState = 24 [...] 10679 41 31_Re: TCP/IP Address: How to get?30_CHRISTOPHER.KELLER@Vereinte.de30_CHRISTOPHER.KELLER@VEREINTE.DE30_Thu, 4 Jan 2001 17:50:00 +0100637_iso-8859-1 --- Erhalten von BG1.KELLERC 6785-2761 04-01-01 17.50

-> db2-l@ryci.com

Hello, right, and then as said ping the name under DOMAIN and you get the IP-address. Regards, Chris

Date: Thu, 4 Jan 2001 08:35:59 -0600 Subject: Re: TCP/IP Address: How to get? From: khampto1@TXU.COM To: DB2-L@RYCI.COM

The IP address and port number listed in the DSNL511I message is the DB2 Connect client or the gateway with which that DB2 OS/390 subsystem is communicating. The numeric IP address of the DB2 for OS/390 subsystem itself does not appear in the MSTR log, (at least not at our shop) but it does display [...] 10721 111 25_Re: Online Reorg with NPI0_18_mebert@AMADEUS.NET30_Thu, 4 Jan 2001 17:58:35 +0100575_us-ascii With NPIs, you're more or less required to use full-TS REORG. The assumption is that you have NPIs because you need them. If you're doing a partition-level REORG, then in the BUILD2 phase (which takes a long time), the logical partition will be in a UT restricted state, and any transactions that need access will fail immediately (no wait period). Thus you're defeating the purpose of OLR. Also the NPI(s) won't be reorged by a partition-level OLR, so you need an additional job for this. With a full-TS REORG in V5, the SWITCH phase used to take a long time. [...] 10833 137 30_Re: DSMAX - Number Of Datasets8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Thu, 4 Jan 2001 10:59:08 -0600312_us-ascii I think, the main problem here is the storage below 16MB line. Remember each open dataset needs 1.8 k above the line and 0.9 k below line. If SWA is defined above the line the overhead below the line is 0.3k. Also time to start and stop DB2 will be higher. Assume 1 to 3 seconds for each datasets. [...] 10971 145 30_Re: DSMAX - Number Of Datasets13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Thu, 4 Jan 2001 08:59:49 -0800327_iso-8859-1 It is 1.5k per open dataset. So, you can do some mathematics and see what is good for your shop. Actually even for (say) 3000 extra datasets defined with CLOSE YES, it will be just 4.5MB. Not too huge unless you are really crunched for virtual memory. Probably other list members could share their experiences. [...] 11117 120 30_Re: DSMAX - Number Of Datasets12_Chad Breiner23_chadbreiner@HOTMAIL.COM30_Thu, 4 Jan 2001 17:02:29 -0000663_- Another reason to setting DSMAX carefully is the amount of time required to shutdown a subsystem. The more open datasets, the longer it takes.



>From: Adrian Savory >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DSMAX - Number Of Datasets >Date: Thu, 4 Jan 2001 16:26:20 -0000 > >At previous (SAP) sites I worked at we did exactly that, mainly because of >concerns about virtual storage. I can't remember the storage overhead per >dataset, but when you run into the 1000s it is significant. We didn't have >any problems letting CLOSE YES close inactive datasets, but [...] 11238 24 45_Converting application from Oracle to DB2 UDB0_39_Patrick_D_Poziwilko@EMAIL.WHIRLPOOL.COM30_Thu, 4 Jan 2001 12:09:32 -0500375_US-ASCII Hi,

We have the opportunity of converting a Siebel application from Oracle 8.16 to DB2 UDB on a RS6000. The application has about 2,000 tables and 6,000 indexes. We are also neophytes with DB2 UDB. So, is there an easy process to migrate the structures and data? Are tools required for the migration? Any silver bullets? Any successes or horror stories? [...] 11263 167 39_Re: Transactions deadlocking on inserts22_Kirkland, Andrew (FR1)18_kirkawp@RBOS.CO.UK30_Thu, 4 Jan 2001 17:09:29 -0000406_iso-8859-1 Tim & Terry,

thanks for the most recent postings - this has raised some very interesting questions indeed because we do in fact have the potential for uniqueness violation, -803s and have re-try logic in the transaction to cater for this. We have the situation where inserts will be being made based on adjacent key values within the same second and we are also experiencing -803's. [...] 11431 66 29_Re: -904 error on alter index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 4 Jan 2001 11:26:17 -0600543_iso-8859-1 Hi, Tim, Well, it is a small point but often if we pursue small points we gain a better understanding on how the whole system works. First, the pending write is probably not occurring on the index but on the table. An S-lock would still have to be taken on the index if the plan needs to use the index to locate the row to update on the table. Second, if Mike has any system monitoring type software or catalog query software of some type then the plans or packages used by that system could conceivably use the catalog tables [...] 11498 204 41_Re: need help asap - a few more questions0_23_edie.richardson@SRS.GOV30_Thu, 4 Jan 2001 12:29:18 -0500404_us-ascii I was assuming the same thing I just wanted some reassurance. I do have a few more questions though. 1) is there any way I can tell if it is actually doing anything? I can't really tell if the whole thing is just hung or if it is really doing something. I do a dis on utility and database and don't really see anything but when I use db2 pm and display thread activity I see the following: [...] 11703 46 16_SQLSTATE 01506 ?9_Ulf Cihak23_UlfCihak@COMPUSERVE.COM30_Thu, 4 Jan 2001 11:28:37 -0600619_- Dear List,

during execution of a DSNTEP2 (on DB2/OS390 Version 5) of

SELECT ... FROM ... WHERE VSTART_DATUM < CURRENT DATE - 3 MONTH AND ( ... OR ...) GROUP BY ...

I got the following SQL Warning, accompanied by CondCode 4 :

DSNT400I SQLCODE = 000, SUCCESSFUL ECUTION DSNT418I SQLSTATE = 01506 SQLSTATE RETURN CODE DSNT416I SQLERRD = 0 0 9507 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000000' X'00000000' X'00002523' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS DSNT417I SQLWARN6-A = W,,,, SQL WARNINGS ..... [...] 11750 54 49_Re: Converting application from Oracle to DB2 UDB9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Thu, 4 Jan 2001 10:26:28 -0700452_iso-8859-1 Patrick,

You will be joining the thousands of "soon to be ex-Oracle" users especially since Sieble has Divorced Oracle like many other software vendors. The migration process is fairly simple because a company called Mantech has joined forces with IBM to migrate customers from Oracle to the "Number-1 DBMS in the World". Attached is a URL that should get you and your asociates to the "starting line" for the Migration process. [...] 11805 22 41_Re: need help asap - a few more questions9_Colin Fay13_cfay2@CSC.COM30_Thu, 4 Jan 2001 12:47:41 -0500540_us-ascii Hi Edie,

It may be something else going on.

Look in the MVS system log. Is the job waiting for anything else? Perhaps a mount of a non-existent volume/pack. If Db2 is waiting on MVS and it requires operator intervention for example, It just waits





Colin

================================================ 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. 11828 133 41_Re: need help asap - a few more questions15_Murley, Michael22_Michael_Murley@BMC.COM30_Thu, 4 Jan 2001 11:50:44 -0600381_iso-8859-1 A failed LOAD REPLACE LOG YES doesn't do a rollback in the traditional sense. Instead, it deletes the rows in SYSLGRNX that represent the updates from the load and then leaves the space in RECP status. Since the log records from the load are not represented in SYSLGRNX, a subsequent recovery does not find them, and the effect of the load is logically backed out. [...] 11962 51 49_Re: Converting application from Oracle to DB2 UDB12_Cook, Nicole19_Nicole_Cook@BMC.COM30_Thu, 4 Jan 2001 11:52:39 -0600364_iso-8859-1 Here is a link to an article in DB2 magazine. It has some wonderful information on how to move an Oracle database to UDB. It also provides tables on how to convert Oracle data types into ones used by UDB, plus translations from PL/SQL to SQL used by UDB.

http://www.db2mag.com/db_area/archives/2000/q2/spratt.shtml

I hope this helps. [...] 12014 118 41_Re: need help asap - a few more questions12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 4 Jan 2001 12:49:27 -0500541_us-ascii Do a -DIS UTIL(*) and tell us what it says for your utility.

Martin Wolff, Global Crossing.

edie.richardson@SRS.GOV wrote:

> > I was assuming the same thing I just wanted some reassurance. I do > have a few more questions though. > 1) is there any way I can tell if it is actually doing anything? I > can't really tell if the whole thing is just hung or if it is really > doing something. I do a dis on utility and database and don't really > see anything but when I use db2 pm and display thread activity [...] 12133 15 49_Re: Converting application from Oracle to DB2 UDB15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM30_Thu, 4 Jan 2001 11:19:51 -0600382_iso-8859-1 Have you looked at DBArtisan from Embacadero¿ They have a wb site EMBARCADERO.COM. I beleive the software can be downloaded from the site. ===============================================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. 12149 27 11_help please11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET30_Thu, 4 Jan 2001 10:02:07 -0800399_US-ASCII I am fairly new to DBA work and the senior DBA is off today. I have a developer who is trying to run SPFUI for the first time (new contractor) and he is getting the following error:

PLAN DSNESPRR NOT AUTHORIZED FOR SUBSYSTEM TSN AND AUTH ID NBB163.

We are DB2 V4.1 on S/390. He does have authorization for the tables he is trying to query. Any help would be appreciated. [...] 12177 42 46_referential integrity question - OS/390 DB2 V513_Judi Westcott23_judi_westcott@YAHOO.COM30_Thu, 4 Jan 2001 12:12:10 -0600357_- Hello

I was wondering if anyone knows of a way to do the following:

table_1 has primary unique key of col_1, col_2, col_3 table_2 has primary unique key of col_1, col_2, col_3, col_4

I have set up RI to check that there exists a row on table_1 before a row with the col_1, col_2, col_3 is inserted into table_2. This works fine. [...] 12220 51 15_Re: help please11_Lorenz, Don15_dlorenz@KCC.COM30_Thu, 4 Jan 2001 12:12:20 -0600518_iso-8859-1 Kathy,

Have the contractor change his default Spufi settings to CS (from RR)......that will probably do it.....

Regards,

-----Original Message----- From: KATHY JONES [mailto:JONESKS@GROUPWISE.CCSD.NET] Sent: Thursday, January 04, 2001 12:02 PM To: DB2-L@RYCI.COM Subject: help please



I am fairly new to DBA work and the senior DBA is off today. I have a developer who is trying to run SPFUI for the first time (new contractor) and he is getting the following error: [...] 12272 52 15_Re: help please12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Thu, 4 Jan 2001 13:19:15 -0500405_us-ascii Kathy, many shops to not use plan dsnesprr due to it being the plan for repeatable read access. go into spufi and change option 5 to yes hit enter and change isolation level to cs and see if that works for you.







KATHY JONES @RYCI.COM> on 01/04/2001 01:02:07 PM

Please respond to DB2 Data Base Discussion List [...] 12325 61 15_Re: help please0_20_John_Lendman@FPL.COM30_Thu, 4 Jan 2001 13:20:54 -0500458_us-ascii It appears that Plan DSNESPRR has not be granted execute on DB2 subsystem TSN for this user ID NBB163.

It order for him to run this you will need to grant execute for this plan(DSNESPRR) on that DB2(TSN) to user id (NBB163)

John Lendman Florida Power and LIght





"KATHY JONES" cc: Sent by: "DB2 Data Subject: help please Base Discussion List" [...] 12387 47 15_Re: help please24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM30_Thu, 4 Jan 2001 13:21:06 -0500424_iso-8859-1 Some shops do not grant the use of that form of the SPUFI plan (repeatable read) but do grant the use of cursor stability (DSNESPCS). So either DSNESPRR will never be made available (in which case you go to the SPUFI defaults panels and change Isolation level to CS) and/or get someone with GRANT EXECUTE authority on the plan to grant execute to this poor , needy user. Maybe you have that auth as DBA. HTH [...] 12435 15 15_Re: help please0_26_truman.g.brown@VERIZON.COM30_Thu, 4 Jan 2001 13:21:17 -0500345_us-ascii Kathy,

He probably needs to go into the defaults panel and set the subsystem name, etc.

================================================ 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. 12451 51 15_Re: help please14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 4 Jan 2001 11:20:51 -0700634_- Most likely, the developer needs to change his defaults to use "CS" instead of "RR"; also the DB2 subsystem needs to be changed to the correct subsystem. Most places I've been dis-allow most people from running "RR" SPUFI queries.



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



> ---------- > From: KATHY JONES[SMTP:JONESKS@GROUPWISE.CCSD.NET] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, January 04, 2001 11:02 AM > To: DB2-L@RYCI.COM > Subject: help please > > I am fairly new to DBA work and the senior DBA is off today. I have a > developer who is trying to [...] 12503 48 15_Re: help please9_Wu, James13_jwu@KRAFT.COM30_Thu, 4 Jan 2001 12:22:32 -0600559_iso-8859-1 I guess you have multiple DB2 subsystems under the same MVS LPAR. You can change the SSID under option D.

James Wu:-)

(847)646-5548 jwu@kraft.com





-----Original Message----- From: KATHY JONES [mailto:JONESKS@GROUPWISE.CCSD.NET] Sent: Thursday, January 04, 2001 12:02 PM To: DB2-L@RYCI.COM Subject: help please



I am fairly new to DBA work and the senior DBA is off today. I have a developer who is trying to run SPFUI for the first time (new contractor) and he is getting the following error: [...] 12552 171 15_Re: help please24_Koleto, Kenneth M. [SLC]21_KKoleto@CSCUS.JNJ.COM30_Thu, 4 Jan 2001 13:25:05 -0500375_- Kathy, Have the developer go into SPUFI Defaults and change it to 'CS' from 'RR". Many shops secure the repeatable read (DNSEPRR) version from use because of the potential for tying up resources. The top of the screen should look like this:

CURRENT SPUFI DEFAULTS SSID: DB2D

===>





Enter the following to control your SPUFI session: [...] 12724 49 15_Re: help please11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Thu, 4 Jan 2001 12:25:20 -0600495_iso-8859-1 GRANT EXECUTE ON PLAN DSNESPRR TO NBB163

HTH

Amit

Tel:414-931-4815







-----Original Message----- From: KATHY JONES [mailto:JONESKS@GROUPWISE.CCSD.NET] Sent: Thursday, January 04, 2001 12:02 PM To: DB2-L@RYCI.COM Subject: help please



I am fairly new to DBA work and the senior DBA is off today. I have a developer who is trying to run SPFUI for the first time (new contractor) and he is getting the following error: [...] 12774 21 15_Re: help please11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET30_Thu, 4 Jan 2001 10:27:36 -0800500_US-ASCII thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again

Kathy Jones Central Information Services Clark County School District Senior Database Analyst 702-799-5040 x366 jonesks@GroupWise.ccsd.net

===============================================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. 12796 44 49_Re: getting field names from DB2 stored procedure16_Peter Van Eeuwen24_pvaneeuwen@ALTAVISTA.COM30_Thu, 4 Jan 2001 11:09:38 -0800349_- It has been our experience that you WILL NOT get column names in the cursor(resultset).

We had a similiar issue and the solution was for the application, your case VB, in ours EJB, to access the columns by displacement order not column name.

If anyone has had success naming column names in a cursor; I too would be interested. [...] 12841 142 41_Re: need help asap - a few more questions16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Thu, 4 Jan 2001 11:12:18 -0800522_iso-8859-1 We're still working in the dark here. Is the load LOG YES (it must be, but confirmation helps)? Is the load RESUME or REPLACE? You said, "hung up ... for hours so I canceled the job and terminated the utility". I take that to mean that it ran for hours BEFORE you cancelled it. How long did it run before it was cancelled? How long has it been since? Does "hung up" mean that it was not processing or that it was just taking a long time to load? Was the row count increasing? How many rows were you trying [...] 12984 175 49_Re: getting field names from DB2 stored procedure13_Andy Seuffert21_aseuffert@NEONSYS.COM30_Thu, 4 Jan 2001 13:19:32 -0600369_us-ascii







Andy Seuffert@NEON 01/04/2001 01:19 PM

For DB2 UDB OS/390, in order to get back the column names for a Describe SQLDA you must have the DESCSTAT paramater in DSNZPARM set to YES. Perhaps UDB on NT has a similar feature.

From the DB2 UDB for OS/390 V6 Installation Guide 2.5.18:

13. DESCRIBE FOR STATIC [...] 13160 19 50_Re: referential integrity question - OS/390 DB2 V515_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 4 Jan 2001 13:40:50 -0600481_us-ascii Judy

If you define RI Constrants correclty then first LOAD first parent table then proceed loading in the child table. In this way you should'nt get any check status once you finish loading in child table.

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. 13180 28 29_Re: -904 error on alter index15_David A. Zelmer25_david.zelmer@CITICORP.COM30_Thu, 4 Jan 2001 13:44:24 -0600413_us-ascii Hi,

The original question concerning this -904 had to do with 4 catalog indexes. We also ran into this issue and a call to IBM support resolved the issue for us. We were told that these 4 indexes had to be converted by the CATMAINT process and could not be done via individual ALTER INDEX, REBUILD INDEX. Unfortunately, the IBM support person did not detail the reasons for this restriction. [...] 13209 17 19_DSNMLM Started Task16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Thu, 4 Jan 2001 14:53:25 -0500449_iso-8859-1 Hey Folks... We're new to WLM.. When we start the DSNWLM, it ends within 5 minutes with a CC=0. Is this normal? Or should it always be up and running?

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. 13227 53 41_Re: Data Sharing members, different times11_rick creech18_ykcirc@HOTMAIL.COM30_Thu, 4 Jan 2001 13:40:47 -0600341_- Richard,

This statement is from the DB2 Data Sharing Guide: "At least one Sysplex Timer must be installed in the Sysplex. The Sysplex Timer keeps the timestamps of the S/390 processors synchronized for all DB2s in the data sharing group. DB2 data sharing uses a value derived from the timestamp to recover data using the log.' [...] 13281 215 41_Re: Data Sharing members, different times14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Thu, 4 Jan 2001 11:55:41 -0800453_iso-8859-1 Rick, My assumption would be that the customer has a sysplex, but one of the members of the sysplex runs with a different timezone parm coded in the CLOCKxx member of parmlib. I never even considered if that is doable, but since DB2 only cares about the GMT time, it may work, I don't know if I would want to try it though. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 13497 30 30_Re: DSMAX - Number Of Datasets14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 4 Jan 2001 15:01:20 -0500510_ISO-8859-1 If the SWA is above the line, it's only about .3K per open DS that you have to be concerned with. Regards, Joel



Message text written by DB2 Data Base Discussion List >It is 1.5k per open dataset. So, you can do some mathematics and see what is good for your shop. Actually even for (say) 3000 extra datasets defined with CLOSE YES, it will be just 4.5MB. Not too huge unless you are really crunched for virtual memory. Probably other list members could share their experiences.< [...] 13528 30 23_Re: Monitoring Question10_Rob Wright17_rwright@LIC.CO.NZ30_Fri, 5 Jan 2001 09:13:13 +1200380_us-ascii Hi Richard

Try the following:

ALTER the table to add the auditing required: ALTER TABLE AUDIT CHANGES Start a DB2 audit trace with audit class 4 -start trace audit class(4) To analyse the output, use the DB2PM AUDIT Report Set. See the DB2PM Report Reference

I haven't used these reports, but I'm sure you will get the detail you want. [...] 13559 39 25_Re: Online Reorg with NPI10_Rob Wright17_rwright@LIC.CO.NZ30_Fri, 5 Jan 2001 09:25:29 +1200414_us-ascii We hit a problem with online reorg that is possibly fixed with UQ49427 - we haven't had time to test this yet. Basically, we had a segmented tablespace with > 2GB, non-partitioned. During the online reorg, we got the following error messages:

DUMP00 TITLE=XXXX,ABND=04E-00C200A4,U=SIBAR1 ,C=XYR00.610.BMC -DSNB1RTR,M=DSNTFRCV,LOC=DSNBBM .DSNB5COM+1F46 DUMP TAKEN TIME=12.35.47 DATE=12/19/2000 [...] 13599 44 30_Re: DSMAX - Number Of Datasets12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 4 Jan 2001 14:44:51 -0600527_- I worked with a dozen or so customers on this for quite a few years (back to MVS times with a zap), and they tend to range DSMAX from about 12,000 to 18,000 now. The speed for open and close varies widely, depending upon the OS/390 level and the tuning for the ICF catalog, as well as the speed of the disk subsystems. If you have integrated systems that need more than 10,000 open data sets, then leaving them open is much more effective. Since this is a question of the working set, the best measurement is to see how [...] 13644 172 37_need help asap - a few more questions13_Basheer Shaik20_shaik123@HOTMAIL.COM30_Thu, 4 Jan 2001 20:54:31 -0000602_- Hi --

How to take out my e-mail address form this list. because i am getting lots of mail. i am not a DB2 Admin.... i am a Java Programmer...



Thanks Much --

>From: "Provenzola, Tony" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: need help asap - a few more questions >Date: Thu, 4 Jan 2001 11:12:18 -0800 > >We're still working in the dark here. Is the load LOG YES (it must be, but >confirmation helps)? Is the load RESUME or REPLACE? You said, "hung up >... >for hours so I canceled the job [...] 13817 27 41_Re: need help asap - a few more questions15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 4 Jan 2001 15:02:12 -0600569_us-ascii Assalam alaikum Basheer

Who said u r JAVA programmer. go to http://db2-l@ryci.com/db2-l/ and proceed to

' Join or leave the DB2-L list Web page ' for requesting these ppl to remove u from their list.



Eid Mubarak And wish you happy and prosperous year 2001



Thx Nayeem DB2 DBA

================================================ 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. 13845 121 23_Re: DSNMLM Started Task12_David Booher22_David.Booher@QUEST.COM30_Thu, 4 Jan 2001 13:01:21 -0800539_iso-8859-1 We too are new to WLM and running in the WLM environment. My address spaces exhibited the same behavior when manually started.

What you should do is switch to GOAL mode "V WLM,MODE=GOAL". Make sure you have the WLM Environment variables and procedure defined in the Application section of WILMA and the address space will start automatically when it is first referenced by the procedure. I have noticed that the address space stays up longer once it has been referenced, but eventually it will gracefully shut down. [...] 13967 16 41_Re: need help asap - a few more questions15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 4 Jan 2001 15:09:58 -0600376_us-ascii Try like this

LOAD DATA LOG NO INDDN SYSREC00 REPLACE SORTKEYS SORTDEVT SYSDA SORTNUM 4 INTO TABLE TABLE_XYZ .........

================================================ 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. 13984 210 44_need help asap - a few more questions -Reply11_Jeff Schade21_jschade@MAIL2.PCH.COM30_Thu, 4 Jan 2001 15:57:34 -0500523_US-ASCII Simply follow the directions posted at the end of every message:

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.

>>> shaik123@HOTMAIL.COM 01/04/01 03:54pm >>> Hi --

How to take out my e-mail address form this list. because i am getting lots of mail. i am not a DB2 Admin.... i am a Java Programmer... [...] 14195 12 48_Re: need help asap - a few more questions -Reply15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 4 Jan 2001 15:16:35 -0600264_us-ascii SOUNDS bitter

================================================ 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. 14208 76 51_Collections Plans Packages and software versioning?20_Westcott-Dryer, Lisa32_Lisa_Westcott-Dryer@FOREMOST.COM30_Thu, 4 Jan 2001 16:05:21 -0500592_iso-8859-1 Help. I'm being asked to devise a method to be able to have multiple versions of an application being developed at the same time! Yuk. I hope someone out there has done this and can give me good parameters.

Basically there are 3 "regions" (using the term region, loosely) DEVL,MODL,QUAL (are my HLQs on the same subsystem). I figure everything must converge in the QUAL region before moving to production (PROD). There are a lot of issues to wrestle with but my question is this. Can I take and make additional COLLECTIONS and PLANs to handle the different branches of [...] 14285 243 41_Re: need help asap - a few more questions16_Richard Mitchell19_dbaguru@HOTMAIL.COM30_Thu, 4 Jan 2001 15:33:21 -0600 14529 44 55_Re: Collections Plans Packages and software versioning?9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Thu, 4 Jan 2001 14:33:07 -0700406_iso-8859-1 Howdy Lisa,

The easiest way would be to CREATE a DATABASE name qualified by region-id (as you call it). This way you can hang identical-name DB2 objects off of them and not have to deal with COLLECTIONs etc. An example would be DEVLDB, MODLDB and QYALDB. As you probably know the Database is the HIGHEST object in the Heirarchy so any object attached to it could have the same name. [...] 14574 101 55_Re: Collections Plans Packages and software versioning?0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 4 Jan 2001 15:45:44 -0600595_iso-8859-1 We have 1 PLAN (per program) each for DEVEL and PROD. (Our equivalent to QUAL is just one of the DEVEL environments. Within DEVEL we have 28 standard "environments" . Each environment has its own CICS region, Collection ID, DBRMLIB, SOURCE Lib, COPYLIB, etc., LOADLIB.

Projects are normally assigned 2 environments (sometimes more), one for developers to work do project development, the second for client accepance testing (ie QUAL). Developer Senior Analysts (and project managers to a lesser exent) are responsible for assigning the available environments to projects. [...] 14676 17 55_Re: Stored Procedure interface after a severe DB2 error12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 4 Jan 2001 15:52:32 -0600420_- COMMIT and ROLLBACK are not permitted in a stored procedure until V7, and that would roll back the stored procedure and all other processing. You might want to check on the savepoints added in a V6 APAR, so that you roll back to a save point at the beginning of the stored procedure. V5 has none of the above, but you can chase through the stored procedure red book SG24-4693 for ROLLBACK to see older functions. [...] 14694 40 55_Re: Collections Plans Packages and software versioning?0_19_Tim.Lowe@STPAUL.COM30_Thu, 4 Jan 2001 16:04:09 -0600625_us-ascii Lisa, IF you do not need separate tables for each of "environment" in your DB2 subsystem, then you may not need to create separate collections or plans at all. You could simply put each "environment" into a separate DBRMLIB, and then cleanup your packages based on the dbrmlib, package, collection and version. For example, our package cleanup process currently keeps only the most current version of each package in each collection for each dbrmlib. This way, if different projects want more "versions", then they use more dbrmlibs. Please note that when these additional dbrmlibs go away, you need to cleanup [...] 14735 13 22_Re: DB2 UDB data types12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 4 Jan 2001 16:02:54 -0600383_- XML support is primarily through the XML extender and text extender. That's true across the DB2 family.

Roger Miller, DB2 for 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. 14749 46 30_Re: DSMAX - Number Of Datasets17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Thu, 4 Jan 2001 21:14:32 -0200393_us-ascii From DB2 V6 installation guide:

2.7.1.8 Increase maximum number of data sets open

The maximum number of concurrently allocated data sets increases to 32767 for customers running OS/390 Version 2 Release 6. The practical limit for concurrently allocated data sets depends on virtual storage below the 16M line, OS/390 allocation control blocks and some DB2 storage. [...] 14796 16 41_Re: Data Sharing members, different times12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 4 Jan 2001 16:16:41 -0600316_- The LRSNs need to be running the same time, or logs and recovery will be a disaster. Different time zones might be possible, but this is certainly outside of manufacturer's design, test or support. Several customers noted that setting the clock back during their Y2K testing did not work.

Roger Miller [...] 14813 71 23_Re: DSNMLM Started Task12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 4 Jan 2001 16:28:27 -0600565_us-ascii Michael, I think most likely you are already in Goal mode of WLM, issue D WLM,SYSTEMS to verify this. Under Goal mode, if you manually start the Stored Procedure address space, it will go away after a few minutes of inactivity. If you did not see any RRS connect errors or other error messages in the log, it is probably functioning normally. WLM will start it himself when there is a request for a stored procedure. Check also to see if RRS is running. Under Compatibility mode, you have to manually start the address space, but then it should never [...] 14885 74 20_Re: SQLSTATE 01506 ?11_Suresh Sane21_data_arch@HOTMAIL.COM30_Thu, 4 Jan 2001 16:37:12 -0600611_- Ulf,

My guess is that the query was run on the 31st of December/July or on 30th/31st of May (where subtracting 3 months results in an invalid date) - DB2 then adjusts to the next day. True?

HTH.

Suresh



>From: Ulf Cihak >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: SQLSTATE 01506 ? >Date: Thu, 4 Jan 2001 11:28:37 -0600 > >Dear List, > >during execution of a DSNTEP2 (on DB2/OS390 Version 5) of > >SELECT ... >FROM ... >WHERE VSTART_DATUM < CURRENT DATE - 3 MONTH > AND ( ... OR ...) >GROUP BY ... > [...] 14960 64 17_Re: LOAD Question14_Laredo, Andrew19_Andy_Laredo@BMC.COM30_Thu, 4 Jan 2001 16:39:49 -0600550_iso-8859-1 Should we assume you're not speaking from experience...

-----Original Message----- From: Blaicher, Chris [mailto:Chris_Blaicher@bmc.com] Sent: Wednesday, January 03, 2001 10:43 AM To: DB2-L@RYCI.COM Subject: Re: LOAD Question



A slight change to the syntax to make it clearer.

LOAD DATA RESUME YES INTO TABLE1 PART nnn REPLACE ........

As with anything you do, test it first in a test environment. Destroying a production table because you did not test it first is no way to get ahead in a company. [...] 15025 27 39_Any SQLJ performance experts out there?13_Scott Goodell22_scott.goodell@WCOM.COM30_Thu, 4 Jan 2001 16:43:17 -0600543_us-ascii We have been trying to use SQLJ to access DB2 OS390 and have determined some "problems" with host variable generation by db2profc.

db2profc creates a ".bnd" file with String host variables for CHAR columns with a length of 254 instead of the actual column length. This causes DB2 OS390 to NOT use matching index scans because of the length miss-match. I have verified this by editing the ".bnd" file and changing the host var lengths to the correct value and then binding. After the manual "fix" access paths are correct. [...] 15053 70 43_Re: Any SQLJ performance experts out there?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 4 Jan 2001 18:18:02 -0500403_us-ascii That is definitely a problem, I haven't found a way yet. Apart from it the number of inner classes the SQLJ precompiler puts also creates performance degradation as compared to JDBC straight.

My JDBC servlet runs 5 times faster that SQLJ servlet (exact same code except for being JDBC or SQLJ). This seems ridiculous that dynamic SQL program runs faster that static SQL program !!! [...] 15124 151 33_PeopleSoft / DB2 Connect problems18_Bond, Victor - CFC20_Bond.Victor@CFWY.COM30_Thu, 4 Jan 2001 16:10:06 -0800429_iso-8859-1 I have a questions about problems anyone has encountered running SQR programs from a client in a PeopleSoft environment using DB2 Client Application Enabler version 6.1, with any level of fixpacks.

We are running PeopleSoft applications (HR and FS), using tools version 7.07. Our database server is DB2 v5.1 running on MVS. We have most of our PeopleSoft clients running on DB2 CAE version 5.2, fixpack 6. [...] 15276 113 32_NM DB2 Users' Group Mtg. 1/17/0116_Ulibarri, Sandra27_Sandra.Ulibarri@STATE.NM.US30_Thu, 4 Jan 2001 17:26:13 -0700693_iso-8859-1 NEW MEXICO DB2 USERS' GROUP MEETING

Wednesday, January 17, 2001 John F. Simms Building, Classroom 2 715 Alta Vista Santa Fe, New Mexico

AGENDA

8:00- 8:30 a.m. Sign-in and Refreshments. Please remember donations for coffee.

8:30- 8:40 a.m. Announcements, Review Agenda & Introduce Guest(s). Craig S. Mullins, BMC Craig S. Mullins is the director of technology planning with BMC Software. Craig has extensive experience in the field of database management, having worked as an application developer, a DBA and an instructor with multiple database management systems including DB2, Oracle, and SQL Server. Craig also worked as a research director with [...] 15390 103 50_Re: referential integrity question - OS/390 DB2 V513_Terry Purcell25_terry_purcell@YLASSOC.COM30_Thu, 4 Jan 2001 19:13:10 -0600348_iso-8859-1 Judi,

Unfortunately I cannot think of automated V5 method to do this reverse Foreign Key check.

However, if your current SQL to perform the check looks like:

SELECT COL_1, COL_2, COL_3 FROM TABLE_2 A WHERE NOT EXISTS (SELECT 1 FROM TABLE_1 B WHERE B.COL_1 = A.COL_1 AND B.COL_2 = A.COL_2 AND B.COL_3 = A.COL_3) [...] 15494 110 23_DB2 CONNECT Performance5_Pearl17_panidak@SCB.CO.TH30_Fri, 5 Jan 2001 10:57:31 +0700550_windows-874 Hello List I'm sorry to sent this topic again. I think previou one was difficult to read,so I cut picture and sent it to list. Our DBA Staffs discuss about DB2 CONNECT Setup. Our objectives are good performance and DB2 CONNECT S/W license expenses. We want to setup DB2 conversation between DB2 V 7.1 on PC at our branch and DB2 for OS/390 V 6 on Mainframe at Head Office. We have 2 choices to install DB2 CONNECT. One was installed DB2 CONNECT on PC Server at Branch and then link to Host (May be used TCP/IP Protocal). Another was [...] 15605 21 24_How to clean-up DB2 Log?18_Yosia Widi Nugroho20_yosia@CENTRIN.NET.ID30_Fri, 5 Jan 2001 12:32:42 +0700416_- Dear All,

Does anyone know how to clean-up the DB2 log files on SQLOGDIR directory? Thank you.

regards, Yosia.





Cheers, Yosia W. Nugroho

================================================ 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. 15627 72 49_AW: Converting application from Oracle to DB2 UDB12_Peter, Georg15_G.Peter@DZBW.DE30_Fri, 5 Jan 2001 08:12:13 +0100721_iso-8859-1 Patrick,

take a look at http://www-4.ibm.com/software/data/db2/migration/ and you'll find what you need.

And as a last thought: Welcome to the DB2 community !!

With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowlegde Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 15700 29 20_Re: SQLSTATE 01506 ?9_Ulf Cihak23_UlfCihak@COMPUSERVE.COM30_Fri, 5 Jan 2001 01:11:40 -0600429_- Suresh,

your guess is correct - the query run on December 31. However, in this case, this SQLSTATE would be very common - I have never noticed it before.

Also, the control query (in European Date Format)

SELECT DATE ('31.12.2000') - 1 MONTH FROM SYSIBM.SYSDUMMY1;

gives me

30.11.2000

WITHOUT any Warning and SQLSTATE.

So, please try a second guess :-) Thank you, Ulf Cihak [...] 15730 122 50_Re: referential integrity question - OS/390 DB2 V513_Terry Purcell25_terry_purcell@YLASSOC.COM30_Fri, 5 Jan 2001 01:35:39 -0600337_iso-8859-1 Judi,

I realised I sent SQL to perform exactly the same check as the CHECK DATA is running, since I've used this in sites for application enforced RI.

You may consider elaborating on the application process which is running slowly, since then the listers can compare with any process they have implemented. [...] 15853 24 20_Re: SQLSTATE 01506 ?9_Ulf Cihak23_UlfCihak@COMPUSERVE.COM30_Fri, 5 Jan 2001 02:03:46 -0600515_- Suresh (and all),

please discard my previous erroneous reply. My 'control query', as discribed in my previous message, does indeed give back SQLSTATE 01506 when issued through DSNTEP2. So, Suresh, you are absolutely right, SQLSTATE 01506 seems to be the normal DB2 correction message in this context.

The reason I never noticed it before is that normally I let my DSNTEP2 Jobs pass through with Returncode 4. Only in this particular case the Returncode 4 was cought by a subsequent Abend step. [...] 15878 67 49_Re: Converting application from Oracle to DB2 UDB10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM30_Fri, 5 Jan 2001 05:45:25 -0500332_ISO-8859-1 Hi Patrick. You might try loking at a tool called Move for Servers from Princeton Softech. This can extract both data and supporting DDL from Oracle, and then create corrresponding objects within DB2 UDB, and then populate them with the extracted data, converting the data from one DBMS format to the other for you. [...] 15946 24 44_Export from SQL SERVER to DB2 for OS/390????11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Fri, 5 Jan 2001 12:52:27 +0200450_- Hello List!

I have been using the import/export ulity of Sql Server's Data Transformation Services in order to move databases between SQL Server 7 and DB2 for NT .I have also imported tables from DB2 for OS/390 v5 to the SQL Server (DB2 Connect PE 7.1.1 installed).Unfortunately,i am not able to do the reverse action:move from Sql Server to DB2 for OS/390.I wonder if this is possible through DTS or if i am wasting my time trying.... [...] 15971 18 33_Re: V6 - Cursor names with dashes15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 5 Jan 2001 05:00:36 -0600389_- We've had problems with dashes in cursor-names in Version 5 only when we did a remote bind and we had to change all programs using cursor names with dashes, because all our production binds are remote.

But if I remember it rigth there was a PTF for version 6 which does allow dashes in SQL-identifiers even for remote bind. I think if I'm not rigth, someone will correct me. [...] 15990 179 39_Re: Transactions deadlocking on inserts13_Terry Purcell25_terry_purcell@YLASSOC.COM30_Fri, 5 Jan 2001 05:08:53 -0600572_iso-8859-1 Andrew,

Let me try and answer your questions based on my understanding.

1. Yes. If the value to be inserted does not already exist in the index, then the "next best" page will be found if the "best" page is locked by another process. 2. Yes. 3. Not sure, but assume that an S-lock would suffice to determine if the page has been committed. 4. The -803 after a retry means that the other process has committed, and your process has determined that the duplicate row is permanent; ie. not deleted or an insert rolled back by the first process. [...] 16170 21 31_DB2 Database Connection pooling11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Fri, 5 Jan 2001 12:05:11 -0000507_- Dear list,

Can somebody enlighten me as to whether the new type 2 inactive threads can be used to increase performance on connections being made through from an application thru DB2 CAE to DB2 connect to DB2 UDB for OS/390 V6?

TIA

Carl Newton

================================================ 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. 16192 50 41_Disaster Recovery Questions regarding DB20_20_cwpark@SAMSUNG.CO.KR30_Fri, 5 Jan 2001 21:46:14 +0900399_US-ASCII Hi everyone!

Our company is trying to make a DR(Disaster Recovery) plan using an Asynchronous Remote copy solution among XRC, SRDF Adaptive copy, HARC and so on.

- Our system configuration: OS/390, CICS, DB2 etc.

- The overall scenario of our DR plan

#1 Log and BSDS is transferred realtime to the backup center disk using Asynchronous Remote copy solution [...] 16243 34 6_DSNRLI15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Fri, 5 Jan 2001 08:09:44 -0500607_iso-8859-1 I'm still confused about how this connection works -

*Stored Procedure compiled with ATTACH(RRSAF)- This translates the calls to use DSNHLIR/DSNRLI *Regular CAF compile of the calling program using DSNALI

The strange thing is that the RRSAF attach in the SP doesn't even appear to be used, since the monitor shows the thread as a CAF thread. In addition, the SP doesn't do any of the RRSAF calls (e.g. we didn't even code them!), yet it appears that the SP uses the CAF thread/plan already allocated. In the documentation it states not to mix the CAF with RRSAF, but strangely [...] 16278 73 37_Re: PeopleSoft / DB2 Connect problems13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Fri, 5 Jan 2001 07:13:22 -0600467_iso-8859-1 I recall having a problem (can't remember the specifics) with SQRs and DB2 Connect V6.1 PE. I did not fight it, just went back to V5.2. I think V6.1 and some fix pack level address the problem. Check customer connection and see if you get any hits. hth glenn

-----Original Message----- From: Bond, Victor - CFC [mailto:Bond.Victor@CFWY.COM] Sent: Thursday, January 04, 2001 6:10 PM To: DB2-L@RYCI.COM Subject: PeopleSoft / DB2 Connect problems [...] 16352 82 45_Re: Disaster Recovery Questions regarding DB214_Vassie, Edward21_Edward_Vassie@BMC.COM30_Fri, 5 Jan 2001 07:30:24 -0600303_iso-8859-1 If DB2 is stopped when you replicate the catalog and database, I can see no reason why it should not restart normally. As far as DB2 is concerned, it will not know if it is being restarted at the home site or at the recovery site, and should behave in the same manner at both locations. [...] 16435 21 10_Re: DSNRLI15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 5 Jan 2001 07:28:25 -0600397_- Hello

A few words to your last question:

SP always use the thread of the program calling the SP. You cannot mix attachments in the very same adress space.

You can use SP with IMS or CICS transactions as well, which use a different attachment (i.e. IMS use DFSLI000) than SPs.

Well the other things I don't know either, but it could be a problem of your monitor. [...] 16457 82 45_Re: Disaster Recovery Questions regarding DB222_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM30_Fri, 5 Jan 2001 08:44:26 -0500544_iso-8859-1 The key point is that the BSDS, Log, DB2 Catalog, ICF Catalog, DASD VTOCs, Tape Mgmt System database must be in sync. The recovery is much simpler. Making copies while DB2 is down also keeps the recovery simple.



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



-----Original Message----- From: cwpark@SAMSUNG.CO.KR [mailto:cwpark@SAMSUNG.CO.KR] Sent: Friday, January 05, 2001 7:46 To: DB2-L@RYCI.COM Subject: [DB2-L] Disaster Recovery Questions regarding DB2 [...] 16540 54 10_Re: DSNRLI15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Fri, 5 Jan 2001 08:57:44 -0500364_iso-8859-1 That's my point exactly - "The SP will always use the thread of the program calling the SP" The calling thread was created through DSNALI(CAF) and the SP is WLM and must run under DSNRLI(RRSAF). The documentation says you cannot mix the CAF with RRSAF - But because the SP is in it's own address space I can understand why that portion would work. [...] 16595 160 37_Re: PeopleSoft / DB2 Connect problems11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Fri, 5 Jan 2001 09:01:27 -0500410_us-ascii Victor:

PeopleSoft has documentation on there site regarding what combinations of Operating System 95/98/NT/2000, CAE release, DB2 Connect EE and PE works with a specific PeopleTools release:

The Docs are just hard to find on their website...

Client Operating System: Windows 95 Client Hardware Platform: Intel PC

DBMS Vendor Supplied Database Connectivity Windows 95 [...] 16756 72 28_Re: How to clean-up DB2 Log?0_22_BILL_GALLAGHER@PHL.COM30_Fri, 5 Jan 2001 09:27:46 -0500357_us-ascii Yosia,

The db2uext2 user exit will archive log files once they are full and when all transactions contained within have been externalized to the physical database. Once the log file has been archived (to ADSM, for instance), it will delete the log file from the SQLOGDIR directory. We've been using this in our shop with great success. [...] 16829 23 48_Re: Export from SQL SERVER to DB2 for OS/390????11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Fri, 5 Jan 2001 10:26:12 -050082_us-ascii Actually, DTS does a decent job extracting data from DB2 on OS/390 to SQL 16853 93 45_Re: Disaster Recovery Questions regarding DB215_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 5 Jan 2001 09:29:48 -0600463_iso-8859-1 .

I can't immediately see any reason that you would need to do a conditional restart in this scenario. You will need to use DEFER(ALL) in the DSNZPARM setting, though. Otherwise, when DB2 restarts, it will try to apply log records to the spaces as they existed at the point of failure. Since the spaces were copied the night before, they will not be in the right state for those log records to be applied until you do the LOGONLY recovery. [...] 16947 51 33_Re: V6 - Cursor names with dashes0_19_Tim.Lowe@STPAUL.COM30_Fri, 5 Jan 2001 09:29:34 -0600451_us-ascii Walter, You are correct, Apar PQ31209 / V6 PTF UQ36980 resolves the problem with remote bind of Cobol programs that contain hyphen in cursor identifiers.







Walter Janissen cc: Sent by: DB2 Data Subject: Re: V6 - Cursor names with dashes Base Discussion List



01/05/2001 05:00 AM Please respond to DB2 Data Base Discussion List [...] 16999 15 30_Automated DB2 Restart Question17_McGee, Luke (ECS)23_LuMcGee@EXCHANGE.ML.COM30_Fri, 5 Jan 2001 10:42:07 -0500528_us-ascii I am interested in the various procedures that might be in use for the automated restart of DB2 after an abnormal termination. I am assuming that some sort of Automatic Operator Software is used to monitor system messages and then take action upon the failure of a DB2 System. There are obviously many details involved with this issue depending on the circumstances of the failure, so maybe we could discuss the issue at a more generic level such as a "must complete" type of failure which stops DB2, by design... [...] 17015 77 25_Re: referential Integrity9_Rob Crane22_racrane@CONCENTRIC.NET30_Fri, 5 Jan 2001 09:09:26 -0700373_us-ascii Sounds like you are saying that you want All Parent rows (table1) to always have at least one Child row (table2). You could verify that this condition exists after your loads are done by doing a LEFT OUTER JOIN Parent table (table1) to child table (table2). If you have any parent rows without children B.COL4 will show at the end of the join output as null. [...] 17093 14 31_CA Datacom/DB to DB2 Conversion16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM30_Fri, 5 Jan 2001 11:19:40 -0500399_iso-8859-1 Is anybody aware of any software to help such a coversion? The programs accessing Datacom/DB are written in Cobol, CA Ideal, and CA Ideal for DB2.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17108 39 26_DB2 Connect Userid Problem16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Fri, 5 Jan 2001 11:35:55 -0500468_iso-8859-1 We're using DB2 Connect 5.2 & DB2 UDB OS/390 V6. I'm trying to add a new database via CCA. Here's what I'm using to create it..

Protocol TCP/IP Target Operating System OS/390 Hostname XXXXXX.XXX.XXX.XX (our mainframe address) Port 446 Location Name MVS2DB2 Database Alias PEA0NPA

When I try to test the connection, I'm using a userid of an sqlid I granted sysadm auth to in our MVS2DB2 location (subsystem). I then receive the following: [...] 17148 38 35_Re: DB2 Database Connection pooling13_McHugh, Gerry21_MchughG@DIVINVEST.COM30_Fri, 5 Jan 2001 10:10:20 -0600454_iso-8859-1 Type 2 are more desireable because they use less storage. Some restrictions apply. DB2 will try to make an inactive thread a type 2 thread if it can. See the install manual under panel DSNTIPR, and the admin guide under chapter 5-6 for more info.

-----Original Message----- From: Carl Newton [mailto:newtonc@INNOVATION-GROUP.CO.UK] Sent: Friday, January 05, 2001 7:05 AM To: DB2-L@RYCI.COM Subject: DB2 Database Connection pooling [...] 17187 28 28_IDUG Conference Registration9_Cam White20_cwhite@REVEALNET.COM30_Fri, 5 Jan 2001 11:38:21 -0500430_iso-8859-1 RevealNet has just started a drawing for a free full-conference registration to IDUG 2001. Details at http://www.revealnet.com in the "Freebies" section. Winner to be announced Monday, April 2.

Best wishes,

Cam White RevealNet, Inc. P.O. Box 5560 Rockville, MD 20855 301-947-5706

Subscribe to the Pipeline Newsletter for free technical articles, tips and discussions. http://www.revealnet.com [...] 17216 166 30_Re: DB2 Connect Userid Problem14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 5 Jan 2001 08:33:03 -0800326_iso-8859-1 Micahel, What error message are you seeing in the mstr log? This will help to pinpoint the problem and ensure that you are getting to the subsystem. Is this a new subsystem? Are you using RACF? Is a new subsystem, then you need to activate the RACF Dist class and be attached to it to connect to DB2. HTH Phil [...] 17383 20 33_PeopleSoft / DB2 Connect problems0_39_brian_holroyd.consultant@PEOPLESOFT.COM30_Fri, 5 Jan 2001 08:45:31 -0800546_us-ascii Victor, Please check you have DISABLEKEYSETCURSOR = 1 as described in our platform cert web page http://www4.peoplesoft.com/psdb.nsf/5127eab217ab83ab082565fb00007cb6/b0bfb338109b3c3a882568c500577e00?OpenDocument

This setting has caused a variety of strange abends in the past

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. 17404 45 28_Re: How to clean-up DB2 Log?11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Fri, 5 Jan 2001 11:43:19 -0500416_us-ascii Yosia,

"Clean-up" would only be needed if you're doing archive logging, and you need to be careful of which files you get rid of. Do not put yourself into an unrecoverable state. Bill Gallagher's answer had some very good comments. Also, to help understand the relationship between backups, logs, and restores, please see a white paper on our web site: http://www.database-guys.com/backrec.html [...] 17450 20 39_Re: Transactions deadlocking on inserts0_39_brian_holroyd.consultant@PEOPLESOFT.COM30_Fri, 5 Jan 2001 08:56:37 -0800370_us-ascii Andrew, I didn't catch your original posting so excuse me if my comment is irrelevent, but I frequently get deadlocks on inserts into tables with row level locking (no dups involved), where I'm using sequentially assigned keys, when I'm doing high volume benchmarks. I've always been lacking an explanation, so I'll follow your thread with interest. Brian [...] 17471 95 18_Re: need help asap0_27_jim.leask@RS-COMPONENTS.COM30_Fri, 5 Jan 2001 16:56:40 -0000638_iso-8859-1 Hi,

If you are on v6 you can see if it is using an archive tape to back out by running a -DIS LOG command.

Jim.

-----Original Message----- From: Billings, Linda [mailto:linda.billings@DOA.STATE.WI.US] Sent: 04 January 2001 16:20 To: DB2-L@RYCI.COM Subject: Re: need help asap



Hi, Edie,

My first thought is that it is that your utility is still rolling back. If the workload is very great right now rolling back could take some time. Also, if your active logs have archived and those archives are on tape then DB2 is constantly waiting for tape mounts. Bouncing DB2 will not help [...] 17567 31 33_Creating a Copy of an Archive Log14_Dean Montevago18_DMonteva@VNSNY.ORG30_Fri, 5 Jan 2001 12:04:40 -0500519_- Hi,

When we cut an archive log tape we want to create a copy of it to be sent offsite. I have code tha will build and excute the copy process. I was thinking of using Netview to trap the message from the archive, parse out the dataset name, and start a task passing the log dataset name as a parm. Is anybody out there using Netview to do this ? If so, would you be willing to donate the clist to facilitate this process, I don't know Netview yet. Or does anyone have any suggestions as to another method [...] 17599 19 23_Re: Monitoring Question12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 5 Jan 2001 11:12:39 -0600293_- DSN1LOGP can extract and print if what you want is simple. You can specify a DBID and OBID, so that you get only the tablespace that you're interested in. If you run something like Quiesce, then getting the RBA or LRSN values is fairly simple. See Utility Guide and Reference for more. [...] 17619 17 54_DB2 Connect - Use of the Call Level Interface (DB2CLI)13_McHugh, Gerry21_MchughG@DIVINVEST.COM30_Fri, 5 Jan 2001 11:20:28 -0600393_iso-8859-1 I know that the CLI/ODBC interface will be called by ODBC, JDBC, and SQLJ applications, meaning that parameter values in the db2cli.ini file associated the database alias/data source name you're connecting to will be observed. I'm a Java novice, and was hoping that someone could tell me if this would also apply to Enterprise Java Beans, meaning will EJB's also call the CLI. [...] 17637 26 18_regarding compress17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Fri, 5 Jan 2001 11:18:37 -0600434_- Hi Folds,

In our shop we are using DB2 v6, on os390. we have both test and prod. database. when i saw the sysibm.systablepart for all the tablespaces in test database defined as a COMPRESS = Y but in production database all the tablespace as defined as COMPRESS = N. why is that so???? is there any performance issue??? if we defined compress = y. how the data will stored in db2.... ??? can somebody help in this issue [...] 17664 49 37_Re: Creating a Copy of an Archive Log11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Fri, 5 Jan 2001 09:25:04 -0800652_iso-8859-1 Dean, you can specify in your Zparms (ARCPFX1=,ARCPFX2=,TWOARCH=YES)to cut two archive tapes and use your tape management system to send one copy offsite. cliff:-)



-----Original Message----- From: Dean Montevago [mailto:DMonteva@VNSNY.ORG] Sent: Friday, January 05, 2001 9:05 AM To: DB2-L@RYCI.COM Subject: Creating a Copy of an Archive Log



Hi,

When we cut an archive log tape we want to create a copy of it to be sent offsite. I have code tha will build and excute the copy process. I was thinking of using Netview to trap the message from the archive, parse out the dataset name, and start a task [...] 17714 25 10_Re: DSNRLI14_Rufus McDoofus24_rufus_mcdoofus@YAHOO.COM30_Fri, 5 Jan 2001 11:21:27 -0600574_- Rick

Stored procedures don't run require a plan, they use packages, and the collection id must be specified on the CREATE PROCEDURE statement, and the package must be bound on the server to do any DB2 work there.

The best fix, I would think, is to precompile your COBOL code (that calls the procedure) with ATTACH(RRSAF) on the input parms to the precompile step, to generate calls to DSNRLI instead of DSNALI (changing your link parms as well of course, to pull in DSNRLI). Why does it work with a blend of CAF/RRSAF? Don't know; might be one of those [...] 17740 15 49_Re: Converting application from Oracle to DB2 UDB12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 5 Jan 2001 11:22:38 -0600511_- I think Siebel has a conversion process, so that the general conversion facilities are not required. So the first call should probably go to your Siebel rep. I did not see the information with a quick look at the web, http://www.ibm-siebel.com/us/

Roger Miller

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 17756 111 35_FW: CA Datacom/DB to DB2 Conversion14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 5 Jan 2001 09:35:15 -0800465_iso-8859-1



-----Original Message----- From: Philip Gunning To: 'Swinski, Kenneth ' Sent: 1/5/01 9:29 AM Subject: RE: CA Datacom/DB to DB2 Conversion

Ken, Here is the one I was trying to remember, MANTECH. They are an IBM business partner. HTH Phil http://www.mssc-mantech.com/products/products.html

-----Original Message----- From: Swinski, Kenneth To: DB2-L@RYCI.COM Sent: 1/5/01 8:19 AM Subject: CA Datacom/DB to DB2 Conversion [...] 17868 48 19_Re: DB2 and Freeway12_Richard Pack23_rpack1@MAIL.STATE.MO.US30_Fri, 5 Jan 2001 12:08:11 -0600644_- On Wed, 3 Jan 2001 10:29:39 -0600, John Arbogast wrote:

>We are building a sub system on our new freeway box to join a 2 way ds >system. Anyone doing something similar? > >-----Original Message----- >From: khampto1@TXU.COM [mailto:khampto1@TXU.COM] >Sent: Wednesday, January 03, 2001 9:44 AM >To: DB2-L@RYCI.COM >Subject: DB2 and Freeway > > >We are running DB2 v6.1 on OS/390 V2.8, on one G5 and >one G6 CMOS processor (sysplexed, but no datasharing). >We are about to install a 2064 (Freeway) processor as a >replacement for the G5 box. Anyone else running DB2 for >OS/390 on a 64-bit machine [...] 17917 21 30_Re: DB2 Connect Userid Problem14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Fri, 5 Jan 2001 13:21:10 -0500329_US-ASCII Michael, I had this problem once, on an NT workstation and found that I had not catalogued my newly created (via CCA) database as a DCS Database. The "security" message threw me off. Seems that CCA does not issue the CATALOG DCS DB correctly, so do a LIST DCS and see if it looks good ... or if it is there at all. [...] 17939 48 49_Re: Converting application from Oracle to DB2 UDB3_DB218_csc266@HOTMAIL.COM30_Fri, 5 Jan 2001 11:26:40 -0700544_iso-8859-1 Get a hold of a copy of Embarcadero's DBArtisan. It was a lifesaver for a whole host of little tasks that are supportive to the migration process. DDL Extracts, comparisons,

I even moved a majority of the tables with DBArtisan ( any table that's under 5-10 Mb ). Of course, the big data tables would need import, bulk-copy methods. But, in most schemas , there are several small tables and some huge tables. A tool like Artisan helped me atleast focus on heavy duty migration just on tables that were large and volatile. [...] 17988 25 49_Re: Converting application from Oracle to DB2 UDB3_DB218_csc266@HOTMAIL.COM30_Fri, 5 Jan 2001 11:27:35 -0700487_iso-8859-1 The download is for trial purposes only. You'll need to buy the software if you needed to actually use it for your business.

----- Original Message ----- From: "Daniel Sullivan" Sent: Thursday, January 04, 2001 10:19 AM Subject: Re: Converting application from Oracle to DB2 UDB



Have you looked at DBArtisan from Embacadero¿ They have a wb site EMBARCADERO.COM. I beleive the software can be downloaded from the site. [...] 18014 52 49_Re: Converting application from Oracle to DB2 UDB14_Scott Saunders20_ssaunders@SIEBEL.COM30_Fri, 5 Jan 2001 10:37:33 -0800469_iso-8859-1 Patrick;

Siebel Systems Expert Services Division has experience and methodologies for converting to DB2 as well as experience with DB2 administration and management.

Regards, Scott Saunders Siebel Systems, Inc. Senior Architecture Specialist 14502 N. Dale Mabry Siebel 2000 Certified Professional Suite 200 IBM DB2 Certified Database Administrator Tampa, FL 33618 ssaunders@siebel.com 727-403-4981 http://www.siebel.com/worldwide-services [...] 18067 25 43_Who Supports DB2 Connect, ODBC, JDBC, etc.?0_22_BILL_GALLAGHER@PHL.COM30_Fri, 5 Jan 2001 14:06:17 -0500392_us-ascii Hi,

I'd like to take a very short survey about which area in your shop supports middleware products such as DB2 Connect, ODBC/MDAC, JDBC, etc. It's been an on-going issue here in my shop about where support for these products should belong. Right now, nobody has assumed responsibility, so we just keep our fingers crossed that everything works from one day to the next. [...] 18093 30 15_Re: help please18_Gerrit Groenendaal22_groenen@WORLDONLINE.NL30_Fri, 5 Jan 2001 20:14:33 +0100601_us-ascii HI,

In our shop we bind the plan dsnesprr standard with CS, so no problem at all.

Gerrit

KATHY JONES wrote:

> thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again > > Kathy Jones > Central Information Services > Clark County School District > Senior Database Analyst > 702-799-5040 x366 > jonesks@GroupWise.ccsd.net > > 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. [...] 18124 18 55_Catalog search for tablespaces names that have indexes.10_Lee Mandel16_DBMSUser@AOL.COM28_Fri, 5 Jan 2001 14:20:02 EST457_US-ASCII Hello List, If I had a database name and I wanted to list only the tablespaces in that database that had tables that had indexes what would the SQL look like to search IBM Catalog tables.

Thanks, Lee

================================================ 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. 18143 55 30_Re: DB2 Connect Userid Problem11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Fri, 5 Jan 2001 14:20:09 -0500374_us-ascii Hello Michael:

1- In looking at your e-mail the 446 port number is the DB2 Connect default port number when adding a new database/alias. Unless you are using the same port # on the MVS side ???

Protocol TCP/IP Target Operating System OS/390 Hostname XXXXXX.XXX.XXX.XX (our mainframe address) Port 446 Location Name MVS2DB2 Database Alias PEA0NPA [...] 18199 24 58_Re: DB2 Connect - Use of the Call Level Interface (DB2CLI)11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Fri, 5 Jan 2001 14:27:06 -0500147_us-ascii If the underline connection to the DB2 database is via that IBM DB2 ODBC DRIVER the settings on the DB2CLI.INI be in effect at the time of 18224 39 59_Re: Catalog search for tablespaces names that have indexes.11_David Nance16_DWNance@FHSC.COM30_Fri, 5 Jan 2001 14:33:40 -0500483_US-ASCII Try something like:

SELECT A.TSNAME FROM SYSIBM.SYSTABLES A ,SYSIBM.SYSINDEXES B WHERE A.DBNAME = '?????' AND A.NAME = B.TBNAME AND A.CREATOR = B.TBCREATOR



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

>>> DBMSUser@AOL.COM 01/05/01 02:20PM >>> Hello List, If I had a database name and I wanted to list only the tablespaces in that database that had tables that had indexes what would the SQL look like to search IBM Catalog tables. [...] 18264 40 59_Re: Catalog search for tablespaces names that have indexes.14_Raja, Shital S19_shital.raja@EDS.COM30_Fri, 5 Jan 2001 14:48:11 -0500613_iso-8859-1 Lee, try something like this

SELECT T.TSNAME, -- SYSIBM.SYSTABLES FROM SYSIBM.SYSTABLES T, SYSIBM.SYSINDEXES I where t.dbname = 'xxxxxxxx' and t.name = i.tbname

thanks Shital Raja

-----Original Message----- From: Lee Mandel [mailto:DBMSUser@AOL.COM] Sent: Friday, January 05, 2001 1:20 PM To: DB2-L@RYCI.COM Subject: Catalog search for tablespaces names that have indexes.



Hello List, If I had a database name and I wanted to list only the tablespaces in that database that had tables that had indexes what would the SQL look like to search IBM Catalog tables. [...] 18305 52 30_Re: DB2 Connect Userid Problem11_Mike Waters18_MWaters@BCBSMS.COM30_Fri, 5 Jan 2001 13:39:39 -0600380_us-ascii test

>>> "Dempsey, Michael" 01/05/01 10:35AM >>> We're using DB2 Connect 5.2 & DB2 UDB OS/390 V6. I'm trying to add a new database via CCA. Here's what I'm using to create it..

Protocol TCP/IP Target Operating System OS/390 Hostname XXXXXX.XXX.XXX.XX (our mainframe address) Port 446 Location Name MVS2DB2 Database Alias PEA0NPA [...] 18358 54 30_Re: DB2 Connect Userid Problem11_Mike Waters18_MWaters@BCBSMS.COM30_Fri, 5 Jan 2001 13:39:22 -0600389_us-ascii We had the same problem. We are using CA-TOP SECRET for security. To correct the problem, we had to permit the FACILITY associated with the DDF address space to the USERID.

>>> "Dempsey, Michael" 01/05/01 10:35AM >>> We're using DB2 Connect 5.2 & DB2 UDB OS/390 V6. I'm trying to add a new database via CCA. Here's what I'm using to create it.. [...] 18413 24 34_Contention on Dropping foreign key15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Fri, 5 Jan 2001 15:21:37 -0500518_iso-8859-1 I am trying to drop a foreign key, but I am getting -911 because one of the developer is running a select query in QMF. (It looks like developer is swapped out of DB2). The table on which the select is running does not have any relation to the table for which I am dropping the foreign key. But both are in the same database. Again there is no lock on any table in this database, but I see table on which select is running displayed when I use USE keyword in DISPLAY command. I am just curious to know [...] 18438 66 38_Re: Contention on Dropping foreign key0_19_Tim.Lowe@STPAUL.COM30_Fri, 5 Jan 2001 14:26:45 -0600406_us-ascii Philip, I would guess that the -911 is because it could not get an exclusive lock on the DBD to update it. What is the resource name and resource type that was returned with the -911?

Thanks, Tim





"Philip, Sibimon" To: DB2-L@RYCI.COM Subject: Contention on Dropping foreign key Sent by: DB2 Data Base Discussion List [...] 18505 52 38_Re: Contention on Dropping foreign key13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Fri, 5 Jan 2001 12:28:05 -0800356_iso-8859-1 Because the developer , owing to the fact that he ran a dynamic query and did not come out of QMF or did not do RESET DATA or did not max down to the bottom of his result set is still holding a shared lock on the DBD and because you are changing the 'database structure' you want an exclusive lock over DBD which is of course not possible. [...] 18558 83 41_IBM DB2 Administration Tool for OS/390 V20_21_kkane@PAINEWEBBER.COM30_Fri, 5 Jan 2001 15:25:40 -0500542_iso-8859-1 Hello List, Is anyone familiar with IBM's Admin Tool for O3/390 ? Our specific need is a tool which will generate scaled down and edited DDL for us to create test environments using the production environment as a baseline, ie we may need to create a copy of database sized at 10 percent of production with all of the necessary edits for qualifiers and subsystems performed properly, perhaps fed through a parameter list or panel. We currently perfom these tasks manually but are in the market for a tool. No salesman please. [...] 18642 46 41_Contention on Dropping foreign key -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM30_Fri, 5 Jan 2001 15:29:00 -0500307_US-ASCII Sibi,

The QMF query is holding a share lock on the database definition (DBD). The drop of the foreign key requires an exclusive lock on the DBD. If it looks like the QMF user is not active but his thread is still connected (and holding Locks) CANCEL his thread and try your drop again. [...] 18689 44 38_Re: Contention on Dropping foreign key13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Fri, 5 Jan 2001 14:37:12 -0600540_US-ASCII Sibi, Your QMF user probably has an S-lock on the DBD and you need an X-lock to drop the foreign key. You will half to drop the FK when no uses are in the database. HTH Kurt



>>> SPhilip@CSXLINES.COM 01/05/01 02:21PM >>> I am trying to drop a foreign key, but I am getting -911 because one of the developer is running a select query in QMF. (It looks like developer is swapped out of DB2). The table on which the select is running does not have any relation to the table for which I am dropping the foreign key. [...] 18734 87 45_Re: IBM DB2 Administration Tool for OS/390 V211_Trace Jones31_Trace_R_Jones@RELIANTENERGY.COM30_Fri, 5 Jan 2001 14:43:05 -0600372_us-ascii We just recently replaced some of our BMC tools with the IBM Admin tools. The IBM Admin tools are cheap and will do what you need and much more.



Trace R. Jones









kkane@PAINEWEBBER.COM Sent by: DB2 Data Base Discussion List 01/05/01 02:25 PM Please respond to DB2 Data Base Discussion List [...] 18822 98 38_Re: Contention on Dropping foreign key15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Fri, 5 Jan 2001 15:43:11 -0500396_- The detail is as follows

5.22.23 STC22555 DSNT501I :DB2T DSNILMCL RESOURCE UNAVAILABLE CORRELATION-ID=L3728CMP CONNECTION-ID=DB2CALL LUW-ID=* REASON 00C9008E TYPE 00000100 NAME CSDTEST

I understood now, but feel this DBD lock is unnecessary. Now I need to track down developer in the other half of the world and he think he is not locking anything since he is using WITH UR. [...] 18921 40 48_IBM DB2 Administration Tool for OS/390 V2 -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM30_Fri, 5 Jan 2001 15:44:19 -0500605_US-ASCII Hello Ken,

I've used CA/Platinum's RC/Migrator to do just what you're describing. It's a good tool for this type of work. You set up Global Change options to make the necessary edits to the base DDL and execute a strategy to create your new objects.

Regards, John

>>> kkane@PAINEWEBBER.COM 01/05/01 03:25pm >>> Hello List, Is anyone familiar with IBM's Admin Tool for O3/390 ? Our specific need is a tool which will generate scaled down and edited DDL for us to create test environments using the production environment as a baseline, ie we may need to create a copy of [...] 18962 83 26_CCSID - DB2v6.1 for OS/39019_Murari Selvakesavan22_MSelvakesavan@FHSC.COM30_Fri, 5 Jan 2001 15:49:25 -0500375_US-ASCII Hi all,

We recently in our shop migrated from DB2v5.1 to DB2v6.1 for OS/390. Before we moved on we had our Single-byte coded character set identifier CCSID used for character conversion defined as 0 and the Encoding Scheme as EBCDIC in our installation. This means all Databases and tablespaces defined under the subsystem assumed value 0 as SBCS_CCSID. [...] 19046 87 22_Re: regarding compress11_Bill Happel22_WRHappel@US.FORTIS.COM30_Fri, 5 Jan 2001 15:45:45 -0600530_US-ASCII Disadvantages of Data Compression:

1) There is some CPU Overhead to compress and expand blocks.

Advantages of Data Compression:

1) Reduces Overall I/O for sequential processing and can also save I/O for random processing. This is the big one. Compression causes more data to be inserted into one block, which means less blocks are needed, which means less I/O is need to need to read less blocks, which means a Performance Improvement. 2) Reduces DASD storage requirements. Less DASD is needed. [...] 19134 34 69_SQL Stored procedure passing a cursor to another SQL stored procedure16_Peter Van Eeuwen24_pvaneeuwen@ALTAVISTA.COM30_Fri, 5 Jan 2001 13:46:30 -0800654_- I am attempting to create a SQL stored procedure (SP1) that calls another SQL stored procedure (SP2).

SP2 creates a cursor which contains default address information. SP1 wants to call SP2 to append this default address information to additional information.

The environment is DB2 OS/390 version 6. DB2 connect version 7.

I was under the impress that SP1 would: DECLARE a RESULT_SET_LOCATOR; DECLARE the appropriate variables for the result set columns; CALL SP2 ASSOCIATE RESULT_SET_LOCATOR with PORCEDURE SP2; ALLOCATE resultset_cursor FOR RESULT_SET_LOCATOR loop / FETCH till my hearts desire; close the resultset_cursor; [...] 19169 18 73_Re: SQL Stored procedure passing a cursor to another SQL stored procedure0_26_truman.g.brown@VERIZON.COM30_Fri, 5 Jan 2001 16:59:23 -0500542_us-ascii I believe the cursor is uniquely defined to the executing SQL statement in an SP (or batch program, or CICS module) and as such cannot be passed to another executing entity. Just my opinion, which, if true, might be an interesting concept for IBM to pursue as an enhancement...

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. 19188 149 50_Re: referential integrity question - OS/390 DB2 V511_Suresh Sane21_data_arch@HOTMAIL.COM30_Fri, 5 Jan 2001 16:02:06 -0600374_- Terry,

I am confused by your solution.

1. Original query had table 1 as the parent (col1,2,3) and table 2 as the child (col1,2,3,4) - you seem to be using these in reverse. No big deal.

2. The construct - group by... having count(*) -> the only reason a combination of c1, c2, c3 exists was because we found one - how can the count ever be zero? [...] 19338 44 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Fri, 5 Jan 2001 15:50:31 -0700292_- As the DBA/Systems Programmer for DB2 I was initially responsible for showing that I could communicate with DB2 via ODBC and DB2 Connect but then the responsibility for installing on the client PC and/or server goes to the PC group and I no longer "manage" the software at that point. [...] 19383 201 47_Re: Who Supports DB2 Connect, ODBC, JDBC, etc.?14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 5 Jan 2001 15:02:19 -0800560_iso-8859-1 We (DBAs) installed and tested new releases and then provided the doc to the pc people to mass distribute the CAE or Runtime client or fixpack. We tested all fixpacks with all applications on a DBA test server then rolled out to test and dev. The problem that occurs when just the pc people support DB2 Connect is that they are not familiar with OS/390 and when doing test connections or troubleshooting they aren't able to get far if it is not on the Lan side. Just MHO. I preferred this setup because ultimately when there was a problem, the [...] 19585 19 11_Any site???15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Fri, 5 Jan 2001 17:07:32 -0600422_us-ascii Hi

Is there any URL where I can have sample REXX routine that checks the DB2 catalogs and generates reorg jobs ? (If it is available)

Thanks & Regards 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. 19605 152 50_Re: referential integrity question - OS/390 DB2 V513_Terry Purcell25_terry_purcell@YLASSOC.COM30_Fri, 5 Jan 2001 17:21:40 -0600351_iso-8859-1 Suresh,

1. Agree, no big deal. What the query does is perform an application enforced RI, where foreign keys are not explicitly defined. So it does not solve Judi's problem, but is an interesting query.....

2. True. "OR COUNT(*) = 0" is "not possible" predicate for HAVING clauses; same as "OR 0=1" is for WHERE clauses. [...] 19758 19 15_Re: Any site???10_Lee Mandel16_DBMSUser@AOL.COM28_Fri, 5 Jan 2001 21:42:06 EST475_US-ASCII Hi Nayeem, I am not sure about reorg jobs but if you are looking for a tool that read DB2 catalog and generate recovery jobs of all kind on the MVS platform see: http://www.recoveryknowledge.bigstep.com

Good luck, Lee

================================================ 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. 19778 43 28_how to empty table more fast5_elibs17_elibs@NETEASE.COM30_Sat, 6 Jan 2001 12:15:18 +0800205_gb2312 Hello,list: I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance?

Thanks

Larry 19822 29 32_Re: how to empty table more fast0_27_Jeff.D.Jerry@WELLSFARGO.COM30_Fri, 5 Jan 2001 22:23:20 -0600350_- If the tablespace it is defined in only contains this one table run load replace with an empty file as input. It basically deletes and redefines the underlying VSAM data set.

-----Original Message----- From: elibs [SMTP:elibs@NETEASE.COM] Sent: Friday, January 05, 2001 10:15 PM To: DB2-L@RYCI.COM Subject: how to empty table more fast [...] 19852 25 32_Re: how to empty table more fast13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 5 Jan 2001 23:25:44 -0500327_iso-8859-1 Hi Larry,

Under normal circumstances, the fastest way to delete all rows from a table is using the LOAD utility with an empty input dataset. It's late here and it's been awhile, but if I recall, if the tablespace is partitioned you need to LOAD each partition with nothing.

Check the Util manual. [...] 19878 119 22_Re: regarding compress16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Sat, 6 Jan 2001 10:43:01 +0530540_- If Compress Y is there for all the tablespaces then i think there should be for some tablespace in production as well which is a good candidate for compression. DSN1COMP is there to check the compression issues. However , your installation might require to check it actually for the performance reason. But YES for all tablespace in Test and NO for all tablespaces in production makes me think that check out your DDL generator (if u have any) and then look if it contains some default value posted YES in TEST and NO in production. [...] 19998 28 49_Re: Converting application from Oracle to DB2 UDB12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sat, 6 Jan 2001 12:51:07 +0200556_iso-8859-1 Hi, On top of all the other good advice you've got - pay attention to the differences between ORACLE PL/SQL and DB2 stored proceduers and the same goes for triggers. I miss the option to issue SQL commands within "before trigger" without reverting to calling a stored procedure. Sometimes you need to do some work "before trigger" and use the result data from it in an "after trigger" - in ORACLE it's in the same package, so you have it immediatly, in DB2 you have either to calculate it twice - ones "before" and once "after" , or call a [...] 20027 48 31_how to improve the performance?5_elibs17_elibs@NETEASE.COM30_Sat, 6 Jan 2001 12:14:29 +0800320_gb2312 Hello,list: My DB2 for OS390 has hold about 1000 tables and many of those have rows up to 40M.These large tables are devoted to being inserted and updated.As the size of the tables increase,the batch jobs run more and more slow.Is there anything to improve the performance except to create index the tabels? [...] 20076 71 49_How to improve the operation of insert and update5_elibs17_elibs@NETEASE.COM30_Fri, 5 Jan 2001 08:50:45 -0500459_gb2312 Hello,All:

I have a serious problem to the performance of my large table which has 40M rows and continues been enlarged.

The case is that, I have a routine batch job devoloped by AP to insert 200K rows into this table daily.I am very frustrated that this job always take me about 20 minutes to complete, althought I have add index to it and patition it into 4 parts, and reorg ,restat and rebind it every week,it still mean nothing. [...] 20148 64 58_How to recover the table only by DB2 Log without imagecopy5_elibs17_elibs@NETEASE.COM30_Fri, 5 Jan 2001 08:51:50 -0500363_gb2312 Hello,All: I am preparing the plans of recovery.One plan is how to recover my tables only by DB2 Log without ImageCopy? I have tried the following: RECOVER db1.tblspace1 LOGONLY.but it doesn't work.And then I tried RECOVER db1.tblspace1 LOGONLY TORBA(X'xxxxxx') ,and neither did it.

could someone kindly enough help me?

Thanks very much [...] 20213 54 68_=?iso-8859-1?Q?=BB=D8=B8=B4:______Re:_how_to_empty_table_more_fast?=5_elibs17_elibs@NETEASE.COM30_Fri, 5 Jan 2001 08:53:32 -0500486_iso-8859-1 Hi,David: Thank you for your kind help. I am afraid that I maybe have made you misunderstood.What I mean is that I don't want to take charge of the deletion of the table which it is the batch programe 's job,for the program has issued the DELETE table1 in the runtime that is dedicated to empty the whole table.I have analysed the programe that most of its run time is spent on the DELETE sql.So I wonder whether there are other ways to make the DELETE sql more faster? [...] 20268 33 15_Re: help please5_elibs17_elibs@NETEASE.COM30_Sat, 6 Jan 2001 12:00:09 +0800588_iso-8859-1 Hello, Since i am new to DD2,could you please kindly enough tell me how to bind dsnesprr? Thank you in advance Larry

----- Original Message ----- From: Gerrit Groenendaal Newsgroups: bit.listserv.db2-l To: Sent: Saturday, January 06, 2001 3:14 AM Subject: Re: help please



> HI, > > In our shop we bind the plan dsnesprr standard with CS, so no problem at all. > > Gerrit > > KATHY JONES wrote: > > > thanks to all that helped - he had RR instead of CS and when he changed it to CS it worked!! thanks again > [...] 20302 68 37_Re: Creating a Copy of an Archive Log11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Sat, 6 Jan 2001 09:14:53 -0500544_ISO-8859-1 Specifying TWOARCH=YES is a good idea for local site recovery. However I suggest that you do not use the second copy for remote site recovery because it can be called for by DB2 at the local site if the first copy throws a read error during rollback or recovery. It would invalidate your disaster recovery procedures to bring that offsite second copy back to the local site. Murphy's Law says this is the moment when the 747 lands on the data centre. Much better to copy one or both of the local archive logs as Dean suggested. [...] 20371 24 49_Re: Converting application from Oracle to DB2 UDB12_Kent Collins15_kcollins@WF.NET30_Sat, 6 Jan 2001 11:46:16 -0600390_iso-8859-1 Also, IBM has a Pre-Sales Technical Team that focuses on converting other DBMS applications to DB2. They have extensive experience in Oracle to DB2 conversions. This is a very talented group.

Dominic Marrese is the manager of the group. ( dmarrese@us.ibm.com )

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net [...] 20396 172 15_Re: help please18_Gerrit Groenendaal22_groenen@WORLDONLINE.NL30_Sat, 6 Jan 2001 18:59:54 +0100648_iso-8859-1 Larry,

Isolation is the parameter it must be CS and not RR, adjust the other parameters to your current settings.

xxxx is the name of the db2 subsystem. We are on version 6.

Gerrit



//STEP1 EXEC PGM=IKJEFT01 //SYSTSPRT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSIN DD DUMMY //SYSTSIN DD * DSN SYSTEM=(XXXX) TEST(0) RETRY(0) rebind plan(dsnesprr) owner(sysadm) + pklist( + *.dsnesprr.dsnesm68) + validate(run) isolation(cs) flag(i) dynamicrules(run) + acquire(use) release(commit) explain(no) + currentdata(yes) + qualifier(sysadm) + cachesize(256) + degree(1) + sqlrules(db2) + disconnect(expliciet) [...] 20569 32 35_Re: CA Datacom/DB to DB2 Conversion11_Brazil, Pat27_PBrazil@MIDATLANTIC.AAA.COM30_Sat, 6 Jan 2001 15:28:55 -0500451_iso-8859-1 Ken,

Try http://www.infoa.com/.

Pat

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Swinski, Kenneth Sent: Friday, January 05, 2001 11:20 AM To: DB2-L@RYCI.COM Subject: CA Datacom/DB to DB2 Conversion



Is anybody aware of any software to help such a coversion? The programs accessing Datacom/DB are written in Cobol, CA Ideal, and CA Ideal for DB2. [...] 20602 126 32_Re: how to empty table more fast16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Sat, 6 Jan 2001 18:21:00 -0500430_us-ascii



Larry, How large is the table? I use the following sql to delete plan tables DELETE FROM &CREATOR.PLAN_TABLE ; very fast for 250,000 rows. What does your sql look like? HTH Tom F









elibs on 01/05/2001 08:53:32 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) 20729 46 68_=?iso-8859-1?Q?=BB=D8=B8=B4:______Re:_how_to_empty_table_more_fast?=5_elibs17_elibs@NETEASE.COM30_Fri, 5 Jan 2001 19:32:50 -0500694_iso-8859-1 Hi,HTH: My sql is simply DELETE FROM db1.table1. Thanks Larry -----Original Message----- ·¢¼þÈË: Thomas E. Faglon ÐÂÎÅ×é: bit.listserv.db2-l ÊÕ¼þÈË: DB2-L@RYCI.COM ÈÕÆÚ: 2001Äê1ÔÂ6ÈÕ 18:35 Ö÷Ìâ: Re: how to empty table more fast



> > >Larry, > How large is the table? >I use the following sql to delete plan tables >DELETE >FROM &CREATOR.PLAN_TABLE ; >very fast for 250,000 rows. >What does your sql look like? >HTH Tom F > > > > > >elibs on 01/05/2001 08:53:32 AM > >Please respond to DB2 Data Base Discussion List > >To: DB2-L@RYCI.COM >cc: (bcc: Thomas E. Faglon/Telcordia) >Subject: [...] 20776 108 67_Re: =?iso-8859-1?Q?=BB=D8=B8=B4:_Re:_how_to_empty_table_more_fast?=16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Sun, 7 Jan 2001 09:21:36 -0500370_us-ascii



Hi, all your deletes are writing to the log , use one of the utility methods mentioned before load dummy replace no log. Tom F







elibs on 01/05/2001 07:32:50 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) 20885 35 32_Re: how to empty table more fast19_Murari Selvakesavan22_mselvakesavan@FHSC.COM30_Sun, 7 Jan 2001 12:50:55 -0500580_US-ASCII Hi Larry,

If the Tablespace contains only the table in question you may try Loading an empty file (SYSREC00 DUMMY) using the Load utility with REPLACE(YES) RESUME(NO) option. This essentially empties the table and redefines the dataset.



Murari Selvakesavan. DB2 DBA Group First Health Services Corp. 804.965.7601



>>> elibs@NETEASE.COM 01/05/01 23:16 PM >>> Hello,list: I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance? [...] 20921 35 32_Re: how to empty table more fast19_Murari Selvakesavan22_mselvakesavan@FHSC.COM30_Sun, 7 Jan 2001 12:58:44 -0500586_US-ASCII Hi Larry,

If the Tablespace contains only the table in question you may try Loading an empty input file (SYSREC00 DUMMY) using the Load utility with REPLACE(YES) RESUME(NO) option. This essentially empties the table and redefines the dataset.



Murari Selvakesavan. DB2 DBA Group First Health Services Corp. 804.965.7601



>>> elibs@NETEASE.COM 01/05/01 23:16 PM >>> Hello,list: I have to a batch job to delete the whole on large table which takes 10 minutes to complete the operation.Could someone tell me how to improve the performance? [...] 20957 188 50_Re: referential integrity question - OS/390 DB2 V511_Suresh Sane21_data_arch@HOTMAIL.COM30_Sun, 7 Jan 2001 12:47:43 -0600312_- Terry,

Thanks for the clarification. Makes sense now.

As to Judy's original question - given parent table T1 (pk = c1+c2+c3) and a child table T2 (pk = c1+c2+c3+c4, fk c1+c2+c3 pointing to T1) and the need to verify that each parent has at least 1 child: (not verifying RI but reverese RI): [...] 21146 27 10_Empy table12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sun, 7 Jan 2001 21:38:57 +0200369_iso-8859-1 Hi, If you can't use "load dummy" to delete the rows from your table (somewhere it was written that ann application program does it in "delete" command) and you can't replace the logic of the whole process then hope it's segmented and not simple or partitioned (delete from segmented clears the space map, from the other it has to go thru the pages...) [...] 21174 75 36_Read-only table design advice wanted15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Mon, 8 Jan 2001 12:03:32 +1100633_- Colleagues,

We have a relatively simple design issue I'd like some comments on. We have a number of solutions to what we want to achieve, all with pros and cons, and I'd appreciate some independent advice. Environment: OS/390 2.8, DB2 for OS/390 V5.

We run multiple application environments within a given DB2 subsystem, each with different qualifiers for their objects. Some of the tables involved are read-only and each environment has its own copy of these tables. Some of the rows in these read-only tables are common across the environments, some are unique to that environment. These tables are large-ish, [...] 21250 102 31_Re: Database middleware "Dr.Da'14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Mon, 8 Jan 2001 14:34:44 +1100597_iso-8859-1 The following is a message that was posted to this list some time ago: -----Original Message----- From: Nigel Hall [mailto:Nigel.Hall@starquest.com] Sent: Thursday, November 26, 1998 6:46 AM To: DB2-L@AMERICAN.EDU Subject: Re: ODBC



There certainly are plenty of 3rd party vendors, and more of them showing up on DB2-L! (welcome Richard :*) I found a list of ODBC vendors several months ago and was amazed at the number of companies who provide access to DB2. The list was shortened a little recently though when MicroFocus bought Intersolv and XDB. [...] 21353 81 10_Re: DSNRLI14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Mon, 8 Jan 2001 14:35:11 +1100488_iso-8859-1 The restriction on mixing attachment types is based on the address space. The code that invokes an SP (if it's not an SP itself) is in a different address space from the SP that is invoked. Hence the restriction is not relevent.

If there are multiple error conditions (eg using DSNALI for an SP), the error actually reported depends on the order that internal processing occurs. Part of the joys(?) of debugging is to decipher the actual error in such a situation. [...] 21435 27 21_FW: BPXBATCH and JAVA13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU30_Mon, 8 Jan 2001 16:08:01 +1100436_- Has anyone attempted to run Java under BPXBATCH to connect to DB2?

We are running a batch job using BPXBATCH that invokes a standard COOL:Gen generated COBOL program (using Call attach) that invokes a C program that invokes a Java application that references DB2. The batch job is failing with a S0C1 claiming that there is no SYSPRINT DD statement. One exists in the BPXAS address space and one in the batch application. [...]