1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2002, week 4 2 116 16_AW: LOAD Utility41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Fri, 22 Mar 2002 08:05:05 +0100326_iso-8859-1 Buz,

I'd check the discard-dataset if the 1.8 million rows - 1 row are there; it might also be useful if you provided the output from your load-job. Could it be that you got an index on your table that prohibits loading duplicate keys ? Do you use 'load .... enforce constraints' ? Just a few thoughts. [...] 119 19 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM31_Fri, 22 Mar 2002 02:30:07 -0600139_us-ascii I will be out of the office from 03/22/2002 until 03/27/2002.

I will respond to your message when I return.



139 70 26_AW: "FOR UPDATE OF" Clause12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 22 Mar 2002 09:22:58 +0100533_iso-8859-1 Good morning, Phil.

instead of FOR FETCH ONLY you can code FOR READ ONLY also.

With kind regards - mit freundlichen Grüssen, Georg H. Peter ---------------------------------------------------------------------- "A computer is like an Old Testament god, with a lot of rules and no mercy." Joseph Campbell



-----Ursprüngliche Nachricht----- Von: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Gesendet am: Donnerstag, 21. März 2002 17:41 An: DB2-L@RYCI.COM Betreff: Re: "FOR UPDATE OF" Clause [...] 210 102 24_Re: TCP/IP setup for DB215_Foweather, Iris30_Iris.Foweather@UK.EXPERIAN.COM31_Fri, 22 Mar 2002 09:08:07 -0000411_iso-8859-1 Hi, I had the same problem last Sunday, what solved it for me was to get the ACID of the DB2 Address space set up correctly as SUPERUSER for Unix System Services. In my case the UID had been set to 0 but no GID had been set up.



Regards,

Iris Foweather Systems Programmer - Software Technical Design Technology Development 0115 934 4312 email iris.foweather@uk.experian.com [...] 313 133 17_Resource type 90316_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Fri, 22 Mar 2002 09:08:53 -0000425_iso-8859-1 Good Morning ,

We have had a strange situation a couple of times lately - where we're unable to understand WHY the dynamic SQL is the cause of timeouts of static SQL transactions. We have seen this with QMF, DSNTEP2 and DSNTIAUL, and we have no clue why a lock is hold for such a long time in DSNDB01. Reason 00C9008E clearly says DEADLOCK or TIMEOUT - but why is the lock being hold for so long time. [...] 447 24 22_DB2 Replication Papers11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Fri, 22 Mar 2002 16:35:16 +0700492_- Hi y'all...

I'm looking for any document/softcopy/articles about DB2 Replication. I've already read the DB2 Replication Guide & Reference, but I still need some other references/case studies to deploy a solution for our client.

I'm looking for a detail overview of DB2 Replication Architecture and Network Configuration/Requirement.

Can anybody help me?

T.I.A

endy@lambey.net

"To the fool, he who speaks wisdom will sound foolish" - Euripides [...] 472 15 34_Roger Tieche is out of the office.12_Roger Tieche19_Roger_Tieche@TD.COM31_Fri, 22 Mar 2002 04:47:17 -0500397_us-ascii I will be out of the office starting 03/22/2002 and will not return until 03/25/2002.

I will respond to your message when I return. 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. 488 30 3_WG:41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Fri, 22 Mar 2002 12:05:44 +0100588_iso-8859-1 Hi everyone out there in (OS/390-)DB2-land,

a colleague of mine and I are having a discussion whether REORG INDEX is really worth the while or not; while I'm inclined to say that reorging one's indexes (without reorging the underlying tablespace) might still be beneficial to one's performance (Nlevels e.g. is used for determining the access path, as far as I know), my colleague says no, stating that, e.g., CARDF/FAROFFPOSF/LEAFDIST from Sysindexpart aren't used for the access path anyway. Is this a 'it depends'-question, or is one of us (hopefully I) right ? [...] 519 117 34_Re: After Triggers - DB2 OS/390 V610_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 22 Mar 2002 22:20:09 +1100595_us-ascii Hi Mario,

Just a thought. Was ur database created prior to v5 by any chance ? APAR PQ42601 might be your solution. ( PROBLEM DESCRIPTION: SQLCODE20100 occurs when attempting to create a trigger. The message tokens on the SQLCODE20100 indicate that the original error was SQLCODE189.)

Check the CCSID of the database in SYSIBM.SYSDATABASE. This is the one inherited by ur tables in the database. It might not be 37 (which is the default for EBCDIC). If this is your problem and if you dont have the PTF applied, then u might try altering the CCSID of the database and [...] 637 21 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 22 Mar 2002 13:12:15 +0100539_us-ascii Thanks to all (actually very few) who provided some numers (but I'm waiting for other number from different

people/shops). This confirm my idea that IBM's sample row length is short and I'll use these 'mean' value for some tests.

Thank again & best regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 659 99 48_Re: Call a remote DB2 Stored Procedure form CICS12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 22 Mar 2002 05:05:56 -0800354_us-ascii Riyaz, DB2 V6.1 on OS/390 does allow a call to a stored procedure by another stored procedure. My client does it all the time. In one application, there are probably 5 different stored procedures called from one main stored procedure. And all of these are SPB built stored procedures.

But in a way I think you've hit on the problem. [...] 759 19 12_DB2 v5 to v714_DAVID PETERSEN27_dpetersen@KEYSPANENERGY.COM31_Fri, 22 Mar 2002 08:35:20 -0500440_us-ascii Hello Listers,

Anyone have any thoughts, concerns, success , failures about migrating from v5 directly to v7. I would appreciate any feedback.

As Usual Thanks In Advance, 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. 779 148 21_Re: Resource type 90316_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Mar 2002 08:40:52 -0500443_iso-8859-1 The reason the lock is held so long is that there is no COMMIT. This is another argument why dynamic SQL processors (especially QMF!!!) should *NEVER* be allowed in a production TP environment.



Regards, eric pearson NS ITO Database Support

-----Original Message----- From: Rasmussen, Steen [mailto:Steen.Rasmussen@CA.COM] Sent: Friday, March 22, 2002 4:09 AM To: DB2-L@RYCI.COM Subject: Resource type 903 [...] 928 105 36_Re: DB2 V7 quantifiable measurements0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Fri, 22 Mar 2002 07:45:57 -0600371_us-ascii Thanks Roger, This is just what I needed. I will check them out.

I had tried may times to get into the DB2 UDB Server for OS/390 and z/OS Version 7 presentation guide redbook (pdf version) but I continued to get i/o errors when I tried and couldn't get but a page or two into the book before it would give me i/o errors. I'll have to order that one. [...] 1034 137 34_Re: After Triggers - DB2 OS/390 V613_Mario Pacheco26_mario.pacheco@MCMASTER.COM31_Fri, 22 Mar 2002 08:02:30 -0600418_iso-8859-1 Hi Kals,

That was the problem. The database was in fact created a few years ago (prior to V5). Once I dropped it and re-created it the CREATE TRIGGER statement worked fine. Thanks again !!!

Regards, Mario

-----Original Message----- From: teldb2kals [mailto:teldb2kals@TELSTRA.COM] Sent: Friday, March 22, 2002 5:20 AM To: DB2-L@RYCI.COM Subject: Re: After Triggers - DB2 OS/390 V6 [...] 1172 25 7_DSNTEP213_Thomas Schulz20_thomas.schulz@SVI.DE31_Fri, 22 Mar 2002 07:27:39 -0600442_- Hi List User's

we have moved to z/OS this week and since that time, the IBM supplied program DSNTEP2 run into S0C4/Reasoncode 11. We are on DB2 5.1

For example select * from sysibm.sysdabase is running (35 rows). select * from sysibm.systables ist not running (1200 rows)

DSNTEP2 hast a trace flag (0=off/1=on). If I switch on this flag, the select is running and returning all the rows. Does anybody have an idea [...] 1198 74 24_Re: Load Replace Problem14_Judy Woodfield13_judyw@CAE.COM31_Fri, 22 Mar 2002 09:20:24 -0500627_iso-8859-1 We had a similar problem last week. It was related to the character x'15'(new line character) embedded in a text field.

Something to check for

Regards, JudyW, CAE Inc.

-----Original Message----- From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Sent: Thursday, March 21, 2002 6:35 PM To: DB2-L@RYCI.COM Subject: Load Replace Problem



List: Working in DB2 V6 on OS/390. I have a problem with a simple Load Replace job. It seems to be not deleting 4 of the rows that were on the table. The input file has 80 records on it. But after the load there are 84 rows in the table - [...] 1273 45 16_Re: DB2 v5 to v716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Mar 2002 09:21:36 -0500347_iso-8859-1 We did that. We are glad we did. It was *very* smooth. IBM did a tremendous job of QA with V7. Main concerns: 1) Before you do this, get your V5 very current. 2) Before (and after!) you do anything, back up everything. We have not had to use the backups, but had we not had them and needed them it would have been very unpleasant. [...] 1319 19 56_Shan Leatherman/MO/americancentury is out of the office.0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Fri, 22 Mar 2002 08:24:59 -0600547_us-ascii I will be out of the office starting 03/22/2002 and will not return until 03/25/2002.

If you need additional assistance, send a message to *DB or if you need immediate assistance page the primary DBA on call at (816) 292-5449 or contact the help desk (816) 340-4250.

Shan Leatherman

================================================ 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. 1339 174 24_Re: Load Replace Problem19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Fri, 22 Mar 2002 09:12:37 -0500379_US-ASCII Well, from the output of the Load I can see "NUMBER OF INPUT RECORDS PROCESSED=84" which I am pretty sure is the total record count on the input file(s). Did you check to see if you had any input file concatenation for the SYSREC in your JCL ???







Murari Selvakesavan. Data Resource Management. First Health Services Corp. 804.965.7601 [...] 1514 98 24_Re: Load Replace Problem10_Marc Costa28_Marc.Costa@MUTUALOFOMAHA.COM31_Fri, 22 Mar 2002 08:24:29 -0600460_us-ascii Karl,

I think the problem is that you have both RESUME NO and REPLACE in the control cards. If you want to replace all of the data, remove the RESUME NO from the control cards. Is this table the only table in the tablespace?

Marc











"Sniderman, Karl" To: DB2-L@RYCI.COM Subject: Load Replace Problem Sent by: "DB2 Data Base Discussion List" [...] 1613 54 30_Re: Generating Image copy jobs13_Baldon, David20_David_Baldon@BMC.COM31_Fri, 22 Mar 2002 08:26:22 -0600355_- Hi Toni, You may want to take a look at Smart Recover for SAP from BMC. It automatically handles backing up and recovering your SAP DB2 system. Concerning the backups you have a choice of backing up all of them or just the spaces that have been modified since the last time they were backed up. Go to www.bmc.com/smartrecover for more information. [...] 1668 233 45_Re: DSNTPSMP and the Stored Procedure Builder0_19_Tim.Lowe@STPAUL.COM31_Fri, 22 Mar 2002 08:26:19 -0600475_us-ascii Annabelle, Over a year ago, I reported this problem to IBM, and got a committment that they were going to change Stored Procedure Builder so that you could specify EXTERNAL for an SQL stored procedure built using SPB. Therefore, I believe that the manual you are quoting is out of date. But, I don't know what release of SPB is needed or what PTF changed DSNTPSMP. In our case, we modified DSNTPSMP ourselves. But, the project that was using SPB was cancelled. [...] 1902 63 16_Re: LOAD Utility10_James Kwan19_JamesDB2Kwan@CS.COM29_Fri, 22 Mar 2002 09:26:58 EST427_US-ASCII Buz,

May be you can post your JCL job listing here?

James Kwan

IBM Certified Solutions Expert

In a message dated 3/21/02 9:43:06 PM Central Standard Time, buzw@YAHOO.COM writes:



> Hey all, > I'm trying to load a new table. I've got 1.8 million records in my input > file. LOAD runs but only loads 1 record. No error message no nothing. > Any ideas? > Thanks. > Buz Williams > 1966 113 26_Re: "FOR UPDATE OF" Clause0_28_Srinivas.Reddy@IN.EFUNDS.COM31_Fri, 22 Mar 2002 20:12:21 +0530511_us-ascii Thanks a Lot everybody for all the info that was given.

regards, Srini







"Seibert, Dave" cc: Sent by: DB2 Data Subject: Re: "FOR UPDATE OF" Clause Base Discussion List



03/21/02 09:46 PM Please respond to DB2 Data Base Discussion List











Hello Srini,

You've received a number of posts with advice and some level of contradiction. [...] 2080 84 24_Re: Load Replace Problem17_David Eugene Will26_david.e.e.will@VERIZON.COM31_Fri, 22 Mar 2002 09:35:46 -0500485_us-ascii Karl,

If you want to do a load replace take the RESUME out of your control card.

ex: LOAD DATA REPLACE LOG NO INDDN SYSREC00

Dave









"Sniderman, Karl" @RYCI.COM> on 03/21/2002 06:35:26 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Load Replace Problem [...] 2165 95 7_Re: WG:10_James Kwan19_JamesDB2Kwan@CS.COM29_Fri, 22 Mar 2002 09:39:41 EST495_US-ASCII Ruediger,

Your colleague is right about those stats will not affect optimizer. However if an index is in a well reorganized state, those factors can improve the performance. There are other catalog columns such as the one you mentioned NLEVELS in SYSINDEXES will affect access path. The bottom line is yes, it is worth while to reorg index alone if you don't have time to reorg the whole tablespace. The amount of improvement will depend on the current state of your index. [...] 2261 56 11_Re: DSNTEP212_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV31_Fri, 22 Mar 2002 09:43:26 -0500563_iso-8859-1 Hi Thomas,

Make sure you have all the ddnames allocated.....I had a job that ran fine until we upgraded the OS and then abended because a required dd name was missing. The really tricky part as I recall was that the ddname was something simple like sysout that the program wrote 2 lines of info I wasn't interested in.....however, when the program abended the OS dynamically allocated a sysout file to print the error message. I guess in the older version of the os, the two lines were thrown in the bit bucket. The abend I got was an 0c4, [...] 2318 61 11_mass delete10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 22 Mar 2002 07:46:24 -0700274_iso-8859-1 Hi List, We need to delete about 10 million records on our key table each month end. We are wondering if there is more efficient way to do it in addition to the normal DELETE. It looks like we can't use load utility since this key table has IDENTITY column. [...] 2380 42 12_SQL Question16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Fri, 22 Mar 2002 09:46:31 -0500506_iso-8859-1 Hey Folks... We have an audit table that records when a user accessed a specific function of an application. I need to retrieve data from that audit table on the last time a userid accessed that application. The query that I've written returns not the last time a userid accessed the application, but the last time that they've accessed it on a daily basis. The tabledef & query are as follows... Can someone enlighten me on how to return just the last time a user accessed this? Thanks... [...] 2423 54 15_Re: REORG INDEX10_Dale Smock20_Dale.Smock@BMSUS.COM31_Fri, 22 Mar 2002 09:53:45 -0500426_iso-8859-1 One area where we have seen major improvements (minutes vs hours) is reorg index on large NPI prior to partition reorgs of large partitioned tables. It makes a major improvement on the run times during the NPI update phase.

Dale Smock BMG

-----Original Message----- From: "Kurtz, Rüdiger" [mailto:Ruediger.Kurtz@HUK-COBURG.DE] Sent: Friday, March 22, 2002 6:06 AM To: DB2-L@RYCI.COM Subject: WG: [...] 2478 225 45_Re: DSNTPSMP and the Stored Procedure Builder11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 22 Mar 2002 09:54:49 -0500452_iso-8859-1 This SPL syntax works for me. The package created name is the same as the EXTERNAL name in the definition. You must have a setup problem. This is DB2 V6.1 at 0104 maintenance level with DB2 Connect (Stored procedure builder) at V7 (fixpak3 I think).

This proc creates a member in the file pointed to by the SQLCSRC DD in the DB2 WLM SP address space. Additional entries are created in tables SYSIBM.SYSPSM and SYSIBM.SYSPSMOPTS. [...] 2704 78 19_PARTITION LIMIT KEY17_Vijay Subramanyam26_Vijay.Subramanyan@UBSW.COM31_Fri, 22 Mar 2002 15:00:46 +0000567_US-ASCII Esteemed Listers,

Is it possible to specify a NULL in the VALUES clause for a PARTITION LIMIT KEY?

eg.

PSS0IE.POSTING PART 51

Posting has a composite partitioning key. index is XPOST03 Key columns are.. SETT_POSTING_IND LEDGER_TYPE CURRENCY_CODE ACCOUNT_ID CAHI_NSPT_CREATE TSPOSTING

Key values for parts 50 and 51 are....

50 'N','99' 51 'N'

Because the second column (LEDGER_TYPE) is nullable, ALL rows that have SETT_POSTING_IND = 'N' and LEDGER_TYPE of NULL go into PART 51. This is 40% of the table. [...] 2783 56 15_Re: mass delete0_19_Tim.Lowe@STPAUL.COM31_Fri, 22 Mar 2002 09:10:54 -0600369_us-ascii Grace, Is the IDENTITY column defined as GENERATED ALWAYS or GENERATED BY DEFAULT? If it is generated by default, then you could unload the rows you want and use load replace to reload them. Either way, you should be able to use REORG with a DISCARD clause.

But, you don't say how many total rows there are. (is 10 million a majority of the rows?) [...] 2840 77 16_Re: SQL Question11_Riyaz Momin15_RMomin@FHSC.COM31_Fri, 22 Mar 2002 10:13:21 -0500709_US-ASCII Try

SELECT C_DATE, A.C_USERID, B.C_LAST_NAME, B.C_FIRST_NAME FROM DB22SQL.TCISUSAG A, DB22SQL.TCWSUSER B WHERE A.C_USERID = B.CPK_USERID AND A.C_DATE || A.C_TIME || A.C_MILLISECOND = ( SELECT MAX ( C.C_DATE || C.C_TIME || C.C_MILLISECOND) FROM DB22SQL.TCISUSAG C )

Regards, Riyaz Momin. Application Programmer. First Health Services Corp. (804)-421-9051. >>> MDempse@UNCH.UNC.EDU 03/22/02 09:46AM >>> Hey Folks... We have an audit table that records when a user accessed a specific function of an application. I need to retrieve data from that audit table on the last time a userid accessed that application. The query that I've written returns not the last time a userid accessed [...] 2918 99 24_Re: TCP/IP setup for DB211_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Fri, 22 Mar 2002 08:59:28 -0600666_us-ascii You are missing the "DOMAIN yourdomainname". Did you exec the "MAKESITE" CLIST?







Raymond Bell cc: Sent by: DB2 Subject: TCP/IP setup for DB2 Data Base Discussion List



03/21/02 05:25 PM Please respond to DB2 Data Base Discussion List











Help!

OS/390 2.8, DB2 V5.

Don't ask me why, but we've got DB2 happily using TCP/IP for remote connections in Prod, QA and Dev but not in our Sysprog playpen subsystem. So, clever and consistency-loving fool that I am, I thought I'd try to get it to work. [...] 3018 101 24_Re: Load Replace Problem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 22 Mar 2002 15:27:04 -0000337_iso-8859-1

I'd also be tempted to select the 84 rows from the table and compare them with the "80" records in the input file.

The extra 4 should be obvious

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 [...] 3120 87 16_Re: SQL Question31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Fri, 22 Mar 2002 15:20:09 +0000448_iso-8859-1 Hi Michael,

If you are using DB2 V7 for OS/390 then use ORDER BY and FETCH FIRST ROW ONLY will do the trick. For lower version to V7, you need to rewrite the query like this.

SELECT C_DATE, A.C_USERID, B.C_LAST_NAME, B.C_FIRST_NAME FROM DB22SQL.TCISUSAG A, DB22SQL.TCWSUSER B WHERE A.C_USERID = B.CPK_USERID AND C_DATE = (SELECT MAX(C_DATE) FROM DB22SQL.TCISUSAG A, DB22SQL.TCWSUSER B WHERE A.C_USERID = B.CPK_USERID) [...] 3208 54 15_Re: mass delete11_Riyaz Momin15_RMomin@FHSC.COM31_Fri, 22 Mar 2002 10:30:52 -0500528_US-ASCII Hi,

What OS, DB2 version are you on?

Are you worried about filling the log because of DELETE activity, or just want to improve the process?

If you are just worried about filling log and if you are on DB2 V7.1 for windows, unix, you can try this.

If your table is defined with NOT LOGGED INITIALLY, use ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY and issue DELETE statement(s) before you issue any COMMIT. This will make sure that activities between ALTER and COMMIT are not logged in. [...] 3263 124 23_Re: PARTITION LIMIT KEY31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Fri, 22 Mar 2002 15:34:09 +0000602_iso-8859-1 If i am not wrong, NULLs go to the last partition. How do DB2 distinguish between the NULLs to choose which partition it should send to.

Let's see what others have to say.

With Best Regards, Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration for OS/390

--- Vijay Subramanyam wrote: > Esteemed Listers, > > Is it possible to specify a NULL in the VALUES > clause for a > PARTITION LIMIT KEY? > > eg. > > PSS0IE.POSTING PART 51 > > Posting has a composite partitioning key. index > is XPOST03 > Key columns are.. [...] 3388 86 16_Re: SQL Question12_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 22 Mar 2002 09:34:49 -0600412_iso-8859-1 I don't have time to test this, but I thing something like the following would work

SELECT DISTINCT A3.C_DATE, A3.C_USERID, B.C_LAST_NAME, B.C_FIRST_NAME FROM ( SELECT DISTINCT A.C_DATE, A.C_USERID FROM DB22SQL.TCISUSAG A1 WHERE A1.C_DATE = (SELECT MAX(A2.C_DATE) FROM DB22SQL.TCISUSAG A2 WHERE A2.C_USERID = A1.C_USERID)) AS A3 INNER JOIN DB22SQL.TCWSUSER B ON A3.C_USERID = B.CPK_USERID [...] 3475 37 16_Re: SQL Question31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Fri, 22 Mar 2002 15:38:36 +0000456_iso-8859-1 Michael,

Change the alias names for the inner query in the SQL below:-

SELECT C_DATE, A.C_USERID, B.C_LAST_NAME, B.C_FIRST_NAME FROM DB22SQL.TCISUSAG A, DB22SQL.TCWSUSER B WHERE A.C_USERID = B.CPK_USERID AND C_DATE = (SELECT MAX(C_DATE) FROM DB22SQL.TCISUSAG C, DB22SQL.TCWSUSER D WHERE C.C_USERID = D.CPK_USERID)

With Best Regards Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration for OS/390 [...] 3513 84 15_Re: REORG INDEX31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Fri, 22 Mar 2002 15:28:19 +0000317_iso-8859-1 What do we think about index only queries with a high value of LEAFDIST. In the worst case scenario, it will reduce to some good value with only REORG INDEX. I am not sure the %age of chance.

With Best Regards, Sanjeev IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 [...] 3598 83 24_Re: Load Replace Problem17_David Eugene Will26_david.e.e.will@VERIZON.COM31_Fri, 22 Mar 2002 10:41:36 -0500468_us-ascii Karl,

On second read of this, did you check the JCL to make sure the SYSREC00 DD doesn't have a second file concatenated? Specifically, yesterdays load file with the additional four records?

Dave







"Sniderman, Karl" @RYCI.COM> on 03/21/2002 06:35:26 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 3682 76 15_Re: mass delete13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 22 Mar 2002 09:48:37 -0600492_iso-8859-1 How about a reorg with the DISCARD option?

-----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Friday, March 22, 2002 8:46 AM To: DB2-L@RYCI.COM Subject: mass delete



Hi List, We need to delete about 10 million records on our key table each month end. We are wondering if there is more efficient way to do it in addition to the normal DELETE. It looks like we can't use load utility since this key table has IDENTITY column. [...] 3759 17 23_Re: PARTITION LIMIT KEY16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 22 Mar 2002 09:20:40 -0600480_- I think a more important question is why do you have a null value in a key field?

You should try to use normal business definitions for your identifiers, which would lead to a more 'natrual' partitioning key.

good luck, pg

================================================ 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. 3777 24 15_Re: REORG INDEX16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 22 Mar 2002 09:25:07 -0600339_- Howdy,

Considering that access paths are only selected at bind time (for static queries) and that the index in question is already deemed to be used for the path, then if the leaf distribution is bad your performance will be bad.

For dynamic queries, if leafdist is not used, then it probably won't make a difference. [...] 3802 24 15_Re: mass delete16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 22 Mar 2002 09:35:51 -0600354_- Hi Grace,

I wonder why you need to delete so many records every month - I would question that design aspect. You could try partitioning which could let you do processing by partitioning - but that's another story...

I'm surprised using a dummy sysrec statement with the load utility doesn't work. What error message are you getting? [...] 3827 34 31_Thread reuse, CICS and Omegamon13_Ealey, Paul M18_paul.ealey@EDS.COM31_Fri, 22 Mar 2002 15:55:50 -0000351_- Dear Listers we want to see the DB2 CLass 1 time in the Omegamon Accounting information but thread reuse is preventing us. We have ACCOUNTREC=TASK specified in DB2ENTRY about which the CICS Resource Definition Guide says:

TASK The CICS DB2 attachment facility causes a minimum of one accounting record for each CICS task to be produced. [...] 3862 147 27_AW: Re: PARTITION LIMIT KEY41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Fri, 22 Mar 2002 17:01:57 +0100331_iso-8859-1 The other day I partitioned a tablespace where some rows had NULLS in the partitioning columns and all those rows got discarded during the subsequent load, although this doesn't really answer the question whether one can specify NULL in the limitkeys. As far as I remember one can't specify NULL in the limit-keys. [...] 4010 21 22_DBA Friend out of Work0_20_John_Lendman@FPL.COM31_Fri, 22 Mar 2002 11:03:09 -0500269_us-ascii A DBA friend of mine is out of work and I was wondering if anyone has any contact in South Florida who might be looking for a DB2 OS/390 DBA or DB2 UDB DBA and even IMS DBA. He has 30+ years of experience. If so please contact me off the list. Thank You. [...] 4032 19 35_Re: Pro's and Con's of using LOB's?12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 10:00:03 -0600476_- The information depends upon the platform and version you are running. I'll presume z/OS & OS/390 and V6 or V7 for this answer.

There is a little on LOB performance in the V6 Technical Update Redbook, SG24-6108. We are working on another Redbook on LOBs now, but it's not ready yet, but perhaps in a month. Most of the LOB information is across the DB2 library, rather than split out separately. The LOB section of the V6 Release Guide is a good source for now. [...] 4052 17 27_Re: DB2DBM1 Enq on SYSZTIOT12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 10:06:35 -0600313_- The book reference I gave you earlier answers your questions. Opens and closes are in statistics, SMF 100.

Close matters very little if you have a current DB2. DSMAX matters. This is in the Administration Guide too, and get the one for the version you are running.

Roger Miller, DB2 for z/OS [...] 4070 76 16_Re: SQL Question14_MIKE FRIEDRICH27_RDBA002@REVENUE.STATE.IL.US31_Fri, 22 Mar 2002 10:34:19 -0600320_US-ASCII If you want the most recent row based on the USERID and the C_DATE, C_TIME, C_MILLISECOND this should work. This assumes that the C_DATE columns is defined as a DATE and C_TIME is defined as TIME and milliseconds is numeric type. If these types are character columns, then the CHAR function is not needed. [...] 4147 177 16_Re: SQL Question19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Fri, 22 Mar 2002 11:36:01 -0500794_US-ASCII Restructuring you Query to a correlated subquery will produce the result that you need.

SELECT A.C_DATE, A.C_USERID, B.C_LAST_NAME, B.C_FIRST_NAME FROM DB22SQL.TCISUSAG A, DB22SQL.TCWSUSER B WHERE A.C_USERID = B.CPK_USERID AND A.C_DATE||A.C_TIME||A.C_MILLISECOND = (SELECT MAX(C_DATE||C_TIME||C_MILLISECOND ) FROM TCISUSAG WHERE C_USERID = A.C_USERID )



Murari Selvakesavan. Data Resource Management. First Health Services Corp. 804.965.7601





>>> MDempse@UNCH.UNC.EDU 03/22/02 09:46AM >>> Hey Folks... We have an audit table that records when a user accessed a specific function of an application. I need to retrieve data from that audit table on the last time a userid accessed that application. The query that I've written returns not [...] 4325 27 15_Re: REORG INDEX0_26_truman.g.brown@VERIZON.COM31_Fri, 22 Mar 2002 11:37:41 -0500597_us-ascii Long ago and far away at another company I was responsible for a very large and active critical DB2 application's performance. Since we had a Saturday night maintenance window my manager insisted on weekly full reorgs for the larger tablespaces/indexes with high CRUD activity, even though most access was random. I finally shut down the tablespace reorgs and only did indexes once a week and performance did not degrade. After it ran that way for a month with weekly index only reorgs I cut the index reorgs back to once every two weeks and the performance difference was so slight [...] 4353 94 24_Re: TCP/IP setup for DB213_John Ferguson17_kf0oujf@YAHOO.COM31_Fri, 22 Mar 2002 08:40:15 -0800423_US-ASCII In OS/390 V2R10.0 Uss Message & Codes

00FD JSRANFNoGID

The user ID is in a group that has no GID

Also, in USS, typing bpxmtext 0b0c00fd gives this:

JRSAFNoGID: The user ID is in a group that has no GID

Action: Create an OMVS segment and assign a GID to the group

John Ferguson



On Fri, 22 Mar 2002 11:25:57 +1200 Raymond Bell wrote: [...] 4448 108 35_Re: Thread reuse, CICS and Omegamon16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Fri, 22 Mar 2002 16:48:48 +0000364_us-ascii Paul,

sorry to ask the stupid question, but you do have Accounting Trace Class 1 active, do you not?

Because otherwise, when accounting trace class 1 is not active and the thread involved is reused, the value on OMEGAMON II displays is cumulative since thread creation.

(I assume we are talking about the realtime monitor, btw). [...] 4557 15 35_Re: Thread reuse, CICS and Omegamon10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 22 Mar 2002 17:52:59 +0100384_us-ascii Have you specified TOKENE=YES ? This cause an account record for every thread,otherways you see the sum of all txs.

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. 4573 23 34_Re: After Triggers - DB2 OS/390 V612_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 10:30:30 -0600341_- You may have another case where you have not set the CCSID in your zparms. Customers who have distributed processing needed to set the CCSID back in V2, but some have no done so.

Check the zparm settings and set valid ones for your data. The Installation Guide appendix on character conversion has good suggestions by country. [...] 4597 94 16_Very Strange SQL12_Grant Mackay28_Grant_Mackay@PROGRESSIVE.COM31_Fri, 22 Mar 2002 12:01:11 -0500378_us-ascii I stumbled on some weird behavior. In my attempt to get DB2 to use an index, I found DB2 behaving differently if I included a constant new field ('xxx' as new_field) in the query vs. not having the field in the query. When I included the field, it did a left outer join using an index and when I omitted the field it did a left outer join using a tablespace scan. [...] 4692 44 39_REXX SP calling another REXX SP - error18_Kimball, William P19_KimballWP@AETNA.COM31_Fri, 22 Mar 2002 12:09:29 -0500281_iso-8859-1 I have a REXX SP calling another REXX SP (actually DSNTPSMP). When the first REXX stored procedure tries to do the connect, I get an S04E, reason 00F30460. This is saying that I'm trying to do something other than RRSAF. My code for the connect is out of the book. [...] 4737 67 15_Re: REORG INDEX14_Andy Lankester33_andy.lankester@FR.CDBSOFTWARE.COM31_Fri, 22 Mar 2002 17:07:03 -0000597_us-ascii Personal opinion. REORG of indexes is MORE important in many cases than the data. As has been stated if NLEVELS is larger than it would be if the index were reorged then this adds an extra getpage to EVERY direct access via the index. Also LEAFDIST matters for index scans since an index with many pages out of sequence (usually due to inserts forcing split pages to the end) will destroy sequential prefetch efficiency. Just beware of using LEAFDIST>200 as a reorg criteria for indexes with FREEPAGE=0. Use LEAFDIST>NLEAF in this case. In general indexes that are growing need more [...] 4805 43 13_DATE Function15_Knabach, Mark L20_mark.knabach@EDS.COM31_Fri, 22 Mar 2002 12:14:00 -0500483_iso-8859-1 Good Morning.

