1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2000, week 2 2 67 12_Re: AW: -4070_31_Ross_Mallett@SEARCHSOFTWARE.COM30_Fri, 8 Dec 2000 17:47:17 +1000398_us-ascii What you guys have is way too much faith in the documentation.

I have the following situation: A trigger, activated when a new row is inserted; Which calls a UDF

Now, the UDF was defined with the NULL CALL option

If it wasn't, then a single NULL field anywhere would prevent it being called at all and have it return a NULL, which would give error -407, as seen. [...] 70 92 35_Re: Excel to DB2 Connect for OS/39012_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 8 Dec 2000 09:05:04 +0200502_iso-8859-1 Hi, It looks like that demand is local. You should not be forced to do it in the db2cli.ini. Look with your local NT admin.

Isaac Yassin DBMS & IT Consultant

Email: yassin@netvision.net.il

Tel : +972 9 9505172 Cell: +972 54 452793 Fax : +972 9 9560803 ----- Original Message ----- From: "Zobjeck, A. J." Newsgroups: bit.listserv.db2-l To: Sent: Thursday, December 07, 2000 10:12 PM Subject: Re: Excel to DB2 Connect for OS/390 [...] 163 20 36_Upgrade from MVS 5.22 to OS/390 2.107_Eric Ng22_ngyh@PUBLICBANK.COM.MY30_Fri, 8 Dec 2000 02:39:03 -0600390_- Our shop is upgrading from MVS/ESA 5.22 to OS/390 2.10. We are using DB2 V4.1. We ordered SystemPak from IBM.

Since we are still using DB2 V4.1, hence no release change. Does this mean, we just need to copy all the DB2 datasets (except DB2 system dataset provided in the SystemPak) from MVS/ESA 5.22 on top of OS/390 2.10 ? Without even rebind the application packges/plans ? [...] 184 81 13_Re: Linux/39030_CHRISTOPHER.KELLER@Vereinte.de30_CHRISTOPHER.KELLER@VEREINTE.DE30_Fri, 8 Dec 2000 10:57:00 +0100522_iso-8859-1 --- Received from BG1.KELLERC 6785-2761 08-12-00 10.57 -

-> DB2-L@RYCI.COM

Hello, I find this discussion also quite interesting. A clear and consistent statement concerning the future of UNIX on OS/390 from IBM would be helpful, or have we (and Nebuchadrezzar)in fact already received this?

If we have an c/s application running under UNIX System Services would not the performance be better getting to DB2 via cross-memory services than with a LINUX solution (assuming here via DDF)? [...] 266 27 33_01.01.0001+ADO+DB2 Connect=Error!11_?????? ????24_dimokap@INTERAMERICAN.GR30_Fri, 8 Dec 2000 12:50:31 +0200608_- Hello List,

the programmers here are writting some programs using Visual Basic 6 + ADO + DB2 Connect (7.1.1).The original tables reside on DB2 for OS/390 v5 and their default date value is 01.01.0001.That value is succesfully read on a Record Set but when they try to use it,for example put it into a text box,they are getting Run-time error '-2147217887(80040e21)'.They have found out that the error occurs only with such dates (0001,0002 etc) but the application works fine with other 'normal' dates. Any ideas from listers who have faced the same or from IBMers would be highly appreciated! [...] 294 11 27_Re: Mad MAX goes again ....15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 8 Dec 2000 05:24:11 -0600360_- Well, the first is a stage2-predicate, the second not, and stage2- predicates all always more expensive than stage1.

================================================ 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. 306 47 31_Re: DB2 V7.1 18 char Limitation13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 8 Dec 2000 06:30:19 -0500530_iso-8859-1 I agree that we have to succumb to the SQL standard. I just wish someone with a little common sense had held sway in creating the standard. I have yet to hear a reasonable need for 128 characters in a name. Something longer than 18 makes sense to me, but with all the table names I have had to type in over the 13+ years as a DB2 DBA/designer I can't understand what human beings ever want to refer to something by a name longer then 30 or 40 characters. Yes, I understand there are twits out there in Oracle-land [...] 354 41 50_DB2 Connect vs. Informatica's PowerConnect for DB210_Peter Boot22_Peter.Boot@MAIL.ING.NL30_Fri, 8 Dec 2000 12:34:55 +0000785_US-ASCII Hi all,

We're being asked an opinion about Informatica's PowerConnect for DB2. We, being the DB2 persons we are, might prefer DB2 Connect, which we're introducing into the company anyway. Does anyone have positive or negative things to say about PowerConnect, either online or offline?

Thanks, Peter Boot

(Peter.Boot@mail.ing.nl)



----------------------------------------------------------------- ATTENTION: The information in this electronic mail message is private and confidential, and only intended for the addressee. Should you receive this message by mistake, you are hereby notified that any disclosure, reproduction, distribution or use of this message is strictly prohibited. Please inform the sender by reply transmission and [...] 396 53 23_Re: DB2 OS/390 V4 to V614_Scott Trometer22_scott.trometer@RCI.COM30_Fri, 8 Dec 2000 07:23:12 -0500614_iso-8859-1 I believe you would have to execute the Repair Utility using the LEVELD option

-----Original Message----- From: Dale E. Miller [mailto:dmiller@UGI.COM] Sent: Thursday, December 07, 2000 5:54 PM To: DB2-L@RYCI.COM Subject: DB2 OS/390 V4 to V6



I am in the process of installing DB2 V6 on OS/390 (V2.8). We are bypassing V5, going from V4 to V6 directly using another subsystem. My question is this: Can I install the new V6 subsystem; define all the structures, install our 3rd party products, copy all the data from the V4 subsys to the V6 subsys and then do a 'rename' of the [...] 450 103 23_Re: DB2 OS/390 V4 to V615_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Fri, 8 Dec 2000 07:44:41 -0500501_iso-8859-1 Dale,

In our environment we migrated from DB2 V4 to V6 of OS/390-V2.6 similar to your scenario. However, once I was ready to start the V6 subsystem (used the same name as V4 subsystem name), I shutdown the old V4 subsystem and brought up the new V6 subsystem.

Is this not possible with how you're planning things? It would make life easier and you wouldn't have to go through renaming all the individual structures. My guess is your needing to run both simultaneously ?? [...] 554 85 50_Re: Change password prompt for DB2 Connect (Reply)21_Brown, James R. - DIT25_brownjr@CI.RICHMOND.VA.US30_Fri, 8 Dec 2000 07:53:10 -0500507_iso-8859-1 Thanks Scott. I found what I was looking for in the archives after I posted the question. The NEWPWD parameter of the connect string solves the problem. I was simply looking for a way to implement password changes from a VB front-end.

Jim



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Scott Goodell Sent: Thursday, December 07, 2000 3:50 PM To: DB2-L@RYCI.COM Subject: Change password prompt for DB2 Connect (Reply) [...] 640 56 23_Re: DB2 OS/390 V4 to V611_Hayden, Lee18_Lee_Hayden@CSX.COM30_Fri, 8 Dec 2000 08:20:12 -0500664_iso-8859-1 I think this will work,

See: Admin Guide.

2.11.10 Changing the high-level qualifier for DB2 data sets

There is also some info in The Data sharing guide

4.4 Renaming the DB2 subsystem



-----Original Message----- From: Dale E. Miller [mailto:dmiller@UGI.COM] Sent: Thursday, December 07, 2000 5:54 PM Subject: DB2 OS/390 V4 to V6



I am in the process of installing DB2 V6 on OS/390 (V2.8). We are bypassing V5, going from V4 to V6 directly using another subsystem. My question is this: Can I install the new V6 subsystem; define all the structures, install our 3rd party products, copy all the [...] 697 69 26_Re: Varchar vs compression14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 8 Dec 2000 07:46:01 -0600602_windows-1255 Don't forget ORDER BY, etc., when discussion VARCHAR expansion

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On > Behalf Of Isaac Yassin > Sent: Thursday, December 07, 2000 12:36 PM > To: DB2-L@RYCI.COM > Subject: Re: Varchar vs compression > > > Hi, > I guess that the confusion is due to VARCHAR being expanded when > used in an > index. > Combine that with the DSNZPARM parameter RETVLCFK (Return Variable Length > Char From Key) to get a fully obscured... ;)) > RETVLCFK=YES ==> you get the VC field from index but the length is [...] 767 61 9_Batch QMF18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Fri, 8 Dec 2000 08:46:55 -0500339_iso-8859-1 Can anyone help me out here... I've got a 'proc' up in QMF that calls a 'query' and I want to pass it variables from my batch job. From inside QMF the proc & query run fine. Here's the query: SELECT COUNT(*) AS TABLES

FROM SYSIBM.SYSTABLES

WHERE

STATSTIME > &TBSTATS

OR CREATEDTS > &TBCREATEDTS [...] 829 69 31_Re: DB2 V7.1 18 char Limitation19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Fri, 8 Dec 2000 08:55:46 EST345_us-ascii I agree with Dave. On our project we have table names starting with Tnnnn_ where nnnn is a running number and most people refer to the table by their number rather than the description. Newcomers take a little time to get used to this but in the long run they too get the hang of it and it has proved convenient here. [...] 899 19 4_test18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Fri, 8 Dec 2000 08:52:42 -0500423_iso-8859-1 this is a test..sorry...

Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:OHS 4 - Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267 <<...>>

================================================ 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. 919 19 8_set mail18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Fri, 8 Dec 2000 08:54:57 -0500413_iso-8859-1 DB2-L SET MAIL

Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:OHS 4 - Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267 <<...>>

================================================ 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. 939 66 38_Re: strange db2look problem - V6.1 AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Fri, 8 Dec 2000 09:11:50 -0500639_iso-8859-1 Kurt:

Tried the command with the options you mentioned.....but still get the same result. Does it work at your installation? We're at "DB2 v6.1.0.27", "s000729".

Thanks for your input, Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Kurt Sahlberg > Sent: Thursday, December 07, 2000 5:47 PM > To: DB2-L@RYCI.COM > Subject: Re: strange db2look problem - V6.1 AIX > > > Hello Manas, > Try > db2look -d dbname -u tbcreator -e -p -o outputfilename > -i userid -w password > HTH > Kurt > >>> manas.dasgupta@PFSFHQ.COM 12/07/00 04:21PM >>> > I [...] 1006 68 23_Re: rounding the values19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Fri, 8 Dec 2000 09:02:08 EST322_us-ascii Rao,

First off, an integer would not have any decimal places. Your example actually provides a decimal number as an input and you want to round it off to a set number of decimal places. This would, theoretically, be possible with SQL but I wonder if the cost of the query would justify the use of it. [...] 1075 75 37_Re: 01.01.0001+ADO+DB2 Connect=Error!10_Marc Costa28_Marc.Costa@MUTUALOFOMAHA.COM30_Fri, 8 Dec 2000 08:16:31 -0600619_us-ascii Kalliopi,

We have the same problem here with MS Access. Access doesn't recognize dates earlier than 01/01/0100. I wouldn't be surprised if VB also didn't recognize dates earlier than 01/01/0100. We were also told by Microsoft that they would not be fixing this since 01/01/0100 is the generally accepted start date for the Gregorian calendar and they (Microsoft) questioned the need for dates earlier than 01/01/0100. We are using a case statement to get around the problem (i.e evaluate the date, if less then 01/01/0100 set the date to 01/01/0100 else use the value in the date field). You could [...] 1151 87 30_Re: non-corr-subquery and join16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM30_Fri, 8 Dec 2000 16:02:26 +0530321_- Hi Walter, I think the optimizer did the right thing by converting it to the nested loop join because as you mentioned the predicates are so bad in "%96" table. Have you checked the performance of the query given by Venkat and your original one ?. Please run both the query and pass on the access path difference. [...] 1239 120 27_Re: Varchar Column Question16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM30_Fri, 8 Dec 2000 15:56:16 +0530527_iso-8859-1 Hi Bruce, It depends the frequency of access of the restart table. I don't think it is better idea always to use one row per page than row level locking. Even if there are 100 applications accessing the data at a time then 100 locks will be taken for different rows. But it also depends on the size of the restart table. If the size is reasonably less then there are not much thoughts to be given for saving the space. As per the table definition given by you, i do not think you can accomodate more than 1 row [...] 1360 82 27_Re: Mad MAX goes again ....16_S, Sanjeev (CTS)25_SSanjeev@CAL.CTS-CORP.COM30_Fri, 8 Dec 2000 16:07:56 +0530333_- Hi Issac, Its interesting. It means that each and every Sqls should be tried and then coded. Is the access path of the second query is with matchcols 2 and for the first one it is with matchcols 1 for the subselect and then may be matchcols 0 ?. What are the indexes and its columns ?. Just guessing few things why it is so . [...] 1443 65 23_Re: rounding the values9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Fri, 8 Dec 2000 09:28:12 -0500481_us-ascii Try SELECT DECIMAL(ROUND(987.235476487,3),15,3)

This returns a result of 987.235, which is correctly rounded to the third decimal place. I am assuming you didn't really mean that you wanted a result of 987.236.











"Morrill, John" @RYCI.COM> on 12/08/2000 12:17:56 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 1509 148 16_Re: XML extender23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 8 Dec 2000 09:31:47 -0500563_- Glenn,

Thank you very much for your input. I guess we will have to put this project on hold until later. In the interim we do plan to try it out and see how far we can go ? Thanks again.



Sanjay Jain



> -----Original Message----- > From: Mackey, Glenn [SMTP:GMackey@GUIDEMAIL.COM] > Sent: Thursday, December 07, 2000 5:42 PM > To: DB2-L@RYCI.COM > Subject: Re: XML extender > > Hi, > > XML Extenders is in V7.1 on the OS390 (and elsewhere). > > I just finished a pilot on the NT UDB V7.1 environment, but found them to > [...] 1658 21 33_Insert from a different subsystem11_Brazil, Pat27_PBrazil@MIDATLANTIC.AAA.COM30_Fri, 8 Dec 2000 09:41:52 -0500324_iso-8859-1 When we move code tables at production turnover time, it would be great to do an insert/select to copy the data from 1 subsystem to another, but DB2 doesn't permit that. So we use the unload and load. Are there any plans to allow insert/select from a different subsystem? Why does db2 not allow it now (v5)? [...] 1680 41 38_Re: strange db2look problem - V6.1 AIX13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Fri, 8 Dec 2000 08:43:29 -0600568_US-ASCII Manas, I seem to recall a bug in one of the fixpaks, check the IBM website. Regards, Kurt

>>> manas.dasgupta@PFSFHQ.COM 12/07/00 04:21PM >>> I have a database called DB2TEST and a few tables created by user DB2TEST in V6.1/AIX. I then execute the following:

db2look -d DB2TEST -u DB2TEST -e

This does not generate the ddl. I then add -x option to the end and now I get the GRANTs generated but again not the DDL. I have also tried using the -t option with a specific table name and get the same result. However in our V5.x boxes the [...] 1722 11 23_Re: rounding the values15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 8 Dec 2000 08:39:58 -0600363_- I don't know if there is any installation option, but there is a new function is Version 6: ROUND which does the trick.

================================================ 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. 1734 34 37_Re: Insert from a different subsystem10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM30_Fri, 8 Dec 2000 09:48:03 -0500394_ISO-8859-1 Hi Pat. I don't know if you would consider a 3rd party solution, but there is a tool called Move for DB2 that allows the transfer of relationally intact subsets of data to be extracted to single ex

-----Original Message----- From: Brazil, Pat [mailto:PBrazil@MIDATLANTIC.AAA.COM] Sent: 08 December 2000 14:42 To: DB2-L@RYCI.COM Subject: Insert from a different subsystem [...] 1769 29 37_Re: Insert from a different subsystem10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM30_Fri, 8 Dec 2000 09:48:04 -0500512_ISO-8859-1 -----Original Message----- From: Brazil, Pat [mailto:PBrazil@MIDATLANTIC.AAA.COM] Sent: 08 December 2000 14:42 To: DB2-L@RYCI.COM Subject: Insert from a different subsystem



When we move code tables at production turnover time, it would be great to do an insert/select to copy the data from 1 subsystem to another, but DB2 doesn't permit that. So we use the unload and load. Are there any plans to allow insert/select from a different subsystem? Why does db2 not allow it now (v5)? [...] 1799 10 13_Re: Batch QMF15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 8 Dec 2000 08:45:41 -0600320_- Have you tried, coding the parms in one line and breaking before or after I=

================================================ 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. 1810 151 16_Re: XML extender13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Fri, 8 Dec 2000 08:50:30 -0600621_iso-8859-1 Sanjay,

Another item for you to consider is how to handle updates/Delete.

Our developers wanted to send an XML document to the extenders and have the extenders insert the data into corresponding tables. The extenders do that and that part works.

The developers also wanted to do an Update and delete function whereby they send an XML document, and have the extenders update (as opposed to an INSERT) or DELETE the corresponding tables. The Java developers did not want to drop back to SQL and table references. They want to stay with there objects and call something to do something. [...] 1962 29 54_Re: Change password prompt for DB2 Connect (Reply - 2)13_Scott Goodell22_scott.goodell@WCOM.COM30_Fri, 8 Dec 2000 09:06:19 -0600336_us-ascii I had a request for the code to connect using JAVA allowing the ability to change the password.

Here is the code stub. If you do not specify the NEWPWD setProperty then it will just attempt the connection.





String userid = argv[0]; String passwd = argv[1]; String url = "jdbc:db2:" + argv[2]; [...] 1992 14 30_Re: non-corr-subquery and join15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Fri, 8 Dec 2000 09:01:53 -0600430_- Hello Venkat

Thank you for your proposal. With the group by, I have my wished access path. And this is an example that DB2 Version 6 still converts a noncorr subquery into a join.

================================================ 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. 2007 37 37_Re: Insert from a different subsystem14_Scott Trometer22_scott.trometer@RCI.COM30_Fri, 8 Dec 2000 10:14:17 -0500542_iso-8859-1 DB2 V5 does allow for access between DB2 sub-sytems via Distributed Data Facility (DDF). If you only want to perform Dynamic SQL and/or minimal program processing, you could probably set it up as system-directed(DB2 Private Protocol)access.

Refer to the V5 Administration Guide 5.11.1 Remote Access Types for basic information.

-----Original Message----- From: Brazil, Pat [mailto:PBrazil@MIDATLANTIC.AAA.COM] Sent: Friday, December 08, 2000 9:42 AM To: DB2-L@RYCI.COM Subject: Insert from a different subsystem [...] 2045 33 37_Re: Insert from a different subsystem13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 8 Dec 2000 10:39:24 -0500438_iso-8859-1 Scott, I believe Pat is specifically asking about a statement like

Insert into Staff Select * from RemotLOC.Q.Staff

which is not supported in v5 or v6.

You get -512 SQLCode which says STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID.

You can't refer to multiple locations in the same SQL statement.

Pat, I have not heard of any plans allowing for multiple locations in one SQL statement. [...] 2079 102 38_FW: strange db2look problem - V6.1 AIX13_Doctor, Nadir24_Nadir.Doctor@BGEHOME.COM30_Fri, 8 Dec 2000 10:43:49 -0500511_iso-8859-1 Issue the query:

"select definer from syscat.tables where tabname= '' "

Most likely, your definer isn't DB2TEST.

Subsequently, try the command:

"db2look -d db2test -u -e -o -i -w "



-----Original Message----- From: Kurt Sahlberg [mailto:Kurt.Sahlberg@EXPERIAN.COM] Sent: Friday, December 08, 2000 9:43 AM To: DB2-L@RYCI.COM Subject: Re: strange db2look problem - V6.1 AIX [...] 2182 50 37_Re: Insert from a different subsystem0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 8 Dec 2000 09:44:16 -0600521_us-ascii If you are talking about copying/moving data from one DB2 subsystem to another at the same DB2 version then yes you can do it, 2 ways: - Via DDF, as Scott T points out. We do that using ALIAS's between the 2 subsytems. It goes across as dynamic SQL - DB2 "CONNECT" command. You can setup a compiled and bound module with static SQL using the CONNECT command, ie Select from subsys1_table1 into host variables DB2 connect to subsys2 Insert into subsys2_table1 from host variables DB2 Connect back to subsys1 [...] 2233 80 37_Re: 01.01.0001+ADO+DB2 Connect=Error!13_Andy Seuffert21_aseuffert@NEONSYS.COM30_Fri, 8 Dec 2000 09:44:03 -0600556_us-ascii Andy Seuffert@NEON 12/08/2000 09:44 AM

The use of 01/01/0001 actually seems to be a common practice among DB2 programmers as we have seen this also with a few of our customers using our ODBC driver, Shadow Direct, to access DB2. We found that what happened is that with ADO 1.5, you would get back 1/1/1901 when the date was set to 01/01/0001 in DB2. With ADO 2.0, it returns the error message you indicate. Looking up this message in MSDN, basicly states that this is an invalid date. My guess is that this was some sort of a Y2K fix [...] 2314 32 30_Re: non-corr-subquery and join20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 8 Dec 2000 10:47:23 -0500578_us-ascii We had the other way round. The query, which was getting converted to join was no longer a join conversion in V6. Which is a big thing for us as lots of access-path turned too bad. APAR 40338 was good news for us.

> -----Original Message----- > From: Walter Janissen [SMTP:walter.janissen@VICTORIA.DE] > Sent: Friday, December 08, 2000 10:02 AM > To: DB2-L@RYCI.COM > Subject: Re: non-corr-subquery and join > > Hello Venkat > > Thank you for your proposal. With the group by, I have my wished access > path. And this is an example that DB2 Version 6 still [...] 2347 141 38_Re: strange db2look problem - V6.1 AIX13_Adrian Savory27_Adrian.Savory@WORKTHING.COM30_Fri, 8 Dec 2000 15:44:40 -0000443_iso-8859-1 ....or alternatively try the -a option instead of -u . This should pick up all definers.

Adrian Savory ACSIS Limited

-----Original Message----- From: Doctor, Nadir [mailto:Nadir.Doctor@BGEHOME.COM] Sent: 08 December 2000 15:44 To: DB2-L@RYCI.COM Subject: FW: strange db2look problem - V6.1 AIX



