1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2004, week 3 2 49 17_Re: db2_hash_join14_Kiran P Gunnam17_kgunnam@YAHOO.COM31_Sun, 14 Mar 2004 22:06:58 -0800503_us-ascii At freddie Mac we actually diabled Hash Joins. Some of the SQL's which is used to run forever and when we changed to No hash joins are actually getting the results back. Thats's in V7. We havent Tested in V8 yet. In couple of weeks.



Kiran

Tom Willis wrote: Can anyone think of a situation is which you would NOT want to enable hash join on UDB v8? My team is getting advice from Siebel saying we should not enable hash joins. Just curious. [...] 52 44 41_Re: Calling SP with one input/output parm14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 15 Mar 2004 02:17:38 -0600279_- This is an SQL Language SP?? Why a VARCHAR? It is very difficult to manipulate binary numbers as characters in SQL - meaning that manipulating the length bytes will be unpleasant. It would be easier to use a fixed length parameter with a PIC 999 type length at the start. [...] 97 33 27_Reading IFCID for Deadlocks16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Mon, 15 Mar 2004 04:19:05 -0800421_us-ascii The deadlock information in detail is apparently displayed by IFCID Record 172. Could someone tell me how to 'view' this information? Have never 'directly' viewed trace information as we have Omegamon installed which suffices for the most part. But we have some deadlocks here and I feel that IFCID Record 172 may have more information of our use. How is trace information viewed? Is it in readable format? [...] 131 106 41_Re: Calling SP with one input/output parm12_Chris Fryett30_Chris.Fryett@MUTUALOFOMAHA.COM31_Mon, 15 Mar 2004 07:01:41 -0600547_us-ascii The SP that I must access is a COBOL SP that has defined its inout parm to be a VARCHAR(500). Since I am using another application that needs to call this SP I figured I would simplify things by having the calling application use IN Field1, IN Field2, OUT Field3, OUT Field4. Since the COBOL SP expects a fixed length field for each one I was CONCAT each field, but then I have to consider padding the input values if they are not long enough. I already have to initialize the output field to their proper lengths. This basically is [...] 238 17 10_DSNJU999 ?6_Daniel17_c300501@YAHOO.COM31_Mon, 15 Mar 2004 07:42:30 -0600331_- I've read this from a presentation : DSNJU999 internal service aid to alter bufferpool size offline in BSDS Useful when buffer pools mistakenly configured too large - PQ56034 (V6, V7)

The enhancement is interesting , and i would like to learn more (JCL sample..) , but could not find any documentation from anywhere. [...] 256 174 31_Re: Migrating the DB2 subsystem52_=?utf-8?B?zpTOl86czp/Oms6RIM6azpHOm86bzpnOn86gzpc=?=24_dimokap@INTERAMERICAN.GR31_Mon, 15 Mar 2004 15:45:57 +0200539_utf-8 We are about to migrate from v6 to v7 also and in order to test the whole thing , i have applied a scenario of that kind many times. More or less, these are the basic steps to follow:

1. Backup production db2 subsystem (bsds,log,catalog,directory,ddf etc) in hostA with ADRDSSU . 2.Create the necessary alias and user catalog (as job DSNTIJCA does) 3. Restore the file in HostB. 4.Update SYS1.PROCLIB in HostB with the db2 procs (mstr, dbm1 etc ) and SYS1.PARMLIB (IEFSSN00,PROG00,PROGLK) -the things job DSNTIJMV does . [...] 431 57 31_Re: Reading IFCID for Deadlocks11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US31_Mon, 15 Mar 2004 09:21:37 -0600381_iso-8859-1 Omegamon may already have this data for you depending on your configuration set up

From the classic omegemon

Check option Resources, Traces (option r.f) and look under the performance trace being written to op1. If it has 172 trace turned on you can view the lock detail by viewing a thread in history (h), select a thread and then option c lock wait [...] 489 187 34_Re: Incomprehensible access path..13_Michael Ebert18_mebert@AMADEUS.NET31_Mon, 15 Mar 2004 16:42:22 +0100348_us-ascii Hi Paul,

sorry for the delay... the SQL couldn't be simpler: SELECT FROM INTO WHERE A=:hv AND B=:hv AND C=:hv AND F=:hv - it uses host variables (COBOL). We do not use REOPT(VARS). Datatypes match. Do you have the APAR/PTF number handy? I haven't found anything promising in the APAR DB. [...] 677 41 17_DB2 SORT question0_16_mscarpa@CESVE.IT31_Mon, 15 Mar 2004 16:49:18 +0100334_US-ASCII Estimeed listers

I'm digging deep in my DB2 SORT usage using IFCIDs 95/96, via REXX. It works quite well but I noticed a 'strange' thing: I've a lot of SORT request from some packages even when the result set has 1 (one) or 0 records to sort. Runstats,clustering and other stuffs seem ok, REBIND is quite recent. [...] 719 72 51_Re: Concurrent Update between SELECT and SUBSELECT?10_Victor Gil18_Victor_Gil@ADP.COM31_Mon, 15 Mar 2004 10:24:39 -0600582_- James,

Thank again. The cursor approach has been suggested by IBM and is also recommended by our DBAs. However, it does feel strange to have to go this route when dealing with a single row. Compare this to updating a VSAM record, say under CICS, using direct READ UPDATE versus START BROWSE, GET NEXT, etc., END BROWSE. I understand that the cursor-for-update places an UPDATE lock at the OPEN time [i.e. BEFORE filtering the rows] and so should do the singleton UPDATE. The discussion with our DBAs I had so far centers around the point that "the UPDATE doesn't know [...] 792 26 42_Re: What if segmented TS is over 64Gbytes?7_Do Park19_doparkdba@YAHOO.COM31_Mon, 15 Mar 2004 10:39:58 -0600407_- Hello Isaac

I already planed to change to parttioned tablespace, but we don't have much time to change. I'd like to know what will happen to the table if we continue to use the tablespace over 64G bytes with any change.



Can Imageplus access data if the size of the tablespace is over 64Gbytes? Can DB2 utility like Imagecopy work if the size of the tablespace is over 64Gbytes? [...] 819 100 51_Re: Concurrent Update between SELECT and SUBSELECT?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 15 Mar 2004 18:49:46 +0200349_- Hi,

I did those measurements on many versions, always the same results. The last ones are for V7. The same environment all the time. I don't have the drill down here :-( so I can't comment on the locking.

It's very easy to conduct it on your own table.

Isaac Yassin IBM Certified solution expert DB2 V7 for OS/390 & Z/OS [...] 920 56 42_Re: What if segmented TS is over 64Gbytes?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 15 Mar 2004 18:55:12 +0200297_- Hi Do,

DB2 will issue a message when going over 64gb and will not continue to add data to the TS.

00C9009B

Explanation: An attempt was made to load data into a linear table space, but the table space is full. A linear table space can contain a maximum of 64GB of data. [...] 977 77 42_Re: What if segmented TS is over 64Gbytes?12_michael bell21_mbell11a1@VERIZON.NET31_Mon, 15 Mar 2004 11:08:10 -0600323_iso-8859-1 DB2 does not extend a basic or segmented tablespace beyond 64 GB. When you attemp to add the next row using SQL, you will get a -904 resource not available on some type. If you attempt to add the data using a utility, the utility will abend with an error message that should be the same as the SQL message. [...] 1055 300 34_Re: Incomprehensible access path..13_Paul Weissman21_paul.weissman@UBS.COM31_Mon, 15 Mar 2004 12:10:21 -0500645_iso-8859-1 -----Original Message----- From: Weissman, Paul Sent: Monday, March 15, 2004 11:57 AM To: 'DB2 Database Discussion list at IDUG' Subject: RE: Incomprehensible access path.. Michael,

The APAR fix that's just been packaged by IBM is PQ82909. We were getting poor access paths due to poor filter factor calculations for range predicates involving date columns, such as " WHERE A >= date-literal" or "WHERE A BETWEEN date-literal1 AND date-literal2". Oddly, > or < were usually OK, but >= or <= was bad. We are mostly using dynamic SQL with literals or static SQL with host-variables and REOPT(VARS) and the runstats included [...] 1356 39 31_Re: Reading IFCID for Deadlocks17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Mon, 15 Mar 2004 11:21:42 -0600475_- You can look at the Admin guide. You have to use READS or READA via IFI. There are several sample codes avlbl in the web. You have to tailor them to suit the IFCID in question.

I may not remember correctly, I dont try to remember which I can easily look up in the manual. I think you need 150 because, IFCID 150 is the one that contains both the holder and waiter information. (may be i am wrong check the macros) IFCID 172 is probably a locking detail record. [...] 1396 126 51_FW: Concurrent Update between SELECT and SUBSELECT?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 15 Mar 2004 12:55:46 -0500614_iso-8859-1 Victor,

If you're not convinced of the significant difference in concurrency improvement using cursor for update versus singleton update, you should read the SQL & Appl Pgming Guide. There's a section on Planning for Concurrency.

In a nutshell, it's documented there that

1 a singleton select holds no lasting lock. It gets an S(hared) lock only momentarily. 2 a cursor with the "for update of" clause gets you an U(pdate) lock upon Fetch which is held until either the Update where current of is issued or other Fetches are issued to move the cursor off the page. If you move [...] 1523 99 21_Re: DB2 SORT question14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 15 Mar 2004 19:19:08 +0100 1623 56 22_Re: Dataspace Question13_Tom Duerbusch26_DuerbuschT@STLOUISCITY.COM31_Mon, 15 Mar 2004 12:57:32 -0600474_US-ASCII Sorry, I wasn't talking DB2 z/OS. The author didn't say, and the message looked like it came from a different listserv for another platform.

Tom Duerbusch THD Consulting

>>> venkat_lserv@HEWITTANDLARSEN.COM 03/13/04 01:03PM >>> For curiosity sake, is it in DB2 z/OS or ?......

Can you please show the command to release storage from dataspace to the virtual pool?. what happens to the pages that were in dataspace?. Are they written out?. [...] 1680 31 12_RLF Question9_Dan Lamas22_dlamas@DUKE-ENERGY.COM31_Mon, 15 Mar 2004 13:24:57 -0600424_US-ASCII Hello Listers,

We have been struggling with developers running bad queries (using control center). They decide to kill there control center session but this did not break the thread to DB2. It was still there consuming resources and CPU time. The display of the databases did not show that it was hold any locks on any tables but still showed up in DB2PM as an active thread and eating up class 2 time. [...] 1712 116 42_Re: What if segmented TS is over 64Gbytes?0_16_khampto1@TXU.COM31_Mon, 15 Mar 2004 13:26:44 -0600598_us-ascii Hello Do, Here we are running Ondemand/390 with DB2 V6.1, it uses the 32K tablespaces just the same as ImagePlus (we used to have ImagePlus also). Several years ago, we partitioned our 32K tablespaces so we could exceed 64 Gig, and OAM and Ondemand handle it just fine. Our DBA partitioned them based on a portion of the date embedded in the OAM object name, giving us 48 partitions with about 8 days' worth of data in each one. CREATE TYPE 2 UNIQUE INDEX GROUP53.OBJT32X1 ON GROUP53.OSM_32K_OBJ_TBL (OTNAME ASC, OTCLID ASC, OTSEG ASC) CLUSTER ( PART 1 VALUES('TXUEBI.D0108'), PART 2 [...] 1829 82 16_Re: RLF Question0_16_FRUSA@BCBSIL.COM31_Mon, 15 Mar 2004 13:49:44 -0600494_us-ascii DB2 connect ? What version ? I think version 7.2 fix pak 6 got us out of this game you describe. We also had to take away control center for those who were ' out of control' and only gave them run time client of DB2connect.





"Dan Lamas" cc: Sent by: "DB2 Subject: RLF Question Data Base Discussion List" 03/15/2004 01:24 PM Please respond to "DB2 Database Discussion list at IDUG" [...] 1912 30 21_Re: DB2 SORT question0_16_mscarpa@CESVE.IT31_Mon, 15 Mar 2004 21:16:16 +0100362_US-ASCII Hello Peter

Thank you for your reply. I was (almost) sure it was WAD (so I add another one to my WAD collection) as SORT call is somewhat 'assembled' in access path and triggered even if there's no rows. But I hoped that with new versions (you said since early '90....)it'd be possible to execute a 'count check' in some manner to avoid it. [...] 1943 57 55_Re: FW: Concurrent Update between SELECT and SUBSELECT?10_Victor Gil18_Victor_Gil@ADP.COM31_Mon, 15 Mar 2004 14:20:59 -0600355_- Dave,

I was just asking for an EXPLANATION as I suspect the performance difference is tightly related to the difference in locking sequence.

And where did I complain about DB2 not working as DOCUMENTED?

All I was saying was that the S+X lock sequence in a "singleton UPDATE with a subselect" has a huge exposure for deadlocks. [...] 2001 60 51_Re: Concurrent Update between SELECT and SUBSELECT?10_Victor Gil18_Victor_Gil@ADP.COM31_Mon, 15 Mar 2004 14:25:01 -0600592_- Isaac,

I am not questioning your measurments. What do you think attributes to the difference [if not the extra CPU spent on the locking]?

-Victor-

On Mon, 15 Mar 2004 18:49:46 +0200, Isaac Yassin wrote:

>Hi, > >I did those measurements on many versions, always the same results. >The last ones are for V7. The same environment all the time. >I don't have the drill down here :-( so I can't comment on the locking. > >It's very easy to conduct it on your own table. > >Isaac Yassin >IBM Certified solution expert > DB2 V7 for OS/390 & [...] 2062 24 26_Re: DB2 pre-compile errors9_John Krew21_john_krew@HOTMAIL.COM31_Mon, 15 Mar 2004 22:34:20 +0200507_iso-8859-1 James Campbell" wrote:

> You wrote "program" (singular), but the quoted message said > "programs" (plural). Are you trying to pre-compile the two programs > in a single step?

Yes, and that was my mistake. After separating them, the pre-compile worked! (By the way, the compile step is quite capable of processing the two programs in a single step (provided the "END PROGRAM XXXXXXXX." statement is used). Only the pre-compile step is "challenged".) [...] 2087 130 16_Re: RLF Question13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Mon, 15 Mar 2004 15:16:02 -0600485_iso-8859-1 Set DB2 Connect (dcs directory) to enable interrupt : DCS parameters = ,D,INTERRUPT_ENABLED,,,,,

Also allow host threads to go inactive (zparm), with reasonable idle thread timeout zparm.

See archives for more info.

Dave



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of FRUSA@BCBSIL.COM Sent: Monday, March 15, 2004 1:50 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: RLF Question [...] 2218 194 29_Database Partition Question ?12_Liao, Kexiao16_liao1k@CMICH.EDU31_Mon, 15 Mar 2004 17:21:17 -0500376_US-ASCII Hi All,

Just a basic question about partitioned database: For a Database Managed Tablespace, there are 64GB limit for the tablespce size, so if we need to store some large volume data in only one table (for some special case) and that table is resided in one tablespace managed by database system, then tablespace will prevent us to store such huge data. [...] 2413 119 33_Re: Database Partition Question ?15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Mon, 15 Mar 2004 17:27:17 -0500547_us-ascii What platform (OS/390-z/OS, AIX, NT, etc.) and version are you running? In OS/390-z/OS, 64MB is only a limit if the tablespace is neither partitioned nor defined LARGE. I can't speak for other platforms.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











"Liao, Kexiao" Sent by: DB2 Data Base Discussion List 03/15/2004 05:21 PM Please respond to DB2 Database Discussion list at IDUG [...] 2533 36 43_DB2 7.1 Windows 2000 accessing ZOS DB2 V7.111_Kathy Jones19_JonesKS@GW.CCSD.NET31_Mon, 15 Mar 2004 14:44:36 -0800429_US-ASCII Our system programmer has set up DB2 V7.1 to work with DB2 Connect on a PC. When they try to do the binds in the Client Configuration Assistant to attach to the mainframe, they select Bind DB2 Utilities then CLI/ODBC Support to be bound.