I'm not an expert SQL programmer and I humble myself to those who are, as you could easily make me look silly. Here's my situation:

BEGIN_DATE is a column in my table that is defined with the attribute of DATE. Given any date during the current week (BEGIN_DATE), is there an SQL statement that I can execute against SYSIBM.SYSDUMMY1 (or any other table the I might need to create) that would give me Monday's date, of the following week? [...] 4849 67 24_FW: Load Replace Problem15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Fri, 22 Mar 2002 11:42:21 -0600342_iso-8859-1 Thank you for the suggestions. We re-verified everything that was suggested. Can not find an explanation. We re-ran the load with the same input file and it worked perfectly: 80 records in, 80 records loaded, 80 rows on the table. We are left with an unsolved puzzle that we hope does not bite us in production sometime. Karl [...] 4917 55 44_OT: Looking for Mike Holmans, Mark McCormack14_Linda Hagedorn30_Linda.Hagedorn@EMBARCADERO.COM31_Fri, 22 Mar 2002 09:59:12 -080089_iso-8859-1 Hello,

Mark, Mike, are you here? Drop me a line, will you?

Linda 4973 51 26_Re: DB2 Replication Papers12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 11:08:01 -0600417_- On Fri, 22 Mar 2002 16:35:16 +0700, Endy Lambey wrote:

>I'm looking for any document/softcopy/articles about DB2 Replication. I've already read the DB2 Replication Guide & Reference, but I still need some other references/case studies to deploy a solution for our client. > >I'm looking for a detail overview of DB2 Replication Architecture and Network Configuration/Requirement. [...] 5025 14 36_Re: DB2 V7 quantifiable measurements12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 11:17:13 -0600436_- I just tried to email you for one more try. What version of Adobe Acrobat are you using? If you are not at least 4.0.5, it's time for a download, and 5 has some advantages.

Roger Miller

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5040 56 20_Re: Very Strange SQL12_Grant Mackay28_Grant_Mackay@PROGRESSIVE.COM31_Fri, 22 Mar 2002 13:06:16 -0500479_us-ascii Thanks to Terry Purcell for the following explanation. ----- Forwarded by Grant Mackay on 03/22/2002 01:03 PM -----

"Terry Purcell" cc: Subject: Re: Very Strange SQL 03/22/2002 12:27 PM Please respond to Terry_Purcell













Grant,

Sorry for emailing you directly, but my YLASSOC email is having problems and I can't get through to DB2-L today. [...] 5097 54 15_Re: mass delete19_Alekos Papadopoulos30_alekospapadopoulos@HOTMAIL.COM31_Fri, 22 Mar 2002 20:15:25 +0200423_- OS and version not stated. If you are not OS/390 or z/OS ignore this reply. Otherwise, check SYSIBM.SYSTABLES, column DATACAPTURE for the specific table. If it is blank, ignore this reply again. If it is 'Y' then before issuing your delete you have to issue ALTER TABLE yourtablename DATA CAPTURE NONE It will save much time in your mass delete, because the deleted rows will not be recorded in your log one by one. [...] 5152 62 17_Re: DATE Function13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Fri, 22 Mar 2002 10:16:58 -0800434_iso-8859-1 SELECT DATE(DAYS(BEGIN DATE)/7 * 7 + 7) FROM SYSIBM.SYSDUMMY1

Thanks. Ashish.



-----Original Message----- From: Knabach, Mark L [mailto:mark.knabach@EDS.COM] Sent: Friday, March 22, 2002 9:14 AM To: DB2-L@RYCI.COM Subject: DATE Function

Good Morning.

I'm not an expert SQL programmer and I humble myself to those who are, as you could easily make me look silly. Here's my situation: [...] 5215 76 17_Re: DATE Function12_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 22 Mar 2002 12:24:41 -0600457_iso-8859-1 I hope you don't mind a "pedagogical" explanation of how I'd approach this.

if BEGIN_DATE is Monday, then return BEGIN_DATE+6 if BEGIN_DATE is Tuesday, then return BEGIN_DATE+5 if BEGIN_DATE is Wednesday, then return BEGIN_DATE+4 if BEGIN_DATE is Thursday, then return BEGIN_DATE+3 if BEGIN_DATE is Friday, then return BEGIN_DATE+2 if BEGIN_DATE is Saturday, then return BEGIN_DATE+1 if BEGIN_DATE is Sunday, then return BEGIN_DATE+0 [...] 5292 19 17_Re: DATE Function12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 12:01:00 -0600302_- You need a tighter specification to get it exactly right. Is is Sunday or Monday that we are looking for? If the date is Sunday and the current day is a Sunday do we add 0 days or 7? Presuming that you are running at least DB2 V6, then you get to use the DAYOFWEEK function. Then something like [...] 5312 32 27_Re: DB2 Governor for OS/3908_D Preble18_preble@CETLINK.NET31_Fri, 22 Mar 2002 12:17:25 -0600345_- On Thu, 21 Mar 2002 07:54:11 -0000, srinivas gopala wrote:

>Hi, >What are the requirements for DB2 Governor implementation ? Is >there any thing in DSNZPARM to be configured? Because I have >created the Tables etc and inserted values. Started the RLIMIT etc >.But still it does not work. > >Regards, >DBA [...] 5345 62 35_Re: Thread reuse, CICS and Omegamon13_Martin Packer24_martin_packer@UK.IBM.COM31_Fri, 22 Mar 2002 18:57:59 +0000493_us-ascii Even with ACCOUNTREC (or TOKENE/TOKENI) set perfectly you may see a long class 1 time. In a thread reuse situation you will see the record cut when the new user of the thread (i.e reusing tran instance) starts. (You can tell this from the QWACRINV field - New User Signon or Same User Signon values). Under these circumstances you might see long class 1 - orders of magnitude are possible, particularly if the transaction rate is low relative to the number of protected threads. [...] 5408 18 39_production experience DB2 for OS/390 V70_21_rumina.jivraj@BMO.COM31_Fri, 22 Mar 2002 13:54:53 -0500466_us-ascii We are planning to upgrade from V6 to V7 of DB2 for OS/390. I would like to hear back on any experiences positive or otherwise of this migration in a production environment.

Thanks for any experiences. Rumina

================================================ 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. 5427 172 43_Re: REXX SP calling another REXX SP - error19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Fri, 22 Mar 2002 13:59:37 -0500394_US-ASCII Bill,

There should'nt be any explicit 'CONNECT' statements coded in the SPs.

The call attempted for DSNTPSMP or any other SPs from your first SP will essentially check the SYSIBM.SYSROUTINES for the associated WLMENV name and executes the corressponding SP accordingly from the associated WLM application environment or from the DB2 SPAS (if it is not WLM managed). [...] 5600 13 11_Re: DSNTEP212_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 12:29:49 -0600389_- The last change I see in that module at a V5 level was 04/17/00 Initialize storage to prevent abend 0c4 PQ36800

Roger Miller, DB2 for z/OS

================================================ 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. 5614 14 43_Re: REXX SP calling another REXX SP - error9_Jim Ruddy18_jaruddy@US.IBM.COM31_Fri, 22 Mar 2002 12:33:01 -0600439_- Don't do any connect within a REXX SP - the connect has already been done by the intialization code within the stored procedure address space.

Jim Ruddy DB2 for z/OS and OS/390 Development

================================================ 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. 5629 63 21_Re: Resource type 9038_D Preble18_preble@CETLINK.NET31_Fri, 22 Mar 2002 12:56:01 -0600588_- >-----Original Message----- >From: Rasmussen, Steen [mailto:Steen.Rasmussen@CA.COM] >Sent: Friday, March 22, 2002 4:09 AM >To: DB2-L@RYCI.COM >Subject: Resource type 903 > > > >Good Morning , > >We have had a strange situation a couple of times lately - where we're >unable to understand WHY the dynamic SQL is the cause of timeouts of static >SQL transactions. We have seen this with QMF, DSNTEP2 and DSNTIAUL, and we >have no clue why a lock is hold for such a long time in DSNDB01. Reason >00C9008E clearly says DEADLOCK or TIMEOUT - but why is the lock being hold >for so long [...] 5693 42 43_Re: production experience DB2 for OS/390 V716_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 22 Mar 2002 14:12:45 -0500358_iso-8859-1 DB2 V7 is far and away the most responsive and stable version IBM has produced. The quality assurance was execellent. The only worry you might have is if you have some non-IBM tools. If so, grill your vendor(s) *intensively* on which DB2 features they exploit, which they tolreate, and which leave their software in a flaming heap of rubble. [...] 5736 30 20_VIEWS and SYSTABAUTH10_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM31_Fri, 22 Mar 2002 13:35:53 -0600397_iso-8859-1 In the DB2 V7 Administration Guide is the following statement:

USER1 creates a table and grants ALL PRIVILEGES on it to PUBLIC. USER2 then creates a view on the table. In the catalog table SYSIBM.SYSTABAUTH, GRANTOR is PUBLIC and GRANTEE is USER2. Creating the view requires the SELECT privilege, which is held by PUBLIC. If PUBLIC loses the privilege, the view is dropped. [...] 5767 16 34_Re: BROKEN PAGE (DB2 V6 on OS/390)8_D Preble18_preble@CETLINK.NET31_Fri, 22 Mar 2002 13:14:29 -0600378_- Is this a normal or large tablespace ? The page address length is different. Is the page with or without the partition # in it ? Are you taking image copies at TS level or TS/part level ? Your recovery level must agree. I have had some problems with JCL requiring the dataset corresponding to the partition 1 even thou you dealing with say part 10 for certain utilities. [...] 5784 17 16_Sequence Problrm16_Twins Ali&Hassan19_chathas@HOTMAIL.COM31_Sat, 23 Mar 2002 00:27:25 +0500 5802 97 17_Re: DATE Function0_26_JCameron@MSI-INSURANCE.COM31_Fri, 22 Mar 2002 13:40:36 -0600341_us-ascii Mark,

You say you need Monday's date, but your examples show getting the following Sunday's date. If the day is Sunday, do you use that date or the following Sunday. If you are V6 or V7, you can use DAYOFWEEK. Here's something that should help even if you're V5. Note : you need to replace the date in all three places. [...] 5900 32 69_Setting the JAVA_HOME path to use the control center in Linux DB2 7.219_infea2000@yahoo.com19_infea2000@YAHOO.COM31_Fri, 22 Mar 2002 11:41:39 -0800648_us-ascii Hello,



Can anyone tell me how to set the JAVA_HOME instance variable to start the the db2cc under Linux.



Does using the default db2setup program install a JVM or no?



Thanks,



Christian



_________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com



================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5933 34 45_Finding userid and spname in Stored Procedure11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 22 Mar 2002 15:04:19 -0500514_iso-8859-1 We are going to be submitting jobs from stored procedures. For audit purposes we would like to know the userid that invoked the stored procedure.

I have run the CVT chain to get to the ACEE but that one is for the main DB2 WLM SP task. There is a pointer to an ACEE in the TCB mapping but that seems to be null. We have the DB2 signon exit in place and have set SECURITY USER in the SP define. When the SP is invoked we see the ICH70001I USER01 LAST ACCESS AT ........ message in the WLM SP [...] 5968 17 27_os/390 collection on cd-rom4_Anil18_usregion10@AOL.COM31_Fri, 22 Mar 2002 13:30:15 -0600371_- Hi All!

Does anyone have the URl where you can go and order the os/390 collection on cd-rom ?

thanks.

Anil

================================================ 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. 5986 17 31_Re: os/390 collection on cd-rom26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Fri, 22 Mar 2002 14:17:53 -0600532_- Think you can still get to it via www.s390.ibm.com. It reroutes to current link. Click on Library on the left then over to pub orders on the right. Enter SK2T-6700-23 enter verify then order. Edward(Ed) J. Finnell, III Big Honkin' M/F Mgr. www.ua.edu or bama.ua.edu/archives/ibm-main.html

================================================ 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. 6004 110 59_JOB: IT Manager (Application Development) in North Carolina13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Fri, 22 Mar 2002 15:19:27 -0500718_iso-8859-1 (Description below) Please contact me directly about this position at: ksears@dpconsultants.com I will not present your resume to my client until we have talked and it will be held in confidence. My personal website: www.members.tripod.com/kathrynsears IT Manager Job Duties: This position is responsible for managing the Corporate Systems team, interacting with Finance and other corporate customers, prioritizing, scheduling, implementing, and supporting IT projects, and performing other duties as assigned. The ideal candidate will have a Bachelor's degree, 5-7 years management experience in IT, and the ability to understand and work with a variety of technical platforms (IBM mainframe & client [...] 6115 98 103_JOB: Applications Manager position in North Carolina (must have Life/70 or similar software experience)13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Fri, 22 Mar 2002 15:19:49 -0500766_iso-8859-1 (Description below) Please contact me directly about this position at: ksears@dpconsultants.com I will not present your resume to my client until we have talked and it will be held in confidence. My personal website: www.members.tripod.com/kathrynsears Systems Manager Job Duties: This position is responsible for leading a team of Programmers/Analysts in support of mission critical, enterprise-wide systems and projects and performing other duties as assigned. Qualifications: The ideal candidate will have 2+ years of direct management experience, the ability to work effectively without close supervision, proven skills in motivating, goal setting, coaching, planning, and organizing, excellent teamwork, partnership, business, and communication [...] 6214 117 51_Job: North Carolina: Cobol/ Java Lead App Developer13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Fri, 22 Mar 2002 15:20:08 -0500657_iso-8859-1 Please contact me directly in response to this posting: ksears@dpconsultants.com This a direct-hire (NON- contract) position in the Piedmont-Triad area (central North Carolina). This is a company where people enjoy working, with low turnover and a strong business presence. Salary should be in the 55-65K range (more, depending on experience. The ideal candidate will have 5+ years of experience in COBOL on the mainframe, 1 year experience in Java, and HTML (a plus). There are additional technical skills necessary. Should have full job description soon. Please email resume for a full job description when it is formulated 1. Leading all [...] 6332 86 16_JOB: Atlanta RPG13_kathryn sears24_ksears@DPCONSULTANTS.COM31_Fri, 22 Mar 2002 15:29:39 -0500638_iso-8859-1 Please reply to email at bottom of this message. This is a full-time direct hire position. I will not forward your resume without your consent. I will call with a more detailed description of the job and company upon receipt of resume. Thanks for help and interest. JOB DESCRIPTION: 65K for mid-level position; 75K for senior position Must have 4 yrs for mid and 7+ years for senior position. Must have programmed in RPG IV or higher. Prefer experience with financial type applications. Must be local to the Atlanta area. Kathryn Sears Data Processing Consultants, Inc. 336.510.5526 ksears@dpconsultants.com Kathryn Sears [...] 6419 14 34_Re: After Triggers - DB2 OS/390 V68_D Preble18_preble@CETLINK.NET31_Fri, 22 Mar 2002 14:06:31 -0600312_- you must alter the database to add a ccsid. Both source and target table's database and tablespace need it. The only one you can add is the one setup in the zparm or the "enhanced ccsid". The easiest way to find it is to look at a database created under v6 and then look at the catalogue (systablespace). [...] 6434 15 23_Re: DB2 Logging Problem12_Joe Hartmann25_jhartman@MAIL.STATE.MO.US31_Fri, 22 Mar 2002 14:23:02 -0600392_- Your problem might be APAR PQ45176 UQ63777. It just became available today. Hope this helps.

Joe Hartmann State of Missouri Technical 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. 6450 71 49_Re: Finding userid and spname in Stored Procedure0_19_Tim.Lowe@STPAUL.COM31_Fri, 22 Mar 2002 15:05:57 -0600578_us-ascii Lee, How about just querying the user? SELECT USER FROM SYSIBM.SYSDUMMY1; ?

Thanks, Tim





"Hayden, Lee" cc: Sent by: DB2 Data Subject: Finding userid and spname in Stored Procedure Base Discussion List



03/22/2002 02:04 PM Please respond to DB2 Data Base Discussion List











We are going to be submitting jobs from stored procedures. For audit purposes we would like to know the userid that invoked the stored procedure. [...] 6522 35 13_-911 Debate?!14_David Williams17_udow@VANGUARD.COM31_Fri, 22 Mar 2002 15:40:35 -0600358_- G'Day all - We are having a debate here, and I'm looking for additional feed back. STAGE: 0S/390 V2.10, DB2 V6.1, SCENERIO: Batch COBOL DB2 prog inserts 50 recs, COMMIT's every 10 recs. Processing rec 15, receives -911. The code 'retries' 2 times before actually abending. The unit of work is therefore 10 recs. IBM manual states the following:...... [...] 6558 45 17_Re: -911 Debate?!31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Fri, 22 Mar 2002 22:30:08 +0000456_iso-8859-1 David,

I could understand the topic of the debate but could not get why was the debate. It seems you have already explained the whole thing.

1) -911 rollbacks the unit of work i.e. UOW started after processing 10 records in the scnerio you mentioned.

2) -913 doesn't rollback it. The SQL at which the deadlock/timeout occured, has already failed and hence that is not rollbacked but was not performed (abended) itself. [...] 6604 15 24_Re: VIEWS and SYSTABAUTH12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 16:22:26 -0600265_- You will find the same wording in every version I can find. I just checked V4, and I would bet that wording is from Version 1. That is how cascade revoke works and how DB2 has worked for nearly 20 years. Changes are likely to break a few thousand customers. [...] 6620 21 17_Re: -911 Debate?!12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 22 Mar 2002 16:33:29 -0600379_- ROLLBACK is to the prior COMMIT or start of the unit of work. All of the SQL statements are rolled back with the -911. Use your standard checkpoint / restart techniques.

If someone reads this as a suggestion to retry a single SQL statement, that is not the intent. The operation is the sequence from the last COMMIT, not one SQL statement, not one instruction, ... [...] 6642 107 7_Trigger10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 22 Mar 2002 16:21:14 -0700334_iso-8859-1 Hi ! List, I am trying to create a TRIGGER:

CREATE TRIGGER REORG AFTER INSERT ON DSN8710.GRACETB2 FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE DSN8710.GRACETB3 SET REORG_STAT = 'Y'; END

and I always got this error: DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: [...] 6750 19 11_Re: Trigger10_Mark Doyle19_mdoyle@JCPENNEY.COM31_Fri, 22 Mar 2002 18:04:00 -0600515_- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you received this in error, please contact the sender and delete the material from any computer. [...] 6770 87 7_Re: WG:0_19_mike.holmans@BT.COM31_Sat, 23 Mar 2002 01:16:57 -0000542_ISO-8859-1 'Tag Ruediger

Access paths are determined on the assumption that LEAFDIST is negligible. DB2 will therefore think that a sequential scan of the index is a good idea. But if LEAFDIST is enormous, what that actually means is that the index entries are not in physical sequence in the index, so you will be incurring huge amounts of synchronous random I/O even though the access path chosen is an asynchronous sequential scan. It also almost certainly means that where dynamic prefetch might have kicked in, it now won't. [...] 6858 77 33_Re: Can Stored Procedure issue MQ9_Oliver Su14_ojsu@EMAIL.COM31_Sat, 23 Mar 2002 09:20:58 +0800313_iso-8859-1 Are there any special settings on DB2 SPAS or MQ message queue definition that will enable DB2 SP to connect to a message queue? I can exercise MQ commands in batch mode but not inside from a SP module. I got a return code 2018 for MQCONN which indicates the connection handle HCONN is not valid. [...] 6936 81 57_Repost from IDUG -- IDUG NA 2002 May 12-16, San Diego, Ca12_Phil Gunning17_db2jock@YAHOO.COM31_Fri, 22 Mar 2002 19:59:55 -0800494_us-ascii Get ready for the ride of you life. Attend IDUG 2002 – North America.

Growing Your Knowledge. Driving Your Future. May 12-16, 2002 San Diego Marriott Hotel & Marina San Diego, California USA

In the next few days, you will receive a courtesy call offering to help facilitate your IDUG 2002 – North America registration. Be sure to take this opportunity to accelerate your DB2 intelligence and enhance your ability to maximize the value of the DB2 family of products. [...] 7018 87 21_Re: Resource type 90312_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 23 Mar 2002 11:37:47 +0200687_windows-1255 Hi, Look for "save data" in QMF procedures

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "D Preble" Newsgroups: bit.listserv.db2-l To: Sent: Friday, March 22, 2002 8:56 PM Subject: Re: Resource type 903



> >-----Original Message----- > >From: Rasmussen, Steen [mailto:Steen.Rasmussen@CA.COM] > >Sent: Friday, March 22, 2002 4:09 AM > >To: DB2-L@RYCI.COM > >Subject: Resource type 903 > > > > > > > >Good Morning , > > > >We have had a strange situation a couple of times lately - where we're [...] 7106 24 35_Re: Thread reuse, CICS and Omegamon10_Max Scarpa16_mscarpa@CESVE.IT31_Sat, 23 Mar 2002 13:20:20 +0100354_us-ascii Hi Martin

In the specific case ACCOUNTREC(TASK) (I used old RCT concept, sorry) may have been the cause of long CLASS1 times. One record per tran (ACCOUNTREC=UOW) may be close to times showed by CICS monitor.

I agree, this may happen (see the good figure in Nigel Slinger, SHARE 1999) and you can see a low tx number as well. [...] 7131 22 44_unioning the result of two recursive queries11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sat, 23 Mar 2002 15:12:37 -0500367_iso-8859-1 Dear SQL Pros,

I've been pulling my hair out, and there's not much left, trying to union the results of two recursive sql queries. Each recursive query returns a single column having the same data type. I'd be deeply appreciative, and so would my barber, if anyone could share an example with the WITHs and parenthesis in all the right places. [...] 7154 47 49_Re: Finding userid and spname in Stored Procedure14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 24 Mar 2002 23:31:24 +1000511_US-ASCII Lee,

The SPname can be found in DBINFO if you ask for it.

James Campbell

On 22 Mar 2002 at 15:04, Hayden, Lee wrote:

> We are going to be submitting jobs from stored procedures. For audit > purposes we would like to know the userid that invoked the stored procedure. > > I have run the CVT chain to get to the ACEE but that one is for the main > DB2 WLM SP task. There is a pointer to an ACEE in the TCB mapping but > that seems to be null. We have the DB2 signon exit [...] 7202 46 11_Re: Trigger14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 24 Mar 2002 23:31:25 +1000361_US-ASCII Grace

read up on the meaning of sqlerrd(5). The other alternative is that you've left semi-colon as the statement delimiter. Depending on how you execute an SQL statement ther are various ways of defining a replacement character - which you put after the END.

James Campbell



On 22 Mar 2002 at 16:21, Grace Chen wrote: [...] 7249 41 24_Re: VIEWS and SYSTABAUTH14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 24 Mar 2002 23:31:27 +1000566_US-ASCII What happens if USER1 makes the grant TO PUBLIC WITH GRANT OPTION?

James Campbell

On 22 Mar 2002 at 13:35, Greg Jonas wrote:

> In the DB2 V7 Administration Guide is the following statement: > > USER1 creates a table and grants ALL PRIVILEGES on it to PUBLIC. > USER2 > then creates a view on the table. In the catalog table > SYSIBM.SYSTABAUTH, > GRANTOR is PUBLIC and GRANTEE is USER2. Creating the view requires > the SELECT privilege, which is held by PUBLIC. If PUBLIC loses the > privilege, > the view is dropped. > > We have [...] 7291 81 16_Re: SQL Question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 24 Mar 2002 13:39:39 -0600396_iso-8859-1 Michael,

Apologies if you get this twice, but I don't believe my original response got back to the list.

If you simply remove C_DATE out of the GROUP BY, then you should get the result you want. Also the DISTINCT is redundant.

Whether you want the time and millisecond portions is unclear. If so, you can concatenate these within the MAX to make a timestamp. [...] 7373 73 48_Re: unioning the result of two recursive queries13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 24 Mar 2002 13:50:14 -0600408_iso-8859-1 Scott,

I thought you had your own resident SQL pro????

Anyway, this is the simplest example I could put together for this....I believe it is what you are looking for:

WITH TEMP1 (N) AS (VALUES (1) UNION ALL SELECT N + 1 FROM TEMP1 WHERE N < 10) ,TEMP2 (N2) AS (VALUES (21) UNION ALL SELECT N2 + 1 FROM TEMP2 WHERE N2 < 30) SELECT * FROM TEMP1 UNION SELECT * FROM TEMP2 ; [...] 7447 110 48_Re: unioning the result of two recursive queries11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Sun, 24 Mar 2002 18:11:29 -0500327_iso-8859-1 Terry,

My resident SQL Pro has been working like a mad man every day since Feb 1 on his book, IDUG, and DGI engagements, so I gave him this weekend off. ;)

Your syntax did the trick! Thank you! The key was using "WITH" once and separating the recursive expressions by a comma. You're the SQL man! [...] 7558 54 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 25 Mar 2002 12:05:30 +1200465_us-ascii OK Max, I feel suitably chastised. Our average reclength from Sysibm.Systables (where type='T') comes to 138.

For what it's worth.



Raymond





Max Scarpa cc: Sent by: DB2 Data Subject: Re: What's the 'mean' row length of a DB2 production table ? - Base Discussion Thanks List



23/03/02 00:12 Please respond to DB2 Data Base Discussion List [...] 7613 146 24_Re: TCP/IP setup for DB212_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 25 Mar 2002 13:57:57 +1200439_us-ascii Thanks to all for the replies so far. John, I don't know why but I got your message below 3 times. You, Iris and Cindy were all on the right track. The ID associated with the DIST started task was a superuser but had no Group ID. Problem partly solved; now DDF sits around for a minute or two before failing on the gethostbyname call again. Step in the right direction, though. At least we're not taking an SVC dump anymore. [...] 7760 227 24_Re: TCP/IP setup for DB212_Phil Gunning17_db2jock@YAHOO.COM31_Sun, 24 Mar 2002 19:11:12 -0800513_us-ascii Raymond, I don't know if you've taken a look at the old Redbook, WOW! DRDA Supports TCP/IP: DB2 Servers for OS/390 and DB2 Universal Database, SG24-2212-00. It contains lots more in addition to the Admin Guide Chapter 3. I have setup DDF, RACF, TCP/IP over the years at several customer sites and used to use this redbook as a guide before I built a checklist. Take a look at Chapter 5 and see if you are missing any steps. The books a little dated but still the best all around reference. HTH Phil [...] 7988 169 24_Re: TCP/IP setup for DB22_id19_dcreed@CABLEONE.NET31_Sun, 24 Mar 2002 22:14:10 -0800381_us-ascii The DNS needs to have a reverse name lookup to allow the Gethostbyname (terminology will make sense to the DNS people... not really me).

Regards Danny

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Raymond Bell Sent: Sunday, March 24, 2002 5:58 PM To: DB2-L@RYCI.COM Subject: Re: TCP/IP setup for DB2 [...] 8158 139 23_[Fwd: Re: SQL Question]14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 25 Mar 2002 07:19:23 +0100 8298 15 47_Paul W. Gatten/IRM/DST/US is out of the office.0_23_PWGatten@DSTSYSTEMS.COM31_Mon, 25 Mar 2002 01:07:02 -0600389_us-ascii I will be out of the office starting 03/23/2002 and will not return until 04/01/2002.

I will respond to your message when I return.

================================================ 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. 8314 23 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 25 Mar 2002 09:00:02 +0100519_us-ascii Hi Raymond

Chastised ? Am I the chastiser ? Me, the most quiet, gentle, tall, blonde,grey-green,rich,long-haired,beloved by women DB2 sysprog on the earth ? Never !

(OK it's monday so I'm still dreaming....)

Thanks for your infos

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. 8338 18 51_BVR/SYS/Nykredit =?iso-8859-1?Q?tr=E6ffes_ikke=2E?=14_Bjarne V. Ravn15_bvr@NYKREDIT.DK31_Mon, 25 Mar 2002 09:01:11 +0100419_iso-8859-1 Jeg er ikke på kontoret fra 25-03-2002 og vender ikke tilbage før 02-04-2002.

Jeg holder Påskefri indtil 2. april 2002. Jeg besvarer din besked, når jeg vender tilbage. ===============================================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. 8357 106 11_AW: Re: WG:41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Mon, 25 Mar 2002 09:02:14 +0100452_iso-8859-1 Mike and all the other who have responded (so far),

thanks a lot for your answers; as my colleague's will be out of the office for the next three days I will show him your views next week, let's see what he'll have to say.

Best regards

Ruediger

-----Ursprüngliche Nachricht----- Von: mike.holmans@BT.COM [mailto:mike.holmans@BT.COM] Gesendet: Samstag, 23. März 2002 02:17 An: DB2-L@RYCI.COM Betreff: Re: WG: [...] 8464 41 16_Selection of row17_Kumar, Arun (CTS)23_KArun@CAL.COGNIZANT.COM31_Mon, 25 Mar 2002 13:45:13 +0530225_- Hi , Whether we can select rows of a specified length(say 6) from a table where the column length(say 10) is more?. If no....is there any other method without using cobol programs?

Thanks & Regards Arun Kumar

8506 20 27_DB2 Version 7 Feature Codes12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Mon, 25 Mar 2002 08:28:21 +0000522_- Could someone tell me the Feature Codes of DB2 Version 7 for OS/390.

Regards Rakesh



_________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

================================================ 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. 8527 41 20_AW: Selection of row41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Mon, 25 Mar 2002 09:40:32 +0100498_iso-8859-1 Arun,

Assuming you're on DB2 for os/390, also assuming you're trying to select char-columns and further assuming your 'shorter' rows have trailing blanks you could code select .... from where length(strip(,t,' ')) =

Best regards

Ruediger Kurtz

-----Ursprüngliche Nachricht----- Von: Kumar, Arun (CTS) [mailto:KArun@CAL.COGNIZANT.COM] Gesendet: Montag, 25. März 2002 09:15 An: DB2-L@RYCI.COM Betreff: Selection of row [...] 8569 76 60_Re: What's the 'mean' row length of a DB2 production table ?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 25 Mar 2002 09:01:39 -0000435_iso-8859-1 on our SAP system ... using same as below you get :

1517419481515

2348474673

= 646

rgds

Leslie -----Original Message----- From: Higgins John [mailto:HigginsJohn@JOHNDEERE.COM] Sent: Thursday, March 21, 2002 5:38 PM To: DB2-L@RYCI.COM Subject: Re: What's the 'mean' row length of a DB2 production table ?



Applying this method to our SAP database on DB2/OS390 gives 628. [...] 8646 27 30_Re: DB2 thread reuse with CICS10_Ozgur Ozen13_OOZEN@YKB.COM31_Mon, 25 Mar 2002 02:51:14 -0600386_- I have a similar problem with accountrec parameter. When we define a group of transactions in the same entry definition , and thread reuse is run;

with ACCOUNTREC TASK option , the records are not cut when another transaction from the group reuses the thread . so I tried ACCOUNTREC TXID option,but now the records are not cut when the same transaction reuses the thread. [...] 8674 74 27_Re: DDF and WLM question...19_Calleja Vilar, Juan22_jcallejav@IBERDROLA.ES31_Mon, 25 Mar 2002 10:35:11 +0100433_iso-8859-1 Dave, Could you please tell us where can we find info about the way to use the DB2 Connect's accounting api you mention?. Or could you instead give us an example?

TIA

Juan Calleja Vilar Iberdrola, S.A. (Spain)

-----Mensaje original----- De: Shapiro, Dave [mailto:Shapiro.Dave@PRINCIPAL.COM] Enviado el: miércoles 13 de marzo de 2002 21:28 Para: DB2-L@RYCI.COM Asunto: Re: DDF and WLM question... [...] 8749 18 20_Re: Selection of row15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 25 Mar 2002 03:55:31 -0600412_- Hi

I don't know, if I understand your question rigth, but what do think about

SELECT ... FROM table where LENGTH(column) = 6

Is this what you mean?

================================================ 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. 8768 18 24_Re: AW: Selection of row15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Mon, 25 Mar 2002 03:58:19 -0600531_ISO-8859-1 Hallo Rüdiger