Issue the query:

"select definer from syscat.tables where tabname= '' " [...] 2489 20 20_Sas-Db2 connectivity16_Srinivasan Kumar31_srinivasan.kumar@PRUDENTIAL.COM30_Fri, 8 Dec 2000 10:53:13 -0500479_us-ascii Hi,

In one of our SAS programs, we need to retrieve data from DB2 ( OS/ 390). I am not sure if there is anyway of connecting to DB2 from SAS. It will be of great help if anybody could help us in this..

Thanks Kumar

================================================ 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. 2510 46 24_Re: Sas-Db2 connectivity16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 8 Dec 2000 11:05:42 -0500638_iso-8859-1 Kumar, This is an 'optional feature' of SAS. You must purchase SAS/DB2. It works very well from either PC or TSO. We use it extensively here.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Srinivasan Kumar [mailto:srinivasan.kumar@PRUDENTIAL.COM] Sent: Friday, December 08, 2000 10:53 AM To: DB2-L@RYCI.COM Subject: Sas-Db2 connectivity



Hi,

In one of our SAS programs, we need to retrieve data from DB2 ( OS/ 390). I am not sure if there is anyway of connecting to DB2 from SAS. It will be of great help if anybody could help us in this.. [...] 2557 37 27_Sas-Db2 connectivity -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM30_Fri, 8 Dec 2000 11:07:59 -0500437_US-ASCII Hello,

SAS markets a SAS-to-DB2 interface which provides the connectivity you're looking for. Contact your SAS sales rep for this.

John

>>> srinivasan.kumar@PRUDENTIAL.COM 12/08/00 10:53am >>> Hi,

In one of our SAS programs, we need to retrieve data from DB2 ( OS/ 390). I am not sure if there is anyway of connecting to DB2 from SAS. It will be of great help if anybody could help us in this.. [...] 2595 90 31_Re: DB2 V7.1 18 char Limitation14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Fri, 8 Dec 2000 10:46:34 -0600527_iso-8859-1 Scott:

I think the single biggest reason for longer names for tables is to support end users. If the only folks who ever accessed and used tables were developers and DBAs, then I suppose some type of encoding down to 8 bytes is not catastrophic (albeit still more difficult to remember). But one of the big benefits of relational databases is that they can be queried in an ad hoc manner by end users with SQL. Do you really want to explain to the end users the difference between INVT0001 and INVT0002? [...] 2686 95 13_Re: Batch QMF16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Fri, 8 Dec 2000 09:05:37 -0800394_iso-8859-1 Your Query has 2 variables: &TBSTATS and &TBCREATEDTS. Your Proc maps 2 *different* variables: &&TBS = &TBS and &&TBC = &TBC.

When you run the Proc in batch, you should get the following:

MESSAGE TEXT: Please give a value for each variable name. &C1: RUN &C2: &TBSTATS

Change your Proc to have &&TBSTATS = &TBS and &&TBCREATEDTS = &TBC, and it should work. [...] 2782 46 27_Re: Mad MAX goes again ....12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 8 Dec 2000 20:15:39 +0200491_windows-1255 Hi, The point is that one is correlated and the other ain't. Correlation costs you dearly, especially when the values need more the 4kb to be stored - so you need to overlay them. Using the second method you build ones a table of the sums and then join it. It was meant only to show that you should always check for better solutions even if you're sure that you have the right answer. I've never seen it in any course material as they always teach the regular correlation. [...] 2829 116 27_Re: Mad MAX goes again ....20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 8 Dec 2000 14:03:00 -0500407_us-ascii Let me put my 2 cents worth -

The first query is correlated query with "=" condition, which usually discouraged. A correlated subquery is more efficient with EXIST kind of predicates.

SELECT A.K_NUMBER FROM MyTable A WHERE A.K_NUMBER = (SELECT MAX(B.K_NUMBER) ------- gets executed as many time as many rows in the table FROM MyTable B WHERE A.YEAR=B.YEAR) GROUP BY A.K_NUMBER ; [...] 2946 36 15_DB2 Job Posting15_Dougan Sherwood20_db2_2001@HOTMAIL.COM30_Fri, 8 Dec 2000 19:08:43 -0000661_- e-commerce company, located in Kendall Square, Cambridge, MA. This position will allow for relocation and will sponsor H1B Visa. This is a permanent, full-time position.

Job Description: Seeking a DB2 DBA to manage the DBA function in a multi-client, multi-server, mission critical, 24x7 environment for an e-commerce company.

Required Skills: Minimum of seven years of DB2 DBA experience, including at least one year of management experience, in an e-commerce or strong Internet focused environment; experience with Sun/Solaris/Unix; demonstrated track record of successfully maximizing system availability while minimizing response time. [...] 2983 17 13_Re: IFCID 1987_DB2 fan16_askdb2@YAHOO.COM30_Fri, 8 Dec 2000 13:20:42 -0600467_- Actually I think fast sampling of the random hit ratio in a pool would let me approximate the average reidency time. That is what I am in the process of simulating. Hit ratio over the average sampling rate(measured in secs) is what I would try to equate to give an approximate residency time. There seems to be no direct way of finding out. I will also have to trim the variances that can be expected in the residency time, measured this way. Regards, DB2 fan [...] 3001 150 27_Re: Mad MAX goes again ....20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 8 Dec 2000 14:26:32 -0500540_iso-8859-1 Correction to my earlier e-mail. -

In the earlier mail I typed following statement wrong - "this could be hog if colcard of K_NUMBER is significantly greater than colcard for YEAR" In fact, it is the opposite.

The correlated subquery uses "In memory table of 4K size" to keep immediate result set. If ratio (colcard K_NUMBER/colcard YEAR) is high and the table is clustered by YEAR then correlated subquery is very good choice because the results are kept in the memory table and no extra access is necessary. [...] 3152 44 38_Re: IBM DB2 Database UTILITY Questions12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 8 Dec 2000 13:30:12 -0600588_- On Thu, 7 Dec 2000 09:33:37 +0000, paul.packham@POSTOFFICE.CO.UK wrote:

>Hi, > >I have two questions on the IBM DB2 OS/390 Utilities which are puzzling us at >the moment. The reason behind the questions are because we are benchmarking the >IBM utilities against another vendors products. We have been running ISV >products in our shop for as long as I can remember so our experience in running >the IBM utilities is somewhat limited. > >a) Does anyone know why a shrlevel change IBM copy should consume more than >twice the CPU as a shrlevel reference copy ? > >b) Is there [...] 3197 19 15_TRACKMOD Change13_Lisa McClaine26_lisa.mcclaine@BLACKBOX.COM30_Fri, 8 Dec 2000 14:53:35 -0500476_iso-8859-1 Does anyone know if changing the TRACKMOD parameter from a YES to a NO requires a reorg? I'm trying to make sure we don't run into the problem with data loss associated with APAR PQ41076.

Thanks.

Lisa McClaine

================================================ 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. 3217 37 19_Re: TRACKMOD Change20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 8 Dec 2000 15:13:16 -0500291_us-ascii No, it doesn't require a reorg but I tested this while V6 was beta and I found that you may have to stop/start the tablespace for it to be effective (not to alter but to be effective). However there may have been changes from beta till now and may not even require stop/start. [...] 3255 28 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?7_DB2 fan16_askdb2@YAHOO.COM30_Fri, 8 Dec 2000 14:11:25 -0600555_- VDWQT at 0 means you will be Dequeuing 32 pages when 40 pages that belong to the same dataset fills a virtual bpool. The effect of it depends on the system configuration especially I/O sub system. Certainly it increases CPU time. Recently by mistake our production system was set up with 0 which resulted in a substantial CPU time with spikes of upto 60%. It actually means more frequent little write I/O rather than less frequent large I/O. A lot of people have different opinion on this. My take is, if the number of pages written per I/O is not [...] 3284 15 19_Re: TRACKMOD Change13_Lisa McClaine26_lisa.mcclaine@BLACKBOX.COM30_Fri, 8 Dec 2000 15:17:06 -0500339_iso-8859-1 Any idea where I can find out for sure if I need to stop/start for it to be effective?

================================================ 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. 3300 28 29_Optimizing processing threads9_Bob Smith29_rjs900@EMAIL1.DSS.STATE.VA.US30_Fri, 8 Dec 2000 15:59:06 -0500639_iso-8859-1 We are currently running DB2 V5, and this question is in regard to TSO batch processing. We have read that the DB2 optimizer creates single processing threads for larger partitions and then will combine relatively smaller partitions for single thread processing in an effort to optimize processing threads. We would like to size our partitions to avoid combining from happening, however the partitions grow at slightly different rates. Is there a rule regarding relative partition size ratio that the optimizer follows in determining what cases it will combine partitions for thread assignment? If so, what is it based on? [...] 3329 111 42_Re: FW: strange db2look problem - V6.1 AIX11_Jeremy Dodd21_jeremy.dodd@WHICH.NET30_Fri, 8 Dec 2000 18:36:45 +0000349_us-ascii We have found something similar. We recently upgraded from DB2 UDB 6.1 fixpaxk 3a ti fixpack 6. This changed the results we saw. Previously the -u flag returned tables in that schema. Now it returns tables created by that user. Technically I suppose that is what it says it will do - although I found the previous results more useful. [...] 3441 73 38_Re: strange db2look problem - V6.1 AIX0_22_BILL_GALLAGHER@PHL.COM30_Fri, 8 Dec 2000 09:25:29 -0500423_us-ascii Manas,

We experienced this problem when we upgraded from fixpack 2d to fixpack 5 in v6.1. IBM changed the behavior of the -u parameter so that it would look at the DEFINER of the tables rather than the schema (CREATOR). This change was introduced by APAR IY11255. The workaround is to have -u specify the ID of the person who actually created the tables, or to leave the -u parameter off all together. [...] 3515 63 23_Re: rounding the values0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Fri, 8 Dec 2000 15:39:05 -0600463_us-ascii If you're in V6, use the new scalar function ROUND It returns a number rounded to the specified number of places to the right or left of the decimal place.

HTH Shan







"Morrill, John" on 12/07/2000 11:17:56 PM





Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Shan Leatherman/MO/americancentury) Subject: rounding the values [...] 3579 27 22_QMF Governor Question;14_jack fernicola20_bowling730@YAHOO.COM30_Fri, 8 Dec 2000 14:10:05 -0800449_us-ascii Does anyone know if the QMF Governor monitors the governed resources under the same USERID from start to finish of a job or does it change the USERID each time the db2 AUTHID is changed by a SET CURRENT SQLID in the QMF query.

Thanks Jack

===== Jack Fernicola

__________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/ [...] 3607 54 37_Re: Insert from a different subsystem11_Brazil, Pat27_PBrazil@MIDATLANTIC.AAA.COM30_Fri, 8 Dec 2000 17:39:17 -0500432_iso-8859-1 Dave,

You are right. That's what I'm talking about. We have third party tools to do this, but many times, it would be a lot easier to just use the insert with the select.

Pat

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Seibert, Dave Sent: Friday, December 08, 2000 10:39 AM To: DB2-L@RYCI.COM Subject: Re: Insert from a different subsystem [...] 3662 12 13_insert_update8_Lo, Mary17_mary.lo@TGSLC.ORG30_Fri, 8 Dec 2000 16:49:08 -0600319_iso-8859-1 What is the OS/390's equivalent of UDB's IMPORT ... INSERT_UPDATE?

================================================ 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. 3675 27 26_Re: QMF Governor Question;11_Matt Kelley18_mgk333@HOTMAIL.COM30_Fri, 8 Dec 2000 17:18:49 -0600356_- >Does anyone know if the QMF Governor monitors the >governed resources under the same USERID from start to >finish of a job or does it change the USERID each time >the db2 AUTHID is changed by a SET CURRENT SQLID in >the QMF query.

Changes to your CURRENT SQLID after starting QMF will NOT change what resource group is used by the governor. [...] 3703 105 36_Re: rounding the values - even on V511_Suresh Sane21_data_arch@HOTMAIL.COM30_Fri, 8 Dec 2000 17:26:45 -0600632_- Rao - As Shan says it's real easy on V6. If you are still on V5 here is a possibility:

SELECT INTEGER((INTEGER(987.235476487 * 10000) + 5)/10)/1000.0 FROM SYSIBM.SYSDUMMY1;

--> returns 987.2350000000000

SELECT INTEGER((INTEGER(987.235576487 * 10000) + 5)/10)/1000.0 FROM SYSIBM.SYSDUMMY1;

--> returns 987.2360000000000

Your statement (rounding to 987.236) was not correct for the value specified.

If you need to round to x decimal places, you need to multiply by 10**x+1 and then divide by 10 and then by 10**x. Reason for the order of operations and for adding 5 should be obvious. [...] 3809 42 13_Re: IFCID 19814_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Fri, 8 Dec 2000 23:50:19 -0500523_ISO-8859-1 What do you mean by "fast sampling"? When the buffer mgr trace is turned on, you get every access and release. Why would you want to ignore sequential access? You would also have to know about I/Os,and prefetch activity since a random page could fall off the lru queue, be read in by a prefetch (and there's no way to know exactly what pages are read) and then be in the pool for a random access. Also, short samples don't provide for transaction/access stabilty, and can result in wild skewing of results. [...] 3852 64 38_Re: IBM DB2 Database UTILITY Questions11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Sat, 9 Dec 2000 00:58:24 -0500632_ISO-8859-1 Hi Andy:

A correction/clarification. COPY SHRLEVEL CHANGE does not take page or row L-locks. It is a 'dirty reader', effectively running ISOLATION(UR). This has been the case since Version 3. But maybe you were referring to P-Locks (see below).

Hi Paul:

Re the COPY CPU. Is this running on a Data Sharing member? If so it might be page P-Lock activity causing the high CPU. Check the Accounting Trace record for the COPY (plan DSNUTIL). On non-Data Sharing I would expect similar CPU times between COPY SHRLEVEL REFERENCE and SHRLEVEL CHANGE. (Unless of course you ran SHRLEVEL CHANGE first and [...] 3917 196 27_Re: Mad MAX goes again ....12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sat, 9 Dec 2000 13:43:05 +0200336_iso-8859-1 Hi, Let's take a look at the 4kb memory table. Assuming an INT field for the group by and another INT for the SUM you get 8 bytes. 4000/8=500 rows (ok, I cut the corner here). and if you have more fields on the group - it's even less. So , if you have more "groups" then that - you start to loose when you overlay them. [...] 4114 114 46_Connolly's Riposte to Seiberts Rambling Lament13_Marc Connolly24_marc.connolly@ORACLE.COM30_Sat, 9 Dec 2000 11:06:13 -05001274_us-ascii create table Connollys_Riposte_to_Seiberts_Rambling_Lament ( I_object_to_this_false_accusation_and_rightly_declaim NUMBER(3), This_venal_attempt_to_smear_and_defame NUMBER(3), For_i_am_not_a_twit_in_oracle_land NUMBER(3), Despite_those_who_in_angst_take_such_a_stand NUMBER(3),

A_sojourner_and_journeyman_i_am_i_maintain NUMBER(3), Steadfastly_working_or_prospecting_worthy_claim NUMBER(3),

But_in_whatever_nation_state_or_land_i_toil NUMBER(3), Be_that_db2_oracle_or_other_domain NUMBER(3), I_lend_my_strength_to_the_task_at_hand NUMBER(3), Quitely_plying_my_craft_so_grand NUMBER(3),

And_fight_petty_complaint_and_soulful_desire NUMBER(3), For_i_am_assured_at_the_end_of_days NUMBER(3), The_workman_is_worthy_his_hire NUMBER(3), );







Seibert's Lament ========================================================================= Date: Fri, 8 Dec 2000 06:30:19 -0500 From: "Seibert, Dave" Subject: Re: DB2 V7.1 18 char Limitation

I agree that we have to succumb to the SQL standard. I just wish someone with a little common sense had held sway in creating the standard. I have yet to hear a reasonable need for 128 characters in a name. Something longer than 18 makes sense to [...] 4229 60 33_Re: Optimizing processing threads12_Myron Miller22_myronwmiller@YAHOO.COM30_Sat, 9 Dec 2000 12:58:49 -0800336_us-ascii Ok. I'll byte. Why do you want to stop the optimizer from combining partitions? It picks what it thinks is the most efficient mechanism to have the request performed in the shortest total amount of time.

When it combines partitions, it does this to have each set of tasks complete in about the same amount of task. [...] 4290 21 25_Reporting off the DB2 Log11_Bruce James20_Bruce.James@UCOP.EDU30_Fri, 8 Dec 2000 17:27:49 -0800544_us-ascii Hello List,

Has anyone had experience doing any kind of reporting from the DB2 Log? We'd be interested in any products from IBM, BMC, Platinum, etc. that might provide the means for us to extract audit information from the Log.

Thanks,

Bruce James University of California

================================================ 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. 4312 39 14_ABEND-00D9602111_patty rizzi19_patty_db2@YAHOO.COM31_Sun, 10 Dec 2000 04:14:27 -0800374_us-ascii He all,

I have a big problem. In our shop we are at V5 . Today , after IPL, we had an error 00D96021 the DB2 can't start. Because the error said that a certain RBA 'NOT IN ANY 259' I tried a cold start crestart create,backout no

but it didn't work. I read better the message and I notice that it could not do : PHASE 3 Forward so I tried again : [...] 4352 62 87_Re: SOLVED! attempting to install fixpacks on UDB NT 6.1 but prob lem with winlogon.exe13_Lynne Flatley17_LFlatley@NEFN.COM31_Sun, 10 Dec 2000 12:25:51 -0500609_us-ascii Thanks Gert! This worked like a charm!



Lynne A. Flatley New England Financial (617) 578-4079 (work) (781) 718-0919 (cell) lflatley@nefn.com



> -----Original Message----- > From: Gert van der Kooij [SMTP:geko@WANADOO.NL] > Sent: Tuesday, December 05, 2000 4:17 PM > To: DB2-L@RYCI.COM > Subject: Re: attempting to install fixpacks on UDB NT 6.1 but problem > with win logon > > Hi, > You need to check all monitoring tools also. If you can't get it to work > you > can do the following: > > - stop all DB2 related services and change the startuptype to manually; > - [...] 4415 96 17_Re: insert_update11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU31_Mon, 11 Dec 2000 10:31:37 +1100563_- Mary,

I am afraid there is no OS/390 equivalent.

Cheers, Ulrich

> -----Original Message----- > From: Lo, Mary [SMTP:mary.lo@TGSLC.ORG] > Sent: Saturday, December 09, 2000 9:49 AM > To: DB2-L@RYCI.COM > Subject: insert_update > > What is the OS/390's equivalent of UDB's IMPORT ... INSERT_UPDATE? > > ================================================ > 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. 4512 55 29_AW: Reporting off the DB2 Log12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Mon, 11 Dec 2000 08:06:56 +0100313_iso-8859-1 Hi Bruce,

The DB2 Command -DISPLAY LOG gives you some information about the log status. For more details: I think, Platinum Log Analyzer should be able to extract the desired info. Our Platinum docu is not available at the moment, so I can only "route" you to CA the home page (www.ca.com) [...] 4568 18 15_DB2 Replication19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Mon, 11 Dec 2000 09:38:28 +0200480_iso-8859-7 Hi all, Db2 replication is transactional or snapshot? When a transaction is committed on the source database this transaction is also applied to target or data that affected is copied to replica database?

Thanks Dimitris

================================================ 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. 4587 47 29_Re: Reporting off the DB2 Log15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Mon, 11 Dec 2000 03:13:38 -0600536_iso-8859-1 BMC's Log Master(TM) for DB2 will give you pretty much anything you can get off the log in terms of reporting/auditing plus much more in terms of SQL 'regeneration' for multiple puroposes. Of particular interest for your requirement could be the ability to reformat/decompress/decode log data into a 'logical log' that can much more easily be processed by user programs for specialised reporting purposes. Please see http://www.bmc.com/products/proddocview.html?id=2097&r=Americas&l=en [...] 4635 46 29_Re: Reporting off the DB2 Log16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 11 Dec 2000 07:33:30 -0500350_iso-8859-1 We have been happy with the Platinum Log Analyzer. A CA salescritter is coming to our site tomorrow to pitch some sort of CA replacement for this.

If it is like in the past, when CA acquires two products which do the same thing, it discards the better one (usually).

regards,

eric pearson NS ITO Database Support [...] 4682 29 9_Deadlocks12_Dauncey, Ian18_idauncey@FNB.CO.ZA31_Mon, 11 Dec 2000 14:43:19 +0200304_iso-8859-1 Hi All,

If you have a deadlock and you run a snapshot on locks, part of the output gives you the number of locks held, the ID of the agent holding the lock plus other info. Also it gives you the Object Name, Object Type, Tablespace Name, Table Schema, Table Name, Mode and Status. [...] 4712 29 15_ADO and Delete.10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Mon, 11 Dec 2000 17:03:15 +0330526_US-ASCII Hi everybody,

I tried to delete a record using ADO in an VB environment and I recieved the following error from DB2 :

[IBM][CLI Driver][DB@/NT]SQL0204N "DB2ADMIN.ado_test" is an undefined name

I'm sure that DB2ADMIN.ado_test is a valid table(name) as I do a SELECT on it. Who knows why?

By the way I want to audit all select/update/insert/delete queries done on a specsific table (specially from my VB project) who can i have it without so many extra informations. Who can I do that? [...] 4742 21 50_Re: Connolly's Riposte to Seiberts Rambling Lament10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 11 Dec 2000 15:14:55 +0100482_us-ascii Hi Dave. As my grandfather said:

'Someone needs to enlarge some useless things to compensate the small size of other things they have, more & more

useful & important.......'

Vade repro Oracle.

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. 4764 35 50_Re: Connolly's Riposte to Seiberts Rambling Lament13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 11 Dec 2000 09:32:59 -0500314_iso-8859-1 Hi Marc, Nice, clever piece of work.

I am glad to see none of your names exceeds (or approaches) 128 characters.

Thanks for the polite scolding.

I certainly did not intend to imply that all in Oracle_land are twits. I have to admit I spent a year over on the dark side myself. [...] 4800 38 38_Re: IBM DB2 Database UTILITY Questions15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Mon, 11 Dec 2000 08:47:38 -0600421_iso-8859-1 Mike,

You are of course quite right (old versions linger in the memory). You were more than generous in assuming I meant data-sharing P-Locks! However I believe that my point about ISV products avoiding all locks by reading the space directly stands - the only interference with the DB2 subsystem is at the disk/controller level apart from any necessary catalog read, QUIESCE and SYSCOPY activity. [...] 4839 68 29_Re: Reporting off the DB2 Log14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 11 Dec 2000 14:45:58 -0000536_iso-8859-1 Just to confirm some possibly impending confusion

The CA Log Analyzer IS the ex-PLATINUM Log Analyzer and is still alive and well.

There are NO current plans to change the status of Log Analyzer in any way.

Just in case anyone was wondering!!

Phil Grainger Product Manager, DB2 Products Computer Associates

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: 11 December 2000 12:34 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Reporting off the DB2 Log [...] 4908 92 29_Re: Reporting off the DB2 Log16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 11 Dec 2000 09:52:54 -0500474_iso-8859-1 Interesting. I wonder what the rep is going to pitch?

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Monday, December 11, 2000 9:46 AM To: DB2-L@RYCI.COM Subject: Re: Reporting off the DB2 Log



Just to confirm some possibly impending confusion

The CA Log Analyzer IS the ex-PLATINUM Log Analyzer and is still alive and well. [...] 5001 132 31_Re: Date fields defined as NULL12_Randy Wilson22_Randy_Wilson@BCBST.COM31_Mon, 11 Dec 2000 10:10:22 -0500615_iso-8859-1 Thanx Ulrich! That did the trick. Have a great day :o) Randy

