1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2003, week 5 2 90 67_Re: DB2 v8 Client and db2fmcd creating a bunch of defunct processes5_db2it18_teamdba@SCOTDB.COM31_Mon, 29 Dec 2003 08:18:17 +0000548_- Mark,

Didn't see a reply to this, so I'll do my best.

The db2fmcd (Fault Monitor Co-ordinator Daemon) definitely does need to be running on the server. In Version 8 this sits at the very lowest level and is responsible to start the instances and restart them if they come down for any unexpected reason. You'll find that this is now what gets started in inittab (in V7 the instances themselves were started in inittab). To have an instance started by db2fmcd at boot time you need to set environment variable DB2AUTOSTART=YES. [...] 93 117 12_Re: DSNU255I13_Bell, Raymond22_raymond.bell@LANDG.COM31_Mon, 29 Dec 2003 09:34:36 -0000421_iso-8859-1 Yo nguyen (sorry, don't know your name),

I could be way off base here, but these messages stem from a failed load. What's happened is, I think, your load has been loading rows into the tablespace and then failed in the load phase for some reason. You're now trying to restart the utility (big clue: the utility Id is RSTART.blah-blah) but haven't preserved the work files from the first execution. [...] 211 391 25_The Beast that is the OTG13_Bell, Raymond22_raymond.bell@LANDG.COM31_Mon, 29 Dec 2003 09:55:44 -0000561_iso-8859-1 Cathy/Myron,

My sympathies to you both for having to work with the OTG. At my last role I had to look after the damn thing. 4.0.1.1 I think was the release. Mainframe-based, purely to allow Oracle clients to get at our precious DB2 data. Two STCs I think, both as flaky as each other. Wanted to swap it for something - anything - else but never managed it. DB2 Connect wouldn't have worked for us, as the clients were all Oracle/AIX. I believe (and I'm quite prepared to be shot down on this) that these gateway-type products are good at [...] 603 169 12_Re: DSNU255I14_Dash, Sushanta21_sushanta.dash@EDS.COM31_Mon, 29 Dec 2003 04:43:39 -0600506_iso-8859-1 Hi Raymond, though I had not experienced the scenario like you explained but slightly disagree with this. May be due to lack of knowledge. Probably you may be knowing more so that it can rectify me. But I guess with all the installation DFP is installed and once the DFP is installed it have macros which enables to reposition during restart and there is no chance of losing the work-files or its contents. I m having less knowledge about this and can be rectified by the experts like you. [...] 773 174 12_Re: DSNU255I14_Dash, Sushanta21_sushanta.dash@EDS.COM31_Mon, 29 Dec 2003 07:46:34 -0500573_iso-8859-1 Dear Raymond , I understood after a bit research and this is the reason probably we have been practicing the recovery once there is failure in reload phase not doing any restart. If you have more answers to this I would gladly accept that. Restart is possible with consistent record only in SORTKEY case and in other case it would produce the wrong result. I was asking why even the DFP is installed why restart of reload phase wont give correct/consistent data. I checked DFP and it says even the macros are there to consistent information in files but it [...] 948 42 15_Re: DB2 Connect8_Ron Root24_ron.root@CPA.STATE.TX.US31_Mon, 29 Dec 2003 08:30:31 -0600419_iso-8859-1 We use Neon's Shadow and are very happy with it. It is used by a variety of Powerbuilder, Java and decision support applications.