Könnten Sie nicht einmal versuchen, ihre Antworten als Reply zu verschicken, so dass man automatisch auch zu Ihren Antworten kommt, wenn man dem Thread entlang hangelt. Ich kenne Ihre Einstellungen nicht, sehe nur, dass Ihre Antworten immer mit "AW" beginnen.

===============================================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. 8787 207 21_AW: Resource type 90312_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Mon, 25 Mar 2002 11:45:40 +0100539_iso-8859-1 Hi Steen,

to follow up the contributions by Eric and D?: Maybe it is an option for you to bind the QMF plans with UR.

Best regards

Thomas ________________________________________ Thomas Weber LDS NRW Postbox 10 11 05 40002 Duesseldorf Germany phone: +49 - 211 - 9449 - 2545 e-mail: thomas.weber@lds.nrw.de





-----Ursprüngliche Nachricht----- Von: Rasmussen, Steen [mailto:Steen.Rasmussen@CA.COM] Gesendet: Freitag, 22. März 2002 10:09 An: DB2-L@RYCI.COM Betreff: Resource type 903 [...] 8995 13 11_Re: DSNTEP213_Thomas Schulz20_thomas.schulz@SVI.DE31_Mon, 25 Mar 2002 05:56:46 -0600321_- I got PQ36800, it runing now.

Best regards and thank you Thomas Schulz

================================================ 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. 9009 20 16_Levelid Mismatch15_Katharine Brown25_kathrinebrown@HOTMAIL.COM31_Mon, 25 Mar 2002 12:37:43 +0000536_- Is there any DB2 command or option so that I can know all those tablespaces having levelid mismatch at one go. Thanks.

Regards Katharine

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

================================================ 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. 9030 24 19_copy pending status34_=?iso-8859-1?q?subrahmanya=20rao?=27_subrahmanya_rao@YAHOO.CO.IN31_Mon, 25 Mar 2002 12:28:25 +0000625_iso-8859-1 Hi, Are there any other utility or task putting a TS in COPY pending status other than LOAD and REORG? Both these utilities will be run by setting the option LOG NO.

Thanks, Subbu

________________________________________________________________________ For live cricket scores download Yahoo! Score Tracker at: http://in.sports.yahoo.com/cricket/tracker.html

================================================ 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. 9055 43 31_Re: DB2 Version 7 Feature Codes13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 25 Mar 2002 07:42:50 -0500596_iso-8859-1 Hello Rakesh,

I'm sorry, I don't understand what you mean by Feature Codes. Please clarify.

You might find your answer at the v7 bookshelf: http://www-4.ibm.com/software/data/db2/os390/v7books.html

If you're looking for new feature information, I recommend

>Release Planning Guide SC26-9943-01 There's also good information in >DB2 UDB Server for OS/390 and z/OS Version 7 Presentation Guide SG24-6121-00 Each manual will also have a summary of changes section. Perhaps you'll find what you want in the SQL Reference or Admin guide Summary of changes. [...] 9099 16 13_-911 Debate.?14_David Williams17_udow@VANGUARD.COM31_Mon, 25 Mar 2002 06:26:02 -0600494_- Thx for the replies... Just to clarify.! (I know it's Monday morning, but..)

-911: Rollback UOW (to last commit point). Retry logic is not appropriate. -913: Doesn't Rollback. Retry logic (2 to 3 times) may resolve the issue.

Thx 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. 9116 42 23_Re: copy pending status16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Mon, 25 Mar 2002 08:30:23 -0500501_iso-8859-1 MODIFY if it deletes all the SYSCOPY entries for the tablespace.

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: subrahmanya rao [mailto:subrahmanya_rao@YAHOO.CO.IN] Sent: Monday, March 25, 2002 7:28 AM To: DB2-L@RYCI.COM Subject: copy pending status



Hi, Are there any other utility or task putting a TS in COPY pending status other than LOAD and REORG? Both these utilities will be run by setting the option LOG NO. [...] 9159 148 23_Re: copy pending status14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 25 Mar 2002 13:54:46 -0000389_iso-8859-1 Also running an incremental copy that abends will set COPYP.

As someone mentioned last week, if you MODIFY all of the copies of a tablespace out of SYSCOPY then that will also set COPYP.

Also RECOVER to any point other than current will set COPY, but I think this depends on which release of DB2 you are on (I'd need to check the Utility manual for that one) [...] 9308 36 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks12_Joe DeCastro14_JoeFDC@AOL.COM29_Mon, 25 Mar 2002 08:55:17 EST507_ISO-8859-1 Max,

Our average row length is 418 using the following SQL:

SELECT AVG(RECLENGTH) FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND CARD > -1 WITH UR

With the catalog and Platinum excluded, the average row length is 328.

Joe

>Thanks to all (actually very few) who provided some >numers (but I'm waiting >for other number from different > >people/shops). This confirm my idea that IBM's sample >row length is short >and I'll use these 'mean' value for some tests. [...] 9345 35 16_PLAN and PACKAGE5_Subha22_subha_sivaji@YAHOO.COM31_Mon, 25 Mar 2002 07:52:11 -0600344_- Hi Listers,Can someone please help me to post the syntax for binding

PLAN - With PROGRAM ( DBRM ) PACKAGE - With PROGRAM ( DBRM )

Do this PACKAGE need to bound with the PLAN or it need not be .

Relation betwwen PLAN - PACKAGE - PROGRAM ( DBRM )

Relation betwwen PLAN - COLLECTION - PACKAGE - PROGRAM ( DBRM ) [...] 9381 114 60_Re: What's the 'mean' row length of a DB2 production table ?17_David Eugene Will26_david.e.e.will@VERIZON.COM31_Mon, 25 Mar 2002 09:45:53 -0500469_us-ascii Max,

Using the SQL below we came in at 257. Am I off the hook?

Dave







"Pendlebury-Bowe, Leslie" @RYCI.COM> on 03/25/2002 04:01:39 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: What's the 'mean' row length of a DB2 production table ? [...] 9496 70 70_AW: What's the 'mean' row length of a DB2 production table ? - Th anks12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 25 Mar 2002 15:52:36 +0100633_iso-8859-1 Hi Max, greetings to Italy.

Here are our numbers: Our overall average row length is 444.

Without the creator SYSIBM the avg rl is 442.

And without regarding the tables of some tool providers this length is 278.

Hope that helps.

With kind regards - mit freundlichen Grüssen, Georg H. Peter ----------------------------------------------------------------------

-----Ursprüngliche Nachricht----- Von: Joe DeCastro [mailto:JoeFDC@AOL.COM] Gesendet am: Montag, 25. März 2002 14:55 An: DB2-L@RYCI.COM Betreff: Re: What's the 'mean' row length of a DB2 production table ? - Thanks [...] 9567 90 74_Re: AW: What's the 'mean' row length of a DB2 production table ? - Th anks41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Mon, 25 Mar 2002 16:05:47 +0100419_iso-8859-1 Hi Max,

I came up with 320.

Best regards

Ruediger

-----Ursprüngliche Nachricht----- Von: Peter, Georg [mailto:G.Peter@DZBW.DE] Gesendet: Montag, 25. März 2002 15:53 An: DB2-L@RYCI.COM Betreff: AW: What's the 'mean' row length of a DB2 production table ? - Th anks



Hi Max, greetings to Italy.

Here are our numbers: Our overall average row length is 444. [...] 9658 65 20_Re: PLAN and PACKAGE11_Riyaz Momin15_RMomin@FHSC.COM31_Mon, 25 Mar 2002 10:11:06 -0500386_US-ASCII Su,

On a BIND PLAN command you can include a list of collection-ids to associate the packages with PLAN, by using PKLIST option. Please refer to "Options of BIND" under OS/390 V6.1 manuals. You can also find the relationship between Plan, Package & program in these manuals.

Good Luck, Riyaz. Application Programmer. First Health Services Corp. 804-965-7580 [...] 9724 143 60_Re: What's the 'mean' row length of a DB2 production table ?8_Ron Root24_ron.root@CPA.STATE.TX.US31_Mon, 25 Mar 2002 09:18:45 -0600444_iso-8859-1 When I used the simple technique I got 310. Using the weighted technique I got 115.

Ron Root Texas Comptroller of Public Accounts Ron.Root@cpa.state.tx.us





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of David Eugene Will Sent: Monday, March 25, 2002 8:46 AM To: DB2-L@RYCI.COM Subject: Re: What's the 'mean' row length of a DB2 production table ? [...] 9868 17 60_Re: What's the 'mean' row length of a DB2 production table ?16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Mon, 25 Mar 2002 10:20:42 -0500297_us-ascii Max development 383 q a 365 prod 360 hth Tom F

================================================ 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. 9886 25 6_STP-MQ17_Robert J. Milonas20_XRJMBOB@NETSCAPE.NET31_Mon, 25 Mar 2002 10:29:31 -0500337_iso-8859-1 There was some discussion of Stored Procedures and MQ last Month We haven't started using these two together yet. But I ran across this and thought I would share it.

Scripting Stored Procedures with MQSeries Workflow http://www.informatik.uni-stuttgart.de/ipvr/as/lehre/skripte/TRSWS0001/Scripting_Stored_Procedures.pdf 9912 52 20_Re: Selection of row10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Mon, 25 Mar 2002 10:58:59 -0500487_iso-8859-1 Arun,

If the column type is VARCHAR you can have SQL "and length(your column) = 6"



______________________________ Reply Separator _________________________________ Subject: Selection of row Author: owner-db2-l@RYCI.COM at INTERNET Date: 3/25/02 3:28 AM



Hi , Whether we can select rows of a specified length(say 6) from a table where the column length(say 10) is more?. If no....is there any other method without using cobol programs? [...] 9965 32 77_Re: What's the 'mean' row length of a DB2 production table ? - Thanks (again)10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 25 Mar 2002 17:17:46 +0100462_us-ascii Hi esteemed listers

Thanks for your replies. I'll continue to collect the data and later I'll pots the result of the survey. But it seems that:

1) Mean rows in real DB2 arein general greater that the row size used by IBM for (some) redbooks performance tests and this could (I repeat could) justify some discrepancies betwen times obtained in a controlled environment and some real DB2 production environments i found some times ago. [...] 9998 44 24_Db2 Command Centre error15_Foweather, Iris30_Iris.Foweather@UK.EXPERIAN.COM31_Mon, 25 Mar 2002 16:19:14 -0000310_iso-8859-1 Help please,

I am trying to run runstats on DB2 Control Centre V7.1 using Db2 Connect PE (the PC is running Windows NT, I am connecting to DB2 V6.1 to OS/390). I get as far as listing the tablespaces and then right click the mouse and choose 'run statistics'. I get the following error: [...] 10043 123 74_Re: AW: What's the 'mean' row length of a DB2 production table ? - Th anks0_19_mike.holmans@BT.COM31_Mon, 25 Mar 2002 16:30:12 -0000713_ISO-8859-1 Max,

I tried the weighted algorithm on two production subsystems and got 105 and 235. It therefore depends.

Mike Holmans

>>-----Original Message----- >>From: "Kurtz, Rüdiger" [mailto:Ruediger.Kurtz@HUK-COBURG.DE] >>Sent: Monday, March 25, 2002 3:06 PM >>To: DB2-L@RYCI.COM >>Subject: Re: [DB2-L] AW: What's the 'mean' row length of a DB2 >>production table ? - Th anks >> >> >>Hi Max, >> >>I came up with 320. >> >>Best regards >> >>Ruediger >> >>-----Ursprüngliche Nachricht----- >>Von: Peter, Georg [mailto:G.Peter@DZBW.DE] >>Gesendet: Montag, 25. März 2002 15:53 >>An: DB2-L@RYCI.COM >>Betreff: AW: What's the 'mean' row length of a DB2 production >>table ? - >>Th anks >> [...] 10167 21 31_Re: DB2 Version 7 Feature Codes12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 25 Mar 2002 10:00:12 -0600381_- The announcements have feature codes for ordering. The one you'll want is the general availability announcement, March 6, 2001.

Here is one I pulled for the US, but you'll want to start at ibm.com/ibmlink in general and look for the Announcement letters, then near the bottom there is ordering information. In the PDF format, the feature codes take up pages 13 - 21. [...] 10189 74 20_Re: PLAN and PACKAGE17_Robert J. Milonas20_XRJMBOB@NETSCAPE.NET31_Mon, 25 Mar 2002 12:01:43 -0500334_iso-8859-1 I remember when the term package was first introduced. The term package sounded like it was more inclusive, like maybe it was a package of plans. But the package kind of took the place of the original plan and the plan became the name of a list of packages.





Riyaz Momin wrote: [...] 10264 114 20_Re: PLAN and PACKAGE14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Mon, 25 Mar 2002 09:14:36 -0800483_iso-8859-1 it is an unfortunate naming convention. In spite of coding Stored procedures regularly and building programs with embedded SQL; I can never recall (with looking) if a stored procedure is a plan without a package or a package without a plan.

Some days we need more coffee

Susan A

-----Original Message----- From: Robert J. Milonas [mailto:XRJMBOB@NETSCAPE.NET] Sent: Monday, March 25, 2002 9:02 AM To: DB2-L@RYCI.COM Subject: Re: PLAN and PACKAGE [...] 10379 24 29_DEFINE NO Indexes and Logging11_Lori Bewley28_lorinda.l.bewley@US.HSBC.COM31_Mon, 25 Mar 2002 11:18:56 -0600579_- When indexes are created with the DEFINE NO clause, what kind of logging is done when the table supported by those indexes is initially populated? I've got a Peoplesoft HR system that is the perfect candidate for DEFINE NO on indexes since so many tables are never populated. When an import script is run to populate certain tables with 'seed data', the DB2 logs fill at an amazing rate for this subsystem, thousands of cylinders in minutes. The amount of data being inserted is minimal, not nearly enough to write this number of log records. I'm guessing that there's an [...] 10404 77 11_Re: Trigger10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 25 Mar 2002 10:40:29 -0700560_iso-8859-1 Hi James, I still got the same error with my trigger creation: CREATE TRIGGER GTRIG AFTER INSERT ON DSN8710.GRACETB2 FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE DSN8710.GRACET3 SET TYPE = 'I'; END If I ran it through SPUFI, I got message : SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SQLERRD = 0 0 0 -1 361 0 SQL DIAGNOSTIC INFORMATION If I ran it through batch job DSNTEP2, I got error message: SQLCODE = -104, ERROR: ILLEGAL SYMBOL "" SQLERRD = 0 0 0 -1 93 0 SQL DIAGNOSTIC INFORMATION I didn't put any after the END. Do you [...] 10482 60 24_FW: VIEWS and SYSTABAUTH10_Greg Jonas29_greg.jonas.ay8i@STATEFARM.COM31_Mon, 25 Mar 2002 11:54:03 -0600434_- This probably would work if you were allowed to give the grant option to public. If you're granting to PUBLIC, the "with grant option" clause is ignored.

-----Original Message----- From: jacampbell@ACSLINK.NET.AU [mailto:jacampbell@ACSLINK.NET.AU] Sent: Sunday, March 24, 2002 7:31 AM To: DB2-L@RYCI.COM Subject: Re: VIEWS and SYSTABAUTH



What happens if USER1 makes the grant TO PUBLIC WITH GRANT OPTION? [...] 10543 104 27_Re: DDF and WLM question...13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Mon, 25 Mar 2002 11:24:20 -0600451_iso-8859-1 Juan,

Look up Accounting String in the DB2 Connect User's Guide index for general information about how to use it. Basically, IBM provides an API for C, which out of the box does not work for VB. We then modified their C API program (which comes with DB2 Connect) and created our own DLL which we put in the SQLLIB path. This DLL is callable from a wrapper within a VB application, and is done just prior the CONNECT statement. [...] 10648 116 11_Re: Trigger15_Andersen, Cindy18_candersen@FNNI.COM31_Mon, 25 Mar 2002 12:19:49 -0600388_us-ascii Grace,

This has been covered before. Please check the DB2-L archives for setting the SQL statement terminator character. In SPUFI, you do it on the first option of the SPUFI options which is "5 CHANGE DEFAULTS ===>YES (Y/N - Display SPUFI defaults panel?)" then Enter the following to control your SPUFI session: 1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator) [...] 10765 103 11_Re: Trigger9_Oliver Su14_ojsu@EMAIL.COM31_Tue, 26 Mar 2002 02:28:38 +0800385_iso-8859-1 Garce, Did you change the SQL terminator to some thing other than ";"? The sql terminator shall be set other than ';' to prevent the conflict between the ending of update SQL and Create Trigger SQL.

Oliver

----- Original Message ----- From: Grace Chen Date: Mon, 25 Mar 2002 10:40:29 -0700 To: DB2-L@RYCI.COM Subject: Re: Trigger [...] 10869 13 24_Re: VIEWS and SYSTABAUTH12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 25 Mar 2002 11:37:48 -0600396_- I think GRANT to public with grant option gets SQLCODE +558, SQLSTATE 01516, noting that with grant option is ignored.

Roger Miller, DB2 for z/OS

================================================ 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. 10883 19 17_Red paper updated12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 25 Mar 2002 11:55:28 -0600573_- DB2 for z/OS and OS/390 Version 7 Selected Performance Topics Published: March, 22, 2002

Some additions and corrections were made this last weekend, so you might want to get the latest one.

More details are available at http://www.redbooks.ibm.com/redpapers/abstracts/redp0162.html

Roger Miller, DB2 for z/OS

================================================ 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. 10903 12 11_Re: Trigger12_Fred Collins20_fred.collins@EDS.COM31_Mon, 25 Mar 2002 12:23:30 -0600415_- Grace, select Change Defaults from the SPUFI panel. If SQL TERMINATOR is set to simi-colon change it to something else such as @. Then trying running your create statement

================================================ 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. 10916 83 27_Changing IP Address for DDF12_Marc Matulis30_marc.matulis@OSFHEALTHCARE.ORG31_Mon, 25 Mar 2002 12:27:56 -0600592_- Running DB2 v7.1 on OS/390 v2.10 with TCPIP.



Original configuration:

DDF pointed to ports 449 and 33003 on IP xxx.xxx.5.170 which is a 100Mb connection. This IP resolved back to "HostName". All worked fine.



DISPLAY DDF showed:

DSNL080I < DSNLTDDF DISPLAY DDF REPORT FOLLOWS: DSNL081I STATUS=STARTD DSNL082I LOCATION LUNAME GENERICLU DSNL083I DSNP xxxxxxx.DB2DSNP -NONE DSNL084I IPADDR TCPPORT RESPORT DSNL085I xxx.xxx.5.170 449 33003 DSNL086I SQL DOMAIN=HostName DSNL086I RESYNC DOMAIN=HostName DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE [...] 11000 172 33_Re: DEFINE NO Indexes and Logging24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 25 Mar 2002 10:46:28 -0800441_- Hi Lori!

Your shop must be DASD poor indeed if it cannot space even 1 track for each index. If we assume that you have about 4,500 indexes that are unnecessary, then at 15 tracks per cylinder, that comes to just 300 cylinders. You might want to consider only creating those indexes that are required to enforce uniqueness, (just in case the tables later contain data), and dropping all other indexes for tables that are empty. [...] 11173 58 26_Sequence - Nextval Problem9_Ali Akbar19_chathas@HOTMAIL.COM31_Mon, 25 Mar 2002 23:59:20 +0500292_iso-8859-1 Hi list, I am using DB2 7.2 on Linux . I am having problem using Nextval statement. Everytime i use "Values Nextval for sequenceName" i get a socket Error / TCP/IP communication error and i have to restart DB Manager.

Can anybody help me out

Thanks

Ali

11232 14 29_DEFINE NO Indexes and Logging11_Lori Bewley28_lorinda.l.bewley@US.HSBC.COM31_Mon, 25 Mar 2002 12:57:10 -0600355_- Thanks for your reply Fritz. We do split HR into 10 databases, how many do you have HR split into? We have payroll, HR, and Benefits right now. As for the logging, perhaps my question wasn't clear. I know that creating an index will log the DBD, what I'm asking is if the creation of the physical VSAM file on a deferred define is logging the DBD. [...] 11247 26 60_Re: What's the 'mean' row length of a DB2 production table ?0_19_csutfin@AMSOUTH.COM31_Mon, 25 Mar 2002 13:14:51 -0600575_us-ascii Max

Only when long-haired, blonde, green eyed men beg, will I do this.

The "mean" row length for our production DB2 is 236. (No SAP here).

For our test DB2 it is 149. (No SAP here either).





Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 Fax:(205)326-5613 csutfin@amsouth.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11274 34 60_DB2 connect limitation while accessing DB2 STORED PROCEDURES14_anand savulgay20_aksavulgay@YAHOO.COM31_Mon, 25 Mar 2002 11:09:14 -0800567_us-ascii Hi, We are facing a problem with DB2 CONNECT (V6) while accessing DB2 stored procedures(DB2 V6 OS/390) on Mainframe. It seems it is not doing anything( no response) When we pass 100 or more parameters to stored procedure. Length is not a problem here(the limit should be 32kb). We could pass large string with couple of parameters but same length could not pass when parameters are 100 or more. Here what we did. We did try to send 100 parameters with one byte each but there was no response after we executed the CALL statement. At this point we don’t [...] 11309 99 33_Re: DEFINE NO Indexes and Logging24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 25 Mar 2002 11:44:03 -0800588_- We're split into 20 databases. Unfortunately, I don't know the answer to the questions about the physical VSAM dataset and DB2 logging.

> -----Original Message----- > From: Lori Bewley [SMTP:lorinda.l.bewley@US.HSBC.COM] > Sent: Monday, March 25, 2002 10:57 AM > To: DB2-L@RYCI.COM > Subject: DEFINE NO Indexes and Logging > > Thanks for your reply Fritz. We do split HR into 10 databases, how many > do > you have HR split into? We have payroll, HR, and Benefits right now. > As for the logging, perhaps my question wasn't clear. I know that > creating > an index will log [...] 11409 14 31_Re: Changing IP Address for DDF12_Marc Matulis30_marc.matulis@OSFHEALTHCARE.ORG31_Mon, 25 Mar 2002 13:42:27 -0600444_- Never mind.

Needed to have PRIMARYINTERFACE parm set in SYS1.TCPPARMS since we are keeping both the original 10Mb and the new 1Gb connections, and the 10Mb connection is defined first to TCPIP.

================================================ 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. 11424 58 64_Re: DB2 connect limitation while accessing DB2 STORED PROCEDURES14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Mon, 25 Mar 2002 12:15:57 -0800696_iso-8859-1 are your parameters being exactly described on the table, in the stored procedure and by the calling statement?

I found that any slip up caused problems

Susan A

-----Original Message----- From: anand savulgay [mailto:aksavulgay@YAHOO.COM] Sent: Monday, March 25, 2002 11:09 AM To: DB2-L@RYCI.COM Subject: DB2 connect limitation while accessing DB2 STORED PROCEDURES



Hi, We are facing a problem with DB2 CONNECT (V6) while accessing DB2 stored procedures(DB2 V6 OS/390) on Mainframe. It seems it is not doing anything( no response) When we pass 100 or more parameters to stored procedure. Length is not a problem here(the limit should be 32kb). [...] 11483 74 23_Re: V7 conversion issue16_Jean E. Crichton26_jeancric@FAMILYCONNECT.COM31_Mon, 25 Mar 2002 15:19:44 -0500607_iso-8859-1 Sorry, I've been out of commission for a couple of weeks (60-hour workweeks), so I didn't pick up on this thread until now, but...

There was one useful aspect to running DSNTIJPM that had nothing to do with unsupported objects from previous versions of DB2. DSNTIJPM identifies any existing views on SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTATS which need to be dropped prior to running CATMAINT. I know of at least one IBM program product that defines such views, and that is the DB2 Performance Monitor. If you ever had CATMAINT blow up on you because of views on those tables, (as I [...] 11558 22 15_BMC Change Mgr.16_Teegarden, Steve22_Teegarden@AEGONUSA.COM31_Mon, 25 Mar 2002 14:52:04 -0600531_- Has anyone using BMC Change Manager with PeopleSoft ran the Compare (CDL Build), comparing whole databases ? v7 and or v8 databases.

I am interested in knowing the amount of time your jobs ran for comparisons.

Thanks

Steve Teegarden, DBA AEGON USA, INC. 319-398-8066

================================================ 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. 11581 54 23_DDL code for procedures14_Helmar Martens22_hmartens@BOWSTREET.COM31_Mon, 25 Mar 2002 15:59:13 -0500544_iso-8859-1 Hi list,

I am new to DB2. I am trying to generate the DDL code for a database (DB2/NT Version 7.2.0) using the Generate DDL command in the Control Center. I was expecting that this command would generate the DDL for all the objects in the DB, but it seems it does not. No code is generated for the procedures (I have checked the database objects check box). Well, as a work around, I copied the "Create Procedure" SQL code from each procedures and appended it to the .DDL file. When I run the .DDL file from the CLP, the [...] 11636 41 19_Re: BMC Change Mgr.10_Tom Willis24_tom_r_willis@HOTMAIL.COM31_Mon, 25 Mar 2002 15:22:45 -0600 11678 46 33_Re: DEFINE NO Indexes and Logging0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 25 Mar 2002 15:30:44 -0600445_us-ascii I just want to clarify this point ... One of our production DBs has DBD LENGTH = 512816. So is it true that each time I create an index, it logs all 500 kb (approx). What other types of changes would cause the entire DBD to be logged. Logically we could divide that DB into 7 or 8 smaller DBs. The largest one would still be about half that size, but the rest would be very small. In general, "... would this be a good thing? ..." [...] 11725 21 21_qmf for windows setup11_Trace Jones31_Trace_R_Jones@RELIANTENERGY.COM31_Mon, 25 Mar 2002 15:42:08 -0600371_us-ascii Getting this error trying to bind the QMF for Windows Packages. Anyone seen this?

An error occurred while binding the packages.

Execution failed due to a distribution protocol error that will not affect the successful execution of subsequent commands or SQL statements: reason DTAMCHRM (X'0200').

(SQL code = -30000, SQL state = 58008) [...] 11747 94 23_Re: V7 conversion issue13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Mon, 25 Mar 2002 15:55:35 -0600552_iso-8859-1 I have gotten IBM to open up APAR PQ58832 to address a suitable V7 version, or add the missing queries to one of the other premigration steps, since a lot of the queries are already being performed including identifying the views in SYSCOLDIST and SYSCOLDISTATS. And I understand about the long weeks. We just migrated 2 systems to V7 and upgraded the V6 to CST01Q4 maintenance on the others on the same day. So I have been fighting V6 and V7 problems for the past 2 weeks. Up until today I had 19 ETRs open including 2 SEV1s. I'm down [...] 11842 84 64_Re: DB2 connect limitation while accessing DB2 STORED PROCEDURES14_anand savulgay20_aksavulgay@YAHOO.COM31_Mon, 25 Mar 2002 13:58:29 -0800653_us-ascii Yes, those parameters defined exactly same while creating stored procedure using DDL and also in stored procedure. We are okay when we use less than 100 parameters.

Thanks, Anand



--- "Allen, Susan A" wrote: > are your parameters being exactly described on the > table, in the stored > procedure and by the calling statement? > > I found that any slip up caused problems > > Susan A > > -----Original Message----- > From: anand savulgay [mailto:aksavulgay@YAHOO.COM] > Sent: Monday, March 25, 2002 11:09 AM > To: DB2-L@RYCI.COM > Subject: DB2 connect limitation while accessing DB2 > STORED [...] 11927 18 29_What will happen after delete3_V K18_tech@GBRONLINE.COM31_Mon, 25 Mar 2002 16:03:29 -0600488_iso-8859-1 I have a question regarding DB2 Logging, for example if I delete 3000 rows from a table using DELETE FROM A.BCDE, will all the 3000 rows be written to the log or will DB2 write some sort of reference for these 3000 rows.

TIA, VK

================================================ 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. 11946 137 11_FW: Trigger10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 25 Mar 2002 15:21:09 -0700418_iso-8859-1 Hi List,

Thanks for all of you who reply my question.

Grace -----Original Message----- From: Grace Chen Sent: Friday, March 22, 2002 4:21 PM To: DB2 list (E-mail) Subject: Trigger



Hi ! List, I am trying to create a TRIGGER:

CREATE TRIGGER REORG AFTER INSERT ON DSN8710.GRACETB2 FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE DSN8710.GRACETB3 SET REORG_STAT = 'Y'; END [...] 12084 55 33_Re: What will happen after delete13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Mon, 25 Mar 2002 14:24:51 -0800441_us-ascii VK,

All 3000 rows will be written to the log in case there was going to be a rollback, or in case going forward from an image copy recovery.

If you wish to clear out an entire table, do a load replace with an input file of DD dummy.

If you are changing more than some percentage of the table (such as 25%), then consider doing a selective unload followed by a load replace using the selective unload file. [...] 12140 63 19_Re: BMC Change Mgr.16_Teegarden, Steve22_Teegarden@AEGONUSA.COM31_Mon, 25 Mar 2002 16:25:45 -0600563_- Thanks,

Steve Teegarden, DBA AEGON USA, INC. 319-398-8066

> -----Original Message----- > From: Tom Willis [SMTP:tom_r_willis@HOTMAIL.COM] > Sent: Monday, March 25, 2002 3:23 PM > To: DB2-L@RYCI.COM > Subject: Re: BMC Change Mgr. > > We are running PS 8.1.5 against UDB v7.2 and are using BMC change manager > 3.3.01. We found that when we tried to do a complete schema compare > between two v7 databases, it ran up to 6 or 7 hours. You might use > db2look and extract the whole schema ddl, ftp to your local machine and > use BMC to do a ddl [...] 12204 30 53_refresh of table(s) in data prop without a cold start12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Mon, 25 Mar 2002 16:28:41 -0600383_us-ascii I am working with DPROP and DataJoiner and need to refresh a target table without doing a cold start of the capture program on os/390.

The current set up is:

source = os/390 DB2 v6.1 with capture running under a started task. control = UDB/NT middle tier with asnapply running on this server pulling the captures. target = SQL Server through Data Joiner. [...] 12235 21 13_XCTL question3_V K18_tech@GBRONLINE.COM31_Mon, 25 Mar 2002 16:33:23 -0600516_iso-8859-1 We have a program that is being called from lot's of programs, is there an easy way to find out if that program is accessed from a partucular program recently.

For example, I need to know if program ABC is accessed from program DEF recently.

TIA, VK

================================================ 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. 12257 98 84_Residency:DM Tools for Database Administration and Change Management on DB2 for z/OS14_Bart Steegmans17_bsteeg@US.IBM.COM31_Mon, 25 Mar 2002 16:05:10 -0600393_- Hello Listers,

Here is another great opportunity to get your name on an IBM Redbook.

This San Jose residency (yes, sunny California) begins 29 Apr 2002, ends 07 Jun 2002, and requires 1 additional resident. Officially, the residency is 7 weeks. I realize this is a long time to be away from your job and home, so we can be flexible and accept people for a shorter period. [...] 12356 56 36_Trigger to invoke procedure DSNUTILS10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 25 Mar 2002 15:42:39 -0700305_iso-8859-1 Hi List, I want to use trigger to invoke stored procedure DSNUTILS to reorg my tablespace and indexspace and would like to know if it is possible. If anyone there has done this before, would you please give me an example.

Thanks in advance! Grace Chen

12413 59 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks12_Raymond Bell17_rbell@NZ1.IBM.COM31_Tue, 26 Mar 2002 10:50:21 +1200341_us-ascii MM,

In case you're interested, our Prod number drops from 138 to 120 when I do the numbers the long (i.e. probably correct) way.



Raymond PS. I'm feeling decidedly less chastised now. ;o) PPS. The first 'M' above stands for Modest. Not Mad (although I'm sure you'd be happy with the Mel Gibson comparison) [...] 12473 47 33_Re: What will happen after delete14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 25 Mar 2002 23:42:01 +0100 12521 154 33_Re: DEFINE NO Indexes and Logging24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 25 Mar 2002 15:27:00 -0800511_- Hi!

There is a lot of information in the manuals about DBDs. Unfortunately, it's all very scattered among several different ones. But from the admin guide:

Each database has exactly one corresponding DBD that describes the database, table spaces, tables, table check constraints, indexes, and referential relationships. A DBD also contains other information about accessing tables in the database. DB2 creates and updates DBDs whenever their corresponding databases are created or updated. [...] 12676 89 33_Re: What will happen after delete24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Mon, 25 Mar 2002 15:29:25 -0800381_- Hi!

I guess I read this question a bit differently. If VK is deleting from a segmented table without specifying a where clause, then from the manual:

DELETE FROM T without a WHERE clause deletes all rows of T. If T is contained in a segmented table space and is not a parent table, this deletion will be performed without accessing T. SQLERRD(3) is set to -1. [...] 12766 41 33_Re: DEFINE NO Indexes and Logging16_Michael McCawley20_Mike_DBA@HOTMAIL.COM31_Mon, 25 Mar 2002 17:30:01 -0600556_iso-8859-1 Each time you make ANY object change within a database, the entire Before and After image of the DBD is logged. Also, any time you bind (or prepare), the databases of the dependant objects are exclusively locked. Spreading out the objects to more, smaller databases will reduce lock contention during these activities. I know of some companies that have a policy of one database per tablespace, but I have found that a limit of 100 objects / database is usually good enough to avoid excessive contention in my shops. Your mileage may vary. [...] 12808 76 38_Repost for IDUG NA 2002 -- Ed Seminars12_Phil Gunning28_pgunning@BREAKTHROUGHDB2.COM31_Mon, 25 Mar 2002 19:16:43 -0500425_- Take another spin around the block and note the additional educational seminar, "21st Century DBA" presented by BMC Software. One more reason to attend IDUG 2002 - North America and a pre-conference seminar.

Held on May 12 in San Diego, these seminars offer in-depth, focused education on a specific DB2 topic and are taught by some of the industry's leading instructors. This year's seminar offerings include: [...] 12885 102 60_FW: What's the 'mean' row length of a DB2 production table ?24_ANDERSON Margaret * IRMD29_Margaret.Anderson@STATE.OR.US31_Mon, 25 Mar 2002 16:18:30 -0800680_iso-8859-1 Production DB2 V6.1 on OS/390 70449450322 / 259467200 = 271





-----Original Message----- From: Pendlebury-Bowe, Leslie [mailto:Leslie.Pendlebury-Bowe@EU.SONY.COM] Sent: Monday, March 25, 2002 9:02 AM To: DB2-L@RYCI.COM Subject: Re: What's the 'mean' row length of a DB2 production table ?



on our SAP system ... using same as below you get :

1517419481515

2348474673

= 646

rgds

Leslie -----Original Message----- From: Higgins John [mailto:HigginsJohn@JOHNDEERE.COM] Sent: Thursday, March 21, 2002 5:38 PM To: DB2-L@RYCI.COM Subject: Re: What's the 'mean' row length of a DB2 production table ? [...] 12988 44 17_Re: XCTL question31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 26 Mar 2002 00:34:16 +0000551_iso-8859-1 Looks to me a non-DB2 question. You probably need to check CICS log for if at all the program is XCTLed.

With Best Regards, Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration for OS/390

--- V K wrote: > We have a program that is being called from lot's of > programs, is there an > easy way to find out if that program is accessed > from a partucular program > recently. > > For example, I need to know if program ABC is > accessed from program DEF > recently. > > TIA, > VK [...] 13033 34 42_sec: unclassified >> Are extents harmless?15_James Chatfield34_James.Chatfield@DCB.DEFENCE.GOV.AU31_Tue, 26 Mar 2002 11:18:07 +1100512_us-ascii Hi List, We run DB2 v5&6 on Os390 2.10. Traditionally we have aimed to keep tablespaces and indexes in 1 extent. Now our storage people inform us that, thanks to Shark, extents don't matter. And if they do matter they don't matter much. So to help reduce dasd fragmentation they'd like all our objects to be 100,100 cylinders. A quick look at the archives reveals various people discussing traversing the extent chain and therefore increasing CPU. The comeback is that the effect would be minimal. [...] 13068 78 33_Re: DEFINE NO Indexes and Logging31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 26 Mar 2002 02:16:20 +0000313_iso-8859-1 Lori,

I have not found this in the manual, may be i have not searched it properly. But it seems, the status of the DB2 object is changing when data is loaded/inserted into a tablespace/index defined with DEFINE NO clause. And if the status is changed, the DBD should be changed and logged. [...] 13147 31 20_Problem with UDB FP613_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Tue, 26 Mar 2002 13:16:53 +1100428_iso-8859-1 I have just installed Fixpack 6. When I try to rebind the packages I get an error saying..

LINE MESSAGES FOR db2uimtb.bnd ------ -------------------------------------------------------------------- SQL0551N "SYAC30" does not have the privilege to perform operation "BIND" on object "D2TA.NULLID.SQLUKD04". SQLSTATE=42501

I am logged on locally as db2admin and my remote user id (SYAC30) is SYSADM. [...] 13179 65 24_Re: TCP/IP setup for DB212_Raymond Bell17_rbell@NZ1.IBM.COM31_Tue, 26 Mar 2002 14:38:17 +1200352_us-ascii Hi Phil,

Thanks for the Redbook pointer - I forgot all about it - and yes it did 'H'. Fortunately someone here printed it off and I found it where it was supposed to be. Yes it's old but, like you say, it's still the Gov'nor. Spells things out a bit more than the Installation Guide, so the whole thing makes a bit more sense now. [...] 13245 45 24_FW: Problem with UDB FP613_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Tue, 26 Mar 2002 14:04:07 +1100353_iso-8859-1 I've just done a bind via the CCA and although I get the same errors reported I can now access the remote DB.

Is it just me or is there something screwy here?

Steve T -----Original Message----- From: TENNANT Steve (EDS) Sent: Tuesday, 26 March 2002 1:17 PM To: 'DB2 Data Base Discussion List' Subject: Problem with UDB FP6 [...] 13291 94 46_Re: sec: unclassified >> Are extents harmless?15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Tue, 26 Mar 2002 14:01:31 +1100480_us-ascii Well, you need to check both response time and CPU time (traversing extent chains cost CPU)

When I was doing these sort of comparisons, I would check:

1) total CPU time per SQL statement (sum of all jobs class 2 CPU plus sum of DB2 address spaces' CP divided by number of SQL statements). For a fairly constant workload over a reasonable amount of time (I pulled 1 number per week for several months) this gives a nice indicator of relative efficency. [...] 13386 79 46_Re: sec: unclassified >> Are extents harmless?12_Raymond Bell17_rbell@NZ1.IBM.COM31_Tue, 26 Mar 2002 15:09:58 +1200561_us-ascii James,

I can't answer any of your questions, but can make some comments. We (i.e. I) try to keep extents down to 5 or less. Any lower is a waste of effort - for me, anyway; as usual your mileage may vary. You may or may not want, depending on how bolshie you're feeling, to ask your storage bods why, if extents don't matter, they want to try to reduce DASD fragmentation. What DASD fragmentation? On the ESS devices _all_ DASD is fragmented, depending on how you look at it. But given that ESS can provide significant I/O improvements for [...] 13466 118 46_Re: sec: unclassified >> Are extents harmless?15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Tue, 26 Mar 2002 14:22:44 +1100476_us-ascii Raymond, On modern DASD, the impact of fragmentation is to cause allocations to fail (Allocation quantity must be available in a max of 5 extents and all must be on same disk). We used to have regular allocation failures at a prior site, even with running defrags weekly, but when we went to a max of 100,100 cylinders (on an RVA) we had no allocation failures till I left there several years later, even though we were not running defrag jobs on the RVA disks. [...] 13585 76 28_Re: FW: Problem with UDB FP612_Phil Gunning28_pgunning@BREAKTHROUGHDB2.COM31_Mon, 25 Mar 2002 22:35:38 -0500379_- Steve, You were accessing it when you received the first bind errors. Don't know what packages failed but they musn't be ones that effect your ability to connect, you may bump into a problem later. Make sure you have BindAdd authority. HTH Phil



> I've just done a bind via the CCA and although I get the same errors reported I can now access the remote DB. [...] 13662 92 28_Re: FW: Problem with UDB FP613_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Tue, 26 Mar 2002 15:09:02 +1100400_iso-8859-1 Thanks Phil. I still don't understand why I cannot bind if I am in a local administrators group and the SYSADM_GROUP is not set. Surely this means I have SYSADM and so all privileges?

Steve T

-----Original Message----- From: Phil Gunning [mailto:pgunning@BREAKTHROUGHDB2.COM] Sent: Tuesday, 26 March 2002 2:36 PM To: DB2-L@RYCI.COM Subject: Re: FW: Problem with UDB FP6 [...] 13755 68 46_Re: sec: unclassified >> Are extents harmless?12_Phil Gunning28_pgunning@BREAKTHROUGHDB2.COM31_Mon, 25 Mar 2002 23:22:04 -0500643_- James, Check out the latest performance paper on ESS SHARK, F Models. http://www.storage.ibm.com/hardsoft/products/ess/essperf.pdf. They do ROCK! At a previous place we implemented first generation SHARK and it really made a difference with PAV enabled.We saw huge performance improvements despite claims of early problems with SHARK. Make sure that PAV is enabled as it reduces IO queueing. I kept secondary extents to 5 or less. If you read the above reference, you will see bullets indicating it "reduces IO bottlenecks" (doesn't say eliminate:)) and bottom of page 8, the word "contention." IO is still one of the long poles in the [...] 13824 166 46_Re: sec: unclassified >> Are extents harmless?12_Raymond Bell17_rbell@NZ1.IBM.COM31_Tue, 26 Mar 2002 16:25:16 +1200413_us-ascii Richard,

Thanks for the clarification. The fragmentation I was initially thinking of was within the ESS itself, where your datasets can - and will - be spread throughout the device every time they're updated. That's part of the point of the ESS; writes don't update in place, but at the end of available space. The definition of 'available space' is beyond my knowledge of how the ESS works. [...] 13991 102 64_Re: DB2 connect limitation while accessing DB2 STORED PROCEDURES17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Mon, 25 Mar 2002 23:31:00 -0500318_iso-8859-1 I am curious, do you need 100 parameters. Can't you break the big picture into smaller pieces (stored procedures) where the connections (calls) are not that wide? Are you working under the handicap of re-using a lot of existing code? Is the problem. managing large logical units of work? Just Curious. [...] 14094 117 33_Re: What will happen after delete10_James Kwan19_JamesDB2Kwan@CS.COM31_Mon, 25 Mar 2002 23:00:16 -0600451_iso-8859-1 RE: What will happen after deleteI have asked a recovery expert the same question log time ago. The answer I got was only spacemap will be logged.



James Kwan

IBM Certified Solutions Expert. -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Leblanc, Francis C - CNF Sent: Monday, March 25, 2002 5:29 PM To: DB2-L@RYCI.COM Subject: Re: What will happen after delete [...] 14212 144 28_Re: FW: Problem with UDB FP612_Phil Gunning17_db2jock@YAHOO.COM31_Mon, 25 Mar 2002 21:25:55 -0800606_us-ascii Steve, You need to be sysadm on the OS/390 side or have bindadd authority. Could be something else also, but I would make sure you have the privileges then go from there. Cheers! Phil



--- Steve Tennant wrote: > Thanks Phil. I still don't understand why I cannot > bind if I am in a local administrators group and the > SYSADM_GROUP is not set. Surely this means I have > SYSADM and so all privileges? > > Steve T > > -----Original Message----- > From: Phil Gunning > [mailto:pgunning@BREAKTHROUGHDB2.COM] > Sent: Tuesday, 26 March 2002 2:36 PM > [...] 14357 93 24_Re: TCP/IP setup for DB212_leila rahimi21_lshosaini@HOTMAIL.COM31_Tue, 26 Mar 2002 05:35:56 +0000460_- Hi Raymond.i saw your first mail,and then follow answers,i sense your problem haven't been solve?! if your DIST adderss space,start successfully,you must determine DOMAIN,in your message,domain is NONE,in UNIX,on /etc/hosts,you must determine,domain name,and IP of server,it must due to TCPPARM(profile)member. I have the same problem,with the same code about 3 months agoe,just setting in /etc/hosts,correct my problem,please do it,and tell me result. [...] 14451 40 21_Re: STORED PROCEDURES0_26_ASundara@CHN.COGNIZANT.COM31_Tue, 26 Mar 2002 10:50:50 +0530135_iso-8859-1 Hi all,

Can someone kindly tell how to call Stored Procedure from Trigger in DB2.

With Regards, Sundar

14492 212 46_Re: sec: unclassified >> Are extents harmless?15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Tue, 26 Mar 2002 16:49:04 +1100373_us-ascii Yes it does, to a minor extent.

When MVS is working out how to access the record in the VSAM file volume it has to walk through the extent chain (in memory) to determine the "physical" address to ask for.

That this is all rubbish from the Shark's (or RVA's) point of view is irrelevent, the overhead is incurred before the IO request is issued. [...] 14705 144 28_Re: FW: Problem with UDB FP612_Phil Gunning17_db2jock@YAHOO.COM31_Mon, 25 Mar 2002 21:53:55 -0800568_us-ascii Also, for a select, the packages are automatically bound upon first connect to DB2 for z/OS or OS/390. Check for BindAdd, Creatin, and execute on the package. Hey, it is like 1:00 AM here in the US, I'm packing it in for the night. Good Luck, Phil

--- Steve Tennant wrote: > Thanks Phil. I still don't understand why I cannot > bind if I am in a local administrators group and the > SYSADM_GROUP is not set. Surely this means I have > SYSADM and so all privileges? > > Steve T > > -----Original Message----- > From: [...] 14850 15 33_John Ordman is out of the office.0_29_John_Ordman@READERSDIGEST.COM31_Tue, 26 Mar 2002 01:01:26 -0500389_us-ascii I will be out of the office starting 03/25/2002 and will not return until 04/01/2002.

I will respond to your message when I return.

================================================ 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. 14866 101 17_QMF error problem29_RAGHAVAN Vadakkupattu Sampath21_VRaghava@COVANSYS.COM31_Tue, 26 Mar 2002 01:59:32 -0500433_- Hi Everyone,

I need a small help from you all,

When accessing the qmf panel , I am receiving the following error message.

DSQ14440 INTERNAL ERROR DETECTED IN MODULE DSQISRM.

* OS version OS/390 V2R9 * QMF version V6R1 * DB2 version V6R1



When I checked the qmf error messages and codes.

DSQ14440 - DSQ14441





Message: Internal error detected in module &1. [...] 14968 156 33_Re: What will happen after delete16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 26 Mar 2002 07:22:24 -0000398_iso-8859-1 Hi James - you probably remember me from the old Pt-days.

You are right about the SPACEMAP being logged - IF - its a segmented tablespace and IF Data Capture Changes is turned OFF. If Data Capture Changes is ON - then every row is logged. I have seen an unqualified delete on the same table - on the same tablespace and with the same rows execute in sub seconds OR ONE HOUR. [...] 15125 17 60_Catherine T Mercado-Chung/US/Household is out of the office.23_Catherine Mercado-Chung29_ctmercado-chung@HOUSEHOLD.COM31_Tue, 26 Mar 2002 01:29:00 -0600484_us-ascii I will be out of the office starting 03/25/2002 and will not return until 04/01/2002.

For Salinas Mainframe DB2 Support, contact Barbara Nigh. For UDB Support, contact Mark Jones. For Cross Sell Support, contact Robert Schumm.

================================================ 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. 15143 61 22_Question on Accesspath20_Vijayababu Sriramulu34_Vijayababu.Sriramulu@IN.EFUNDS.COM31_Tue, 26 Mar 2002 13:42:53 +0530570_us-ascii Hi List,

I have a quick question on the access path determination by the optimizer in DB2.

I have the following scenario.

Test table has 46 rows Statistics for this table has been updated as in production (Table Cardinality says 121940528 rows. All the related tables used in access path determination are also updated as in production) For column COL_A i have only 2 distinct values. The values are 1 and 2. I have an index on column COL_A. The STATS has been updated on the index with the test statistics. This is a SMALLINT column. [...] 15205 98 26_Re: Question on Accesspath19_Alekos Papadopoulos30_alekospapadopoulos@HOTMAIL.COM31_Tue, 26 Mar 2002 10:42:12 +0200525_- 1) Suppose DB2 for OS/390 2) I agree with the optimizer. If you only have 2 distinct values in the index, then it doesn't make sence to go through the index. You'll have to fetch 50% of the index pages first and then 50% of the datapages (most likely via a list prefetch), which costs more than a sequencial prefetch of the entire table. Since you have only 2 values you will not benefit by using the index. If the value does not exist, then it is much more efficient to go through the index. That's why on 200 access [...] 15304 73 26_Re: Question on Accesspath11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Tue, 26 Mar 2002 08:52:06 +0000555_us-ascii Vijay

