1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2005, week 1 2 179 42_Restrict access to rows for a set of users12_Maclane, Don22_don.maclane@BOEING.COM30_Sun, 1 May 2005 12:26:21 -0700519_us-ascii Further clarification:

General issue: We have a DB2 for z/OS V7 application that must to restrict access to rows for a set of users.

Details:

We have two groups of users. One group is allowed to access all the data in a table, the other group can only access a subset of rows. The group of users that can access all the rows belong to a RACF group (groupall). All other users cannot see the restricted rows. The restricted rows can be identified by a value of "M" in a column (colx). [...] 182 82 12_SQL Gurus...14_Randall Ibbott15_randall@QBE.COM30_Mon, 2 May 2005 17:01:00 +1000577_UTF-8 I was wondering if anyone could rewrite the following SQL in such a way as to enable it to return one row on DB2 for Z/OS (V7) - works fine on DB2 LUW, but not on Z/OS V7... I tried several times using a union and a case, but couldn't get anything returning only one row. select ibmreqd, ( SELECT count(*) as "count" FROM JPP.TBI_BDM ) as jppcount from sysibm.sysdummy1; Regards, Randall… “Enjoy the little things in life, for one day you may look back and realise they were the big things.” Robert Brault. Randall Ibbott. Senior Database Administrator Insure [...] 265 60 31_Re: STATSTIME on DB2 V7 on z/OS14_Dash, Sushanta21_Sushanta.Dash@FMR.COM30_Mon, 2 May 2005 03:24:27 -0400678_us-ascii Dear Raquel,

When u go with UPDATE SPACE only the catalog tables get updated are SYSTABLEPART, SYSLOBSTAT and SYSTABLES. Go with UPDATE ALL and verify that.



Thanks Sushant Dash Lead DBA Fidelity Business Services India Pvt. Ltd., Embassy Golf Links Business Park, Off Intermediate Ring Road, Bangalore - 560 071.

Any comments or statements made in this email are not necessarily those of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity Investments group companies. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. [...] 326 108 33_Online reorg fails on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Mon, 2 May 2005 01:55:11 -0700635_us-ascii We converted to Online reorgs few months back; yesterday night, one of our Reorgs failed with 00F30055 (The maximum number of concurrent identify level agents has been exceeded.). The tablespace TS1 has only one table TB1. This table has 8 indexes.

REORG TABLESPACE DB1.TS1 LOG NO SORTDATA NOSYSREC SORTKEYS COPYDDN (CPY1) SHRLEVEL CHANGE DEADLINE CURRENT TIMESTAMP + 0 YEARS + 0 MONTHS + 0 DAYS + 2 HOURS + 30 MINUTES DRAIN_WAIT 0 MAPPINGTABLE DB1.MP1 MAXRO 15 DRAIN ALL LONGLOG TERM DELAY 900 TIMEOUT TERM FASTSWITCH YES STATISTICS TABLE(ALL) SAMPLE 25 INDEX(ALL) REPORT NO UPDATE ALL HISTORY NONE SORTDEVT DEVD [...] 435 97 31_Re: STATSTIME on DB2 V7 on z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Mon, 2 May 2005 01:55:46 -0700592_us-ascii Thanks Sushanta. It worked fine.

Raquel.

--- "Dash, Sushanta" wrote: > Dear Raquel, > > When u go with UPDATE SPACE only the catalog tables > get updated are > SYSTABLEPART, SYSLOBSTAT and SYSTABLES. Go with > UPDATE ALL and verify > that. > > > Thanks > Sushant Dash > Lead DBA > Fidelity Business Services India Pvt. Ltd., > Embassy Golf Links Business Park, > Off Intermediate Ring Road, > Bangalore - 560 071. > > Any comments or statements made in this email are > not necessarily those > of Fidelity Business Services India Pvt. Ltd. [...] 533 58 37_Re: Online reorg fails on DB2 V7 z/OS14_Dash, Sushanta21_Sushanta.Dash@FMR.COM30_Mon, 2 May 2005 05:45:08 -0400364_us-ascii Hi Raquel, here are the details for you. The message 00F30055 not only meant the max batch connects but it considers max tso users and max users also. Form your note it seems to me that you perceived it only as IDBACK.

For question 1: The messages were thrown for as each sub tasks were connected parallel and your indexes are built parallel. [...] 592 63 41_Re: How to unload table with ROWID column17_SALVADORI Etienne17_esalvadori@GMF.FR30_Mon, 2 May 2005 11:49:44 +0200457_iso-8859-1 I'll suggest you to use unload reload (LOG NO) versus SQL as you'll expense very much io's with logging SQL INSERT ...

Etienne Salvadori

AZUR-GMF . SGBD Levallois . ISM 01-49-64-37-73



-----Message d'origine----- De : DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] De la part de Raj Ghose Envoy : vendredi 29 avril 2005 20:56 : DB2-L@WWW.IDUGDB2-L.ORG Objet : How to unload table with ROWID column [...] 656 126 37_Re: Online reorg fails on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Mon, 2 May 2005 03:26:01 -0700516_us-ascii Hi Sushanta,

Thanks for your detailed reply. My main question was: does each of the task/subtask result in a separate 'Connection' with DB2? Guess it remained unanswered, but with your reply, I think the answer is 'yes'.

Also, I have just one mapping index (why would anyone have more?).

I think the culprit here is the fact that STATS are being collected simultaneously. So, 1 task for STATs for each index + 1 task for STATs for tablespace. Hmm...that I think will add up to 27. [...] 783 259 30_Re: Disaster recovery software12_Haak, Enrico19_Enrico_Haak@BMC.COM30_Mon, 2 May 2005 05:59:42 -0500857_- Hi Paul,





not to forget: BMC is also offering a DR solution.





http://www.bmc.com/products/proddocview/0,,0_0_0_8563,00.html

and for subsystem recovery:

http://www.bmc.com/products/proddocview/0,,0_0_0_7678,00.html





Regards,

Enrico





_____

From: Fazio, Paul [mailto:Paul.Fazio@MUTUALOFAMERICA.COM] Sent: Mittwoch, 27. April 2005 17:31 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Disaster recovery software





Does anyone use Disaster Recovery Software to help recover the DB2 subsystem at a DR site? What do you use and how does it perform? Is it difficult to use? Thanks for any input. [...] 1043 132 16_Re: SQL Gurus...18_Reddy, Siva (TATA)39_Siva.Reddy@CONTRACTORS.NATIONALCITY.COM30_Mon, 2 May 2005 07:22:16 -0400662_utf-8

Try this one:

select ibmreqd, count from sysibm.sysdummy1, (SELECT count(*) as "count" FROM JPP.TBI_BDM) jppcount



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Randall Ibbott Sent: Monday, May 02, 2005 3:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL Gurus...





I was wondering if anyone could rewrite the following SQL in such a way as to enable it to return one row on DB2 for Z/OS (V7) - works fine on DB2 LUW, but not on Z/OS V7... I tried several times using a union and a case, but couldn't get anything returning only one row. [...] 1176 155 16_Re: SQL Gurus...13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Mon, 2 May 2005 07:30:04 -0400476_- Hello Randall,

I'm no SQL guru and haven't thought of a way to get the result you want in v7.

However, your query works fine in DB2 UDB v8 for z/OS. In v8, we get the scalar fullselect enabling exactly what you want to do.

Dave

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Randall Ibbott Sent: Monday, May 02, 2005 3:01 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL Gurus... [...] 1332 44 28_DB2 V7 z/OS: Left Outer Join33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Mon, 2 May 2005 07:03:46 -0500292_- Hi

I am just dealing with an interesting question. I want to do a double left outer join:

SELECT * FROM T1 A WHERE Col1 = ? AND Col2 = (SELECT MAX(Col2) FROM T1 B WHERE A.Col1 = B.Col1) AS Z LEFT OUTER JOIN T2 ON T1.Col1 = T2.Col1 LEFT OUTER JOIN T3 ON T2.Col2 = T3.Col1 [...] 1377 204 40_DB2 V7.1 Z/OS 1.4 Error running Runstats16_Proctor, William25_William.Proctor@TGSLC.ORG30_Mon, 2 May 2005 07:25:41 -0500456_us-ascii Good Morning,

Thanks for all replies in advance. I started getting the following error message last night when running runstats against two of our partitioned datasets. The datasets have 15 partitions with the largest being around 10,000,000 rows. This job has run fine for months on end. Has anyone else gotten this error before. Naturally the error message doesn't tell me much and I've been unable to find the reason code anywhere. [...] 1582 117 46_Re: Restrict access to rows for a set of users0_25_LL581@DAIMLERCHRYSLER.COM30_Mon, 2 May 2005 08:55:19 -0400420_US-ASCII Don,

Do you have any stored procedures that access this table? Authorization to execute the stored procedure is different from authorization to do table access.

Do you have any dynamic SQL access to this table? If so, is access via DDF by distributed applications that connect using a 'generic' application ID? If so, then that might not be enough information to determine who the 'user' is. [...] 1700 370 46_Re: Restrict access to rows for a set of users12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Mon, 2 May 2005 08:20:46 -0500318_us-ascii If the access is all via static SQL, then you could have have a view with the same name as the table, but a different owner (you'd have to create views or synonyms of all your other tables too) and bind with the new owner. This would require new plans (and collections if you use packages), though, too. [...] 2071 336 46_Re: Restrict access to rows for a set of users13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Mon, 2 May 2005 08:37:44 -0500531_iso-8859-1 Just to start with a disclaimer -- anytime you start with the restriction that you can only afford minimal code changes you are not going to end up with a very good solution. Having said that, you might be able to implement this through the use of one new table and a view. If you can create a new table that just contains the userids of everyone in the RACF group "groupall" then you can create a view that joins the base table with the new table. If you can't afford to change the SQL, then rename the base table [...] 2408 222 46_Re: Restrict access to rows for a set of users8_Ron Root24_ron.root@CPA.STATE.TX.US30_Mon, 2 May 2005 08:49:32 -0500426_us-ascii Message -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Maclane, Don Sent: Sunday, May 01, 2005 2:26 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Restrict access to rows for a set of users



Further clarification:

General issue: We have a DB2 for z/OS V7 application that must to restrict access to rows for a set of users. Details: [...] 2631 71 48_Surfing the web for CICS - and D*** tired of if.0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Mon, 2 May 2005 08:26:07 -0700835_- You know I hate using IBMs web site. Does anyone have the URL for CICS manuals, something like the DB2 site (http://www-306.ibm.com/software/data/db2/os390/v7books.html ) would be nice.

I have a user trying to execute a CICS transaction from a stored proc and he's getting a "RESP-SPOLBUSY" return code.

thanks much, cliff:-)



--------------------------------------------------------------------------------- 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 [...] 2703 214 52_Re: Surfing the web for CICS - and D*** tired of if.12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Mon, 2 May 2005 10:43:33 -0500540_us-ascii Here are all the releases of Transaction Server: http://www-306.ibm.com/software/htp/cics/library/.





I couldn't find pre-TS CICS. The link I had for 4.1 now goes to an announcement letter saying it's unsupported.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Maurice.C.BOLEY@ODOT.STATE.OR.US Sent: Monday, May 02, 2005 10:26 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Surfing the web for CICS - and D*** tired of if. [...] 2918 60 52_Re: Surfing the web for CICS - and D*** tired of if.15_Schade, Jeffrey15_JSchade@ISO.COM30_Mon, 2 May 2005 11:44:04 -0400694_us-ascii I always use the following link to look-up IBM manuals:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves?filter=os%2 52F3



Jeff _________________________________________ Jeffrey Schade Systems Consultant, Technology Engineering

Insurance Services Office, Inc. 545 Washington Boulevard Jersey City, NJ 07310 Voice: (201) 469-3738 FAX: (201) 748-1500 jschade@iso.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Maurice.C.BOLEY@ODOT.STATE.OR.US Sent: Monday, May 02, 2005 11:26 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Surfing the web for CICS - and D*** tired of if. [...] 2979 15 41_Re: DB2 7.1 OS/390 online reorg questions9_Jim Ruddy18_jaruddy@US.IBM.COM30_Mon, 2 May 2005 10:51:40 -0500719_- Just thought I would mention that if V8 Online REORG win inline statistics should fail (usually because it is not possible to drain users for SWITCH phase) that the catalog statistics will not be updated.

Jim Ruddy DB2 for z/OS Development

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". 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 2995 80 52_Re: Surfing the web for CICS - and D*** tired of if.0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Mon, 2 May 2005 09:11:07 -0700407_- Found them, thanks guys. It seem that whenever I go to the IBM web site its like 'Alice in Wonderland' I go to all kinds of weird places and never get a straight answer.

cliff:-)

-----Original Message----- From: Schade, Jeffrey [mailto:JSchade@ISO.COM] Sent: Monday, May 02, 2005 8:44 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Surfing the web for CICS - and D*** tired of if. [...] 3076 256 44_Re: DB2 V7.1 Z/OS 1.4 Error running Runstats12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 2 May 2005 19:17:56 +0200749_us-ascii Hi,

Ijust googled IEF450I - interesting result (address space abend ...)



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Monday, May 02, 2005 2:26 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V7.1 Z/OS 1.4 Error running Runstats





Good Morning,