Ron Root Texas Comptroller of Public Accounts



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Fazio, Paul Sent: Monday, December 22, 2003 7:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 Connect [...] 991 458 15_Re: DB2 Connect0_19_mike.holmans@BT.COM31_Mon, 29 Dec 2003 14:56:25 -0000446_iso-8859-1 We have the OTG for DRDA running on some flavour of Unix or other (HP/UX or Solaris, I can't remember off-hand). It works a *lot* better than the configuration we had with the mainframe component. We have an issue with our users, who seem somewhat hard of thinking because we keep telling them to get the userid that the gateway uses set up right, but they don't, it expires and then they tell us the gateway has stopped working. [...] 1450 164 47_DB2 Connect Enterprise Edition license question22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Mon, 29 Dec 2003 09:36:47 -0600437_iso-8859-1

Resending the message. The original did not make it. This is a test as well.

Thanks,

Venkatesh

-----Original Message----- From: Venkatesh Mokshagundam Sent: Tuesday, December 23, 2003 2:30 PM To: 'DB2 Database Discussion list at IDUG' Subject: RE: DB2 Connect Enterprise Edition license question



Its been a while since I have done it, but, I think the following is still true. [...] 1615 28 34_Explicitly starting a transaction?16_Jonathan Gennick20_jonathan@GENNICK.COM31_Mon, 29 Dec 2003 11:03:08 -0500316_us-ascii Does DB2 support a statement similar to Oracle's SET TRANSACTION, that lets you explicitly start a transaction and specify the isolation level for that transaction? I've looked in the DB2 docs, but if such a statement exists, it's name is not obvious enough for me to pick it out.

Best regards, [...] 1644 82 38_Re: Explicitly starting a transaction?0_20_simon.george@UBS.COM31_Mon, 29 Dec 2003 16:27:16 -0000388_iso-8859-1 I think the simple answer is no. In DB2 (specifically z/OS) isolation levels are supported by DB2 either in its static SQL model via the BIND ISOLATION(CS/RR/UR/RS) parameter or in the dynamic SQL model by specifying the WITH CS/RR/UR/RS on the statement. Likewise read only is specified on the SELECT statement using the FOR READ ONLY clause at the end of the statement. [...] 1727 150 57_Re: Effect of the host variable definition on access path12_Boone, Henry16_HBoone@GEICO.COM31_Mon, 29 Dec 2003 14:09:22 -0500423_iso-8859-9 Host variable information is stored in the DBRM. If the DBRM is v2.3 or newer, DSNXNDBRM in SDSNMACS has the layout. You should be able to eyeball the DBRM and find the statement. Just after the statement is the host variable section (XBRMPVAR in the macro).

If you'd like, e-mail me off-list with the DBRM attached and I'll run it through a REXX we have that spits-out much of the DBRM information. [...] 1878 15 33_PREFETCH Change from Blank to "L"10_Jane Leyba19_jane.leyba@NAII.ORG31_Mon, 29 Dec 2003 13:38:28 -0600680_us-ascii We have a program that has been running successfully on a regular basis in which a REBIND is done on a weekly basis. The PREFETCH mode was BLANK until 3 weeks ago it suddenly changed to "L" with no apparent changes made. Can anyone explain what might cause this?

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to listserv@www.idugdb2-l.org. The IDUG List Admins can be reached [...] 1894 46 33_Re: DB2 and the MVS System Logger14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Mon, 29 Dec 2003 13:47:57 -0600412_us-ascii John, I know that this change has NOT been made in version 8, and I would be surprised if DB2 ever went to use the MVS system logger since DB2 utilizes its logs in a manner that is much different than other (non-DB2) logging strategies. Wayne Driscoll Sr. Software Developer Quest Software wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...] 1941 68 37_Re: PREFETCH Change from Blank to "L"13_Mark A. Doyle19_mdoyle@JCPENNEY.COM31_Mon, 29 Dec 2003 13:53:06 -0600443_iso-8859-1 Most likely explanation: your table has grown large enough for the optimizer to conclude that it makes sense (from an I/O perspective) to prefetch to the pages into buffer in advance of your need.

HTH Mark ----- Original Message ----- From: "Jane Leyba" Newsgroups: bit.listserv.db2-l To: Sent: Monday, December 29, 2003 1:38 PM Subject: PREFETCH Change from Blank to "L" [...] 2010 29 37_AW: PREFETCH Change from Blank to "L"33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Mon, 29 Dec 2003 21:52:03 +0100594_iso-8859-1 Clusterratio for the index > 80%?

Roland



-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Jane Leyba Gesendet: Montag, 29. Dezember 2003 20:38 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: PREFETCH Change from Blank to "L"