If you have a 121 million row table, the optimizer is unlikely to consider that a cardinality of 2 for your index would make a good access path. The reason that it uses the index for the value of 200 is because it knows very few of the rows will qualify (from distribution stats - there are no values of 200). There are, I'm assuming, a great many rows with COL_A = 1. From what you've described, you're not getting index-only access so processing a large number of index entries and also accessing the data pages does not make [...] 15378 20 60_Re: What's the 'mean' row length of a DB2 production table ?10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 26 Mar 2002 10:34:33 +0100509_us-ascii Hi Carol

You're for sure a refined and strong lady. And the fact I'm rich (how can a useless DB2 sysprog be rich ????) doesn't matter ? ;-)

Anyway thanks a lot for your reply

Max Scarpa Db2 sysprog pursued by women (to beat me soundly !)

================================================ 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. 15399 20 69_Re: What's the 'mean' row length of a DB2 production table ? - Thanks10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 26 Mar 2002 10:39:33 +0100484_us-ascii Mel Gibson whoooo ? That black beetle that no women would never touch ? ; -))

Modest is my second name, how do you know it ???

Thanks again to all people who replied (expecially women)

Max Modest Pinocchio 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. 15420 70 19_Re: BMC Change Mgr.14_Thomas, Steven21_Steven_Thomas@BMC.COM31_Tue, 26 Mar 2002 03:57:52 -0600572_iso-8859-1 Tom's correct in that a catalog to DDL compare will work much faster than a catalog to catalog comparison. When you do a catalog to catalog compare, the compare has to do a lot of catalog access to synchronise source and target objects together.

You could use a Migrate worklist instead of the DDL to avoid needing to use db2look - a Catalog to Migrate Worklist compare works very well, and is usually how I recommend my customers to run comparisons. Generating a migrate worklist is very easy and quick, and is probably more reliable than db2look. [...] 15491 210 26_Re: Question on Accesspath20_Vijayababu Sriramulu34_Vijayababu.Sriramulu@IN.EFUNDS.COM31_Tue, 26 Mar 2002 15:48:53 +0530442_iso-8859-1 Hi Alekos,

Thanks for your response !