Thanks for all replies in advance. I started getting the following error message last night when running runstats against two of our partitioned datasets. The datasets have 15 partitions with the largest being around 10,000,000 rows. This job has run fine for months on end. Has anyone else gotten this error before. Naturally the [...] 3333 48 41_Re: DB2 7.1 OS/390 online reorg questions12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Mon, 2 May 2005 11:28:32 -0500569_us-ascii That's a good change -- the way I thought it would work. Thanks for passing the word.

Tina

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim Ruddy Sent: Monday, May 02, 2005 10:52 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 7.1 OS/390 online reorg questions

Just thought I would mention that if V8 Online REORG win inline statistics should fail (usually because it is not possible to drain users for SWITCH phase) that the catalog statistics will not be updated. [...] 3382 114 44_Re: DB2 V7.1 Z/OS 1.4 Error running Runstats12_Bill Shipley28_william_shipley@VANGUARD.COM30_Mon, 2 May 2005 12:32:34 -0400680_UTF-8

Bill , I found the following on QuickRef, I only included the 1212 reason code:

Item ==> * MVS/QuickRef 6.3 * Col 1 Line 1 of 335 Command ==> Scroll ==> CSR You may scroll the information below UP, DOWN, LEFT, and/or RIGHT as needed ------------------- V=IBM P=Z/OS SYSTEM CODES R=V1R6 I=052 ------------------- ********************* Text Below Copyright (c) 2005, IBM ********************* 052 Explanation: A program incorrectly issued a macro to request processing by Program Call/authorization (PC/AUTH) service routines. Register 15 contains a hexadecimal reason code in the form xxyy, where x identifies the macro that was issued, and yy identifies [...] 3497 215 19_Re: ASCII tables V733_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Mon, 2 May 2005 19:48:36 +0200640_iso-8859-1 For the archive.

The problem was caused by an entry in the SYSSTRINGS loaded by someone sometimes for unknown reason. So far it seems the wrong entry was based on codepage 273<-1252.

I delete the wrong entry and now I can see the german umlauts.

Cut and paste from PMR

Roland



I setup a testenviroment with SCCSID=273 and ASCCSID=850 but I wasn't able to recreate. Talked with Mr. Schiradin to check the entries in SYSIBM.SYSSTRINGS. We determined that there are 2 entries for INCCSID = 850 and OUTCCSID=273 and the userdefined entry is incorrect. Mr. Schiradin will check for what [...] 3713 147 49_Have you tried the IBM LOOKAT Web message service14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Mon, 2 May 2005 11:03:59 -0700402_us-ascii I found a very cool IBM tool that allows me to look up messages. Its a MVS OS/390 tool but seems to support DB2 Called "Lookat" http://www-1.ibm.com/servers/eserver/zseries/zos/bkserv/lookat/

There a a moble edition as well so at last I have some use on the low band width web interface for my Palm Treo. http://www-1.ibm.com/servers/eserver/zseries/zos/bkserv/lookat/lookatm.html [...] 3861 66 55_DSNSLD1 Storage Management Driver (DB2 V7, z/OS 1.6.0.)15_Moss, William R15_MossW@AETNA.COM30_Mon, 2 May 2005 14:26:30 -0400286_US-ASCII I've been researching a long running batch job. Strobe shows 99% of the CPU in DSNSLD1. I've looked in all of the DB2 manuals to learn more about this module, but havn't found anything. The program does a lot of inserts and updates. Our environment is DB2 V7, z/OS 1.6.0. [...] 3928 19 27_Re: db2 with data warehouse3_lan18_lxw176@HOTMAIL.COM30_Mon, 2 May 2005 13:22:08 -0500749_- Hi,

I was told that the data warehouse I'm trying connecting to is sit on Operating System - Windows 2000, Server Database - SQL Server 2000. Could you give me some detailed information on how to connect? I'm using DB2 UDB version 8.1 on aix machine.

Thanks! Lan

--------------------------------------------------------------------------------- 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 3948 43 27_Re: db2 with data warehouse18_Lombardozzi, Bryan25_Bryan_Lombardozzi@CSX.COM30_Mon, 2 May 2005 14:47:05 -0400336_us-ascii There are 2 ways to do this. The easy way is to use the en Hi,

There are 2 ways to do this. The easy way is to use the enterprise manager that comes with SQL Server. If they want to use the DB2 Client then they have to go into the configuration assistant then go into the configure option to register the database. [...] 3992 71 59_Re: DSNSLD1 Storage Management Driver (DB2 V7, z/OS 1.6.0.)15_Basivi Inaganti22_INAGANB@NATIONWIDE.COM30_Mon, 2 May 2005 15:06:31 -0400403_UTF-8 It is load module. You need to find out CSCET under DSNSLD1 module which took more CPU. Some where in your Strobe report it should list the CSECT names, may be under 'PROGRAM USAGE BY PROCEDURE' report. It’s been a while I used Strobe.

From DSNWMODS in SDSNSAMP, it is part of STORAGE MANAGER Subcomponent of DB2 and the description says 'MODULE ENTRY POINTS (NONEXECUTABLE CSECT)'. [...] 4064 213 64_what should Max Users, Max TSO connect, and Max Batch be set at?14_Neff Stephen R22_Stephen.R.Neff@IRS.GOV30_Mon, 2 May 2005 15:32:22 -0400495_- H ello,

We ran simultaneous batch image copy jobs and two of the job got

DSNU028I DSNUTILB - UNABLE TO CONNECT TO DB2, REASON=X'00F30055'

DSNU012I DSNUTILB - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8

The error message for this code is

00F30055





Explanation: The maximum number of concurrent identify level agents has

been exceeded.





This abend code is issued by the following CSECT: DSN3ID30 [...] 4278 165 67_Re: what should Max Users, Max TSO connect, and Max Batch be setat?12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Mon, 2 May 2005 16:26:15 -0400486_us-ascii Steve, There parms are specified within DSNZPARM in your samplib. Also most monitors should diplay what they are also. These parms are dynamically updateable i believe. IDBACK is max batch connections, IDFORE is max tso connections, CTHREAD is max users. HTHs Jeff







Neff Stephen R Sent by: DB2 Data Base Discussion List 05/02/2005 03:32 PM Please respond to DB2 Database Discussion list at IDUG [...] 4444 41 64_FW: Which Columns Updated (?) via Trigger << z/OS 1.4 DB2 V7.1>>15_Gaston, Raymond17_GastonRay@ORU.COM30_Mon, 2 May 2005 17:15:48 -0400496_iso-8859-1 > Dear DB2Listers: > > Is there any way that we can determine from an Update Trigger, what column(s) of a row were updated without having to hardcode the column names in the Create Trigger statement. That is, if we have a 100 column row, we don't want to code: > > CREATE TRIGGER TRIGNAME > ... AFTER UPDATE OF COL1, COL2, COl3... COL100 ON MYTABLE > > : > : > : > WHEN O.COL1 <> N.COL1... > : > : > WHEN O.COL2 <> N.COL100... > > etc. > > We also would rather not have to create [...] 4486 202 68_Re: what should Max Users, Max TSO connect, and Max Batch be set at?13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Mon, 2 May 2005 16:54:17 -0500577_us-ascii Take a look at you DSNZPARM member (might be in job DSNTIJUZ in DSNSAMP) and tell us what the values of CTHREAD, IDFORE, and IDBACK are on the DSN6SYSP macro...

Willie

Neff Stephen R wrote:

> H ello, > > We ran simultaneous batch image copy jobs and two of the job got > > DSNU028I DSNUTILB - UNABLE TO CONNECT TO DB2, REASON=X'00F30055' > > DSNU012I DSNUTILB - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8 > > The error message for this code is > > 00F30055 > > > > Explanation: The maximum number of concurrent identify level agents [...] 4689 32 28_Runstat tasks on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Mon, 2 May 2005 23:43:48 -0700440_us-ascii I had asked a similar questions yesterday but let me rephrase it.

A segmented tablespace (single table) with 8 indexes defined on it is Runstated (TABLE ALL INDEX ALL UPDATE ALL).

How many tasks/subtasks will it start? Any place where I can find documentation in this regard? I believe each task/subtask will acquire a separate connection with DB2 (and will require as many IDBACK entries to be accommodated). [...] 4722 19 66_Could someone let me know a similar functional Listserv for Oracle16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Mon, 2 May 2005 23:46:39 -0700654_us-ascii





__________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--------------------------------------------------------------------------------- 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 4742 28 70_Re: Could someone let me know a similar functional Listserv for Oracle11_Grant Allen28_Grant.Allen@TOWERSOFT.COM.AU30_Tue, 3 May 2005 16:45:29 +1000383_iso-8859-1 > Could someone let me know a similar > functional Listserv > for Oracle

Best one by far is Oracle-l, hosted at freelists.org. You can subscribe at the website, or read it for email subscription instructions.

Ciao Fuzzy :-)

---------------------------------- "Everything you think, do, and say was in the pill you took today." -- Zager and Evans [...] 4771 90 32_Re: Runstat tasks on DB2 V7 z/OS13_Michael Ebert18_mebert@AMADEUS.NET30_Tue, 3 May 2005 09:27:24 +0200395_ISO-8859-2 RUNSTATS is a single task. The TS and indexes are processed one after the other, not in parallel by separate subtasks. If you have problems with the number of connections, just up the corresponding ZPARMs. We've got: CONDBAT=500,CTHREAD=500,IDBACK=300, IDFORE=200, MAXDBAT=500.

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 4862 28 27_UDB 7.2 AIX Trigger problem13_Ealey, Paul M18_paul.ealey@EDS.COM30_Tue, 3 May 2005 09:03:38 +0100398_iso-8859-1 A complex atomic trigger is returning -811 (db2 returned more than one row when only one row was expected). How do I determine which part of my trigger is causing this? I've tried using event monitor to capture statements but it didn't show up in the output. I've been through the sql in the trigger but can't spot the offending statement. Is there a tool or trick available to me? [...] 4891 40 52_Re: Surfing the web for CICS - and D*** tired of if.13_Julian Levens18_Julian@CAPSCAN.COM30_Tue, 3 May 2005 10:05:03 +0100884_us-ascii I prefer:

http://www-1.ibm.com/servers/eserver/zseries/zos/bkserv/find_shelves.html

Julian

> I always use the following link to look-up IBM manuals: > > http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves?filter=os%2 > 52F3 > > > Jeff > _________________________________________ > Jeffrey Schade > Systems Consultant, Technology Engineering

***** Matchcode International awarded the "Best International Product" by Direct Marketing Business at the IDMF 2003 *****

******************************************************************* This e-mail is confidential and intended solely for the use of the individual to whom it was addressed. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this message is strictly prohibited. Please delete any copies you may have, [...] 4932 154 67_Re: what should Max Users, Max TSO connect, and Max Batch be setat?13_Martin Packer24_martin_packer@UK.IBM.COM30_Tue, 3 May 2005 10:40:42 +0100439_US-ASCII And when setting them you should think carefully about the memory cost of a given number of threads. Most sites I work with have unrealistically hight values for these parameters: If all these threads showed up the subsystem would keel over.

On the other hand: Some applications take "rejection" poorly so a failed attempt to connect can be unpleasant. Then you're into using internal throttles in these applications. [...] 5087 97 12_SQL question16_Minnie, Louwrens17_lminnie@FNB.CO.ZA30_Tue, 3 May 2005 12:10:07 +0200460_us-ascii Hi All

Another SQL question:

Environment: DB2 V7.1 z/OS 1.4.

The table has around 300M rows and with 31 partitions, one for each calendar date. With an index on column DATE_KEY. This table has the following views defined on it:

1. CREATE VIEW CREATOR.VIEW1 AS SELECT * FROM CREATOR.TABLE1 WHERE DATE_KEY BETWEEN 20050401 AND 20050430 UNION ALL SELECT * FROM CREATOR.TABLE2 WHERE DATE_KEY BETWEEN 20050401 AND 20050430 [...] 5185 18 16_Re: SQL question33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Tue, 3 May 2005 05:55:57 -0500492_- Hi

I am not sure, if I understand things right. You have an index on a partitioned tablespace with only one column. A select with a special value for that index will result in an index access for view1 and view2, but not for view3. I am wondering, why DB2 used that index at all, because it has to scan the entire partition, so a tablespace scan using page range scan would be more feasable. Is this the access path chosen for view3? Does the viewname appear in the plan_table? [...] 5204 138 16_Re: SQL question14_Seth Bienstock19_sethdb2@COMCAST.NET30_Tue, 3 May 2005 11:40:15 +0000417_- Louwrens:

One would expect VIEW3 to yield a substantially different access path (and more costly!) since in that case VIEW1 would have to be MATERIALIZED. This is caused by the fact that in this case you have not only column functions (i.e. SUM), but a GROUP BY as well.

With the other two views, DB2 is able to combine your initial SQL statement with VIEW1/VIEW2 to process it more efficiently. [...] 5343 122 16_Re: SQL question16_Minnie, Louwrens17_lminnie@FNB.CO.ZA30_Tue, 3 May 2005 13:59:36 +0200724_iso-8859-1 Hi

Apologies!!!!

There is another view between VIEW2 and VIEW3, doing the following: CREATE VIEW CREATOR.VIEW2B AS SELECT 'POSTED BALANCES' AS BAL_TYPE, ...,DATE_KEY, COL1, COL2......... FROM CREATOR.VIEW2 UNION ALL SELECT 'LATE ADJUSTMENT' AS ...., DATE_KEY, COL!,COL2 FROM CREATOR.TABLE3

TABLE3 is also indexed on DATE_KEY.