We then get the bind is in progree and get "LED' does not ahve the privilige to perform operation 'CREATE IN' on object 'CCSDTSN.NULLID.SQLABD01. No package was created. [...] 2570 62 42_Re: What if segmented TS is over 64Gbytes?12_Cianci, Nick19_nick.cianci@EDS.COM31_Tue, 16 Mar 2004 10:03:03 +1100430_- Partition it! DB2 will. not go past 64G on Segmented.

If you are in immediate trouble, try COMPRESS YES & REORG. If you are already compressed do some trail compression to another TableSpace to see if a ReOrg will buy some extra space savings. Set FreeSpace & Pctfree to 0 when doing your TEST runs. I would probably keep Pct Free to 0 as well when doing the live run to try to pack as much into a page as possible. [...] 2633 65 47_Re: DB2 7.1 Windows 2000 accessing ZOS DB2 V7.113_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Mon, 15 Mar 2004 17:04:51 -0600523_iso-8859-1 Kathy,

You may want to issue the binds from the command prompt instead of db2cca. For example:

C:\SQLLIB\bnd>db2 bind @ddcsmvs.lst owner LED release commit isolation CS

where LED has been granted packadm on collection NULLID



Dave

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Kathy Jones Sent: Monday, March 15, 2004 4:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 7.1 Windows 2000 accessing ZOS DB2 V7.1 [...] 2699 19 14_Re: DSNJU999 ?12_Billy Larsen19_billarsen@YAHOO.COM31_Mon, 15 Mar 2004 17:37:24 -0600708_- Daniel , You don't need this program to update the BSDS , just Repro it and you will find easily your way .. I'm curious about this, can you give me the reference of the presentation ?

Regards

B.Larsen Kravitz &Co -Norway

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2719 20 23_thread and MVS dispatch20_desiz db2 consultant20_desig@CONSULTANT.COM31_Mon, 15 Mar 2004 22:19:58 -0500 2740 147 31_Re: Reading IFCID for Deadlocks16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Mon, 15 Mar 2004 21:05:42 -0800313_us-ascii Some more confusion on this (from my end, as always). While looking at Command reference, it seems that IFCID 172 (for deadlocks) appears in the following two types of traces: 1. Statistics class 3. 2. Performance class 6.

Why does an IFCID (172 in this case) appear in more than once class? [...] 2888 54 20_DB2 SP229 key 7 RSTE11_Martha Hall16_martha@BAYSS.COM31_Tue, 16 Mar 2004 13:41:36 +0800303_iso-8859-1 We are running DB2 V7 at 0310 level. The other day we had an 878-10 in which private>16m was depleted. The majority of the storage was in this subpool and key. I have displayed some of the storage and see eyecatchers for RSTE. Does anyone know what this is and where I find doc on this? [...] 2943 48 31_Re: Reading IFCID for Deadlocks17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 00:23:12 -0600329_- Racquel, Don't you see a msg, with something like, one holder of resource .....just above or below the dsnt500? Anytime you enter into a conflict, you should see both the correlation and plan name posted. Deadlock is hard to simulate. Can you post a few lines below / above the msg u sent.It should be ided by dsnt375?... [...] 2992 25 27_Re: thread and MVS dispatch17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 00:34:19 -0600361_- What type of workload is this?. What are the accounted class-1, class-2 and class-3 times. What is the value for class-2 elapsed - class-2 cpu?. What is the percent of class-2 cpu time in classs-2 elapsed - total class-3 wait?. A first suspect in dispatching issues is the unaccounted time?. What is the value for class-2 elapsed - class-2 cpu -class-3. [...] 3018 18 11_Table usage12_Mitesh Nagar18_MiteshN@NEDCOR.COM31_Tue, 16 Mar 2004 02:03:06 -0600283_- Hi, Can anyone suggest the easiest method to identify if a table is being used by users?

I know you can check if the amount of data in a table has changed, but what if the data is static and we would like to know if anyone referenced the table in the last month or so. [...] 3037 50 15_Re: Table usage16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 16 Mar 2004 02:19:26 -0600454_iso-8859-1 You can turn on AUDIT for the table and use SMF to report. This is pretty expensive, but if its not used, the costs are of course close to zero.

Kind regards Steen Rasmussen Computer Associates Senior Consultant



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Mitesh Nagar Sent: Tuesday, March 16, 2004 9:03 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Table usage [...] 3088 110 31_Re: Reading IFCID for Deadlocks16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 16 Mar 2004 00:49:06 -0800508_us-ascii Ok, got Omegamon deadlock to work. But deadlock analysis didn't get any easier. Following is the entire printout of Omegamon deadlock report for a particular deadlock instance:

HPLN

+ Thread: Plan=PLAN1 Connid=CICS111 Corrid=POOLTRN10036 Authid=TRN1 + Attach: CICS DB2=DSNP MVS=3090

+ Time : Start=03/15/2004 15:11:43.519256 End=03/15/2004 15:11:55.783628 wait

+ Lock Contention (Deadlock)

+

+ Time of Deadlock = 15:11:55.730 Deadlock Interval = 79907 [...] 3199 111 31_Re: Reading IFCID for Deadlocks16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 16 Mar 2004 00:50:16 -0800560_us-ascii "Two different pages or rows cannot deadlock with each other."

Venkat, I must not be hearing you correct. Deadlock is (almost) ALWAYS between two different pages or rows (of same or different tables or two rows of the same page in case of row level locking).

As I have stated below, between the 'two' resources involved, MSTR gives 'one' of the resources (Venkat, note that I am not talking about the two 'processes' here; MSTR does give me the two processes that deadlocked against each other). Just can't find the other resource. [...] 3311 17 31_Re: Reading IFCID for Deadlocks2_da15_bufes@YAHOO.COM31_Tue, 16 Mar 2004 03:14:41 -0600277_- Raquel, it seems that there is no information on the 2nd object in your deadlock report. We get a lot of deadlocks, but I have never seen this happening. Try to contact your software vendor to see if it is something that could be fixed in Omegamon. Best regards, Daniel [...] 3329 41 34_impact of NUMLKTS when LOCKMAX=0??11_Samit Goyal19_samgoyal@IN.IBM.COM31_Tue, 16 Mar 2004 01:23:42 -0800431_US-ASCII Hi All,

One of our user faced locking problem while saving data (through SAVE DATA command in QMF) in a tablespace that has SEGSIZE=0, LOCKSIZE=PAGE,LOCKMAX=0.

the tablespace contain many tables and looks like that a lock escalation happened and the entire tablespace got locked when user issued SAVE DATA command (which internally creates table and inserts the records selected by a particular query). [...] 3371 81 38_Re: impact of NUMLKTS when LOCKMAX=0??11_David Nance16_dwnance@FHSC.COM31_Tue, 16 Mar 2004 07:43:58 -0500586_US-ASCII Samit, Any create table is going to need exclusive use of the tablespace and database. The way to avoid having this issue in the future is to give users their own individual tablespace or just learn to live with it. The account I'm with right now had individual tablespaces when I first got here. We have since done away with that. Reason being, everytime you get someone new, you would have to create another tablespace and grant permissions to the user. Then what about when people leave the company? DBA's aren't typically notified that developer Joe Blow has moved on [...] 3453 17 20_Re: Lock escalation.33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 16 Mar 2004 07:38:30 -0600354_- I think, you must look for the mystery process C.

From your numbers, even if every page, the row has to be inserted according to the clustering index is locked, then you will come up with 500 pages. But lock escalation occurs when 1000 locks are held.

Are you sure, that process A gets the -913 on the ressource, it is inserting in? [...] 3471 126 24_Re: DB2 SP229 key 7 RSTE12_michael bell21_mbell11a1@VERIZON.NET31_Tue, 16 Mar 2004 07:59:05 -0600396_iso-8859-1 I hope you realize that normal MVS rules are to allocate memory in extended private until the max available has been used and then allocate in low private. DB2 address spaces should run with region=0M unless you have very good usage tracking so you never run out of extended CSA or extended private. This is a symptom of a DB2 region that has a too small region parm on the proc. [...] 3598 160 47_Re: Real time statistics vs runstats statistics0_26_mark.walker@CUNAMUTUAL.COM31_Tue, 16 Mar 2004 07:57:44 -0600333_US-ASCII Hi Dirk,

Thanks for the information. We're going to go with these and IBM's recommendations in the documentation. I'm still kind of uncomfortable giving up the idea of clusterratio so once we get this new process implemented, we'll watch our clusterratio's after we do reorgs and see if we are missing anything. [...] 3759 54 47_Re: DB2 7.1 Windows 2000 accessing ZOS DB2 V7.115_McCardle, Corey23_CMcCardle@AMERISURE.COM31_Tue, 16 Mar 2004 09:30:24 -0500402_- Good Morning Kathy,

The Id that you are using to bind to OS390 from Windows needs to be authorized to Bind and Create packages on OS390 (CCSDTSN).

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Kathy Jones Sent: Monday, March 15, 2004 5:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 7.1 Windows 2000 accessing ZOS DB2 V7.1 [...] 3814 82 38_Re: impact of NUMLKTS when LOCKMAX=0??19_Bhattacharya, Ujjal27_ujjal.bhattacharya@CGEY.COM31_Tue, 16 Mar 2004 22:33:36 +0800317_iso-8859-1 Hi Samit, You can as well give a Display command to check whether anyone is using the locks or not. -Dis Database(Dbname) Space(Tsname) USE LOCKS will tell you whether anyone is using it or not.You can as well ask those Users to use Isolation Level UR if the query is just reports or inquiry. Thanks. [...] 3897 71 38_Re: impact of NUMLKTS when LOCKMAX=0??23_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM31_Tue, 16 Mar 2004 08:43:13 -0600517_iso-8859-1 Please take alok at this Info APAR II11903.

HTH

Juan Pablo Tello Mexico City.







-----Mensaje original----- De: Samit Goyal [mailto:samgoyal@IN.IBM.COM] Enviado el: Martes, 16 de Marzo de 2004 03:24 a.m. Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: impact of NUMLKTS when LOCKMAX=0??



Hi All,

One of our user faced locking problem while saving data (through SAVE DATA command in QMF) in a tablespace that has SEGSIZE=0, LOCKSIZE=PAGE,LOCKMAX=0. [...] 3969 400 33_Re: Database Partition Question ?18_Prather, Doug, ISD28_Dprather467@WORLDSAVINGS.COM31_Tue, 16 Mar 2004 09:15:19 -0600739_us-ascii Phil, at what point (size) would you recommend going to Partitioning instead of using a Segmented Tablespace?





Doug Prather

DB2/DBA

World Savings

4101 Wiseman

San Antonio, TX 78251

210-543-6742





-----Original Message----- From: Philip Sevetson [mailto:Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM] Sent: Monday, March 15, 2004 4:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Database Partition Question ?







What platform (OS/390-z/OS, AIX, NT, etc.) and version are you running? In OS/390-z/OS, 64MB is only a limit if the tablespace is neither partitioned nor defined LARGE. I can't speak for other platforms. [...] 4370 34 31_Re: Reading IFCID for Deadlocks17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 09:24:13 -0600367_- Racquel, I am sorry, it is not posted because the process that was successful is considered a contention winner.

Again, Two pages never deadlock. Two threads only deadlock. There may a terminology difference and you are expecting something that is not there. The locks acquired by various processes on two different pages cause a deadlocking situation. [...] 4405 30 24_Re: db2l documents??????0_24_db2_dba@STANDARDLIFE.COM31_Tue, 16 Mar 2004 15:18:49 +0000680_us-ascii Hi, I've just tried to retrieve a document but have received the same error as described in the previous posts. Is the document archive available.

Thanks, Keith





For more information on Standard Life, visit our website http://www.standardlife.com/ ; The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No. SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent and please return it to [...] 4436 19 42_Re: What if segmented TS is over 64Gbytes?7_Do Park19_doparkdba@YAHOO.COM31_Tue, 16 Mar 2004 09:40:42 -0600667_- Hello

Thank you very much for your update.

I'd like to open another subject regarding OAM 32k partitioned tablespace. Would you please take a look at that?

Best Regards, Do.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4456 114 35_Looking for out-of-print IBM Manual16_Riffe, Jim@HHSDC19_JRiffe@HHSDC.CA.GOV31_Tue, 16 Mar 2004 07:43:45 -0800376_- Excuse me if this note appears twice, the last time I sent it MS Outlook locked up my machine.

I'm trying to locate a copy of the manual listed below. If anyone happens to have one laying around, please shoot me a note at jriffe@hhsdc.ca.gov . If you are not willing to part with it, I would be willing to return it after I read it. [...] 4571 31 97_Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)11_Sinha, Amit19_sinha.amit@MBCO.COM31_Tue, 16 Mar 2004 09:45:55 -0600384_iso-8859-1 Hi,

We have following Accounting trace activated for our DB2 7.1 ZOS :

TNO TYPE CLASS DEST QUAL 02 ACCTG 01,02,03 SMF NO 05 ACCTG 01,02,03,07, OP1 NO

I am trying to find number of select statement executed by various authid from our omegamon tables where we store our trace data. I am finding that the column sql_select as 0 value for the authid. [...] 4603 42 39_Re: Looking for out-of-print IBM Manual11_Mark Ediger22_MEDIGER@TRANSUNION.COM31_Tue, 16 Mar 2004 09:57:45 -0600508_US-ASCII Post this on IBM MAIN-L. Those guys keep everything.





>>> JRiffe@HHSDC.CA.GOV 3/16/2004 9:43:45 AM >>> Excuse me if this note appears twice, the last time I sent it MS Outlook locked up my machine.

I'm trying to locate a copy of the manual listed below. If anyone happens to have one laying around, please shoot me a note at jriffe@hhsdc.ca.gov . If you are not willing to part with it, I would be willing to return it after I read it. [...] 4646 76 47_Re: DB2 7.1 Windows 2000 accessing ZOS DB2 V7.111_Kathy Jones19_JonesKS@GW.CCSD.NET31_Tue, 16 Mar 2004 08:07:04 -0800421_US-ASCII thanks to all that answered - indeed I gave him packadm to collection NULLID and now he is accessing the mainframe from his pc = since this is our first attempt at that we are all excited here

>>> CMcCardle@AMERISURE.COM 3/16/2004 6:30:24 AM >>> Good Morning Kathy,

The Id that you are using to bind to OS390 from Windows needs to be authorized to Bind and Create packages on OS390 (CCSDTSN). [...] 4723 93 18_Scrollable cursors16_Michael Liberman19_michaelli@BLL.CO.IL31_Tue, 16 Mar 2004 18:09:36 +0200757_us-ascii Hi all,

Can anyone suggest some more reasons why one should not use scrollable cursors. besides of course the temporary table and locking overhead.



Thnx,





Michael Liberman DBA Team Leader HERMESH project 927 54 685756





--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4817 85 101_Re: Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 16 Mar 2004 16:21:14 +0000436_US-ASCII I'm not sure I can answer your question but:

In Statistics/Accounting Trace SQL Statistics section (QXST) the count for Selects is only Singleton Selects. If your application (eg JDBC) doesn't do those then this count will be zero. I have seen this lots of times.

But that mightn't be your problem. Having said that your trace classes look entirely reasonable to me to capture QXST data in Accounting Trace. [...] 4903 148 60_Re: Database Partition Question ? (AND Partitioning by Week)15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 16 Mar 2004 11:40:02 -0500406_us-ascii Umm, "It Depends." (Pause for audience to throw tomatoes.)

Seriously, the more powerful your CPU cluster and the more tolerant your system is of outages, the more size you can tolerate without partitioning. You need to be able to complete your utilities within the "low-activity" window (formerly the batch window), with a generous safety buffer of time left over for troubleshooting. [...] 5052 84 39_Re: Looking for out-of-print IBM Manual15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 16 Mar 2004 11:42:04 -0500701_us-ascii You should put this on the MVS-L mailing list.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











"Riffe, Jim@HHSDC"




To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: [DB2-L] Looking for out-of-print IBM Manual

Excuse me if this note appears twice, the last time I sent it MS Outlook locked up my machine.