-----Original Message----- From: KUHN,Ulrich [mailto:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] Sent: Thursday, December 07, 2000 10:04 PM To: DB2-L@RYCI.COM Subject: Re: Date fields defined as NULL





Randy,

you need to include the "NULL INDICATORS" clause. I had always assumed that the positions you identify for your null indicators had to be different from all your actual data, but it looks like you can "overlay" null indicators, so assuming your ninth field is the problem date, the clause would be: [...] 5134 18 29_Re: Reporting off the DB2 Log11_Matt Kelley18_mgk333@HOTMAIL.COM31_Mon, 11 Dec 2000 09:11:46 -0600489_- On Fri, 8 Dec 2000 17:27:49 -0800, Bruce James wrote:

>Has anyone had experience doing any kind of reporting from the DB2 Log? >We'd be interested in any products from IBM, BMC, Platinum, etc. that might >provide the means for us to extract audit information from the Log.

In case you aren't aware of it, the announcement letter for the new IBM DB2 Log Analysis Tool (among other tools) is at http://www.ibmlink.ibm.com/usalets&parms=H_200-302. [...] 5153 17 50_Re: Connolly's Riposte to Seiberts Rambling Lament10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 11 Dec 2000 16:27:06 +0100402_us-ascii Hi Dave.

I was for one year on 'the dark side of the force ', so I can understand differences between the 2 main DBRMS in the

market. And

================================================ 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. 5171 47 60_Visual Basic calling SP with variable length input parameter23_Sercombe, George W A13325_George.Sercombe@CIGNA.COM31_Mon, 11 Dec 2000 10:30:20 -0500590_iso-8859-1 Fellow DB2'ers

Does anyone have an example of a Visual Basic script calling a mainframe DB2 Stored Procedure with a Variable Length input parameter (VARCHAR(14000) in the SP DML create statement:

CREATE PROCEDURE SYSPROC.GXAMI008 (IN VARCHAR(14000),OUT CHAR(40)) LANGUAGE COBOL EXTERNAL NAME GXAMI008 PARAMETER STYLE GENERAL NO WLM ENVIRONMENT COLLID XAM_SP ASUTIME LIMIT 60000 RESULT SETS 0 EXTERNAL SECURITY DB2 COMMIT ON RETURN NO CALLED ON NULL INPUT NO DBINFO STAY RESIDENT NO RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),LIBS(4K,,),ALL31(ON)'; [...] 5219 65 29_Re: Reporting off the DB2 Log9_Bob Lewis23_BLewis@MICROGENERAL.COM31_Mon, 11 Dec 2000 08:31:40 -0800716_iso-8859-1 Does anyone have a comparable product to report from the non-OS/390 DB2 logs?

Bob Lewis Mercenary Contractor DBA

-----Original Message----- From: Lankester, Andy [mailto:Andy_Lankester@BMC.COM] Sent: Monday, December 11, 2000 3:14 AM To: DB2-L@RYCI.COM Subject: Re: Reporting off the DB2 Log



BMC's Log Master(TM) for DB2 will give you pretty much anything you can get off the log in terms of reporting/auditing plus much more in terms of SQL 'regeneration' for multiple puroposes. Of particular interest for your requirement could be the ability to reformat/decompress/decode log data into a 'logical log' that can much more easily be processed [...] 5285 129 18_Re: ABEND-00D9602116_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Mon, 11 Dec 2000 17:51:56 +0100373_us-ascii Patrizia,

apparently this could happen in one of two situations:

- DB2 archive logs or BSDS or pagesets are allocated on extended format datasets (compressed) - which thing your subsystem will not like, or

- this could occur if you have 2 DB2's sharing the same BSDS running concurrently (see APAR PL26125 - very old - for more details). [...] 5415 17 37_Re: Insert from a different subsystem12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 11 Dec 2000 10:54:24 -0600392_- Right now, only the DataJoiner function does this job for us. Providing a multiple site unit of work is a major effort. It's not too hard until we build it with the expected robustness, reliability and performance. The error paths are probably 80% or 90% of the work. When we ask customers to prioritize the top 100 or so items, this one comes out lower than the work which gets done. [...] 5433 30 58_Compatibilty of DB2 UDB 5.2 on AIX vs DB2 UDB V6 for OS3909_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Mon, 11 Dec 2000 09:47:58 -0700350_iso-8859-1 Fellow Listers,

We are preparing to install DB2 Version 6 for OS390 for one of our customers. My question is that they currently have DB2 UDB and DB2 Connect Version 5.2 running on an AIX platform the processes data from the DB2 Version 5 on the OS390 mainframe and if we upgrade to DB2 Version 6 will there be any problems ?? [...] 5464 31 20_SHARD, ESS Questions0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 11 Dec 2000 11:08:41 -0600454_us-ascii Our service provider is moving us to SHARK DASD in the new year. I have a couple of questions that still need answering.

1. Does anyone have the current list purchase price for 500 - 1000 gb on SHARK. I realize that final price can vary greatly (down from list) depending on circumstances, I just would like List price as a starting reference point. I suspect our contracted DASD costs are based on much older, expensive technology. [...] 5496 38 55_Pipeline Newsletter for DB2 and Oracle - December Issue9_Cam White20_cwhite@REVEALNET.COM31_Mon, 11 Dec 2000 12:13:18 -0500456_iso-8859-1 The December, 2000 Issue of the Pipeline Newsletter is now available for Oracle and DB2 professionals.

http://www.revealnet.com/newsletter/newsletter_1200.htm

Feature articles include:

- "Oracle Database Cloning Using Hot Backups" - by Shankar Govindan - "LOB Columns vs. VARCHAR & VARGRAPHIC" - by Craig Mullins - "Top Tips for Designer 6.0", by Peter Koletzke - "Digging Deep Into STANDARD.SQL" - by Solomon Yakobson [...] 5535 23 16_Check Constraint13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Mon, 11 Dec 2000 11:13:26 -0600428_- When using more than one check constraint in a single table.... for example....one on Sex Code and one on Marital Status.....

If you get an SQLCODE that indicates a check constraint has been violated, is there a way to determine (other than interrogating the SQLERR text message) which specific column's constraint was violated? I notice with triggers, you can set an SQLSTATE....but what about Check Constraints? [...] 5559 35 20_Re: Check Constraint9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 11 Dec 2000 10:28:32 -0700573_us-ascii Each constraint will have a unique name, this name will be part of the error message so if you can make meaning out of the 8 character constraint name you should be able to tell.

Shauna Hadden wrote: > > When using more than one check constraint in a single table.... for > example....one on Sex Code and one on Marital Status..... > > If you get an SQLCODE that indicates a check constraint has been violated, > is there a way to determine (other than interrogating the SQLERR text > message) which specific column's constraint was violated? I notice [...] 5595 67 24_Re: Sas-Db2 connectivity13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Mon, 11 Dec 2000 12:46:42 -0500336_US-ASCII Hi Kumar,

You will need to verify that in addition to Base SAS, you have SAS/Access to DB2 and SAS/Connect for DB2 installed. Otherwise, you need to contact your SAS Rep to buy it. If you do have these products installed, you can connect through Poc SQL (see example below).

I hope this helps, Burgess Evans [...] 5663 27 35_DB2 for OS/390 V7 Migration Webinar12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 11 Dec 2000 11:45:01 -0600385_- I'll be presenting a talk on V7 migration Thursday. The charts and notes appear to be out there now.

DB2 Version 7 - Migration Considerations from V5 or V6

Thursday December 14, 2000 at 10:00 am PT/ 11:00 am MT/12:00 pm CT/1:00 pm ET Duration: 60 min

Here is the URL.

http://www.lightyr.com/ Then click on the TechTalk Conference Calls button or try [...] 5691 91 24_Re: SHARD, ESS Questions16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Mon, 11 Dec 2000 19:13:55 +0100415_us-ascii Rohn,

I guess somebody on the list might remember that my previous assignment had ordered Flashcopy for ESS/Shark, and I told the list I would keep those interested informed on how it went.

well I didn't and I shall not, it is still not available despite loads of announcements (which I duly relaid to the customer greeted by a chorus of "yeah yeah yeah" towards the end :-P ) etc. etc. [...] 5783 123 24_Re: SHARD, ESS Questions0_20_John_Lendman@FPL.COM31_Mon, 11 Dec 2000 13:28:50 -0500425_us-ascii I am not sure the question, but we are using Flashcopy on ESS. We replaced all of our SNAPCOPY of RVA's.

John Florida Power and LIght





"Aurora Dell'Anno" cc: Sent by: "DB2 Data Subject: Re: SHARD, ESS Base Discussion Questions List"



12/11/00 01:13 PM Please respond to "DB2 Data Base Discussion List" [...] 5907 105 24_Re: SHARD, ESS Questions0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 11 Dec 2000 13:56:50 -0600569_us-ascii Question #2 ... as I understand, Flashcopy currently only does disk level backups, but as much of it is a logical copy, the process is very quick. And at some point in the (near?) future, a dataset (Tablespace) level Flashcopy feature will be available.

Currently a disk level backup will not work with our Disaster Recovery procedure, since we recover a very limited subset of production at the DR site (long story . . .). So I need to be able to do tablespace image copies. As an alternative to online image copy, which is too much of a 'radical [...] 6013 37 17_recovery question13_Judi Westcott23_judi_westcott@YAHOO.COM31_Mon, 11 Dec 2000 14:12:58 -0600336_- Hello all

A question has arisen in our shop and we are not sure what is a good way to protect our data/database.

We have Table_A and are using Platinum tools to insert a column in the middle of existing columns.

Platinum generates a fastunload, a DROP TABLE, a CREATE TABLE with new column, then a fastload. [...] 6051 73 21_Re: recovery question9_Sally Mir22_Sally.Mir@WACHOVIA.COM31_Mon, 11 Dec 2000 15:30:46 -0500407_us-ascii Judi,

With Platinum RC/Migrator, you can specify that a Recovery job be created before running your strategy to drop and recreate your objects. It will save the DDL reflecting the 'old' object, and produce an unload file of the data. Then, if you ever need to recover to the pre-change version of your object, you just run that Recovery job, which will put it back just the way it was. [...] 6125 61 21_Re: recovery question24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 11 Dec 2000 12:33:02 -0800320_- The simplest way might be to change the DROP TABLE to a RENAME TABLE (DB2 version 5). The only thing to watch for is space, since the tablespace will contain both the old, and the new table. Once you're sure of the new table, you can then DROP the old renamed table and reorg the tablespace to reclaim the space. [...] 6187 35 25_DB2 V6 Log Suspend/Resume11_Lorenz, Don15_dlorenz@KCC.COM31_Mon, 11 Dec 2000 15:07:48 -0600423_iso-8859-1 Hi List,

We are using DB2's Log Suspend/Log Resume to take quick backups, using Timefinder. We have run into a problem with our present scheduling software (another problem), where the Suspend works, but the Resume hangs. For this application, we are OS390 DB2 V6, SAP R/3. Note: with the amount of activity and objects on our SAP R/3 application, image copies and a quiesce are not a viable option. [...] 6223 38 33_DPROPR implementation experiences17_Aldrin Jayatilaka19_aldrinj@AU1.IBM.COM31_Tue, 12 Dec 2000 08:13:34 +1100496_us-ascii I'm interested to hear from anyone who is using DPROPR to maintain a secondary copy of a DB2 operational database.

Key issues:

DPROPR's effectiveness to transfer changed data from a high volume, highly volatile and high available, very large operational database environment. Re-synchronisation issues when loads/database structure changes are performed at the source environment. The amount of on-going support required to maintain the process (after initial set-up) [...] 6262 42 19_Re: DB2 Replication13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Mon, 11 Dec 2000 13:17:11 -0800331_iso-8859-1 Replication is transactional. The change data table (containing 'all' the changed data) and UOW table (containing only the unit of works which have been committed at the source server) are joined together to finally arrive at the 'changed committed' data at the source and this is then applied to the target table. [...] 6305 48 20_Re: Check Constraint13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Mon, 11 Dec 2000 15:26:57 -0600588_- I already knew of that method, but the programmers would prefer not to have to "fish" for the column or constraint name in the error text....their words not mine.



On Mon, 11 Dec 2000 10:28:32 -0700, Rob Crane wrote:

>Each constraint will have a unique name, this name will be part of the >error message so if you can make meaning out of the 8 character >constraint name you should be able to tell. > >Shauna Hadden wrote: >> >> When using more than one check constraint in a single table.... for >> example....one on Sex Code and one [...] 6354 43 29_Re: Reporting off the DB2 Log12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Mon, 11 Dec 2000 16:34:38 -0500645_- Bruce,

We use the BMC Log Master product extensively to do anything from audit reporting, to building 'undo SQL', to building 'migrate' SQL for pseudo replication to another database. These functions are used extensively in both test and production scenarios.

Lynda



> -----Original Message----- > From: Bruce James [SMTP:Bruce.James@UCOP.EDU] > Sent: Friday, December 08, 2000 8:28 PM > To: DB2-L@RYCI.COM > Subject: Reporting off the DB2 Log > > Hello List, > > Has anyone had experience doing any kind of reporting from the DB2 Log? > We'd be interested in any products from IBM, BMC, Platinum, etc. that > [...] 6398 40 35_Peoplesoft calling stored procedure12_Kirk Hampton16_khampto1@TXU.COM31_Mon, 11 Dec 2000 15:40:36 -0600620_us-ascii We have Peoplesoft Financials 7.5 on Windows front end accessing DB2 v6.1 for OS/390 v2.8 as the backend via DB2 Connect. Currently, the only stored procedures we run were written to access this Peoplesoft database. I seem to remember the original developers, who of course are long gone, had trouble with various Windows-based languages not being able to successfully call an OS/390 DB2 stored procedure, or not being able to access the result variables after the call. So they devised an interface layer using MicroFocus COBOL, which made a static SQL call to the stored procedure and reviewed the resut [...] 6439 67 39_Re: Peoplesoft calling stored procedure12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 11 Dec 2000 14:51:49 -0700385_ISO-8859-1 Kirk- We have a very similar set up to you in our shop and yes our developers tell me that we currently call db2 stored procedures from peoplecode. They are some limitations with this (for instance not being able to pass parameters-so they have coded a work around). If you would like additional information feel free to e-mail me offline with your specific questions. [...] 6507 69 29_Re: DB2 V6 Log Suspend/Resume14_Richard Bowles23_RBowles@DOA.STATE.LA.US31_Mon, 11 Dec 2000 15:53:23 -0600559_iso-8859-1 We are also using Log Suspend/Log Resume for our SAP backups. We are running OS390 V2R8, DB2 V6R1, SAP R/3 4.6B and TimeFinder. Our scheduler is the Altai (or is that CA) ZEKE product. We schedule a batch job, composed of five steps, twice daily. Step 1 forces a DB2 checkpoint (SET LOGLOAD 0), Step 2 issues the SET LOG SUSPEND command, Step 3 dumps the SAP instance ICFCAT to a TimeFinder volume, Step 4 issues the TimeFinder 'SPLIT' commands for that SAP instances volumes (we also do a before and after QUERY in case we need to track down [...] 6577 49 28_DB2 Connect - ODBC - VB6 ADO14_Adrian Collett27_adriancollett@TISCALINET.IT31_Mon, 11 Dec 2000 23:03:15 +0100426_iso-8859-1 Dear List,

I have a client who is lucky enough to have a VB6 application using ADO, MTS, OLE DB, and DB2 Connect to get to DB2/OS390 V5 - :)

By using the ODBC trace function of Db2 connect I have noticed the following strange ODBC function calls:-

SQLNativeSql, SQLPrimaryKeys, SQLStatistics

All of which cause unnecessary network traffic, and accesses to the DB2 catalog on OS390. [...] 6627 35 23_Db2 ODBC Function Calls14_Adrian Collett27_adriancollett@TISCALINET.IT31_Mon, 11 Dec 2000 23:03:18 +0100360_iso-8859-1 Dear List,

Further to my recent message about DB2 ODBC function calls and VB6;

I have also noticed that sometimes some of the ODBC function calls, namely SQLNativeSql, SQLPrimaryKeys, SQLStatistics , are executed as static SQL via a DB2 connect program SQLC76Y(I think), whereas some other times they are executed as dynamic SQL. [...] 6663 55 20_Re: Check Constraint15_Keith Phimister31_keith.phimister@VIRGINNET.CO.UK31_Mon, 11 Dec 2000 22:08:33 -0000381_iso-8859-1 Building on that, remember that you can specify your own constraint name when creating them - this means you can provide your own naming convention for them. Keith

----- Original Message ----- From: "Rob Crane" Newsgroups: bit.listserv.db2-l To: Sent: Monday, December 11, 2000 5:28 PM Subject: Re: Check Constraint [...] 6719 56 27_Re: Db2 ODBC Function Calls15_Keith Phimister31_keith.phimister@VIRGINNET.CO.UK31_Mon, 11 Dec 2000 22:14:58 -0000365_iso-8859-1 Have you tried using the same SQL through RDO instead and tracing the ODBC calls. That way you might find out whether it is an OLE DB "feature" or an ODBC one. Obviously this is not always possible but you might be able to create a little test system (as long as you have VB6 Enterprise, Professional will not develop or compile RDO apps) HTH Keith [...] 6776 85 20_Re: Check Constraint12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Mon, 11 Dec 2000 16:30:54 -0600458_us-ascii Now to combine two threads:

While referential constraint names are limited to 8 bytes, table check constraint names can extend to 18 bytes!

This is one place where long names might be an advantage.

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

Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098 [...] 6862 112 32_Re: DB2 Connect - ODBC - VB6 ADO13_Andy Seuffert21_aseuffert@NEONSYS.COM31_Mon, 11 Dec 2000 16:30:53 -0600642_us-ascii Andy Seuffert@NEON 12/11/2000 04:30 PM

Adrian, I have seen this a lot working with our customers using our ODBC driver for DB2, Shadow Direct. What is happening is that the ADO application is creating an "updateable" result set. When this occurs, ADO issues several ODBC function calls like the ones you described (SQLStatistics, SQLPrimaryKeys) in order to get back the index information for the underlying DB2 table. Then if the VB application makes an update to the underlying result set returned, ADO has enough information to dynamically create it's own update or Insert statement (SQL) to update the underlying DB2 [...] 6975 19 37_Re: Insert from a different subsystem15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Tue, 12 Dec 2000 10:24:17 +1100307_- You could also use QMF on the target subsystem to query the source table with a 3 part name then use QMF command 'SAVE DATA AS' to populate the target table using a 2 part name (assuming the target table exists, otherwise QMF may store the data in the default Tablespace, which can be inconvenient ) [...] 6995 20 45_ORACLE Financials - is there a DB2 Equivalent10_Rob Wright17_rwright@LIC.CO.NZ31_Tue, 12 Dec 2000 12:36:28 +1200300_us-ascii Hi

We have ORACLE Financials running on a SUN box. Without using Oracle on OS/390, is there an equivalent product for DB2 that could (seamlessly) replace Oracle Financials? If so, do you use it, did you migrate to it, and how much of an effort was it to do so?

Thanks Rob [...] 7016 94 19_Re: DB2 Replication0_31_greg.palgrave@UNISYSWEST.COM.AU31_Tue, 12 Dec 2000 08:38:06 +0800390_us-ascii Also be aware that the replicated data is not necessarily inserted/updated in the same table sequence as the source.

If you have RI in your source tables, you may get occasions when the replication tries to insert a row in a child table before it has inserted the row in the parent table - which results in an error, and your apply process gets a bit out of sorts..... [...] 7111 62 49_Re: ORACLE Financials - is there a DB2 Equivalent12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Tue, 12 Dec 2000 12:07:43 +1100575_- SAP? PeopleSoft (not sure if they are in the financials space though)?

Although I don't know about the seamless bit! But if you "courted" IBM and SAP / PeopleSoft I'm sure they'd be more than helpful at telling you how good it would be. The last time I was near SAP was SAP/IMS (DB&DC - it was still DC 6 years ago), I was impressed with its technical sophistication in parts (and the level of support), but it was definitely not "plug-n-play" and it struck me as a bit clunky and not particularly social with interfacing applications, but [...] 7174 73 31_DB2 v.2 to DB2 v.7.1 on RS/600011_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Tue, 12 Dec 2000 08:30:09 -0800419_us-ascii Dear all,