We have a program that has been running successfully on a regular basis in which a REBIND is done on a weekly basis. The PREFETCH mode was BLANK until 3 weeks ago it suddenly changed to "L" with no apparent changes made. Can anyone explain what might cause this? [...] 2040 198 38_Re: Explicitly starting a transaction?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 29 Dec 2003 13:29:25 -0800554_iso-8859-1 APAR PQ47595 introduced a "new stored procedure for calling a CICS transactions". However, this may not be the kind of transaction you're asking about, or the right platform. A CICS transaction does not have an isolation level; that is a characteristic of SQL statements. HTH, Cathy Taddei

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of simon.george@UBS.COM Sent: Monday, December 29, 2003 8:27 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Explicitly starting a transaction? [...] 2239 147 38_Re: Explicitly starting a transaction?12_tim malamphy20_timalamphy@YAHOO.COM31_Mon, 29 Dec 2003 14:06:35 -0800600_us-ascii I read the original question a bit differently... From what I know about Oracle, it sounds to me like the gentleman is looking for something along the lines of START UOW command...and wants to do it explicitly. As I understand it, Oracle uses transactions like DB2 uses UOW to process commits/rollbacks, etc. DB2 "implicitly" starts a UOW when it encounters a SQL statement, and stops it upon COMMIT. So you could say that executing any SQL statement (and I'm sure there are exceptions) is an explicit way of starting a transaction. SQL statements allow you to set the Isolation level. [...] 2387 449 15_Re: DB2 Connect13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 29 Dec 2003 15:36:13 -0800562_iso-8859-1 Hi Mike. How does the gateway communicate with DB2 on OS/390? Are you using an ODBC driver, or something else? We would like to do something like this -- any information you can provide would be welcome. Email me offline if you prefer. Thanks! Cathy Taddei Cathy.Taddei@pacificorp.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of mike.holmans@BT.COM Sent: Monday, December 29, 2003 6:56 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 Connect [...] 2837 186 29_Re: The Beast that is the OTG13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 29 Dec 2003 15:43:00 -0800556_iso-8859-1 Raymond, my story is much worse than it sounds. I'm STILL on Release 4.0.1.1.0 (afaik, the cost of upgrading is the cost of the product). NOT ONLY do I take care of OTG for 3 DB2 subsystems, BUT... users connect to it using _Oracle on the mainframe_! I have 3 Oracle instances on my mainframe, whose sole function in life is to allow users to log on and use the gateway. It was really funny last year when I turned on password checking in the gateway. Nobody in security could log on to the Oracle instances to change anyone's long-expired [...] 3024 39 39_Quest Central for DB2 (OS/390 and z/OS)13_Bell, Raymond22_raymond.bell@LANDG.COM31_Tue, 30 Dec 2003 10:49:43 -0000408_iso-8859-1 Colleagues,

Does anyone have any experience with Quest Software's product, Quest Central for DB2? Specifically the one to administer DB2 on OS/390 and z/OS. I see from a casual glance at their website the only version that would be appropriate for what we want would be the Professional Edition. Does anyone (that's not a sales person) have any comments to make regarding the product? [...] 3064 20 33_Re: DB2 and the MVS System Logger0_16_mscarpa@CESVE.IT31_Tue, 30 Dec 2003 14:09:14 +0100761_us-ascii Dr Jim Teng said (personal communication during a seminar) that they are thinking to modify DB2's log mechanism to disable logging under some circumstances (as ORACLE do), but using the actual logging structure. He didn't talk about other MVS resources.

Regards

Max Scarpa DB2 sysprog

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to listserv@www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out [...] 3085 18 26_Date Value and Access Path14_Ben Eisenstein22_beisenstein@THEOCC.COM31_Tue, 30 Dec 2003 09:28:45 -0600520_- We have a query where the access path changes based on a predicate date with all other things being held constant. The table with the date has the same values for all rows. The high2key and low2key are the same. The value (lets call it COLA is '2003-12-29'. It has 166,000 rows and is joined to other tables. If I set the predicate to COLA = '2003-12-31' it runs fine. If I set the predicate to COLA='2004-01-01 it chooses a completly different access path, which is terrible (goes from 2 seconds to 20 minutes!) [...] 3104 70 30_Re: Date Value and Access Path0_23_Bill.Kebea@WAKEFERN.COM31_Tue, 30 Dec 2003 10:47:49 -0500372_us-ascii Take a look at the cardinality for '2003-12-29' and '2004-01-01'. Since you are giving it an actual value the optimizer knows the cardinality and will choose a path based on this. You might try running an explain on the query where you give the date a host variable. The optimizer will then assume even distribution and then choose a different path as well. [...] 3175 19 43_Re: Quest Central for DB2 (OS/390 and z/OS)10_Tom Willis24_tom_r_willis@HOTMAIL.COM31_Tue, 30 Dec 2003 10:56:57 -0600 3195 21 30_Re: Date Value and Access Path14_Ben Eisenstein22_beisenstein@THEOCC.COM31_Tue, 30 Dec 2003 11:36:55 -0600308_- The cardinality is that there is only one date value in the table. The cardinality is 1, there is only one date value in the table for 166,000 rows and that value is '2003-12-29'.

If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I use '2004-01-01' it chooses a bad path. [...] 3217 19 10_map tables10_cass cheng11_cass@DR.COM31_Tue, 30 Dec 2003 13:46:02 -0500 3237 20 14_Re: map tables0_18_jaruddy@US.IBM.COM31_Tue, 30 Dec 2003 12:56:47 -0600436_- Well, among other reasons, if you don't use a segmented tablespace REORG will give you an error message and won't run.

Segmented tablespaces are almost always more efficient than simple tablespaces. In the case of Online REORG, nothing is actually stored in the data, just the index is used. Multiple mapping tables are allowed in a single tablespace and no lock contention occurs. That would not be possible with simple. [...] 3258 59 16_Update SQL Query16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 30 Dec 2003 11:10:54 -0800309_us-ascii Table T1 has columns C1 and C2. Table T2 has columns C1 and C2. All columns are not-nullable.

For rows in tables T1 and T2 that match on C1 (WHERE T1.C1 = T2.C1), I want to update T1.C2=T2.C2; something along similar lines:

UPDATE T1 SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1) [...] 3318 40 30_Re: Date Value and Access Path10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 30 Dec 2003 13:39:53 -0600624_- Hi Ben,