VIEW3 then becomes:

CREATE VIEW CREATOR.VIEW3 AS SELECT ....., F.DATE_KEY, CASE WHEN COALESCE( SUM (F. COL1),0) < 0 THEN COALESCE(SUM ( COL1),0) ELSE 0 END AS COL1A, CASE WHEN COALESCE(SUM (F.COL1),0) > 0 THEN COALESCE(SUM (COL1),0) ELSE 0 END AS COL1B, COALESCE(SUM ( F.COL2),0) AS COL2B, COALESCE(SUM ( F.COL3),0) AS COL3B FROM CREATOR.VIEW2B F [...] 5466 226 16_Re: SQL question9_Agus Kwee19_askwe@OPTONLINE.NET30_Tue, 3 May 2005 08:06:23 -0400412_iso-8859-1 Louwrence,

When you are using VIEW1 and VIEW2 the evental predicate is DATE_KEY BETWEEN which according to the Predicate types and processing table 68 in the Application Programming SQL Guide Manual ia an indexable predicate.

When you are using VIEW3 , you specify : WHEN COALESCE ... < 0 In table 68, this is represented by: expression op value which is specified as nonindexable. [...] 5693 155 16_Re: SQL question33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Tue, 3 May 2005 07:14:39 -0500670_- indexable and non-indexable only hold for predicates, not for elements in a select-list.

On Tue, 3 May 2005 08:06:23 -0400, Agus Kwee wrote:

>Louwrence, > >When you are using VIEW1 and VIEW2 the evental predicate is >DATE_KEY BETWEEN which according to the Predicate types and >processing table 68 in the Application Programming SQL Guide >Manual ia an indexable predicate. > >When you are using VIEW3 , you specify : >WHEN COALESCE ... < 0 >In table 68, this is represented by: >expression op value >which is specified as nonindexable. > >Agus Kwee >Themis Training >http://www.themisinc.com > -------------- Original message [...] 5849 190 16_Re: SQL question14_Seth Bienstock19_sethdb2@COMCAST.NET30_Tue, 3 May 2005 12:37:33 +0000588_- Also, there is an equals predicate (date_key = 20050403 ) which IS indexable and can get merged. -------------- Original message --------------

> indexable and non-indexable only hold for predicates, not for elements in a > select-list. > > On Tue, 3 May 2005 08:06:23 -0400, Agus Kwee wrote: > > >Louwrence, > > > >When you are using VIEW1 and VIEW2 the evental predicate is > >DATE_KEY BETWEEN which according to the Predicate types and > >processing table 68 in the Application Programming SQL Guide > >Manual ia an indexable predicate. > > > >When you are using VIEW3 , [...] 6040 54 41_DB2 V7 on zOS: selective partition access13_Michael Ebert18_mebert@AMADEUS.NET30_Tue, 3 May 2005 14:40:50 +0200428_US-ASCII Hi List,

we have a big partitioned TS. It has 254 partitions, but only the first 100 hold data: about 1.5 GB each (compressed), approx. 7M rows per partition. The partitioning index is (NUPARTITION SMALLINT, SUMMARY_ID CHAR(33)). Only NUPARTITION is specified in the VALUES clause. Effectively NUPARTITION=n selects the nth partition. One partition holds data for one day. They are recycled after 100 days. [...] 6095 131 31_Re: UDB 7.2 AIX Trigger problem11_Jay Reavill23_Jay.Reavill@CERTEGY.COM30_Tue, 3 May 2005 09:01:00 -0400443_iso-8859-1 Not that this helps much (and maybe you've already done it), but what I would do is break the SQL into logical pieces and execute them one at a time adding an additional piece of the overall atomic logic each time till it breaks.

---------------------------------------- Jay Reavill DBA Certegy Card Services 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 ---------------------------------------- [...] 6227 95 39_Re: DB2 LOAD RESUME YES SHRLEVEL CHANGE14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 3 May 2005 14:47:53 +0100394_us-ascii Hi Debra

Some thoughts

I guess you are running these loads in parallel to get them done faster, but as you say, they all have to be SHRLEVEL(CHANGE) otherwise they will contend with each other.

If no other access is required to the data, have you timed a LOAD SHRLEVEL(NONE) with no parallelism just to see if it is actually slower - it might even be faster. [...] 6323 44 37_Which Columns Updated (?) via Trigger15_Gaston, Raymond17_GastonRay@ORU.COM30_Tue, 3 May 2005 09:51:29 -0400542_iso-8859-1 << RESUBMISSION: This was sent out late yesterday with no responses, so we thought that we'd give it another try. Thanks>>



> Dear DB2Listers: << z/OS 1.4 DB2 V7.1>> > > Is there any way that we can determine from an Update Trigger, what column(s) of a row were updated without having to hardcode the column names in the Create Trigger statement. That is, if we have a 100 column row, we don't want to code: > > CREATE TRIGGER TRIGNAME > ... AFTER UPDATE OF COL1, COL2, COl3... COL100 ON MYTABLE > > : > : > : > [...] 6368 70 31_Re: UDB 7.2 AIX Trigger problem13_Ealey, Paul M18_paul.ealey@EDS.COM30_Tue, 3 May 2005 15:00:59 +0100416_iso-8859-1 We did this and it worked.

Interestingly it highlighted a restriction on UPDATE which I had not encountered before. I could not find the restriction in the SQL Reference. My colleague found it in Graeme Birchall's SQL Cookbook. This is what caused the -811:-

"One can also have an update refer to the output of a select statement - as long as the result of the select is a single row:" [...] 6439 34 33_REASON 00C90088 and Type 0000030212_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 3 May 2005 16:05:56 +0200822_- Collegues,

environment: DB2 for z/OS Version 7.

A CICS transaction ran into an deadlock (= 00C90088), the type is 00000302.

And I cannot find this type. Is it a deadlocked page, an index or what ?

Any comments, thoughts, experiences are highly appreciated. Thanks in advance.

With kind regards - mit freundlichen Gruessen, Georg H. Peter c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- >We don't make mistakes - only happy accidents.< [...] 6474 15 37_Re: REASON 00C90088 and Type 0000030210_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 3 May 2005 16:12:43 +0200539_US-ASCII Here is indicated as tablespace page (V6)

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 6490 65 37_Re: REASON 00C90088 and Type 0000030214_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 3 May 2005 15:12:11 +0100516_us-ascii The V7 msg manual has a 302 as a "tablespace page" (as opposed to a 300 which is just a "page"!)



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: 03 May 2005 15:06 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] REASON 00C90088 and Type 00000302 [...] 6556 55 37_Re: REASON 00C90088 and Type 0000030250_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM30_Tue, 3 May 2005 10:12:58 -0400441_- The last time I saw this was the following :

DSNI011I % PAGE TEMPORARILY UNAVAILABLE TYPE 00000302 NAME DBBD .TSBDUSC .X'00000002' MODNAME DSNIOST2 ERQUAL 0C01

Bob Knight



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: Tuesday, May 03, 2005 10:06 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] REASON 00C90088 and Type 00000302 [...] 6612 45 45_AW: [DB2-L] REASON 00C90088 and Type 0000030212_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 3 May 2005 16:13:38 +0200518_iso-8859-1 Thanks, Max.

With kind regards - mit freundlichen Gruessen, Georg ---------------------------------------------------------------------- We don't make mistakes - only happy accidents.

-----Ursprngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Max Scarpa Gesendet: Dienstag, 3. Mai 2005 16:13 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] REASON 00C90088 and Type 00000302



Here is indicated as tablespace page (V6) [...] 6658 93 37_Re: REASON 00C90088 and Type 0000030215_Pierre R Bodart23_Bodart_Pierre_R@CAT.COM30_Tue, 3 May 2005 16:17:38 +0200655_US-ASCII Peter,

DB2 V7 messages and codes

APPENDIX1.2.2 Resource types |---------------+---------------------------------------+-----------------| | 00000302 | Table space page | DB.SP.PG | | | | | |---------------+---------------------------------------+-----------------|





HTH,

Pierre







"Peter, Georg" Sent by: DB2 Data Base Discussion To List DB2-L@WWW.IDUGDB2-L.ORG



05/03/2005 16:05 Subject [DB2-L] REASON 00C90088 and Type Please respond to 00000302 DB2 Database Discussion list at IDUG [...] 6752 168 52_AW: [DB2-L] again: REASON 00C90088 and Type 0000030212_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 3 May 2005 16:25:05 +0200463_iso-8859-1 Thanks to all who have answered my qestion.

The table behind this tablespace contains a lot of "small" rows (reclength 204) and because of this reclength one page contains a lot of rows.

Does it make sense to change this tablespace via ALTER TABLESPACE to row level locking ?

Thanks in advance.

With kind regards - mit freundlichen Gruessen, Georg ---------------------------------------------------------------------- [...] 6921 377 68_Re: what should Max Users, Max TSO connect, and Max Batch be set at?14_Neff Stephen R22_Stephen.R.Neff@IRS.GOV30_Tue, 3 May 2005 10:56:20 -0400430_- IDBACK = 300, IDFORE = 75, & CTHREAD = 500. There are over 20 batch jobs that were submitted that run image copies simultaneously. All were submitted about the same time on about 3:30 am. when there was no DB2 activity aside from these jobs. The PARALLEL statement is used and there is no number associated with it. Some of the image copy jobs are on partitioned table spaces. Are these normal values for these parameters? [...] 7299 41 45_Re: DB2 V7 on zOS: selective partition access15_Patrick Bossman18_bossman@US.IBM.COM30_Tue, 3 May 2005 09:56:28 -0500324_- Hello, I had a post a week or so ago on the deleting rows from a partitioning table, that was relevant to this issue.

DB2 for z/OS V7 only uses the leading column of the partitioning index to determine qualified partitions. If you have a multi-column partitioning index, then n and n+1 partitions may qualify. [...] 7341 20 45_Re: DB2 V7 on zOS: selective partition access33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Tue, 3 May 2005 09:58:58 -0500363_- Michael

As I understand LOCKPART, it has only something to do with locking, but has no influence on access path. At our site, we always use LOCKPART NO and nevertheless we have got page range scans.

But, I cannot tell you, why you don't get a page range scan. Did you try to code the statement with just the NUPARTITION = constant predicate? [...] 7362 137 16_Re: SQL question13_Fleming, Mark21_MFLEMING@ALLSTATE.COM30_Tue, 3 May 2005 10:21:47 -0500561_US-ASCII Louwrens,

If I have this correct:

1. You have view1 on table1 union table2. This view will use MERGE uses the indexes you have on table1 and table2. 2. You have view2 on view1. There are no indexes for views, just for tables. No indexes are used in view2 directly, but will in view1. Indirectly, the indexes are used. MERGE can be used here, too. 3. You have view3 on view2. Ditto what I said in #2. In addition, materialization will be used on view3 because of the GROUP BY and column function (SUM). Even so, the indexes on table1 [...] 7500 117 18_cancel thread rexx12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Tue, 3 May 2005 10:42:37 -0500398_us-ascii Hi everyone. I just wanted to let you know that the updated and improved version of my rexx to cancel threads is now in the Code Place. It's been tested under both version 7 and version 8, but let me know if you find any problems. If you have my old version, I'd recommend you replace it with this one as it has a much more polished look and a lot of unnecessary code was eliminated. [...] 7618 27 32_Re: Runstat tasks on DB2 V7 z/OS9_Jim Ruddy18_jaruddy@US.IBM.COM30_Tue, 3 May 2005 10:54:03 -0500527_- RUNSTATS will only use a single task.

However, if you were to collect statistics at the same time as REORG TABLESPACE of a segmented tablespace (single table) with 8 indexes there would be at most a main task, a task to collect data statistics, a sort task for each index (total of 8), a build task for each index (8 more), and a statistics task for each index (8 again) for a total of 26 tasks - you would need IDBACK set to a bit more than 26 because the algorithm to determine the number of tasks tries to not [...] 7646 42 6_Lawson11_Craig Otway18_otwayc@EDNET10.NET30_Tue, 3 May 2005 11:21:05 -0500208_iso-8859-1 I am interested to know if any shop is running Lawson software, where DB2 on the mainframe is the db server.

--------------------------------------------------------------------------------- 7689 24 45_UDB V8.2 - STATISTICS_PROFILE - Mark Horrocks13_Mark Horrocks22_agentlease@HOTMAIL.COM30_Tue, 3 May 2005 16:27:17 +0000720_- Guys,

Is there anyway to set the profile for table to be NULL, not obvious from the manual.

i.e. make the STATISTICS_PROFILE become null.

This is UDB V8.2 on UNIX.

Many Thanks,

Mark Horrocks. Mobile: 07973-786657

--------------------------------------------------------------------------------- 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 7714 48 49_Re: UDB V8.2 - STATISTICS_PROFILE - Mark Horrocks14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Tue, 3 May 2005 12:44:45 -0400393_- Hi Mark,

How are you doing ?

Did you try using the 'SET PROFILE NONE' option of the RUNSTATS command ?

Abhijit

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Horrocks Sent: Tuesday, May 03, 2005 12:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] UDB V8.2 - STATISTICS_PROFILE - Mark Horrocks [...] 7763 19 22_Re: cancel thread rexx0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Tue, 3 May 2005 11:59:26 -0500626_US-ASCII Hi Tina!

Thanks for the contribution! Where is "the code place"? Is it DB2-L Documents? If so, I couldn't find it there.