I have a question on your point 2. If i am going to have only 2 distinct values for this column, i am going to have very less number of index pages to be transferred to the buffer which is more efficient than having all the data pages to be transferred to the buffer by tablespace scan (Except for the advantage you will have for the sequential prefetch). Is this correct ? [...] 15702 67 33_Re: DEFINE NO Indexes and Logging14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 26 Mar 2002 20:38:06 +1000360_US-ASCII My understanding (I've never actually measured it) it that in V6 a single pair of DBD images (one before and one after) is logged for each commit. The number of modifications is not relevent.

Changing table/indexspaces to CLOSE YES (in V4) logged the before and after pairs. (At least I presumed that was what was filling all those logs.) [...] 15770 38 21_Re: STORED PROCEDURES14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 26 Mar 2002 20:38:07 +1000380_US-ASCII CREATE TRIGGER blah blah blah BEGIN ATOMIC CALL MY_SP(more blah); END# (replace # by whatever you use as the SQL terminator)

But have you tried the above (with suitable substitutions) and are having an error? If so, please post your create and the error messages

James Campbell



On 26 Mar 2002 at 10:50, ASundara@CHN.COGNIZANT.COM wrote: [...] 15809 45 25_Question on Buying Laptop15_Durga Vamsidhar22_durga.vamsidhar@DB.COM31_Tue, 26 Mar 2002 11:55:51 +0100548_iso-8859-1 Dear Listers,

First of all I would like to say sorry to use this list in an un-acceptable way. But please help me with the following question.

I want to buy a new Laptop but I am not able to judge myself which one I should take. Definitely lot of people should be having their own Laptops in this list. So, I thought I can ask you the suggestion in this regard. Recently I have seen one laptop from IPC Archtec AG with P-4,256 MB RAM etc. But unfortunately I don't know much about this brand. Pleas give your feedback [...] 15855 23 29_Re: Question on Buying Laptop14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Tue, 26 Mar 2002 05:38:04 -0600538_iso-8859-1 Vamsi,

you are way off topic here.

The answer is really the usual 'it depends' as you do not say what you want to use it for and how much money you have to spend.

All I can suggest is to get the highest spec you can afford.

Colin

(My opinions only. )

================================================ 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. 15879 145 33_Re: DEFINE NO Indexes and Logging14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 26 Mar 2002 12:21:39 -0000350_iso-8859-1 There was some maintenance applied to either V4 or V5 (or maybe both!) that changed the logging for DBDs.

In the "olden" days, you would get a before AND after image of the WHOLE DBD logged for EVERY ALTER/CREATE/DROP that you did.

After the relevant PTF, you now only get a before and after image when you do a commit. [...] 16025 109 33_Re: DEFINE NO Indexes and Logging11_rick creech18_ykcirc@HOTMAIL.COM31_Tue, 26 Mar 2002 06:29:39 -0600520_- Hi,

I got in a little late on this one, so maybe someone else has made the same point... but I would like to point out another major advantage to having more databases and fewer objects per database: any DDL results in fewer objects being locked by means of the DBD lock. If I do a DBD X lock, which happens with any DDL, the other processes cannot even read the DBD. One of the things that needs to read the DBD: dynamic sql. When I do DDL for object1, it will conflict with the need for transaction x to [...] 16135 96 20_Re: PLAN and PACKAGE12_leila rahimi21_lshosaini@HOTMAIL.COM31_Tue, 26 Mar 2002 12:48:01 +0000664_- HI su,always yoy must bind your progrm,or packages,or collections to plan. plan is neccessary for DB2 activities, if you bind a program to a plan ,that plan is only for that program, in the other way you can bind group of programs to a package and then bind package in a plan,there is another way,you can bind a set of programs to a package,and know that a collection is a group of packages, and then bind collection to a plan. program------bind--->plan programS-----bind---package-----bind--->plan programS-----bind---packageS-----collection-----bind--->plan. in the follow is some binds example,this examples are easy,if you need more you van read books. [...] 16232 102 13_A Union query14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 26 Mar 2002 12:54:46 -0000585_iso-8859-1 SQL officionados,

Is it possible to have a UNION within a nested table expression? Because I sure as heck can't get one working.

Marcus



----------------------------------------------------------------------------------------------------------------------- This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. [...] 16335 22 29_Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Tue, 26 Mar 2002 08:12:54 -0500364_iso-8859-1 Hi Esteem colleagues

How do I find the timestamps in a DBRM module ?

Someone had restore the application (from backups), but it is not acting as original image, so I would like to find if the modules are at the correct level and have the correct timestamps.

The Load modules look correct, but how do I verify the DBRM module ? [...] 16358 43 26_Re: Question on Accesspath19_Alekos Papadopoulos30_alekospapadopoulos@HOTMAIL.COM31_Tue, 26 Mar 2002 15:33:55 +0200325_- Well, in my opinion, the number of index pages will be large enough to accomodate all these millions of rids, although the actual values are only 2. It will therefore cost you in performance. If however, the columns you need exist in the index (and you get an index only access path) you will have better performance. [...] 16402 156 21_Re: QMF error problem11_Moore, Tony15_TMoore@IKON.COM31_Tue, 26 Mar 2002 08:32:11 -0500564_iso-8859-1 Yo Raghavan, For the -805 in SPUFI, here's a few possibilities that come to mind...

1. Your TSO logon proc has a load library allocated to it which does not contain the latest copy of the SPUFI load modules. 2. If your logon proc does not have the loadlib (where the SPUFI modules reside) allocated to it, it could be that a loadlib in the linklist does not contain the latest copy. 3. Whoever installed/upgraded your DB2 subsystem last, did not run the install job which binds the SPUFI packages or the job failed and they didn't catch it. [...] 16559 129 20_Re: PLAN and PACKAGE41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Tue, 26 Mar 2002 14:38:23 +0100398_iso-8859-1 Leila,

I'm sure you are aware of this but there's no need to issue the first bind using ACTION(ADD), you can always use ACTION(REPLACE), DB2 will add the package all the same.

Hth Ruediger

-----Ursprüngliche Nachricht----- Von: leila rahimi [mailto:lshosaini@HOTMAIL.COM] Gesendet: Dienstag, 26. März 2002 13:48 An: DB2-L@RYCI.COM Betreff: Re: PLAN and PACKAGE [...] 16689 43 17_Re: A Union query15_Satyakant Evani17_evanis6@ATTBI.COM31_Tue, 26 Mar 2002 13:31:51 +0000635_- This feature is new in Version 7. Evani > SQL officionados, > > Is it possible to have a UNION within a nested table expression? Because I > sure as heck can't get one working. > > Marcus > > > -------------------------------------------------------------------------------- > --------------------------------------- > This e-mail is intended only for the above addressee. It may contain > privileged information. If you are not the addressee you must not copy, > distribute, disclose or use any of the information in it. If you have > received it in error please delete it and immediately notify the sender. > > evolvebank.com [...] 16733 44 33_Re: Timestamps in the DBRM module11_Moore, Tony15_TMoore@IKON.COM31_Tue, 26 Mar 2002 08:39:42 -0500422_iso-8859-1 Yo Manish, View the module in ISPF and enter "HEX ON" on the command line to see the data in hex. Then check out positions 25 through 32 on the first line of the DBRM module (just right of the DBRM name).

HTH, Tony

-----Original Message----- From: Manish Kothari [mailto:mkothari@DATAMIRROR.COM] Sent: Tuesday, March 26, 2002 8:13 AM To: DB2-L@RYCI.COM Subject: Timestamps in the DBRM module [...] 16778 43 29_Re: Question on Buying Laptop5_chris22_hhardy1@HOUSTON.RR.COM31_Tue, 26 Mar 2002 07:26:46 -0600603_iso-8859-1 I use a Dell with Pent III, 256M Ram, 30G harddrive, CD R-W, WIN 2000 Professional....... chris hardy, managing partner



----- Original Message ----- From: "Clayton, Colin" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, March 26, 2002 5:38 AM Subject: Re: Question on Buying Laptop



> Vamsi, > > you are way off topic here. > > The answer is really the usual 'it depends' as you do not say what you want > to use it for and how much money you have to spend. > > All I can suggest is to get the highest spec you can [...] 16822 18 61_IBM announces price decreases on some DB2 for OS/390 software12_McKown, John22_JMckown@HEALTHAXIS.COM31_Tue, 26 Mar 2002 08:06:29 -0600179_iso-8859-1 302037 Price Changes: IBM Database 2 Universal Database Server for OS/390 Selected Features (3.7KB) http://isource.ibm.com/cgi-bin/goto?it=usa_annred&on=302-037

16841 31 17_Re: A Union query11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Tue, 26 Mar 2002 09:07:48 -0500421_ISO-8859-1 Marcus

Assuming DB2 for z/OS and OS/390. Yes, provided you are on Version 7. If Version 6 or before, no.

Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk

Message text written by DB2 Data Base Discussion List >SQL officionados,

Is it possible to have a UNION within a nested table expression? Because I sure as heck can't get one working. [...] 16873 62 33_AW: Timestamps in the DBRM module12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 26 Mar 2002 15:12:41 +0100468_iso-8859-1 Hi Manish,

select the option 3.4 inside the ISPF/PDF PRIMARY OPTION MENU, select a DBRMLIB, select a DBRM and you will see the following (especially the second line of a DBRM):

DBRM...µF080 DVZAXE00.ê...Óêc..2.......................................1..Ø.F ..1999-07-19-14.07.31.445066 <<<<<<<<< here is your timestamp !! ..............DBRM...........p........DECLARE DFZ02 TABLE ( DFZ02_ARTSCHL CHAR ( 1 ) NOT NULL

Hope that helps. [...] 16936 16 29_DEFINE NO Indexes and Logging11_Lori Bewley28_lorinda.l.bewley@US.HSBC.COM31_Tue, 26 Mar 2002 07:27:46 -0600416_- Thanks for all the great responses. I've since found out that the PeopleSoft tool being used to import the data, Data Mover, does a commit after each table import, so the DBD is being logged repetitively, hence the huge amounts of logs being generated.

Rick, does SAP deliver the DDL with 9000+ databases or do you have to break it out yourself? I wish PeopleSoft would get a little more DBA friendly! [...] 16953 157 17_Re: A Union query13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 26 Mar 2002 08:24:21 -0600482_iso-8859-1 A Union queryMarcus,

In V7 on OS/390 DB2 only, or non-OS/390 since V2 (approx)......

Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Davage, Marcus Sent: Tuesday, March 26, 2002 6:55 AM To: DB2-L@RYCI.COM Subject: A Union query [...] 17111 62 33_Re: Timestamps in the DBRM module9_Joe Huang25_Joe_Huang@NAVYFEDERAL.ORG31_Tue, 26 Mar 2002 09:27:18 -0500673_iso-8859-1 4 bytes after DBRM name is the consistency stamp ======================>HERE<==================== DBRM µEOT03 ATMBRIMD ¥ >ôÃ 2 CCDD000ACDEFF444CEDCDCDC1B3006C600F0000000000000000 429400005630300013429944624A9EB66020020000000000000









Manish Kothari @RYCI.COM> on 03/26/2002 08:12:54 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Timestamps in the DBRM module



Hi Esteem colleagues

How do I find the timestamps in a DBRM module ? [...] 17174 36 43_Creation of INSTANCES and NODES.Urgent need17_jennifer jennifer22_jenni_jeni@HOTMAIL.COM31_Tue, 26 Mar 2002 14:32:08 +0000383_- Hi All experts , here is need for me to seek the help of you. While moving from mainframe to UDB I need the following clarification. I m writing my doubts. I would highly oblidged if any one suggest the methods and clarifies the doubts.

How I would create the INSTANCES and NODES on NT. I mean where I have to creat it. Is it to be created at client or at server side. [...] 17211 80 26_Re: Question on Accesspath14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 26 Mar 2002 14:36:40 -0000360_iso-8859-1 What DB2 is saying here is the following

"If I access the table through the index, unless the table is clustered by the key, then I might get only one row per GETPAGE. On the other hand, if I do a tablespace scan, I will get prefetch and there will be NO I/O wait to the application at all." In fact, it's an easy choice for DB2 to make [...] 17292 74 28_Re: Db2 Command Centre error0_16_khampto1@TXU.COM31_Tue, 26 Mar 2002 08:35:53 -0600389_us-ascii Do you know if you have the Control Center Enablement feature installed on your OS/390 DB2 ? This is a free (I believe) feature, but it is installed as a separate FMID. It mostly provides some stored procedures to invoke utilities such as Runstats.









"Foweather, Iris" @RYCI.COM> on 03/25/2002 10:19:14 AM [...] 17367 62 33_Re: Timestamps in the DBRM module13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 26 Mar 2002 09:36:08 -0500418_iso-8859-1 Hello Manish.

The consistency token begins in the 25th position of the DBRM in your PDS. Prior to that are the Userid of the user doing the precompile, and the Member name.

Depending on the language and compiler, you may also see a readable timestamp. Hope this is the information you're seeking. If you're not an ISPF-comfy person, holler and we can explain the browse hex format below. [...] 17430 14 46_Force index access when using WHERE with UCASE14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Tue, 26 Mar 2002 08:37:37 -0600420_iso-8859-1 I have a user that is using the UCASE clause in a WHERE predicate. How can I get the SQL to use an index in that predicate? Currently, explain shows a tablespace scan.

================================================ 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. 17445 41 33_Re: DEFINE NO Indexes and Logging11_rick creech18_ykcirc@HOTMAIL.COM31_Tue, 26 Mar 2002 08:32:34 -0600627_- Hi,

In SAP you have the option of breaking some out on a one-by-one basis, but with each new release of SAP, SAP has been breaking out more and more of them. We are at 4.6C which gives the current number at around 9,000+.



>From: Lori Bewley >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: DEFINE NO Indexes and Logging >Date: Tue, 26 Mar 2002 07:27:46 -0600 > >Thanks for all the great responses. I've since found out that the >PeopleSoft tool being used to import the data, Data Mover, does a commit >after each table import, [...] 17487 23 42_Needed urgently a script for UDB utilities8_duam lee20_duam_lee@HOTMAIL.COM31_Tue, 26 Mar 2002 14:42:29 +0000461_- Good Day masters. Can any one provide me a script program for dynamic configuration on diffrent servers. Similarily can any one provide me a script program for running utilities like REORGCHK,RUNSTATS etc.

It would be a great help to me. Thanking you all in advance for the support. Duam





_________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com [...] 17511 111 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Tue, 26 Mar 2002 09:43:11 -0500823_iso-8859-1 Hi Tony/Georg,

Thank you for the assistance, but that did not work.

Here is part of my first two lines in HEX...

----+----1----+----2----+----3----+ ----+----F----+----F----+----F----+ ----+----1----+----2----+----3----+ ----------------------------------- DBRM µUSERID1 TSDDAA NÀ% Pm D CCDD000AEECDCCF4EECCCC441D6603D900C 429400004259941032441100654C8E74704 -----------------------------------

00444444444444444444444444444444444 00000000000000000000000000000000000

Do I need to convert the last 8 bits from HEX to Numeric

Thanks

Manish



Yo Manish, View the module in ISPF and enter "HEX ON" on the command line to see the data in hex. Then check out positions 25 through 32 on the first line of the DBRM module (just right of the DBRM name). [...] 17623 70 33_Re: Timestamps in the DBRM module13_Schulz Thomas20_thomas.schulz@SVI.DE31_Tue, 26 Mar 2002 15:43:46 +0100671_iso-8859-1 Hi Manish

3.4 in ISPF to your DBRM Library and Hex ON

----+----1----+----2----+----3----+----4 ----+----F----+----F----+----F----+----F ----+----1----+----2----+----3----+----4 --------------------------------------- ********************************* Top of



--------------------------------------- DBRM...µIULJO MHPAPL .\ê..b.@..C..... CCDD000ACEDDD444DCDCDD441E50080B00C00000 4294000094316000487173006C24D22520300200

column 25 - 28 and 29 - 32 are your timestamp in the dbrm. the precompile generates sql in your programm, see timestamp1 and timestamp2. convert the hex from the dbrm to dec, than it will be the same. [...] 17694 101 17_Re: A Union query14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 26 Mar 2002 14:44:31 -0000669_iso-8859-1 >Assuming DB2 for z/OS and OS/390. Yes, provided you are on Version 7. If Version 6 or before, no.

Thanks. Our DB2 for OS.390 version 5.1 answers the question. Oh, well. Time to twiddle my thumbs for 18 months...

Marcus



----------------------------------------------------------------------------------------------------------------------- This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. [...] 17796 193 26_Re: Question on Accesspath13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 26 Mar 2002 08:53:18 -0600322_iso-8859-1 Vijay,

True that there can be much less index pages, but because the columns in your select list reside in the index and data (so you cannot get indexonly access), then you will be transferring the index and data pages into the buffer, whereas the tablespace scan will transfer just the data pages. [...] 17990 17 58_CA Platinum release -- Compatible with version 6 and/or 7?12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Tue, 26 Mar 2002 09:02:31 -0600311_iso-8859-1 We are planning to migrate from DB2 version 6 to version 7 on our OS/390 system and we currently have P99E on our TECH system.

My question is should I go ahead and put P99E into production so I'm ready for DB2 version 7 or should I order P99F and install that version of the CA products? [...] 18008 106 27_Re: DDL code for procedures14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM31_Tue, 26 Mar 2002 09:15:08 -0600601_us-ascii Helmar,

Regarding your first question, I believe the Control Center's "Generate DDL" option uses the "db2look" command, and the documentation for db2look does not list stored procedures as one of the data objects for which db2look can extract DDL.

The error messages appear to relate to the statement termination character. Without seeing the actual DDL you ran, and how you invoked db2, it's hard to tell what happened. Did you use the "-t" option and semicolons as statement terminators? (See the section on "Command Line Processor Options" in the DB2 Command Reference. [...] 18115 39 50_Re: Force index access when using WHERE with UCASE31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 26 Mar 2002 15:30:32 +0000334_iso-8859-1 I presume if you are giving UCASE(COLNAME) = :HV. Then your HV is in the upper case. Why not try and reverse the same to have COLNAME = LCASE(:HV). This will give you the index access you are looking for.

With Best Regards, Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration for OS/390 [...] 18155 35 21_Re: STORED PROCEDURES10_Shery Hepp17_schepp@SRPNET.COM31_Tue, 26 Mar 2002 08:46:01 -0700505_iso-8859-1 Sundar- here's one example - (this is for db2 v6 os/390)

CREATE TRIGGER X0DB27.RCAPA001 AFTER INSERT ON F0DB27.PS_VENDOR REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL X0DB27.SP11200A ('7' ,N.VENDOR_ID ,N.SETID ,CURRENT DATE ,'Y',0) ; END

hth's Shery





-----Original Message----- From: ASundara@CHN.COGNIZANT.COM [mailto:ASundara@CHN.COGNIZANT.COM] Sent: Monday, March 25, 2002 10:21 PM To: DB2-L@RYCI.COM Subject: Re: STORED PROCEDURES [...] 18191 43 62_Re: CA Platinum release -- Compatible with version 6 and/or 7?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 26 Mar 2002 10:45:15 -0500436_iso-8859-1 Does P99E support the 'J' datasets created by REORG SHRLEVEL CHANGE? Does P99F? Does P99E RC/Migrator handle things correctly when you change the primary key/unique index? Does P99F? Does P99E support the changes/additions to TABLE CONSTRAINT? Does P99F? Ask the same question everywhere you see a '|' on the left of the V7 manuals. If you are exploiting many of the new V7 features you might even need newer than P99F. [...] 18235 136 46_Re: sec: unclassified >> Are extents harmless?24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Tue, 26 Mar 2002 07:49:57 -0800444_- Regarding secondary extents: We recently encountered a problem when allocating SECQTY in a pool that was tight on space. The pool was tight enough that the secondary allocation could not be satisfied in 5 or fewer extents on any disk volume in the pool. Rather than fail, DB2 actually allocated additional space in 2-cylinder increments. For the volume that we were running at the time, we reached 251 total extents in just a few hours. [...] 18372 140 27_Re: DDL code for procedures14_Helmar Martens22_hmartens@BOWSTREET.COM31_Tue, 26 Mar 2002 10:54:12 -0500384_iso-8859-1 Hi Steve,

Thanks for your reply. I really appreciate it.

You are right about the fact that the Control Center uses db2look. The documentation does not list stored procedures, but I thought they had listed some of the objects. It also states that the command creates DDL for all the objects in the db. Aren't the Procedures db objects? Well, no big deal. [...] 18513 35 33_Re: Timestamps in the DBRM module13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 26 Mar 2002 11:01:45 -0500692_iso-8859-1 Hello again Manish. The consistency token in your DBRM is:

14Dc686e0736D790

If you're browsing your load module and want to look for this you can issue the command: Find x'14Dc686e0736D790'

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













The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 18549 29 62_Re: CA Platinum release -- Compatible with version 6 and/or 7?7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Tue, 26 Mar 2002 11:03:29 -0500572_ISO-8859-1 The P99 releases are only v7 tollerant. One big problem is they don't support the fastswitch option of online reorg. If you use this option and your file names are using "J" instead if "I" Platinum win't find them. Supposedly releae P01 is scheduled for 2nd quarter, but I'm sure a CA person will jump in with a good date. > > From: "Martin, Paul" > Date: 2002/03/26 Tue AM 10:02:31 EST > To: DB2-L@RYCI.COM > Subject: CA Platinum release -- Compatible with version 6 and/or 7? > > We are planning to migrate from DB2 version 6 to [...] 18579 52 50_Re: Force index access when using WHERE with UCASE14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Tue, 26 Mar 2002 10:10:34 -0600534_iso-8859-1 The COLNAME in the DB has both upper and lower case so the user has WHERE UCASE(CLNAME) = :HV

-----Original Message----- From: Sanjeev .... [mailto:sanjeev_db2l@YAHOO.CO.IN] Sent: Tuesday, March 26, 2002 9:31 AM To: DB2-L@RYCI.COM Subject: Re: Force index access when using WHERE with UCASE



I presume if you are giving UCASE(COLNAME) = :HV. Then your HV is in the upper case. Why not try and reverse the same to have COLNAME = LCASE(:HV). This will give you the index access you are looking for. [...] 18632 23 19_QMF EXPORT question15_Schaeffer, Dave25_dave.schaeffer@BENDIX.COM31_Tue, 26 Mar 2002 09:15:56 -0700310_- Hi, We are on QMF v3.3 on OS/390. I'm trying to EXPORT about 12 thousand rows and I keep getting a B37-04 on the EXPORT dataset. I am deleting the dataset before issuing the EXPORT command. Is there anyway to increase the size of the dataset before issuing the command? Or do I need to pre-allocate it? [...] 18656 193 33_Re: Timestamps in the DBRM module12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 26 Mar 2002 18:34:59 +0200910_iso-8859-1 Hi, That should make it easy ...

/* REXX */ TRACE n PARSE upper arg PARM call init /****************************************************************/ LIBS: LIB = "'HLQ.DBRMLIB.INWORK("PARM")'" CALL CHKLIB LIB

call nextlib LIB = "'HLQ.DBRMLIB.READY("PARM")'" CALL CHKLIB LIB

call nextlib LIB = "'HLQ.DBRMLIB.EMER("PARM")'" CALL CHKLIB LIB

call nextlib LIB = "'HLQ.DBRMLIB.PROD("PARM")'" CALL CHKLIB LIB

RETURN /****************************************************************/ CHKLIB: PARSE UPPER ARG lib1 IF SYSDSN(lib1) <> OK THEN SAY " MEMBER " lib1 " NOT FOUND " ELSE do ; SAY " " SAY " MEMBER " lib1 call text ; end ; RETURN /****************************************************************/ nextlib: call init say " " say "***********************************************************" say "***********************************************************" say [...] 18850 96 50_Re: Force index access when using WHERE with UCASE31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 26 Mar 2002 16:49:47 +0000371_iso-8859-1 This is surely an exception and for this requirement,you need to transfer the WHERE UCASE(CLNAME) to SELECT UCASE(CLNAME) and form a subuery. Performance depends on the query and size of result set.

Something like

SELECT COL1, COL2, COLX FROM (SELECT COL1, COL2, UCASE(COL3) as COLX FROM T1 WHERE all other conditions) AND COLX = :HV) AS V1 [...] 18947 53 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Tue, 26 Mar 2002 11:51:41 -0500504_iso-8859-1 Hi,

I don't understand how you found consistency token.



Manish -----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Tuesday, March 26, 2002 11:02 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module



Hello again Manish. The consistency token in your DBRM is:

14Dc686e0736D790

If you're browsing your load module and want to look for this you can issue the command: Find x'14Dc686e0736D790' [...] 19001 108 28_Re: FW: Problem with UDB FP613_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 26 Mar 2002 10:51:50 -0600562_iso-8859-1 Is it possible that the datasource definition in DB2Connect is setting SQLID=xxx (See Enterprise tab) which does not have the privileges. That got me recently.

-----Original Message----- From: Steve Tennant [mailto:steve.tennant@CUSTOMS.GOV.AU] Sent: Monday, March 25, 2002 10:09 PM To: DB2-L@RYCI.COM Subject: Re: FW: Problem with UDB FP6



Thanks Phil. I still don't understand why I cannot bind if I am in a local administrators group and the SYSADM_GROUP is not set. Surely this means I have SYSADM and so all privileges? [...] 19110 225 40_FW: Trigger to invoke procedure DSNUTILS10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Tue, 26 Mar 2002 10:06:00 -0700730_iso-8859-1 Hi List, I am trying to create a trigger to invoke stored procedure DSNUTILS and got this error: SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -199, SQLSTATE 42601, AND MESSAGE TOKENS TABLESPACE,) , SQLSTATE = 56059 SQLSTATE RETURN CODE SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR This is my trigger definition: CREATE TRIGGER GTRIG3 AFTER INSERT ON DSN8710.GRACETB2 FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL SYSPROC.DSNUTILS( RGRACE,NO, REORG TABLESPACE DSN8D71A.GRACETS3 SHRLEVEL REFERENCE SORTKEYS, RC, REORG TABLESPACE, DB2.UNLOAD.SYSREC,3390,10,,,0,,,0, DB2.SN8D71A.GRACETS3,3390,10,,,0,,,0,,,0,,,0,,,0,,,0,,,0,,,0); [...] 19336 28 5_SPACE11_Todd Martin15_tm9us@YAHOO.COM31_Tue, 26 Mar 2002 09:08:25 -0800639_us-ascii Hi All,

Im trying to download 52,000,000,000 bytes of data and trying to store in a SEQ file .Can anyone tell me what will be the best numbers in SPACE

SPACE=(CYL,(500,500),RLSE) is OK ??? or any suggesstions

HTH, TM

__________________________________________________ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19365 91 33_Re: Timestamps in the DBRM module13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 26 Mar 2002 12:25:40 -0500476_iso-8859-1 I apologize, Manish. I confused you by giving you the wrong con-token. I forgot that I was looking at the email in a proportional font.

The real consistency token is 16D5646C083ED7

In case you're unfamiliar with the ISPF browse/edit Hexadecimal display format, let me bore everyone who is by explaining it.

Many products on z/OS, including ISPF browse, display data in hex format using 3 lines: For example, take the character string ABC. [...] 19457 15 55_Sashi Mannepalli/CONSULT/HQ/FHLMC is out of the office.19_Shashi B Mannepalli31_sashi_mannepalli@FREDDIEMAC.COM31_Tue, 26 Mar 2002 12:30:28 -0500389_us-ascii I will be out of the office starting 03/26/2002 and will not return until 03/27/2002.

I will respond to your message when I return.

================================================ 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. 19473 52 23_Re: QMF EXPORT question11_Riyaz Momin15_RMomin@FHSC.COM31_Tue, 26 Mar 2002 12:28:31 -0500665_US-ASCII Dave,

You can pre-allocate the dataset with large PRIM & SEC quantity, refer to the previous dataset you mentioned in EXPORT for dataset attributes. EXPORT Data to pre-allocated dataset. You will see a warning message, accept the replace dataset option.

Regards, Riyaz. Application Programmer. First Health Services Corp. (804)965-7580.

>>> dave.schaeffer@BENDIX.COM 03/26/02 11:15AM >>> Hi, We are on QMF v3.3 on OS/390. I'm trying to EXPORT about 12 thousand rows and I keep getting a B37-04 on the EXPORT dataset. I am deleting the dataset before issuing the EXPORT command. Is there anyway to increase the size of the dataset [...] 19526 106 9_Re: SPACE0_20_John_Lendman@FPL.COM31_Tue, 26 Mar 2002 12:44:52 -0500407_iso-8859-1 Using the 3390 as a model. Which holds 56,664 bytes per Track And 15 Tracks per Cylinder. You would need 61,179 cyls to hold you data. Now it you were to allocated 500,500,using 16 extents for Seq dataset, you would get 8,000 cyls. So I would say that this will not fit. You need to allocate a bigger primary and secondary, that is if you actual have the DASD to hold this. Hope this helps. [...] 19633 51 9_Re: SPACE20_Sirlapu Srinivasarao33_Sirlapu_Srinivasarao@VANGUARD.COM31_Tue, 26 Mar 2002 12:48:27 -0500540_us-ascii I think you require 86000 cyclinders so i think you need to give (cyl,(5000,5000),rlse).











Todd Martin on 03/26/2002 12:08:25 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Sirlapu Srinivasarao/IT/VGI) Subject: SPACE





Hi All,

Im trying to download 52,000,000,000 bytes of data and trying to store in a SEQ file .Can anyone tell me what will be the best numbers in SPACE [...] 19685 91 29_Secondary allocation question21_Powers, Carol A - CNF20_Powers.Carol@CNF.COM31_Tue, 26 Mar 2002 09:52:15 -0800605_iso-8859-1 Dear Listpeople,

We recently had an unfortunate incident where a tablespace partition reached the maximum number of extents and had to be reorganized. This caused an application outage and an unhappy user. Our environment is DB2 6.1 on OS/390 2.10. There was a space constraint in the storage pool at the time, so we would have expected problems, but didn't expect what happened. The primary space requested was 81 cylinders and the secondary was 405 cylinders. When we looked at it later we saw that most of the extents in the dataset were allocated at 2 cylinders, so when things [...] 19777 43 23_Re: QMF EXPORT question14_Harvey Puckett19_hpuckett@GTA.GA.GOV31_Tue, 26 Mar 2002 12:51:22 -0500639_iso-8859-1 Hey Dave ... yeah, just run your EXPORT DATA or TABLE in batch, and you can preallocate what you will to DSQPRINT ... e-mail me offline if you need example ...

-----Original Message----- From: Schaeffer, Dave [mailto:dave.schaeffer@BENDIX.COM] Sent: Tuesday, March 26, 2002 11:16 AM To: DB2-L@RYCI.COM Subject: QMF EXPORT question



Hi, We are on QMF v3.3 on OS/390. I'm trying to EXPORT about 12 thousand rows and I keep getting a B37-04 on the EXPORT dataset. I am deleting the dataset before issuing the EXPORT command. Is there anyway to increase the size of the dataset before issuing the command? [...] 19821 28 50_TCP/IP failure while calling DB2 stored procedure.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 26 Mar 2002 12:58:01 -0500495_iso-8859-1 We are getting following error message some time in DB2MSTR log

12.11.45 STC29271 DSNL511I :DB2P DSNLIENO TCP/IP CONVERSATION FAILED

TO LOCATION 172.30.24.202

IPADDR=172.30.24.202 PORT=1152

SOCKET=READ RETURN CODE=1121 REASON CODE=00000000

Does anybody know anything about this error? The same stored procedure call with same data work fine in next call. We are using DB2 V6.1 with Put level 0104 on OS/390 2.9. TCPIP is at 2.8 put level 2008. [...] 19850 62 9_Re: SPACE17_David Eugene Will26_david.e.e.will@VERIZON.COM31_Tue, 26 Mar 2002 13:00:29 -0500598_iso-8859-1 TM

If your using 3390 architecture this should fit on 62,000 cylinders. Have you considered using tape?

Dave







Todd Martin @RYCI.COM> on 03/26/2002 12:08:25 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



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



Hi All,

Im trying to download 52,000,000,000 bytes of data and trying to store in a SEQ file .Can anyone tell me what will be the best numbers in SPACE [...] 19913 39 9_Re: SPACE7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Tue, 26 Mar 2002 13:03:59 -0500626_ISO-8859-1 That equates to about 21 volumes. Good luck. > > From: Todd Martin > Date: 2002/03/26 Tue PM 12:08:25 EST > To: DB2-L@RYCI.COM > Subject: SPACE > > Hi All, > > Im trying to download 52,000,000,000 bytes of data and > trying to store in a SEQ file .Can anyone tell me what > will be the best numbers in SPACE > > SPACE=(CYL,(500,500),RLSE) is OK ??? or any > suggesstions > > HTH, > TM > > __________________________________________________ > Do You Yahoo!? > Yahoo! Movies - coverage of the 74th Academy Awards® > http://movies.yahoo.com/ > > ================================================ [...] 19953 66 9_Re: SPACE11_Moore, Tony15_TMoore@IKON.COM31_Tue, 26 Mar 2002 12:58:12 -0500355_iso-8859-1 Yo Todd, Some of the Guru's may have something closer, but when calculating space for my DB2 tables, for use with 3390 Mod 3 devices, I figure at 49,152 bytes per track. Assuming you're using (or emulating) mod 3's...

52,000,000,000 / 49,152 = 1,057,943 tracks 1,057,943 / 15 = 70,530 cylinders 70,530 / 3300 = 21.37 3390-3 devices [...] 20020 117 50_Re: Force index access when using WHERE with UCASE13_Terry Purcell23_purcell_terry@YAHOO.COM31_Tue, 26 Mar 2002 10:04:00 -0800593_us-ascii Sanjeev/Al,

I don't believe this is an exception. In fact it was just covered 2 weeks ago on DB2-L (search the archives for "case insensitive searches".

The example you provide will still perform a tablespace scan.

The previous posts pointed to a paper written which solves this problem for non-OS/390 DB2. OS/390 solutions include adding an additional column to the table with all upper case values (populated at insert/update time using UCASE) and using this in the WHERE clause as UC_CLNAME = UCASE(:HV); or adding redundant indexable predicates such as: [...] 20138 18 21_3390 Mod 9 Experience15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Tue, 26 Mar 2002 10:43:52 -0800551_iso-8859-1 Please provide feedback on 3390-9 experience good / bad. We are currently 3390-3 only. As we encounter large objects, we are wondering if it would be easier to size the allocations on larger volumes. DASD is currently EMC 5xxx series soon to be 8830. Storage is SMS controlled.

Thanks, Steve

================================================ 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. 20157 175 21_Re: QMF error problem11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Tue, 26 Mar 2002 13:56:49 -0500442_iso-8859-1 Another possibility for the SPUFI problem is that the installer didn't bind DSNESPRR -- only DSNESPCS to prevent people from using repeatable read. Change your SPUFI default to use DSNESPCS instead and I bet it will work. I don't know about the QMF problem.

Tina

-----Original Message----- From: Moore, Tony [mailto:TMoore@IKON.COM] Sent: March 26, 2002 8:32 AM To: DB2-L@RYCI.COM Subject: Re: QMF error problem [...] 20333 151 50_Re: Force index access when using WHERE with UCASE31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 26 Mar 2002 18:56:54 +0000599_iso-8859-1 Terry,

I did miss the post few weeks ago. Thanks for pointing me out to the archive.

What i meant from exception is exception to my solution in the first mail where i said, COLNAME = LCASE(:HV)

Thanks

With Best Regards, Sanjeev

--- Terry Purcell wrote: > Sanjeev/Al, > > I don't believe this is an exception. In fact it was > just covered 2 weeks ago on DB2-L (search the > archives > for "case insensitive searches". > > The example you provide will still perform a > tablespace scan. > > The previous posts pointed to a [...] 20485 146 50_Re: Force index access when using WHERE with UCASE13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 26 Mar 2002 12:58:58 -0600450_iso-8859-1 We use the same technique of adding another column. Used a trigger to convert the extra column to upper case. Our case was that some names it is not always easy to determine which character is upper, lower or a typo. e.g. (Mackey, MacKey, McKey etc").

It is a bit unsightly for programmers to reference column X in the predicate but really want column Y - but they are flexible and it is efficient - which the end users likes. [...] 20632 108 33_Re: Secondary allocation question27_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM31_Tue, 26 Mar 2002 14:03:10 -0500387_iso-8859-1 This isn't an answer to your question, but another option you had was to move the dataset to another volume via DFDSS. That would have cleaned up the extents and taken less time.

Tina

-----Original Message----- From: Powers, Carol A - CNF [mailto:Powers.Carol@CNF.COM] Sent: March 26, 2002 12:52 PM To: DB2-L@RYCI.COM Subject: Secondary allocation question [...] 20741 35 62_Re: CA Platinum release -- Compatible with version 6 and/or 7?9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 26 Mar 2002 12:23:02 -0700381_us-ascii I would order P99F maint and P99G maint and put them both on in one roll out. P99G should be released on 4/15 and would get you the most up to-date code and functionality.



The Exploitation Support Matrix for CA products supporting DB2 UDB for z/OS and OS/390 Version 7 can be found at: http://intranet.cai.com/db2/prodinfo/ca_db2_v7_support_matrix.pdf [...] 20777 45 37_Unexpected RI Result : is this normal19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Tue, 26 Mar 2002 19:28:18 +0000640_- DB2 V7.2 on Linux

I create a table -

T0010STOCK_MARKET_DATA

with primary key (EXCHANGE_SYMBOL, SECURITY_SYMBOL);

Created another table -

T0020TASK

with primary key (TASK_ID)

and the fields EXCHANGE_SYMBOL and SECURITY_SYMBOL as nullable fields (since every task does not apply to a specific security).

Now am able to insert the following records -

(TASK_ID, EXCHANGE_SYMBOL, SECURITY_SYMBOL) 1,NULL,NULL (I expect this to work) 1,'US','MSFT' (again I'd expect this) 1, '1', 'MSFT' (this fails : I'd expect that) 1, '1', NULL (didn't expect this) 1, NULL, '1') (or this) [...] 20823 112 63_Re: CA Platinum release -- Compatible with version 6 and/ or 7?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 26 Mar 2002 19:59:01 -0000367_iso-8859-1 Correct - we're in the beta cycle right now (and still looking for volunteers, if anyone is interested.... - drop me a line OFF LIST if you are). How the beta cycle goes will dictate when the production release hits the streets.

For now, though, it is true that the P99 series is only V7 tolerant - if you're using new features you'll need P01 [...] 20936 57 41_Re: Unexpected RI Result : is this normal9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 26 Mar 2002 13:14:54 -0700380_us-ascii If any of the columns in a composite RI relationship is null the entire relationship is null and individual columns are not validated since the entire relationship is set to null.

I believe Oracle has the ability to still validate the non null columns of a composite FK relationship, but DB2 does not do this, with DB2 the entire relationship is set to null. [...] 20994 144 33_Re: Secondary allocation question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 26 Mar 2002 20:09:22 -0000509_iso-8859-1 IMHO failing an application is the LAST thing DB2 should do - the "minimal allocation" rules in Db2 saved me many times.

As an aside, if you have an automated ops tool, do what I did. We put some rules in place that trapped the "nearly out of space DSNP" message, extracted the job name from it (it was nearly always a batch job that was causing the problems) and then issue an ops command to swap the job into a special performance group we had which was not allowed to be swapped in. [...] 21139 101 50_Re: [DB2EUG] Unexpected RI Result : is this normal12_Jason Hughes20_Jason.Hughes@TRW.COM31_Tue, 26 Mar 2002 15:17:42 -0500410_US-ASCII Philip, I had just asked the same question of an Oracle database I'm currently working with. At first I blamed Oracle but that's just the DB2 in me ;-)

I later found out this is one of the "working as designed" features of a relational database. The following blurb of text comes from an Oracle Concepts book but since it only talks about SQL 92 standards, it should apply to DB2 as well. [...] 21241 46 54_Re: TCP/IP failure while calling DB2 stored procedure.15_Camitta, Steven30_steven.camitta@INGRAMMICRO.COM31_Tue, 26 Mar 2002 12:18:00 -0800457_iso-8859-1 From Assembler Callable Services for OpenEdition MVS 1121 is ECONNRESET (Connection reset by peer)

Sorry to say that it's a pretty generic error. Steve

-----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Tuesday, March 26, 2002 9:58 AM To: DB2-L@RYCI.COM Subject: TCP/IP failure while calling DB2 stored procedure.



We are getting following error message some time in DB2MSTR log [...] 21288 19 43_DB2 Version 7 Utilities versa OEM Utilities14_Victor Hedrick26_victor.hedrick@ABNAMRO.COM31_Tue, 26 Mar 2002 14:00:08 -0600483_- My company is debating not to purchase the IBM Utilities site for DB2 version 7 and is thinking about purchasing DB2 utilities from vendor like BMC or CA. I am wondering how many other IBM customers have decided not to purchase the utilities from IBM and go with an OEM vendor only? I also would like to know what has been their experiences using OEM utilities with DB2 version 7? (Including using image copy, recovery and reorg against the DB2 catalog and directory objects) [...] 21308 35 19_Re: BMC Change Mgr.11_Tony Howard15_thoward@BMC.COM31_Tue, 26 Mar 2002 14:05:27 -0600379_- The BMC Software CHANGE MANAGER for DB2 product (mainframe) will be publishing some performance benchmarks for the Compare and the Baseline components later this year for our version 7.2.01 release. We will also make a couple of performance improvements. Until then, I can give you a couple of hints to dramatically reduce the amount of time it takes to run a comparison. [...] 21344 53 54_Re: TCP/IP failure while calling DB2 stored procedure.11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 26 Mar 2002 12:49:43 -0800423_iso-8859-1 Phil, the return code is from UNIX, here's a url to the manual. http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/BPXB100/A.0

I get weird TCP/IP message all the time. They don't seem to effect performance (so far) and I haven't gotten any user complaints. So until DB2 comes crashing down around my ears or users show up at my door with pitch forks and torches I'm going to ignore them. :-) [...] 21398 154 28_Re: FW: Problem with UDB FP613_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Wed, 27 Mar 2002 08:10:30 +1100405_iso-8859-1 Thanks Glenn and Phil, hope you have/had a good sleep. It's just 8.00 am here and another day of fulfilling frustration begins :)

I think my problem relates to the fact that I have several user id's. Because of the way things are run here (don't ask) I log on to the network with one id (XXXXST), administer UDB with another (db2admin) but on the mainframe I need a third (SYACXX). [...] 21553 152 64_Re: DB2 connect limitation while accessing DB2 STORED PROCEDURES14_anand savulgay20_aksavulgay@YAHOO.COM31_Tue, 26 Mar 2002 13:13:14 -0800269_us-ascii Hi, We can in different ways. We can can group the parameters into less than 100 and make it work. However, I just want to know what is the problem. I don't think there is a limitation. At this point, I don't have any debugging tools to find the problem. [...] 21706 40 47_Re: DB2 Version 7 Utilities versa OEM Utilities16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Tue, 26 Mar 2002 16:36:01 -0500646_iso-8859-1 We have the IBM utils and the CA ones. We use the IBM ones much more than the CA ones.



regards, eric pearson

-----Original Message----- From: Victor Hedrick [mailto:victor.hedrick@ABNAMRO.COM] Sent: Tuesday, March 26, 2002 3:00 PM To: DB2-L@RYCI.COM Subject: DB2 Version 7 Utilities versa OEM Utilities



My company is debating not to purchase the IBM Utilities site for DB2 version 7 and is thinking about purchasing DB2 utilities from vendor like BMC or CA. I am wondering how many other IBM customers have decided not to purchase the utilities from IBM and go with an OEM vendor only? I also [...] 21747 26 23_DATE FORMAT in SYSPUNCH23_GORRELLA, SRINI (SBCSI)14_sg7974@SBC.COM31_Tue, 26 Mar 2002 15:48:12 -0600389_ISO-8859-1 Hi All,

Can I load 6 char input field (for e.g. mmddyy) into a Table of column DATE data type ??? Is there any FORMAT clause I can use in my punch card to do this .

I did this kind of FORMATs in Teradata but never in Db2 .I know the best option will be extract the data in Db2 desired format .I want to know is there any FORMAT clause for columns in SYSPUNCH [...] 21774 184 28_Re: FW: Problem with UDB FP613_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Tue, 26 Mar 2002 15:52:39 -0600391_iso-8859-1 I would not expect any local binding to happen, assuming you are connecting to a m/f datasource.

If you want to persevere try this ...

You can start a CLP (command line processor) session and execute the BIND command manually.

You may get more messages and have more control. Last time i used this command was way back, here is a cut & paste/edited job. [...] 21959 47 47_Re: DB2 Version 7 Utilities versa OEM Utilities22_Naidoo, Rama [IBM GSA]28_Rama.Naidoo@TEAM.TELSTRA.COM31_Wed, 27 Mar 2002 08:59:42 +1100390_iso-8859-1 Victor,

We are trying the reverse. We have traditionally BMC utilities and we are planning to replace the BMC utilities with IBM V7 utilities. With LISTDEF, TEMPLATE and many other enhancements in IBM utilities, these utilities are comparable with third party utilities. I rather buy parts from the FORD supplier rather than parts made for FORD from other suppliers. [...] 22007 104 33_Re: Timestamps in the DBRM module14_Linda Hagedorn30_Linda.Hagedorn@EMBARCADERO.COM31_Tue, 26 Mar 2002 14:07:23 -0800484_iso-8859-1 The ASM map of the DBRM can be found in ADSNMACS(DSNXDBRM), along with much documentation about the DBRM structure and layout.