High2key and Low2key values are char(8) fields. Thus, the data stored there is '2003-12-'. Thats probably the reason for the difference in your access paths.

Regards, Kals.

On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein wrote:

>The cardinality is that there is only one date value in the table. The cardinality is 1, there is only one date value in the table for 166,000 rows and >that value is '2003-12-29'. > >If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I use '2004-01-01' it chooses a bad path. > >Curiously '2003-12-01' [...] 3359 72 20_Re: Update SQL Query10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 30 Dec 2003 13:54:15 -0600385_- Hi Raquel,

Are u sure about you got -104 ? I would expect to see -407, which is the sqlcode for 'column cannot contain a null value'.

Obviously there are some C1 values in T1 that dont exist in T2. You could add a WHERE EXISTS clause to your update.

UPDATE T1 SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1) WHERE EXISTS (SELECT 1 FROM T2 WHERE T1.C1=T2.C1) [...] 3432 17 25_Dynamically add partition3_Bob21_bherring@TXFB-INS.COM31_Tue, 30 Dec 2003 13:48:44 -0600337_- We have several partitioned tablespaces and our shop standard is to define them with 35 partitions. Something about having 35 years worth of data.

My question is: Can partitions be added dynamically? It would be so much easier to define them with say numparts 5 and then add a partition each year as the new data comes in. [...] 3450 47 29_Re: Dynamically add partition13_Fung, Chi-Yun23_Chi-Yun.Fung@UNISYS.COM31_Tue, 30 Dec 2003 13:08:09 -0700295_iso-8859-1 Bob,

You can not dynamically add a new partition. In V7 you can only dynamically change the part key range. But then you have to reorg after the alter. In V8 you can roll off the oldest partition and change the part key range to the new range - in your case the new year. [...] 3498 105 20_Re: Update SQL Query13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 30 Dec 2003 17:08:46 -0300466_US-ASCII Raquel,

this works:

UPDATE T1 SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1) WHERE EXISTS (SELECT C2 FROM T2 WHERE T1.C1=T2.C1)

Your T1 table has values that don't exist in T2.

Regards & happy new year,

Horacio Villa







Raquel Rodriguez cc: Sent by: DB2 Data Subject: Update SQL Query Base Discussion List [...] 3604 201 21_BMC Apptune questions11_Chad Walmer19_cwalmer@RITEAID.COM31_Tue, 30 Dec 2003 15:50:02 -0500328_us-ascii

I have a couple of questions for any current BMC Apptune users:

1. Do you use the sampling feature for collection of data? If so, have you had any problems with missing SQL statements or SQL errors?

2. If you don't have sampling turned on, has the CPU overhead of the product been a concern? [...] 3806 13 29_Re: Dynamically add partition0_18_jaruddy@US.IBM.COM31_Tue, 30 Dec 2003 15:39:22 -0600624_- You will have to wait until V8 to be able to add partitions.

Jim Ruddy DB2 for z/OS Development

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to listserv@www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3820 20 15_No More Handles7_Kishore21_kishore_db2@YAHOO.COM31_Tue, 30 Dec 2003 16:21:40 -0600393_- Hi List,