We are trying to upgrade DB2 v.2 to DB2 7.1. EE on RS/6000, but our main problem is that none of us know anything about RS/6000 and DB2 v.2 What are the requirements that we need to know ? Is there any specific problem that we would encounter and should beware of ? If anyone can tell us where to find all the necessary information, or even steps, we would be very thankful. Best Regards [...] 7248 116 24_Re: Sas-Db2 connectivity12_Kerry Hubert17_khubert@LIC.CO.NZ31_Tue, 12 Dec 2000 17:10:16 +1200287_us-ascii Kumar (& Burgess, )

We are just implementing DB2 (on OS/390) & have used SAS for our end-user ( & a bit of production) systems for many years on our previous Database. We have a just installed SAS801 and also have a 'trial' version of SAS ACCESS FOR DB2 (from SAS). [...] 7365 30 15_ADO and Delete.10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Tue, 12 Dec 2000 10:12:21 +0330408_US-ASCII Hi everybody,

I tried to delete a record using ADO by VB environment and I recieved the following error from DB2-UDB :

[IBM][CLI Driver][DB@/NT]SQL0204N "DB2ADMIN.ado_test" is an undefined name

I'm sure that DB2ADMIN.ado_test is a valid table(name) as I do a SELECT on it and I could open the ado recordset by it. Who knows why this problem happens and how can I solve it? [...] 7396 30 3_DB210_laura king22_laura_king81@YAHOO.COM31_Mon, 11 Dec 2000 23:08:12 -0800375_us-ascii Hi Guys,

I am new here. For my next job, I am preparing DB2 UDB 7.0 Family Fundamentals Test 512. I have the book by Baklarz and Wong. I am a little confused because the book is for both Test 512 and Test 513, and the objective for the test is not very clear. I really appreciate if any body can give some suggestions about the preparation and the exam. [...] 7427 148 32_Re: DB2 Connect - ODBC - VB6 ADO14_Adrian Collett27_adriancollett@TISCALINET.IT31_Tue, 12 Dec 2000 08:42:47 +0100666_iso-8859-1 Thanks very much Andy - fantastic !!

However, your answers prompt more questions!

1) Are you saying that if the underlying result set is modified by the user then ADO will automatically generate its own Update/Insert/delete statements and issue them via normal SQLExecDirect function calls ? If so, does this make programming the application much quicker/reliable ? 2) In general what are the advantages/disadvantages of using adLockReadOnly - what I really mean is what are the advantages/disadvantages AND IMPLICATIONS for ODBC/DB2 ? 3) Your answer didn't mention the ODBC function call SQLNativeSql - we are seeing that this is also [...] 7576 77 27_Re: Db2 ODBC Function Calls14_Adrian Collett27_adriancollett@TISCALINET.IT31_Tue, 12 Dec 2000 08:42:51 +0100273_iso-8859-1 Keyth, Thanks for your reply - unfortunately I am "MS challenged" and wouldn't know an RDO from an ADO if it hit me in the face. I will try and ask the company who is creating the application - however, they seem to be intent on directing the blame on DB2. [...] 7654 131 21_Re: recovery question15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 12 Dec 2000 08:57:07 +0100365_iso-8859-1 Judi,

Also be aware of the authorsations you might have granted on the table and the check constraints as well. The binding and rebinding of plans/packages related to the table or it's views. To put in my two cents, with BMC Change manager you won't miss a stroke. The produkt gives you the availability to take a baseline which captures the [...] 7786 35 24_Re: SHARD, ESS Questions10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 12 Dec 2000 08:55:33 +0100415_us-ascii Hi Rohn, as now I'm involved in storage management for DB2, I can say that:

1) You're right, the final price strongly depends on how many Gb you buy, how big is your company (for future DASDs) and it depends on some other mysterious (more or less) commercial factors. For instance we had a very good offer from Hitachi for the latest storage device. The list price is a 'false' starting point. [...] 7822 61 19_Re: DB2 Replication19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Tue, 12 Dec 2000 10:05:06 +0200702_iso-8859-1 Hi, Do you mean that the committed transactions in the source database then is applied to the replica database or the 'changed committed' data are copied to replica database.

Thanks

-----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Monday, December 11, 2000 11:17 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Replication



Replication is transactional. The change data table (containing 'all' the changed data) and UOW table (containing only the unit of works which have been committed at the source server) are joined together to finally arrive at the 'changed committed' data at the source and this is then applied to the target [...] 7884 27 18_Language Interface14_Kenneth Larsen10_khl@PBS.DK31_Tue, 12 Dec 2000 02:17:08 -0600292_- We are compiling our Cobol programs with the DYNAM compiler option. This way we can use the same modules under TSO & IMS, as long as we concatenate the correct load library, with the correct Language Interface first. Now we would like to call the same modules from a Stored Procedure. [...] 7912 44 53_3rd DB2 Symposium in the Netherlands 14-16 March 200111_Klaas Brant14_KBrant@KBCE.NL31_Tue, 12 Dec 2000 09:34:11 +0100636_iso-8859-1 Dear DB2 professionals in Europe,

At the roots of DB2 Symposium: Veldhoven in The Netherlands we will have our 3rd DB2 Symposium in this area. Date: 14-16 March 2001. Your teachers will be: Susan Lawson, Joyce Bischoff, Richard Yevich, Jan Henderyckx and Klaas Brant.

We have 6 new sessions for you and we kept the 3 best sessions from last year in case you missed them. There are sessions on Performance, Java, Triggers/Stored Procedures, Application and Database Design, SQL and.... Data Warehousing. In fact the Data Warehouse track is completely data base independent. So if you want to implement your [...] 7957 68 21_Re: recovery question10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 12 Dec 2000 09:41:05 +0100605_us-ascii Hi Judi, see comments below.....:-))

We have Table_A and are using Platinum tools to insert a column in the middle of existing columns.

*** In a relational database the position of the column doesn't matter, unless you have varcharz or NOT NULL with no default (if I remember it correctly).

Platinum generates a fastunload, a DROP TABLE, a CREATE TABLE with new column, then a fastload.Our recent understanding is that if we take an IMAGCOPY before the drop table we do not have a copy of the DB2 catalog information (OBID, etc) required to recover the table if we need [...] 8026 149 29_Re: DB2 V6 Log Suspend/Resume0_29_paul.packham@POSTOFFICE.CO.UK31_Tue, 12 Dec 2000 09:22:52 +0000581_us-ascii Richard,

We have a similar environment to yourselves & we are using Timefinder to do our SAP backups, however in our case rather than suspending log writes we actually shut down the application cleanly. The reason for this is that prior to implementing the Timefinder solution we had not fully tested the 'suspend log write solution' and our application was able to take the hit is being stopped for a minute of so anyway. Our intention is to fully test the suspend log write solution and implement in the future, however you might want to be aware of SAP OSS [...] 8176 56 7_AW: DB212_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Tue, 12 Dec 2000 10:59:08 +0100623_iso-8859-1 Hi Laura,

already had a look at http://www-4.ibm.com/software/data/db2/selfstudy/ ?

Regards



Thomas

> -----Ursprüngliche Nachricht----- > Von: laura king [mailto:laura_king81@YAHOO.COM] > Gesendet am: Dienstag, 12. Dezember 2000 08:08 > An: DB2-L@RYCI.COM > Betreff: DB2 > > Hi Guys, > > I am new here. For my next job, I am preparing DB2 > UDB 7.0 Family Fundamentals Test 512. I have the book > by Baklarz and Wong. I am a little confused because > the book is for both Test 512 and Test 513, and the > objective for the test is not very clear. I really > appreciate if [...] 8233 176 24_Re: SHARD, ESS Questions16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Tue, 12 Dec 2000 11:39:12 +0100436_us-ascii John,

this is very interesting, can you please give me some details, like when did you do this, was it very recently, etc?

my ex-account keep on being told that it is still not available, so it would be interesting to hear your story (if you get my meaning)!!!

TIA.

Aurora Emanuela Dell'Anno Systems Programmer Sistemi Informativi S.p.A. e-mail: aurora.dellanno@sistinf.it tel. +39 335 7429486 [...] 8410 145 24_Re: Sas-Db2 connectivity13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Tue, 12 Dec 2000 07:52:37 -0500519_US-ASCII Hi Kerry,

Thank you for the suggestion. We just recently migrated to SAS801 and I have not had time to try this method yet.

Thank you for the suggestion, Burgess Evans

>>> khubert@LIC.CO.NZ 12/12/00 12:10AM >>> Kumar (& Burgess, )

We are just implementing DB2 (on OS/390) & have used SAS for our end-user ( & a bit of production) systems for many years on our previous Database. We have a just installed SAS801 and also have a 'trial' version of SAS ACCESS FOR DB2 (from SAS). [...] 8556 85 39_Re: Peoplesoft calling stored procedure15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 12 Dec 2000 10:04:50 -0500408_iso-8859-1 I had put the earlier question regarding accessing DB2 V5.1 stored procedure from powerbuilder. With many people's help we finally was able to call a db2 stored procedure from powerbuilder using DB2 connect. But we could not succeed in extracting SQLcode and Error message from DB2 stored procedure in a acceptable manner. This is because powerbuilder could not receive the output variables. [...] 8642 38 27_Site License for DB2 OS/39011_John Curran26_John.Curran@DRESDNERKB.COM31_Tue, 12 Dec 2000 15:19:16 -0000312_iso-8859-1 Hi

I know this subject has come up before but it would be very helpful if anybody could tell me how many DB2 OS/390 sites there are world wide, it would also be of interest to know if they are on the way up or down.

Thank you in advance

John Curran Dresdner Kleinwort Benson [...] 8681 198 29_Re: DB2 V6 Log Suspend/Resume14_Richard Bowles23_RBowles@DOA.STATE.LA.US31_Tue, 12 Dec 2000 10:08:33 -0600600_iso-8859-1 We had also been shutting all SAP instances down daily to ensure that we could cleanly backup the volumes, but the development team found that the down time was pushing project completion beyond the 'bonus' deadline and convinced management that they needed as close to 24x7 as possible. I managed to hold the line with a once a week shutdown/backup, and was aware that 32K pagesets could be a problem on restore. Thanks for the OSSNote. That explains it much better than the rumors of potential problems. The question that comes to mind, though, is how the TimeFinder Split function [...] 8880 129 28_Re: IMS and Stored Procedure16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Tue, 12 Dec 2000 17:01:14 +0100413_us-ascii Jacob,

from the same manual (admittedly, about 35 secs search time), I read that the COMMIT_ON_RETURN column must be set to "N" since "DB2 cannot commit work in a CICS or IMS environment".

also, it would cxxx-up your DB2 subsystem. quoting again:

"Do not include these SQL statements in your stored procedure:

CALL COMMIT CONNECT RELEASE SET CONNECTION SET CURRENT SQLID [...] 9010 220 32_Re: DB2 Connect - ODBC - VB6 ADO13_Andy Seuffert21_aseuffert@NEONSYS.COM31_Tue, 12 Dec 2000 10:23:11 -0600605_us-ascii Andy Seuffert@NEON 12/12/2000 10:23 AM

Adrian, in regards to your questions:

1) Yes, if the underlying VB object which contains the result set is updated and that result is marked as updateable, then ADO will generate its own SQL and issue an SQLExecDirect call to update the underlying DB2 table. Yes, in some regards this does make programming a client-server application easier, since you do not have to intercept updates by the user of the app and generate your own update or insert SQL statements. By allowing the user of the app to simply update the result set, then ADO [...] 9231 13 0_14_Picou, Michael33_picou.michael@MAIL.DC.STATE.FL.US31_Tue, 12 Dec 2000 11:36:54 -0500459_iso-8859-1 During disaster recovery, - after a recovery of the DB2 directory and a rebuild of the indexes - DSNSCT01, DSNSPT01, DSNLUX01, DSNLUX02, DSNLLX01, DSNLLX02 these indexes are in ICOPY status. Is this normal?

================================================ 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. 9245 21 26_DB2 Connect V7.1 stability13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Tue, 12 Dec 2000 10:54:02 -0600499_iso-8859-1 Anyone using DB2 Connect v7.1 clients and servers in production, either AIX or NT?

Are you satisfied as to its stability?

Are you using the sysplex option, or connection concentrator?



TIA for the feedback!

Dave

================================================ 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. 9267 85 22_Re: Language Interface0_19_Tim.Lowe@STPAUL.COM31_Tue, 12 Dec 2000 10:27:56 -0600357_us-ascii Kenneth, We currently support the use of the DYNAM compiler option in the DB2 stored procedure environment by creating a copy of the proper language interface module(DSNALI or DSNRLI depending on use of WLM) with an alias of DSNHLI. This subject has been discussed extensively in the past on this list, you may want to look into the archives. [...] 9353 87 0_0_18_mebert@AMADEUS.NET31_Tue, 12 Dec 2000 18:16:34 +0100302_us-ascii I have never encountered this on DB2 V5, so I looked in the V6 docs. In the V6 Utility Guide (REBUILD INDEX) it says that REBUILD places indexes in Informational COPY Pending status (I guess that's ICOPY) to sort of "suggest" taking an IC. In that case treat it as annoying but harmless. [...] 9441 80 27_Online Reorg - IRLMRWT Parm16_Brookman, Gerald32_gerald.brookman@US.ORIGIN-IT.COM31_Tue, 12 Dec 2000 12:19:47 -0500332_iso-8859-1 Env: DB2 OS/390 V6.1, no data sharing, using online reorgs

Could someone furnish some examples of what your current value of DSNZPARM "IRLMRWT" is set to if you are using online reorgs? We were using 60 seconds, but sometimes the "switching" process will exceed this if a non-committing batch job is running. [...] 9522 36 0_16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 12 Dec 2000 12:29:43 -0500586_iso-8859-1 Yes this is normal in V6. When you migrate to V6, these indexes have their definitions set to COPY YES.



regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Picou, Michael [mailto:picou.michael@MAIL.DC.STATE.FL.US] Sent: Tuesday, December 12, 2000 11:37 AM To: DB2-L@RYCI.COM Subject:



During disaster recovery, - after a recovery of the DB2 directory and a rebuild of the indexes - DSNSCT01, DSNSPT01, DSNLUX01, DSNLUX02, DSNLLX01, DSNLLX02 these indexes are in ICOPY status. Is this normal? [...] 9559 129 31_Re: Online Reorg - IRLMRWT Parm16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 12 Dec 2000 12:39:23 -0500432_iso-8859-1 Gary, We had to set ours up from 90 seconds to 240 when we started using Online Reorg. We are trying to improve the COMMIT situation. One handy diagnostic aid is to implement the ZPARM URCHKTH=#. This says send a message to the console for LUW which go more than # checkpoints without a COMMIT. Also consider lowering IDTHTOIN since many distributed requests come from poorly designed client software which has BPS. [...] 9689 67 0_8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 12 Dec 2000 11:56:47 -0600306_us-ascii In V6 you can take image copy of Indexes and this issue just a warning to take image copies of indexes. Recover index will use this Image copy, where as rebuild index will rebuild from data. Only indexes that were defined with the COPY YES attribute can be copied and subsequently recovered. [...] 9757 32 39_Packages in syspackage, can't be freed?11_David Nance16_DWNance@FHSC.COM31_Tue, 12 Dec 2000 13:58:21 -0500577_US-ASCII DB2 V5 OS/390, Attempting to clean up sysibm.syspackage. find a few thousand old packages that are no longer used when issuing a free against packages receive:

FREE PACKAGE (VMPASB.ASD110.(2000-11-07-17.05.58.216014)) DSNT238I -DBT1 FREE ERROR USING A9139 AUTHORITY PACKAGE = FHDB2T.VMPASB.ASD110.(2000-11-07-17.05.58.216014) DOES NOT EXIST DSNT233I -DBT1 UNSUCCESSFUL FREE FOR PACKAGE =

Checked error message says that pkg does not exist and to verify location, collid, and package have done this and still receive same error. Any ideas??? Thanks. [...] 9790 63 20_Re: Check Constraint19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Tue, 12 Dec 2000 13:28:17 -0600563_ISO-8859-1 Hi Shauna, Here's an interesting twist on this issue. We ran upon the same concern as you and came up with a combination of DB2 column constraints and COBOL 88 level checks. This method was only used on tables that had more than one constraint. We ignored the 88 level checks until any one of the constraints failed. We then ran the 88 level checks. This would allow you to find all the violated columns at once, if you prefer. One obvious issue is that you must maintain the 88 levels concurrent with the constraint values. (We have an automated [...] 9854 85 32_FW: DB2 Connect - ODBC - VB6 ADO14_Adrian Collett27_adriancollett@TISCALINET.IT31_Tue, 12 Dec 2000 21:24:35 +0100509_iso-8859-1 Again Andy, thanks very much for your prompt and exhaustive answers - they are far more comprehensive than anything that the VB programmers have drip-fed me with !!

One last question(I hope):

I presume this behaviour of VB is completely DBMS independent, i.e. the same application will generate exactly the same ODBC function calls independently of whether the ODBC driver being used is SQL/Slower, Oracle or DB2 ? Is this true ? Would the OLE DB layer have any effect on this ? [...] 9940 79 29_FW: DB2 V6 Log Suspend/Resume11_Lorenz, Don15_dlorenz@KCC.COM31_Tue, 12 Dec 2000 14:28:20 -0600631_iso-8859-1 Hi Richard, Paul, John and Patrick (those who responded about Log Suspend/Resume),

Thanks for your responses. After reading your responses, we are presently contacting SAP about OSS note 0363189. During testing, we encountered a similar (but possibly different) problem, with inconsistent pages on a recovery - of a 32K object. As Richard so succintly pointed out, the issue is how the Timefinder split actually works. We believe that we have another solution, which we are pursuing with SAP currently. Our question to them is essentially this: do we have 2 separate issues here or do we have 2 solutions to [...] 10020 134 36_Re: FW: DB2 Connect - ODBC - VB6 ADO13_Andy Seuffert21_aseuffert@NEONSYS.COM31_Tue, 12 Dec 2000 14:36:41 -0600630_us-ascii Andy Seuffert@NEON 12/12/2000 02:36 PM

Adrian, this is most likely specific to DB2, or in other words specific to a DBMS which does not support scrollable cursors. I don't think DB2 Connect "fully" supports the ODBC function SQLSetPos, which allows the positioning of a cursor on a result set. I know our ODBC driver does not, and this is because DB2 currently only supports forward-only cursors. Scrollable cursors are suppose to be available in DB2 for OS/390 V7. So what happens is ADO, via some ODBC SQLGetInfo calls, determines that the underlying DBMS only supports Forward-only cursors therefore it is [...] 10155 33 19_Re: DB2 Replication20_Alvarez, Maximiliano20_MAXIMILIANO@IADB.ORG31_Tue, 12 Dec 2000 17:52:43 -0500445_iso-8859-1 Dimitris

db2 replication supports transactional and non-transactional replication. Depending on how you design the replication, you may want every change done in a transaction to be propagated or just the final result of the transaction to be propagated.

Max.

-----Original Message----- From: Dimitris Margaritis [mailto:margardi@NOVABANK.GR] Sent: Monday, December 11, 2000 2:38 AM Subject: DB2 Replication [...] 10189 65 43_Re: Packages in syspackage, can't be freed?14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Wed, 13 Dec 2000 10:45:32 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 10255 17 30_Shark and Cylinder Allocations0_14_DPetro@AOL.COM29_Tue, 12 Dec 2000 20:51:20 EST415_US-ASCII With the new Shark devices and 'virtual' disks, is the idea of allocating on cylinder boundaries still valid? Any thoughts would be appreciated.

Thanks Dave.

================================================ 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. 10273 43 33_Simple but annoying SQL statement15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 13 Dec 2000 12:54:15 +1100464_- Colleagues,

I've got a simple but annoying SQL statement I'm trying to make run quickly but I'm stumbling on the no-doubt obvious fix. I have a table, T1, with 4.5m rows. I have a table, T2, with 180 rows. The definitions of T1 and T2 are the same (create table T2 like T1, indexes created to match) and runstats are current for both. Both tables are in the same tablespace - not ideal, but the smaller one is just for the life of the task I've got. [...] 10317 114 37_Re: Simple but annoying SQL statement0_31_greg.palgrave@UNISYSWEST.COM.AU31_Wed, 13 Dec 2000 10:39:07 +0800453_us-ascii Given there's only 180 rows in T2, why not just generate the delete statements?

e.g.

SELECT 'DELETE FROM T1 WHERE keycol1='||x'7D'||CAT_M||x'7D' ||' AND keycol2='||x'7D'||PROD_M||x'7D5E' FROM T2

Generates statements like:

DELETE FROM T1 WHERE keycol1='Fred' AND keycol2='John';

The x'5E' just puts a ';' on the end (use x'3B' for little DB2...), the x'7D' puts a single quote (use x'27' for little DB2). [...] 10432 11 0_16_Jittima Konglert21_Jittima@IT.EGAT.OR.TH31_Wed, 13 Dec 2000 10:22:47 +0700248_- TEST

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10444 117 27_Re: Mad MAX goes again ....21_Salim, Arli (SG-SEMA)22_arli.salim@SEMA.COM.SG31_Wed, 13 Dec 2000 12:04:12 +0800336_iso-8859-1 Hi Isaac & other Mad MAXes,

I'm a new dba but am prepared to be shot down by you all, on or off the list.

I feel that there's a better SQL to do the same job. Could any of you confirm on the equivalency and relative performance of this one:

select distinct max(k_number) from MyTable group by year; [...] 10562 75 37_Re: Simple but annoying SQL statement14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Wed, 13 Dec 2000 17:00:15 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 10638 94 37_Re: Simple but annoying SQL statement14_Massimo Ceraso24_Massimo.Ceraso@TLSOFT.IT31_Wed, 13 Dec 2000 08:10:21 +0100484_ISO-8859-1 Hi Raymond.

The solution is in the middle.

Your SQL > DELECT > FROM owner.T1 A > WHERE EXISTS ( > SELECT 1 > FROM owner.T2 B > WHERE B.keycol1=A.keycol1 > AND B.keycol2=A.keycol2 )

New SQl

> DELECT > FROM owner.T1 A > WHERE EXISTS ( > SELECT 1 > FROM owner.T2 B > WHERE B.keycol1=A.keycol1 > AND B.keycol2=A.keycol2 ) --- AND A.Keycol1 in --- ( Select B.Keycol1 from owner.T2 b) --- AND A.Keycol2 in --- ( Select B.Keycol2 from owner.T2 b) [...] 10733 106 37_Re: Simple but annoying SQL statement15_Matthias Pohlig27_matthias.pohlig@T-ONLINE.DE31_Wed, 13 Dec 2000 01:19:29 -0600402_- Hi Raymond,

Of course, it seems ugly that DB2 doesn't find a good way to access T1 in a "reasonable manner". (unless Steve's idea already did the trick).