-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Tuesday, March 26, 2002 9:26 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module



I apologize, Manish. I confused you by giving you the wrong con-token. I forgot that I was looking at the email in a proportional font. [...] 22112 77 9_Re: SPACE14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 27 Mar 2002 08:04:16 +1000557_ISO-8859-1 The 15 extent limit for sequential datasets is per volume.

James Campbell

On 26 Mar 2002 at 12:58, Moore, Tony wrote:

> Yo Todd, > Some of the Guru's may have something closer, but when calculating space for > my DB2 tables, for use with 3390 Mod 3 devices, I figure at 49,152 bytes per > track. Assuming you're using (or emulating) mod 3's... > > 52,000,000,000 / 49,152 = 1,057,943 tracks > 1,057,943 / 15 = 70,530 cylinders > 70,530 / 3300 = 21.37 3390-3 devices > > If you got Mod 9's, you could probably get it all [...] 22190 75 40_Re: Trigger to invoke procedure DSNUTILS14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 27 Mar 2002 08:04:18 +1000665_US-ASCII Grace

What happens if you put apostrophes around REORG TABLESPACE DSN8D71A.GRACETS3 SHRLEVEL REFERENCE SORTKEYS?

Unless RGRACE, NO etc are columns in the trigger table, you'll probably require more apostrophes.

James Campbell



On 26 Mar 2002 at 10:06, Grace Chen wrote:

> Hi List, > I am trying to create a trigger to invoke stored procedure DSNUTILS and got > this error: > SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL > STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -199, SQLSTATE > 42601, AND MESSAGE TOKENS TABLESPACE,) , > SQLSTATE = 56059 SQLSTATE RETURN CODE > [...] 22266 65 28_FW: FW: Problem with UDB FP613_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Wed, 27 Mar 2002 09:12:39 +1100441_iso-8859-1 Belay all that!

Now that I have searched the archives for the right string I found the problem.

I did have SYSADM and all that but did not have CREATE in the NULLID collection.

Problem fixed.

Thanks everyone.

Steve T

-----Original Message----- From: TENNANT Steve (EDS) Sent: Wednesday, 27 March 2002 8:11 AM To: 'DB2 Data Base Discussion List' Subject: RE: FW: Problem with UDB FP6 [...] 22332 181 28_Re: FW: Problem with UDB FP619_Michael Piontkowski34_michael_piontkowski@COMPUSERVE.COM31_Tue, 26 Mar 2002 17:10:22 -0500550_iso-8859-1 Steve -

Apologies for jumping in the middle here. If you're talking about binding DB2 Connect packages, then the DB2 Connect packages are created on the DB2 for OS/390 subsystem in the default collection name NULLID and you'll need BINDADD privledge in DB2 for OS/390.





Mike Piontkowski

-----Original Message----- From: owner-db2-l@RYCI.COM [mailto:owner-db2-l@RYCI.COM]On Behalf Of Steve Tennant Sent: Tuesday, March 26, 2002 16:11 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] FW: Problem with UDB FP6 [...] 22514 62 29_Trigger call stored procedure10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Tue, 26 Mar 2002 15:44:07 -0700244_iso-8859-1 Hi list, I have heard that :

The stored procedure that called by trigger can only have IN parm. If that's the case, how can I do if I have a stored procedure that has OUT parm defined.



Grace Chen



22577 105 40_Re: Trigger to invoke procedure DSNUTILS11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 26 Mar 2002 16:56:37 -0600486_- Grace,

In addition to what James has suggested (enclosing each char parm in quotes), I am not sure this can be done/would be useful. Once you get past the syntax, the question is what about RC? I don't think a trigger can accept an output parm. If you get past this and you do get a return code, the trigger does not listen, it just talks (like some people I know). Bottom line: I don't think you cancall this SP from a trigger. If anyone has done it, I am staying tuned. [...] 22683 102 32_Re: FW: FW: Problem with UDB FP612_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 27 Mar 2002 11:03:38 +1200444_us-ascii Eh? Sysadm isn't enough to add packages to a collection? I find that hard to believe. Still, DB2 authorisations being as complicated (or is that flexible?) as they are maybe it's not so hard to believe after all.



Raymond







Steve Tennant cc: Sent by: DB2 Data Subject: FW: FW: Problem with UDB FP6 Base Discussion List [...] 22786 67 47_Re: DB2 Version 7 Utilities versa OEM Utilities9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 26 Mar 2002 16:18:41 -0700405_us-ascii Depending on what and where you currently use your BMC utilities that approach might work, however I typically see the opposite. Meaning the BMC utilities still provide more features and functions that the shop relies on then provided by V7 equivalent, especially in situations where BMC utilities are interacting with other BMC products (like Change Manager, DASD Manger, LogMaster, etc.). [...] 22854 52 33_Re: Timestamps in the DBRM module12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 27 Mar 2002 11:08:25 +1200450_us-ascii Nice REXX, Isaac. Think I'll keep a copy for future reference. You might want to post it on the DB2-L-Documents thingey as its so useful.

Cheers,



Raymond





Isaac Yassin cc: Sent by: DB2 Data Subject: Re: Timestamps in the DBRM module Base Discussion List



27/03/02 04:34 Please respond to DB2 Data Base Discussion List [...] 22907 97 40_Re: Trigger to invoke procedure DSNUTILS10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Tue, 26 Mar 2002 16:10:11 -0700294_iso-8859-1 James, You are right. I need to put apostrophes around all parms that defined as CHAR or VARCHAR. Now I got an error -469 which means I have an OUT parm which need a host variable be supplied on the SQL CALl statement. How can I supply a host variable to my SQL call statement? [...] 23005 54 24_Re: TCP/IP setup for DB212_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 27 Mar 2002 11:12:29 +1200400_us-ascii Hi Leila,

Thanks for the note. Yes, it came down to a missing DOMAIN value. Our Network Sysprogs sorted it out and it now works fine. I can use TCP/IP to DDF between this subsystem and the others now so, yes, it works. My problem now is getting SQL*Net and the OTG (both on OS/390) to work, but that's another story and one I won't drag DB2-L into just yet - or ever, hopefully. [...] 23060 37 25_Re: 3390 Mod 9 Experience11_Doug Fuerst21_doug@BKASSOCIATES.NET31_Tue, 26 Mar 2002 18:51:10 -0500638_us-ascii 3390-9 geometry is fine. On an array, you do not get the performance degradation that you get on a real mod9 Great for those big requirements. Our res's moved for OS390.

At 10:43 AM 3/26/2002 -0800, you wrote: >Please provide feedback on 3390-9 experience good / bad. We are currently >3390-3 only. As we encounter large objects, we are wondering if it would be >easier to size the allocations on larger volumes. DASD is currently EMC >5xxx series soon to be 8830. Storage is SMS controlled. > >Thanks, >Steve > >================================================ >To change your subscription options or to cancel your [...] 23098 48 23_SQL Performance Problem11_Pawan kumar24_pawan_kalyan75@YAHOO.COM31_Tue, 26 Mar 2002 17:10:52 -0600407_- The query is based on a date range. When the date is "hardcoded" in the query it runs efficiently. I have an issue to change the query to let the system calculate the date using one of our database table. When I use the appropriate date range logic the query runs for hours sorting without ever retrieving rows of data. This is a year end job so the query would be retrieving a year' worth of data . [...] 23147 51 46_Re: sec: unclassified >> Are extents harmless?12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 27 Mar 2002 11:52:19 +1200460_us-ascii Nice document, Phil. Thanks for posting the link. Shame about the loud orange background for a lot of the pages. Still, as you say, they do indeed rock! Just confirmed we've got F20s coming online. Goodie, can't wait...



Raymond





Phil Gunning cc: Sent by: DB2 Data Subject: Re: sec: unclassified >> Are extents harmless? Base Discussion List [...] 23199 105 32_Re: FW: FW: Problem with UDB FP612_Phil Gunning17_db2jock@YAHOO.COM31_Tue, 26 Mar 2002 16:03:32 -0800530_us-ascii Cool. --- Steve Tennant wrote: > Belay all that! > > Now that I have searched the archives for the right > string I found the > problem. > > I did have SYSADM and all that but did not have > CREATE in the NULLID > collection. > > Problem fixed. > > Thanks everyone. > > Steve T > > -----Original Message----- > From: TENNANT Steve (EDS) > Sent: Wednesday, 27 March 2002 8:11 AM > To: 'DB2 Data Base Discussion List' > Subject: RE: FW: Problem with UDB FP6 > > > Thanks Glenn and Phil, [...] 23305 98 27_Re: SQL Performance Problem31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Wed, 27 Mar 2002 00:25:13 +0000464_iso-8859-1 Pawan,

It looks the problem lies in CHAR function. In subquery of ANY i.e.

(SELECT CHAR(SALES_YR_MTH - 11 MONTHS) FROM MOON.MISTCTRL_MTH)

and

(SELECT CHAR(AS_OF_DT) FROM MOON.MISTCTRL_MTH )



Use DATE instead of CHAR.

Hope this helps ! If it doesn't then EXPLAIN and indexes are needed.

With Best Regards, Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration on OS/390. [...] 23404 118 47_Re: DB2 Version 7 Utilities versa OEM Utilities14_Edward P. Long19_RDHM99A@PRODIGY.NET31_Tue, 26 Mar 2002 19:27:24 -0500464_- We have only the IBM utilities, are on V7, and exploit every bell and whistle imaginable- like blobs, triggers, UDF's, Stored Procedures, Unions in views,cross system loading, outer joins and other stuff. To date, my experience with the new model IBM utilities that are actually active (CSL only really went live in August 2001) is that they have as much trouble with the bells and whistle's of V7 as BMC and CA have had. Blob support is particularly weak. [...] 23523 100 46_Re: sec: unclassified >> Are extents harmless?12_Phil Gunning17_db2jock@YAHOO.COM31_Tue, 26 Mar 2002 16:37:58 -0800567_us-ascii Prepare to ROCK! Sit, back, relax a bit, until that wild bad SQL comes along that you have to take a look at:). Cheers, Phil





--- Raymond Bell wrote: > Nice document, Phil. Thanks for posting the link. > Shame about the loud > orange background for a lot of the pages. Still, as > you say, they do > indeed rock! Just confirmed we've got F20s coming > online. Goodie, can't > wait... > > > Raymond > > > > Phil Gunning > DB2-L@RYCI.COM > UGHDB2.COM> cc: > Sent by: DB2 Data > Subject: [...] 23624 16 33_Locksize ANY versus Locksize PAGE0_20_bjnigh@HOUSEHOLD.COM31_Tue, 26 Mar 2002 16:39:04 -0800364_us-ascii I have a high-volume insert/update/delete/select CICS/DB2 OLTP application which currently has the tablespace defined as LOCKSIZE ANY. After attending a performance session earlier this month in SHARE, it was recommended that the LOCKSIZE be set to PAGE. Does anyone have any experience on when it is best to use ANY and when it is best to use PAGE? [...] 23641 93 54_Re: TCP/IP failure while calling DB2 stored procedure.12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 26 Mar 2002 17:05:32 -0800628_us-ascii Hi Guys- I too got a lot of TCP error messages even though we weren't using stored procedures. I tracked some of them down to people rebooting their PCs while a trans was running, and DB2 couldn't return the results. If you ever look at the TCP error log, you'd probably go crazy tracking everthing down. Besides, you can smell the burning torches from quite a ways off...gives you a nice head start... Tim --- Cliff Boley wrote: > Phil, > the return code is from UNIX, here's a url to the > manual. > http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/BPXB100/A.0 > > I [...] 23735 77 24_Re: TCP/IP setup for DB212_leila rahimi21_lshosaini@HOTMAIL.COM31_Wed, 27 Mar 2002 04:14:00 +0000597_- Hi Raymond,can i ask how Sysprogs set DOMAIN. thanks. leila.

>From: Raymond Bell >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: TCP/IP setup for DB2 >Date: Wed, 27 Mar 2002 11:12:29 +1200 > >Hi Leila, > >Thanks for the note. Yes, it came down to a missing DOMAIN value. Our >Network Sysprogs sorted it out and it now works fine. I can use TCP/IP to >DDF between this subsystem and the others now so, yes, it works. My >problem now is getting SQL*Net and the OTG (both on OS/390) to work, but >that's another story and [...] 23813 25 75_What does Platinum PDA statistics really do? How about Runstat? Please Read11_Linda Ayeni22_lindaayeni@HOTMAIL.COM31_Tue, 26 Mar 2002 23:22:54 -0700392_- Hi All, Currently we execute CA/Platinum PDA statistics periodically along with regular DB2 Runstat utility. For the life of me, I cannot explain to you why I have to do both. How really really important to the health of my DB2 objects and access path selection is this PDA stats business when I have and reularly run Runstat? What's the differenec? I am so curious? Thanks everybody! [...] 23839 71 80_Re: What does Platinum PDA statistics really do? How about Runsta t? Please Read14_Basiaco, Lucio20_Lucio.Basiaco@CA.COM31_Wed, 27 Mar 2002 18:04:16 +1100367_iso-8859-1 Hi Linda,

PDA relies on the statistics that it gathers in order to carry out it's other functions which include responding to situations that require a utility to be run to correct a problem.

PDA stats are specially collected for the above purpose. RUNSTATS are collected for the DB2 optimiser.

Here is a snip from the User Guide [...] 23911 16 53_Debra Jordan/Riverwoods/BT/DFSI is out of the office.15_Debra D. Jordan33_debrajordan@DISCOVERFINANCIAL.COM31_Wed, 27 Mar 2002 01:03:28 -0600422_us-ascii I will be out of the office starting 03/27/2002 and will not return until 03/29/2002.

For data management assistance, please contact my backup, Dona Bell, at x2822.

================================================ 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. 23928 15 52_Jeff A Miller/Pr/Consumers/CMS is out of the office.13_Jeff A Miller25_jeff_miller@CMSENERGY.COM31_Wed, 27 Mar 2002 02:51:50 -0500387_us-ascii I will be out of the office starting 03/26/2002 and will not return until 03/29/2002.

I will return your message when I get back.

================================================ 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. 23944 21 26_Db2 Family Cross Loader???11_Dimoka Popy24_dimokap@INTERAMERICAN.GR31_Wed, 27 Mar 2002 11:01:46 +0200342_- Hello List, anybody heard of the "DB2 Family Cross Loader" ?I think it comes with Db2 V7 but i don't know any other details. So,since i am going to migrate a database from NT to OS/390 ,i wonder if that loader could help me make the process automatic,instead of creating the schema on Os/390 and then import the exported from NT data. [...] 23966 198 32_Re: FW: FW: Problem with UDB FP60_18_mebert@AMADEUS.NET31_Wed, 27 Mar 2002 10:04:44 +0100369_us-ascii Reading the DB2 Command ref, being SYSADM does imply BINDADD and CREATE IN * - so it IS enough (reading, and musing, all about BIND authorisations does indeed fill a rainy afternoon). It gets confusing when it's not the Primary AuthID that has SYSADM...

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 24165 75 33_Re: Timestamps in the DBRM module12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 27 Mar 2002 11:29:44 +0200468_ISO-8859-1 Hi Raymond,

You're right. I'll send it over there ( as I've already done with other REXXes in the past)

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Raymond Bell" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, March 27, 2002 1:08 AM Subject: Re: Timestamps in the DBRM module [...] 24241 142 40_Re: Trigger to invoke procedure DSNUTILS14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 10:12:39 -0000497_iso-8859-1 Grace,

Not tried this, so it's only a theory but...

I also think you cannot have a returned parameter when a trigger calls a stored procedure so why not write your own "wrapper" stored procedure that accepts minimal parameters (such as database and tablespace name) and calls DSNUTILS itself with the RIGHT set of parameters. Then you can check the return code in YOUR stored procedure and then decide how to signal the failure (if there is one) back to your trigger [...] 24384 136 37_Re: Locksize ANY versus Locksize PAGE14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 10:12:44 -0000315_iso-8859-1 Maybe I'm a dinosaur, but I can't think of ANY reason to use LOCKSIZE ANY - EVER!

If you can accept an escalation to a tablespace (or table) lock, why didn't you do a lock table in the first place. If you can't afford the escalation (which in an OLTP environment you can't) then why risk it. [...] 24521 181 80_Re: What does Platinum PDA statistics really do? How abou t Runstat? Please Read14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 10:12:53 -0000394_iso-8859-1 Linda,

It's very simple

YOU (as a DBA) need to know about the health of your DB2 objects. However, does DB2 itself always need to know that your tables/indexes are getting disorganised?

PDA allows you to collect stats that only YOU can see (of course, you can also allow PDA to update the DB2 catalog, removing the need to run RUNSTATS at all, by the way). [...] 24703 33 34_Answer: Db2 Family Cross Loader???12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 27 Mar 2002 11:10:31 +0100351_iso-8859-1 Kalimera, Popy.

Last week I've found some information about the cross Loader under http://www.redbooks.ibm.com/redbooks/SG246513.html :

Cross Loader came up with DB2 UDB for z/OS V7 and it can be used to load DB2 for z/OS tables from DB2 UDB tables on distributed platforms as NT and UNIX or another DB2 for z/OS system. [...] 24737 163 30_Re: Db2 Family Cross Loader???14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 10:23:54 -0000499_iso-8859-1 Hi Popy,

Hope things are will in Athena?

The IBM LOAD utility in Version 7 has a feature that allows you to load data into a DB2 table from a non-OS/390 source.

What happens is, you can declare a cursor(!) within the DSNUTILB input parameters (you can now do EXEC SQL .... END EXEC) and then use the cursor name as the input to your LOAD (instead of an INDDN you have an INCURSOR). LOAD will then FETCH the rows from the cursor and LOAD them into your DB2 table [...] 24901 192 80_Re: What does Platinum PDA statistics really do? How about Runsta t? Please Read16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 27 Mar 2002 10:25:19 -0000604_iso-8859-1 Linda - to extent Lucio's comments a bit.

As he mentioned - PDA Statistics collection serves a couple of purposes:

1) To make PDA available to generate correct jcl-allocations for work datasets 2) To make you able to generate the correct PRIQTY to have your data in one extent. 3) Update the DB2 catalog instead ONLY when you need to without risking BAD OPTIMIZER statistics and changed access path as a result. You can schedule the CATALOG to ONLY be updated IF certain values have changed. 4) PDA Statistics also serve as a Reporting Tool. You can generate trend analysis [...] 25094 201 32_Re: FW: FW: Problem with UDB FP641_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 27 Mar 2002 11:32:48 +0100341_iso-8859-1 It get's confusing indeed; in our shop Primary AuthIds do not have any privileges, all privileges are granted to RACF-groups; so one cannot log in as Prim. Auth_Id (the bind will fail), and one cannot log in as Secondary AuthId (having no password); so in the end we had to grant CREATE IN ... NULLID to our Primary AuthIds. [...] 25296 109 33_Re: Timestamps in the DBRM module31_Ramakrishnan Chandraseharapuram41_Ramakrishnan_Chandraseharapuram@USSWI.COM31_Wed, 27 Mar 2002 16:07:14 +0530586_iso-8859-1 hi guys.... got a problem here.....a nagging one too.... i've got some Stored Procedures(SPs) registered in DB2 (version 7.0).... the "KEEP DARI" option is set true.

Now if i modify an SP and register it, the call to the SP fails and i get an error code -818 (Time Stamp Error).... one temporary solution i tried was to drop the procedure, the corresponding package and then re-register the SP. this works most of the times but say about one out of 5 times this fails to work....... i have even tried to stop and restart DB2 .......still the problem persists.... [...] 25406 53 49_Problem with DB2 Connect and underscore character19_Calleja Vilar, Juan22_jcallejav@IBERDROLA.ES31_Wed, 27 Mar 2002 12:44:53 +0100406_iso-8859-1 Hi, list.

We are involved in the following scenario:

- DB2 V6.1 for OS/390 - DB2 Connect V6.1 - Microsoft Access

When we try to bind a table from Microsoft Access whose name includes the underscore character '_' the operation fails. We have analyzed a trace and it seems that Microsoft Access (or maybe DB2 Connect, we don't know) is generating the following sentence: [...] 25460 72 53_Re: Problem with DB2 Connect and underscore character11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 27 Mar 2002 07:26:12 -0500436_iso-8859-1 Add UNDERSCORE=0 to your db2cli.ini file for each subsystem. That should help.

-----Original Message----- From: Calleja Vilar, Juan [mailto:jcallejav@IBERDROLA.ES] Sent: March 27, 2002 6:45 AM To: DB2-L@RYCI.COM Subject: Problem with DB2 Connect and underscore character



Hi, list.

We are involved in the following scenario:

- DB2 V6.1 for OS/390 - DB2 Connect V6.1 - Microsoft Access [...] 25533 28 37_Re: Locksize ANY versus Locksize PAGE7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Wed, 27 Mar 2002 07:39:53 -0500558_ISO-8859-1 In basic DB2 fashion I would have to say it depends, but I have to go along with Phil on this one. To me it depends on where your system priorities are. If you use ANY you are basically saying that I can afford the escalation and my priority is leaning toward batch applications without proper commit frequency. If a shop has established good standards and code reviews are done to ensure that proper commit frequencies are used then this becomes a non issue. However, it is a crutch for the shop that does not understand DB2 and thinks that [...] 25562 23 27_Re: SQL Performance Problem15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 27 Mar 2002 06:14:02 -0600550_- The predicate

AND BLL.INVOICE_DT >= ANY (SELECT CHAR(SALES_YR_MTH - 11 MONTHS) FROM MOON.MISTCTRL_MTH)

means INVOICE_DT must be greater than any date in the MOON.MISTRCTRL_MTH, especially greater than the minimun. The same holds accordingly to the other predicate. So why don't you join your SPACE.MIST_ALL with one table expression like SELECT DATE(MIN(SALES_YR_MTH - 11 MONTHS)) AS BEGIN_DATE, DATE(MAX(AS_OF_DT) AS END_DATE and BLL.INVOICE_BT BETWEEN BEGIN_DATE AND END_DATE. I think that would give you the best access path. [...] 25586 77 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Wed, 27 Mar 2002 07:53:03 -0500443_iso-8859-1 Hi,

How can I get of the existing DB2-L-Documents ?

My Company is very frugal with its spending... We don't have CBT stuff. I have asked for the authorization (6 months ago) but have not received a reply.

thanks

Manish

-----Original Message----- From: Raymond Bell [mailto:rbell@NZ1.IBM.COM] Sent: Tuesday, March 26, 2002 6:08 PM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module [...] 25664 82 46_Re: sec: unclassified >> Are extents harmless?13_Wolff, Martin31_Martin.Wolff@GLOBALCROSSING.COM31_Wed, 27 Mar 2002 08:19:58 -0500565_iso-8859-1 Hi Fritz and everyone else. We have a similar issue with secondary allocations. Let me set the scene:- We have a table which has multiple partitions but at any one time, a relative few have data in them. The partitions that do have data have 30+ million row in them each. To save space, we allocate a small primary for each partition and larger secondary with the hope of only filling 20 secondary extents. Data is put into the partitions using the load utility only. Each partition is defined to a stogroup that has multiple volumes in it. This is [...] 25747 105 33_Re: Timestamps in the DBRM module41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 27 Mar 2002 14:29:01 +0100424_iso-8859-1 Manish,

go to the bottom of this e-mail and follow the url; then go on from there, it is all very plain.

Hth Ruediger Kurtz

-----Ursprüngliche Nachricht----- Von: Manish Kothari [mailto:mkothari@DATAMIRROR.COM] Gesendet: Mittwoch, 27. März 2002 13:53 An: DB2-L@RYCI.COM Betreff: Re: Timestamps in the DBRM module



Hi,

How can I get of the existing DB2-L-Documents ? [...] 25853 22 34_Waiting for notification from VTAM15_Claudio Sanchez20_clsanchez@BNL.COM.AR31_Wed, 27 Mar 2002 10:50:46 -0300400_us-ascii Dear listers I am using a connection DRDA by lu6.2 between DB2 V5/OS390 and UDB V7 running under Windows 2000. I see that to run a query from UDB that returns about 400.000 rows, the elapsed time is of 2 hours aprox, while the time in db2 is of 1 minute ???? The thread is suspended in DB2 and is waiting for notification from VTAM the greater time. Someone knows the cause of this ??? [...] 25876 13 21_Advantages of DB V7.111_Pawan kumar24_pawan_kalyan75@YAHOO.COM31_Wed, 27 Mar 2002 07:28:38 -0600493_- My shop presently have DB2 on V6.1 even though we don't have much problems right now with V6.1, they want to convert to V7.1 in couple of months frame time. Can anyone tell what are the advantages in converting to V7.1? Any feedbacks are welcome!!!!

================================================ 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. 25890 63 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Wed, 27 Mar 2002 09:10:46 -0500571_iso-8859-1 Thanks..

-----Original Message----- From: "Kurtz, Rüdiger" [mailto:Ruediger.Kurtz@HUK-COBURG.DE] Sent: Wednesday, March 27, 2002 8:29 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module



Manish,

go to the bottom of this e-mail and follow the url; then go on from there, it is all very plain.

Hth Ruediger Kurtz

-----Ursprüngliche Nachricht----- Von: Manish Kothari [mailto:mkothari@DATAMIRROR.COM] Gesendet: Mittwoch, 27. März 2002 13:53 An: DB2-L@RYCI.COM Betreff: Re: Timestamps in the DBRM module [...] 25954 43 25_Re: Advantages of DB V7.118_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 27 Mar 2002 09:28:02 -0500619_- Which environment, OS/390, Unix, NT?

Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2



-----Original Message----- From: Pawan kumar [mailto:pawan_kalyan75@YAHOO.COM] Sent: Wednesday, March 27, 2002 8:29 AM To: DB2-L@RYCI.COM Subject: Advantages of DB V7.1



My shop presently have DB2 on V6.1 even though we don't have much problems right now with V6.1, they want to convert to V7.1 in couple of months frame time. Can anyone tell what are the advantages in converting to V7.1? Any feedbacks are welcome!!!! [...] 25998 119 31_Re: Clarification on SAVEPOINTs14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 14:31:02 -0000422_iso-8859-1 I'm struggling to find this book - do you have a pub number?

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



-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: 21 March 2002 16:53 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Clarification on SAVEPOINTs [...] 26118 40 33_Re: Timestamps in the DBRM module12_McKown, John22_JMckown@HEALTHAXIS.COM31_Wed, 27 Mar 2002 08:32:59 -0600136_iso-8859-1 Manish, If you want something from the CBT, then simply go to http://www.cbttape.org and download it. Free of charge!

26159 53 25_Re: 3390 Mod 9 Experience12_McKown, John22_JMckown@HEALTHAXIS.COM31_Wed, 27 Mar 2002 08:33:15 -0600282_iso-8859-1 The only "problem" with using a -9 is that you can only have one I/O outstanding to it at a time. If the entire -9 is dedicated to a single data set, this may not be a problem. If you have multiple data sets on the volume, then you could see some volume contention. [...] 26213 73 9_Re: SPACE12_McKown, John22_JMckown@HEALTHAXIS.COM31_Wed, 27 Mar 2002 08:33:36 -0600343_iso-8859-1 Todd, Let's see. 52,000,000,000 bytes = 52,000,000K = 52,000 Mb = 52 Gb (OK, it only "about"). Since a 3390-3 disk (the most popular kind) only holds 2.89 Gb of data (and is 3339 cylinders), this means you need about 22 volumes on 3390-3 space! Your friendly neighborhood DASD administrator will be by soon to kill you. [...] 26287 40 33_Re: Secondary allocation question10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 27 Mar 2002 15:37:19 +0100588_us-ascii Hi Carol

As far as I'm concerned we try to be proactive (obvious) but as everyone who REALLY fight at the front space knows shortage happens. We try ti manage this event via DATACLASS using space constraints relief + reduce space as first level. At the second level we used (in a previous company) to define storage groups with more volumes than the online DASDs for that storage group; when a space shortage was detected (messages intercepted via AUTOOPERATOR for instance but there are nice toolz in CBT tape as well) an application put offline a dasd in the spare [...] 26328 14 46_Re: sec: unclassified >> Are extents harmless?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 27 Mar 2002 15:42:28 +0100298_us-ascii Which SMS version do u have ?

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. 26343 12 46_Re: sec: unclassified >> Are extents harmless?10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 27 Mar 2002 15:43:15 +0100280_us-ascii How is DATACLASS definition ?

================================================ 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. 26356 47 33_Re: Trigger call stored procedure11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 27 Mar 2002 09:05:21 -0600467_- Grace - use a "driver" SP that only contains IN parms. This will, in turn, call DSNUTILs and handle the return code. Seems long-winded but I think the only feasible means of doing it.

BTW - you intend to trigger on some insert/update/delete action? Would this not be too excessive? For example, would you really want several reorg kicked off after a bunch of updates? The reorgs could potentially take over the system where no useful work can be done?! [...] 26404 41 34_CREATE FUNCTION question, sort-of.12_McKown, John22_JMckown@HEALTHAXIS.COM31_Wed, 27 Mar 2002 09:11:06 -0600581_iso-8859-1 Well, I don't think this is possible, but I have another relational database that I use (PostgreSQL) which has a DEFINE AGGREGATE ability. This is like a DEFINE FUNCTION, except that the function is called, iteratively, for each value is a set of columns. In DB2, I think this is called a "column function". Like SUM() or AVG(). I don't seem to be able to find something similiar in DB2. In particular, I have implemented a PostgreSQL AGGREGATE function which takes all the select values of a VARCHAR column and concatenates them into a single result. For example: [...] 26446 60 38_Re: Waiting for notification from VTAM12_Phil Gunning17_db2jock@YAHOO.COM31_Wed, 27 Mar 2002 07:21:02 -0800567_us-ascii Claudio, It could be many things. What do you have pacing and rusize set to? Have you eliminated waits built into vtam? What does it show you when you display the PU and independent lu? Any buffer shortages in vtam? Are you retrieving 400 rows or 400,000? Either way it should only take a few minutes max. Are you using DB2 Connect EE or PE? What SNA stack are you using on Windows? Check out the Quick Beginnings for Db2 Connect, SNA settings and compare to what you are using. HTH Phil --- Claudio Sanchez wrote: > Dear listers [...] 26507 65 37_Re: Locksize ANY versus Locksize PAGE12_Susan Lawson22_lawson_susan@YAHOO.COM31_Wed, 27 Mar 2002 07:26:24 -0800304_us-ascii LOCKSIZE ANY is the default. This has to do with where DB2 starts to lock, not escalation. ANY allows DB2 to choose the lock - usually LOCKSIZE PAGE and LOCKMAX SYSTEM.

Of course we all know defaults are not always optimal for our applications, so it is best not leave it up to DB2. [...] 26573 54 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Wed, 27 Mar 2002 10:37:11 -0500384_iso-8859-1 I am glad it is free but I need permission to do it .

-----Original Message----- From: McKown, John [mailto:JMckown@HEALTHAXIS.COM] Sent: Wednesday, March 27, 2002 9:33 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module



Manish, If you want something from the CBT, then simply go to http://www.cbttape.org and download it. Free of charge! 26628 26 25_Re: Advantages of DB V7.115_Satyakant Evani17_evanis6@ATTBI.COM31_Wed, 27 Mar 2002 15:59:10 +0000661_- http://publib.boulder.ibm.com/cgi- bin/bookmgr/BOOKS/dsnwnh11/CCONTENTS gives you the details. Has many improvements both for DBAs as well as app programmers. The SQL improvements are pretty cool. Have been waiting for it for a while. Evani

> My shop presently have DB2 on V6.1 even though we don't have much problems > right now with V6.1, they want to convert to V7.1 in couple of months frame > time. Can anyone tell what are the advantages in converting to V7.1? Any > feedbacks are welcome!!!! > > ================================================ > To change your subscription options or to cancel your subscription visit the > DB2-L webpage [...] 26655 134 47_Re: DB2 Version 7 Utilities versa OEM Utilities12_TOM DUVERNAY21_Tom.Duvernay@USDA.GOV31_Wed, 27 Mar 2002 09:28:00 -0500555_- What has been your experience with UNION in A VIEW. we are implementing V7 soon to take advantage of this. How has your response times compared with the data being all in one table? did you need aditional indexes, etc. we currently have a table with 1.2 billlion rows, partitioned by year, month, organization. this contains 5 years. we are going to create another identical table with only 2003 data in it. do we partition it the same (do we need YEAR since it is all one year, or do we have to match partitioning strategy)? Did your view combine [...] 26790 60 63_Re: CA Platinum release -- Compatible with version 6 and/ or 7?15_Terry Zimmerman35_terry.zimmerman@GENERALCASUALTY.COM31_Wed, 27 Mar 2002 09:36:35 -0600442_- We migrated to DB2 V7 on our OS/390 test system, and we our on P99D version of Platinum.

I have only found one problem in Platinum, after running an online reorg with the fastswitch option on. Which is, we are unable to do a listcat on the 'J' file names.

Is there any additional problems dealing with the fastswitch option that anyone is aware of in Platinum? Is there another time when these 'J' files cannot be found? [...] 26851 112 30_Re: Db2 Family Cross Loader???0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Wed, 27 Mar 2002 10:17:28 -0600429_us-ascii If I needed to retrieve Oracle data, is it a requirement that I also purchase Data Joiner in order for Cross Loader to work?









"Grainger, Phil" To: DB2-L@RYCI.COM Subject: Re: Db2 Family Cross Loader???

Sent by: DB2 Data Base Discussion List



03/27/2002 04:23 AM Please respond to DB2 Data Base Discussion List [...] 26964 23 42_SQL to compare objects from two subsystems4_Anil18_usregion10@AOL.COM31_Wed, 27 Mar 2002 09:48:43 -0600538_- Hi all!

Life was so much simpler when I used DBAXPERT in my previous project.Since I am out of tools (at this site), I am looking forward to get some assistence from the listserv. I want to compare the differences in objects (SG,DB,TS,TB,IX,Syn) between two subsystems. Which means I need a copy of the catalog tables (some of the them) from one of the subsystem. Could someone send me a) the list of system tables that need to be copied b) ddl to copy system tables c) sql to compare old and new objects Thanks in advance. [...] 26988 21 33_Re: DEFINE NO Indexes and Logging12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 10:03:23 -0600474_- We worked a lot with SAP and PeopleSoft on the table space and database strategy for DB2. I took a couple of releases for each to implement. There were major changes in the PeopleTools 8.0 release, with a better table space strategy out of the box and a much better ability to customize and group. For the systems programming PeopleSoft instance, most customers want just a few table spaces. For production, many more table spaces and many databases are much better. [...] 27010 14 17_Re: A Union query12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 10:10:09 -0600461_- V5 will be out of service in 9 months. Is Lloyds TSB planning to run without a support commitment?