Off late we get these errors in the online application. "[IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 " . It started happening when we upgraded to DB2 V7 on S/390. Our online application is written in java and use webshpere 5.0 and DB2 connect V7 on AIX 5.0. We tried many things but none of it helped. Any suggestions or solutions are greatly appreciate. [...] 3841 11 29_Re: Dynamically add partition13_Bobby Herring21_bherring@TXFB-INS.COM31_Tue, 30 Dec 2003 16:38:07 -0600566_- That is what I needed to know. Thanks, Bobby

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to listserv@www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3853 45 19_Re: No More Handles14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 30 Dec 2003 17:38:53 -0500445_iso-8859-1 Make sure you close your Statement, PreparedStatement, CallableStatement & ResultSet objects when done with them. Keeping too many of these open and then trying to open fresh ones can give this error.

Abhijit

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Kishore Sent: Tuesday, December 30, 2003 5:22 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: No More Handles [...] 3899 61 11_DB2 Connect12_Gab Gambassi20_gabriele@NZ1.IBM.COM31_Wed, 31 Dec 2003 12:07:56 +1300297_ISO-8859-1 Oracle Gateway for DB2 comes in two flavours: Transparent and DRDA.

The Transparent consist of two STCs running on OS/390 or z/OS, the DRDA consist of a bunch of processes running on AIX or Solaris (I think this are the only two opions) and works like a DB2 client via DDF. [...] 3961 91 20_Re: Update SQL Query14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Wed, 31 Dec 2003 00:20:21 +0100247_- -104 is a syntax error. If you really got -104, I would try to use a table alias on the update statement and use this alias in the subquery. I always do it that way, but I'm not sure if it is only a question of style or a real requirement. [...] 4053 112 20_Re: Update SQL Query10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 30 Dec 2003 17:47:10 -0600507_- Hi Bernd,

The subquery does seem to return a null if no rows are found. Following is an extract from the SQL Reference (UPDATE statement) :

" scalar-fullselect Specifies a fullselect that returns a single row with a single column. The column value is assigned to the corresponding column-name. If the fullselect returns no rows, the null value is assigned; an error occurs if the column to be updated is not nullable. An error also occurs if there is more than one row in the result. " [...] 4166 236 15_Re: DB2 Connect13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 30 Dec 2003 16:11:40 -0800582_iso-8859-1 Thanks for the clear and concise explanation, Gabriele!

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Gab Gambassi Sent: Tuesday, December 30, 2003 3:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 Connect



Oracle Gateway for DB2 comes in two flavours: Transparent and DRDA.

The Transparent consist of two STCs running on OS/390 or z/OS, the DRDA consist of a bunch of processes running on AIX or Solaris (I think this are the only two opions) and works like a DB2 client via DDF. [...] 4403 61 12_Re: DSNU255I13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 31 Dec 2003 04:40:02 GMT410_- -- "Bell, Raymond" wrote: [Snip]

PS. Anyone heard from Mr. Waugh lately?



ZZZZZZZZZZZZZZZZZZZZ...Snort! Huh? Wazzit? WhereamI?

Oh, sorry -- must have fallen asleep last time I crawled back under my rock... Thanks for wakin' me up Raymond -- would've slept right through New Year's Eve if it hadn't been for you!

nguyen: On the DSNU255I topic, [...] 4465 145 20_Re: Update SQL Query16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 30 Dec 2003 22:28:23 -0800568_us-ascii Thank you VERY much Kals and Horacio. Your suggested queries worked like a charm.

Appreciate it. Raquel.

--- Horacio Villa wrote: > Raquel, > > this works: > > UPDATE T1 > SET C2 = (SELECT C2 FROM T2 WHERE T1.C1=T2.C1) > WHERE EXISTS (SELECT C2 FROM T2 WHERE T1.C1=T2.C1) > > Your T1 table has values that don't exist in T2. > > Regards & happy new year, > > Horacio Villa > > > > > Raquel Rodriguez > DB2-L@WWW.IDUGDB2-L.ORG > @YAHOO.COM> cc: > Sent by: DB2 Data > Subject: Update SQL Query > [...] 4611 76 30_Re: Date Value and Access Path14_Dash, Sushanta21_sushanta.dash@EDS.COM31_Wed, 31 Dec 2003 01:45:55 -0500558_iso-8859-1 Hi Kal, the data is not stored the way you expect. Please take a look at HIGH2KEY and LOW2KEY with hex value from syscolumn table for any column with type DATE.