Here's another "dirty hack" that might work if you are in a hurry ... I know it looks queer, but ...

Step 1:

SELECT 'DELETE FROM owner.T1 WHERE keycol1=' !! keycol1 !! ' AND keycol2=' !! keycol2 !! ' ; ' FROM T2; [...] 10840 22 18_timestamp question19_Dimitris Margaritis20_margardi@NOVABANK.GR31_Wed, 13 Dec 2000 11:21:59 +0200336_iso-8859-7 Hi all I'm new to DB2 SQL and i want to create a procedure that retrieves all rows from the table with a timestamp column that have inserted during current day e.g

select a, timestamp column from table where column between (current day 00.00.00) and current timestamp

Which functions should I use? Regards [...] 10863 38 22_Re: timestamp question10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Wed, 13 Dec 2000 04:36:21 -0600704_- Hello Dimitris,

select a, timestamp column from table where DATE(timestamp column) = CURRENT DATE;

is one way.



On Wed, 13 Dec 2000 11:21:59 +0200, Dimitris Margaritis wrote:

>Hi all >I'm new to DB2 SQL and > i want to create a procedure that retrieves all rows from the table with a >timestamp column that have inserted during current day e.g > >select a, timestamp column >from table >where column between (current day 00.00.00) and current timestamp > >Which functions should I use? >Regards > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage [...] 10902 27 48_DB2 program calling another DB2 program question10_Lee Mandel16_DBMSUser@AOL.COM29_Wed, 13 Dec 2000 06:17:42 EST476_US-ASCII Hi All, Lets say I have a db2 program running that I have called using tso like this. DATA DSN SYSTEM(DB2T) RUN PROGRAM(PGMA) PLAN(PGMA) END ENDDATA

pgma is running OK and I wish to call another db2 program PGMB Can I use the same code as above to call PGMB and if so what must I do to BIND these together or whatever it take to call PGMB from PGMA. Also what manual will give me an example. Any help will be appreciated. Point me in the right direction. [...] 10930 16 53_Fwd: DB2 program calling another DB2 program question10_Lee Mandel16_DBMSUser@AOL.COM29_Wed, 13 Dec 2000 06:40:00 EST463_US-ASCII I forgot to mention that both program PGMA and PGMB both run OK using the same procedure one at a time. I just want PMGA to call PGMB. Also I am using ISPF Dialog Manager to run this application. Thank again. 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. 10947 56 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 13 Dec 2000 07:55:52 -0500546_ISO-8859-1 Using VDWQT of 0 will rarely have any effect on the overall number of I/Os, or the CPU time. Most systems and pools have a low number of avg pages/write (<5), and when this is he case there will be no increase of CPU. Most systems I look at avg <3 pages/write. So when write gets triggered, the number of I/Os does not change with a low vdwqt..... it just smoothes them out, or causes a "trickle" write. Lowering vdwqt to 0 avoids flooding the system with I/Os when Db2 takes a checkpoint - and this flooding usually causes large [...] 11004 32 64_V6 Migration/Missing colon - REXX to regenerate new format DBRMs20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 13 Dec 2000 08:56:59 -0500490_us-ascii Dear Listserv folks

For those, who are migrating to V6 and still have useful PLAN-DBRM or packages, which were pre-compiled during V2.2 and prior and may/may not have missing colons, I have a useful REXX.

The REXX exec reads SYSIBM.SYSSTMT or SYSIBM.SYSPACKSTMT and generates DBRM as if they have been pre-compiled fresh in V5. This should take care of migrating DBRMs from older version to newer version (regardless of colon for host variables missing or not). [...] 11037 63 37_Re: Simple but annoying SQL statement19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US29_Wed, 13 Dec 2000 09:09:12 EST391_us-ascii Raymond,

I have a question. When you select the rows to insert into T2, is the access path so bad that you would not want to use the same criteria to delete the rows?

Rajeev P Dhanawade Phone : 302-577-4659 Ext. 221 ++ Fax : 302-577-4454 ++ ------------- Original Text From: "Bell, Raymond W" , on 12/12/2000 8:54 PM: Colleagues, [...] 11101 69 52_Re: DB2 program calling another DB2 program question0_22_BILL_GALLAGHER@PHL.COM31_Wed, 13 Dec 2000 09:10:42 -0500458_us-ascii Lee,

Plan PGMA would have to include PGMB as either a DBRM or as part of it's PKLIST. As long as plan PGMA includes programs PGMA and PGMB, you should not have any problems.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







Lee Mandel cc: Sent by: DB2 Subject: DB2 program calling another DB2 program question Data Base Discussion List [...] 11171 16 30_Shark, EMC & Network Appliance6_hhardy17_hhardy@SWBELL.NET31_Wed, 13 Dec 2000 08:26:54 -0600487_iso-8859-1 Can anyone share experience running Oracel fin-manuf & dB, SAP or DB2/UDB on the above mention platforms in a shared environment. Performance considerations, tunning and problems..... sharing within desktop, AS400, NT and Big Iron....

================================================ 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. 11188 16 23_Specifying A Data Class16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Wed, 13 Dec 2000 09:42:09 -0500437_iso-8859-1 Is there a ZPARM value that I can use to specify an SMS data class. We want to be able to send our archive data set's to 3590's. Thanks...

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. 11205 14 15_SEND_DATA error32_Arocho, Vance (CORP, Consultant)29_Vance.Arocho@CORPORATE.GE.COM31_Wed, 13 Dec 2000 09:51:18 -0500372_iso-8859-1 Using: PowerBuilder 6.5, DB2, Windows95

Has anyone run into a SEND_DATA error and if so, how did you resolve it?

================================================ 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. 11220 79 27_Re: Mad MAX goes again ....0_24_db46@DAIMLERCHRYSLER.COM31_Wed, 13 Dec 2000 10:11:21 -0500500_iso-8859-1 Yes this will work since by adding the 'GROUP BY' clause, DB2 returns a row for 'year' even though it is not the 'select' portion.

Dean









"Salim, Arli (SG-SEMA)" @RYCI.COM> on 12/12/2000 11:04:12 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: Mad MAX goes again .... [...] 11300 101 37_Re: Simple but annoying SQL statement13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Wed, 13 Dec 2000 10:18:38 -0500413_ISO-8859-7 Raymond,

I'm puzzled by this, especially your statement that "T1 is scanned first". Please confirm a few basics first.

Is this DB2 V? for OS/390? Are you running this SQL statement as static or dynamic SQL (program? SPUFI?) Has the tablespace containing the tables been REORG-ed? Has RunStats been run recently? Before/after a Reorg? If static SQL, was it Re-BIND-ed after Runstats? [...] 11402 67 27_Re: Specifying A Data Class10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 13 Dec 2000 09:21:21 -0600538_us-ascii Mike,

From my zparm guideline doc: |--------------------+-------------+-------------+-------------+-------------| | SMS data class for | SMSDCFL | * system | * system | * system | | tablespaces | | specific * | specific * | specific * | |--------------------+-------------+-------------+-------------+-------------| | SMS data class for| SMSDCIX | * system | * system | * system | | indexspaces | | specific * | specific * | specific * | |--------------------+-------------+-------------+-------------+-------------| [...] 11470 76 19_Row Level locking !27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Wed, 13 Dec 2000 21:13:56 +0530336_iso-8859-1 Hi !

How do we achieve row level locking in a Java -DB2 program. We are using UDB 6.1 on a Sun Solaris box.

When the user selects a particular row, then we want that all insertions, updations related to that particular record be locked for that transaction. I give a small example to clarify what I mean : [...] 11547 111 23_Re: Row Level locking !16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 10:32:18 -0500306_iso-8859-1 Deepak, Once you have done a SELECT FOR UPDATE OF on the school row, the page in which the row resides is locked. No other task can update this until you do the COMMIT.

All that row-level locking buys you is that it allows access (at a cost!) to *other* rows in that same data page. [...] 11659 12 23_Re: Row Level locking !15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Wed, 13 Dec 2000 09:40:08 -0600302_us-ascii Can also use CS Isolation parameter in bind command

================================================ 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. 11672 17 16_Tivoli Inventory12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Wed, 13 Dec 2000 10:58:34 -0500387_us-ascii Hello, We are attempting to send an inventory query from Tivoli Tme10 running on a unix box thru NT to DB2 on the mainframe. DB2 is v5. On the order by clause is Char(ucase) col-name. We are getting a -104. We believe UCASE is a Version 6 option. What we are really questioning is can the Char(ucase) be used on the order by clause and is antone else doing this? TIA, Jeff [...] 11690 160 24_Re: SHARD, ESS Questions30_CHRISTOPHER.KELLER@Vereinte.de30_CHRISTOPHER.KELLER@VEREINTE.DE31_Wed, 13 Dec 2000 17:21:00 +0100582_iso-8859-1 --- Received from BG1.KELLERC 6785-2761 13-12-00 17.21 --------------------------------------------------------------------------------------------------------------

-> DB2-L@RYCI.COM

Hello, Yes, no dataset copy yet, just volume. According to what I have read the use of FlashCopy in an online environment for IC purposes, i.e. use of set log suspend in DB2V6, has problems with: (1) long running processes which don't issue commits (like SAP) (2) 32KB pages (see SG24-6108-00, V6+) = a 100% POC is not given, or? Regards, Christopher ................ [...] 11851 137 23_Re: Row Level locking !19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 13 Dec 2000 08:39:46 -0800591_iso-8859-1 Just for clarification, unless I've missed something in a fixpack, UDB 6.1 defaults to Row level locking and does not support Page level. OS/390 defaults to Page, and allows Row as an option (with more overhead).

HTH, Tim > -----Original Message----- > From: Pearson, Eric L, [SMTP:Eric.Pearson@NSCORP.COM] > Sent: Wednesday, December 13, 2000 7:32 AM > To: DB2-L@RYCI.COM > Subject: Re: Row Level locking ! > > Deepak, > Once you have done a SELECT FOR UPDATE OF on the school row, > the page in which the row resides is locked. No other task can > update this until [...] 11989 160 23_Re: Row Level locking !16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 11:47:30 -0500441_iso-8859-1 Oh my! I'll have to study up on UDB. I wonder how much CPU this critter burns doing row-level locking for a site with high (hundreds of millions per day) update activity???

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Ohling, Tim R - CNF [mailto:Ohling.Tim@CNF.COM] Sent: Wednesday, December 13, 2000 11:40 AM To: DB2-L@RYCI.COM Subject: Re: Row Level locking ! [...] 12150 159 23_Re: Row Level locking !14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 13 Dec 2000 12:16:46 -0500429_iso-8859-1 Are you sure??? From the V6 Manuals 'ANY' is the defualt and further states:

"ANY

Specifies that DB2 can use any lock size. Currently, DB2 never chooses row locks, but reserves the right to do so. "





-----Original Message----- From: Ohling, Tim R - CNF [mailto:Ohling.Tim@CNF.COM] Sent: Wednesday, December 13, 2000 11:40 AM To: DB2-L@RYCI.COM Subject: Re: Row Level locking ! [...] 12310 205 23_Re: Row Level locking !19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 13 Dec 2000 09:31:07 -0800621_iso-8859-1 I double-checked the UDB manuals and they indicate Row level is default. The text your referencing matches the OS/390 version I found.

E.g. from the "DB2 UDB for UNIX, Windows, OS/2 Database Administration Certification Preparation" class handout from the October Tech Conference: "DB2 uses record (row) level locking by default"

So it depends - on which manuals go with which version of DB2! What IBM needs is a "universal" SQL reference with the full syntax indicating which options go with which platforms. There are significant differences that will get you if you aren't aware of them. [...] 12516 77 120_Feb 27th-28th - Central PA User Group - Linda Claussen - Cross Platform DBA & DB2 Triggers classes - $150 - Registration10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Wed, 13 Dec 2000 12:09:49 -0500653_us-ascii ----- Forwarded by Cathy L Peck/ISG/CORP/Highmark on 12/13/00 12:14 PM -----

Cathy L Peck To: Cathy L Peck/ISG/CORP/Highmark@HighmarkNotes 12/13/00 cc: (bcc: Cathy L Peck/ISG/CORP/Highmark) 11:59 AM Subject: Feb 27th-28th - Central PA User Group - Linda Claussen - Cross Platform DBA & DB2 Triggers classes - $150 - Registration







Hi everyone! Since so many people are anxious to get a handle on DB2 triggers & begin transitioning from the OS390 platform into the Open Systems arena, Linda is coming to the area in Feb to teach 2 days of classes. Many of you may remember Linda from her numerous visits [...] 12594 75 158_April 25th-26th - Central PA User Group - Sheryl Larsen, SML - DB2 Advanced/COmplex SQL & SQL Performance, Access Paths & Tuning Classes - $175 - Registration10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Wed, 13 Dec 2000 12:10:33 -0500388_us-ascii ----- Forwarded by Cathy L Peck/ISG/CORP/Highmark on 12/13/00 12:15 PM -----

Cathy L Peck To: Cathy L Peck/ISG/CORP/Highmark@HighmarkNotes 12/13/00 cc: (bcc: Cathy L Peck/ISG/CORP/Highmark) 12:00 PM Subject: April 25th-26th - Central PA User Group - Sheryl Larsen, SML - DB2 Advanced/COmplex SQL & SQL Performance, Access Paths & Tuning Classes - $175 - Registration [...] 12670 111 23_Re: Row Level locking !0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 13 Dec 2000 12:44:52 -0500507_us-ascii My understanding is that UDB (non OS/390) "only" allows row-level locking. Tablespace and data page locking does not exist outside of DB2 OS/390

R/...Ed







"Thakral, Deepak Kumar (CTS)" @RYCI.COM> on 12/13/2000 10:43:56 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Row Level locking ! [...] 12782 62 52_Re: DB2 program calling another DB2 program question10_Lee Mandel16_DBMSUser@AOL.COM29_Wed, 13 Dec 2000 12:42:54 EST821_US-ASCII Bill, I am still doing something incorrect. My bind job look like this: //SYSTSIN DD * DSN SYSTEM(DB2T) BIND PLAN(RKIDL419) MEM(RKIDL419) ACT(REP) ISOLATION(CS) - LIB('RKI.D1129.DBRMLIB.DATA') BIND PLAN(RKIDL420) MEM(RKIDL420) ACT(REP) ISOLATION(CS) - LIB('RKI.D1129.DBRMLIB.DATA') BIND PLAN(RKIDL421) MEM(RKIDL419,RKIDL420) - ACT(REP) ISOLATION(CS) - LIB('RKI.D1129.DBRMLIB.DATA') RUN PROGRAM(DSNTEP2) PLAN(DSNTEP31) - LIB('DB2.TEST.RUNLIB.LOAD') END //SYSIN DD * GRANT EXECUTE, BIND ON PLAN RKIDL419, RKIDL420, RKIDL421 TO PUBLIC; Also my clists look like this: this one work OK CONTROL END(ENDO) MSG CONLIST LIST FLUSH /*NTROL END(ENDO) ISPEXEC VGET (DB2ID ICBACK ZUSER HILEV) SHARED ISPEXEC LIBDEF ISPLLIB DATASET ID('&HILEV..LOAD') IF &LASTCC = 0 THEN DO DATA DSN SYSTEM(&DB2ID.) RUN PROGRAM(RKIDL419) [...] 12845 38 23_Re: Row Level locking !13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 13 Dec 2000 12:49:29 -0500340_iso-8859-1 Hello Scott & Tim, I think part of the problem here is name branding.

Seems to me Tim, that when you say UDB, you mean UDB for non-OS/390. Perhaps you're assuming the reverse, Scott.

Here's what I have found. Scott is quoting the OS/390 v6 manual. Tim is referring to the UDB for Windows, Unix, and OS/2 pubs. [...] 12884 55 23_Re: Row Level locking !14_Scott Trometer22_scott.trometer@RCI.COM31_Wed, 13 Dec 2000 12:52:23 -0500427_iso-8859-1 Yep, And it's my bad for jumping in anyway. After re-reading the original post, I see that UDB is running on Sun Solaris...





-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Wednesday, December 13, 2000 12:49 PM To: DB2-L@RYCI.COM Subject: Re: Row Level locking !



Hello Scott & Tim, I think part of the problem here is name branding. [...] 12940 34 19_Re: DB2 Replication18_Yosia Widi Nugroho20_yosia@CENTRIN.NET.ID31_Thu, 14 Dec 2000 00:55:50 +0700562_iso-8859-1 Hi Max,

Could you please tell me how to design this kind of replication? It will be very usefull for us.

Thanks, Yosia

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Alvarez, Maximiliano > > Dimitris > > db2 replication supports transactional and non-transactional replication. > Depending on how you design the replication, you may want every > change done > in a transaction to be propagated or just the final result of the > transaction to be propagated. > > Max. [...] 12975 55 23_Re: Row Level locking !19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM31_Wed, 13 Dec 2000 09:57:40 -0800568_- Seems like we're all (locked) on the same page now... I'd still like to see one "universal" set of docs so avoid this confusion.

Thanks, Tim > -----Original Message----- > From: Seibert, Dave [SMTP:Dave_Seibert@COMPUWARE.COM] > Sent: Wednesday, December 13, 2000 9:49 AM > To: DB2-L@RYCI.COM > Subject: Re: Row Level locking ! > > Hello Scott & Tim, > I think part of the problem here is name branding. > > Seems to me Tim, that when you say UDB, you mean UDB for non-OS/390. > Perhaps you're assuming the reverse, Scott. > > Here's what I have found. > [...] 13031 52 52_Re: DB2 program calling another DB2 program question16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 13:05:19 -0500507_iso-8859-1 Lee, This is easy if you use PACKAGES. BIND all the programs as PACKAGES in the same COLLECTIONID. Then Bind the PLANS to COLLECTIONID.* rather than to DBRMs. This allows any of the programs to call any of the others.



regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Lee Mandel [mailto:DBMSUser@AOL.COM] Sent: Wednesday, December 13, 2000 6:18 AM To: DB2-L@RYCI.COM Subject: DB2 program calling another DB2 program question [...] 13084 28 23_Re: Row Level locking !13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 13 Dec 2000 13:25:38 -0500410_iso-8859-1 > I'd still like to see one "universal" set of docs so avoid this confusion.

In your dreams, Tim.