Stg

--------------------------------------------------------------------------------- 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 7783 70 22_Re: cancel thread rexx13_Mertens, Bart18_bart.mertens@CZ.NL30_Tue, 3 May 2005 19:01:29 +0200588_- http://www.idug.org/user/userlogin.asp

met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl



-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Steve_Grimes@AISMAIL.WUSTL.EDU Verzonden: Tuesday, May 03, 2005 6:59 PM Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Re: [DB2-L] cancel thread rexx

Hi Tina!

Thanks for the contribution! Where is "the code place"? Is it DB2-L Documents? If so, I couldn't find it there. [...] 7854 66 32_Re: DB2 V7 z/OS: Left Outer Join13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 3 May 2005 15:43:09 -0500403_- Hi Walter,

Provided X.Col1 comes from T2, then these 2 SQLs should be logically equivalent (but if Col1 also exists in T3 then you would receive an error message anyway because of the ambiguous column reference).

To answer your other question:

QUESTION: What will be done with the NULLs, I get, when there is no hit in T2 for T1.Col1. Then T2.Col2 is NULL, joined to T3. Hm? [...] 7921 247 56_Re: AW: [DB2-L] again: REASON 00C90088 and Type 0000030213_Robert Tilkes22_TILKESR@NATIONWIDE.COM30_Tue, 3 May 2005 16:21:49 -0500318_ISO-8859-1 Peter,

Before considering Row Level locking you might want to look at setting the maxrows per page as an option in your tablespace definition. We have used it at our shop here with great success. Keep in mind with row level locking there is a lot of overhead involved verses page level locking. [...] 8169 60 45_Re: DB2 V7 on zOS: selective partition access15_Patrick Bossman18_bossman@US.IBM.COM30_Tue, 3 May 2005 23:08:38 -0500444_- Ok, I fall on my sword. I created 3 testcases.

-- single column PI CREATE INDEX IXPT01 ON TBPT01 (C1 ) CLUSTER ( PART 1 VALUES( '01' ) , PART 2 VALUES( '02' ) , PART 3 VALUES( '03' ) , PART 4 VALUES( '99' ))

-- 2 column PI with limit key on both columns CREATE INDEX IXPT02 ON TBPT02 (C1, C2 ) CLUSTER ( PART 1 VALUES( '01', '99' ) , PART 2 VALUES( '02', '99' ) , PART 3 VALUES( '03', '99' ) , PART 4 VALUES( '99', '99' )) [...] 8230 139 21_testmail...plz ignore12_Shanker_Ravi23_Shanker_Ravi@SATYAM.COM30_Wed, 4 May 2005 12:31:50 +0530495_us-ascii



------------------------------------------------------------------------ --------- 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 [...] 8370 37 45_Re: DB2 V7 on zOS: selective partition access13_Michael Ebert18_mebert@AMADEUS.NET30_Wed, 4 May 2005 10:41:04 +0200390_US-ASCII I've done some more testing. For example, I've removed the AND-ed WHERE clause, leaving only the WHERE NUPARTITION=constant. I've tested with another partitioned TS which was recenty created, so there's no question of leftover V5 issues. I've added OPTIMIZE FOR 8000000 ROWS. In all cases I get one-column matching index access, PAGE-RANGE is blank. Stats are collected using [...] 8408 26 32_Re: DB2 V7 z/OS: Left Outer Join33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Wed, 4 May 2005 04:18:04 -0500377_- Hi Terry

Thank you for your reply. You sayd "should", so I conclude that you are not sure. Can you tell me, why the optimizer materialized Z in the first case but not in the second case? When I code:

>SELECT * > FROM T1 A > WHERE Col1 = ? > AND Col2 = (SELECT MAX(Col2) > FROM T1 B > WHERE A.Col1 = B.Col1) AS Z > LEFT OUTER JOIN T2 > ON T1.Col1 = T2.Col1 [...] 8435 34 45_Re: DB2 V7 on zOS: selective partition access33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Wed, 4 May 2005 05:38:11 -0500344_- Michael

Just a question: Have you any row in your plan_table, where PAGE_RANGE is not blank? If yes, how does the SQL looks like, is the tablespace definition different from your test case?

Well, I never heard about a ZPARM to activate page range scan.

For example at our site the follwong SQL uses page range scan: [...] 8470 112 45_Re: DB2 V7 on zOS: selective partition access13_Michael Ebert18_mebert@AMADEUS.NET30_Wed, 4 May 2005 13:36:54 +0200545_ISO-8859-2 I checked all 10.000 packages in Prod. They were all rebound during our V7 upgrade, November 2003. After the rebind, ONE statement used PAGE-RANGE=Y (an UPDATE). This statement changed to matching index access (ACCESS_TYPE=N [index scan with IN-List], MATCHCOLS=1) at the next rebind a couple of months later (I'm not aware of any service that was applied in between - the follow-up service which brought our DB2 to 06APR2004 level was done later). Currently NO statement uses PAGE_RANGE=Y. It looks like DB2 lost the ability... [...] 8583 16 12_DB2 V8 S0C4s9_Jim Irwin19_Jim.Irwin@OA.MO.GOV30_Wed, 4 May 2005 06:58:42 -0500473_- We are towards the end of migrating our 20+ DB2s to V8 and, while we haven't had any major problems, we have been fighting various S0C4 issues throughout the process.....mostly with 3rd party products like AllFusion (COOL:Gen) and CA-DB2 Tools (Platinum). I don't know the right question to ask here (being that I'm mostly a management puke now and not the most technical guy around) but was there a major change in how DB2 manages it's control blocks etc. with V8? [...] 8600 55 16_Re: DB2 V8 S0C4s14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 4 May 2005 13:29:27 +0100350_us-ascii Jim