With thanks Dash

DB2 / IMS Database Administration Tel: 91 ( 44 ) 28113801. X-2366. India Solution Center Fax: 91 (44 ) 28113790. Chennai. E-mail : sushanta.dash@eds.com Dash ____________________________________________________________________________ _____________ "The views expressed are my own and do not necessarily represent the views or policy of my employer" [...] 4688 361 19_Happy New Year 200411_Aysel Boncu17_aboncu@TAI.COM.TR31_Wed, 31 Dec 2003 08:52:10 +0200602_iso-8859-1

Happy New Year 2004,

Wish you in the next year 2004 Good Health Fw Merry Christmas & Happy Ne5.gif,Good appetiteFw Merry Christmas & Happy Ne6.gifIf you are single , wish you find your lover Fw Merry Christmas & Happy Ne7.gif ,and get married soonFw Merry Christmas & Happy Ne8.gif ;If you are married, wish you love each other more, Fw Merry Christmas & Happy Ne9.gifand have a smart little babyFw Merry Chri! stmas & Happy Ne10.gif ;If you are a man, wish you more handsome Fw Merry Christmas & Happy Ne11.gifand strongerFw Merry Christmas & Happy Ne12.gifIf you are a [...] 5050 115 15_Re: DB2 Connect0_19_mike.holmans@BT.COM31_Wed, 31 Dec 2003 10:50:33 -0000577_iso-8859-1 Gab,

I don't think you're exactly right. The Oracle Transparent Gateway used to come in two flavours - OTG for DB2 and OTG for DRDA. I have a vague feeling that the OTG4DRDA used to run with a similar mainframe configuration as the other one because DB2 was unable to speak TCP/IP and Orrible unable to speak SNA. The OTG4DB2 operated a simpler connection than the DRDA one -quite possibly basic ODBC. When we trialled the two versions many years ago, we found that the DRDA version performed worse than the other at low volumes, but was to be preferred [...] 5166 370 32_Re: suspect: Happy New Year 200426_Dell'Anno, Aurora Emanuela33_Aurora.DellAnno@BANKOFAMERICA.COM31_Wed, 31 Dec 2003 11:05:50 -0000464_- tesekkurlar Aysel'hn.

Iyi yeni yillar!

ciao!

Aurora Emanuela Dell'Anno Database Analyst Data Services Group - Bank of America tel. 66192 ext. 0208 760 6192 aurora.dellanno@bankofamerica.com

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER

no trees were killed in sending this message. However, a large number of electrons were seriously inconvenienced :-) [...] 5537 51 29_Collection names - any ideas?13_Bell, Raymond22_raymond.bell@LANDG.COM31_Wed, 31 Dec 2003 10:03:09 -0000311_iso-8859-1 Colleagues,

Just a quickie. I'm trying to track down the origin of a couple of collection names we have here. I think they belong to products we don't have installed anymore. The removal seems to have left these behind. Anyway, do any of you know what product these collections are from? [...] 5589 137 20_Re: Update SQL Query14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Wed, 31 Dec 2003 12:46:36 +0100298_- Thank you, I didn't know that.

But then, the error should not have been a -104, and the solution with COALESCE should have worked, IMO.

No, I see now: the subquery returns NULL, if nothing is found. The COALESCE only would help, if NULL was found, but not, if no row is found. [...] 5727 117 11_Trigger UOW13_Brett Walters16_BWalters@UGI.COM31_Tue, 30 Dec 2003 18:22:56 -0500361_-

Is there any way to make DB2 (ver 6.1) treat the following process as a UNIT OF WORK?

Action on Table *> Kicks off Trigger *> Trigger calls DB2 SP *> Associated COBOL program updates different Table

We have tried the "NO CASCADE BEFORE" option on the trigger, but this option does not allow the triggered action to contain updates. [...] 5845 206 15_Re: Trigger UOW14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 31 Dec 2003 13:48:18 -0000384_iso-8859-1 Hi Brett

Anything you do from a Trigger body is part of the SAME unit of work as the UOW that caused the trigger to fire.

If this is not what you are seeing then there may be a problem



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 6052 326 25_Re: BMC Apptune questions0_21_paul.weissman@UBS.COM31_Wed, 31 Dec 2003 09:41:43 -0500557_iso-8859-1 Chad,