There used to be SQL FRED (Formal Register of Environmental Differences (I think)) which did exactly this. But I was told it is no longer published because Oracle spies used to use it to show embarrassing inconsistencies or deficiencies. (Geez I hope Marc Connolly's not listening) [...] 13113 41 46_EXPORT command on DB2 UDB v6.1 on AIX and LOBS10_Robert Ord21_robertord@HOTMAIL.COM31_Wed, 13 Dec 2000 18:46:16 -0000365_- I have a number of tables to export containing LOB data and EXPORT works fine until I get the message;

SQL3040N Unable to use the lobpath/lobfile parameters as specified. Reason code: "4".

"There are not enough lobfile names provided. For export, there are more lobs than the number of lobfile names provided multiplied by SQLU_MAX_SESSIONS. " [...] 13155 26 40_Re: Evaluating CA/Platinum DB2 Utilities16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Wed, 13 Dec 2000 14:19:48 -0500358_us-ascii Esteemed List:

Our shop is in the process of evaluating CA/Platinum's DB2 utilities. Their maintenance records suggest that they are rock-solid - only three to six HIPER's/year per product. However they do not appear to have updated their products yet to fully support DB2 V6.

Can anyone who is using these products confirm/deny? [...] 13182 31 40_Re: Evaluating CA/Platinum DB2 Utilities13_Lisa McClaine26_lisa.mcclaine@BLACKBOX.COM31_Wed, 13 Dec 2000 14:27:52 -0500558_iso-8859-1 We are using this suite of products (though not the utilities). We use RC/Update, Migrator, Query, and Secure. I thought that their latest version (P99C) did support V6 of DB2. I know that P99D is due out in a few weeks.

As far as reliability, I would have called them rock solid a year ago but not anymore. No, I haven't experienced anything that caused downtime but ever since CA took over Platinum, we are running into little bugs all of the time. After we upgraded to the P99C version, we discovered 3 bugs -- not big ones but big [...] 13214 28 22_Re: Language Interface12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 13 Dec 2000 13:29:17 -0600597_- I think we chose the fast way that is well architected. Several requests have come in lately that ask for a single stub. There is no existing architecture to tell what environment we are in, e.g. IMS, CICS, TSO, batch, stored procedure, USS, WebSphere, ... Not having an architecture means that the interface is more likely to break and much more likely to require changes. For some of those environments there are several possibilities for the attachment. The attaches include IMS, CICS, command or TSO attach, Call Attach and RRS. Getting the needed parameters to the modules is the next [...] 13243 48 34_Re: Shark and Cylinder Allocations15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Wed, 13 Dec 2000 13:35:12 -0600541_iso-8859-1 Allocation on cylinder boundaries is combined hardware/software question. It has nothing to do with SHARK devices per se.

In the old days, any non-cylinder aligned allocations would cause track level interrupts, no matter what device it is on. If a program is still using seek commands, then this is still true, even for a SHARK device. However, most all, if not all, programs have been modified to use the define extent and locate record commands. By using them many of the old time considerations have dropped away. [...] 13292 53 40_Re: Evaluating CA/Platinum DB2 Utilities16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 14:35:52 -0500362_iso-8859-1 We use these and have been pleased but may dump RapidReorg and QuickCopy since the free IBM versions have improved so much.

The only real problems we have had is that the LOAD can't do a 254-partition Large Tablespace (has Getmain problems) so loads for LARGE tablespaces must be broken into chunks of about 30 or so partitions at a time. [...] 13346 20 27_Re: Specifying A Data Class10_Rob Wright17_rwright@LIC.CO.NZ31_Thu, 14 Dec 2000 08:45:19 +1200300_us-ascii I think the value you want is on the DSN6ARVP macro, UNIT and UNIT2. Set these to your 3590 esoteric name (see your MVS sys prog). This may be something like 3590, or C3590.

Alternatively, you could have your SMS administrator to define an ACS rule to force the dataset to 3590. [...] 13367 16 31_Re: Site License for DB2 OS/39012_Roger Miller19_millerrl@US.IBM.COM31_Wed, 13 Dec 2000 13:44:34 -0600313_- There are over 10,000 licenses of DB2 for OS/390 and the numbers continue to climb. Around 70% to 80% of the OS/390 machines have a DB2 running there. Counting is trickier than ever. We would like you to have a zSeries 900 instead of 4 CMOS generation 2 machines, but that reduces the count, for instance. [...] 13384 76 40_Re: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Wed, 13 Dec 2000 13:53:01 -0600335_iso-8859-1 Hi, Richard,

I have 99C here now but I don't have it fully installed yet. From the README members that I have seen the products TOLERATE V6 but don't try to use any of the new V6 features. That is a problem for us since our agency customers want to exploit V6 and can't use the Platinum tools in order to do so. [...] 13461 111 27_Re: Specifying A Data Class14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Wed, 13 Dec 2000 11:59:11 -0800407_iso-8859-1 Mike, DB2 allows specifying the DATACLAS for tablespaces and indexes, but not for logs. However, I think the best way to do this would be to have the SMS admin add code to the DATACLAS ACS routine for this. Since the dataset name is a constant, this should be straight-forward. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 13573 114 40_Re: Evaluating CA/Platinum DB2 Utilities0_20_John_Lendman@FPL.COM31_Wed, 13 Dec 2000 15:01:35 -0500456_us-ascii We do use Platinum Utilities and to the point about PDA, I use it to generate my SAP reorgs. It does seem to work very well. If any one wants to discuss the details please send me an separate email.

John Lendman Jlendman@fpl.com





"Billings, Linda" cc: Sent by: "DB2 Data Subject: Re: Evaluating CA/Platinum DB2 Base Discussion List" Utilities [...] 13688 16 40_Re: Evaluating CA/Platinum DB2 Utilities13_Lisa McClaine26_lisa.mcclaine@BLACKBOX.COM31_Wed, 13 Dec 2000 15:03:28 -0500299_iso-8859-1 We use PDA too. Works well for us.

Lisa

================================================ 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. 13705 61 27_Re: Mad MAX goes again ....20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 13 Dec 2000 15:07:44 -0500556_us-ascii Of course you are right. It is really simple and there is no need to twist and test the query. But what we were discussing was the efficient/inefficient use of subquery using 4k memory table, example is immaterial.

> -----Original Message----- > From: Salim, Arli (SG-SEMA) [SMTP:arli.salim@SEMA.COM.SG] > Sent: Tuesday, December 12, 2000 11:04 PM > To: DB2-L@RYCI.COM > Subject: Re: Mad MAX goes again .... > > Hi Isaac & other Mad MAXes, > > I'm a new dba but am prepared to be shot down by you all, > on or off the list. > > I feel [...] 13767 55 11_signoff DB210_Wang.James24_Wang.James@AAA-CALIF.COM31_Wed, 13 Dec 2000 12:13:18 -0800174_us-ascii Signoff db2

James Wang ESD-Technical Services Automobile Club of Southern California (714) 850-2851 Wang.James@aaa-calif.com 13823 21 30_Xpediter and Stored Procedures16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 15:22:03 -0500579_iso-8859-1 Anyone have any success using XPediter from Compuware for debugging Stored Procedure? IBM recommends using the "Debug Tool feature of COBOL/390". They want over $13K per year to add this feature. We already have XPediter and would prefer to avoid additional expense.

regards,

eric pearson NS ITO Database Support

================================================ 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 59 34_Re: Xpediter and Stored Procedures0_19_Tim.Lowe@STPAUL.COM31_Wed, 13 Dec 2000 14:31:38 -0600453_us-ascii Eric, We talked to Compuware about getting Xpediter support for testing stored procedures, but when they came out with it, the extra cost for this feature was too much for our management to approve. So, this is on hold for now.

Good luck, Tim





"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: Xpediter and Stored Procedures Sent by: DB2 Data Base Discussion List [...] 13905 96 40_Re: Evaluating CA/Platinum DB2 Utilities0_22_BILL_GALLAGHER@PHL.COM31_Wed, 13 Dec 2000 15:38:13 -0500476_us-ascii Eric,

In case you missed the overkilled thread of discussion a few months ago, IBM DB2 utilities are no longer free as of v7. They will be a separately licensed and chargeable product.

Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083







"Pearson, Eric L," To: DB2-L@RYCI.COM Subject: Re: Evaluating CA/Platinum DB2 Utilities Sent by: DB2 Data Base Discussion List [...] 14002 123 40_Re: Evaluating CA/Platinum DB2 Utilities16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 13 Dec 2000 16:06:48 -0500331_iso-8859-1 WOW. In that case, I have a *real* strong argument for us to drop the OEM utilities. If we must pay no matter what, it makes sense to use one vendor for the product and the utilities and thus avoid cross-vendor finger pointing and responsibility evasion.

regards,

eric pearson NS ITO Database Support [...] 14126 34 114_June 6th-7th - Central PA User Group - Bonnie Baker - EXPLAIN Explained & Things... Part III - $150 - Registration10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Wed, 13 Dec 2000 17:00:03 -0500 14161 43 144_March 28-29th - Central PA User Group - Susan Lawson, YL&A - DB2 V6/7 Performance & High Performance Design/Tuning Classes - $150 - Registration10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Wed, 13 Dec 2000 17:00:56 -0500 14205 106 37_Re: Simple but annoying SQL statement15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 14 Dec 2000 10:40:07 +1100670_- Greg/Steve/Massimo/Matthias/Rajeev/Lockwood/Jeff/Bill/anyone_else_I've_misse d,

Thanks for the excellent ideas on this 'illegitimate' SQL statement. The prize for finding the solution goes to... (drum roll, please)... Massimo and Jeff. Sorry guys, you'll have to share the T-shirt between you. The trick, as Jeff explained, was to create an 'IN' list for my two keys so DB2 could use this to access T1 efficiently. Still goes to T1 first though - according to (the way I'm reading) the plan_table anyway, which to me doesn't make sense - but with accesstype=N (in-list processing) using the unique index via matchcols=2. And I still needed the correlation [...] 14312 123 52_Re: DB2 program calling another DB2 program question14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 14 Dec 2000 11:02:26 +1100492_iso-8859-1 Lee

How do you 'call a clist'? Do you attach a task or run it as a subprogram?

The DSN command is not designed to support multiple attachments to DB2 at the same time.

The fact that you are getting a S0C4 means that you can raise a PMR. However, you might find that the response is along the lines of "the DSN command cannot be invoked in a subtask/subprogram of of a DSN command so, instead of giving you a S0C4, we will give you a S04E-???????? abend." [...] 14436 34 4_-15116_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Wed, 13 Dec 2000 16:03:00 -0800533_- Programmer received the following error on some new SQL UPDATing a table in a partitioned TS. The PARTKEYU parameter is SAME and the SQL didn't change the partition# of any row anyway. The UPDATES column in SYSIBM.SYSCOLUMNS for the offending column is 'N' which I assume is there from the days when you could not change a column in a partitioned table. This SQL ran against the test table which is also partitioned and has UPDATES = 'N' for the column. This is a table and not a view. The column is a TIMESTAMP, not a ROWID. [...] 14471 64 8_Re: -15111_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 13 Dec 2000 18:30:06 -0600391_iso-8859-1 I remember encountering this problem. Even when the PTF for updating the partitioning key was applied on the subsystem, the update column in the SYSIBM.SYSCOLUMNS had 'N' in it. But if you drop and recreate the table the update column in the SYSIBM.SYSCOLUMS will change to 'Y', and then you should be able to update it. About the update happening in test table don't know ? [...] 14536 62 22_Re: Language Interface14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Thu, 14 Dec 2000 12:10:27 +1100480_iso-8859-1 But does the fact you provide a fast, well-architected set of LI modules stop you _also_ providing a not-so-fast, only-suitable-for-a-particular-set-of-circumstances single LI module that could determine and use whatever attachment is currently active?

Let us determine which size fits our particular needs best. The previous posts should indicate that some users are prepared to go to considerable lengths to get a module to execute in different attaches. [...] 14599 142 21_IDUG 2001Notification14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Wed, 13 Dec 2000 17:50:11 -0800671_- Posted for IDUG by Phil Gunning

Dear DB2 Professional- Mark your calendars for the premier user-run DB2 event in North America:

IDUG 2001 - North America DB2 Powered by You May 13-17, 2001 Orlando Marriott World Center Orlando, Florida, USA

With four full days of education and more than 140 in-depth sessions IDUG 2001 - North America will empower you with solutions to help you work smarter, faster, and more efficiently. New for 2001, a full technical track dedicated to DB2 fundamentals and free DB2 Universal Database for OS/390 Certification testing. For detailed session information and online registration visit . [...] 14742 59 40_Re: Evaluating CA/Platinum DB2 Utilities12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 14 Dec 2000 12:01:30 +1100505_- Linda, you could be right <> My suspicions are that tech support for the products is overwhelmed right now because of the buy-out. Perhaps they need more time to settle in, only a suspicion on my part. Someone might correct me.

My opinion and observation only, , but I feel that the reasons may be a little different. When they were Platinum tools they tried to deliver ASAP the exploitation of new releases of DB2. Quite simply they had to, to win business from the likes of BMC et al. [...] 14802 98 27_Re: Mad MAX goes again ....21_Salim, Arli (SG-SEMA)22_arli.salim@SEMA.COM.SG31_Thu, 14 Dec 2000 10:15:19 +0800334_iso-8859-1 Hi Venkat,

I see what you're saying. The SQLs happened to intrigue me since they were run on a machine; so presumably, were taken from a real life application.

Does the 4K need to be increased? It'd be very interesting to find out what the optimal size should be and, if any, supporting evidence for it. [...] 14901 25 28_Online Reorg SHRLEVEL CHANGE0_20_bjnigh@HOUSEHOLD.COM31_Wed, 13 Dec 2000 18:18:04 -0800460_us-ascii We have a distributed DB2 application which performs only selects on a table that needs to be reorganized frequently. This is a 24 by 7 application and cannot have down time for maintenance. While running an IBM ONLINE REORG SHRLEVEL CHANGE today I ran into a problem when trying to obtain a drain lock on the DB2 objects during the SWITCH phase. It was only after taking down the distributed app that my REORG was able to complete successfully. [...] 14927 35 17_'Insert of BLOBs'0_18_dsi.listsrv@DB.COM31_Thu, 14 Dec 2000 10:13:09 +0530361_us-ascii Hi,

I have a table which has the following structure :

Table x.

A CHAR(20) PK B DECIMAL(05) PK C BLOB(1M)

I have a 'MS-Word' document(abc.doc) which I want to insert into the above table(ie. into the BLOB field). Is it possible to insert this using the command insert / import /Load etc. I am working on UDB 6.1 on NT. [...] 14963 18 64_How to create a temporary table and pass data to that in DB2/40020_Yasas Mallawarachchi14_yasas@SLIIT.LK31_Thu, 14 Dec 2000 10:53:31 +0600413_iso-8859-1 Hi All,

is there anyone who knows how to create a temporay table in DB2/400 and pass data into it.Please let me know the SQL lines for that.

Yasas

================================================ 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. 14982 211 37_Re: Simple but annoying SQL statement11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU31_Thu, 14 Dec 2000 15:47:51 +1100375_- Just to add a last comment to this discussion - should anybody ever need to solve this type of problem in little db2 (Unix etc), there you CAN use something similar to Steve's suggestion since DB2 UNO allows expressions on either side of the IN predicate - i.e.

SELECT * -- DELETE FROM owner.T1 WHERE (keycol1,keycol2) IN (SELECT keycol1,keycol2 FROM owner.T2) [...] 15194 128 27_Re: Mad MAX goes again ....13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 14 Dec 2000 00:45:47 -0600493_iso-8859-1 Arli & Venkat,

I had been off the list and unfortunately missed the trail on this.

The 4K cache is the limit and there is no way to alter the size.

With the combination of the DISTINCT and GROUP BY, the DISTINCT is redundant and therefore discarded by the optimizer.

Also, the comparison of the original two queries (I know it was a logical comparison); the main difference between the GROUP BY expression and the correlated subquery is as follows: [...] 15323 83 37_Re: Simple but annoying SQL statement15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 14 Dec 2000 17:45:56 +1100665_- Interesting. I guess, seeing as how both my key columns were chars (and even if they weren't, thanks to some fancy column functinos) I could have said 'where keycol1||keycol2 in (select keycol1||keycol2 from owner.t2)'.

Many ways to flay a feline, so to speak.



Raymond



> -----Original Message----- > From: KUHN,Ulrich [SMTP:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] > Sent: Thursday, 14 December 2000 3:48 pm > To: DB2-L@RYCI.COM > Subject: Re: Simple but annoying SQL statement > > Just to add a last comment to this discussion - should anybody ever need > to solve this type of problem in little db2 (Unix etc), there you [...] 15407 201 37_Re: Simple but annoying SQL statement13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 14 Dec 2000 01:16:19 -0600485_iso-8859-1 RE: Simple but annoying SQL statementUlrich,

As you pointed out, it is an excellent feature; known as a ROW Expression which is FINALLY delivered in DB2 OS/390 in V7.

Raymond,

The performance improvement you are seeing from this revised query is due to the V6 feature: indexability of non-correlated IN subqueries. In V6 this feature has some bugs, but since you are on V5 with the APAR applied (call it intuition), then it is working as designed. [...] 15609 112 37_Re: Simple but annoying SQL statement13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 14 Dec 2000 01:20:29 -0600627_iso-8859-1 Raymond,

The difference between what you proposed and what Ulrich highlighted; is that the V7 row expression is indexable, but your concatenation is stage 2 and not indexable. Otherwise you are correct, they are logically equivalent.

Regards Terry Purcell Terry_Purcell@YLAssoc.com Yevich Lawson & Associates WWW.YLASSOC.COM WWW.DB2-PORTAL.COM WWW.DB2-SYMPOSIUM.COM

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Bell, Raymond W Sent: Thursday, December 14, 2000 12:46 AM To: DB2-L@RYCI.COM Subject: Re: Simple but annoying SQL statement [...] 15722 83 27_Re: Mad MAX goes again ....16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 14 Dec 2000 13:00:57 +0530530_- Hi Arli, What if i point out something which may be wrong ? "someone will point me in the right direction, that's good".I sent this mail yesterday as well but it bounced back because of change in my e-mail address. Thanks to the list owner for changing the address.

I think that the result set "may" be different due to the grouping you have done on year.In the sqls mentioned in the Issac's mail you can miss the records of some years if the max(K_number ) is same for the some of the years.In your sql , u will be [...] 15806 64 32_Re: Online Reorg SHRLEVEL CHANGE16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 14 Dec 2000 13:19:17 +0530583_- Perfect co-ordination of all the SHRLEVEL CHANGE parameters will definitely help you. Just go through the manual for the description of all the parameters and then change those accordingly. In the mean time changing the SQL statements to UR may not give you much results because CS Claim classes are aquired by the SQLs with UR and DB2 drains the claims(reader and writers) , in this case it looks only reader claims. In your case it is reader claims. For switch phase DB2 has to drain everything because it has to rename the object datasets name. For read only transactions, [...] 15871 89 37_Re: Mad MAX goes again ....correction13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 14 Dec 2000 02:16:06 -0600387_iso-8859-1 After a discussion with Arli off list, I must post a correction to my previous post:

I misread that the original query had additional GROUP BY's to remove the duplicate K_NUMBER values. Therefore the statement: "With the combination of the DISTINCT and GROUP BY, the DISTINCT is redundant and therefore discarded by the optimizer." Is not correct in this context. [...] 15961 241 27_Re: Mad MAX goes again ....21_Salim, Arli (SG-SEMA)22_arli.salim@SEMA.COM.SG31_Thu, 14 Dec 2000 17:02:48 +0800461_iso-8859-1 Hi Sanjeev,

I think the distinct in the third SQL would solve that problem.

However, I agree with you that the result sets "may" be different (because no order bys are specified in any of the SQLs.) Any opinion on this?

Cheers, arli

-----Original Message----- From: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Sent: Thursday, December 14, 2000 3:31 PM To: DB2-L@RYCI.COM Subject: Re: Mad MAX goes again .... [...] 16203 22 20_SQL coding standards11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK31_Thu, 14 Dec 2000 09:18:02 -0000370_- Fellow DB2 professionals,

Can anyone direct me to a resource (book, white paper, redbook etc.) that documents what standards should be employed when developing an application against SQLServer at the desktop that will be run against DB2 (for OS/390 V6) in production? Similarly any experiences of problems, design issues etc. would be gratefully recieved. [...] 16226 31 27_Re: Specifying A Data Class14_Langley, Moira30_Moira.Langley@VENDEXKBB-ITS.NL31_Thu, 14 Dec 2000 11:04:43 +0100431_iso-8859-1 Michael

If you're on V6 then PQ32414 adds new ZPARM parms SPRMDCFL and SPRMDCIX which allow SMS data classes to be specified for tablespaces and indexes. I don't think there's anything for archive log datasets though.

Cheers Moira

-----Oorspronkelijk bericht----- Van: Dempsey, Michael [mailto:MDempse@UNCH.UNC.EDU] Verzonden: woensdag 13 december 2000 15:42 Onderwerp: Specifying A Data Class [...] 16258 39 54_LIKE predicate available for Oracle and SQL Server ???12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 14 Dec 2000 11:07:28 +0100747_iso-8859-1 Hello Listers,

The LIKE predicate, a SQL-92 standard, works very good within DB2 for OS/390.

My question: Is LIKE supported by Oracle (8.0.5 and higher) and SQL Server also ? And if the answer is yes, are there any pitfalls ?

Thanks in advance for any help you can offer.

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 Technology & Development Center Knowlegde Center DB2 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 [...] 16298 93 21_Re: 'Insert of BLOBs'11_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Thu, 14 Dec 2000 13:17:25 +0100346_us-ascii Hi Siva,

of course, you can insert data into the LOB columns. How:

1. INSERT : a. host variable of type C/BLOB(...) this is similar to the VARCHAR [FOR BIT DATA] (data & length (long int!) components b. or you can use host variables of type [VAR]CHAR [FOR BIT DATA] since they can be automatically converted to C/BLOB [...] 16392 49 32_Re: Online Reorg SHRLEVEL CHANGE16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 14 Dec 2000 07:40:18 -0500449_iso-8859-1 Sorry, this will not do it. The DRAIN subprocess requires the termination of all CLAIMs. Even FOR FETCH ONLY WITH UR causes a CLAIM.

Have the programmers add COMMITs.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: bjnigh@HOUSEHOLD.COM [mailto:bjnigh@HOUSEHOLD.COM] Sent: Wednesday, December 13, 2000 9:18 PM To: DB2-L@RYCI.COM Subject: Online Reorg SHRLEVEL CHANGE [...] 16442 77 40_Re: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 06:54:32 -0600578_iso-8859-1 Nick, I read your comments to a colleague here and he agrees with you. This sort of thing does happen.

Linda

-----Original Message----- From: Cianci, Nick [mailto:Nick.Cianci@TEAM.TELSTRA.COM] Sent: Wednesday, December 13, 2000 7:02 PM To: DB2-L@RYCI.COM Subject: Re: Evaluating CA/Platinum DB2 Utilities



Linda, you could be right <> My suspicions are that tech support for the products is overwhelmed right now because of the buy-out. Perhaps they need more time to settle in, only a suspicion on my part. Someone might correct me. [...] 16520 23 31_FW: Control Center 6.1 question13_Victor, Craig17_CVictor@SANDC.COM31_Thu, 14 Dec 2000 07:06:50 -0600537_iso-8859-1 > Hello all > I'm having a problem with Control Center 6.1 and DB2/VM 6.1. When trying > to add or redefine a column to be not null the procedure fails with an -- > ARI0897E unknown parameter starting at or near position 31. This happens > for decimal or character columns. I'm using the SQLTABLE panel in CONTROL > CENTER doing an option 4 then add the column make it (N) for not null then > make the default value (N) then procedure runs then fails. I checked > IBMLink and found nothing Anyone run into this? > Thanks [...] 16544 93 16_Re: SQLCODE -87916_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 14 Dec 2000 14:15:18 +0100564_us-ascii and it looks like this is second time round with no reply :-) ........

"check the EBCIDIC CCSID field (#8) on panel DSNTIPF. If it is 0, and you use DDF, you will have to correct the ccsid before running the dsntijsg job.

If you are not running DDF, then these stored procedures will not work. These are the stored procedures required to run the DB2 Control Centre, which runs on a client. DDF is required for these. If you do not want this function, just skip the step that is creating the procedures. You can always create them later [...] 16638 75 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 14 Dec 2000 18:25:23 +0530274_- Hi , I think what everyone is trying to point out in this thread is the "perfect utilization of the write I/O", means if 32 pages can be written in one I/O then why write 3 or 5 or less than that. Please correct me if i misunderstood anything.

Regards Sanjeev [...] 16714 71 43_Re: Packages in syspackage, can't be freed?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 14 Dec 2000 18:28:11 +0530549_- Please ignore if your problem is resolved because it is just a guess. > I am not sure because i have not seen the detailed output message anytime > but i can see that FHDB2T is the qualifier added to your package name.Is > this ok ? This looks to me the subsystem name. Does this get added in the > normal scenario ? > > Your Command - FREE PACKAGE > (VMPASB.ASD110.(2000-11-07-17.05.58.216014)) > Output says - > FHDB2T.VMPASB.ASD110.(2000-11-07-17.05.58.216014) DOES NOT EXIST > > > As i said, i am not sure but is it worth checking it ? > [...] 16786 147 16_AW: SQLCODE -87912_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 14 Dec 2000 14:36:04 +0100317_iso-8859-1 You have received a SQLCODE -879 (SQLSTATE 53095) because the system does not have an appropriate CCSID defined for the encoding scheme specified.

It might be a good idea to check if your new function tries to use a object that is defined as MIXED DATA, GRAPHIC, VARGRAPHIC or LONG VARGRAPHIC. [...] 16934 68 21_Re: 'Insert of BLOBs'16_Michael McCarthy18_mmccarthy@DTCC.COM31_Thu, 14 Dec 2000 09:04:27 -0500435_us-ascii Hi Siva,

The easiest way to insert and retrieve BLOB data on the ascii platforms is through file reference variables. I have some sample C code that does just that. If you would like the samples, email me directly, and I will send them to you.

-Mike.









dsi.listsrv@DB.COM on 12/13/2000 11:43:09 PM

Please respond to DB2 Data Base Discussion List [...] 17003 89 40_Re: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 08:16:25 -0600368_iso-8859-1 Correction: We do have one agency that uses PDA in production. However, we are trying to get them to use the CDB automation because it is more efficient.

Linda

-----Original Message----- From: Billings, Linda Sent: Wednesday, December 13, 2000 1:53 PM To: 'DB2 Data Base Discussion List' Subject: RE: Evaluating CA/Platinum DB2 Utilities [...] 17093 107 34_Re: Xpediter and Stored Procedures0_26_JCameron@MSI-INSURANCE.COM31_Thu, 14 Dec 2000 08:14:28 -0600303_us-ascii Eric, If the stored procedure is written in COBOL, we just set up a stub program to call it and test the stored procedure like any other COBOL sub-program. After the testing, recompile it as a stored procedure and define it to the system. Low-Tech, but it works.

Best of Luck, John [...] 17201 101 60_Re: April 25th-26th - Central PA User Group - Sheryl Larsen,17_Armando Zaporteza25_azaporte@CI.PHOENIX.AZ.US31_Wed, 13 Dec 2000 12:31:42 -0700446_US-ASCII HI!!!



Could you let me know when are you going to have the same class in the Phoenix, AZ area or Los Angeles?



Thank you.



Armando



______________________________ Reply Separator _________________________________ Subject: April 25th-26th - Central PA User Group - Sheryl Larsen, Author: DB2 Data Base Discussion List at INTERNET-MAIL Date: 12/13/00 12:10 PM [...] 17303 114 37_Re: Simple but annoying SQL statement13_Lockwood Lyon19_Lockwool@MEIJER.COM31_Thu, 14 Dec 2000 09:34:03 -0500571_US-ASCII Raymond,

Glad you were able to solve this! Looks like this proves the worth of the list to us all (in case anyone needs an example to show management ...). I didn't really want a Team Telstra T-shirt (what's it look like, anyway?), but I remain a bit puzzled. When I Explain-ed your original query on our own system (actually a functional equivalent) I got a very nice indexed access to "T1"; hence, my wondering why on earth your original would do a TS scan. That's why my interest in your Plan-Table. I also wondered about the ClusterRatio of your [...] 17418 107 40_Re: Evaluating CA/Platinum DB2 Utilities16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 14 Dec 2000 14:58:24 -0000488_iso-8859-1 Hi list,