I imagine (or rather I hope) you are talking to our support folks about these S0C4s (and I am also assuming you are on r11, as you'd probably get much worse that S0C4 on P01!!)

Anyway, don't forget that there have been SIGNIFICANT changes in control blocks in V8 - a lot of them are now above the 2Gb bar for example. [...] 8656 233 16_Re: DB2 V8 S0C4s10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV30_Wed, 4 May 2005 07:40:14 -0500414_- Yes...we are current everywhere except AllFusion and we are working with the support groups of all the concerned parties. I was just after some general "what may have changed deep in the bowels" info for my own amusement.

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Wednesday, May 04, 2005 7:29 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 S0C4s [...] 8890 14 27_Abends in Stored Procedures14_Carol Goldberg19_puli@MINDSPRING.COM30_Wed, 4 May 2005 07:41:44 -0500623_- I was wondering if others are using the LE routine, CEE3ABD , to handle system abends within their application stored procedures.

Thanks, Carol

--------------------------------------------------------------------------------- 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 8905 36 22_Sun, sea and ..... DB214_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 4 May 2005 13:56:47 +0100633_us-ascii Computer Associates is proud to be the sponsor of SQL Adria 2005 European DB2 User Conference.

Plan now to attend the conference, being held in Dubrovnik June 26-29, 2005 . The User Event will feature technical sessions by Industry recognized DB2 experts, leading DB2 Gold Consultants and IBM Development. Participating in the SQLAdria event is an ideal opportunity to greatly improve your DB2 skills, expand your peer support community, network with DB2 industry experts and enhance your value to your organization. Don't miss this opportunity to become a better connected, more knowledgeable DB2 professional! [...] 8942 156 16_Re: DB2 V8 S0C4s10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV30_Wed, 4 May 2005 08:26:59 -0500396_-



-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Wednesday, May 04, 2005 7:29 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V8 S0C4s

Jim

I imagine (or rather I hope) you are talking to our support folks about these S0C4s (and I am also assuming you are on r11, as you'd probably get much worse that S0C4 on P01!!) [...] 9099 53 31_DSN1984I UNEXPECTED PAGE NUMBER24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 08:50:59 -0500484_- Dear List,

I was trying to do a DSN1COPY with a shrlevel change Image copy as SYSUT1 and encountered DSN1984I UNEXPECTED PAGE NUMBER. I ran a DSN1PRNT to see what was happening. Oddly , DSN1PRNT gives the following error. The page it expected and the page it printed are the same . Page 42. But still it gives the unexpected page error.Also every page after 42 gives the same error inspite of the page mentioned in the error message and the page printed being the same. [...] 9153 93 35_Re: DSN1984I UNEXPECTED PAGE NUMBER0_19_mike.holmans@BT.COM30_Wed, 4 May 2005 15:04:11 +0100379_iso-8859-1 Ramachandran,

The printout is telling you that although this is the 42nd page it has processed, the page number field in the data does not say 42. If you look at the third word of the hex, you'll see that on page 41 it has 00004100, on page 42 it has 00000130, and then on 43 and subsequent it has 00004200 etc instead of the 00004300 etc it is expecting. [...] 9247 79 35_Re: DSN1984I UNEXPECTED PAGE NUMBER15_Murley, Michael22_Michael_Murley@BMC.COM30_Wed, 4 May 2005 09:05:31 -0500306_- Is this, perhaps, an inline copy? Where it's expecting page 42, it found page 1, a space map page (for the page number, look at offset 0x7 for 4 bytes). I would expect that sort of thing in an inline copy.

You may need to specify the INLCOPY parm on the EXEC statement for the DSN1COPY step. [...] 9327 234 56_Re: AW: [DB2-L] again: REASON 00C90088 and Type 0000030211_Chris White18_crick616@YAHOO.COM30_Wed, 4 May 2005 09:06:22 -0500621_ISO-8859-1 Peter, If you have the time and resources, I would encourage you to benchmark Robert's suggestion against row level locking. We found surprises here. In some cases, the effective outcome of row level locking was LESS cpu overhead. This appeared to be due to the cpu savings from less lock contention, timeouts, deadlocks, and rollbacks (and subsequent re- submits). A timed out transaction can triple your cpu costs... 1) initial execution, 2) rollback, 3) re-try. And it gets worse if the subsequent re- try is timed out again (which is often the case in highly contentious situations). If the competing [...] 9562 13 35_Re: DSN1984I UNEXPECTED PAGE NUMBER24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 09:08:51 -0500661_- THE DIFFERENCE BETWEEN THE TWO COPIES ARE THE FAILED IMAGE COPY IS A SHRLEVEL REFERENCE WITH STYPE=S (LOAD REPLACE(NO)) THE SUCCESSFUL IMAGE COPY IS A SHRLEVEL REFERENCE NORMAL IMAGE COPY.

--------------------------------------------------------------------------------- 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 9576 16 35_Re: DSN1984I UNEXPECTED PAGE NUMBER24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 09:11:34 -0500687_- Dear List,



THE DIFFERENCE BETWEEN THE TWO COPIES ARE THE FAILED IMAGE COPY IS A SHRLEVEL REFERENCE WITH STYPE=S (LOAD REPLACE(NO)) THE SUCCESSFUL IMAGE COPY IS A SHRLEVEL REFERENCE NORMAL IMAGE COPY.

--------------------------------------------------------------------------------- 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 9593 32 42_DB2 V6/V7 and Visual explain on windows XP10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 4 May 2005 16:30:15 +0200360_US-ASCII Hi all

I'm installing Visual Explain in a Windows XP PC, but no one of the installed version (V6,V7, V8 beta) is able to start correctly as I get the following error

0xc0000005

Saying that the environment isn't able to inizialize it correctly. I'm using Db2 connect 7.1, DB2 V6 on os/390. On windows 98 all worked perfectly. [...] 9626 102 44_Setting a Default Schema for Crystal Reports19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Wed, 4 May 2005 07:37:20 -0700607_us-ascii DB2 UDB V8.2 Express Edition on Windows 2003

We are supporting the installation of a purchased package that is Windows-centric and uses Crystal Reports to drive the customizable reporting.

As delivered, none of the reports are qualified for the database schema. Instead there is a facility for the super-users to use DBADM authority to create a complete set of aliases with select grants for every one of the 500 tables and 200 views in the database. We're expecting around 100 users on the system. If you do the math, you may realize why the DBA's supporting this (all z/OS and [...] 9729 48 46_Re: DB2 V6/V7 and Visual explain on windows XP14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Wed, 4 May 2005 17:03:45 +0200541_us-ascii We are using : Visual explain v1.0.5 Db2 conne Hi,

We are using : Visual explain v1.0.5 Db2 connect v8.2 Windows XP sp2 and db2 v7.1

We had some problems. Look into pmr 82766 & 82934 ( still open )

greetings,

Patrick Steurs DBA at National Bank of Belgium

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa Sent: woensdag 4 mei 2005 16:30 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V6/V7 and Visual explain on windows XP [...] 9778 31 50_Does Locksize Row take more CPU than locksize Page24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 10:12:46 -0500481_- Dear List, For this whole discussion I am considering random reads on a fairly large table. (2 + million rows )





Currently running V7 DB2 on Z/OS. People always say "Dont use row level locking ...you will use more CPU" .



Is there any truth to this?



As I see it a lock is a lock , page or row. Maybe the memory required to represent a row lock is slightly bigger than that of a page lock , but why should it cost more CPU? [...] 9810 106 54_Re: Does Locksize Row take more CPU than locksize Page15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Wed, 4 May 2005 17:34:51 +0200488_iso-8859-1 What you are saying is basically correct: a lock is a lock and the CPU cost is more or less the same. But this is true only if you *really* only use random read via a unique index (1 SQL - 1 row -> 1 data page accessed). In real word there are often other types of data access: non unique index, index screening, tablespace scan. In those cases is the total number of lock requests that can be very different with row or page locks. And this make the CPU cost difference. [...] 9917 205 54_Re: Does Locksize Row take more CPU than locksize Page11_David Nance16_DWNance@FHSC.COM30_Wed, 4 May 2005 10:37:54 -0500513_us-ascii I feel the same as you. You do have one thing wrong below, "a lock is a lock". With row level locking, you could potentially take a lot more locks than you would with page locks. If you lock every row on a page, you are taking that many locks versus a single lock. I happen to use row level locking quite often and have not really seen a detriment from it as yet. When I was at a large telecommunications company we changed all of our tables to row level locking and our systems folks did not see a [...] 10123 249 41_DB2 UDB for Z/OS V7 and Sunopsis ETL Tool15_Schade, Jeffrey15_JSchade@ISO.COM30_Wed, 4 May 2005 11:44:14 -0400330_us-ascii I am working on installing a new Windows based tool called Sunopsis ETL for data transformations and I have been having problems with the tool hanging up while accessing DB2. It looks to me like the DB2 threads on the mainframe are waiting for work from the client software and the vendor is trying to find out why. [...] 10373 16 20_How to Unload a CLOB9_Raj Ghose17_raj.ghose@RBC.COM30_Wed, 4 May 2005 10:44:16 -0500653_- Hi List, What is the best way to unload a CLOB(2M) since there is a limit of 32K bytes for an OS dataset. Using DSNTIAUL only unloads upto 32K.

Thanks in advance!

Raj

--------------------------------------------------------------------------------- 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 10390 54 24_Re: How to Unload a CLOB14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 4 May 2005 17:36:16 +0100392_us-ascii You need to either write an application program that will unload the LOB, and cut it into "chunks" that will fit into a flat file (and hopefully remember how it did it, so that the CLOB can be "de-chunked" when you want to load it again), or else talk to your friendly third-party software vendors who already exploit unloading of LOB data beyond the 32K (or indeed any) limit. [...] 10445 81 24_Re: How to Unload a CLOB12_HEPP SHERY C17_schepp@SRPNET.COM30_Wed, 4 May 2005 09:42:17 -0700413_us-ascii You don't specify what requires the unload- whether it's a change to the table or a data migration. If your requirement is data migration then you can run an image copy and use it in dsn1copy to migrate the data. If your requirement is to change the table- then I would recommend creating shadow copies of your base/aux table- use insert to seed your new aux table- then rename and drop the tables. [...] 10527 34 35_Re: DSN1984I UNEXPECTED PAGE NUMBER15_Murley, Michael22_Michael_Murley@BMC.COM30_Wed, 4 May 2005 12:12:23 -0500567_- Yes, that would explain the presence of page 1 in the middle of the copy. Be sure to specify INLCOPY in the DSN1COPY EXEC parm.

-----Original Message----- From: Ramachandran Subramanian [mailto:rrnsss@YAHOO.COM] Sent: Wednesday, May 04, 2005 9:12 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DSN1984I UNEXPECTED PAGE NUMBER

Dear List,



THE DIFFERENCE BETWEEN THE TWO COPIES ARE THE FAILED IMAGE COPY IS A SHRLEVEL REFERENCE WITH STYPE=S (LOAD REPLACE(NO)) THE SUCCESSFUL IMAGE COPY IS A SHRLEVEL REFERENCE NORMAL IMAGE COPY. [...] 10562 16 46_Re: DB2 V6/V7 and Visual explain on windows XP15_Patrick Bossman18_bossman@US.IBM.COM30_Wed, 4 May 2005 12:20:32 -0500666_- Hi Max, Visual Explain V8 does not "require" java on the host. It only requires the Java Universal Driver installed on the host to get the error message text for SQL errors.

Regards, Pat

--------------------------------------------------------------------------------- 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 10579 32 24_CDB/ADrec Active Standby13_Agarwal, Amit33_amit_agarwal@STANDARDANDPOORS.COM30_Wed, 4 May 2005 13:57:52 -0400728_iso-8859-1 DB2 V7 Is anyone using the above product for Disaster Recovery? I am looking for any user experiences, comments etc online or offline.

Thanks Amit

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

The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this [...] 10612 162 41_Re: RLF Predictive governor SQL code +49511_Jay Reavill23_Jay.Reavill@CERTEGY.COM30_Wed, 4 May 2005 14:21:55 -0400491_us-ascii Roger,

We are very interested in this APAR's ability to capture -905's. I found some information on it at... http://www-1.ibm.com/support/docview.wss?uid=swg1PQ87848. However, I don't see where it will be capturing the actual SQL, just the cursor name. Depending on the app the dynamic SQL could change greatly even for the same cursor, making it still very difficult to identify the offending SQL. Hopefully, I'm just missing it. Maybe it's in the RLF diagnostic area? [...] 10775 15 35_Re: DSN1984I UNEXPECTED PAGE NUMBER24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 13:42:33 -0500707_- what is the differnce between inline copy and fullcopy ? I know the differnce superficially. Inline is taken as and when we reorg or load , and a full image is just a normal image copy.



But how can the type of image copy determine the contents of a page?

--------------------------------------------------------------------------------- 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 [...] 10791 92 38_Re: cancel thread rexx/db2-l-documents16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Wed, 4 May 2005 14:54:06 -0400545_iso-8859-1





Just to confirm, db-l-documents is no longer an active listserv. In replacement of this function, Code Place was created (access within the IDUG insider at www.IDUG.ORG).

In there you will find all the code in question plus a lot more.

If you have any questions feel free in contacting me



Robert Galambos IDUG listserv volunteer Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 V7 Database Administration robert.galambos@compuware.com [...] 10884 54 35_Re: DSN1984I UNEXPECTED PAGE NUMBER9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 4 May 2005 14:42:42 -0500551_windows-1250 The biggest issue for inline copies is that some pages are not in sequence on the tape. For example, spacemap pages are not built until the data pages are written and they are on the tape when the data is complete not when it is in sequence.



Mike Bell HLS Technologies -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramachandran Subramanian Sent: Wednesday, May 04, 2005 1:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DSN1984I UNEXPECTED PAGE NUMBER [...] 10939 36 25_db2 udb aix set integrity12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Wed, 4 May 2005 16:18:19 -0400471_us-ascii Hello, we aix v5 and db2 udb v8.2 with fixpack 8 on. we are trying to run the folowing command : set integrity for whse.whs_trans_header_p11 immediate checked for exception in whse.whs_trans_header_p11 use whse.whs_trans_header_p11_e we have 3 primary and 128 secondary logs defined. the process abends with logs full. this is going against 35 million rows plus. we are at a loss of how to get around this. any suggestions, hints that might help? TIA, Jeff [...] 10976 272 86_Next DB2 Performance Webinar - May 12th. Seats are limited -so reserve your spot today10_David Kane14_dkane@TACT.COM30_Wed, 4 May 2005 16:20:24 -0400712_iso-8859-1 "How EZ-DB2 Captures and Consolidates Dynamic SQL from the Distributed Environment, and helps you dramatically improve your Mainframe DB2 Performance."

Dear DB2 Technician,

Join TACT Software online for a free "Webinar" presentation:

"How EZ-DB2 Captures and Consolidates Dynamic SQL from the Distributed Environment, and helps you dramatically improve your Mainframe DB2 Performance."

The EZ-DB2 family of products is the only DB2 software for z/OS that lets you Capture and Consolidate Dynamic and Static SQL Statements that are essentially the same, Load them into an SQL Warehouse, Pin-Point the Top 20 SQL Statements that consume the most Resources, Alert you to [...] 11249 28 45_Re: DB2 V7 on zOS: selective partition access13_Michael Ebert18_mebert@AMADEUS.NET30_Wed, 4 May 2005 22:54:16 +0200454_US-ASCII Hi List,

Thanks for the many responses I've received.

My colleague may have found that it is a question of DB2 SW level - apparently we get PAGE-RANGE=Y on our maintenance system which is at a later level. Tomorrow is a holiday in Germany so I don't think I'll be able to do much checking before monday. In fact I'm starting to think I've invested too much work already, I'll first have to see how I've got time next week.... [...] 11278 24 27_Moving DB from AIX to Linux13_Brian Stewart23_briankstewart@GMAIL.COM30_Wed, 4 May 2005 14:54:01 -0600308_ISO-8859-1 Hello -

I am looking into moving a DB2 v8.2 DB from an AIX platform to a Linux platform. I wanted to run this by the group to see if anybody out there has done this and encountered any problems. I suppose I am looking for any hidden landmines or other "gotchas" that they might share. [...] 11303 22 45_Re: DB2 V7 on zOS: selective partition access14_Seth Bienstock19_sethdb2@COMCAST.NET30_Wed, 4 May 2005 16:08:10 -0500570_- Michael,

What is the value of your ZPARM(NPGTHRSH) ?

I would also consider reviewing the aggregate stats in the catalog for that table and/or other stats (i.e. INDEXES.FIRSTKEYCARD/FULLKEYCARD, COLUMNS.COLCARDF, etc.) that may not necessarily be up-to-date due to the fact that you're running partition-level RUNSTATS.

Also, I forgot whether you mentioned you were using a host-variable or a hard-coded value in the predicate? The limited partition scan is not possible when using a host-variable. (I believe this is not true for v8 though). [...] 11326 89 16_Re: DB2 V8 S0C4s12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 May 2005 17:07:18 -0500354_- What changed in V8? It's almost easier to say what did not change when it comes to structure, with lots of 31 bit pointers becoming 64 bits. Most of the names grew from 18 bytes to 128 bytes. 254 partition max became 4096. EBCDIC data becomes Unicode UTF-8. SQL statements max is 2 megabytes. There are more SQL changes than in any prior release. [...] 11416 118 54_Re: Does Locksize Row take more CPU than locksize Page12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 May 2005 18:24:57 -0500473_- Row locking needing more memory and more cpu time is realistic in most situations I've seen. It works well for a small percentage of situations - perhaps 1% to 4% of the table spaces. At 2 million rows, you might not even be partitioned. One of the rules of thumb for a large table is when you partition it - generally in the range of 500 MB to 2 GB. You might be able to get away with design for 2 million rows that would not be acceptable at 100 GB, 1 TB or more. [...] 11535 155 41_Re: RLF Predictive governor SQL code +49512_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 May 2005 18:49:35 -0500479_- I don't see any requests for the +495 in the requirements database, but there were several for the -495. Capturing the application action from a +495 does not seem to be possible to implement in DB2, since DB2 may not be in the vicinity, much less involved. There are many other traces to get the SQL statements, for example IFCIDs 0063, 0124, 0350. Of course, the applications have that information and more context, so the application can handle some situations better. [...] 11691 28 31_Are you attending NA IDUG 2005?13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Wed, 4 May 2005 19:47:20 -0500353_ISO-8859-1 If you are planning on attending IDUG in Denver in a few weeks (and you should because it looks like it will be a great one), make sure you get your Brown DB2-L ribbon at registration so everyone knows that you are a DB2-L participant.. And join me (and the rest of your DB2-L friends) for lunch.. watch the daily Globe for date and location. 11720 32 33_Number of Packages in Single Plan14_Barbara J Nigh26_barbara.j.nigh@US.HSBC.COM30_Wed, 4 May 2005 18:30:44 -0700771_us-ascii Does anyone know what the maximum number of packages for a single plan should be before performance starts to degrade on a DB2 subsystem?



Barbara J. Nigh - CAL261 Phone: 831.754.1400 Ext. 2048 Pager: 877.406.7868 barbara.j.nigh@us.hsbc.com





----------------------------------------- ************************************************************************ This E-mail is confidential. It may also be legally privileged. If you are not the addressee you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return E-mail. Internet communications cannot be guaranteed to be timely, secure, [...] 11753 123 33_DSNHPC S0C4 if SYSIN concatenated18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM30_Wed, 4 May 2005 21:21:27 -0400481_us-ascii Our environment: z/OS 1.4 and DFSms 1.03 When we concatenate the SYSIN DD statement for DSNHPC, it abends with an S0C4 RC10. Based on the output, it looks like this is occurring when it finishes with the first dataset and tries to get to the second one. This is only happening with DB2 V8. Running the same jcl except pointing to DB2 V7 libraries works as expected. There is a very old APAR from 1993, PN38242, with a similar issue. Has anyone else has this problem? [...] 11877 55 46_Re: DB2 V6/V7 and Visual explain on windows XP12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 4 May 2005 18:59:01 -0700398_us-ascii Max, I'm running Visual Explain V8 on a Windows XP PC with DB2 Connect V8.2 against DB2 V7 OS/390. I did run V7 and DB2 Connect 7.2 against DB2 V7 just fine as well.

One thing to check is whether the SPs that VE need are running from an Authorized MVS library. I've gotten that exact same error in startup of VE and in Control Center when the MVS SPs were not setup correctly. [...] 11933 23 29_Re: db2 udb aix set integrity24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 23:31:26 -0500275_- 1. I assume archive logging is turned on 2. What are the sizes of your log ? 3. did you see how big the log files that were being archived while your set integrity ran. That will also give you an idea about how bigh yyour logs are.





can you do a [...] 11957 41 54_Re: Does Locksize Row take more CPU than locksize Page24_Ramachandran Subramanian16_rrnsss@YAHOO.COM30_Wed, 4 May 2005 23:53:32 -0500513_- I have a few questions on your reply.



1. I do understand that we can take possibly 254 more locks on the same page in row level as compared to a page level



1a. Assuming that the other 254 additional locks on the same page are caused by 254 cuncurrent applications, I would think that it is acceptable to spend more CPU now because I am getting work done on the other 254 trAnactions instead of them being in Lock suspension or Timing out. Is that not an acceptable situation? [...] 11999 88 54_Re: Does Locksize Row take more CPU than locksize Page14_Peter Backlund21_BacklundDB2@TELIA.COM30_Thu, 5 May 2005 09:32:51 +0200 12088 80 34_Re: Stored Procedure Error Routine0_18_IWANT2BEME@AOL.COM28_Thu, 5 May 2005 06:50:30 EDT574_US-ASCII In our applications, the design is for server side to log the errors and the input parameters of transactions that return anything other than a good return code. The stored procedure formats the error including the SQL code, a error message, and other DB2 related info. In addition the start and ending timestamp of the stored procedure are logged. The server side may also log information at it's discretion if it does receive a response or can not access the stored procedure. The server side has alarms to notify them of issues based on certain parameters. [...] 12169 35 36_DB2 V7 OS/390 LOAD SYSUT1 CART ABEND0_36_Simon.Thornton@MARKS-AND-SPENCER.COM30_Thu, 5 May 2005 05:38:55 -0500603_- We're running a load on a fairly large partitoned ts, cartidge is being used for SYSUT1. After the SORT phase completes the job abends (code=07F1) with the following messages;

IEC501A M 0288,591640,SL,COMP,jobname,STEP0010

IECTMS3 0288,591640,IS NOT SCRTCH (64)

IEFTMS50 7XX- 04 jobname,STEP0010,SYSUT1 ,0288,591640,0001,S.jobname.SYSUT1

Load Statements used are LOAD DATA REPLACE LOG NO INDDN SYSREC00

Dis Util Output; PROCESSING UTILITY STATEMENT 1 UTILITY = LOAD PHASE = BUILD COUNT = 0 NUMBER OF OBJECTS IN LIST = 1 LAST OBJECT STARTED = 1 STATUS = STOPPED [...] 12205 74 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND21_Deepak Gujaba Gaikwad24_deepakggaikwad@YAHOO.COM30_Thu, 5 May 2005 04:37:10 -0700323_us-ascii Hi Simon,

It's not a problem with sysut1 or db2. its a CA1 tape issue used for SYSUT1. 591640 is not a scratch tape and hence cannot be overwritten. You should have IECTMS3 messages in joblog or syslog which would give more info about the tape and error.

Thanks & Regards, Deepak Gujaba Gaikwad [...] 12280 58 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND15_McCardle, Corey23_CMcCardle@AMERISURE.COM30_Thu, 5 May 2005 07:59:34 -0400281_- Verify that your DSN:.... is acceptable to cart. For example I would receive a NOT SCRATCH when a dsn started with my ID, but the job would write to cart it I used PRD... or TST... Check with Production Control to find out what are acceptable DSN names when writing to cart [...] 12339 76 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND9_Mike Bell21_mbell11a1@VERIZON.NET30_Thu, 5 May 2005 08:06:41 -0500435_windows-1250 It is a CA-1 setup issue - by default CA-1 won't let a job/program write to the same tape twice. It also has to do with the way the DB2 utilities reset the SYSUT1 dataset. I went through the CA-1 manuals for several days before I found the description of the parm. No, I don't remember the setting now. If I remember correctly, after long meetings, I gave up and made all the SYSUT1 dataset on disk and multi-volume. [...] 12416 113 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND13_Daley, Norman24_Norman.Daley@CINERGY.COM30_Thu, 5 May 2005 09:09:35 -0400430_windows-1250 This may be a long shot, but I used to have that problem with ZARA tape management. LABEL=EXPDT=98000 would override and let tapes be reused.

Norm Daley Cinergy

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Mike Bell Sent: Thursday, May 05, 2005 9:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V7 OS/390 LOAD SYSUT1 CART ABEND [...] 12530 54 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND12_McKown, John26_john.mckown@UICIINSCTR.COM30_Thu, 5 May 2005 08:17:12 -0500447_US-ASCII > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daley, Norman > Sent: Thursday, May 05, 2005 8:10 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] DB2 V7 OS/390 LOAD SYSUT1 CART ABEND > > > This may be a long shot, but I used to have that problem with > ZARA tape management. LABEL=EXPDT=98000 would override and > let tapes be reused. > > Norm Daley > Cinergy > [...] 12585 119 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Thu, 5 May 2005 08:24:22 -0500505_us-ascii What disposition did you specify? We use DISP=(MOD,CATLG,CATLG). If you're specifying a disposition of NEW, that's probably the problem. We've put SYSREC and SYSUT1 to tape when needed.

Tina Hilton Random House Bertelsmann Shared Services

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell Sent: Thursday, May 05, 2005 8:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V7 OS/390 LOAD SYSUT1 CART ABEND [...] 12705 67 68_UDB 8.1 on AIX - error building java stored procedure What to tweak?14_Bruce Lightsey30_Bruce.Lightsey@ITS.STATE.MS.US30_Thu, 5 May 2005 08:54:51 -0500672_US-ASCII 32-bit UDB 8.1 fp6b on AIX 5.1 latest fp

Multiple databases on one instance, user can build this same procedure with a different schema name on a different db.

On this particular database ( which she could build on a month ago ) she gets back :

PHOENIX.DBQINSERT - Jar file created. Call sqlj.install_jar ('file:///C|/Documents and Settings/RMILLS/Application Data/IBM/DB2/DC/Projects/bld1115231066438/SQL50402025412180.jar', '"PHOENIX".SQL50402025412180', 0) [IBM][CLI Driver][DB2/6000] SQL1042C An unexpected system error occurred. SQLSTATE=58004 PHOENIX.DBQINSERT - Build failed. PHOENIX.DBQINSERT - Roll back completed successfully. [...] 12773 88 72_Re: UDB 8.1 on AIX - error building java stored procedure What to tweak?13_Mark Horrocks22_agentlease@HOTMAIL.COM30_Thu, 5 May 2005 14:50:07 +0000636_- Hi,

You said you could build on a different db, was this on a different instance?

>From: Bruce Lightsey >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] UDB 8.1 on AIX - error building java stored procedure >What to tweak? >Date: Thu, 5 May 2005 08:54:51 -0500 > >32-bit UDB 8.1 fp6b on AIX 5.1 latest fp > >Multiple databases on one instance, user can build this same procedure with >a different schema name on a different db. > >On this particular database ( which she could build on a month ago ) she >gets [...] 12862 29 50_Does Locksize Row take more CPU than locksize Page16_Lemaire, Nigel B21_nigel.lemaire@EDS.COM30_Thu, 5 May 2005 09:54:26 -0500573_us-ascii All this talk about CPU utilization and no mention of extra storage required by IRLM? I think that is a major "gotcha", if you run out, and bring down the IRLM. Specifying LOCKMAX 0 and LOCKSIZE ROW can have "predictable" results if you are not carefull.

Nigel Lemaire EDS Americas Infrastructure Technology & Engineering Mainframe DB2 Production DBA Phone : 541-618-8444 vnet : NONE Pager : 1-877-285-2796 Email page : Nigel.Lemaire@skytel.com EMAIL : Nigel.Lemaire @ EDS.COM AOLIM : WA6MSE JABBER : nigel.lemaire Windows Messenger : Lemaire, Nigel B [...] 12892 135 40_Re: DB2 V7 OS/390 LOAD SYSUT1 CART ABEND0_36_Simon.Thornton@MARKS-AND-SPENCER.COM30_Thu, 5 May 2005 09:53:02 -0500405_- DD for Dataset is; //SORTOUT DD DSN=dsname,DISP=(MOD,DELETE,CATLG), // UNIT=CART,LABEL=RETPD=0100,VOL=(,,,68) //* SPACE=(CYL,(4369,4369),RLSE)

As the SORT phase has completed the dataset has already used 7 carts so I don't think the problem is with the dataset name. I guess it's resetting it for a subsequent phase. I think I'll have a word with storage to see how much spare disk we have. [...] 13028 100 72_Re: UDB 8.1 on AIX - error building java stored procedure What to tweak?18_Lombardozzi, Bryan25_Bryan_Lombardozzi@CSX.COM30_Thu, 5 May 2005 10:51:15 -0400565_us-ascii This should help!

database_memory - Databa Hey Bruce,

This should help!

database_memory - Database shared memory size configuration parameter Configuration Type Database Applies to

Database server with local and remote clients Database server with local clients Partitioned database server with local and remote clients Parameter Type

Configurable Default [Range] Automatic [ 0 -- 4 294 967 295 ] Unit of Measure Pages (4 KB) When Allocated When the database is activated When Freed When the database is deactivated [...] 13129 54 37_Re: Number of Packages in Single Plan14_Seth Bienstock19_sethdb2@COMCAST.NET30_Thu, 5 May 2005 15:06:09 +0000369_- The answer to that might depend upon:

a) Whether or not the CURRENT PACKAGESET and/or CURRENT PACKAGESET PATH (v8) special registers are being set by the application? b) The format of the entries coded in the PKLIST parameter of the BIND PLAN.