We have BMC Apptune and have implemented sampling to reduce the CPU overhead. With sampling the traces are only turned on about 25% of the time. As a ROT, for frequently used SQL, the relative stats like CPU%, getpages per open, etc. are still reliable, but the "absolute" CPU-time, counts, etc. need to be multiplied by 4. It is quite possible to completely miss infrequent SQL errors or statements when sampling is turned on. It's a good tool but not a replacement for DB2 accounting, traces or a good error-handling routine. [...] 6379 31 28_DB2 Peoplesoft financials v80_26_Jeffrey_Frazier@WENDYS.COM31_Wed, 31 Dec 2003 10:25:55 -0500326_us-ascii Hello Everyone and a Happy New Year. We are DB2 V7 running under z/OS v1.4. We are testing peoplesoft v8 financials within our test LPAR. The DBA's start up the peoplesoft process schedulers. They run fine for a couple of days then we start getting S878 rc10's. we also get other nasty messages from omvs like : [...] 6411 109 33_Re: Collection names - any ideas?0_28_Missy.Case@FIRSTDATACORP.COM31_Wed, 31 Dec 2003 09:41:53 -0600306_US-ASCII Raymond,

Hey - Happy New Year. I'd lean towards Platinum. Our Database is PTDB & many of the collections look similar to ours. The DTMM looks like - maybe - Development, ? If you used to have the Platinum suite & no longer do, that's what I'd think.

Missy Case FDR 701-275-6358 [...] 6521 65 32_Re: DB2 Peoplesoft financials v812_Bob Galeotti22_bgaleotti@SOFTBASE.COM31_Wed, 31 Dec 2003 11:02:12 -0500547_iso-8859-1 Hi Jeff, These people may be of help: http://www.peoplesoftpros.net/net/faq/faqs.asp?fldAuto=5 They have a forum via this email on their web page mailto:faq@peoplesoftpros.net

HTH,

Bob Galeotti, Sales Tech Support SoftBase Systems, Inc. 800.669.7076 x372 bgaleotti@softbase.com http://www.softbase.com/

----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, December 31, 2003 10:25 AM Subject: DB2 Peoplesoft financials v8 [...] 6587 84 32_Re: DB2 Peoplesoft financials v80_16_FRUSA@BCBSIL.COM31_Wed, 31 Dec 2003 10:49:11 -0600359_us-ascii We are running version 8.4 Financials on version 7 of DB2 and no problem with the process scheduler on Os390. Are you up to date on all the DB2 fixes ? I assume you are using USS - ? We bring our process scheduler down every day so that DB2 catalog image copies and DB2 database image copies will run clean. Are you leaving it up all the time ? [...] 6672 57 30_Re: Date Value and Access Path14_Ben Eisenstein22_beisenstein@THEOCC.COM31_Wed, 31 Dec 2003 12:09:42 -0600691_- I've verified and '2003-12-29' is being stored as hex in high2key and low2key.