Roger Miller, DB2 for z/OS, scared in Silicon Valley http://www-3.ibm.com/software/data/db2/os390/availsum.html

================================================ 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. 27025 101 69_Why not more than one partitioned index on a partitioned tablespace ?17_Joao Alberto Lima22_joaolima@SENADO.GOV.BR31_Wed, 27 Mar 2002 10:16:34 -0600334_- Hi List,

The maintenance of large NPIs, even with the enhancements of version 7, is not a trivial task. We know that DB2 must have one partitioned index (cluster index) on partitioned tablespace. This index may contain, in addition to the partitioned column(s), another column(s), that define, together, an access path. [...] 27127 25 36_IBM VisualAge Cobol in Windows 2000.14_Riju Parambath37_Riju_Parambath@PROVIDENTCOMPANIES.COM31_Wed, 27 Mar 2002 11:19:52 -0500502_us-ascii Hi All,

I apologize for posting a non-DB2 question!!! We are trying to move one of our OS2/COBOL//DB2 UDB (on AIX) application to Windows 2000/Visual age Cobol/DB2 UDB (on AIX) application.

We are finding it difficult to compile Cobol programs in Visual age (basically initial set up issues). Has anybody in the list done this type of conversion before? Could anybody point us in the right direction (websites or any other resources) regarding the visual age initial setup? [...] 27153 90 37_Re: Locksize ANY versus Locksize PAGE13_Rusty Schmidt26_russell.schmidt@LLBEAN.COM31_Wed, 27 Mar 2002 11:30:14 -0500562_ISO-8859-1 Morning Susan,

I build most of my tablespaces with LOCKSIZE ANY and LOCKMAX SYSTEM (currently 10000) . I have batch work that often, but not always, escalates to tablespace, I assumed reducing IRLM workload. A good thing right ? I assumed I was allowing DB2 to work as efficiently as possible... hence ANY. If at execution it can escalate to a tablespace level I assumed all the better, is this thinking flawed ? And since DB2 uses page to start with... what would be the difference ? To date, I have not had to back off a LOCKSIZE of ANY [...] 27244 90 63_Re: CA Platinum release -- Compatible with version 6 and/ or 7?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 27 Mar 2002 11:40:10 -0500563_iso-8859-1 We have had to put a *lot* of maintenance on P99D in order to get various components (FASTLOAD, QUICKCOPY, FASTUNLOAD) to work with the 'J' datasets. The curious thing is that PDA was able to cope with the 'J' datasets at P99D base! We have not yet found a combination of maint which lets all 3 survive the 'J' datasets and at the same time does not break something else. We have heard that things improved with P99F. We are not likely to upgrade our CA version until we receive positive feedback from CA customers (*NOT* from CA $ale$type$) that [...] 27335 49 31_Re: Clarification on SAVEPOINTs8_helenctu28_helenctu@UCLINK.BERKELEY.EDU31_Wed, 27 Mar 2002 08:30:06 -0800708_ISO-8859-1 Phil,

Rebbook SG24-6108-00 ISBN 0738417955.



>===== Original Message From DB2 Data Base Discussion List ===== >I'm struggling to find this book - do you have a pub number? > >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 > > >-----Original Message----- >From: Roger Miller [mailto:millerrl@US.IBM.COM] >Sent: 21 March 2002 16:53 >To: DB2-L@RYCI.COM >Subject: Re: [DB2-L] Clarification on SAVEPOINTs > > >The V6 Technical Update book has a bit more on savepoints. The word >relational is important. Updates outside of the local DB2, such as [...] 27385 51 27_Re: DATE FORMAT in SYSPUNCH64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Wed, 27 Mar 2002 10:57:59 -0600309_ISO-8859-1 If you are running DB2 for OS/390, you cannot load 'mmddyy' data into a DB2 DATE column without defing a LOCAL DATE format, and that has bigger implications that just loading. See the utility guide and reference for allowable data formats (ISO, JIS, USA and EUR). They all require delimiters. [...] 27437 28 46_Re: sec: unclassified >> Are extents harmless?12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 10:36:20 -0600482_- We did put the latest DB2 measurements in the V7 Performance Topics Redbook, SG24-6129 (including some striping), and in the Redpaper V7 Selected Performance Topics (working with FICON channels). The performance can remove many bottlenecks.

For extents, we don't worry about a reasonable number, say 10 to 20 extents, even in benchmarking. The difference can't be measured. We are getting an increasing volume of people who define all data sets to allow multi-volume. [...] 27466 15 25_Re: 3390 Mod 9 Experience12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 10:44:53 -0600515_- With mod 9 definitions, you'll probably want to set up Parallel Access Volumes and Multiple Allegiance to avoid IO hot spots, even if you don't need them on smaller volumes. Access density becomes a concern in some shops, but not others.

Roger Miller, DB2 for z/OS

================================================ 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. 27482 165 47_Re: DB2 Version 7 Utilities versa OEM Utilities14_Edward P. Long19_RDHM99A@PRODIGY.NET31_Wed, 27 Mar 2002 11:56:13 -0500354_- No benchmarks yet to report.

The DBA tool from IBM definitely doesn't like them; we ordered the latest version a while ago; haven't seen it yet.

None of our tables get to your size.

--- Original Message --- From: TOM DUVERNAY To: DB2-L@RYCI.COM Subject: Re: DB2 Version 7 Utilities versa OEM Utilities [...] 27648 113 30_Re: Db2 Family Cross Loader???14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 27 Mar 2002 17:00:05 -0000444_iso-8859-1 As far as I read the documentation, so long as you can create an alias that points to a table "somewhere" and you can then declare a cursor on the alias, it should work fine.

How you create an alias in DB2 of an Oracle table is another question entirely......

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 [...] 27762 149 73_Re: Why not more than one partitioned index on a partitioned tablespace ?31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Wed, 27 Mar 2002 17:06:34 +0000403_iso-8859-1 Looks to me everything is same but we are calling an NPI as an partitioning index. The idea of partitioning index is to point to the correct partitions, offer clustering (which is important to point out to the correct partition in addition to all its advantages).

I don't want to say no but it seems by calling the second index partitioning, we are just going to console ourself. [...] 27912 76 33_Re: DEFINE NO Indexes and Logging0_24_William_O'Black@FFIC.COM31_Wed, 27 Mar 2002 09:17:07 -0800568_us-ascii Roger, I'd be interested in seeing a PowerPoint presentation on PeopleSoft. We are starting an upgrade project for PeopleSoft V8 Financials and V8.4 EPM on OS/390 DB2 V6. So far we've just defined the demo environments. Using the DB2 definitions out of the box, we've got DBD sizes ranging from 500K up to 3 meg. I'm getting ready to define development environments and, for our DB2 objects, I'm trying to find a balance between what PeopleSoft delivers (3 meg DBDs) and what our system programmers would like (50 K DBDs). Any comments would be welcome. [...] 27989 39 40_Re: IBM VisualAge Cobol in Windows 2000.14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 27 Mar 2002 09:40:10 -0800629_iso-8859-1 the newsgroup COMP.LANG.COBOL has experts that should be able to help

there is also a really terrific COBOL FAQ posted regularly at ALT.COBOL with many, many links



Susan A

-----Original Message----- From: Riju Parambath [mailto:Riju_Parambath@PROVIDENTCOMPANIES.COM] Sent: Wednesday, March 27, 2002 8:20 AM To: DB2-L@RYCI.COM Subject: IBM VisualAge Cobol in Windows 2000.



Hi All,

I apologize for posting a non-DB2 question!!! We are trying to move one of our OS2/COBOL//DB2 UDB (on AIX) application to Windows 2000/Visual age Cobol/DB2 UDB (on AIX) application. [...] 28029 67 55_Re: SYSLGRNX; was DATASET OPEN RELATION WITH BUFFERPOOL12_Chris Munson18_cmunson@US.IBM.COM31_Wed, 27 Mar 2002 09:49:49 -0800472_us-ascii Michael,

Sorry for not responding sooner but I only look/respond to the list when I have time and lately I haven't had the time. To answer your questions:

1) The start_rba = stop_rba is not due to down level detection. Unfortunately my understanding is that the individual SYSLGRNX records are there for internal purposes and not really meant for general use, which is why REPORT is provided I guess so I really can't get into details on this. [...] 28097 31 25_Re: Advantages of DB V7.112_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 11:45:50 -0600469_- Here is the page that gets you to the others and more: http://www.ibm.com/software/data/pubs/

DB2 for UNIX & Windows, I'd suggest that the more likely version is V7.2, since that is about the same as V7.1 with fixpak 3 or higher. http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d 2w/en_main

There are several What's New? books with the overview, Release Notes for the fixpaks, and the rest of the library for the detail. [...] 28129 58 38_Re: CREATE FUNCTION question, sort-of.13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Wed, 27 Mar 2002 11:49:20 -0600413_- Hi John I agree that column functions are not supported ( unless my knowledge is out of date ) but I think there is way to do it. Define a table function returning a table of one column. Return null for every call except the final call when you should return the aggregate value. Wrap the function in MAX or MIN and you should have your "column" function. BTW this is just a wild *untested* idea. Mohammad [...] 28188 65 25_Re: Advantages of DB V7.111_Pawan Reddy24_pawan_kalyan75@YAHOO.COM31_Wed, 27 Mar 2002 10:19:35 -0800456_us-ascii

Sorry! It is in OS/390 ENVIRONMENT "Krawetzky, Peter J" wrote: Which environment, OS/390, Unix, NT?

Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2



-----Original Message----- From: Pawan kumar [mailto:pawan_kalyan75@YAHOO.COM] Sent: Wednesday, March 27, 2002 8:29 AM To: DB2-L@RYCI.COM Subject: Advantages of DB V7.1 [...] 28254 17 38_Re: CREATE FUNCTION question, sort-of.12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 11:55:40 -0600422_- The best I can do is a sort-of answer. I don't know this and don't have the time to chase it down. In the DB2 SQL, that seems to match with a user-defined column function. We have built-in functions that include column functions. We have many varieties of user-defined function (See under CREATE FUNCTION in Chapter 6 SQL Statements in your version and platform), but I don't see any user-defined column functions. [...] 28272 15 30_Re: Db2 Family Cross Loader???12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 27 Mar 2002 12:07:49 -0600508_- To get to Oracle with the code we ship from IBM, as I understand it, you'd need DB2 Connect with the Relational Connect option or DataJoiner. There are certainly other options for the connections, speaking DRDA or other protocols.

Roger Miller, DB2 for z/OS

================================================ 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. 28288 116 38_Re: CREATE FUNCTION question, sort-of.13_Terry Purcell23_purcell_terry@YAHOO.COM31_Wed, 27 Mar 2002 10:51:21 -0800572_us-ascii John,

It's 3 weeks since a similar question was raised (See "Need SQL Help" from early March). So I'll just cut and paste the SQL from then.

SELECT C.COLA, C.COLB ,MAX(CASE C.SEQ_NO WHEN 1 THEN C.COLC ELSE NULL END) AS COL1 ,MAX(CASE C.SEQ_NO WHEN 2 THEN C.COLC ELSE NULL END) AS COL2 ,MAX(CASE C.SEQ_NO WHEN 3 THEN C.COLC ELSE NULL END) AS COL3 FROM ( SELECT A.COLA, A.COLB, A.COLC, COUNT(*) AS SEQ_NO FROM TABLE A , TABLE B WHERE B.COLA = A.COLA AND B.COLB = A.COLB AND B.COLC <= A.COLC GROUP BY A.COLA, A.COLB, A.COLC) AS C GROUP BY C.COLA, [...] 28405 91 80_Re: What does Platinum PDA statistics really do? How about Runsta t? Please Read21_zengreg@earthlink.net21_zengreg@EARTHLINK.NET31_Wed, 27 Mar 2002 13:41:53 -0500625_iso-8859-1 (Apologies if someone has already commented on this; I receive the list in Digest form, and this Q appeared late yesterday)

The bottom line is that you don't actually need the RUNSTATS utility if you're using Database Analyzer's PDA stats. (Which is good news, since with DB2v7, RUNSTATS is now separately priced... part of IBM's Operational Utilities bundle). PDA maintains its own stats tables (the RATS% and RAIX% user tables), which can be used for historical information; PDA stats can write both to them AND to the actual DB2 catalog tables with the latest access path information. The PDA stats [...] 28497 41 40_C++ Declaration for CLOB larger than 32K13_Carlisle, Ron33_ron.carlisle@COUNTRYFINANCIAL.COM31_Wed, 27 Mar 2002 13:11:17 -0600505_iso-8859-1 The goal is static SQL select of a 512K CLOB column in a DB2 for OS/390 v6 database from within a C++ program running on WinNT. The precompile on WinNT is giving this error "SQL0314N The host variable 'clob_data' is incorrectly declared". If the size of the clob_data is changed to 32K or below it precompiles fine. How do you declare a C++ host variable larger than 32K to deal with CLOB data? Or is there a better way to select CLOB data from within a C++ program? Sample code is below. [...] 28539 20 36_DBRM timestamp and Loadlib timestamp8_K.Balaji19_K.Balaji@TARGET.COM31_Wed, 27 Mar 2002 14:06:04 -0600364_iso-8859-1 Hi, Just a quick question... I took the DBRM timestamp and trying to find the same timestamp in the loadlib of the source code from which this DBRM was created. But I couldnt find the exact DBRM timestamp in the loadlib... Am I missing something here ? Are we not supposed to find the same timestamp in loadlibb ? Thanks Balaji Ph # (510) 727-3259 [...] 28560 40 40_Re: DBRM timestamp and Loadlib timestamp14_Edward P. Long19_RDHM99A@PRODIGY.NET31_Wed, 27 Mar 2002 15:37:56 -0500626_- Timestamp is stored in load module differently. DBRM = 12345678 LOAD = 56781234

If you have the right value reverse the two words and retry the load module search.

--- Original Message --- From: "K.Balaji" To: DB2-L@RYCI.COM Subject: DBRM timestamp and Loadlib timestamp

>Hi, >Just a quick question... I took the DBRM timestamp and trying to find the >same timestamp in the loadlib of the source code from which this DBRM was >created. But I couldnt find the exact DBRM timestamp in the loadlib... Am I >missing something here ? Are we not supposed to find the same timestamp [...] 28601 54 40_Re: DBRM timestamp and Loadlib timestamp0_19_Tim.Lowe@STPAUL.COM31_Wed, 27 Mar 2002 14:41:41 -0600429_us-ascii Blajaji, Optimizing compilers generally reverse the timestamp. Try looking for the first half of the timestamp (in hex of course).

Thanks, Tim





"K.Balaji" cc: Sent by: DB2 Data Subject: DBRM timestamp and Loadlib timestamp Base Discussion List



03/27/2002 02:06 PM Please respond to DB2 Data Base Discussion List [...] 28656 68 33_Re: Trigger call stored procedure10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Wed, 27 Mar 2002 13:42:01 -0700428_iso-8859-1 Hi Suresh,

Yes, this is also my concern that using trigger to kick off REORG jobs will potentially take over all system resources. We are new to the DB2. How do you kick off REORG job automatically?



Grace

-----Original Message----- From: Suresh Sane [mailto:data_arch@HOTMAIL.COM] Sent: Wednesday, March 27, 2002 8:05 AM To: DB2-L@RYCI.COM Subject: Re: Trigger call stored procedure [...] 28725 65 24_Re: DB2 Connect V7 Binds19_Natalie W. Faulkner27_nfaulknr@GWMAIL.STATE.WV.US31_Wed, 27 Mar 2002 14:22:34 -0600474_- Dave,

Had reason today to bind into a new subsystem.... tried using the owner parameter

db2 => BIND c:\progra~1\sqllib\bnd\@ddcsmvs.lst OWNER xxDB2ADM

and receive the following:

SQL30053N The value for OWNER failed the authorization check at the remote database. SQLSTATE=42506

The DBA attempting the bind is a member of the RACF group specified in the owner parm and has create in collection on NULLID and has bindadd authority... [...] 28791 168 72_Re: Why not more than one partitioned index on a partitionedtablespace ?10_James Kwan19_jamesdb2kwan@CS.COM31_Wed, 27 Mar 2002 16:05:07 -0500427_iso-8859-1 When I first read the message, I was thinking the same way as your message. However I think the original author's intend is for IBM to create another type of index called "secondary cluster index".(I just made it up). With this type of index,total data independent can be achieved since all indexes are depended on their own cluster data only. The disadvantage of course you won't have a full relational table. [...] 28960 44 40_Re: DBRM timestamp and Loadlib timestamp13_Davis, Ronald17_DavisRo@CONED.COM31_Wed, 27 Mar 2002 16:06:53 -0500565_iso-8859-1

use this sql to get both tokens. If you're using plans - just change the sql.

SELECT A.COLLID AS COLLECTION, A.NAME AS PACKAGE, HEX(A.CONTOKEN) AS DBRM_TOKEN, SUBSTR(HEX(A.CONTOKEN),9,8) || SUBSTR(HEX(A.CONTOKEN),1,8) AS LOADLIB_TOKEN, A.VERSION, A.PDSNAME,A.BINDTIME FROM SYSIBM.SYSPACKAGE A WHERE A.COLLID LIKE 'yourstuff%' AND NAME ='yourpackage';

-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Wednesday, March 27, 2002 3:06 PM To: DB2-L@RYCI.COM Subject: DBRM timestamp and Loadlib timestamp [...] 29005 86 24_Re: DB2 Connect V7 Binds10_Dale Smock20_Dale.Smock@BMSUS.COM31_Wed, 27 Mar 2002 16:55:38 -0500471_iso-8859-1 My experience is that the owner for the remote binds must have sysadm authority granted directly, not through a secondary RACF group. Rather than do that, we do these binds specifying the install sysadm userid/group instead of our own userid.

Dale Smock BMG

-----Original Message----- From: Natalie W. Faulkner [mailto:nfaulknr@GWMAIL.STATE.WV.US] Sent: Wednesday, March 27, 2002 3:23 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect V7 Binds [...] 29092 103 33_Re: Trigger call stored procedure11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 27 Mar 2002 16:07:47 -0600504_- Grace - I would NOT use a trigger for this purpose. Not sure what you mean by "automatically". Any one of the traditonal methods will work -

(a) Run IBM or vendor runsats, look at report, set up reorg manually based on clusterraio, nearoffpos, etc... (b) same as (a) but parse report and feed into a reorg generator (c) use a PDA, DASD manager or a similar smart product where you define the criteria and go golfing (get paged if abend) (d) do nothing, wait for user to complain then reorg [...] 29196 130 72_Migration from DB2 NT to OS/390 - Import error on table with LOB columns22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Wed, 27 Mar 2002 16:26:22 -0600512_- Dear Listeners,

DB2 OS/390 V7 DB2 NT V7.2 DB2 Connect V7.2 TCP/IP Communication Server V2.10 MVS V2.10

I'm trying to import a table that contains LOB columns from DB2 NT to OS/390 and I'm getting the following error. I'm running the import command from DB2 CLP: ---------------------------------------------------------------------------- ------------------------------------------------- SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date "20020326", and time "162307". [...] 29327 238 72_Re: Why not more than one partitioned index on a partitionedtablespace ?31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Wed, 27 Mar 2002 22:52:20 +0000396_iso-8859-1 Nicely correlated James, and this is the reason i said, "I won't say that it is impossible" but its a different structure altogether. Multiple clustering index requires multiple data storage and again lots of problems/changes with that.

Thanks for your input !

With Best Regards Sanjeev IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration for OS/390 [...] 29566 36 72_Re: Why not more than one partitioned index on a partitionedtablespace ?0_19_Tim.Lowe@STPAUL.COM31_Wed, 27 Mar 2002 19:02:56 -0600546_us-ascii I believe that the "Requirements Survey" session at the DB2 tech conference each year lists an item called "Global Index Partitioning", that matches what you are discussing. (the capability to partition an NPI) Therefore, I think that it is a fairly well-known requirement, but it has not gotten enough "points" yet to make it a reality. (we all want new features, but this one does not seem to be at the top of people's "needs".) If I remember right, it was considered to be a major enhancement, which would require a lot of time. [...] 29603 16 47_Julie A Lundrigan/GIS/CSC is out of the office.15_Julie Lundrigan16_jlundri2@CSC.COM31_Thu, 28 Mar 2002 01:03:28 +0000389_us-ascii I will be out of the office starting 03/27/2002 and will not return until 04/08/2002.

I will respond to your message when I return.

================================================ 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. 29620 37 72_Re: Why not more than one partitioned index on a partitionedtablespace ?17_Joao Alberto Lima22_joaolima@SENADO.GOV.BR31_Wed, 27 Mar 2002 19:51:13 -0600374_- Dear Sanjeev and James Khan,

Thanks for the responses. I think that we have a misunderstanding. What I am considering is not a "secondary cluster index", but a "secondary partitioned index".

In the example, the index 1 (ZZZ1I001) uses only the first column to define the partitioning. The others two columns of index 1 define, together, an access path. [...] 29658 71 40_Re: DBRM timestamp and Loadlib timestamp8_K.Balaji19_K.Balaji@TARGET.COM31_Wed, 27 Mar 2002 21:01:34 -0600431_iso-8859-1 Tim and Long, Thank you very much for the input. Now I got it. Balaji

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Wednesday, March 27, 2002 12:42 PM To: DB2-L@RYCI.COM Subject: Re: DBRM timestamp and Loadlib timestamp



Blajaji, Optimizing compilers generally reverse the timestamp. Try looking for the first half of the timestamp (in hex of course). [...] 29730 113 37_Re: Locksize ANY versus Locksize PAGE10_John Hardy25_john_hardy@ALLIANZ.COM.AU31_Thu, 28 Mar 2002 00:30:20 -0600337_ISO-8859-1 Rusty,

Phil seems to have already answered your question:

"If you can accept an escalation to a tablespace (or table) lock, why didn't you do a lock table in the first place."

So far as "letting DB2 decide" goes, DB2 does not have sufficient information at its disposal to make the optimal decision. [...] 29844 45 26_Stored Procedure 4 Dummies11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Thu, 28 Mar 2002 14:10:24 +0700436_- Dear all,

I'm a newbie in Stored Procedure and starting to learn. I have this SP:

CREATE PROCEDURE LENDY.Proc1 ( OUT var0 smallint ) RESULT SETS 1 LANGUAGE SQL

P1: BEGIN

SET var0 = (Select count(*) from PRANK.ETL_STATUS);

END P1

I've managed to create the SP into the DB2 database. THe problem is that I don't know how to call the SP within a SQL statement in the Command Center or CLP. [...] 29890 19 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM31_Thu, 28 Mar 2002 02:19:47 -0600139_us-ascii I will be out of the office from 03/28/2002 until 04/01/2002.

I will respond to your message when I return.



29910 34 40_Re: DBRM timestamp and Loadlib timestamp13_Schulz Thomas20_thomas.schulz@SVI.DE31_Thu, 28 Mar 2002 09:25:14 +0100560_us-ascii in the Loadlib you have timestamp1 and timestamp2, both integer Format(pic s9(9) comp) convert the first 4 byte in the DBRM than you will see timestamp1 in the LOAD. Then the second 4 BYTE.

Thomas

"K.Balaji" schrieb: > > Hi, > Just a quick question... I took the DBRM timestamp and trying to find the > same timestamp in the loadlib of the source code from which this DBRM was > created. But I couldnt find the exact DBRM timestamp in the loadlib... Am I > missing something here ? Are we not supposed to find the same timestamp in [...] 29945 32 33_Migration from OS/390 to Midrange15_Peter Nancollis16_pnancoll@CSC.COM31_Thu, 28 Mar 2002 08:54:24 +0000307_us-ascii What ho ! OK please be gentle I have been asked the question so here goes......

If we were to move from OS/390 UDB V7 to DB2 on a midrange box , a) What tools are there to help us ? b) What are the technical issues ? I'll leave the "how long is a piece of string ?" to the philosophers [...] 29978 113 17_Re: A Union query14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 28 Mar 2002 09:54:50 -0000378_iso-8859-1 Putting in V6 even as we speak...

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: 27 March 2002 16:10 To: DB2-L@RYCI.COM Subject: Re: A Union query



V5 will be out of service in 9 months. Is Lloyds TSB planning to run without a support commitment?

Roger Miller, DB2 for z/OS, scared in Silicon Valley [...] 30092 54 46_Top 50 cpu usage rapport for SQL text from DB213_D'hoine Frank19_Frank.Dhoine@NBB.BE31_Thu, 28 Mar 2002 10:58:42 +0100544_iso-8859-1 . I am investigating to have a "top 50-report" that indicates the SQL TEXT of a SQL request of a user to its CPU usage in DB2.

What tools and method would one use for this?

(Which SMF DB2 accounting records...,IFCID..., online monitors like Mainview...)

. Some of the problems I encounter are: Difficulties to identify users: The SQLs are generated and accounting records only contain one plan-name for the whole of the application and using a lot of resources. The next step is to tune the sql generation. [...] 30147 184 30_Re: Stored Procedure 4 Dummies32_Harish Parameswaran,ASDC Chennai24_harishp@MSDC.HCLTECH.COM31_Thu, 28 Mar 2002 15:54:24 +0530566_- I guess what you need is a function, not an SP.

Harish Parameswaran.



> -----Original Message----- > From: Endy Lambey [SMTP:endyl@CENTRIN.NET.ID] > Sent: Thursday, March 28, 2002 12:40 PM > To: DB2-L@RYCI.COM > Subject: Stored Procedure 4 Dummies > > Dear all, > > I'm a newbie in Stored Procedure and starting to learn. > I have this SP: > > CREATE PROCEDURE LENDY.Proc1 ( OUT var0 smallint ) > RESULT SETS 1 > LANGUAGE SQL > > P1: BEGIN > > SET var0 = (Select count(*) from PRANK.ETL_STATUS); > > END P1 > > I've managed to create the [...] 30332 42 20_MVS Job log question15_Duc Tuan NGUYEN30_duc.nguyen@EUROCLEARFRANCE.COM31_Thu, 28 Mar 2002 11:46:31 +0100646_US-ASCII Hi list ,

does anyone know what the column "CONN" means in the Jes2 Job log