Another factor might be whether or not and to what extent you're using VERSIONing, but I'm not certain. [...] 13184 310 54_Re: Does Locksize Row take more CPU than locksize Page12_Isaac Yassin20_yassini@BEZEQINT.NET30_Thu, 5 May 2005 18:05:29 +0200449_US-ASCII Hi,

Another add-on:

V7 - each lock is ~250 bytes V8 - each lock is ~540 bytes



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Backlund Sent: Thursday, May 05, 2005 9:33 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Does Locksize Row take more CPU than locksize Page



Just a minor comment on point 1b. [...] 13495 421 54_Re: Does Locksize Row take more CPU than locksize Page14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 5 May 2005 16:18:53 +0100572_us-ascii I've not been paying that much attention to this thread, so apologies for jumping in part way through, but....

A page lock (in theory) costs just the same as a row lock, so the decision between page & row locking comes down to:

1. How many times do different processes want to lock DIFFERENT rows on the SAME page? If this happens a LOT, then row locking will give me contention relief but AT A COST. This can result from EITHER high throughput OR from short rows. For small tables, setting MAXROWS is perhaps a better solution (you can always [...] 13917 13 72_Re: UDB 8.1 on AIX - error building java stored procedure What to tweak?14_Bruce Lightsey30_Bruce.Lightsey@ITS.STATE.MS.US30_Thu, 5 May 2005 10:21:18 -0500528_US-ASCII same instance - only one currently on the server

--------------------------------------------------------------------------------- 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 13931 191 54_Re: Does Locksize Row take more CPU than locksize Page14_Chad A. Walmer19_Cwalmer@RITEAID.COM30_Thu, 5 May 2005 11:43:35 -0400489_us-ascii

It has already been stated in this thread but I feel it is important enough to state again. In data sharing environments, you will take an extra lock on the page with row level locking which means that those "purely" random accesses will acquire three locks (TS/table, page, row) compared to the two it would take otherwise (TS/table, page). For large OLTP environments, the extra contention and negotiation for the page P-lock can add up to quite a bit of overhead. [...] 14123 71 32_Seeking DBA for UNIX and Windows0_18_fredgarx@YAHOO.COM30_Thu, 5 May 2005 08:46:30 -0700744_us-ascii Greetings,

Once again we're looking to grow our DBA group. Please respond to marty_lawandus@jbhunt.com, or go to the website www.jbhunt.com to apply or for more info., as I will not be responding to any inquiries.

Thanks, Fred

Here's the text supplied to me:

"The ideal candidate will possess: a working knowledge administrating database systems in a UNIX or Windows environment (DB2 UDB preferred). Database administration experience to include utilities, performance and tuning, restart and recovery, underlying storage management systems, and SQL coding/tuning. Fundamental knowledge of at least one programming or scripting language. Some experience administrating UNIX or Windows servers helpful. [...] 14195 62 52_Networking Opportunities Abound at IDUG 2005 - North35_Mike Skaff - Colorado zOS UG Leader28_mike.skaff@FIRSTDATACORP.COM30_Thu, 5 May 2005 10:55:01 -0500511_- Not only does IDUG 2005 -- North America offer you premiere DB2 education, but you'll expand your network of contacts too. Take advantage of the multiple networking opportunities offered. Meet with other conference attendees to talk about successes, challenges, and tips and tricks you've learned. Learn more about the IDUG 2005 networking opportunities online . [...] 14258 238 45_Re: DB2 V7 on zOS: selective partition access15_Tomory, Cecilia29_Cecilia.Tomory@PACIFICORP.COM30_Thu, 5 May 2005 09:22:39 -0700527_us-ascii I'm a little behind on this list and don't know if you still are having this page_range issue. But, I am pretty sure you need to alter the LOCKPART parm to YES on your partitioned tablespace. This defaulted to NO in the older days.

Cecilia.

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



Date: Wed, 4 May 2005 10:41:04 +0200 From: Michael Ebert Subject: Re: DB2 V7 on zOS: selective partition access [...] 14497 21 72_Re: UDB 8.1 on AIX - error building java stored procedure What to tweak?14_Bruce Lightsey30_Bruce.Lightsey@ITS.STATE.MS.US30_Thu, 5 May 2005 10:21:18 -0500591_us-ascii same instance - only one currently on the server

--------------------------------------------------------------------------------- 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 -------------------------------------------------------- [...] 14519 79 54_Re: Does Locksize Row take more CPU than locksize Page12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 5 May 2005 11:57:41 -0500327_- 1.a. That would be useful, if the situation is that simple, but I've never seen something like this. Ten threads on a single page that is not read only generally causes problems. Often there will be an insert, an expanding update, a delete, a search for free space or a page split, causing those timeouts and deadlocks. [...] 14599 69 39_Accessing AIX DB2-UDB from PL1 programs11_Ng, Ricardo13_RNg@KABLE.COM30_Thu, 5 May 2005 12:11:34 -0500730_iso-8859-1 Greetings:

Can anyone advise what products do we need to have in a VSE environment so that our PLI programs can access an AIX DB2 UDB version 8.0 database ?

Ricardo Ng Kable News Company Inc. 815-734-5923 rng@kable.com





--------------------------------------------------------------------------------- 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 14669 159 41_Re: RLF Predictive governor SQL code +49513_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Thu, 5 May 2005 10:54:11 -0700573_us-ascii Thank you for the information, Roger. I found a way to get what I wanted -- I wrote an Insight request to capture basic information on every +495, such as date/time, authid, etc. Then I set up Detector to flag every dynamic SQL that runs over the RLFASUWARN as an exception. In theory, I should be able to correlate the +495 occurrences with the Detector exception data to get the SQL statements and other thread information. If the exception data is not present, then the assumption would be that the user chose BYPASS. In theory, anyway. I haven't got that [...] 14829 62 39_OS/390 DB2 Database Utility Conversions14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Thu, 5 May 2005 10:57:22 -0700463_us-ascii The IBM DB2 utilities have been an optional, extra cost item for a couple of versions. This has resulted in a renewed interest in evaluating and implementing tools from vendors other than a shops historic utility tool provider.

My question is what tools are available for converting existing utility jobs from one vendors suite to another. This question assumes basic functionality only, not the special options that all vendors tend to add. [...] 14892 176 50_JUNE MEETING BALTIMORE/WASHINGTON DB2 USERS' GROUP14_Henry L Nalven25_henry.nalven@MARRIOTT.COM30_Thu, 5 May 2005 13:14:51 -0500418_ISO-8859-1 GREAT AGENDA!!!!!

The Baltimore/Washington DB2 Users' Group Meeting Schedule for June 8, 2005 (Wednesday) When? Wednesday, June 8, 2005, 9:00 AM-3:30 PM (Registration at 8:30) Where? Sheraton Columbia Inn, 10207 Wincopin Circle Columbia, MD (410) 730-3900

Meeting Fees: Pre-registered members $20 Pre-registered non-members $30 At-the-door for members $30 At-the-door for non-members $40 [...] 15069 465 43_Re: OS/390 DB2 Database Utility Conversions12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Thu, 5 May 2005 13:53:40 -0500553_us-ascii DSN1COPY isn't part of the utility suite, so you don't have to worry about that. We did converted Copy, Reorg, Unload, and Load from CA/Platinum to BMC for a business unit who moved their application to our data center. Unload was the only one we had real problems with. That was because of the difference between the format of the output. BMC worked with us and even made code changes in short order so we could get the conversion done on time. We wrote some rexxes that helped change the jcl and control cards, but each job still had to [...] 15535 132 40_DB2 V7.2 on ZOS : Setting TRACKMOD to NO15_Kompalli, Shyam31_Shyam.Kompalli@NATIONALCITY.COM30_Thu, 5 May 2005 15:45:40 -0400367_iso-8859-1 Hi

We have couple of tablespaces that are defined with TRACKMOD YES even though we never take incremental image copies. Our policy is to take full image copy (one for local recovery and one for DRP).

I am planning to change the TRACKMOD to NO. But before doing that, I would appreciate if someone could clarify the following two aspects [...] 15668 64 37_Re: Number of Packages in Single Plan12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 5 May 2005 14:41:30 -0500523_- As noted, the search can change and has changed with PTFs or new releases, but the basics are in the 1993 redbook, GG24-4001, DB2 Packages, section 2.2 Package List Search (about 10 pages).

Here is another flavor of answer, from Robert Catterall in DB2 Magazine: http://www.db2mag.com/qanda/2001q1_db2dba2.shtml http://www.quest-pipelines.com/newsletter-v3/0202_C.htm

SQLCODE -805 has a page and a half of explanation of the options for not finding the plan and package in the Messages and Codes book. [...] 15733 89 49_Replication between DB2 for z/OS V7.1 and Windows13_Burgess Evans27_BMEvans@OHIOAUD.STATE.OH.US30_Thu, 5 May 2005 16:01:03 -0400571_US-ASCII Hi all,

I am looking into the feasibility of bidirectional replication. The source server would be DB2 for z/OS V7.1. The targets would be laptops running Windows XP. The two scenarios I am considering for the targets are DB2 UDB V8.current and MS Access.

I just read the "1.4.3 Bidirectional with a master (update anywhere)" section of Redbook "A PRACTICAL GUIDE TO DATA REPLICATION V8". The publish date on this book was 12/1/2002 so I don't know if the requirements and restrictions mentioned still apply. That is why I have turned to you. [...] 15823 18 24_Re: How to Unload a CLOB9_Raj Ghose17_raj.ghose@RBC.COM30_Thu, 5 May 2005 15:32:03 -0500699_- Hi Shery, I just needed to unload the data in a string format and pass it to the user. DSNTIAUL can only unload upto 32k. Is there a REXX program that can give me the data in chunks of 32k.

Thanks in advance!

Raj

--------------------------------------------------------------------------------- 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 15842 444 54_Re: Does Locksize Row take more CPU than locksize Page9_Dee Reins24_dee.reins@CENTURYTEL.COM30_Thu, 5 May 2005 16:10:00 -0500412_iso-8859-1 I was wondering at the time that row level locking was implemented, did the DB2 system have data sharing? I understand the the coupling facility can only do page locks. I work for a telecommunications company and we have data sharing. My thoughts are to add row level locking to tables that are like this. This table is a table that keeps track of the number of assignments for a central office. [...] 16287 278 54_Re: Does Locksize Row take more CPU than locksize Page24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Thu, 5 May 2005 15:40:49 -0700514_us-ascii You don't show the DDL for the tablespace, but it appears that you could have as many as 127 rows on a 4K page. Before going to row-level locking, I'd try using MAXROWS instead. Even with the lowest setting for MAXROWS of 1, you'd still only need about 42 cylinders for the data. But you may not need to go anywhere near that level. I'd start by setting MAXROWS to 50 and if needed, continue to cut by 50% until you find an appropriate balance. I'd also explicitly define the second index as unique. [...] 16566 96 95_Central PA DB2 User Group - June 9th - Sheryl Larsen - Unleash the Power of SQL for Data Access10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Thu, 5 May 2005 21:06:09 -0400500_utf-8







Greetings! Please check out the upcoming class for June 9th. Please rsvp to Cathy.Peck@Highmark.Com if interested in registering. Tks! Seeya





Unleash the Power of SQL for Data Access Thursday, June 9th, 2005 Sponsored By Central PA DB2 Users Group

Location: Central PA DB2 Users Group Highmark 1800 Center Street Camp Hill, PA 17089 Training Room 2B-103 NOTE: Class is NOT being held where normal User Group events take place!! [...] 16663 93 88_Central PA DB2 User Group - June 10th - David Beulke - High Performance Data Warehousing10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Thu, 5 May 2005 21:06:15 -0400493_utf-8







Greetings! Please check out the upcoming class for June 10th. Please rsvp to Cathy.Peck@Highmark.Com if interested in registering. Tks! Seeya





High Performance Data Warehousing Friday, June 10th, 2005 Sponsored By Central PA DB2 Users Group

Location: Central PA DB2 Users Group Highmark 1800 Center Street Camp Hill, PA 17089 Training Room 2B-103 NOTE: Class is NOT being held where normal User Group events take place!! [...] 16757 82 50_where is table OBID stored after table is dropped?15_Chris Hoelscher21_choelscher@HUMANA.COM30_Thu, 5 May 2005 22:07:19 -0400351_ISO-8859-1 hello listees ... we are moving an application from an existing subsystem to a newly-created subsystem. We have chosen to use (existing) dfdss backups to restore to the new subsystem (with a new hlq naturally and forcing all datasets to I0001. To make this work, we are preserving the table OBID when adding them to the new subsystem. [...] 16840 79 53_Re: Replication between DB2 for z/OS V7.1 and Windows14_Martin Flavell18_mflavell@I-TCS.COM30_Fri, 6 May 2005 08:46:44 +0100 16920 76 45_AW: [DB2-L] DSNHPC S0C4 if SYSIN concatenated0_23_Martin.Balz@HVBINFO.COM30_Fri, 6 May 2005 10:50:28 +0200674_iso-8859-1 Hi,

I had a similar Problem - S0C4 with concatinated SYSIN-DD Stmts in V8, the Precompile worked with V7. PTFs PQ97943 / UQ96503 and PQ92986 / UQ92348 helped me althought they don't state anything about this Problem...

Kind regards

Martin



> -----Ursprngliche Nachricht----- > Von: DB2 Data Base Discussion List [SMTP:DB2-L@IDUGDB2-L.ORG] im Auftrag > von GRESHAM, DEBORAH B > Gesendet am: Donnerstag, 5. Mai 2005 03:21 > An: DB2-L@WWW.IDUGDB2-L.ORG > Betreff: [DB2-L] DSNHPC S0C4 if SYSIN concatenated > > Our environment: z/OS 1.4 and DFSms 1.03 > > When we concatenate the SYSIN DD statement for DSNHPC, it abends with [...] 16997 191 54_Re: where is table OBID stored after table is dropped?14_Andy Lankester30_andy.lankester@CDBSOFTWARE.COM30_Fri, 6 May 2005 04:33:35 -0500407_windows-1250 It is held in the DBD until the last image copy which could contain data for the table is removed from SYSCOPY by MODIFY.

Andy Lankester

_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Chris Hoelscher Sent: Friday, May 06, 2005 3:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] where is table OBID stored after table is dropped? [...] 17189 483 48_Re: Setting a Default Schema for Crystal Reports13_Mertens, Bart18_bart.mertens@CZ.NL30_Fri, 6 May 2005 11:45:24 +0200549_- Tim,

You can use DB2 Connect's CURRENTSQLID and CURRENTSCHEMA settings to specify a default sqlid/schema. met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl -----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Ohling, Tim R - CNF Verzonden: Wednesday, May 04, 2005 4:37 PM Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: [DB2-L] Setting a Default Schema for Crystal Reports [...] 17673 77 32_Re: Runstat tasks on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 6 May 2005 03:06:44 -0700497_us-ascii Thanks Jim for this very comprehensive info. So, the bottomline seems to be that Runstats, if run along with Reorg opens up many more connections (primarly depending upon the no. of indexes) as compared to, if the Runstats were to run as a separate step.

Parallel index rebuild and parallel statistics collections should mean a faster job. I am just curious why some of the listers have observed that a Reorg followed by a Runstats is 'faster' than a Reorg with inline stats. [...] 17751 34 43_Varchar positioning in table on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 6 May 2005 03:08:50 -0700392_us-ascii In the 'old' days varchars were always positioned at the end of a DB2 (mainframe) table and if you added a new (non varchar) column you had to drop the table and recreate it with the new column before the varchar.

This was due to the perfromance implications. But these days (DB2 V7) this is probably no longer the case but I can't seem to find any doco to support this. [...] 17786 35 48_Online Reorg termination question on DB2 V7 z/OS16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Fri, 6 May 2005 03:09:58 -0700381_us-ascii I am planning to issue instructions to our operations group to just 'terminate' the utility in case any online Reorg abends, since the tablespace anyway remains in a RW status. The DBAs will subsequently (whenever) investigate why the job abended, correct the error condition and then resubmit the Reorg from the beginning. Does anyone see a lacuna in this approach? [...] 17822 258 54_Re: where is table OBID stored after table is dropped?12_Isaac Yassin20_yassini@BEZEQINT.NET30_Fri, 6 May 2005 13:26:26 +0200504_us-ascii Hi,

Whenever there's a need to DROP TABLESPACE it is a VERY good idea to do MODIFY RECOVERY DELETE AGE (*) Actually I use REXX to do the DROP TS and part of its' actions is to do just that.



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Lankester Sent: Friday, May 06, 2005 11:34 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] where is table OBID stored after table is dropped? [...] 18081 71 52_Re: Online Reorg termination question on DB2 V7 z/OS13_Michael Ebert18_mebert@AMADEUS.NET30_Fri, 6 May 2005 12:31:22 +0200280_US-ASCII In my jobs, OLR gets terminated automatically if it abends or ends with RC>=8 (and a log is written to a file and an email is sent). Leaving the utility STOPPED and the TS in UTRW might cause other utility jobs running against the same TS or SYSUTILX to fail/abend. [...] 18153 20 73_Re: UDB 8.1 on AIX - error building java stored procedu re What to tweak?13_Ealey, Paul M18_paul.ealey@EDS.COM30_Fri, 6 May 2005 11:34:35 +0100606_iso-8859-1 Take a look at :- http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0406qi/