To remove any confusion wether PDA is V6 exploitative or not - P99C fully supports:

8K + 16K pagesize, REBUILD INDEX syntax as well as COPY INDEX, UDT's, LOB and ROWID are recognized and statistics collected as well as some new action conditions. TRACKMOD and MEMBER CLUSTER are supported - and instead of having to execute an initial RUNSTATS on partitioned objects before catalog update can execute - PDA support INSERTs into the SYSSTATS tables. [...] 17526 17 34_Re: Xpediter and Stored Procedures9_Eric Kwai18_Eric_Kwai@DTCC.COM31_Thu, 14 Dec 2000 08:56:31 -0600478_- John: This is an excellent idea. We were having the same issue in our shop, and your testing method will save us a bundle. Many Thanks. One question, what do you do to SP that return result set?

Thanks in advance. Eric Kwai 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. 17544 32 40_Re: Evaluating CA/Platinum DB2 Utilities14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 Dec 2000 15:03:26 -0000417_iso-8859-1 Firstly,

It's nice to see that we DO have some friends out there in DB2-land - reading some of the posts recently I was sort f beginning to wonder.....

Anyway, I just wanted to take a couple of minutes to clarify some points about V6 toleration/exploitation and V7 toleration in the CA DB2 tools (and, yes I still keep calling them the PLATINUM tools too - it's a hard habit to break!!) [...] 17577 62 35_Transactions deadlocking on inserts22_Kirkland, Andrew (FR1)18_kirkawp@RBOS.CO.UK31_Thu, 14 Dec 2000 15:01:28 -0000473_- We are experiencing the following problem and would appreciate any assistance that the list can give, as we are at a loss as to a solution :

Two occurrences of the same transaction are deadlocking with each other when they try to Insert into the same physical page. The contention is between a physical data page of a Partition and variably the Header (Page 000), Space Map (Page 001) or 1st page of a Compression Dictionary (Page 002) of the same Partition. [...] 17640 36 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 14 Dec 2000 10:11:44 -0500363_ISO-8859-1 Yes, but you have absolutely NO Control over how many pages are written for an I/O. Therefore, if the current avg pages/write is a single digit, then VDWQT should be lowered, and "usually" to ZERO - unless you are on V6 with a large pool.... then you can specify (0,xxx) where xxx is the number of pages that will trigger the write. Regards, Joel [...] 17677 80 40_Re: Evaluating CA/Platinum DB2 Utilities9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Thu, 14 Dec 2000 08:19:11 -0700442_iso-8859-1 Howdy Phil,

I'm glad to see your response. I have been using Platinum DB2 tolls for more than five years and have been very satisfied with them. Having been spoiled on the Technical Support side (It wasn't uncommon to get one of the tool's Developers) when I had a problem. Now, since most of them seem to have "flown the coop" I usually get someone who seems to be still learning the product when I call Tech Support. [...] 17758 26 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?7_DB2 fan16_askdb2@YAHOO.COM31_Thu, 14 Dec 2000 09:36:46 -0600571_- I dont think it is anything to do with the utilization of I/O. I think it is in the effective utilization of CPU. Incidentally I am looking at a USENIX paper on the same subject. The fact is 'Idleness is sloth'. You don't want the CPU to be idle at any time. By triggering the I/O at frequent intervals, you are in essence triggering additional processing. Now this is a very generalized statement that would apply to all operating systems. In a typical large computing environment I/O is handled by reduced instruction processors. The actual CPU will not care to [...] 17785 115 39_Re: Transactions deadlocking on inserts0_19_Tim.Lowe@STPAUL.COM31_Thu, 14 Dec 2000 09:40:58 -0600323_us-ascii Andrew, What DB2 release are you on? If V5, are you using type-2 indexes? Are you using DB2 data sharing? Have you considered using MEMBER CLUSTER on the tablespace? (V5 Apar PQ02897) If you are not using incremental copy, have you considered altering the tablespace to TRACKMOD NO?

I hope this helps. [...] 17901 101 40_Re: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 09:43:35 -0600504_iso-8859-1 Don,

You must be one of the lucky ones. Unfortunately, that was not our experience. I have had better support from other vendors and other products. Recent support experiences with CA have been average to below average. Nothing to write home about as the expression goes.

Linda

-----Original Message----- From: Don Alden [mailto:Don.Alden@I-STRUCTURE.COM] Sent: Thursday, December 14, 2000 9:19 AM To: DB2-L@RYCI.COM Subject: Re: Evaluating CA/Platinum DB2 Utilities [...] 18003 77 34_Re: Xpediter and Stored Procedures0_26_JCameron@MSI-INSURANCE.COM31_Thu, 14 Dec 2000 09:42:59 -0600459_us-ascii Eric, We currently only have one stored procedure that returns a result set, and the programmer who wrote it isn't here today. I think he made a copy of his code and changed it to send output parms for 1 row. When his XPeditor testing was done, we wrote a COBOL stub that called it as a stored procedure (instructions in the Application Programming and SQL Guide 6-72 for V5) to make sure it actually sent the result sets. Again, very low tech. [...] 18081 88 40_Re: Evaluating CA/Platinum DB2 Utilities9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 08:58:06 -0700517_us-ascii If you want to use Platinum to support any V6 features you need the P99 code.

Don Alden wrote: > > Howdy Phil, > > I'm glad to see your response. I have been using Platinum DB2 tolls for > more than five years and > have been very satisfied with them. Having been spoiled on the Technical > Support side (It wasn't > uncommon to get one of the tool's Developers) when I had a problem. Now, > since most of them seem to > have "flown the coop" I usually get someone who seems to be still learning [...] 18170 26 40_Re: Evaluating CA/Platinum DB2 Utilities10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 14 Dec 2000 16:52:35 +0100459_us-ascii Hi listers....and Hi Linda.

I agree with you, Linda. My very recent experience (Hi Mauro and others .....) with CA/Platinum toolz and utilitiez is not very positive. We had problem with the products itself and with the tech support (especially out of Italy).

I'm Phil Grainger's 'friend' (I hope and I would like to continue to be a 'friend') but some difficulties must be known (for instance to obtain a setup parmilb member... ) [...] 18197 118 40_FW: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 09:59:41 -0600523_iso-8859-1 I should add that my experience with Platinum itself was that people in the field seemed to show a lot of enthusiasm for resolving your issues but there was very little follow up. It was sort of like being left at the altar. I even got visited by a vice president in charge of something or other because I was so frustrated with PDA. (Remember, this was 8 years ago.) I even wrote up a paper outlining everything that was a problem and I got no follow-up, not even a thank you. So I remain a bit skeptical. [...] 18316 124 22_Re: Language Interface12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 14 Dec 2000 07:59:54 -0800578_us-ascii James, I agree with you. For some shops its a real problem.

Roger, My current client has an application with several hundred subprograms. Some of these are IMS only, some are DB2 only, some are both and some are neither. Its a real MAJOR problem getting some of the main driver programs linkedited correctly with all of these subprograms due to the DSNHLI module being the same name for IMS and DB2 but working differently. There several main programs where some of them are IMS and some of them are DB2 only that needed to call the same subroutine. (IBM's [...] 18441 40 39_TCP/IP connection to Data Sharing Group12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Thu, 14 Dec 2000 09:57:08 -0600415_- We have installed our first Data sharing group on a Sysplex with two LPARS. The location name is DB0A. I have one member running on each LPAR, DB1A and DB1B I am trying to use TCP/IP to connect to the group from a differant Sysplex. The first member comes up with the following. -DB1A DDF START COMPLETE LOCATION DB0A LU MOSS.LUDB1A GENERICLU -NONE DOMAIN db0a.sdcplex1.state.mo.us TCPPORT 1224 RESPORT 1225 [...] 18482 72 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 14 Dec 2000 10:04:10 -0600602_ISO-8859-1 Sanjeev, This subject was pretty well covered in the archives; consensus of opinions and experiences were to use "trickle out writes".

Joel, I agree with you overall. However, we do have some control over the number of pages written per I/O in that we can group (either on purpose or accidentally) table/indexspace into bufferpools that have a good locality of reference. This would tend to force pages written per I/O up. I don't think I understand all I think I know about this fact as it relates to VDWQT(0,nnn) and would be very interested in your opinion. Would it actually [...] 18555 39 40_Re: Evaluating CA/Platinum DB2 Utilities9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 09:21:17 -0700553_us-ascii The P99 code has a new features of the SDM install which make it very easy to have the install build the necessary information for the SETUP parmlib member. If you are having trouble send me a note and we can get it taken care of.

-Rob Crane

Max Scarpa wrote: > > Hi listers....and Hi Linda. > > I agree with you, Linda. My very recent experience (Hi Mauro and others > .....) with CA/Platinum toolz and utilitiez is not very positive. We had > problem with the products itself and with the tech support (especially out > of [...] 18595 65 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM31_Thu, 14 Dec 2000 10:21:27 -0600542_ISO-8859-1 DB2 Fan, I agree, a MIP is a terrible thing to waste! However, please consider this. An OS390 image running at 100+ percent most of the time. Scheduling I/O takes the same amount of time regardless of how much data is written per I/O. But, as data written per I/O goes up, scheduling of more and more I/O tasks goes up due to wait. Thus, the capacity of the I/O subsystem is the real key in the equation. If I had a Shark_wiz_bang I/O subsystem I'd probably look at ways to increase data written per I/O thus freeing up OS390 [...] 18661 13 3_LPL17_McGee, Luke (ECS)23_LuMcGee@EXCHANGE.ML.COM31_Thu, 14 Dec 2000 11:14:14 -0500332_us-ascii Does anyone know where the Logical Page List (LPL) is stored in DB2 Land? Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 18675 31 11_bind error?12_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Thu, 14 Dec 2000 10:24:21 -0600394_iso-8859-1 I have (for the first time) installed a new DB2 subsystem on a new LPAR. I am trying to install a BMC product (that I have successfully installed repeated times in the past). The following error is received:

BIND PLAN(ACT612DK) OWNER(TRSA65) QUALIFIER(ACT612D) VALIDATE(BIND) ACTION(REPLACE) PKLIST(*.ACT612_D_MAIN.*)

DSNT245I -DB2P LOCATION * CANNOT BE SPECIFIED [...] 18707 36 32_CA/Platinum DB2 Utilities -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM31_Thu, 14 Dec 2000 11:25:48 -0500628_US-ASCII Greetings everyone!

While we're on the subject of the CA/Platinum tools and support, we've had a problem with RC/Migrator for many months. CA's tech support has been unable to help us resolve this. They keep telling us to rebind their plans which has no effect.

When attempting to create a "compare" type strategy in RC/Migrator to compare an object in 2 different subsystems, a -924 SQLCODE is returned from the "RC/M Create Compare Strategy" screen. Then you are returned to the "RC/M Strategy Services" screen without a strategy having been created. This has been a problem since we first applied [...] 18744 42 40_Re: Evaluating CA/Platinum DB2 Utilities14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 Dec 2000 16:38:24 -0000457_iso-8859-1 Hi Max,

No problem - friendships and business relationships don't always mean the same thing, but certainly I don't take criticism of the company I work for as criticism of me personally! And you are right, sometimes things need airing!

Phil G

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: 14 December 2000 15:53 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Evaluating CA/Platinum DB2 Utilities [...] 18787 22 52_Re: meaning of VDWQT=0 & CASTOUT CLASS THRESHOLD=0 ?7_DB2 fan16_askdb2@YAHOO.COM31_Thu, 14 Dec 2000 10:36:18 -0600553_- True, I agree. I neither disagreed with Joel in general. We all share the same thought to reduce the burstiness in I/O but what I disagree with is the fact that additional CPU cost would not be incurred by doing this. I personally witnessed the CPU spike in DB2 when accidentally some of the BPOOLs were changed to 0. We also had high Async write wait time. So as you rightly said, it depends on the characterstics of I/O sub-system. What if you have a poor I/O configuration and by turning on more frequent little writes, the channel paths were [...] 18810 63 15_Re: bind error?11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US31_Thu, 14 Dec 2000 10:40:36 -0600725_- John:

On this parameter: PKLIST(*.ACT612_D_MAIN.*)

Try it like this: PKLIST(ACT612_D_MAIN.*)

HTH.......



Tony Clark DB2 System Administrator Texas Workforce Commission 15th Congress Ave ADP Room 320 Austin, Texas 78778-0001 E-Mail: tclark@twc.state.tx.us Phone: (512) 463-3079 Fax: (512) 936-3190

> -----Original Message----- > From: Vernon, John [SMTP:John.Vernon@TRS.STATE.TX.US] > Sent: Thursday, December 14, 2000 10:24 AM > To: DB2-L@RYCI.COM > Subject: bind error? > > I have (for the first time) installed a new DB2 subsystem on a new LPAR. > I > am trying to install a BMC product (that I have successfully installed > repeated times in the past). The following error [...] 18874 37 44_Re: FW: Evaluating CA/Platinum DB2 Utilities14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 14 Dec 2000 16:42:07 -0000566_iso-8859-1 Linda,

OK - I know this was WAYYYY back, but I don't suppose you have filed your PDA document away anywhere you could find it????

Now that I have taken on part of the product management for the DB2 tools, I am VERY keen to find out where our products are considered 'deficient' by customers. I'd like to get to the situation where we as a software company actually write tools to solve real problems (sometimes we are all just a little but guilty of writing exciting software 'for the hell of it' and are surprised when no-one wants to [...] 18912 173 16_Re: SQLCODE -87910_CEI, Bruno20_Bruno.CEI@SEMA.CO.UK31_Thu, 14 Dec 2000 16:46:08 -0000327_iso-8859-1 Hi,

I agree that it sounds suspiciously similar to the problem with CREATE PROCEDURE.

Check values for MIXED and all CCSIDS in the DSNHDECP data only module compiled by job DSNTIJUZ. Mixed should be YES and CCSIDs set to valid values. All the info is in the installation manual for panel DSNTIPF. [...] 19086 35 47_Help! - DDF & TCP/IP (DB2 V6 on OS390) overview14_Dale E. Miller15_dmiller@UGI.COM31_Thu, 14 Dec 2000 11:45:38 -0500530_us-ascii I am working on an install of a new V6 subsystem. We want to make use of DDF for the first time and connect to DB2 on OS390(V2.8). The install manual makes the TCP/IP setup look fairly simple. What is confusing is the section on "Defining DB2 to VTAM". Our network guy wants to know "why we are doing this?" and "Is each DB2 subsystem going to be a VTAM node?" and "Where does LU6.2 come into play?". I'm stumped. My knowledge of VTAM ends after I spell it. Can someone explain, in simple terms, what VTAM has to do [...] 19122 165 39_Re: Transactions deadlocking on inserts22_Kirkland, Andrew (FR1)18_kirkawp@RBOS.CO.UK31_Thu, 14 Dec 2000 16:54:24 -0000607_iso-8859-1 > Thanks Tim, > > We are V5 and entirely use Type 2 Indexes. Although the Table in question > is participating in a 3-way data sharing group, all transactions to this > table are local. > > We do have PQ02897 but would prefer not to have to resort to non-clustered > driven inserts (to better support eventual enquiries on the same table). > > -----Original Message----- > From: Tim.Lowe@STPAUL.COM [SMTP:Tim.Lowe@STPAUL.COM] > Sent: Thursday, December 14, 2000 3:41 PM > To: DB2-L@RYCI.COM > Subject: Re: Transactions deadlocking on inserts > > > *** Warning : This message originates from [...] 19288 87 44_Re: FW: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 11:13:08 -0600551_iso-8859-1 Sorry, Phil, that was with my previous employer. I doubt that I would have that paper around. I can check around at home to see if I packed it away somewhere. All I remember was being continually frustrated with each new feature that I tried because it would prove to be a nice idea but impractical. PDA, ultimately for me anyway, turned out to be a product that I would use to spot checking one or two objects but nothing I could use system-wide. The fact that someone else is using it for SAP shows that perhaps some improvement has [...] 19376 42 7_Re: LPL15_Toine Michielse18_vndobtm@US.IBM.COM31_Thu, 14 Dec 2000 18:11:31 +0000749_us-ascii Hello Luke,

LPL information is stored in the DBET.

Regards,

Toine Michielse





"McGee, Luke (ECS)" @RYCI.COM> on 12/14/2000 04:14:14 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: LPL





Does anyone know where the Logical Page List (LPL) is stored in DB2 Land? Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 19419 14 35_Anyone using UDB 7.1 Federated DBs?14_Roy R Reynolds20_Roy_Reynolds@GAP.COM31_Thu, 14 Dec 2000 09:08:51 -0800306_us-ascii I'd be interested in knowing your experiences. TIA, Roy

================================================ 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. 19434 128 44_Re: FW: Evaluating CA/Platinum DB2 Utilities6_hhardy17_hhardy@SWBELL.NET31_Thu, 14 Dec 2000 11:18:01 -0600376_iso-8859-1 While with CA I did much follow-up on technical issues in the filed one on one with clients.... Keep in mind that very few people in the field per sales are technical so they not aware or really can do anything to resolve an issue....

Its important to call the tech support line and you may have to place several calls to get the required attention.... [...] 19563 28 11_DB2 Connect12_Chad Breiner20_glassman87@YAHOO.COM31_Thu, 14 Dec 2000 09:13:47 -0800304_us-ascii I have a basic question about DB2 connect. My client is currently using a DB2 connect gateway to get to the mainframe data. The current direction for my client is to install DB2 on all PC's. Are there any advantages of going thru a db2 connect gateway rather than directly to the database? [...] 19592 18 16_ORACLE FINANCIAL10_Elsie Tama24_elsie.m.tama@VERIZON.COM31_Thu, 14 Dec 2000 08:29:46 -0500435_us-ascii I live in the Metropolitan Wsahington area and I'm looking for somewhere to trained as an ORACLE FINANCIAL ANALYST OR specialist. Any ideas????

Thanks,

Elsie 703-974-2605

================================================ 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. 19611 79 15_Re: bind error?16_Aurora Dell'Anno26_aurora.dellanno@SISTINF.IT31_Thu, 14 Dec 2000 17:52:10 +0100519_us-ascii John,

quoting (as always):

"...The problem occurs when an '*' is specified as a location-id in the PKLIST of a bind (i.e. *.COL.PKG) and DDF has not been loaded. The location token in the DSNT245 message is invalid."

HTH.

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

no trees were killed in the sending of this message. however - a large number of electrons were seriously inconvenienced. [...] 19691 157 44_Re: FW: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 11:34:03 -0600419_iso-8859-1 I didn't just talk to sales folks. I also talked to techies who were sent out by someone (the SE? This was 8 years ago, after all.) to help me with the problems. They still couldn't help. They went away and never came back. No follow up. I also remember talking to tech support. We also had people come out to educate us about the products but we turned out to be more knowledgable than the instructor. [...] 19849 22 39_Re: Transactions deadlocking on inserts0_19_Tim.Lowe@STPAUL.COM31_Thu, 14 Dec 2000 11:39:20 -0600394_us-ascii Andrew, I have not seen deadlocks on insert-only transactions using type-2 indexes. Are you sure that these are only inserts, and there are no deletes or updates?

If all accesses to this table are froma single system in your sysplex, then can I assume that this tablespace is not group-bufferpool dependent? (and therefore ALTER tablespace ....TRACKMOD NO would not help?) [...] 19872 62 20_TRIGGER SEQUENCE #'S12_Craig Reiter23_Craig_Reiter@MAY-CO.COM31_Thu, 14 Dec 2000 11:34:40 -0600730_us-ascii I'm trying to create a trigger to update a column with a sequence #(s/390 DB2 v6). When I use a COUNT(*) the trigger is created without error but when I use the MAX() function on the mentioned sequencing column I get the error -118. The trigger works on NT.



--------------------------- Command entered ---------------------------- CREATE TRIGGER MFT.TI3COMP AFTER INSERT ON MFT.EV_COMPONENT REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL WHEN (NEW.STATUS_KEY < 0) UPDATE MFT.EV_COMPONENT SET STATUS_KEY = (SELECT COUNT(*) FROM MFT.EV_COMPONENT WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY) WHERE EVENT_KEY = NEW.EVENT_KEY AND COMPONENT_KEY = NEW.COMPONENT_KEY AND STATUS_KEY [...] 19935 174 44_Re: FW: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 11:44:35 -0600524_iso-8859-1 An additional comment, we pay a lot for tech support so I don't feel that once I have initiated a problem report that I should have to chase down tech support all the time in order to get any satisfaction. What kind of customer service is that? If you reported a problem to your car dealer and he took a look at it and then went away and you had to keep pestering him until he resolved the problem would you buy your next car from that dealer? I think not. Especially, if you paid big bucks for the extended [...] 20110 22 68_Re: V6 Migration/Missing colon - REXX to regenerate new format DBRMs12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 14 Dec 2000 11:42:53 -0600359_- Thanks to Venkat and Merrill Lynch for this. For customers who lost source code or received a vendor package without source, this is probably the best technique for migration.

Those who have the source code are better off changing the source if they can, but this will help some to migrate sooner, knowing that a rebind will not cause failures. [...] 20133 74 51_Re: Help! - DDF & TCP/IP (DB2 V6 on OS390) overview22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 14 Dec 2000 12:49:54 -0500421_iso-8859-1 Dale -

Yes, The VTAM requirements must be satisfied to use DDF & TCP/IP. DDF "base" code (i.e. before TCP/IP) requires VTAM resources. I was told that the DDF code that supplies the TCP/IP support uses DDF "base" code. TCP/IP support needs the same functions (e.g. reading the SYSDDF database) supplied by the DDF "base" code. Sharing code makes sense from a development and support point of view. [...] 20208 100 44_Re: FW: Evaluating CA/Platinum DB2 Utilities9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 11:08:40 -0700407_us-ascii Sounds like you had a very old version of PDA. All of the things you describe are not issues with the current version of PDA. Personally I think PDA rocks! You can literally do whatever you want if you take the time to understand the product and build your own models. It is very flexible. If you ever get the chance to use it again send me a note and I can get you up to speed on it quickly. [...] 20309 103 51_Re: Help! - DDF & TCP/IP (DB2 V6 on OS390) overview22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 14 Dec 2000 13:06:46 -0500489_iso-8859-1 Hit the send button too soon.

Yes, each non-data sharing subsystem needs its own unique LU. Similar to each non-data sharing DB2 subsystem needing its own primary and recovery TCP/IP port numbers.

Before TCP/IP, DDF needed to support multiple concurrent connections to DB2 through one DDF address space. DDF wouldn't be very useful if it was one connection per DDF address space. Hence, DB2's need for a multi-session LU6.2 LU, not a single session LU6.2 LU. [...] 20413 158 44_Re: FW: Evaluating CA/Platinum DB2 Utilities12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 14 Dec 2000 10:17:11 -0800627_us-ascii Its been about 4 years since I've used PDA, but when I used it at one client site, we had it automatically reorg tables and not indexes, pick up easily all the tablespaces within a given database, select when to reorg/image copy, generate exception reports, the entire nine yards.

I used it for a major Peoplesoft installation, doing almost everything with it, including setting up multiple test versions of the Peoplesoft environments quickly as the developers needed it. At one time, there were over 15 different Peoplesoft databases plus that many INDUS PASSPORT instances being managed within one DB2. [...] 20572 80 36_Re: CA/Platinum DB2 Utilities -Reply9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 11:23:48 -0700547_us-ascii I have not seen this before at the sites where I support the products. It sounds like you are doing a subsystem to subsystem compare. Are the 2 subsystems on the same LPAR or separate? Are the 2 subsystems part of a data sharing group. Depending on the answers to these questions you might have to look at the SETUP and MIGRATOR members of PARMLIB to insure they are set up correctly. Assuming you have DDF between the two subsystems give this a try. This will use DDF to connect to the target and get the information it needs while [...] 20653 133 44_Re: FW: Evaluating CA/Platinum DB2 Utilities15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 14 Dec 2000 12:38:36 -0600300_iso-8859-1 Yes, I did mention that this evaluation occurred 8 years ago and yes, it was a very old version of PDA. It is nice to hear that the product has improved since then. However, we have pushed off into a different direction since then. PDA missed its chance. Thanks for the offer though. [...] 20787 94 36_Re: CA/Platinum DB2 Utilities -Reply9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 11:43:58 -0700561_us-ascii If you are in a rush and don't have time to monkey with the setup member, etc., you could also do a compare without involving DB2, so you won't get the connection error. Not a solution just a work around.

1) Use a Migration strategy on both source and target side. Migrate all objects to a flat file. 2) Use a compare strategy that compares flat files instead of the db2 subsystems. If your test and production subsystem don't have shared dasd, you will need to get the flat file that contains your Migrator Production DDL over to the test [...] 20882 74 15_Re: DB2 Connect12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 14 Dec 2000 10:39:52 -0800584_us-ascii Each has its pros and cons. It really depends. I won't attempt to give all of the pro's and con's but some major that I know about:

Gateway: Advantages 1) Common interface - don't have to install as much on each user PC; 2) Don't have as many connections directly to DB2 - can use Gateway connection pooling for some performance advantages Disadvantage: 1) must carefully size gateway server to handle loads; 2) another single point of failure that must be addressed - if gateway down all users down even if DB2 up. 3) Performance needs must be carefully watched [...] 20957 69 56_Re: Using stored procedures with JDBC (DB2 v5 on OS/390)15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Thu, 14 Dec 2000 11:30:20 -0800707_iso-8859-1 We're doing it. Works fine. Check the \SQLLIB\SAMPLES\JAVA subdirectory for sample code. See StpCli. Regards, Steve