(I've run runstats since my original post, the bizzare date behavior is still occurning, almost like if your not in the same month the optimizer does something different!)

Ben





On Tue, 30 Dec 2003 13:39:53 -0600, Teldb2kals wrote:

>Hi Ben, > >High2key and Low2key values are char(8) fields. Thus, the data stored there >is '2003-12-'. Thats probably the reason for the difference in your access >paths. > >Regards, >Kals. > >On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein >wrote: > >>The cardinality is that [...] 6730 92 30_Re: Date Value and Access Path8_Ron Root24_ron.root@CPA.STATE.TX.US31_Wed, 31 Dec 2003 13:40:46 -0600408_iso-8859-1 Ben, Do you have any entries in SYSIBM.SYSCOLDIST for that column? I assume not, based on your comments. But that is one possible explanation.

Ron

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Ben Eisenstein Sent: Wednesday, December 31, 2003 12:10 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Date Value and Access Path [...] 6823 31 33_Re: DB2 and the MVS System Logger12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 31 Dec 2003 14:39:18 -0600445_- Why would you want DB2 to use the System Logger? We have looked a couple of times, and do not see a way of using System Logger to improve DB2 performance or availability. The System Logger does not have many key functions that allow the current log process its speed. When we see the ability to write with no force to the log, performance measurements in the 30 MB / sec range, or some major availability improvements, we can look again. [...] 6855 113 30_Re: Date Value and Access Path12_craig patton21_prgpatton@HOTMAIL.COM31_Wed, 31 Dec 2003 20:43:56 +0000524_- Ben,

I think the issue is the fact that the high2 and low2 are the same. It does appear that there is some logic in the optimizer which is allowing index access based on a partial match (6 out of 8 bytes), but seems to stop at 5 bytes (from the pattern described). The question is: Why is the SQL looking for any date other than the 1 date used? If you were to put more realistic data into your table, with a number of varying months, you will see more consistent access path selection. I am not surpised when [...] 6969 64 24_Re: Cascade Delete Locks12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 31 Dec 2003 14:56:53 -0600371_- I don't think these specifics are documented. But what is documented is how to determine the specifics of your locking situation, and what we recommend doing about it. Access to check for a deletion can still require a lock. Is your isolation level CS? What is CURRENTDATA? Is lock avoidance working, if permitted? What are the specifics of the timeout situation? [...] 7034 109 43_Re: Quest Central for DB2 (OS/390 and z/OS)11_Hardy, Dale14_DHardy@MIB.COM31_Wed, 31 Dec 2003 16:02:18 -0500605_iso-8859-1 We are primarily a DB2 OS/390 shop and have had Quest Central V3.1.1 for about 6 months. Our prime motivation for getting it was to have automate migrations and alterations from devwelopment to QA and production.

Our application tend to have a lot of dependent views, aliases, triggers and stored procedures and the tool does an excellent job of identifying all the dependent objects for a table or a set a tables; creating a migration script(s) and executing the migration. It does a great job of extracting SQL from both the performance monitor and directly from DBRMs into a SQL [...] 7144 132 30_Re: Date Value and Access Path14_Ben Eisenstein22_beisenstein@THEOCC.COM31_Wed, 31 Dec 2003 15:36:44 -0600346_- Your supposition about the number of bytes makes makes sense. Also since high2 and low2 are the same, it may assume that any literal date presented outside the range should be super selective and the index makes a good choice. (In this case its not because there are 166,000 rows with identical entries but different from high2 and low2.) [...] 7277 72 22_Re: PRE-Loading EDM ??12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 31 Dec 2003 15:39:06 -0600576_- There are some huge differences in various shops for the time to open data sets. The key techniques are tuning for the VSAM catalog and caching the VSAM catalog in memory, rather than reading from disk all the time. There is also a big difference between the slowest, most overloaded disks and the best performing disks in most shops. Another important part is making parallelism for the open work. We published a benchmark in V7 showing the difference for parallelism of the parts within a table space, with about 4 seconds for 200 partition opens in V6 and 2 seconds [...] 7350 33 18_Re: Union in Views12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 31 Dec 2003 15:51:28 -0600636_- This sounds like another good reason for getting to V7. If you see Jerry Bustamente or Mike Phillips when you check on the timing, please relay season's greetings from me.

Roger Miller



On Wed, 24 Dec 2003 11:05:26 -0600, Swedlund, Delores B wrote:

>We are at version 6 of DB2. It appears 'union' can't be used to create a view. Can anyone suggest an alternate solution? > >Here's my situation: I have 2 tables (table names OLD_DATA and NEW_DATA, for example) with identical structures. The rows on each table are mutually exclusive. I want a view (view name ALL_DATA) [...] 7384 38 38_Re: Explicitly starting a transaction?12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 31 Dec 2003 16:14:57 -0600564_- Here are a couple more thoughts to add. There is no explicit SET TRANSACTION statement, but there are a number of alternatives, depending upon what you need. The isolation level in DB2 can be set on the plan, on the package or statement by statement on the WITH clause. If you want to finish this transaction and start another, then COMMIT does the job. If you want some form of nesting in transactions then SAVEPOINT and ROLLBACK to savepoint may do the job. RRS attach (recommended) and Call Attach have techniques to start a new transaction, as do IMS & [...] 7423 60 18_Re: Union in Views11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 31 Dec 2003 20:04:09 -0600655_- How about a FULL OUTER JOIN? It should do the trick (join on a dummy column or use ON 1=1).

Thanks, Suresh



>From: Roger Miller >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: Union in Views >Date: Wed, 31 Dec 2003 15:51:28 -0600 > >This sounds like another good reason for getting to V7. If you see Jerry >Bustamente or Mike Phillips when you check on the timing, please relay >season's greetings from me. > >Roger Miller > > >On Wed, 24 Dec 2003 11:05:26 -0600, Swedlund, Delores B > wrote: > > >We are [...]