I'm trying to locate a copy of the manual listed below. If anyone happens to have one laying around, please shoot me a note at jriffe@hhsdc.ca.gov. If you are not willing to part with it, I would be willing to return it after I read it. [...] 5137 12 101_Re: Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 11:17:31 -0600520_- You need statistics classes on for that. Venkat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 5150 221 27_Use of Tape/Cart for reorgs10_Jacob Wych18_Jacob.Wych@RRD.COM31_Tue, 16 Mar 2004 11:14:04 -0600537_- This is probably an easy question, but I don't have the solution.

I would like to do a reorg using a list of tablespaces to reorg in a single step. I also want to use Tape/Cart for the SYSUT1 file. I have tried allocating the file multiple ways (OLD or MOD or NEW). The first reorg completes successfully, but the second fails on the allocation the SYSUT1. If I allocate DASD datasets, it works fine. Is there something that needs to be done to use Tape/Cart or is the only alternative to do each reorg as a separate step? [...] 5372 325 31_Re: Use of Tape/Cart for reorgs13_Fung, Chi-Yun23_Chi-Yun.Fung@UNISYS.COM31_Tue, 16 Mar 2004 10:34:27 -0700465_iso-8859-1 May be you want to try to use TEMPLATE instead of hard coded SYSREC, SYSUT1 etc so each object can have its own set of work datasets. . I don't think you can reuse tape or MOD to tape.

Hope this helps.

Angela

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Jacob Wych Sent: Tuesday, March 16, 2004 12:14 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Use of Tape/Cart for reorgs [...] 5698 465 31_Re: Use of Tape/Cart for reorgs15_Trbovic, Philip25_Philip.Trbovic@SHOPKO.COM31_Tue, 16 Mar 2004 11:40:19 -0600638_us-ascii Jacob,

You need to do a REFERBACK to each of you DD statements. I have combined multiple DD statements in my REORGS using TAPE/CART.

Look it up in a JCL book. That should resolve your issue.



Phil Trbovic

Sr. Database Architect

ShopKo Inc.

Phone: 920-429-4645

FAX: 920-429-4260

www.shopko.com













-----Original Message----- From: Fung, Chi-Yun [mailto:Chi-Yun.Fung@UNISYS.COM] Sent: Tuesday, March 16, 2004 11:34 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Use of Tape/Cart for reorgs [...] 6164 230 20_DB2 V8 withdrawal???12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 16 Mar 2004 19:41:25 +02001137_us-ascii Hi,





Can anyone make head nor tail of the following announcement -





http://www306.ibm.com/common/ssi/OIX.wss?DocURL=http://d03xhttpcl001g.boulder.ib

m.com/common/ssi/rep_ca/3/897/ENUS904-043/index.html&InfoType=AN&InfoSubType=CA&

InfoDesc=Announcement+Letters&panelurl=OIX.wss%3Fbuttonpressed%3DNAV002PT090&pan

eltext=Announcements





(Take the whole address)









It seems to be announcing withdrawal of some of the DB2 V8 features that were announced previously to be available on the same date: 26th Mar 2004.





Isaac Yassin

IBM Certified solution expert

DB2 V7 for OS/390 & Z/OS









--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached [...] 6395 173 31_Re: Use of Tape/Cart for reorgs12_Michael Darr21_mdarr@COOPERPOWER.COM31_Tue, 16 Mar 2004 11:48:14 -0600622_US-ASCII Hi, Jake...

Have you checked to see if your tape management system is not allowing you to open for output the dataset on a volume that it thinks is no longer in scratch status? Just a thought...

Michael Darr Data Base Administrator mdarr@cooperpower.com 262-524-3379



>>> Jacob.Wych@RRD.COM 3/16/2004 11:14:04 AM >>>

This is probably an easy question, but I don't have the solution.I would like to do a reorg using a list of tablespaces to reorg in a single step. I also want to use Tape/Cart for the SYSUT1 file. I have tried allocating the file multiple ways (OLD or MOD [...] 6569 306 60_Re: Database Partition Question ? (AND Partitioning by Week)12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 16 Mar 2004 19:50:27 +0200391_us-ascii Hi,

I have a 16GB segmented table + 7 indexes (all in all - 20 3390-3 disks).

Compress yes (72%)

The only problem is REORG (disk space, time, .), COPY is done for indexes as well.

Making it partitioned will incur 6 NPI (still problem for REORG).

Looking for V8 to partition it (however - looks like V8 is moving away with the new withdrawal) [...] 6876 141 24_Re: DB2 V8 withdrawal???33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 16 Mar 2004 18:57:33 +0100910_iso-8859-1 QPP Quality Partnership Program

End of ESP

Roland



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Isaac Yassin Sent: Tuesday, March 16, 2004 6:41 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 V8 withdrawal???





Hi,





Can anyone make head nor tail of the following announcement -







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 7018 347 24_Re: DB2 V8 withdrawal???16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Tue, 16 Mar 2004 10:04:43 -0800456_iso-8859-1 Forward of a response to this question in IBM-MAIN.



-----Original Message----- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@BAMA.UA.EDU] On Behalf Of Tom Schmidt Sent: Tuesday, March 16, 2004 7:34 AM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: funny DB2 announcement

Tom Schmidt < Tom.Schmidt@OASSOFTWARE.COM> wrote: It only means that IBM is withdrawing the QPP for DB2 V8 on the same day that it becomes GA. [...] 7366 64 61_Change OAM 32k tablespace from segmented TS to Partitioned TS7_Do Park19_doparkdba@YAHOO.COM31_Tue, 16 Mar 2004 12:33:56 -0600342_- Hello

We use Imageplus v3.1, CICS TS v1.3, and DB2 V7. Segemented tablespaces are used for Imageplus. The total size of 32k tablespace is almost 64Gbytes. We are going to change 32k tablespace from segmented tablespace to partitioned tablespace. I searched this question in the DB2 Listserv, but I could not find solid answers. [...] 7431 175 60_Re: Database Partition Question ? (AND Partitioning by Week)15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 16 Mar 2004 13:31:46 -0500562_iso-8859-1 Isaac's right, and I forgot to mention -- when your table gets really big (somewhere between 10GB and 100GB), you start *really* feeling the pain of those extra indexes. The most NPIs we have on our Data warehouse fact tables is 2, and the second NPI leads to some really long load times (in the BUILD2 phase). IBM has given us all the help they can -- unfortunately, we were already using most of the measures which they suggested -- and we're looking at a third-party load tool to see whether it can give us the additional performance we need. [...] 7607 247 24_Re: DB2 V8 withdrawal???19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 16 Mar 2004 12:56:43 -0600473_us-ascii

I'm glad you could tell us this.... The IBM announcement is worthless (and utterly confusing) without this information. (see http://www.ibm.com/common/ssi/fcgi-bin/ssialias?subtype=ca&infotype=an&a ppname=iSource&supplier=897&letternum=ENUS904-043 )

I hope someone in IBM has enough clout to get the original announcement clarified without scaring the customer. Especially when the phrase "no replacement product" was used in the announcement. [...] 7855 258 24_Re: DB2 V8 withdrawal???14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Tue, 16 Mar 2004 14:09:54 -0500391_us-ascii I think this announcement is the QPP is being withdrawn. This makes sence to me. A QPP is a limited, early release version for customer input on quality.

notice QPP is included in all the detailed program elements.



Effective March 26, 2004, IBM will withdraw from marketing the following program supply features licensed under the IBM Customer Agreement: [...] 8114 285 24_Re: DB2 V8 withdrawal???19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 16 Mar 2004 14:30:02 -0600658_us-ascii

If this announcement makes sense to you, it only means you are familiar with the acronym QPP.

Since announcement letters go to a broad audience, this becomes a VERY poor announcement in my opinion. Especially when it includes the ending line: Replacement product information: None