Paul Ealey EDS, UK

--------------------------------------------------------------------------------- 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 18174 31 47_Re: Varchar positioning in table on DB2 V7 z/OS10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 6 May 2005 12:40:10 +0200384_US-ASCII If table is almost always read and updated very rarely, you should put VARCHAR columns at the end of the row. In the other cases they should be put near the end of the row but BEFORE most frequently updated columns as (in general but YMMV) they are rarely updated (here for instance are addresses, descriptions etc that in general are updated very rarely). In a schema: [...] 18206 20 34_Large Plans impacting Buffer Pools12_Adam Baldwin19_y0027528@ES.IBM.COM30_Fri, 6 May 2005 05:51:34 -0500526_- Sorry to bring up the old "one big plan or many small plans" debate again but I have a query. Yevich states that "large, cumbersome plans cause performance degredation, buffer pool problems, and EDM pool problems."

I am not too worried about the performance degredation as this is generally taken to be minimal and I am aware of the EDM pool implications. Can anyone explain what the "buffer pool problems" are likely to be and why? Also does anyone have a link to the Susan Lawson presentation related to this? [...] 18227 127 52_Re: Online Reorg termination question on DB2 V7 z/OS14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM30_Fri, 6 May 2005 07:29:34 -0400639_us-ascii Guten morgen, Herr Doctor! (I don't get to use my high school German enough!)

Raquel, here's the two parms that enable an online reorg to automatically terminate itself...

LONGLOG TERM TIMEOUT TERM

Lynne Flatley Liberty Mutual Insurance Co. Personal Market Systems - DBA Portsmouth, NH 03802-7201 ( 603-245-9334

"You cannot strengthen the weak by weakening the strong. You cannot help the wage-earner by pulling down the wage-payer. You cannot help the poor by destroying the rich. You cannot help men permanently by doing for them what they could and should do for themselves." -William Boetcker [...] 18355 161 52_Re: Online Reorg termination question on DB2 V7 z/OS11_David Nance16_DWNance@FHSC.COM30_Fri, 6 May 2005 07:06:04 -0500283_us-ascii Be careful with the blanket term. Do you plan online reorgs of partitions, within a partitioned table with NPI(s)? You have to worry about the build2 phase of the NPI. Also, what stage of the reorg did you fail in? I have had a screwy instance during the switch phase. [...] 18517 296 52_Re: Online Reorg termination question on DB2 V7 z/OS14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM30_Fri, 6 May 2005 09:00:48 -0400587_us-ascii To protect yourself from the never-quite-able-to-switch phase, use the deadline parm,

DEADLINE CURRENT_TIMESTAMP + 4 HOURS (for example)





Lynne Flatley Liberty Mutual Insurance Co. Personal Market Systems - DBA Portsmouth, NH 03802-7201 ( 603-245-9334

"You cannot strengthen the weak by weakening the strong. You cannot help the wage-earner by pulling down the wage-payer. You cannot help the poor by destroying the rich. You cannot help men permanently by doing for them what they could and should do for themselves." -William Boetcker [...] 18814 53 38_Re: Large Plans impacting Buffer Pools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 6 May 2005 10:00:01 -0400288_iso-8859-1 When a plan is not in the EDM pool it must be loaded.... and comes through BP0 to get there. If you have isolated BP0 so it's only for the catalog and directories, there shouldn't be much of a pool impact anymore. Except for the I/O's and application wait for it to load. [...] 18868 78 38_Re: Large Plans impacting Buffer Pools9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 6 May 2005 09:14:48 -0500461_windows-1250 There is a major difference between a plan with lots of DBRM in MEMBER list and a plan with lots of packages in PKLIST.

The plan with DBRM's has to be loaded all at once even if only 2 of the DBRM's are executed.

The plan with PKLIST only loads a stub for the plan and then when a package is referenced, will load that package and process the call. When a different package is referenced, it will be loaded and initialized also. [...] 18947 49 22_IFCID 198's PageNumber16_Riffe, Jim@HHSDC19_JRiffe@HHSDC.CA.GOV30_Fri, 6 May 2005 07:42:13 -0700680_- I am needing a better understanding of the 'PAGENUMBER' value returned from IFCID 198 TRACE. I am getting large values (i.e., 12321) when the VP is sized at 2000 and the HP is sized at 0, or VP is sized at 4000 and the HP is sized at 2000.

--------------------------------------------------------------------------------- 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 [...] 18997 245 52_Re: Online Reorg termination question on DB2 V7 z/OS10_Jwkwan200025_jwkwan2000@HOUSTON.RR.COM30_Fri, 6 May 2005 10:01:14 -0500461_iso-8859-1 Dave,

I agreed with you that if the job abended during switch phase, some or all datasets could have been renamed. You can image what would happen if you are using some reorg'ed data mixed with some old data.

James Kwan ----- Original Message ----- From: David Nance Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Friday, May 06, 2005 7:06 AM Subject: Re: [DB2-L] Online Reorg termination question on DB2 V7 z/OS [...] 19243 79 26_Re: IFCID 198's PageNumber35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 6 May 2005 11:01:38 -0400566_iso-8859-1 IFCID 198's PageNumberThis is the page number of the object, not a buffer in the pool.

Regards, Joel ----- Original Message ----- From: Riffe, Jim@HHSDC Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Friday, May 06, 2005 10:42 AM Subject: [DB2-L] IFCID 198's PageNumber



I am needing a better understanding of the 'PAGENUMBER' value returned from IFCID 198 TRACE. I am getting large values (i.e., 12321) when the VP is sized at 2000 and the HP is sized at 0, or VP is sized at 4000 and the HP is sized at 2000. [...] 19323 22 26_Re: IFCID 198's PageNumber10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 6 May 2005 17:14:36 +0200374_US-ASCII Actually it should be *page number*, the same of - say - ifcid 0006.

Do a simple test: create a test tablespace with no index and small pages, isolate it in a dedicated BP and execute a select * . Then check with your application using IFCID 198 the page number you retrieve :(if reorged) you should obtain page number in sequences and no high numbers. [...] 19346 73 43_DB2 V7 on zOS: Bufferpool Hit Ratio is Dead11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US30_Fri, 6 May 2005 10:29:49 -0500333_- I'm reading through an article Joel Goldstein wrote called the DB2 Bufferpool hit ratio is dead (link below). I was hoping someone had published the DB2 macros for calculating the formula, and I just could not find them on the listserv. The formula is as follows

((getpages - sum of all getpages read)/ getpages) * 100 [...] 19420 49 27_LOB Redbook for DB2 v7 z/OS13_Phil Sevetson26_Phil.Sevetson@WAKEFERN.COM30_Fri, 6 May 2005 11:43:51 -0400809_us-ascii http://www.redbooks.ibm.com/redbooks/pdfs/sg246571.pdf

--Phil Sevetson IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for OS/390

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



--------------------------------------------------------------------------------- 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 19470 107 47_Re: DB2 V7 on zOS: Bufferpool Hit Ratio is Dead35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 6 May 2005 11:49:24 -0400533_iso-8859-1 DB2 V7 on zOS: Bufferpool Hit Ratio is DeadRich,

I'm not sure what you mean by "macros". The data to calculate the mostly useless Hit Ratio is from your system statistics/performance reports. Do you need some additional info? Create a simple excel spreadsheet, and plug in the numbers.

Thanks, 'Joel ----- Original Message ----- From: Gugel, Rich Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Friday, May 06, 2005 11:29 AM Subject: [DB2-L] DB2 V7 on zOS: Bufferpool Hit Ratio is Dead [...] 19578 286 47_Re: DB2 V7 on zOS: Bufferpool Hit Ratio is Dead11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US30_Fri, 6 May 2005 11:08:26 -0500476_- Thanks Joel

I'm using MXG and/or Omegemon. I'm looking at the fields as descrived in sdsnmacs

Assuming:

QBSTGET The number of getpage requests issued. QBSTSPP Number of pages read via Sequential prefetch. QBSTLPP Number of pages read via list prefetch QBSTDPP Number of pages read via dynamic prefetch.

I think the formula should be this, but it does not include pages read in for random (QBSTGET - QBSTLPP - QBSTSPP - QBSTDPP ) / QBSTGET)) [...] 19865 114 54_Re: Does Locksize Row take more CPU than locksize Page12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 May 2005 11:03:46 -0500521_- Type 2 indexes, row level locking, and data sharing all delivered in DB2 Version 4 in 1995. They have changed in PTFs, V5, V6, V7 and V8. Your understanding about the coupling facility is not quite accurate, but the coherency controls and consistency controls do interact. There are both L-Locks (which can be rows) and P-Locks for the physical consistency of a page. See the current Data Sharing book and search for row lock since there are only about a dozen occurrences in the V8 book, but about 1200 for lock. [...] 19980 62 53_Re: Replication between DB2 for z/OS V7.1 and Windows12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 May 2005 12:30:47 -0500507_- The situation has changed a lot in the past couple of years, particularly in V8.2. I suggest that you check these redbooks instead: http://publib-b.boulder.ibm.com/abstracts/sg246487.html?Open http://publib-b.boulder.ibm.com/abstracts/sg246071.html?Open

IBM Data Replication Offerings on z/OS DB2 DataPropagator WebSphere Information Integrator Replication for z/OS WebSphere Information Integrator Event Publisher for DB2 UDB for z/OS WebSphere Information Integrator Classic Event Publisher [...] 20043 36 18_Table Partitioning11_Bryan Wells18_bunjibry@GMAIL.COM30_Fri, 6 May 2005 11:37:42 -0600635_ISO-8859-1 We are considering moving our data warehouse to db2 from oracle. does DB2 use table partitioning?

-- Bryan S Wells - DBA Newbie bunjibry@gmail.com

--------------------------------------------------------------------------------- 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 20080 241 47_Re: DB2 V7 on zOS: Bufferpool Hit Ratio is Dead35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 6 May 2005 14:34:38 -0400349_iso-8859-1 DB2 V7 on zOS: Bufferpool Hit Ratio is DeadThe other field you want is QBSTRIO the number of synch I/Os.

Regards, Joel ----- Original Message ----- From: Gugel, Rich Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Friday, May 06, 2005 12:08 PM Subject: Re: [DB2-L] DB2 V7 on zOS: Bufferpool Hit Ratio is Dead [...] 20322 31 22_Re: Table Partitioning12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 May 2005 14:35:46 -0500567_- Which platform are you moving to? DB2 for z/OS shipped partitioning with DB2 Version 1 Release 1 in 1983. DB2 for Linux, Unix and Windows partitioning is primarily node based today, but also shipped that long ago. There are many resources to help you move, for example http://www.ibm.com/developerworks/db2/zones/porting/ http://www.ibm.com/software/data/db2/migration/ http://www.redbooks.ibm.com/redbooks/pdfs/sg247048.pdf http://webevents.broadcast.com/ibm/db2migrate/home_oracle.asp http://www.embarcadero.com/resources/tech_papers/easingmigration.pdf ... [...] 20354 160 22_Re: Table Partitioning11_Bryan Wells18_bunjibry@GMAIL.COM30_Fri, 6 May 2005 13:53:26 -0600610_ISO-8859-1 Roger, et. al. Thanks for the links. We havnt decided on a platform yet. We currrently have JDE running on the OS/400. becuase of the cost we may be going to stand alone. im pushing for unix as the current Oracle 9.2.0.5 instances are running on w2k (aaagh...) The primary reason for migrating to DB2 would be to create a more cohesive environment between the datawarehouse and OS/400 daily functions. Secondly, I inherited this DBA position from the DBA that built the envirionment from the ground up, made the deals with the "then current" Oracle reps. Well, im sure everyone [...] 20515 111 49_here is table OBID stored after table is dropped?15_Tomory, Cecilia29_Cecilia.Tomory@PACIFICORP.COM30_Fri, 6 May 2005 14:33:40 -0700455_us-ascii Chris,

the OBID's are not reused within a database. You will have to drop the database and rebuild all the object within it to accomplish what you are trying to do. Yup, it is a pain. (my guess is that they are stored in the DBD, so can't be reused.)