-----Original Message----- From: Slot, JP (Jaap) [mailto:J.P.Slot@RF.RABOBANK.NL] Sent: Wednesday, December 06, 2000 12:43 AM To: DB2-L@RYCI.COM Subject: Using stored procedures with JDBC (DB2 v5 on OS/390)



Hello all,

In my shop we have various Intranet applications whom are connecting to DB2 on OS/390 via an NT-machine using DB2 connect EE. Those JAVA applications use dynamic SQL with JDBC, DB2 Connect EE, and SNA. We don't like having dynamic SQL on a production database. Therefore we want to implement stored procedures. We are on DB2v5, [...] 21027 90 15_Re: DB2 Connect13_McHugh, Gerry21_MchughG@DIVINVEST.COM31_Thu, 14 Dec 2000 14:15:46 -0600381_iso-8859-1 One more advantage of gateway - single point of administration for tuning purposes. If it's PC directly to DB2, then everyone running the admin client on their PC would almost need to be a DBA.

-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Thursday, December 14, 2000 1:40 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect [...] 21118 107 15_Re: DB2 Connect12_Kline, Wayne27_wayne.kline@INGRAMMICRO.COM31_Thu, 14 Dec 2000 12:21:02 -0800528_iso-8859-1 Re: then everyone running the admin client on their PC would almost need to be a DBA.

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

-----Original Message----- From: McHugh, Gerry [mailto:MchughG@DIVINVEST.COM] Sent: Thursday, December 14, 2000 12:16 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect



One more advantage of gateway - single point of administration for tuning purposes. If it's PC directly to DB2, then everyone running the admin client on their PC would almost need to be a DBA. [...] 21226 61 36_Re: CA/Platinum DB2 Utilities -Reply13_Olson, Carlos14_COlson@QRS.COM31_Thu, 14 Dec 2000 12:29:28 -0800511_iso-8859-1 My memory may be failing but I think I remember something like this where we had to rebind a package with the REOPT(VARS) option. We are using P97G on OS390 V2.6 with OS390 DB2 V6.1

Carlos Olson Database Administrator QRS Corporation

510.215.3873 voice 510.621.3873 fax colson@qrs.com



-----Original Message----- From: John Bucaria [mailto:JBUCARIA@MAIL2.PCH.COM] Sent: Thursday, December 14, 2000 8:26 AM To: DB2-L@RYCI.COM Subject: CA/Platinum DB2 Utilities -Reply [...] 21288 68 31_DB2 stored procedures questions22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM31_Thu, 14 Dec 2000 15:31:47 -0500627_iso-8859-1 We are currently at DB2 v5.1 and WLM in compatibility mode. The customer wants to start using stored procedures and is asking about having multiple WLM-established stored procedures address spaces. In looking at the red book Getting Started with DB2 Stored Procedures: Give Them a Call through the Network it mentions that if you want multiple WLM-established stored procedures address spaces you would have to start them manually, or have some automation tool start them. My question is if you start multiple address spaces, for example, three, how would the work be distributed? Is that a function of the WLM [...] 21357 117 43_Re: CA/Platinum DB2 Utilities -Reply -Reply12_John Bucaria22_JBUCARIA@MAIL2.PCH.COM31_Thu, 14 Dec 2000 15:59:20 -0500430_US-ASCII Thanks for the tip Rob. Our subsystems are on the same LPAR and are not part of any data sharing group. The PARMLIB members appear to be correctly setup. A test of the scenario you suggested again yielded the -924.

I'm following up on a suggestion which came from Steen Rasmussen (CA Scandinavia) related to a DB2 PTF which solved an identical problem one of his customers had not long ago. Looks promising ! [...] 21475 76 50_FW: EXPORT command on DB2 UDB v6.1 on AIX and LOBS13_Doctor, Nadir24_Nadir.Doctor@BGEHOME.COM31_Thu, 14 Dec 2000 11:26:55 -0500864_iso-8859-1 I'm afraid that there isn't an easy way out for this - What I did in a similar situation was as follows:

export to f:\pics_events_data\pics_events_data_subset01.txt of del lobs to f:\pics_events_data\lobsdata\ lobfile 001, 002, 003, 004, 005, 006, 007, 008, 009, 010 modified by lobsinfile messages f:\pics_events_data\pics_events_subset01_emsg.txt select * from dba.pics_events where EVENT_NUM between 2001 and 11990 ; export to f:\pics_events_data\pics_events_data_subset02.txt of del lobs to f:\pics_events_data\lobsdata\ lobfile 011, 012, 013, 014, 015, 016, 017, 018, 019, 020 modified by lobsinfile messages f:\pics_events_data\pics_events_subset02_emsg.txt select * from dba.pics_events where EVENT_NUM between 11991 and 21980 ; export to f:\pics_events_data\pics_events_data_subset03.txt of del lobs to f:\pics_events_data\lobsdata\ [...] 21552 99 39_Re: Transactions deadlocking on inserts15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Thu, 14 Dec 2000 22:41:46 +0100521_us-ascii Robin,

what's the exact message of the deadlock?

Some time ago a deadlock was mentioned by somebody where it all came down to contention on the highest row-id (recorded if I'm not mistaken in the header page). I can imagine something similar happening to a space map (highest page number perhaps) but I fail to see for the moment how a compression page might be affected (maybe somebody else has an idea on this). The remedy used then I believe was a random insert instead of a clustered one. [...] 21652 15 15_Re: DB2 Connect0_19_Tim.Lowe@STPAUL.COM31_Thu, 14 Dec 2000 16:00:07 -0600473_us-ascii At the DB2 Tech conference I heard about another "gateway" option, running the DB2 Connect V7.1 on Linux/390 for performance and scalability. I have not tried this, but is sounded like an interesting option for some cases.

================================================ 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. 21668 102 35_Re: DB2 stored procedures questions22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 14 Dec 2000 17:28:41 -0500389_iso-8859-1 WLM in compatibility mode and in goal mode assigns the work based on WLM service definition. An added benefit of goal mode is that WLM will start/stop the stored procedures address spaces automatically based on servicing the WLM goals for DB2 stored procedures.





Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749 [...] 21771 29 25_DB2 Referential Integrity15_David A. Zelmer25_david.zelmer@CITICORP.COM31_Thu, 14 Dec 2000 17:03:29 -0600535_us-ascii Listers,

In looking at RI, I have a question regarding the removal and then subsequent reactivation of RI. Do the application packages need to be rebound if RI is removed and later added back?

The reason for this is due to third party products at a "hot-site" backup. In order to replicate DB2 data we can not have RI defined at the shadow site. During DR, we would have to activate RI. After the DR, we would need to remove RI. I am wondering the implications to the application packages when this occurs. [...] 21801 25 27_Anyone using Brio with DB2?16_Maculsay, Gary R28_Maculsay.Gary@EMERYWORLD.COM31_Thu, 14 Dec 2000 23:16:58 -0000516_iso-8859-1 We're about open up our DB2 OS/390 environment a bit to allow "efficient" web-enabled reporting via Brio. We also use Brio for reporting against our Oracle-based data warehouse.

We currently use DB2 Connect EE on Solaris, which will support the production queries from the Brio web portal. We have not configured a developer machine yet (NT), as we have not decided whether they will have direct DB2 connectivity (via DB2 Connect PE or the run time client), along with the Brio/DB2 interface. [...] 21827 25 17_DSNREXX and ALTER26_=?iso-8859-1?q?DB2=20DBA?=19_db2ibm@YAHOO.COM.AU31_Fri, 15 Dec 2000 10:32:48 +1100656_iso-8859-1 Hi All,

Is it possible to issue the ALTER TABLESPACE statement from the DB2 for OS/390 DSNREXX interface?

If so could someone provide an example of the EXECSQL statements needed ?

Thanks

_____________________________________________________________________________ http://au.yahoo.com/shopping/giftguide/christmas/jumppage.html - Yahoo! Gift Guides - Looking for inspiration?

================================================ 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. 21853 72 58_Re: LIKE predicate available for Oracle and SQL Server ???14_Tennant, Steve24_Steve.Tennant@ATO.GOV.AU31_Fri, 15 Dec 2000 10:03:21 +1100678_iso-8859-1 **************************************************************** IMPORTANT

The information transmitted is for the use of the intended recipient only and may contain confidential and/or legally privileged material. Any review, re-transmission, disclosure, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may result in severe penalties. If you have received this e-mail in error please notify the Privacy Hotline of the Australian Taxation Office, telephone 13 2869 and delete all copies of this transmission together with any attachments. [...] 21926 51 37_Re: Simple but annoying SQL statement15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 15 Dec 2000 10:32:55 +1100400_- Lockwood,

Can't tell you what a Telstra T-shirt looks like; I don't work for them. I work for CPT Global, neither company having responsibility for what I may or may not say on this list. Hmmm, sounds familiar... Anyway, T1 might have had matchcols=2 if it was more than 58% clustered. I knew T2 was a better table to go to first; I just didn't know how to tell DB2 that. But I do now. [...] 21978 35 29_Re: DB2 Referential Integrity9_Rob Crane22_racrane@CONCENTRIC.NET31_Thu, 14 Dec 2000 16:46:01 -0700526_us-ascii No, the database manager handles RI not the packages.

"David A. Zelmer" wrote: > > Listers, > > In looking at RI, I have a question regarding the removal and then > subsequent reactivation of RI. Do the application packages need to be > rebound if RI is removed and later added back? > > The reason for this is due to third party products at a "hot-site" > backup. In order to replicate DB2 data we can not have RI defined at > the shadow site. During DR, we would have to activate RI. After the > DR, we [...] 22014 24 31_Re: Anyone using Brio with DB2?10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Thu, 14 Dec 2000 17:47:42 -0600477_- Don't know all the particulars. Arizona State think it's www.asu.edu implemented Brio as their client for data warehousing. They cross query Sybase, DB/2, and maybe SQL servers. It's a very impressive production(they've been here a couple of times to give the pitch). Think they started out at $25 a seat and it's up to $750 a seat. Think Dr.John Porter is head of OIR and John Rolm was the DBA at the time. For sensitive updates use it in conjunction with Cold Fusion. [...] 22039 62 21_Re: DSNREXX and ALTER14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 15 Dec 2000 10:55:00 +1100549_iso-8859-1 Well, I've never done an alter, but have you tried

ADDRESS DSNREXX "EXECSQL ALTER TABLESPACE ..."

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

-----Original Message----- From: DB2 DBA [mailto:db2ibm@YAHOO.COM.AU] Sent: Friday, December 15, 2000 10:33 AM To: DB2-L@RYCI.COM Subject: [DB2-L] DSNREXX and ALTER



Hi All,

Is it possible to issue the ALTER TABLESPACE statement from the DB2 for OS/390 DSNREXX interface? [...] 22102 41 35_Re: DB2 stored procedures questions11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Thu, 14 Dec 2000 16:20:28 -0800403_iso-8859-1 Tom, you set up WLM environments to start the address spaces. Then when you create your storedprocedure (or UDF) you assign it to one of WLM environments created. When a program calls the SP the WLM will start the address space for the defined environment and the SP will run in it. BTW you must also have the RRS task installed and running to use WLM address spaces. have fun, cliff:-) [...] 22144 64 35_Re: DB2 stored procedures questions22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM31_Thu, 14 Dec 2000 19:41:22 -0500738_iso-8859-1 WLM will start/stop the address spaces in Goal Mode. WLM will NOT start/stop the address spaces in Compatibility Mode



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



-----Original Message----- From: Cliff Boley [mailto:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Thursday, December 14, 2000 19:20 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 stored procedures questions



Tom, you set up WLM environments to start the address spaces. Then when you create your storedprocedure (or UDF) you assign it to one of WLM environments created. When a program calls the SP the WLM will start the address space for the defined environment and the SP will [...] 22209 102 24_Re: TRIGGER SEQUENCE #'S13_Terry Purcell25_terry_purcell@YLASSOC.COM31_Thu, 14 Dec 2000 18:42:52 -0600465_iso-8859-1 Craig,

The problem (as far as I can tell) is that the update statement within your trigger is self-referencing. Although valid in non-OS/390 DB2, an update statement cannot be self-referencing until DB2 OS/390 V7.

/snip/ UPDATE MFT.EV_COMPONENT SET STATUS_KEY = (SELECT MAX(STATUS_KEY) FROM MFT.EV_COMPONENT /snip/

Regards Terry Purcell Terry_Purcell@YLAssoc.com Yevich Lawson & Associates WWW.YLASSOC.COM WWW.DB2-SYMPOSIUM.COM [...] 22312 66 21_Re: DSNREXX and ALTER26_=?iso-8859-1?q?DB2=20DBA?=19_db2ibm@YAHOO.COM.AU31_Fri, 15 Dec 2000 12:12:47 +1100501_iso-8859-1 Thanks James,

I tried that without any luck. I got -518 , so I tried to prepare it and got -504, so I tried to declare it and got -518.

Any ideas.

Thanks

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



Well, I've never done an alter, but have you tried

ADDRESS DSNREXX "EXECSQL ALTER TABLESPACE ..."

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...] 22379 106 58_Re: LIKE predicate available for Oracle and SQL Server ???12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Thu, 14 Dec 2000 20:28:19 -0600325_iso-8859-1 The syntax for LIKE in DB2 6+ is identical to Oracle 8+. AFAIK it is also the same in prior releases of both.

The only difference I know is that Oracle usually considers an empty string to be the same as NULL and DB2 does not.

Best regards, John P. Higgins Performance Specialist SAP BASIS Team [...] 22486 104 21_Re: DSNREXX and ALTER14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 15 Dec 2000 13:31:17 +1100459_iso-8859-1 Well, I just tried what I suggested and it worked for me. Could you post your actual code.

BTW, which version of DB2, and any PTFs.

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

-----Original Message----- From: DB2 DBA [mailto:db2ibm@YAHOO.COM.AU] Sent: Friday, December 15, 2000 12:13 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DSNREXX and ALTER [...] 22591 139 21_Re: DSNREXX and ALTER26_=?iso-8859-1?q?DB2=20DBA?=19_db2ibm@YAHOO.COM.AU31_Fri, 15 Dec 2000 14:02:51 +1100434_iso-8859-1 James,

Problem solved. You are right it does work, I had a commit statement that was causing the -518.

Thanks again.

-----Original Message----- From: James Campbell [mailto:James.Campbell@HANCORP.COM.AU] Sent: Friday, 15 December 2000 13:31 To: DB2-L@RYCI.COM Subject: Re: DSNREXX and ALTER



Well, I just tried what I suggested and it worked for me. Could you post your actual code. [...]