Announcements are supposed to be informative, not cryptic. If someone as knowledgeable as Issac Yassin is questioning the announcement (and I'm not a novice myself) then I'm forced to conclude many others are reading the same letter and getting mightily confused right now. Especially those who are not aware of the usefulness of this forum [...] 8400 37 19_to many workfiles ?10_Tom Taylor17_ttaylor@CHUBB.COM31_Tue, 16 Mar 2004 16:07:20 -0500585_US-ASCII Hi all

Is there any disadvantage to having TO MANY workfiles ( dsndb07) defined



Tom

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 8438 62 23_Re: to many workfiles ?0_16_FRUSA@BCBSIL.COM31_Tue, 16 Mar 2004 15:12:57 -0600489_us-ascii Runaway queries might eat up all that space before they die and you might have wanted them to die earlier on a smaller set of work files - may give false hope to the customer and they wait longer before a failure...





"Tom Taylor" cc: Sent by: "DB2 Subject: to many workfiles ? Data Base Discussion List" 03/16/2004 03:07 PM Please respond to "DB2 Database Discussion list at IDUG" [...] 8501 229 24_Re: DB2 V8 withdrawal???14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Tue, 16 Mar 2004 16:25:04 -0500537_us-ascii I agree the announcement is poorly worded as evidenced by the discussion about what I means. I have worked on QPP efforts with IBM in the past and that is why I am guessing that this notice reports the withdrawal of the QPP. I thought it was important to quite the potential for panic. Ofcourse the use of QPP in the announcement could refer to something totally diffrent than what I suggest ... We all need to wait for the official word. In the mean time I suggest not losing sleep over it and if you are a betting person [...] 8731 108 23_Re: to many workfiles ?14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Tue, 16 Mar 2004 16:34:30 -0500355_us-ascii I think there are two advantages to many smaller workfiles vs fewer bigger ones. 1. Better (more) volume seperation. Work files are one of the common DB2 datasets so they might be used by almost every DB2 thread and be quite busy. 2. Each work file is its own table space and therefore could potencially have its own write engine when busy. [...] 8840 86 31_Re: Reading IFCID for Deadlocks19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 16 Mar 2004 15:33:35 -0600551_us-ascii Hi Raquel,

This is definitely a Omegamon problem. Although deadlocking most often occurs at the row or page level there are many different things that could be deadlocked on. Look at hilevel.SDSNMACS(DSNDQW02) macro for the QW0172 record on your sub-system. Probably Omegamon could be looking at this flag and tell you the proper resource. If they can't, (and Omegamon gets more insider details from IBM then we do) then they should at least tell you the value of QW0172FR and QW0172PN so you could at least talk to IBM about it. [...] 8927 33 23_Re: too many workfiles?13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 16 Mar 2004 22:39:54 +0100571_us-ascii Hi Tom

in a performance seminar I attended a long time ago, I learned that there are good reasons to have many (even hundreds) of work files - especially if you are using parallelism in queries (DEGREE ANY); e.g. in a datawarehouse environment with partitioned TSs. I think that is because workfiles cannot be shared - only one thread can use a workfile at a time. Of course one thread can still allocate several workfiles. So it would be preferable to divide a given space quantity into many small files rather than few large ones - but only if you [...] 8961 42 59_Successive data row access from a duplicate index rid chain14_Andrew Meddick27_andrew.meddick@MARRIOTT.COM31_Tue, 16 Mar 2004 15:59:06 -0600381_- I have an embarassingly basic question regarding the way DB2 z/OS V7 physically positions subsequent reads for index plus tablespace data access. Assuming multiple index rid chain entries per each discrete index key entry on a non-unique index (not the clustered index), how does DB2 find the tablespace pages for multiple accesses from each discrete index key's RID chain? [...] 9004 84 23_Re: too many workfiles?35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 16 Mar 2004 17:05:22 -0500515_iso-8859-1 Dr. Ebert,

As`far as I remember, physical workfiles can be shared by multiple users, concurrently. DB2 breaks the physical workfiles into logical workfiles, and spreads the logicals around the physicals. Take the simple case of (as many places do) having a relatively small number of physicals, like 6-10, and having several dozen qmf users running, plus online transactions, batch jobs, and many of them sorting data.... If a physical could not be 'shared' you wouldn't get much throughput. [...] 9089 271 24_Re: DB2 V8 withdrawal???15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Tue, 16 Mar 2004 16:19:41 -0600625_iso-8859-1 Tom happens to work here. He says that he is confused as to why IBM even posted that announcement since participating in a QPP is by invitation only.

Linda Billings Database Administrator State of Wisconsin Department of Administration Division of Enterprise Technology Bureau of Development and Operations



"Y'all have some people who might be indicted? I'm so happy for you!...Why, in Texas, five speakers of the House in a row were indicted. Then we had one who was shot to death by his wife. Now, of course, she wasn't indicted -- because in Texas we recognize public service when we [...] 9361 97 23_Re: too many workfiles?15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Wed, 17 Mar 2004 09:32:08 +1100357_us-ascii Last time this was my problem I used "guarranteed space" to put a workfile

on every volume I had - not that I had all that many volumes. I worked out

how much space I wanted, divided this by the number of volumes and allocated each workfile as that size, with no secondary. That was on an RVA and I wanted to minimise IOSQ time. [...] 9459 44 24_Re: db2l documents??????14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 17 Mar 2004 08:33:37 +1000611_US-ASCII I asked the document list owners about this a month or two ago and was told that they were still transferring the attachements.

looks like they are still at it.

James Campbell

On 16 Mar 2004 at 15:18, db2_dba@STANDARDLIFE.COM wrote:

> Hi, > I've just tried to retrieve a document but have received the same > error as described in the previous posts. Is the document archive > available. > > Thanks, > Keith > > > > For more information on Standard Life, visit our website > http://www.standardlife.com/ ; The Standard Life Assurance Company, > Standard Life House, 30 [...] 9504 55 15_Re: Table usage14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 17 Mar 2004 08:39:28 +1000357_US-ASCII It might be expensive in absolute terms, but it will still be cheaper than the alternatives.

Unfortunately for Mitesh, it only works for the future: there is no way of knowing who has referenced a table in the past (update can be found by examining the log).

James Campbell

On 16 Mar 2004 at 2:19, Rasmussen, Steen wrote: [...] 9560 101 63_Re: Successive data row access from a duplicate index rid chain12_michael bell21_mbell11a1@VERIZON.NET31_Tue, 16 Mar 2004 16:42:55 -0600541_iso-8859-1 It is a simple answer - the RID is composed of 2 or 3 components 1. if a partitioned tablespace, the partition number is the first component of the rid. the number of bits used varies by number of partitions and DSSIZE or LARGE parms also there are 4 byte and 5 bytes rid's - see the diagnosis guide for complete details 2. the middle part is a page number - if simple or segmented tablespace the page number increments across 2GB datasets and continues counting as if it was a single dataset. 3. the last byte is the offset [...] 9662 70 15_Re: Table usage12_michael bell21_mbell11a1@VERIZON.NET31_Tue, 16 Mar 2004 16:48:21 -0600351_Windows-1252 If your MVS groups capture smf for dataset open/close (i think it is 37), you can scan for db2 open of the tablespace. If the dataset wasn't opened, then nobody referenced that tablespace. This only identifies the usage at a tablespace level and will include image copies and other utilities but it is usually available for history. [...] 9733 53 15_Re: Table usage14_Andy Lankester30_andy.lankester@CDBSOFTWARE.COM31_Tue, 16 Mar 2004 23:00:13 -0000326_US-ASCII I think there was a previous thread - have a look in the archives. Simple solutions:

Make sure that the table is in its own tablespace then either 1) Create a new bufferpool and make this tspace the only one using it. Your stats will show any activity. Or 2) Migrate the tspace and monitor for any recall [...] 9787 438 60_Re: Database Partition Question ? (AND Partitioning by Week)18_Prather, Doug, ISD28_Dprather467@WORLDSAVINGS.COM31_Tue, 16 Mar 2004 17:11:22 -0600294_us-ascii Well thank you Phil and all the rest of you experts for answering my questions! As a ROT I was thinking 2GB was the practical limit for a Segmented TS and it seems like it really is. Partitioning is the best way to go when it gets really big. NPIs apparently can kill your loads! [...] 10226 47 31_Re: Reading IFCID for Deadlocks19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 16 Mar 2004 17:10:39 -0600430_us-ascii Hi Raquel,

You might try just using FILEAID (or some such utility) against the SMF data looking for record type '66'hex records (i.e. 102 dec) records and then looking for records with the character string 'DSNPDIST.DSNPDIST.BAEBD2EF2944' (i.e. the specific LUW info you provided. Once you get that subset of records, then you could dump the records to see the actual information Omegamon is not showing you. [...] 10274 166 15_Re: Table usage15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 16 Mar 2004 18:18:58 -0500597_us-ascii You forgot, "Stop the tablespace and see who screams!" :-))

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











Andy Lankester Sent by: DB2 Data Base Discussion List 03/16/2004 06:00 PM Please respond to DB2 Database Discussion list at IDUG



To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: Re: [DB2-L] Table usage



I think there was a previous thread - have a look in the archives. Simple solutions: [...] 10441 41 31_Re: Reading IFCID for Deadlocks17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 17:44:26 -0600342_- Humphris / Rachel,

The holder and waiter were interested in the same resource. We need to differentiate the the term resource here. Resource is the page which is just one.

This is a true deadlock, in a true deadlock, the holder is interested in the page the waiter has secured and the waiter wants a page the holder has. [...] 10483 92 31_Re: Reading IFCID for Deadlocks14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 16 Mar 2004 17:46:50 -0600356_- Raquel

Think of the various classes as "reason(s) for generating this IFCID". Once generated there is no data within the ifcid record that describes why it was generated. So yes, either of these will do. Or, if you want to get just the 172's and not the others also generated in these classes, you could use PERFORMANCE CLASS(32) IFCID(172). [...] 10576 27 23_Re: to many workfiles ?17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 16 Mar 2004 19:04:56 -0600410_- There is no certain limit as to what should be the number. Ceratinly, bigger or smnaller installation, more work files are better than one large workfile. Large sorts are spread into multiple smaller logical work files by dB2. You can avoid IO contention.

Multiple threads can be allocated with to the same physical workfile. There is no constraint. This could increase the chance of contention. [...] 10604 145 21_UK DB2 Symposium 200414_Julian Stuhler27_julian.stuhler@TRITON.CO.UK31_Wed, 17 Mar 2004 01:36:44 -0000621_us-ascii

Triton Consulting is delighted to announce the UK DB2 Symposium 2004 - the first of its kind in the UK. The event is being held at the Kensington Hilton Hotel in central London on 18th and 19th May 2004. with 3 concurrent full-day seminars being held on each of the two days. Don't miss this opportunity to receive in-depth DB2 education from some of the world's most knowledgeable independent DB2 consultants. The speakers for this year's event are Bonnie Baker, Jan Henderykx, Klaas Brant, Iqbal Goralwalla and Julian Stuhler. We also delighted to have Barbara Stanley (North Region Executive for [...] 10750 93 31_Re: Reading IFCID for Deadlocks16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 17 Mar 2004 00:51:14 -0800338_us-ascii Thank you Venkat, Humphris and James for your very helpful answers.

You are correct Venkat; I think we were unnecessarily getting bogged down by terminology about deadlocking when both of us understood the same thing. I agree with your point that in MSTR, information is provided only about the 'failed' transaction. [...] 10844 21 22_Re: Scrollable cursors33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 17 Mar 2004 02:57:13 -0600309_- Hi

For me, the main reason not to use scrollable cursors in V7, is that the entire resultset is stored in a temporary table and that can be quite time consuming. And because we are using IMS DC, the resultset is gone, if the IMS output message is sent to the user, so scrolling is not possible. [...] 10866 27 13_DEFAULT CCSID17_Roberto Mandolini25_roberto.mandolini@EDS.COM31_Wed, 17 Mar 2004 03:12:05 -0600572_- Hi all, I am having a small discussion with the colleagues of the IBM support. I have read a document in which IBM is recommending the translation of the CCSID from default, that we use (500), to specific CCSID of the country of origin (italy 280). Described motives are: DB2 even if CCSID=500 to every external application coding the CCSID according to the client application, so DB2 perform a translation from 500 to XXX of a data written with codepage 280 standard and this can provoke errors. My question is this: it seems me that such problem is overcome with [...] 10894 82 63_Re: Successive data row access from a duplicate index rid chain17_MORTIMER, Dominic37_Dominic.MORTIMER@WATFORD.SEMA.SLB.COM31_Wed, 17 Mar 2004 09:15:17 +0000493_ISO-8859-1 I'd have thought using date would be easier than timestamp. If you used timestamp then the SQL would either have to be

DATE(TIMESTAMP) = CURRENT DATE - 1 DAY -- which is probably stage 2

or programmatically work out the timestamps BETWEEN midnight and one minute to midnight for today.

Although timestamp will have a higher cardinality it sounds like date would have a fairly high cardinality as well, so from a cardinality point of view both would be OK. [...] 10977 15 31_Re: Reading IFCID for Deadlocks11_Daniel Adam15_bufes@YAHOO.COM31_Wed, 17 Mar 2004 03:21:19 -0600714_- We have statistics trace class(3) turned on. On a daily basis we execute batch report on deadlocks. For every deadlock we can see which resources were involved - both of them. Raquel - can't you use an offline reporting? Best Regards, Daniel

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10993 63 23_Re: too many workfiles?0_16_mscarpa@CESVE.IT31_Wed, 17 Mar 2004 10:49:40 +0100627_US-ASCII Estimeed listers

Curious, I'm just dealing with DSNDB07 & workfile performances........

Well according M. Petras (Petras, IDUG 2001, Florence), in addition of having physical wkfile in different disks,channels etc, the number of wkfiles should be at least 5 or 1/5 of maximum number of data partitions (the higher of the two).

Apart DASD problems more wkfile is *always* better than few as DB2 SORT can allocate 1 LOGICAL wkfile per runs (see HOOVER, 2002 for details about runs) and 1 LOGICAL wkfile to 1 PHYSICAL wkfile. So if you have few physical files you could have more than 1 logical [...] 11057 141 38_Re: impact of NUMLKTS when LOCKMAX=0??11_Samit Goyal19_samgoyal@IN.IBM.COM31_Wed, 17 Mar 2004 03:56:02 -0800543_US-ASCII Hi David,

Thanks a lot for getting into the crux of problem and addressing it from right there.

Your approach of automatic clean up/dropping of the tables sounds good to me and I would see if I could implement this in my project. This will help me in cleaning up the tablespaces, but still it wont help me in resolving the locking problems. So looks like that we will have to "learn living with it" . I still prefer to have locking problems when compared to the problems with a "one table per tablespace" approach. [...] 11199 25 24_Opthint and version auto13_Johnny Mossin24_johnny.mossin@NORDEA.COM31_Wed, 17 Mar 2004 06:13:52 -0600426_- We have started to use opthints. We are precompiling with version(auto). In the program we coded queryno 1. Then we compile and bind the package explain(yes). I corrected the access path in the plan_table and made a rebind and evry thing is working fine. The next time we compile the program, we get a new package/dbrm version and the bind will NOT find my modified plan_table row and will NOT use my brillant opthint. [...] 11225 283 36_Re: Stored procedure accessing Idms.16_Proctor, William25_William.Proctor@TGSLC.ORG31_Wed, 17 Mar 2004 06:50:11 -0600413_us-ascii Several people responded and wanted to know if I was able to get this to work. You can access IDMS or other DB's using a stored procedure. The stored procedure has to be Cobol and you have to put the load lib's for your other DB in the work load manager started task. The SQL stored procedure's can only access other SQL stored Procedures. They cannot call a sub-program. All input was appreciated. [...] 11509 195 59_Could and additional WHERE-Clause impact the sql-accespath.14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 17 Mar 2004 13:52:35 +0100574_iso-8859-1

> Hi, > > We are using db2 v6.1 on z/os and Java > > In our Java-application we use, for security-reasons, a file which contains sql. This file has been parameterised, which means the programmer can add a value for certain where-clause-values. Due to the fact that he is not obliged to use this parameters, there's allways 1 added parameter "1=1 AND", to avoid an sql-syntax-error. > > For our convenience, we already wrote a where-clause in the sql-statement. > > Sample : > > SELECT FIELD1, FIELD2, FIELD3, FIELD4 > FROM TABLE1 > > WHERE 1=1 > AND [...] 11705 252 63_Re: Could and additional WHERE-Clause impact the sql-accespath.13_D'hoine Frank19_Frank.Dhoine@NBB.BE31_Wed, 17 Mar 2004 13:55:39 +0100688_iso-8859-1 This is a good question, i will look in to it,

Frank d'Hoine

-----Original Message----- From: Steurs Patrick Sent: woensdag 17 maart 2004 13:53 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Could and additional WHERE-Clause impact the sql-accespath.







Hi,

We are using db2 v6.1 on z/os and Java

In our Java-application we use, for security-reasons, a file which contains sql. This file has been parameterised, which means the programmer can add a value for certain where-clause-values. Due to the fact that he is not obliged to use this parameters, there's allways 1 added parameter "1=1 AND", to avoid an sql-syntax-error. [...] 11958 18 28_Re: Opthint and version auto33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 17 Mar 2004 06:56:37 -0600361_- Hi

We are facing similar problems and AFAIK there is no simple way to use opthint of a previous version even if you specify the statement numbers. What you can do is, generate the BIND-command and use the version from the DBRM-Member to update your opthint-rows in the plan_table. And don't forget to specify OPTHINT(opthint) in the BIND-command. [...] 11977 60 28_Re: Opthint and version auto13_Rp, Venkatesh20_venkatesh.rp@EDS.COM31_Wed, 17 Mar 2004 07:51:11 -0500325_- Hi Johnny Mossin

Once you made changes to the query 1 in the plan table. Update the column OPTHINT in the plan_table with some meaningful name of 8 characters. If you would like to use the same query for future binds, use the option OPTHINT ('MEANINGFUL NAME')in your bind card. This will resolve your problem. [...] 12038 81 28_Re: Opthint and version auto13_Johnny Mossin24_johnny.mossin@NORDEA.COM31_Wed, 17 Mar 2004 07:27:17 -0600352_- No, this is not solving my problem. When we compile the program, we get a new version of the package and when DB2 binds the package , the search in plan_table includes the version. Our suggestion only works with version='' (no version).

Johnny Mossin

On Wed, 17 Mar 2004 07:51:11 -0500, Rp, Venkatesh wrote: [...] 12120 38 51_Using LOAD with delimited data in DB2 V7.1 on S390.11_Samit Goyal19_samgoyal@IN.IBM.COM31_Wed, 17 Mar 2004 06:02:49 -0800490_US-ASCII Hi all,

Can anyone please tell if the LOAD utility for DB2 V7.1 for z/OS supports delimited data.

If it supports, then how..? what is the syntax? and if not, then don't you think it is a basic feature which should have been incorporated long back?

I have to setup job for loading a tab-delimited file in DB2 table. And the last thing I want to do is to write code to first re-format the data in Fixed Width format so that it becomes compatible with LOAD. [...] 12159 141 55_Re: Using LOAD with delimited data in DB2 V7.1 on S390.15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Wed, 17 Mar 2004 09:18:10 -0500476_us-ascii Read the delimited data into a UDB/NT, FoxPro, or Access database, then write it out as fixed length. It isn't pretty, but it works.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











Samit Goyal Sent by: DB2 Data Base Discussion List 03/17/2004 09:02 AM Please respond to DB2 Database Discussion list at IDUG [...] 12301 21 63_Re: Successive data row access from a duplicate index rid chain14_Andrew Meddick27_andrew.meddick@MARRIOTT.COM31_Wed, 17 Mar 2004 09:27:39 -0600445_- Thank you Michael. So are you saying DB2 uses the page number from the index RID and jumps DIRECTLY to the tablespace page it needs each time it gets an INDEX RID, no matter where positionally the tablespace page sits in the tablespace order without having to scan each tablespace page to compare the page id with the index RID tablespace page id until it finds the one it needs? And also have to keep doing this for each successive RID? [...] 12323 36 61_Change OAM 32k tablespace from segmented TS to Partitioned TS0_27_mamccormack@STATESTREET.COM31_Wed, 17 Mar 2004 10:55:53 -0500384_us-ascii Do,

We do not use ImagePlus, but we have another application that uses OAM at the bottom layer. We have avoided your problem by setting up tens of SMS storage groups, each backed by a separate segmented 32k tablespace. We rarely go beyond 2Gb (so far). Our biggest has reached about 8Gb. This probably is of no use to you unless you decide to rearchitect things. [...] 12360 165 38_Re: impact of NUMLKTS when LOCKMAX=0??11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Mar 2004 11:06:35 -0500518_US-ASCII Samit, I would question why its so important for so many of them to be saving that much data. I've worked at a few different places, we had 1 or 2 tablespaces that were available for the users to use and never had that big of an issue. Is it possible they do not have good test data in their development region, so they are saving a bunch of data into their own copy of a table, to then run their program against? This is what we had here when we first went from the individual tablespaces to the common [...] 12526 13 101_Re: Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)4_Mike31_michael.brennan@I-STRUCTURE.COM31_Wed, 17 Mar 2004 10:12:40 -0600660_- One thing to keep in mind, is that Omegamon doesn't report on transactions that have abended. In other words, those transactions that have abended will not appear on the Omegamon reports.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12540 256 101_Re: Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Mar 2004 11:23:08 -0500539_US-ASCII I wasn't watching this thread, but thought I'd respond to this last statement. It appears Omegamon will give you the DML counts even when a CICS transaction abends. Here's the thread history from one. > THREAD HISTORY SQL COUNTS HPLN + Thread: Plan=P85CPCP Connid=FHAFTFP1 Corrid=GT01VH11 Authid=E6F91 + Attach: CICS DB2=DBP1 MVS=F2 + Time : Start=03/17/2004 11:12:47.489255 End=03/17/2004 11:12:47.499374 sqls + Commit = 0 Abort = 1 Select = 1 + Open Cursor = 0 Close Cursor = 0 Fetch = 0 + Insert = 0 Delete = 0 Update = 0 [...] 12797 65 41_inconsistent data condition Z/OS DB2 V7.111_Kathy Jones19_JonesKS@GW.CCSD.NET31_Wed, 17 Mar 2004 08:26:21 -0800347_US-ASCII I am receiving this error on a table in our test region. The background is 2 weekends ago, we ran a job to load the test region from a production image copy. The job abended because there was not enough space in the test region for the 1st table.

I was able to access the table and browse it so I thought everything was okay. [...] 12863 100 63_Re: Successive data row access from a duplicate index rid chain12_michael bell21_mbell11a1@VERIZON.NET31_Wed, 17 Mar 2004 10:30:21 -0600557_iso-8859-1 DB2 does everything with direct read - pick a location and read a specific block ( or blocks for bigger than 4k page). Even tablespace scan knows which was the last block read and reads 32 ( number may vary based on buffer pool size and page size) pages. This is required because DB2 is multi-user. program A may be doing a tablespace scan while program B is using a non-sequential index. They both have to work. DB2 doesn't have anything that works like QSAM/BSAM where the only option is to read (or write) the next block (well except for [...] 12964 35 60_IBM's CF412 "DB2 UDB Performance Tuning" class over the Web!24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Wed, 17 Mar 2004 11:39:04 -0500508_iso-8859-1 Beginning March 29, 2004 The Fillmore Group will be delivering:

CF412 "DB2 Universal Database Performance Tuning and Monitoring Workshop"

For the first time, these classes will be available via the Internet using The Fillmore Group's Distance Learning facility. Now you can receive the same student materials, live instructor-led lecture, and hands-on lab exercises without having to travel. Or, if you prefer, you can come to Baltimore, USA for traditional classroom training. [...] 13000 206 45_Re: inconsistent data condition Z/OS DB2 V7.111_David Nance16_DWNance@FHSC.COM31_Wed, 17 Mar 2004 11:37:46 -0500432_US-ASCII I would say you have to fix your space issues and do a load replace to finish the load you attempted two weeks ago.

>>> JonesKS@GW.CCSD.NET 3/17/04 11:26:21 AM >>> I am receiving this error on a table in our test region. The background is 2 weekends ago, we ran a job to load the test region from a production image copy. The job abended because there was not enough space in the test region for the 1st table. [...] 13207 88 31_Re: Reading IFCID for Deadlocks19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Wed, 17 Mar 2004 10:38:27 -0600346_us-ascii I have to disagree with the statement "This is a true deadlock, in a true deadlock, the holder is interested in the page the waiter has secured and the waiter wants a page the holder has.". Because this is a description of a TIMEOUT (not a deadlock).

We use CA's DB2 insight and we see everything involved with the deadlock. [...] 13296 114 101_Re: Activating Accounting Trace to get count of all DML statement exe cuted by AUTHID ( DB2 7.1 /ZOS)11_Sinha, Amit19_sinha.amit@MBCO.COM31_Wed, 17 Mar 2004 10:50:43 -0600382_iso-8859-1 Thanks Martin & all for your reply. The traces were fine as you all said, What I was looking for authid executing dynamic sql (db2connect). But I can get the same information from sql_prepare by authid.

Which works for us as we don't have insert,update, delete for these authid. And the sql_prepare should be equal to the number of select done by the authid. [...] 13411 20 33_What depends on the region size ?12_Billy Larsen19_billarsen@YAHOO.COM31_Wed, 17 Mar 2004 10:56:04 -0600327_- Dear esteemed colleagues,

I am writing a document on the DB2 Usage of memory , a lot of "components" are out of control of the Region size (i.e not located in the User Region and the Extended User Region ) , on the list of components which are located in the User Region , i am a bit short. Can you please help . [...] 13432 113 45_Re: inconsistent data condition Z/OS DB2 V7.110_Ravi Reddy20_ravi.reddy@PS.GE.COM31_Wed, 17 Mar 2004 12:05:41 -0500782_ISO-8859-1 Hi Kathy,

I encoutered the same issue this monday morning ! Data refresh of test was attempted but failed due to space issues on test database. Rebuild index gave us the mentioned dump, the same as yours

0.28.19 STC09977 DSNI013I = DSNIRFNX POTENTIALLY INCONSISTENT DATA REASON 00C9021B ERQUAL 500E TYPE 00000302 NAME TBDOCDT3.DOCSDOCR.X'00030354' CONNECTION-ID=UTILITY CORRELATION-ID=TZT3ROP1 LUW-ID=* 00.28.19 STC09977 DSNI013I = DSNIRFNX POTENTIALLY INCONSISTENT DATA REASON 00C9021B ERQUAL 500E TYPE 00000302 NAME TBDOCDT3.DOCSDOCR.X'0002F0A4' CONNECTION-ID=UTILITY CORRELATION-ID=TZT3ROP1 LUW-ID=* 00.28.40 STC09977 DSN3201I = ABNORMAL EOT IN PROGRESS FOR USER=TB0526T CONNECTION-ID=UTILITY CORRELATION-ID=TZT3ROP1 JOBNAME=TZT3ROP1GR TCB=009D32C8 [...] 13546 62 37_Re: What depends on the region size ?12_michael bell21_mbell11a1@VERIZON.NET31_Wed, 17 Mar 2004 11:13:23 -0600563_iso-8859-1 Are you talking about how the DB2 address spaces use memory or how an application using DB2 uses memory. The are multiple papers from tech conference and IDUG on DB2 use of memory in DBM1 and MSTR and IRLM.

The answer for application program is not much at all - DSNHLI loads another program to control the access to DB2 and everything else is done using cross memory. You could do an analysis of the memory usage of running under DSN vs call attach but both are minimal usage of memory. You could include the size of the PLIST's generated [...] 13609 183 45_Re: inconsistent data condition Z/OS DB2 V7.117_Duane Lee - EGOVX22_DLee@MAIL.MARICOPA.GOV31_Wed, 17 Mar 2004 10:14:27 -0700547_iso-8859-1 Use DSN1COPY to reload the table and then recover/rebuild the index.

-----Original Message----- From: Kathy Jones [mailto:JonesKS@GW.CCSD.NET] Sent: Wednesday, March 17, 2004 9:26 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: inconsistent data condition Z/OS DB2 V7.1



I am receiving this error on a table in our test region. The background is 2 weekends ago, we ran a job to load the test region from a production image copy. The job abended because there was not enough space in the test region for the 1st table. [...] 13793 69 37_Re: What depends on the region size ?21_McDermott, William G.19_wmcdermo@LEHMAN.COM31_Wed, 17 Mar 2004 12:12:24 -0500596_iso-8859-1 Bill,

Look on the web for a Share presentation by John J. Campbell, from IBM, titled "DB2 for z/OS Virtual Storage Management Before and After zSeries". It may prove helpful.

Bill

William G. McDermott Lehman Brothers, Inc. Vice President DB2 Systems Phone (201) 499-4759 Pager (800) 693-7243 [17398122] wmcdermo@lehman.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Billy Larsen Sent: Wednesday, March 17, 2004 11:56 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: What depends on the region size ? [...] 13863 44 22_Re: Scrollable cursors12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 17 Mar 2004 19:31:06 +0200428_- Hi, In V8 it's not only the dynamic but the fact that you don't use the DGTT as you work on the base table. Currently (V7) the overhead for the DGTT is too much for online.

Isaac Yassin

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Walter Janißen Sent: Wednesday, March 17, 2004 10:57 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Scrollable cursors [...] 13908 103 45_Re: inconsistent data condition Z/OS DB2 V7.114_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Wed, 17 Mar 2004 10:37:45 -0600527_us-ascii Kathy, When you say you "Loaded it a production image copy" do you mean that you ran an unload job against an image copy then ran a load on the test side, or did you use DSN1COPY with OBIDXLAT to refresh the tablespace? Based on the error you are getting, I am guessing that DSN1COPY was used, because the spacemap page is out of sync with the data, if a load failed the space map would never have been updated for those rows not loaded. Most likely you will have to either - 1 - Unload from the production image [...] 14012 29 24_Linux/390 and UDB V8.1.512_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Wed, 17 Mar 2004 12:39:01 -0500348_iso-8859-1 I have a question about what type of tools are available for UDB running on Linux/390?

I am looking for something like Omegamon for DB2 that we have on the Mainframe, does any one know of a product or products that can do some of the same type of functions, like show the SQL as it is running, show history of threads, etc.. [...] 14042 43 28_Re: Opthint and version auto14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Wed, 17 Mar 2004 11:43:34 -0600385_us-ascii There is no easy way. We use a COBOL program prior to every bind to take opt hints from a staging table that either fills in the appropriate collection ids and version numbers as need be prior to the bind. We can then control opt hints to work for a program regardless of collection id or version or we can also specify opt hints for specific versions or collection ids. [...] 14086 103 45_Re: inconsistent data condition Z/OS DB2 V7.113_Horacio Villa17_hvilla@AR.IBM.COM31_Wed, 17 Mar 2004 16:28:00 -0300456_US-ASCII Kathy,

was your IC shrlevel reference? I think I had a similar problem doing same thing with a shrlevel change IC.

Horacio Villa







Kathy Jones cc: Sent by: DB2 Data Subject: inconsistent data condition Z/OS DB2 V7.1 Base Discussion List



03/17/04 01:26 PM Please respond to DB2 Database Discussion list at IDUG [...] 14190 57 55_Re: Using LOAD with delimited data in DB2 V7.1 on S390.14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Wed, 17 Mar 2004 20:45:44 +0100339_- I have a program that loads (and unloads) different data formats into DB2 (and ORACLE and MySQL and SQL Server), including CSV, dBASE-Files (DBF) and various XML formats.

But I can't give it away for free, because I need to make some money out of this, not much :-)

If you're interested, you could contact me offline. [...] 14248 37 29_DB2 Prefetch failed no engine0_28_Missy.Case@FIRSTDATACORP.COM31_Wed, 17 Mar 2004 16:53:44 -0600277_US-ASCII All, We recently have seen a very large number of prefetch failed no engine occurring in 2 of our high profile bufferpools. We moved CPUs to one with half the engines, we believe this is one of the major causes in the CPU increase in our DB2 trans for this pool. [...] 14286 132 45_Re: inconsistent data condition Z/OS DB2 V7.111_Kathy Jones19_JonesKS@GW.CCSD.NET31_Wed, 17 Mar 2004 15:07:14 -0800330_US-ASCII Thanks to all that answered. I unloaded the table then loaded an empty file, This was succesful so tnoight I will load the table. I can't make it bigger as there is not enough room in our test system. Thanks again.

Kathy Jones 702-799-5040 X5683 OS/390 DB2 Database Administrator Clark County School District [...] 14419 26 30_Test Message --- Please Ignore14_Richard Atkins16_rratkp@YAHOO.COM31_Wed, 17 Mar 2004 16:03:07 -0800173_us-ascii

Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam

--------------------------------------------------------------------------------- 14446 100 43_Help to build an effective query for update14_Richard Atkins16_rratkp@YAHOO.COM31_Wed, 17 Mar 2004 16:52:53 -0800380_us-ascii Hi All,

I have what looks like a simple task but need an effective SQL. I hate to ask a developer use procedural code to accomplish it. Any suggestions will be greatly appreciated.

Notes: Table A is fairly large . Contains at least 15 million records (7.5 GB) Col1 is the only primary key of the table The value 'R' in col2 appears at a fixed position [...] 14547 189 37_DB2 Disaster Recovery - Press Release0_19_DB2Automate@AOL.COM29_Wed, 17 Mar 2004 20:19:39 EST675_US-ASCII Recovery Knowledge is please to announce an enhanced version of the GENDB2 software product. GENDB2 is a software product that automates the recovery of DB2 for OS/390-z/OS resources Off-site Disaster Recovery as well as On-site.

The enhancements to the GENDB2 software product include:



(1) Feature to allow the customer to display GENDB2 tables regarding Disaster Recovery. A. The Archive Log Copy recording information (Original and Copied volume serial number, and data set names (BSDS and Archive) with TIMESTAMP. B. Start/End RBA and LRSN for latest Archive Log Registration. C. Conditional Restart CRestart D. Start RBA for DSN1LOGP [...] 14737 88 33_Re: DB2 Prefetch failed no engine35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 17 Mar 2004 21:55:09 -0500575_iso-8859-1 Missy, If I remember correctly, there are 600 internal read engines available in DB2 - for the entire subsystem. If you are running out of them, it usually means they are all busy waiting for I/O to complete. This is usually due to DASD performance problems. While the number of en gines is a (non-externalized) parameter in the zparm, it's not something you want to change unless IBM support specifically tells you to change based on your system problem. Regarding moving to a CPU with 1/2 the engines, it all depends on what this means. Going from a 16 way [...] 14826 21 15_Names for LPARS9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Thu, 18 Mar 2004 09:33:40 +0530316_iso-8859-1 Hi, We have recently purchased a Z800 0A2 system and have activated 2 LPARS. We are looking for some good names for the two. Any suggestions welcome. As on now we are thinking of using names of some of the tall mountain peaks in the world.But I think we can do better. Kindly share your experiences. [...] 14848 30 19_Re: Names for LPARS10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Wed, 17 Mar 2004 22:42:58 -0600326_iso-8859-15 Typically want something that means something to the folks who have to drive them. PROD, TEST, SAND lets you know where priorities are when start trying to bring them up and something fails or one of them fails. If you plan on different SCPs, ZPROD, ZTEST,ZSAND, VMPROD,VMTEST,VMSAND, LIPROD, LITEST, LISAND. [...] 14879 62 40_Application logic and Locking question..16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 17 Mar 2004 22:51:37 -0800344_us-ascii We have a program here with the following logic:

DECLARE CURSOR C1 FOR SELECT COL3, COL4 FROM TABLE1 WHERE COL1 = :hv1 AND COL2 = :hv2 FOR UPDATE OF COL3, COL4

OPEN CURSOR C1

FETCH CURSOR C1 INTO :hv3, :hv4

ADD 1 TO hv3 ADD 2 to hv4

UPDATE TABLE1 SET COL3 = :hv3, COL4 =:hv4 WHERE CURRENT OF C1 [...] 14942 258 47_Re: Help to build an effective query for update16_Michael Liberman19_michaelli@BLL.CO.IL31_Thu, 18 Mar 2004 09:48:31 +0200339_us-ascii Hi Richard,

If i understand correctly the issue that you have presented, it appears that you can use the SUBSTR function. (you said that the R value is in fixed position) ---> SUBSTR (1,length of colume,fixed postion of R value) and another thing, pay attention that you should have an index defined on those columns. [...] 15201 39 33_Re: DB2 Prefetch failed no engine0_16_mscarpa@CESVE.IT31_Thu, 18 Mar 2004 09:21:23 +0100362_US-ASCII Missy





I agree with Joel, take a look to your DASD response with your friendly DASD guy, but I'd have a look to your WLM policies (just in case) to see if they were modified to consider the new CPUs (it must be done when you have hw changes).

What's the actual number of CPUs and what was the original (just to know) ? [...] 15241 19 19_Re: Names for LPARS0_16_mscarpa@CESVE.IT31_Thu, 18 Mar 2004 09:26:35 +0100714_US-ASCII Use names of nice and gorgeous lady, they are easier to remember (if there are mostly man working there). Or man, if the contrary :-)

I worked in a place were machine were called 'Ganesh', 'Betty' and so on...

Max Scarpa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 15261 110 47_Re: Help to build an effective query for update14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Thu, 18 Mar 2004 09:41:22 +0100 15372 52 19_Re: Names for LPARS23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 18 Mar 2004 09:18:30 -0000636_iso-8859-1 we use Black Adder names for our machines ...

Kate Edmond

but we always use PRD1/2... etc for our regions.

Les

-----Original Message----- From: SrinivasG [mailto:SRINIVASG@INFOSYS.COM] Sent: Thursday, March 18, 2004 4:04 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Names for LPARS



Hi, We have recently purchased a Z800 0A2 system and have activated 2 LPARS. We are looking for some good names for the two. Any suggestions welcome. As on now we are thinking of using names of some of the tall mountain peaks in the world.But I think we can do better. Kindly share your experiences. [...] 15425 60 19_Re: Names for LPARS13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 18 Mar 2004 09:38:10 -0000509_iso-8859-1 Some muppet(s?) here has named our LPARs using a number of themes. We've gone down the Wind in the Willows path, Morecambe and Wise, Laurel and Hardy and a few others of hysterical significance. I hate it. What is this, Unixland? This is the only site I've been at in 18 years that doesn't call them something approximating their function. Makes working out what LPAR you're talking about difficult. If it were me I'd stick to something like DEV1, PRD2, etc. Boring, yes. Obvious? Definitely. [...] 15486 21 19_Re: Names for LPARS0_16_mscarpa@CESVE.IT31_Thu, 18 Mar 2004 10:51:07 +0100383_US-ASCII That's why they call us 'dynosaurs'....People who call machine using abbreviation, working with a black/green screen instead a GUI interface and not using a mouse. If symbols mean something, we are losing since many years.

But some young managers are pleased by these names, as they are pleased by Microsoft machines and so on. Don't you like a rise in salary ? [...] 15508 116 19_Re: Names for LPARS14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 18 Mar 2004 10:29:52 -0000695_iso-8859-1 Try mythology...

ODIN, THOR, MARS, ATHENE, HERMES, APHRODITE etc...

Or fish...

COD, HAKE, TROUT, HALIBUT, HADDOCK etc...

Or the Simpsons...

HOMER, MARGE, BART, LISA, MAGGIE etc...

The list is quite literally, in front of you.



--------------------------------------------------------------------------------------------------------------- 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. [...] 15625 147 19_Re: Names for LPARS14_Nelson, Philip34_Philip.Nelson@SCOTTISHWIDOWS.CO.UK31_Thu, 18 Mar 2004 10:49:35 -0000374_iso-8859-1 This E-Mail is sent in confidence for the addressee only. Unauthorised recipients must preserve this confidentiality and should notify the sender immediately by telephone on 0131-655-6789 and must delete the original E-Mail without taking a copy. If you are not the addressee you must not copy, distribute, disclose or use any of the information in any way. [...] 15773 40 46_Application algorithm and deadlock conundrum..16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 18 Mar 2004 04:16:17 -0800318_us-ascii Well..have racked my brains and can't figure this out. We have a transaction here which deadlocks against itself dozens of times everyday. The 'two resources' involved are always some page of table T1 and some page of table T2. The program invoked by transaction has algorithm along the following lines: [...] 15814 111 45_Re: inconsistent data condition Z/OS DB2 V7.10_16_FRUSA@BCBSIL.COM31_Thu, 18 Mar 2004 06:49:09 -0600406_us-ascii How did you load the test table ? Dsn1copy ? Do the table and index structures of source and target match ?





"Kathy Jones" cc: Sent by: "DB2 Subject: inconsistent data condition Z/OS DB2 V7.1 Data Base Discussion List" 03/17/2004 10:26 AM Please respond to "DB2 Database Discussion list at IDUG" [...] 15926 54 50_Re: Application algorithm and deadlock conundrum..14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Mar 2004 23:09:39 +1000515_US-ASCII Not your week, is it?

Does the application do any selects? They, as well as updates, can have locks. If the selects are WITH RR, then the locks prevent any updates that would have changed the result of the select (and under some circumstances be much broader than just the pages on which selected rows appear); if the select is WITH RS, then only the pages with rows selected are locked. These locks stay until the next COMMIT - unlike CS locks which are released once the cursor has moved on. [...] 15981 62 50_Re: Application algorithm and deadlock conundrum..17_MORTIMER, Dominic37_Dominic.MORTIMER@WATFORD.SEMA.SLB.COM31_Thu, 18 Mar 2004 13:12:27 +0000621_ISO-8859-1 Are there any reads going on before the update?

Cheers

Dom



-----Original Message----- From: Raquel Rodriguez [mailto:raquel_rodriguezus@YAHOO.COM] Sent: 18 March 2004 12:16 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Application algorithm and deadlock conundrum..



Well..have racked my brains and can't figure this out. We have a transaction here which deadlocks against itself dozens of times everyday. The 'two resources' involved are always some page of table T1 and some page of table T2. The program invoked by transaction has algorithm along the following lines: [...] 16044 140 50_Re: Application algorithm and deadlock conundrum..19_Alekos Papadopoulos13_apapad@NBG.GR31_Thu, 18 Mar 2004 15:22:52 +0200611_ISO-8859-7 Maybe T1 and T2 are in the same, non segmented tablespace? Alekos

-----Original Message----- From: Raquel Rodriguez [mailto:raquel_rodriguezus@YAHOO.COM] Sent: Thursday, March 18, 2004 2:16 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Application algorithm and deadlock conundrum..

Well..have racked my brains and can't figure this out. We have a transaction here which deadlocks against itself dozens of times everyday. The 'two resources' involved are always some page of table T1 and some page of table T2. The program invoked by transaction has algorithm along the following lines: [...] 16185 102 19_Re: Names for LPARS11_Tina Hilton23_thilton@RANDOMHOUSE.COM31_Thu, 18 Mar 2004 08:25:31 -0500545_- You have to watch out for naming a system DEVx or PRDx too. At my previous employer, their first MVS system was named DVLESA -- DVL for development and ESA because the OS was MVS/ESA. When the application was ready for production, the tech support team was ready to create the PRDESA system, but the application folks wouldn't let them. This version worked and they wanted it to become production. The ESA part of the name at least lasted a while. When I left in 2000, they were still on MVS/ESA 4.3. This shop also used 3090 in the link [...] 16288 102 19_Re: Names for LPARS14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Thu, 18 Mar 2004 13:34:02 -0000312_iso-8859-1 Boringly, you could name your LPAR thus...

Byte 1 = N - for "Node" Byte 2 = alphnumeric character allocated to CPU name Bytes 3-4 = two-byte sequential unique number

e.g. For CPU "A", first LPAR would be NA00, second LPAR would be NA01 For CPU "B", first LPAR would be NB00, etc... [...] 16391 131 55_Re: Using LOAD with delimited data in DB2 V7.1 on S390.11_Samit Goyal19_samgoyal@IN.IBM.COM31_Thu, 18 Mar 2004 05:46:16 -0800502_US-ASCII Hi All,

Thanks for your inputs on this topic.

But as I said, I want to setup jobs to do this task automatically and repeatitively without any manual intervention. And also, I do not want to go out of MVS box!

Regards, Samit. _______________________________________ Samit Goyal, IBM Global Services India, Prestige Towers, 4A-018, No.99, Residency Road, Bangalore-560025 Board : 91-80-2079999 Extn: 4013. Mobile : +919886192819 _______________________________________ [...] 16523 162 55_Re: Using LOAD with delimited data in DB2 V7.1 on S390.11_Tina Hilton23_thilton@RANDOMHOUSE.COM31_Thu, 18 Mar 2004 08:51:36 -0500501_- I don't know about IBM's load utility, but BMC Load Plus supports delimited data. That's why 3rd party utilities exist: to supply additional features and speed. IBM will probably add these features at some point, though.

Tina Hilton Random House Bertelsmann Shared Services

-----Original Message----- From: Samit Goyal [mailto:samgoyal@IN.IBM.COM] Sent: Thursday, March 18, 2004 8:46 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Using LOAD with delimited data in DB2 V7.1 on S390. [...] 16686 149 33_Re: DB2 Prefetch failed no engine0_28_Missy.Case@FIRSTDATACORP.COM31_Thu, 18 Mar 2004 08:12:20 -0600530_US-ASCII Joel & Max, Thanks for your kind thoughts.

We moved to a TREX machine from a 10 way w/ 1934 MIPS to a 5 way with more (don't have the exact number in front of me). The trouble with this high profile system is that the dynamic queries do about 60 mln gps an hour, but only around 20,000 IOs. We are seeing far, far more prefetch engine failed than we believe should be ios to dasd. We are very curious how this could happen, we're talking huge percentages & and prefetch engine failing about 5300 times in a 5 [...] 16836 267 63_Re: Could and additional WHERE-Clause impact the sql-accespath.16_Michael Liberman19_michaelli@BLL.CO.IL31_Thu, 18 Mar 2004 16:38:46 +0200501_us-ascii Hi Patrick, if i recall it correctly (and correct me if i'm wrong) using the 1=1 clause automaticlly stage-2 processing.







Michael Liberman DBA Team Leader HERMESH project 927 54 685756



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steurs Patrick Sent: Wednesday, March 17, 2004 2:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Could and additional WHERE-Clause impact the sql-accespath. [...] 17104 15 33_Re: DB2 Prefetch failed no engine0_16_mscarpa@CESVE.IT31_Thu, 18 Mar 2004 15:35:18 +0100530_US-ASCII Missy are you using query parallelism ?

MAx

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 17120 51 33_Re: DB2 Prefetch failed no engine0_28_Missy.Case@FIRSTDATACORP.COM31_Thu, 18 Mar 2004 08:37:43 -0600906_US-ASCII Max, No. Missy Case FDR 701-275-6358







mscarpa@CESVE.IT Sent by: DB2 Data To: DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc: List bcc:



03/18/04 08:35 AM Please respond to DB2 Database Discussion list at IDUG











Missy are you using query parallelism ?

MAx

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org . The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm [...] 17172 236 63_Re: Could and additional WHERE-Clause impact the sql-accespath.14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Thu, 18 Mar 2004 15:43:46 +0100574_iso-8859-1 Michael,

I think it's only when you use a '1=1 or'-clause. At hte bank, we use ONLY a '1=1 and' clause'

from the books :

"In general, if you form a compound predicate by combining several simple predicates with OR operators, the result of the operation has the same characteristics as the simple predicate that is evaluated latest. For example, if two indexable predicates are combined with an OR operator, the result is indexable. If a stage 1 predicate and a stage 2 predicate are combined with an OR operator, the result is stage 2." [...] 17409 208 33_Re: DB2 Prefetch failed no engine35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 18 Mar 2004 09:49:51 -0500577_iso-8859-1 Missy, Just as a thought along a subject Max brought up, prefetch runs at the priority of the dbm1 address space. So checking all your wlm policies is a good idea too. Now your Trex machine...... I'll guess it's a 2084-305 with 1984 Mips. Of course to start with, Mips = meaningless indicator of processor speed. If this is the machine, it's only 3% faster based on the Mips. Mips can easily vary up to 30%, depending on your workload, based on Cheryl Watson's publications. Now the big one..... there have been many reported instances of the new Trex machines [...] 17618 29 33_Re: DB2 Prefetch failed no engine0_16_mscarpa@CESVE.IT31_Thu, 18 Mar 2004 16:14:27 +0100541_US-ASCII Hi Missy

It's easy to have read engine shortage with parallelism, but it's not your case, I assume all ZPARM values are set ok to eliminate it. I assume that no one of the BPs is SORT BP.

Mhhh...if you *ONLY* changed machine and DB2 problems came up after it maybe your goals are too high for this machine and you could have too much workload in the same DB2 service class with the same high priority (as it must be for DB2). I heard about it in a seminar where almost all address spaces where in 1 service class [...] 17648 110 24_Re: DB2 V8 withdrawal???0_17_mjartigas@ATCA.ES31_Thu, 18 Mar 2004 16:25:22 +0100682_us-ascii "Humphris,Richard P." @IDUGDB2-L.ORG> con fecha 16/03/2004 21:30:02

Por favor, responda a DB2 Database Discussion list at IDUG

Enviado por: DB2 Data Base Discussion List



Destinatarios: DB2-L@WWW.IDUGDB2-L.ORG CC: Asunto: Re: DB2 V8 withdrawal???







If this announcement makes sense to you, it only means you are familiar with the acronym QPP.

Since announcement letters go to a broad audience, this becomes a VERY poor announcement in my opinion. Especially when it includes the ending line: Replacement product information: None [...] 17759 114 44_Re: Application logic and Locking question..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 10:08:59 -0600438_us-ascii Hi Raquel,

Yes, your re-write is much better code. Every SQL call has some amount of overhead associated with it; and depending on the number of rows you're updating you've potentially eliminated the overhead of many FETCH and UPDATE calls. And you've reduced the amount of time your application code has to be directly involved with your update process so DB2 will be able to process the rows much more efficiently. [...] 17874 74 50_Re: Application algorithm and deadlock conundrum..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 10:20:27 -0600698_us-ascii (from left field): Could the update of table2 also contain a sub-select referencing table1?

Rich Humphris

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raquel Rodriguez Sent: Thursday, March 18, 2004 6:16 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Application algorithm and deadlock conundrum..



Well..have racked my brains and can't figure this out. We have a transaction here which deadlocks against itself dozens of times everyday. The 'two resources' involved are always some page of table T1 and some page of table T2. The program invoked by transaction has algorithm along the following lines: [...] 17949 171 47_Re: Help to build an effective query for update17_Duane Lee - EGOVX22_DLee@MAIL.MARICOPA.GOV31_Thu, 18 Mar 2004 09:29:57 -0700401_iso-8859-1 Not exactly sure what you're asking for but if I guessed correctly you could use SUBSTR against COL2 to "extract" only that portion that you want.

Duane

-----Original Message----- From: Richard Atkins [mailto:rratkp@YAHOO.COM] Sent: Wednesday, March 17, 2004 5:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Help to build an effective query for update



Hi All, [...] 18121 17 33_Re: DB2 Prefetch failed no engine17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Thu, 18 Mar 2004 11:06:36 -0600705_- Missy, Since you indicated that you had dynamic queries, it is possible that the access path changed wildly for a mysterious query or two, due to the cpu reduction. Do you notice degraded class-2 and class-3?.

Regards, venkat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 18139 188 32_R: DB2 Prefetch failed no engine14_Adrian Collett24_adriancollett@TISCALI.IT31_Thu, 18 Mar 2004 18:07:59 +0100443_US-ASCII Missy, just a shot in the dark...

Could it be that you have a lot of sort activity going on at the same time ?? What I mean by that is, are there big sorts using LOTS of LOGICAL work files. I'm pretty sure DB2 will use ONE prefetch engine PER LOGICAL work file. Therefore, if you do have some big sorts going on, then your other work may suffer the engine failures, due to less engines being available.... get my drift ?? [...] 18328 50 60_[DB2V7 OS/390] REORG: Clustering without clustering indexes?15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Thu, 18 Mar 2004 13:30:37 -0500486_us-ascii We're getting poor clustering -- 60% and lower -- on _any_ index when we REORG tablespaces which don't have an explicit clustering index. Anyone know if this is "Works as designed" vs. a bug? A manual reference would be perfect, but I'll take opinions and thoughts.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD 230 Raritan Center Parkway, P5-105 Edison, NJ 08837 Phone: (732) 225-8086 Fax: (732) 225-8109 mailto:phil.sevetson@wakefern.com [...] 18379 172 64_Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Thu, 18 Mar 2004 13:38:02 -0500453_iso-8859-1 Phil, I believe this is "working as designed"

If you do not have a clustering index, ie telling DB2 what physical order you want to store your table, then DB2 can not determine how to sort the data when it is reorging them. Thus if you two or three different indexes, DB2 does not know which one he/she should use during the reorg, thus it use none. What you get is two or three indexes that have a 60% or lower clustering ratio. [...] 18552 112 64_Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Thu, 18 Mar 2004 13:41:54 -0500613_us-ascii Whoops! We got the answer from Mullins DBA Developer's guide. DB2 doesn't consider a default clustering index (as opposed to an explicit clustering index) to be a candidate index for access during the UNLOAD phase.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











Phil_Sevetson/CISD/WAKEFERN.WAKEFERN Sent by: DB2 Data Base Discussion List 03/18/2004 01:30 PM Please respond to DB2 Database Discussion list at IDUG [...] 18665 176 64_Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?17_Duane Lee - EGOVX22_DLee@MAIL.MARICOPA.GOV31_Thu, 18 Mar 2004 11:50:16 -0700445_iso-8859-1 I believe I read at one time that if you do not specify a cluster index the FIRST index created on the table will be used.

Duane

-----Original Message----- From: John Lendman [mailto:lendman@PALMBEACH.K12.FL.US] Sent: Thursday, March 18, 2004 11:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?



Phil, I believe this is "working as designed" [...] 18842 31 50_Re: Application algorithm and deadlock conundrum..17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Thu, 18 Mar 2004 13:13:35 -0600304_- So you have identified the SQL. Is the update statement tuned?.

Within the same thread, a transaction should never cause a deadlock or a timeout to itself. In step 10 if you try to read what was updated in step 1 but not yet written to dasd, worst case is you will incur a few sync writes. [...] 18874 88 7_Purcell15_William Johnson36_William.Johnson@ANTARESSOLUTIONS.COM31_Thu, 18 Mar 2004 15:22:35 -0500455_us-ascii Whatever happened to Terry Purcell? He taught a class in Pittsburgh that I took a couple of years ago and I used to see him answer SQL questions. I haven't seen him respond in quite some time? Thanks,

Bill Johnson Tech Services OS/390, z/OS, Top Secret, Syncsort Antares Management Solutions 23700 Commerce Park Road Beachwood, OH 44122-5832 Phone:(216) 292-0400 ext.3478 FAX: (216) 292-1619 E-mail: william.johnson@antaressolutions. [...] 18963 206 63_Re: Could and additional WHERE-Clause impact the sql-accespath.13_Horacio Villa17_hvilla@AR.IBM.COM31_Thu, 18 Mar 2004 17:32:29 -0300472_US-ASCII Patrick,

I don't see how a '1=1 AND' can avoid a sql-syntax error. Could you please explain that to me?

Horacio Villa







Steurs Patrick cc: Sent by: DB2 Data Subject: Re: Could and additional WHERE-Clause impact Base Discussion the sql-accespath. List



03/18/04 11:43 AM Please respond to DB2 Database Discussion list at IDUG [...] 19170 141 11_Re: Purcell15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Thu, 18 Mar 2004 15:32:32 -0500377_us-ascii He's in charge of the DB2 Optimizer, working for IBM. I can't remember whether his problem was time or ethics, but he's not posting here while he does that.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD 230 Raritan Center Parkway, P5-105 Edison, NJ 08837 Phone: (732) 225-8086 Fax: (732) 225-8109 mailto:phil.sevetson@wakefern.com [...] 19312 189 11_Re: Purcell12_Cianci, Nick19_nick.cianci@EDS.COM31_Fri, 19 Mar 2004 07:49:37 +1100302_- He joined IBM, working w/ the Optimiser.

Current where-about is Sydney in the land of Oz for IDUG, but at the end of the week when he clicks those red-stair climbing runners together, he'll be whispering there is no place like IBM there is no place like IBM; and he'll be back in the US. [...] 19502 18 64_Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?9_Jim Ruddy18_jaruddy@US.IBM.COM31_Thu, 18 Mar 2004 15:05:11 -0600415_- An implicit clustering index (the first one created) is only used for Inserts in V7 and earlier releases. In V8, REORG will also use the implicit clustering index. As one of the features of Online Schema in V8, The CLUSTER attribute can be turned on or off on an index with an ALTER statement without having to drop and recreate the index. Of course, only one index is allowed to have the CLUSTER attribute. [...] 19521 47 12_TSM Question22_Prasadu, Vara (cahoot)23_Vara.Prasadu@CAHOOT.COM31_Thu, 18 Mar 2004 21:16:19 -0000374_iso-8859-1 Hi,

Our database backups are stored in TSM. Can we take the backup file from TSM into database machine's file system and restore the database from the file? Is it possible?

Is there any restriction that if the backup is created in TSM, should the restore be also from TSM?

If you could send the link about the same, that would be great. [...] 19569 88 50_Re: Application algorithm and deadlock conundrum..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 15:18:41 -0600379_us-ascii I think she was suggesting she has two threads (i.e. two transactions) running the same code. But these two threads end up deadlocking with one another. I believe she has the following situation:

Thread 1: has a lock on page-x in Table1 and wants a lock on a page-y in Table2. Thread 2: has a lock on page-y in Table2 and wants the lock on page-x in Table1. [...] 19658 74 50_Re: Application algorithm and deadlock conundrum..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 15:20:14 -0600715_us-ascii Ahh, maybe there is a trigger defined on a column in Table2; and that trigger updates something in Table1???

Rich Humphris

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raquel Rodriguez Sent: Thursday, March 18, 2004 6:16 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Application algorithm and deadlock conundrum..



Well..have racked my brains and can't figure this out. We have a transaction here which deadlocks against itself dozens of times everyday. The 'two resources' involved are always some page of table T1 and some page of table T2. The program invoked by transaction has algorithm along the following lines: [...] 19733 116 64_Re: [DB2V7 OS/390] REORG: Clustering without clustering indexes?15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Thu, 18 Mar 2004 16:35:37 -0500515_us-ascii Hey, yet another thing you guys are properly fixing with V8! Blessings on y'all.

--Phil Sevetson Database Administration Wakefern Food Corporation CISD 230 Raritan Center Parkway, P5-105 Edison, NJ 08837 Phone: (732) 225-8086 Fax: (732) 225-8109 mailto:phil.sevetson@wakefern.com











Jim Ruddy Sent by: DB2 Data Base Discussion List 03/18/2004 04:05 PM Please respond to DB2 Database Discussion list at IDUG [...] 19850 297 50_Re: Application algorithm and deadlock conundrum..11_David Nance16_DWNance@FHSC.COM31_Thu, 18 Mar 2004 16:47:48 -0500534_US-ASCII The other possibility is thread A is in table 2 with a lock on page n and thread B is already done with table 1 and trying to get a lock on page n in table 2, as well. I would suggest looking at your locksize and the possibility of commiting between the two updates. I suspect row level locking would resolve your issues, though. I know everyone is always saying to never use row level locking, but I have used it on two major applications now and we haven't seen any issues with CPU consumption. We did, however, raise [...] 20148 46 35_REBIND while a program is executing11_Hardy, Dale14_DHardy@MIB.COM31_Thu, 18 Mar 2004 17:34:57 -0500371_iso-8859-1 Listers,

I always thought that you could always submit a package rebind anytime even regardless of whether the package was being executing or not. If it wasn't executing then the rebind would have no problem. If the package was in use, you job would either wait or abend with a resource unavailable. Either way it would not impact the application. [...] 20195 208 39_Re: REBIND while a program is executing22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 18 Mar 2004 17:02:04 -0600555_iso-8859-1

I've had the exact same thing happen to me before and had asked the same question. Mine was with CICS transactions. Search for subject '-904 in CICS' in the archives and you'll find the responses.

HTH,

Venkatesh Mokshagundam Database Administrator Corporate Systems, Amarillo, TX 79102 Ph: 806-337-3374 Fax: 806-345-2736



-----Original Message----- From: Hardy, Dale [mailto:DHardy@MIB.COM] Sent: Thursday, March 18, 2004 4:35 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: REBIND while a program is executing [...] 20404 187 50_Re: Application algorithm and deadlock conundrum..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 17:21:25 -0600576_us-ascii

The result using your scenario would be: either Thread B would do a commit in time resulting in only a delay for Thread-A OR it wouldn't commit in time in which case Thread-A would get a timeout. But this scenario won't produce any deadlocks.

But I only see row level locking using more CPU time. Depending on how many rows you average in a page will determine how many times more locks you need to acquire. I agree with you that sometimes using row level locking may make a difference for your ELAPSED time that your jobs and/or transactions run [...] 20592 280 50_Re: Application algorithm and deadlock conundrum..12_michael bell21_mbell11a1@VERIZON.NET31_Thu, 18 Mar 2004 17:38:44 -0600521_Windows-1252 MessageA lock is a lock is a lock.

The cost of a row lock is exactly the same as the cost of a page lock.

The CPU cost depends on the number of rows referenced. If a program processes 3 rows then the lock cost is 3 x page locks (assuming the 3 rows were in the same page). It the program only updates 1 row per transaction, then the locking cost of row locks is exactly the same as page locks. It doesn't matter how many rows are on a page unless the application is processing them - then [...] 20873 375 50_Re: Application algorithm and deadlock conundrum..19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Thu, 18 Mar 2004 17:51:07 -0600492_us-ascii

You have a very good point, it depends. But using even your best case, the best row level locking can do is match the CPU expense of page level locking. Once you lock even one more row in the same page, row level locking just became twice as expensive as the cost of a single page lock (for that page). If heard of long running batch jobs which when converted from row level locking to page level locking not only ran many times faster but caused less timeouts as well. [...] 21249 192 38_Re: impact of NUMLKTS when LOCKMAX=0??13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 18 Mar 2004 23:51:28 GMT575_- Samit:

Here's a little SQL to produce cleanup statements for QMF SAVE DATA tables:

SELECT SUBSTR('DROP TABLE ' CONCAT STRIP (CREATOR,TRAILING) CONCAT'.' CONCAT STRIP(NAME,TRAILING) CONCAT';', 1,39) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSQDBDEF' AND DAYS(CURRENT DATE) - DAYS(CREATEDTS) > 7 WITH UR;

And one for cleanup of DSNDB04 temp tablespaces:

SELECT SUBSTR('DROP TABLESPACE DSNDB04.' CONCAT STRIP(NAME,TRAILING) CONCAT';', 1,33) FROM SYSIBM.SYSTABLESPACE WHERE DBNAME = 'DSNDB04' AND DAYS(CURRENT DATE) - DAYS(CREATEDTS) > 7 WITH UR; [...] 21442 162 55_Re: Using LOAD with delimited data in DB2 V7.1 on S390.13_David S Waugh16_dsw-dba@JUNO.COM29_Fri, 19 Mar 2004 00:14:57 GMT758_- Samit:

Pardon me if I misunderstand the original question, but you can use DFSORT's OUTFIL CONVERT facility to reformat a dataset and convert it to fixed length. That would allow you to 'keep it in the MVS box'.

Check out these sources for tips & techniques:

http://www.storage.ibm.com/software/sort/mvs/tricks/srtmst03.html#t09

http://bama.ua.edu/cgi-bin/wa?A2=ind9911&L=ibm-main&P=R64066&I=1

HTH

David Waugh, NCW DSW Consulting & Services Former DB2 Sysprog, now clueless DB2 UDB Win/NT DBA =================================================== Computer One-Liners (from www.oneliners-and-proverbs.com) =================================================== A Life? Cool! Where can I download one of those from? [...] 21605 46 57_DB2 migration from V6 to v7 under datasharing environment23_=?big5?B?w1EgpKSopQ==?=17_j9wei@HOTMAIL.COM31_Fri, 19 Mar 2004 03:04:26 +0000274_big5 We want to migration our DB2 system from V6 to v7 under Data sharing environment. we need some suggestion from your guy, it is a great help for us to do this migration under data sharing environment. we make some plan to implement this task as following : Plan 1. [...] 21652 49 50_Re: Application algorithm and deadlock conundrum..16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 18 Mar 2004 21:47:15 -0800538_us-ascii Yes, I meant multiple invocations (threads) of the same transaction locking each other out. This transaction is actually executed around 20 times per minute. No triggers, no Select statements against table T1 or T2. SQLs in question (UPDATEs against T1 and T2) update on Primary key as predicate and so, no performance issues. Table T1 has around 60 rows (all on 1 page) and already has Row level locking. Table T2 has around 43k rows and has page locking. As these are 'transactions' intermediate commits is not an option. [...] 21702 80 28_Re: Opthint and version auto13_Johnny Mossin24_johnny.mossin@NORDEA.COM31_Fri, 19 Mar 2004 01:16:28 -0600586_- Thanks For idea, Glen.

I am working with a small REXX program and I will use your suggestion with an extra table.

Johnny Mossin.

On Wed, 17 Mar 2004 11:43:34 -0600, Glen Sanderson wrote:

>There is no easy way. We use a COBOL program prior to every bind to take opt hints from a staging table that either fills in the appropriate collection ids and version numbers as need be prior to the bind. We can then control opt hints to work for a program regardless of collection id or version or we can also specify opt hints for [...] 21783 19 39_Re: REBIND while a program is executing33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 19 Mar 2004 02:06:31 -0600456_- Hi

I cannot talk for stored procedures, but for normal packages. When you submit a job with rebinds, the rebind will wait for packages, which are just executed, but other application will receive a -904 immediate for this package, when they try to execute it without waiting. I think it is a 00C90090 reason code. And that happened, I think at least since DB2 V3. And I asked myself, why didn't the application wait until they get a timeout. [...] 21803 147 63_Re: Could and additional WHERE-Clause impact the sql-accespath.14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Fri, 19 Mar 2004 10:06:57 +0100588_iso-8859-1 Sample : SELECT FIELD1, FIELD2, FIELD3, FIEL

Horacia,

Sample : SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE1 WHERE 1=1 AND FIELD1 LIKE ?

As you can see in this sample, there's only one parameter ( to keep the sample easy ). If the programmer wants to use this parameter, he only has to give it the right value. If he doesn't provide a value the executed sql will be "select field1, field2, field3, field4 from table1 where 1=1 " otherwise it would be " select field1, field2, field3, field4 from table1 where 1=1 and field like 'abc%' ". You also [...] 21951 164 63_Re: Could and additional WHERE-Clause impact the sql-accespath.13_D'hoine Frank19_Frank.Dhoine@NBB.BE31_Fri, 19 Mar 2004 10:10:49 +0100688_iso-8859-1 -----Original Message----- Yes, kind regards, Frank

-----Original Message----- From: Steurs Patrick Sent: vrijdag 19 maart 2004 10:07 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Could and additional WHERE-Clause impact the sql-accespath.



Sample : SELECT FIELD1, FIELD2, FIELD3, FIEL

Horacia,

Sample : SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE1 WHERE 1=1 AND FIELD1 LIKE ?

As you can see in this sample, there's only one parameter ( to keep the sample easy ). If the programmer wants to use this parameter, he only has to give it the right value. If he doesn't provide a value the executed sql will be "select field1, field2, [...] 22116 76 16_Re: TSM Question13_Ealey, Paul M18_paul.ealey@EDS.COM31_Fri, 19 Mar 2004 09:36:06 -0000393_iso-8859-1 Prasad Use the DB2ADUTL utility to retrieve the backup from TSM. Then restore as normal. Here's an example:-

db2adutl query db dbname - this asks TSM to tell you about the backups it's got.

db2adutl extract full taken at timestamp db dbname - this retrieves the backup to the current path.

Get hold of red book: Backing Up DB2 Using Tivoli Storage Manager [...] 22193 18 33_Re: DB2 Prefetch failed no engine0_16_mscarpa@CESVE.IT31_Fri, 19 Mar 2004 10:46:16 +0100617_US-ASCII Just another thought, Missy:

Did you ever reached any critical threshold like DM for the offending BPs ?

Regards Max Scarpa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 22212 118 19_Re: Names for LPARS9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 19 Mar 2004 15:22:29 +0530414_iso-8859-1 Me and my fellow Admin wanted to use something like ZOS... , but higherups wanted something flashy , something that could be easily recalled. Thanks a lot for everyone who replied my mail.

And yes, I have much more meaningful names for Db2 subsystems as I have more control over them. SSID are typically DSNS , DBA1 , DSN1 , DB2P etc. Instances have names having Organization name prefixed. [...] 22331 151 19_Re: Names for LPARS13_Bell, Raymond22_raymond.bell@LANDG.COM31_Fri, 19 Mar 2004 10:21:58 -0000329_iso-8859-1 Hey Srinivas,

I hope you don't think I was having a go at you. It would appear I'm in the minority for preferring 'meaningful' names for LPARs. But then, I also was (am?) of the belief that LPAR names could only be four characters. Shows what I know. Call 'em what you like, both LPARs and DB2 subsystems. [...] 22483 142 16_Re: TSM Question14_Nelson, Philip34_Philip.Nelson@SCOTTISHWIDOWS.CO.UK31_Fri, 19 Mar 2004 10:27:10 -0000374_iso-8859-1 This E-Mail is sent in confidence for the addressee only. Unauthorised recipients must preserve this confidentiality and should notify the sender immediately by telephone on 0131-655-6789 and must delete the original E-Mail without taking a copy. If you are not the addressee you must not copy, distribute, disclose or use any of the information in any way. [...] 22626 49 39_Re: REBIND while a program is executing14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Fri, 19 Mar 2004 11:32:11 +0100705_iso-8859-1 Hello Walter,

are you shure that it was a simple REBIND? Or could it have been a BIND with REPLACE option instead?

Regards

Gerhard



-----Original Message----- From: Walter Janißen [mailto:walter.janissen@VICTORIA.DE] Sent: Friday, March 19, 2004 9:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: REBIND while a program is executing



Hi

I cannot talk for stored procedures, but for normal packages. When you submit a job with rebinds, the rebind will wait for packages, which are just executed, but other application will receive a -904 immediate for this package, when they try to execute it without waiting. I think it is a 00C90090 [...] 22676 38 50_Re: Application algorithm and deadlock conundrum..2_da15_bufes@YAHOO.COM31_Fri, 19 Mar 2004 04:31:44 -0600577_- On Thu, 18 Mar 2004 21:47:15 -0800, Raquel Rodriguez wrote:



> Table T1 has around 60 rows >(all on 1 page) and already has Row level locking. >Table T2 has around 43k rows and has page locking.

Raquel, in one of your previous posts you say:

>The 'two resources' >involved are always some page of table T1 and some >page of table T2. The program invoked by transaction >has algorithm along the following lines: > >Some Update against table T1 >lot of other application logic >Some Update against table T2 [...] 22715 209 19_Re: Names for LPARS9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 19 Mar 2004 16:12:13 +0530384_iso-8859-1 Hi, The LPAR name I wanted was more for DNS entry. On the Support Element, the LPARS are named differently. Anyway , I have had SSIDs in the past such as DSNS and DSNG .

Srinivas G

-----Original Message----- From: Bell, Raymond [mailto:raymond.bell@LANDG.COM] Sent: Friday, March 19, 2004 3:52 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Names for LPARS [...] 22925 17 39_Re: REBIND while a program is executing33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 19 Mar 2004 06:09:55 -0600319_- Gerd

Very good question. I must admit, that I don't know it for sure. Mostly, I did rebinds to improve the access path using SPUFI. So I think, it was a rebind, but sorry it could also be a bind replace.

The next time, this abend happens, I have a closer look, if I did a rebind or a bind replace. [...] 22943 71 50_Re: Application algorithm and deadlock conundrum..16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Fri, 19 Mar 2004 04:18:05 -0800567_us-ascii Sorry about the confusion. T1 is indeed defined as LOCKSIZE ROW and row in T1 is deadlocking against a page in T2.

Thanks. Raquel.



--- da wrote: > On Thu, 18 Mar 2004 21:47:15 -0800, Raquel Rodriguez > wrote: > > > > > Table T1 has around 60 rows > >(all on 1 page) and already has Row level locking. > >Table T2 has around 43k rows and has page locking. > > Raquel, > in one of your previous posts you say: > > >The 'two resources' > >involved are always some page of table [...] 23015 174 11_Re: Purcell23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 19 Mar 2004 12:20:45 -0000542_iso-8859-1 Hi Terry is very much about .. let me know if you want his IBM mail address , I will not post here for obvious reasons. Les

-----Original Message----- From: William Johnson [mailto:William.Johnson@ANTARESSOLUTIONS.COM] Sent: Thursday, March 18, 2004 8:23 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Purcell



Whatever happened to Terry Purcell? He taught a class in Pittsburgh that I took a couple of years ago and I used to see him answer SQL questions. I haven't seen him respond in quite some time? Thanks, [...] 23190 70 50_Re: Application algorithm and deadlock conundrum..12_Fay, Colin M22_cfay@UNUMPROVIDENT.COM31_Fri, 19 Mar 2004 07:53:11 -0500425_us-ascii Any chance these are deadlocks on the spacemap pages and not the data itself? Are these updates possibly changing the primary key or record length?



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raquel Rodriguez Sent: Friday, March 19, 2004 12:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Application algorithm and deadlock conundrum.. [...] 23261 12 50_Re: Application algorithm and deadlock conundrum..2_da15_bufes@YAHOO.COM31_Fri, 19 Mar 2004 07:16:36 -0600524_- Any referential constraints on those tables? Daniel

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 23274 250 19_Re: Names for LPARS15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Fri, 19 Mar 2004 09:06:26 -0500558_us-ascii Hmm... Flashy, eh? How 'bout an "animal/people sounds" theme? That'd give you plenty of names: GOBL (Gobble, as in turkey), QUAK (Duck), ROAR, GRWL (Growl), GRON (Groan), LAFF, YELL, SING, TALK, PURR, MUTR (Mutter)

--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com











SrinivasG Sent by: DB2 Data Base Discussion List 03/19/2004 04:52 AM Please respond to DB2 Database Discussion list at IDUG [...] 23525 30 51_Re: Concurrent Update between SELECT and SUBSELECT?10_Victor Gil18_Victor_Gil@ADP.COM31_Fri, 19 Mar 2004 08:37:25 -0600324_- Thanks to all those who replied to my question on UPDATE-with-SUBSELECT concurrency. It was most helpful.

Luckily, the application table has very few rows [all fit in one page]and is not accessed from online. This justified the use of LOCK TABLE IN EXCLUSIVE MODE which finally did solve the deadlock problem. [...] 23556 123 50_Re: Application algorithm and deadlock conundrum..13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 19 Mar 2004 08:46:59 -0600563_iso-8859-1 Raquel - can you clarify what you mean by "T1 is deadlocking against a page in T2"? I believe this was mentioned before, but *processes* deadlock, not pages - maybe a semantical difference at best, but does help to clarify the situation (for me anyway). When a deadlock occurs, DB2 will only record a single resource name in the MSTR -- I'm assuming this always shows T2? If so, is there anything that points to T1 as being a resource involved in the deadlock? (aside from the fact that it's the only other thing being updated?). My point here is [...] 23680 71 39_Re: REBIND while a program is executing11_Hardy, Dale14_DHardy@MIB.COM31_Fri, 19 Mar 2004 10:45:36 -0500405_iso-8859-1 Yes, it was a rebind command. It looks like our system was just too busy, the bind process took too long and the stored procedure timed out.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Heiss, Gerhard Sent: Friday, March 19, 2004 5:32 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: REBIND while a program is executing [...] 23752 418 37_00C90101 on Load Using Declared Curso12_Mark Cassity27_Mark.Cassity@MOTION-IND.COM31_Fri, 19 Mar 2004 11:25:10 -0600539_us-ascii V7.1 ZOS. We are developing a process to load several tables using declared cursors. These tables are read only summary tables used by our CICS and web applications and reloaded each night The typical process is to load replace the table with a declared cursor and group the data by our location id (500+). The next step is to summarize this data further at each location's accounting division level (35 or so) using load resume yes. The table used in the load resume yes declared cursor is the same table being loaded into. [...] 24171 195 42_Re: 00C90101 on Load Using Declared Cursor19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Fri, 19 Mar 2004 12:54:38 -0500514_US-ASCII

From: Utility Manual 2.12.1.4 Option descriptions for INTO TABLE









INCURSOR cursor-name | Specifies the cursor for the input data set. The cursor | must be declared before it is used by the LOAD utility. You | cannot load into the same table where you defined the # cursor. Use the EXEC SQL utility control statement to # define the cursor for the DB2 family cross loader function. | The INCURSOR option is incompatible with the SHRLEVEL | CHANGE option. [...] 24367 22 57_DB2 disk storage change from mirror to RAID - help needed13_Tim Brumfield23_brumfit@DFA.STATE.MS.US31_Fri, 19 Mar 2004 13:25:48 -0600428_US-ASCII Hello all, I have DB2 ESE v8 running on AIX 5.1 SP box. The DB is on IBM7133 SSA disks and is mirrored. Free disk space is running low and boss does not want to buy more. Instead they want to go from mirroring to RAID5 to get more space from the disks we have. Has anyone done this before? I'm just wondering how much work this would be, and if it can be done in increments. Any info would be greatly appreciated. [...] 24390 32 41_Re: 00C90101 on Load Using Declared Curso9_Jim Ruddy18_jaruddy@US.IBM.COM31_Fri, 19 Mar 2004 13:38:26 -0600344_- When loading into the same table the results are unpredictable - it happens to work for you because the result set is materialized at open cursor time because the group by causes a sort. In other cases you would lose all but the first internal buffer of data. That is why we have the following in the DB2 V7 Utility Guide and Reference: [...] 24423 58 61_Re: DB2 disk storage change from mirror to RAID - help needed12_McKown, John26_john.mckown@UICIINSCTR.COM31_Fri, 19 Mar 2004 13:48:10 -0600529_- > -----Original Message----- > From: Tim Brumfield [mailto:brumfit@DFA.STATE.MS.US] > Sent: Friday, March 19, 2004 1:26 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: DB2 disk storage change from mirror to RAID - help needed > > > Hello all, > I have DB2 ESE v8 running on AIX 5.1 SP box. The DB is on IBM7133 SSA > disks and is mirrored. Free disk space is running low and > boss does not > want to buy more. Instead they want to go from mirroring to > RAID5 to get > more space from the disks we have. Has anyone done this [...] 24482 103 11_Re: Purcell10_Ed Finnell23_efinnell@SEEBECK.UA.EDU31_Fri, 19 Mar 2004 14:00:45 -0600597_iso-8859-15 Whois.IBM.COM returns

TERENCE P. (Terry) PURCELL Email: tpurcel@us.ibm.com > Hi > Terry is very much about .. let me know if you want his IBM mail address > , I > will not post here for obvious reasons. > Les > > -----Original Message----- > From: William Johnson [mailto:William.Johnson@ANTARESSOLUTIONS.COM] > Sent: Thursday, March 18, 2004 8:23 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Purcell > > > Whatever happened to Terry Purcell? He taught a class in Pittsburgh that > I > took a couple of years ago and I used to see him answer SQL questions. I > haven't seen [...] 24586 30 54_DB2 7.1 for z/OS - getting -925 in CICS called program14_David R Slocum22_drslocum@CMSENERGY.COM31_Fri, 19 Mar 2004 15:44:42 -0500579_US-ASCII We have a client that has a CICS DB2 program that calls a second CICS DB2 program. The second program is hard linked into the first program. Both programs are bound with different packages. The first program (the driver) is bound with CS, and the second program is bound with UR. The client is getting a -925 in the second program. The manual says "An application executing in either an IMS or CICS environment or an application executing in an RRSAF environment when DB2 is not the only resource manager has attempted to execute a COMMIT statement. The SQL COMMIT [...] 24617 64 58_Re: DB2 7.1 for z/OS - getting -925 in CICS called program12_michael bell21_mbell11a1@VERIZON.NET31_Fri, 19 Mar 2004 14:56:32 -0600507_Windows-1252 It says that somewhere in the second program is an exec sql commit and it was executed. CICS has to be the commit coordinator so the COMMIT has to be removed from the subroutine. The only allowed COMMIT in CICS is the cics syncpoint.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of David R Slocum Sent: Friday, March 19, 2004 2:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 7.1 for z/OS - getting -925 in CICS called program [...] 24682 25 58_Re: DB2 7.1 for z/OS - getting -925 in CICS called program14_David R Slocum22_drslocum@CMSENERGY.COM31_Fri, 19 Mar 2004 16:06:04 -0500353_US-ASCII Michael Bell said:

"It says that somewhere in the second program is an exec sql commit and it was executed. CICS has to be the commit coordinator so the COMMIT has to be removed from the subroutine. The only allowed COMMIT in CICS is the cics syncpoint."

Yeah, I came to that conclusion too shortly after I sent out my note. [...] 24708 56 24_Error in UNLOAD FROMCOPY13_Horacio Villa17_hvilla@AR.IBM.COM31_Fri, 19 Mar 2004 18:20:05 -0300604_US-ASCII Hi list,

I'm running UNLOAD FROMCOPY (DB2 V7.1 not very current, OS/390 2.10). This is SYSIN for DSNUTILB: //SYSIN DD * TEMPLATE UNLDDS DSN BKDS2F.GENWK.TEST2.&DB..&TS..UN UNIT CARVTS3 STACK YES RETPD 02 VOLCNT (20) DISP(NEW,CATLG,DELETE) TEMPLATE PUNCHDS DSN STB.GENWK.TEST2.DS2F.&DB..&TS..P UNIT SYSDA SPACE TRK RETPD 2 DISP(NEW,CATLG,DELETE) UNLOAD TABLESPACE DBATEST.SCCDPSTE FROMCOPY BKDS2F.DBATEST.SCCDPSTE.P00000.F40316.H2302 PUNCHDDN PUNCHDS UNLDDN UNLDDS UNLOAD TABLESPACE DBATEST.SCRNGNTE FROMCOPY BKDS2F.DBATEST.SCRNGNTE.P00000.F40316.H2302 PUNCHDDN PUNCHDS UNLDDN UNLDDS [...] 24765 41 61_Re: DB2 disk storage change from mirror to RAID - help needed16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM31_Fri, 19 Mar 2004 14:22:05 -0700470_us-ascii Tim Brumfield wrote:

> Hello all, > I have DB2 ESE v8 running on AIX 5.1 SP box. The DB is on IBM7133 SSA > disks and is mirrored. Free disk space is running low and boss does not > want to buy more. Instead they want to go from mirroring to RAID5 to get > more space from the disks we have. Has anyone done this before? I'm just > wondering how much work this would be, and if it can be done in > increments. Any info would be greatly appreciated. [...] 24807 22 27_DSNTIAUL in DB2 7.2 for LUW13_Horacio Villa17_hvilla@AR.IBM.COM31_Fri, 19 Mar 2004 18:23:03 -0300746_US-ASCII Hi list,

is there something similar to DSNTIAUL in DB2 7.2 (for AIX 4.3.3, in this case)? I need to unload tables in fixed format (not comma delimited), something similar as DSNTIAUL does in MVS. LOAD utility supports an ASC file format, but EXPORT does not.

Tia,

Horacio Villa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at [...] 24830 67 61_Re: DB2 disk storage change from mirror to RAID - help needed13_Tim Brumfield23_brumfit@DFA.STATE.MS.US31_Fri, 19 Mar 2004 15:34:45 -0600545_US-ASCII Ian and John, thanks for the replies. I am using SMS storage and the database is only 150gigs in size. There are about 30 filesystems per node for 3 nodes. Sounds like the procedure would be: - backup everything, using both a db2 backup and aix type backups - reconfigure the disks as raid arrays - re-create all filesystems with same names as before - restore from aix backups - restore from db2 backup I guess db2 will not care that the volume groups may be different as long as the filesystems are the same. eh?? As for i/o and [...] 24898 308 63_Re: Could and additional WHERE-Clause impact the sql-accespath.13_Horacio Villa17_hvilla@AR.IBM.COM31_Fri, 19 Mar 2004 18:34:13 -0300499_US-ASCII Patrick,

I think the part I'm missing is that if the user does not use the parameter, the sql will the pruned ( "and field2 = ?" will not remain in the sql. Conceptually, if you don't use "where 1=1" would be the same.

Horacio Villa







Steurs Patrick cc: Sent by: DB2 Data Subject: Re: Could and additional WHERE-Clause impact Base Discussion the sql-accespath. List [...] 25207 37 31_Re: DSNTIAUL in DB2 7.2 for LUW16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM31_Fri, 19 Mar 2004 17:54:01 -0700308_us-ascii Horacio Villa wrote: > Hi list, > > is there something similar to DSNTIAUL in DB2 7.2 (for AIX 4.3.3, in this > case)? > I need to unload tables in fixed format (not comma delimited), something > similar as DSNTIAUL does in MVS. > LOAD utility supports an ASC file format, but EXPORT does not. [...] 25245 43 33_Re: Names for LPARS (It's Friday)13_David S Waugh16_dsw-dba@JUNO.COM29_Sat, 20 Mar 2004 04:43:31 GMT372_- OK, so call them ZOS1, ZOS2, ZOS3, etc.; then tell the higher-ups that it's pronounced Zeus 1, Zeus 2, Zeus 3, etc. If they're the typical image-over-substance type PHBs, they'll probably be thrilled with those names... After all, what could be more flashy (so to speak) than to name your systems after the supreme ruler of the Greek Gods, thrower of thunderbolts? [...] 25289 69 61_Re: DB2 disk storage change from mirror to RAID - help needed13_David S Waugh16_dsw-dba@JUNO.COM29_Sat, 20 Mar 2004 05:05:39 GMT458_- I agree. I'd recommend taking a good-sized two-by-four and laying it upside the boss's head. Then turn him over, empty his pockets, and use his loose change to buy more mirrored disks.

These days, DASD is cheap. It's people time that's expensive... How much will he be paying you to do this, and how much will the company be paying for the downtime while you do it? And how much will he be saving again by not buying the extra disks? Hmmmmm... [...] 25359 55 51_Re: Concurrent Update between SELECT and SUBSELECT?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 20 Mar 2004 10:41:21 +0200317_- Hi, Doing the exclusive lock in the program, will only affect those that use the program (due to the serialization). It will not affect anyone trying to update the table with another program (that has no such serialization mechanism). The method you describe is a sure way into locking problems along the way. [...] 25415 23 29_migrating jobs in UDB journal10_john lantz23_john.lantz@ZURICHNA.COM31_Sat, 20 Mar 2004 23:19:11 -0600406_- In preparation for upgrading to V8, we are migrating to a new server under V7 first. We need to copy everything from one server to another. The databases are covered via a backup/restore. But all of our scripts and scheduled jobs are another issue. I know all the scripts are stored under the DB2DAS00 directory. And it looks like the schedule information might be stored in a couple of JOB* files. [...] 25439 35 39_Shortage of space in Group Buffer Pools10_Alan Smith21_smithac@NILDRAM.CO.UK31_Sun, 21 Mar 2004 13:12:56 +0000426_US-ASCII Often when we run a lot of reorgs we get messages reporting a lack of space in the GBPs:

DSNB325A -DNN1 DSNB1CNE THERE IS A CRITICAL SHORTAGE 414 OF SPACE IN GROUP BUFFER POOL GBP5 DSNB319A -DNN1 DSNB1CNE THERE IS A SHORTAGE OF SPACE 694 IN GROUP BUFFER POOL GBP5

This can go on for some time before the problem clears itself. The group bufferpools that have the problem only have indexes in them. [...]