Cecilia.





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

This email is confidential and may be legally privileged. [...] 20627 46 54_Re: where is table OBID stored after table is dropped?0_22_DB2information@AOL.COM28_Fri, 6 May 2005 23:05:15 EDT672_US-ASCII Chris, The GENDB2 software product will save the DBID, PSID and all OBIDs and automate the generation of JCL and control cards with all IDs. Visit: www.recoveryknowledge.com



Ed.

--------------------------------------------------------------------------------- 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 20674 97 54_Re: where is table OBID stored after table is dropped?11_David Nance16_DWNance@FHSC.COM30_Fri, 6 May 2005 22:12:24 -0500521_us-ascii Chris, Now that you already have the object dropped and the only way I and others know of to get it back is to drop that database as well, you should probably start looking at using DSN1COPY and use the OBIDXLAT. With this method, you would recreate the tablespaces and tables involved. Then use DSN1COPY to put the data back in there. You have to provide the OBID transalation(OBIDXLAT) of what the old obid was and what the new obid is. Have used it a few times in the past and does work fine. Good luck. [...] 20772 213 50_where is table OBID stored after table is dropped?10_Alan Smith21_smithac@NILDRAM.CO.UK30_Sat, 7 May 2005 09:53:11 +0100379_US-ASCII You need to look at the OBIDs for tablespaces (also PSIDs), indexes, triggers, and whatever other objects you're using as the OBIDs are a shared series - a different type of object could be using your OBID. For what you're trying to do to work, you need to create the tablespaces and their tables in the order of table OBID, then create everything else at the end. [...]