11.12.58 JOB03398 ---- THURSDAY, 28 MAR 2002 ---- 11.12.58 JOB03398 IRR010I USERID AST0010 IS ASSIGNED TO THIS JOB. 11.12.58 JOB03398 ICH70001I AST0010 LAST ACCESS AT 10:59:53 ON THURSDAY, MARCH 11.12.58 JOB03398 $HASP373 AST0010S STARTED - INIT 2 - CLASS S - SYS SIC2 11.12.58 JOB03398 IEF403I AST0010S - STARTED - TIME=11.12.58 11.13.32 JOB03398 - --TIMINGS (M 11.13.32 JOB03398 -JOBNAME STEPNAME PROCSTEP RC EXCP CONN TCB SRB 11.13.32 JOB03398 -AST0010S UTIL0001 00 409 772 .03 .00 11.13.52 JOB03398 -AST0010S UTIL0002 00 739 1547 .06 .00 [...] 30375 19 24_Re: MVS Job log question14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 28 Mar 2002 06:00:10 -0600382_iso-8859-1 Duc Tuan,

I believe that it reports DEVICE CONNECT TIME and is produced by the IEFACTRT exit.





Colin

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 30395 57 40_Re: DBRM timestamp and Loadlib timestamp12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 28 Mar 2002 14:11:30 +0200524_ISO-8859-1 Hi, If you use "C" then the contoken in the LOAD is shuffled 78563412 (from 12345678) and if any quarter starts with 0 (zero) it goes out (thrown away ...)

Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Schulz Thomas" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, March 28, 2002 10:25 AM Subject: Re: DBRM timestamp and Loadlib timestamp [...] 30453 158 17_Fw: A Union query11_James Szabo18_jim.szabo@CORE.COM31_Thu, 28 Mar 2002 07:17:41 -0500401_iso-8859-1 RE: A Union query ----- Original Message ----- From: James Szabo To: DB2 Data Base Discussion List Sent: Wednesday, March 27, 2002 10:37 PM Subject: Re: A Union query



Marcus,

Remember, you can simulate the equivalent of a UNION using a FULL OUTER JOIN with no intersection:

SELECT A1, A2, ... FROM A WHERE ... UNION ALL SELECT B1, B2, ... FROM B WHERE ... [...] 30612 15 29_DEFINE NO Indexes and Logging11_Lori Bewley28_lorinda.l.bewley@US.HSBC.COM31_Thu, 28 Mar 2002 06:26:08 -0600310_- Roger, I'd like to see the powerpoint presentation too.

Bill, we are getting ready to install EPM for the first time into a development DB2 V6 subsystem that already houses 10-12 (the number changes weekly) copies of the 8.1.6 Tools/8.3 HR database. Any words of wisdom or gotchas to pass along? [...] 30628 44 24_Re: MVS Job log question13_Schulz Thomas20_thomas.schulz@SVI.DE31_Thu, 28 Mar 2002 14:09:49 +0100656_us-ascii Hit

visit this site

http://www.planetmvs.com/flowerbox/#ibmsactrt

regards Thomas

Duc Tuan NGUYEN schrieb: > > Hi list , > > does anyone know what the column "CONN" means in the Jes2 Job log > > 11.12.58 JOB03398 ---- THURSDAY, 28 MAR 2002 ---- > 11.12.58 JOB03398 IRR010I USERID AST0010 IS ASSIGNED TO THIS JOB. > 11.12.58 JOB03398 ICH70001I AST0010 LAST ACCESS AT 10:59:53 ON THURSDAY, MARCH > 11.12.58 JOB03398 $HASP373 AST0010S STARTED - INIT 2 - CLASS S - SYS SIC2 > 11.12.58 JOB03398 IEF403I AST0010S - STARTED - TIME=11.12.58 > 11.13.32 JOB03398 - --TIMINGS (M > 11.13.32 JOB03398 -JOBNAME STEPNAME PROCSTEP [...] 30673 62 38_Re: Waiting for notification from VTAM12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 28 Mar 2002 05:18:57 -0800588_us-ascii Claudio- I've gotten much better throughput on DRDA connections via TCP/IP than VTAM, with the exception of the first versions. There are several parameters in VTAM which can be tuned...Pacing and blocksize. Your VTAM support guy should be familiar with them, and will want to set them up so that your moving of 400K rows doesn't screw up normal network traffic. There are also considerations as to how much memory is in your 37x5 front end processor. In addition, you'll want to use a fairly recent version of VTAM, and make sure it has a high dispatching priority in MVS. [...] 30736 110 50_Re: Top 50 cpu usage rapport for SQL text from DB28_Ron Root24_ron.root@CPA.STATE.TX.US31_Thu, 28 Mar 2002 07:33:09 -0600454_iso-8859-1 Frank, Yours is problem that many of us face with web and PowerBuilder applications. The work comes it via a single plan and often via a single userid. Online monitors (like Mainview or Aptune in our case) are a big help. One can identify the high resource SQL with these products. We also find Stobe to be very useful. We use Neon's Shadow Direct and are able to Strobe the Shadow tasks on the mainframe to identify high resource users. [...] 30847 94 33_Re: Timestamps in the DBRM module27_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM31_Thu, 28 Mar 2002 08:52:19 -0500421_iso-8859-1 Do you not have the CBT stuff because you need approval to use it? If so, won't you have the same problem with the code on DB2-L-Documents?

Tina

-----Original Message----- From: Manish Kothari [mailto:mkothari@DATAMIRROR.COM] Sent: March 27, 2002 7:53 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module



Hi,

How can I get of the existing DB2-L-Documents ? [...] 30942 177 73_Re: Why not more than one partitioned index on a partitio nedtablespace ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 28 Mar 2002 14:11:37 -0000516_iso-8859-1 Before this discussion gets out of hand, might I suggest we revisit the question in a few months.........

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



-----Original Message----- From: Joao Alberto Lima [mailto:joaolima@SENADO.GOV.BR] Sent: 28 March 2002 01:51 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Why not more than one partitioned index on a partitionedtablespace ? [...] 31120 204 72_Re: Why not more than one partitioned index on a partitionedtablespace ?17_Joao Alberto Lima22_joaolima@SENADO.GOV.BR31_Thu, 28 Mar 2002 07:42:40 -0600377_- Dear Sanjeev, Tim and James Khan,

Thanks for the answers. I think that we have a misunderstanding. What I am considering is not a "secondary cluster index", but a "secondary partitioned index".

In the example, the index 1 (ZZZ1I001) uses only the first column to define the partitioning. The others two columns of index 1 define, together, an access path. [...] 31325 138 37_Re: Locksize ANY versus Locksize PAGE13_Rusty Schmidt26_russell.schmidt@LLBEAN.COM31_Thu, 28 Mar 2002 09:12:16 -0500565_ISO-8859-1 Morning,

Maybe my world is very unique. The problem is that I don't always know, so I can not just build with locking at the tablespace but with the defaults, I have the best of both worlds... page level locking 99% of the time and then when an application requires the volume of locked pages DB2 can attempt escalation to tablespace hence reducing the work of the IRLM. Is the reason to build with PAGE because you cannot afford to escalate for some reason or another ? I apologize if my concept of lock escalation is flawed, maybe its time [...] 31464 152 37_Re: Migration from OS/390 to Midrange12_Chao Terry F20_Terry.F.Chao@IRS.GOV31_Thu, 28 Mar 2002 09:06:01 -0500340_iso-8859-1 Recently done something similar and ported about 50 gig. worth of DB2 data to midrange. One, among many, solution we've used with no extra budget requirements:

a-1) Some DDL reverse/forward engineering tool to port DB2 objects to midrange, or db2look command with a lot of work a-2) DB2 Connect for OS/390 DB2 access [...] 31617 25 33_Re: Timestamps in the DBRM module14_Manish Kothari23_mkothari@DATAMIRROR.COM31_Thu, 28 Mar 2002 09:27:19 -0500347_iso-8859-1 Yes, but I will at least know of another resource that might be able to use. Once it is given, I can get it without asking anyone.

-----Original Message----- From: Hilton, Tina, BmS - NMI -PM [mailto:Tina.Hilton@BMSUS.COM] Sent: Thursday, March 28, 2002 8:52 AM To: DB2-L@RYCI.COM Subject: Re: Timestamps in the DBRM module [...] 31643 30 74_Re: Why not more than one partitioned index on a partitioned tabl espace ?14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 28 Mar 2002 09:00:30 -0600316_iso-8859-1 Hi Joao,

perhaps I am missing something here, but why do you want the index to be partitioned ? Is it simply to allow utilities to run in parallel ?

I don't see what else you will gain from breaking it into separate pieces based on the key, and we already have the piecesize construct. [...] 31674 154 37_Re: Locksize ANY versus Locksize PAGE10_James Kwan19_jamesdb2kwan@CS.COM31_Thu, 28 Mar 2002 09:01:38 -0600379_iso-8859-1 Rusty,

Based on what you just said, you should use ANY option. If you see a lot of deadlock/timeout because of the escalation, then you might change it back to PAGE. Susan Lawson mentioned that even you set it to PAGE, escalation might still occur. I personally did not see that happen, may be you should check the manual again to see if this is the case. [...] 31829 29 46_Re: sec: unclassified >> Are extents harmless?8_D Preble18_preble@CETLINK.NET31_Thu, 28 Mar 2002 08:38:13 -0600552_- Your have to look at your SMS definitions (dataclas). I think its ACS routines is the correct buzz words. We had a problem will allocations too but the real problem was that the rules did not allow multivolume attribute for the DB2 vsam datasets. In our case, we simply got extent failed error message. Be aware that when you go to a new volume that you will get the amount space = to the priqty and not the secqty for the first allocation on the new volume. When dealing will larger datasets (reguardless if these are partitioned or no), using [...] 31859 130 37_Re: Locksize ANY versus Locksize PAGE14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 28 Mar 2002 15:43:56 -0000361_iso-8859-1 Rusty,

The problem is not what happens to the escalating application its what happens to everyone else - they get locked out of the tablespace until your escalator is done with the locks.

Most people think that any task taking enough locks to cause escalation is taking too many locks (i.e., is not committing often enough) anyway [...] 31990 112 40_Re: Why not more than one partitioned in64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Thu, 28 Mar 2002 09:55:12 -0600617_ISO-8859-1 Hi Joao Lima,

What you propose:

Defining an NPI (non-partitioning index, or secondary index, or performance index - whatever you want to call it) with the same key structure as the clustering index, in order that you might gain total partition independence...

flat out will not work today.

It won't work because it IS a secondary index, and will be treated like a secondary index, and will still be in one large dataset (unless you define pieces). But even still, the design today of everything around DB2, and within DB2, is structured around the concept that is it NOT a [...] 32103 36 52_Exist clause works in version 6 but not in version 726_Johnson, Bill (Pittsburgh)26_bjohnson@RUSSELLMELLON.COM31_Thu, 28 Mar 2002 08:07:18 -0800312_iso-8859-1 We are currently in the process of converting from DB2 V6 to V7 and are experiencing a problem in some of our existing SQL in V7 that ran fine in V6. An example of the code is below. Anyone else run into this problem? The exist/not exist clauses give us an SQLCODE of -582 and SQLSTATE of 42625. [...] 32140 44 74_Re: Why not more than one partitioned index on a partitioned tabl espace ?17_Joao Alberto Lima22_joaolima@SENADO.GOV.BR31_Thu, 28 Mar 2002 09:38:01 -0600350_- Hi Colin,

>perhaps I am missing something here, but why do you want the index to be >partitioned ? Is it simply to allow utilities to run in parallel ?

The maintanace of PI (partitioned index) is easier than NPI (non partitioned index). For example, a REORG of a NPI of a multi-billion row table could spends more than 20 hours. [...] 32185 69 37_Re: Locksize ANY versus Locksize PAGE13_Rusty Schmidt26_russell.schmidt@LLBEAN.COM31_Thu, 28 Mar 2002 11:19:21 -0500340_US-ASCII I do see your point. Like others I assume, I struggle with the fine line of attempting to get the application in and out with the least possible resources required. locks, commits(open of a new cursor) etc... while avoiding lock wait. I admit, I am maybe a DBA with much to learn. I just hasn't been a issue here yet. Thanks. [...] 32255 22 38_Summary Table Refresh and Catalog Lock16_Victor McDonnell31_victor_mcdonnell@FREDDIEMAC.COM31_Thu, 28 Mar 2002 11:26:16 -0500521_us-ascii Dear List, 'Anyone know why UDB locks systables during a refresh of a summary table? TIA

Victor McDonnell Database Administration Data Architecture & Infrastructure Email: victor_mcdonnell@freddiemac.com



(Embedded image moved to file: pic22180.gif)

================================================ 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. 32278 196 37_Re: Locksize ANY versus Locksize PAGE0_24_William_O'Black@FFIC.COM31_Thu, 28 Mar 2002 07:11:43 -0800590_iso-8859-1 Actually Phil answered the question with a question. As many of you folks like to say, the answer (or question) depends on what you're doing.

I support a good sized PeopleSoft application. During the day, we have nice small transactions entered by online users. Row or page locks are what you want so you can have multiple updaters against the same tables. At night the online users are kicked off (we stop ddf) and batch work takes off. For those of you who support PeopleSoft (and maybe SAP), the batch work runs without commits. We'd need hundreds of thousands of [...] 32475 126 50_Re: Top 50 cpu usage rapport for SQL text from DB20_24_William_O'Black@FFIC.COM31_Thu, 28 Mar 2002 07:26:06 -0800510_iso-8859-1 Apptune from BMC (I'm a user not a rep) can sort SQLs by CPU time and provide the SQL text. Getting the actual user in three tier is tougher. I haven't worked with Powerbuilder, but you'd need to get Powerbuilder to send the actual id up to DB2. In DB2 V5 we had to get a fix from IBM to display the actual userid in addition to the connect id when you do a display thread. I believe that's included as part of V6 but not sure. We also got an upgrade for Apptune that lets them save the actual [...] 32602 16 14_creating users15_Mark Maziarczyk22_MMaziarczyk@SYMCOR.COM31_Thu, 28 Mar 2002 11:47:01 -0500426_us-ascii How do you go about creating users that will have DBA authority over the database? We would like to get away from using the instance owner id for these tasks?

Thank you

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 32619 44 28_RETURN and Commit processing31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Thu, 28 Mar 2002 16:47:48 +0000332_iso-8859-1 Hi All,

I have faced a different situation today than what i had sometime 2 years back. I have a master plan A used with somewhere around 50 DBRMs of 50 programs. All of them are CICS programs. There is only one transaction to invoke the programs, and which program to execute is decided by a router program. [...] 32664 168 37_Re: Locksize ANY versus Locksize PAGE14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 28 Mar 2002 16:58:12 -0000340_iso-8859-1 This is a GOOD example of using locksize any to do exactly what you want it to do, ESPECIALLY the part about kicking the on-line users off (if only we could KEEP them off....)

The point being that you are almost using LOCKSIZE ANY to get "guaranteed" tablespace locks in batch and "guaranteed" page locks in on-line. [...] 32833 70 56_Re: Exist clause works in version 6 but not in version 713_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 28 Mar 2002 10:56:21 -0600326_iso-8859-1 Russell,

I haven't seen this problem, but does the query contain an IN list within a CASE expression? This is not permitted but was allowed in some instances in V6. Checkout APAR PQ45679.

If not, then I don't have any idea.

A side issue, should there be a join predicate in your subquery? [...] 32904 70 32_Re: RETURN and Commit processing10_James Kwan19_jamesdb2kwan@CS.COM31_Thu, 28 Mar 2002 11:25:53 -0600334_iso-8859-1 Sanjeev,

I have not touched CICS for a long time. I know there is a difference between RETURN and RETURN TRANSID. Basically if the transaction id changes, it will implicitely commit otherwise it will not. I don't believe in your case, it has issued a commit. That's why you can roll back the transactions in Y. [...] 32975 90 56_Re: Exist clause works in version 6 but not in version 726_Johnson, Bill (Pittsburgh)26_bjohnson@RUSSELLMELLON.COM31_Thu, 28 Mar 2002 09:41:38 -0800485_iso-8859-1 Terry, You are correct, there are IN list within case expressions and after removing them, the query ran fine on both our test (V7) and production (V6) systems.

Thanks for your help and expertise.

Bill Johnson Russell Mellon Pittsburgh, Pa.

-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Thursday, March 28, 2002 11:56 AM To: DB2-L@RYCI.COM Subject: Re: Exist clause works in version 6 but not in version 7 [...] 33066 31 73_Re: Why not more than one partitioned index on a partitioned tablespace ?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 28 Mar 2002 11:13:50 -0600419_- Here are a couple of my opinions and thoughts, hopefully with a dash of humor. Any relationship to anything official from my company are probably a coincidence. The primary technique for new requirements is a database for IBMers. It provides a structure for responding and managing the information and responses. Your local IBMer can look up the Lotus Notes application called FITS if you want to add your vote. [...] 33098 21 44_Re: C++ Declaration for CLOB larger than 32K12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 28 Mar 2002 11:28:59 -0600563_- It's time to dig into the SQL Reference probably. The application development books cover this as well, but you'll want to read about the BEGIN DECLARE SECTION and SQL TYPE IS CLOB. There are some options:

Here is another place to look pasted from the index of the DB2 for UNIX, WIndows, OS/2 SQL Reference V7.2 References to BLOB, CLOB, and DBCLOB HostVariables..........138 References to Locator Variables ....139 References to BLOB, CLOB, and DBCLOB File Reference Variables .......139 References to Structured Type Host Variables............142 [...] 33120 76 56_Re: Exist clause works in version 6 but not in version 711_David Nance16_DWNance@FHSC.COM31_Thu, 28 Mar 2002 12:44:14 -0500566_US-ASCII Bill, Just a thought. In the select stmt that is returned, is the query attempting to select E11.AS_OF_SECURITY_ID or any other column from tables PRAMPRO.BLKROC_CNTL E10 or PRAMSEC.BLKROC_FIX E11? Or as Terry suggested within a case stmt. We could make better guesses seeing the rest of the stmt.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Johnson, Bill (Pittsburgh) Sent: Thursday, March 28, 2002 10:07 AM To: DB2-L@RYCI.COM Subject: Exist clause works in version 6 but not in version 7 [...] 33197 32 80_Jim Kleewein interview about the Xperanto Technology Demo - DB2 + XML and XQuery16_Jeffrey I Condon19_jicondon@US.IBM.COM31_Thu, 28 Mar 2002 09:46:12 -0800680_us-ascii The DB2 developer Domain recently posted some more stuff about Xperanto, which is IBM's new middleware for integrating XML and DB2.

Interview @: http://www7b.software.ibm.com/dmdd/library/demos/0203xperanto/0203xperanto.html?open&l=327,t=gr

Xperanto Demo http://www7b.software.ibm.com/dmdd/library/demos/0203xperanto/0203xperanto.html?open&l=327,t=gr





============================== Jeffrey I. Condon IBM Corporation -- jicondon@us.ibm.com 415-545-5512 (T/L - 473-5512) http://www.ibm.com/developerWorks =============================== "Even if you are on the right track, you'll get run over if you just sit there." -Will Rogers [...] 33230 36 18_Re: creating users11_Steve Mazer17_smazer@FMTUSA.COM31_Thu, 28 Mar 2002 12:28:29 -0500437_us-ascii Mark, Not sure of your platform, but for AIX, here goes. Users are put into groups by the Systems Admins and those groups would show in the DBM config for the instance as SYSADM, SYSCTRL, and SYSMAINT groups. Each of these levels has different priveledges and many overlap. SYSADM is of course the highest. A good start is in the Admin Guide, Implementation, Chapter 5. You can also grant, with SQL, DBADM on the database. [...] 33267 34 32_Re: RETURN and Commit processing31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Thu, 28 Mar 2002 17:49:38 +0000309_iso-8859-1 James,

The manual surely says EXEC CICS RETURN command at EOT. This EOT is end of task or end of transaction. It looks logical to me now and thanks for pointing out the difference between RETURN and RETURN TRANSID. I just could not visualize the difference when i found this behaviour. [...] 33302 39 32_Re: RETURN and Commit processing31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Thu, 28 Mar 2002 18:05:06 +0000600_iso-8859-1 James,

Forgot to send this one. Please explain EOT in this context. Is this end of task or end of transaction? I understand it as end of task and if it is then implicit COMMIT should be issued.

=================== FROM CICS/DB2 Guide CICS ignores any EXEC SQL COMMIT statement in your application programs. The DB2 commit must be synchronized with CICS, which means that your program must issue an EXEC CICS SYNCPOINT command. CICS then performs the commit processing with DB2. An implicit SYNCPOINT is always invoked by the EXEC CICS RETURN at EOT. =================== [...] 33342 74 18_Re: creating users14_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM31_Thu, 28 Mar 2002 13:11:23 -0500523_us-ascii Mark,

If you're referring to DBADM access over a particular database, you can just grant DBADM authority to individual user ids.

If you're referring to SYSADM authority for all databases in a particular instance, then you would need to create a group on the database server using the native operating system services (i.e. NT, AIX, etc.), and then update the database manager configuration SYSADM_GROUP parameter with that group name. This would require a bounce of the instance to take effect. [...] 33417 68 32_Re: RETURN and Commit processing12_Smith, Bruce22_Bruce.Smith@BBANDT.COM31_Thu, 28 Mar 2002 13:13:20 -0500528_- I believe the implicit commit only happens when a CICS program issues a EXEC CICS RETURN END-EXEC that returns control to CICS thus ending the CICS transaction and the logical unit of work. If the CICS program is only returning control to a "linking" application program then the logical unit of work is still active and no implicit commit happens. There is also an implicit rollback of a logical unit of work that will happen if CICS receives control back from an application program due to an abort or abend condition. [...] 33486 21 50_Re: Top 50 cpu usage rapport for SQL text from DB212_Roger Miller19_millerrl@US.IBM.COM31_Thu, 28 Mar 2002 11:47:25 -0600346_- Here is an option.

When it's dynamic SQL from an application, then the dynamic statement cache is generally needed to avoid having PREPARE dominate the cpu time. There is instrumentation for the dynamic statement cache that is relatively easy to report on, and I think all of the major performance monitors have the needed support. [...] 33508 71 37_Re: Locksize ANY versus Locksize PAGE13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Thu, 28 Mar 2002 10:16:43 -0800375_iso-8859-1 Phil,

I am confused here. My understanding has been that LOCKSIZE has, in almost all the cases, nothing to do with Lock escalation. It is the LOCKMAX parameter which determines when escalation. For that matter Lock escalation can even occur with Locksize Page. If Lock escalation is your concern, why not define tablespace with Locksize ANY Lockmax 0. [...] 33580 47 37_declared vs. created temporary tables16_Scott.Gjerdingen27_Scott.Gjerdingen@TARGET.COM31_Thu, 28 Mar 2002 12:04:20 -0600149_iso-8859-1 Any thoughts on the pros/cons in using declared temporary tables vs. created temporary tables in v6/7 on z/os?

tia

- scott - 33628 47 34_Problems with HPunload after Reorg13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US31_Thu, 28 Mar 2002 13:24:04 -0500434_US-ASCII Hello All,

Platform OS - z/OS V1R1.0 DB2 - V7.1 for z/OS HPunload V1.1

Problem

HPunload abends with a SYS00001 and message code IKJ56228I "Data set *.*.*.*.I0001.* not in catalog". This only happens after a Reorg using SHRLEVEL CHANGE does a switch to the ghost data set and that data set becomes *.*.*.*.J0001.* It appears that DB2 does not change catalog entries when it uses the J0001 data sets. [...] 33676 96 56_Re: Exist clause works in version 6 but not in version 726_Johnson, Bill (Pittsburgh)26_bjohnson@RUSSELLMELLON.COM31_Thu, 28 Mar 2002 10:26:57 -0800329_iso-8859-1 Terry was right, we have an IN list within a case statement which works fine in our V6 production system and gives us the error in our V7 test system. After breaking the IN list into AND's/OR's it works fine on both systems. I'd have sent the entire SQL, but it's over 700 lines long. One of our shorter ones. [...] 33773 137 37_Re: Locksize ANY versus Locksize PAGE31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Thu, 28 Mar 2002 19:05:28 +0000525_iso-8859-1 Looks to me as well that LOCKSIZE just decides the hierarchy while escalation.

Phil, Please explain the reason for "X" lock promotion on SYSPLAN and SYSPLANAUTH. Looks like i am unable to visualize that it will only occur in case of LOCKSIZE ANY.

As Ashish mentioned, lock escalation will occur unless we specify LOCKMAX = 0, which i do not think is a good idea because of chances of having more number of locks(CPU constraints). This can also lead to -904 depending on LOCK PER USER parameter. [...] 33911 84 37_Re: Locksize ANY versus Locksize PAGE13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 28 Mar 2002 13:07:02 -0600592_iso-8859-1 I think one source of confusion on this is the default for lockmax when you create an object. If you specify LOCKSIZE ANY and leave off the LOCKMAX, it defaults to SYSTEM (allowing escalation). If you specify LOCKSIZE PAGE and leave off LOCKMAX, it defaults to 0 (disabling escalation). This leads to the belief that LOCKSIZE ANY is causing the escalation, and not the LOCKMAX. If you simply alter the LOCKSIZE from ANY to PAGE (without changing the LOCKMAX) you will find out pretty quick that you do still get escalation. In my opinion, the recommendation in the Appl prog. [...] 33996 33 37_Re: Locksize ANY versus Locksize PAGE13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 28 Mar 2002 13:18:19 -0600552_us-ascii LOCKSIZE ANY is basically left over from the old S/370 days. The IRLM had problems with the storage for two many locks in the first release of DB2 and there was no "above the line" back then. The solution was LOCKSIZE ANY to reduce the number of locks.

Today, because DB2 made it the default, folks use it without thinking of the consequences. If you don't mind escalation (which by the way is not the natural way for DB2 to do stuff) use ANY. If you want control over the locking and can afford the resource unavailable use PAGE. [...] 34030 75 37_Re: Migration from OS/390 to Midrange12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 28 Mar 2002 12:55:27 -0600636_- Here are a couple of my favorite URLs for migration. There are differences in which box you mean,

SQL common features for developers of portable family applications http://www.iseries.ibm.com/developer/db2/db2common.html

We are working on a book, SQL Reference that expands the discussion, but it won't be ready for a couple of months.

Planning for Conversion to the DB2 Family: Methodology and Practice, GG24-4445-00 Redbook, might be useful in project, planning Migrating and Managing Data on RS/6000 SP with DB2 Parallel Edition, SG24-4658-00 Redbook, moving data DB2 UDB V7.1 Porting Guide, SG24-6128-00 [...] 34106 86 37_Re: Locksize ANY versus Locksize PAGE13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 28 Mar 2002 13:26:19 -0600613_us-ascii If you select LOCKSIZE ANY and exceed the system defined NUMLKTS value, you will escalate to the next higher level lock. Escalation is the combination of both factors occurring.

Willie

"Mohan, Ashish" wrote:

> Phil, > > I am confused here. My understanding has been that LOCKSIZE has, in > almost all the cases, nothing to do with Lock escalation. It is the LOCKMAX > parameter which determines when escalation. For that matter Lock escalation > can even occur with Locksize Page. If Lock escalation is your concern, why > not define tablespace with Locksize ANY Lockmax 0. > > [...] 34193 57 32_Re: RETURN and Commit processing10_James Kwan19_jamesdb2kwan@CS.COM31_Thu, 28 Mar 2002 13:24:18 -0600639_iso-8859-1 I think it meant end of transaction since we are talking about CICS transaction here.

James ----- Original Message ----- From: "Sanjeev ...." Newsgroups: bit.listserv.db2-l To: Sent: Thursday, March 28, 2002 12:05 PM Subject: Re: RETURN and Commit processing



> James, > > Forgot to send this one. Please explain EOT in this > context. Is this end of task or end of transaction? I > understand it as end of task and if it is then > implicit COMMIT should be issued. > > =================== FROM CICS/DB2 Guide > CICS ignores any EXEC SQL COMMIT statement in [...] 34251 23 15_Any volunteers?10_Tom Willis24_tom_r_willis@HOTMAIL.COM31_Thu, 28 Mar 2002 14:15:08 -0600 34275 45 32_Re: RETURN and Commit processing13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Thu, 28 Mar 2002 13:43:43 -0600411_- Hi all It's end of task, the CICS task remain alive until control returns to CICS. For example if you have this sequence of events : Program A is started by entering a TRANSID on a terminal A links to B B XCTLs to C C does a RETURN

The control will return to A and as far as CICS is concerned the task is still alive. If A now issues a RETURN it's a end of task with implicit commit. HTH Mohammad [...] 34321 82 50_Re: [DB2EUG] Unexpected RI Result : is this normal14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM31_Thu, 28 Mar 2002 14:20:55 -0600423_us-ascii Phil,

How is it that you are able to insert more than one row with a TASK_ID value equal to 1? You said that you declared TASK_ID to be the primary key. If that's the case, DB2 should create a unique index on that column and reject any attempts to insert more than one record with TASK_ID = 1, yet, if I'm interpreting you correctly, you say that you inserted four rows having the value 1 for TASK_ID. [...] 34404 102 32_Re: RETURN and Commit processing12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 28 Mar 2002 12:27:43 -0800521_us-ascii My client currently has the problem that CICS Program A Updates DB2 and then links to CICS Pgm B which does some DB2 processing. B then links to C which does some DB2 SELECTs and does a IMS PSB SCHED updates IMS databases and then issues a PSB TERM. It then does a CICS RETURN To B passing a value back. B then returns to A. If A now does a CICS ROLLBACK, its changes are not rolled back as somehow they've already been committed, even though no commits have been issued. If the Link to C is commented out, [...] 34507 28 32_Re: RETURN and Commit processing31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Thu, 28 Mar 2002 20:27:51 +0000450_iso-8859-1 Mohammad,

This is exactly what i was looking for. This explaination matches to my thought and research that it is a task but unless control goes to CICS, it is the same task for CICS.

Thanks a lot !!

With Best Regards, Sanjeev

________________________________________________________________________ For live cricket scores download Yahoo! Score Tracker at: http://in.sports.yahoo.com/cricket/tracker.html [...] 34536 134 82_Reposting Migration from DB2 NT to OS/390 - Import error on table with LOB columns22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 28 Mar 2002 14:45:04 -0600635_- > Dear Listeners, > > DB2 OS/390 V7 > DB2 NT V7.2 > DB2 Connect V7.2 > TCP/IP Communication Server V2.10 > MVS V2.10 > > I'm trying to import a table that contains LOB columns from DB2 NT to > OS/390 and I'm getting the following error. I'm running the import command > from DB2 CLP: > -------------------------------------------------------------------------- > --------------------------------------------------- > SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date > > "20020326", and time "162307". > > SQL3153N The T record in the PC/IXF file has name "C:\Export\basefile", > qualifier "", and source " [...] 34671 159 19_Re: Any volunteers?14_Cable, Larry A19_larry.cable@NWA.COM31_Thu, 28 Mar 2002 15:04:49 -0600621_iso-8859-1 I could test them on UDB 7.2 EE and EEE on AIX if you like. What language are they written in??

LarryC

-----Original Message----- From: Tom Willis [mailto:tom_r_willis@HOTMAIL.COM] Sent: Thursday, March 28, 2002 2:15 PM To: DB2-L@RYCI.COM Subject: Any volunteers?





Fellow UDB DBAs,

They say necessity is the mother of invention. In my UDB shop, we had some needs that our usual UDB utilities did not cover. Therefore I decided to write my own. We have found them useful in our environment which is UDB v6 v7 on Solaris 8, WINNT and WIN2K. I want to find out if: [...] 34831 58 20_UDF related question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Thu, 28 Mar 2002 16:28:30 -0500366_iso-8859-1 Hi Listers

I would like get your valuable input on this. I was under the impression that I could write a UDF to calculate "Geometric Mean" just like the existing column functions SUM, AVG etc. In other words, I hoping to be able to write SQL like

SELECT GeoMean(monthy_return) from t1 where stock = xyz and period between 2000 and 2001 [...] 34890 146 50_Re: Top 50 cpu usage rapport for SQL text from DB212_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 28 Mar 2002 16:52:57 -0600534_iso-8859-1 I would say to look at APPTUNE for this kind of summarization. To get to userid level then Merant will have to use a published DB2 interface to pass that data to DB2 and then, if it uses that interface, APPTUNE can associate the userid with the SQL text. Apptune will work whther it is static or dynamic, but if it is dynamic you need to monitor the files that collect the SQL text as they would need to be large to hold any decent size of data interval. If the files are too small, then online reporting would suffer [...] 35037 121 33_Re: Trigger call stored procedure10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 28 Mar 2002 15:56:02 -0700466_iso-8859-1 Hi Suresh, Thanks for your suggestions, I have to think about which way is the best one for us.

Grace

-----Original Message----- From: Suresh Sane [mailto:data_arch@HOTMAIL.COM] Sent: Wednesday, March 27, 2002 3:08 PM To: DB2-L@RYCI.COM Subject: Re: Trigger call stored procedure



Grace - I would NOT use a trigger for this purpose. Not sure what you mean by "automatically". Any one of the traditonal methods will work - [...] 35159 98 37_Re: Locksize ANY versus Locksize PAGE13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 28 Mar 2002 17:01:00 -0600391_us-ascii I guess I messed up and used NUMLKTS instead of LOCKMAX SYSTEM. To make it more clear I should have said "If you specify LOCKSIZE ANY, LOCKMAX SYSTEM and exceed the NUMLKTS value you will escalate." And to make sure I don't get blasted again, LOCKSIZE ANY and LOCKMAX nnnn, where nnn is some integer, DB2 will escalate if you exceed the LOCKMAX value specified by the integer. [...] 35258 95 24_Re: UDF related question11_Riyaz Momin15_RMomin@FHSC.COM31_Thu, 28 Mar 2002 18:09:37 -0500562_US-ASCII Sanjay,

IBM books mention that an external column UDF cannot be defined to DB2, but a column UDF that is sourced on one of the built-in column functions can be defined. If you are only interested in calculating GeoMean, and not using it's values DIRECTLY in a SQL, I think what you need is a Stored Procedure. You can pass the parameters in WHERE clause as input to SP. Put all your logic of calculating GeoMean in the Stored Procedure, and return the result. You can customize the SP to return multiple GeoMeans if you want to do a GroupBy [...] 35354 64 54_Forcing a RANGE partition on UDB/AIX - not working ???10_Umesh Apte19_apteumesh@YAHOO.COM31_Thu, 28 Mar 2002 17:49:33 -0800478_us-ascii Hi list,

If someone could help me out with this, it will be GREAT.

In essence - I can NOT find a way to force a unique value of partitioning key to occupy a unique partition / node. DB2/UDB for AIX version 7.2

I have (7 nodes total but) 6 data nodes / partitions across which I need to spread data. Tablsepace is defined across these 6 data nodes. There are 6 distinct values assigned to a generated column and it is used as PARTITIONING KEY. [...] 35419 147 37_Re: Locksize ANY versus Locksize PAGE13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 28 Mar 2002 22:13:09 -0600550_iso-8859-1 The one thing I would add (and the point that seems to be in contention from time to time) is that this is the exact same as with locksize page -- if you specify locksize page, lockmax system and exceed NUMLKTS (or exceed the "nnn" specified for lockmax) you will still get lock escalation. In other words, you do *not* need "LOCKSIZE ANY" for escalation to occur. The one thing I'm not completely sure of (and have never bothered to test) is what happens if you specify locksize ANY lockmax 0 -- I would be willing to guess that you [...]