1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2007, week 2
2 290 54_[AD] Compuware April 2006 Technical Spotlight Sessions17_Dell'Anno, Aurora32_Aurora.DellAnno@UK.COMPUWARE.COM30_Mon, 9 Apr 2007 14:03:36 +0100365_US-ASCII Hi All,
please find below the April schedule for Spotlight Sessions, for all you Compuware users/customers out there.
Remember, our customers are required to register for FrontLine to access these sessions, the April 10 Spotlight is a great opportunity to register and gain an insight and understanding of how FrontLine can benefit you. [...]
293 462 58_Re: [AD] Compuware April 2006 Technical Spotlight Sessions16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Mon, 9 Apr 2007 09:10:46 -0400440_iso-8859-1 Aurora
sorry but I have to remind you that you need to put '[AD]' in front of all these type of posts on DB2-l
Robert Galambos
Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 Database Administration robert.galambos@compuware.com
Tel: +1 905 886 7000 Toll Free: +1 800 263 7189 Fax: +1 905 886 7023 Quebec: +1 877-281-1888 [...]
756 324 58_Re: [AD] Compuware April 2006 Technical Spotlight Sessions16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Mon, 9 Apr 2007 09:23:17 -0400485_iso-8859-1
SORRY
I mis-looked Aurora
you have permission to hit me on the head with a wet noodle
Robert Galambos
Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 Database Administration robert.galambos@compuware.com
Tel: +1 905 886 7000 Toll Free: +1 800 263 7189 Fax: +1 905 886 7023 Quebec: +1 877-281-1888
Compuware Canada [...]
1081 126 19_Password Characters12_Sloss, Scott19_scott_sloss@ADP.COM30_Mon, 9 Apr 2007 09:29:52 -0400390_us-ascii Hey all,
I'm experiencing issues with DB2 authenticating when the password has an exclamation point (!) in it. DB2 8.1.13 (8.2.6) on Windows 2k server.
I know this has been an issue, but I thought it was fixed. I know this is a question for IBM, but I was wondering if any other characters are a known issue out there while I wait for an answer. [...]
1208 35 32_DB2 UDB V8.2 Password Characters12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Mon, 9 Apr 2007 11:18:18 -0500523_ISO-8859-1 This is not a problem in 8.2 FP12. Check the Quick Beginnings for Servers manual for password details. Phil
Hey all,
I’m experiencing issues with DB2 authenticating when the password has an exclamation point (!) in it. DB2 8.1.13 (8.2.6) on Windows 2k server.
I know this has been an issue, but I thought it was fixed. I know this is a question for IBM, but I was wondering if any other characters are a known issue out there while I wait for an answer. [...]
1244 36 37_DB2 UDB V8 AIX - Startup and Shutdown16_Dave Stritzinger19_davestr@COMCAST.NET30_Mon, 9 Apr 2007 14:45:28 -0400388_iso-8859-1 Folks,
I am trying to find the manual / technical document / white paper / whatever that describes the entries, configuration, and processes that should be in place so that when an AIX system administrator shuts downs and then bring back up my AIX 5.x server the V8 DB2 administrator server and more importantly the DB2 instances are shut down and started back up. [...]
1281 20 26_ccsid oddity z/OS & DB2 V710_Ben Alford21_PA7280@UTKVM1.UTK.EDU28_Mon, 9 Apr 2007 16:39:36 EDT357_US-ASCII The code where SPUFI checks the database ccsid vs. the terminal ccsid was made available for DB2 V7 in 2004 by apar PQ89018. There were a couple later apars too. This code was rolled into the base of DB2 V8. Some shops may have chosen to not apply this particular enhancement to their DB2 V7 systems (I have). You don't have a choice with V8. [...]
1302 57 20_Re: ccsid oddity zOS33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Mon, 9 Apr 2007 23:02:17 +0200488_iso-8859-1 Well you can setup the C-Compiler and also USS to work with codepage 37. We had the same issue with codepage 273/1142 and 1047 for C and USS.
Don't know about Websphere but java is always UTF8 or UTF16, sorry cant remeber
Roland
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bruce Lightsey Sent: Wednesday, April 04, 2007 4:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] ccsid oddity zOS [...]
1360 45 20_Re: ccsid oddity zOS33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Mon, 9 Apr 2007 23:06:19 +0200384_iso-8859-1 Cobol: Depending on the Cobol Version and using the intergrated Precompiler
Enterprise Cobol 3.4.1 (comes via PTF) introduce a different codepage for the DB2 precompiler, before the compiler use the Cobol Codepage listed at the beginging of each compile. Using a different compiler codepage can be a performance issue as each variable will be converted by DB2. [...]
1406 29 23_Re: Password Characters13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Mon, 9 Apr 2007 16:03:39 -0700422_ISO-8859-1 On 4/9/07, Sloss, Scott wrote: > > I'm experiencing issues with DB2 authenticating when the password has an > exclamation point (!) in it. DB2 8.1.13 (8.2.6) on Windows 2k server. > > I know this has been an issue, but I thought it was fixed. I know this is a > question for IBM, but I was wondering if any other characters are a known > issue out there while I wait for an answer. > [...]
1436 45 41_Re: DB2 UDB V8 AIX - Startup and Shutdown13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Mon, 9 Apr 2007 16:26:46 -0700490_ISO-8859-1 On 4/9/07, Dave Stritzinger wrote: > I find in the etc/inittab the line that respawns the db2fmcd Fault > Monitor Coordinatior. I have found documentaion about the db2fm command and > looked at the fm.reg file in the instance sqllib directory. I > have also found that DB2AUTOSTART is not the valid way of doing this in V8. > I know my DB2 instance starts on the one server I am currently working on, > but I can rememnber how I set this up. [...]
1482 19 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 10 Apr 2007 04:05:29 -0500539_- Mike
So I was wrong, when I state, that the page lock will be released, before control is returned back to the application. Well, I must admit, when this was explained, in general DB2 was checking rows somehow in a loop, until a row qualifies. But if no row ever qualifies, what happens to the lock acquired on the last checked page? You say, it will be released when the cursor is closed or when the application commits (as far as the cursor was not declared with hold, but in this case, it will be demoted to S, won't it). [...]
1502 53 41_Re: DB2 UDB V8 AIX - Startup and Shutdown12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Tue, 10 Apr 2007 06:10:54 -0400325_us-ascii In the reg. Variable there is a parm DB2AUTOSTART=YES this will cause DB2 to start when every the AIX system is brought up. As for shutting it down, that an be just a normal script where you do a DB2STOP command after all process are stopped, or a DB2STOP FORCE command that will rip DB2 down.(not recommended) [...]
1556 99 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Tue, 10 Apr 2007 11:36:33 +0100369_iso-8859-1 Hi Walter
Well, it depends ... The lock MAY be released before control is returned to the application. I said that the page lock is released when the cursor moves onto the next page for ISOLATION(CS). The release of a page lock is not determined by which rows are returned to the application, but only by the (internal) processing of the cursor. [...]
1656 48 41_Re: DB2 UDB V8 AIX - Startup and Shutdown14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 10 Apr 2007 21:18:34 +1000393_US-ASCII Further to the other replies, - "~dasusr1/das/adm/dasauto on" should auto start the administration server. - "~dasusr1/das/bin/db2admin stop /force" should stop it
(or whatever user-id you used for the admin server.) Probably best to have "su - dasusr1" first - then it will have run dasprofile
James Campbell
On 9 Apr 2007 at 14:45, Dave Stritzinger wrote: [...]
1705 33 65_Mike Cerrone/Southbury/IBM is off-site attending a remote meeting12_Mike Cerrone18_cerron1@US.IBM.COM31_Tue, 10 Apr 2007 08:07:36 -0400346_US-ASCII I will be out of the office starting 04/10/2007 and will not return until 04/16/2007.
I will have limited access to NOTES during this time frame, please contact my manager, Rick Peeso, if you require an immediate response during this period.
---------------------------------------------------------------------------------
1739 79 45_z/OS question about BIND PACKAGE(..) COPY(..)17_McCormack, Mark A27_mamccormack@STATESTREET.COM31_Tue, 10 Apr 2007 11:14:19 -0400355_US-ASCII I have questions about the inner workings of BIND PACKAGE(..) COPY(..). If one runs BIND PACKAGE(..) COPY(..), is the optimizer invoked, or is the access path copied? In other words, will the access path for the original and the copy always be the same, or can they be different?
Thanks for any help you can give here. Mark McCormack [...]
1819 53 49_Re: z/OS question about BIND PACKAGE(..) COPY(..)9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 10 Apr 2007 10:38:57 -0500441_US-ASCII optimizer is invoked - notice that you can change qualifiers, tables, indexes on the new target. You can even copy to a different subsystem. DB2 trace will show you the same catalog access as bind processing.
Separate issue but related - if you bind copy to a different subsystem, you do NOT get the full SQL in SYSPACKSTMT. Only the explainable SQL gets copied. OPEN CURSOR, FETCH, CLOSE CURSOR and others are missing. [...]
1873 139 49_Re: z/OS question about BIND PACKAGE(..) COPY(..)13_David Simpson22_dsimpson@THEMISINC.COM31_Tue, 10 Apr 2007 12:14:42 -0400651_iso-8859-1 From the DB2 Command Reference Manual
COPY (collection-id.package-id) (collection-id.package-id) COPYVER On: BIND PACKAGE
Determines that you are copying an existing package and names that package. Copying the package recalculates the access paths in the copy.
David Simpson Senior Technical Advisor Themis Inc. dsimpson@themisinc.com http://www.themisinc.com
________________________________
From: DB2 Data Base Discussion List on behalf of McCormack, Mark A Sent: Tue 4/10/2007 10:14 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] z/OS question about BIND PACKAGE(..) COPY(..) [...]
2013 142 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Tue, 10 Apr 2007 18:45:13 +0100407_iso-8859-1 Walter
I have checked my teaching notes and can clarify my doubts about the U-lock case. I said:
'The original question specified CURRENTDATA(NO) and so the row is returned to the application without a lock, if the locking protocol requires an S-lock (I am not sure what happens if a U-lock is required - I have not traced the lock avoidance mechanism when U-locks are used).' [...]
2156 26 36_IMAGE COPY - DSNU1408I ERROR MESSAGE9_Raj Ghose17_raj.ghose@RBC.COM31_Tue, 10 Apr 2007 14:07:44 -0500406_- Hi List, Image Copy utility against a LOB tablespace is abending on the following error message.
DSNU1408I :PAJ0 DSNUBASI - SOURCE DATA SET DOES NOT MATCH TABLESPACE DBx.TSx DSNU012I DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8
A DSN1COPY was used to copy the data from system "A" to System "B". This tablespace exists in system "B". SELECT on this table works fine. [...]
2183 114 76_Z/OS Db2 V8 - New ZPARM liminting nbr of SQL stored proceduce calls in a UOW0_18_iwant2beme@AOL.COM31_Tue, 10 Apr 2007 16:06:02 -0400576_utf-8 Myron wrote: “We've also been bitten by some person at IBM whose idea was to add a ZPARM and hard-limit to the number of times a stored procedure can be called in a unit of work in DB2 V8. And that limit was 99,999 rather than some reasonable number like 2gig. We have some stored procedures that are basically read-only (with UR) that are called by batch programs hundreds of thousands to millions of times per night (performing calculations and are not functions). These processes worked fine until we started to convert to DB2 V8 and then [...]
2298 45 62_[DB2 z/OS V8] Justifying separation of Data Warehouse and OLTP14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Tue, 10 Apr 2007 17:08:46 -0400371_us-ascii Dear Esteemed List:
Here's a (somewhat) new twist on a very old issue. We currently run OLTP and data warehouse (DW) applications against DB2 V8 NFM. All data currently resides in the same n-way data sharing subsystem. There is a project afoot to separate the DW data into its own subsystem. The Question: Is this a good idea, and what will we save? [...]
2344 28 27_[FLUFF] IDUG is almost here13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Tue, 10 Apr 2007 17:12:20 -0500510_ISO-8859-1 With IDUG just a few weeks away, I want to remind everyone to pick up their Brown DB2-L ribbon when they register. And make sure you wear it. It's the first step at letting everyone know you participate on this list.
BTW, at the Sunday evening Welcoming Reception, IDUG is going to set aside a spot for all of us DB2-L folk to meet and put faces with e-mail addresses. We will make sure there is some "obvious" way for everyone to find us. More on this to come as we get closer to IDUG. [...]
2373 111 80_Re: Z/OS Db2 V8 - New ZPARM liminting nbr of SQL stored proceduce calls in a UOW33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Wed, 11 Apr 2007 01:00:21 +0200656_utf-8 MAX_ST_PROC in DSN6SPRM
Roland
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of iwant2beme@AOL.COM Sent: Tuesday, April 10, 2007 10:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Z/OS Db2 V8 - New ZPARM liminting nbr of SQL stored proceduce calls in a UOW
Myron wrote: “We've also been bitten by some person at IBM whose idea was to add a ZPARM and hard-limit to the number of times a stored procedure can be called in a unit of work in DB2 V8. And that limit was 99,999 rather than some reasonable number like 2gig. We have [...]
2485 54 40_Re: IMAGE COPY - DSNU1408I ERROR MESSAGE25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Tue, 10 Apr 2007 16:47:26 -0700330_iso-8859-1 You may want to check the XLATs to make sure the IDs were translated correctly. Additionally, cross loader now supports LOBs > 32k. You may be able to avoid DSN1COPY, by dropping and recreating the object in System B and using cross loader to copy the data.
--- Raj Ghose wrote: [...]
2540 34 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 11 Apr 2007 05:46:30 -0500291_- Hi Mike
Let me quote one of your phrase:
> So, while the decision when to acquire a page lock can be influenced by > which rows are returned to the application, the decision on when to > release the lock is based solely on the internal processing of the cursor > by DM. [...]
2575 134 51_Baltimore/Washington DB2 Users' Group-June 13, 200740_Henry Nalven-Baltimore-Washington UG Ldr25_henry.nalven@MARRIOTT.COM31_Wed, 11 Apr 2007 06:59:38 -0500501_ISO-8859-1 The Baltimore/Washington DB2 Users' Group Meeting Schedule for June 13, 2007 (Wednesday)
When? Wednesday, June 13, 2007, 9:00 AM-3:30 PM (Registration at 8:30)
Where? Sheraton Columbia, 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
To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...]
2710 153 44_Re: Performance worry with too many triggers10_Dave Churn19_davechurn@GMAIL.COM31_Wed, 11 Apr 2007 07:24:48 -0500495_ISO-8859-1 Deborah,
Have you considered using an Identity column in place of the trigger/sequence object? If you need only 1 of these columns on a table, then it might be a good match. When you use the GENERATED ALWAYS option with the Identity column, then DB2 will not allow the applications to insert or update a value. With version 8, you can alter between GENERATED ALWAYS and GENERATED BY DEFAULT options to allow you to load data with specific keys and then go back to DB2 handling.
2864 40 42_One PLAN Table vs. Application PLAN Tables15_Dave Petronella24_dpetronella@PERSHING.COM31_Wed, 11 Apr 2007 09:22:28 -0400597_US-ASCII Hi,
Currently our shop is setup to have one PLAN_TABLE for each application. All application PLAN_TABLEs are in a single database and each one is its own tablespace that is named to identify the application.
A proposal has been made to go to one large PLAN_TABLE for all applications and point to this table via either a synonym or alias. I would appreciate any feedback from anyone who may have already done this, or considered it and decided not to do it. What are the advantages and disadvantages of doing this that you've experienced or that you feel this approach [...]
2905 97 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Wed, 11 Apr 2007 14:24:15 +0100524_iso-8859-1 Hi Walter
Taking you assumptions: ISO(CS) and CURRENTDATA YES, no row qualifies and no page is clean. I assume that by 'no page is clean' you mean every page fails the CLSN test and contains at least one row with the PUNC bit set.
In both cases 1 and 2, the same things happen. Each page will be locked (case 1 U-lock, case 2 S-lock) as soon as the first row with the PUNC bit set is encountered by DM. This is done before the row is checked to see if it is a qualifying row. I am assuming the [...]
3003 85 46_Re: One PLAN Table vs. Application PLAN Tables9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 11 Apr 2007 08:38:29 -0500546_us-ascii BIND EXPLAIN(YES) does NOT work for synonyms and only works with alias in V8? Since the PLAN_TABLE is selected by the owner parm, that is tied to your application authority structures. That is not something to change lightly.
The interface into PLAN_TABLE and DSN_STATEMNT_TABLE from BIND is a fairly low level construct. During the V8 ESP, I managed to define a unicode PLAN_TABLE and BIND put EBCDIC data into it. There is a ptf that prevents that from happening now but it tells you that BIND is not a normal SQL process. [...]
3089 97 46_Re: One PLAN Table vs. Application PLAN Tables13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 11 Apr 2007 14:52:49 +0100431_us-ascii Dave,
Go for it. Quite a few sites I've worked at have had one big Plan_Table for their explain output, but that's because they've had one ID owning all the packages so didn't have a choice.
I'm struggling now, but I don't think I've ever seen a locking issue with one big explain table, neither from concurrent binds nor from people browsing (with UR, right?) the Plan_Table while binds are occurring. [...]
3187 81 46_Re: One PLAN Table vs. Application PLAN Tables11_Dirk Johann15_D.Johann@SEG.DE31_Wed, 11 Apr 2007 16:04:22 +0200427_us-ascii Dave,
one argument for breaking up plantables is the usage of opthints. In this case the size of the plantable when applying an opthint can matter.
Other issues of course are of course maintenance of plantables in production. It might be easier and faster to go through smaller tables when looking for entries of non-existing packages. But if you have appropriate indexes, it's usually a non-issue. [...]
3269 141 46_Re: One PLAN Table vs. Application PLAN Tables15_Schade, Jeffrey15_JSchade@ISO.COM31_Wed, 11 Apr 2007 10:04:42 -0400550_us-ascii AT my shop we have one plan table for Acceptance and Production environments since all normal binds are done using Endevor which uses a single authorization id for all binds.
For Test we implemented ALIASES for all the programmer explain tables all pointing to a single definition. This was new to DB2 V8 and works even in compatibility mode since that is what we are currently running. It is much easier to create the necessary ALIAS definitions for new programmers as opposed to managing many tablespace and table definitions. [...]
3411 11 39_z/os v8: rexx with final table question10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Wed, 11 Apr 2007 09:18:36 -0500570_- does anyone have a sample or used "select from final table" with an insert statement in rexx?????
--------------------------------------------------------------------------------- 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
3423 67 68_Re: CM for One Year was (One PLAN Table vs. Application PLAN Tables)11_Tom Moulder21_tom_moulder@1SCOM.NET31_Wed, 11 Apr 2007 09:38:10 -0500289_windows-1250 Jeff
I couldn't help but notice that you have been V8 for over a year now. Did I understand you to say that you are currently running in V8 CM and have been for over a year?
How is that working for you and do you have any plans to move to NFM anytime soon? [...]
3491 89 46_Re: One PLAN Table vs. Application PLAN Tables12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Wed, 11 Apr 2007 10:12:09 -0500339_us-ascii You can do this with a view. Create a view with your owner.plan_name pointing to the group.plan_name. This supports everything going to one place but does not remove the issue of maintenance. As you go between releases you will have to maintain the views.
Troy Coleman, Support Engineer IBM Certified Solutions Expert [...]
3581 109 68_Re: CM for One Year was (One PLAN Table vs. Application PLAN Tables)15_Schade, Jeffrey15_JSchade@ISO.COM31_Wed, 11 Apr 2007 11:51:26 -0400367_us-ascii Hi Tom,
Yes we have been CM for that long and have not had any issues. We are planning on NFM in the next 2 months. We were delayed waiting for one of our 3rd party tools to fully support NFM and they should be ready by June.
Jeff _________________________________________ Jeffrey Schade Systems Consultant, Technology Engineering [...]
3691 185 31_DBADM authority not sufficient?12_Steve Grimes22_steve_grimes@WUSTL.EDU31_Wed, 11 Apr 2007 11:50:43 -0500717_US-ASCII Hello, DB2 V7.1, z/OS 1.4 here.
We use RACF Groups as secondary authorization ID's for almost everything we do, and it works well. DBAPROD.DBT_DB2BPSTATS is a table in the DBDXMISC database. GDBPADMN (a RACF Group) has been granted DBADM authority over database DBDXMISC.
From SYSDBAUTH:
GRANTOR GRANTEE NAME TIMESTAMP DATEGRANTED TIMEGRANTED GRANTEETYPE AUTHHOWGOT CREATETABAUTH CREATETSAUTH DBADMAUTH DBCTRLAUTH DBMAINTAUTH DISPLAYDBAUTH DROPAUTH IMAGCOPYAUTH LOADAUTH REORGAUTH RECOVERDBAUTH REPAIRAUTH STARTDBAUTH STATSAUTH STOPAUTH IBMREQD GRANTEDTS WU07SG GDBPADMN DBDXMISC C4C1C5D7C9E5C6D7F5D6E7C5 070403 14541434 S Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y N 2007-04-03 14:54:14.345652 [...]
3877 137 35_Re: DBADM authority not sufficient?13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 11 Apr 2007 13:40:47 -0400377_us-ascii Hi Steve.
No, it doesn't make sense. But Yes, that's the way it is designed.
I believe there is a discussion on the list archives (It vaguely seems to me, Roger may have even explained why it is wanted thiis way).
If you look at the DB2 9 announcement, you might be tempted to say Hurrah! This is finally fixed!!!! But you might be mistaken. [...]
4015 50 56_[DB2 z/OS V8] Microsoft OLE DB Driver and Catalog Access14_Adrian Collett24_adriancollett@TISCALI.IT31_Wed, 11 Apr 2007 18:25:17 +0200863_ISO-8859-1 Hi,
Since we moved to V8 CM we have noticed a problem with an application that uses Microsoft OLE DB Provider.
As soon as we moved to CM the application has started executing the following query thousands of times throughout the day:
SELECT SYSIBM.SYSINDEXES.TBCREATOR as TABLE_QUALIFIER , SYSIBM.SYSINDEXES.TBCREATOR as TABLE_OWNER , SYSIBM.SYSINDEXES.TBNAME as TABLE_NAME , SYSIBM.SYSINDEXES.UNIQUERULE as NON_UNIQUE , SYSIBM.SYSINDEXES.INDEXSPACE as INDEX_QUALIFIER , SYSIBM.SYSINDEXES.NAME as INDEX_NAME , 0 as TYPE , SYSIBM.SYSKEYS.COLSEQ as SEQ_IN_INDEX , SYSIBM.SYSKEYS.COLNAME as COLUMN_NAME , SYSIBM.SYSKEYS.ORDERING as COLLATION , SYSIBM.SYSKEYS.COLNO as ORDINAL_IN_BASE FROM SYSIBM.SYSINDEXES , SYSIBM.SYSKEYS WHERE SYSIBM.SYSKEYS.IXNAME = SYSIBM.SYSINDEXES.NAME AND SYSIBM.SYSINDEXES.TBCREATOR = 'our creator' AND [...]
4066 171 49_Re: z/OS question about BIND PACKAGE(..) COPY(..)12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Wed, 11 Apr 2007 16:09:50 -0400407_us-ascii Mark,
The optimizer is invoked with a bind copy and a new access path is determined - the access paths between the original and the copy can be different. We use this process frequently - binding a production collection "off to the side" - to determine how access paths will look before we rebind the production collection after a major DB2 version release or other significant change. [...]
4238 102 60_Re: [DB2 z/OS V8] Microsoft OLE DB Driver and Catalog Access13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 12 Apr 2007 08:45:20 +0100635_us-ascii Hi Adrian,
In a previous life I was involved with a third-party mid-tier (i.e. Fisher-Price) app. that connected to DB2 for z/OS. They were issuing multiple metadata-type queries of the kind you've got below, and some were performing rather poorly. I never got to the bottom of it, but it was resolved when our run-time client DB2 for LUW software was upgraded from something-or-other to 8.something.something. Something in the client software seemed to be saying, a-ha, I'm talking to DB2 for z/OS via a more modern client, so I'll use those jazzy IBM-supplied metadata stored procedures on z/OS (the SYSIBM.SQL* [...]
4341 16 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 12 Apr 2007 02:54:05 -0500363_- Hi Mike
Thanks for your very clear explanation, but nevertheless, I have one thing to mention (or 1 question remains). I think, that the check for the CLSN will only be done, if lock avoindance is taken into account, and that's only for S-locks. A U-Lock will always be acquired, independantly of any ZPARM, PUNC-bits or what so ever. Do you agree? [...]
4358 64 60_Re: [DB2 z/OS V8] Microsoft OLE DB Driver and Catalog Access14_Adrian Collett24_adriancollett@TISCALI.IT31_Thu, 12 Apr 2007 11:51:09 +0200319_ISO-8859-1 Hi Raymond,
Thanks for your help, the FP-Layer is all Microsoft, of which I am blissfully ignorant, but is run by another group, so we have the usual demarcation issues as well.... apparently they have tried upgrading to a newer version, although I can find no evidence of that in syspackages... [...]
4423 60 60_Re: [DB2 z/OS V8] Microsoft OLE DB Driver and Catalog Access13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 12 Apr 2007 11:08:35 +0100424_us-ascii Hey,
My favourite one was the non-indexable query on Systables looking for its own tables. I think the CREATOR column value used had a different length to the Catalog, so it TS scanned. Not a problem when typically installed in FP-land in its own instance/database (300-ish tables), but a bit more of a problem a) in a DB2 subsystem with 10's of thousands of objects, and b) when issued multiple times. [...]
4484 75 60_Re: [DB2 z/OS V8] Microsoft OLE DB Driver and Catalog Access14_Adrian Collett24_adriancollett@TISCALI.IT31_Thu, 12 Apr 2007 12:34:38 +0200593_ISO-8859-1 Couldn't agree more.....after all, if the flake breaks, you just do the "Seattle Salute" and all is welll.....
Next step is to open a problem in FP-land...which will be fun !!!!
Ciao, Adrian
Bell, Raymond ha scritto: > Hey, > > My favourite one was the non-indexable query on Systables looking for > its own tables. I think the CREATOR column value used had a different > length to the Catalog, so it TS scanned. Not a problem when typically > installed in FP-land in its own instance/database (300-ish tables), but > a bit more of a problem a) in [...]
4560 72 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Thu, 12 Apr 2007 14:01:12 +0100327_iso-8859-1 Hi Walter
Does lock avoidance work when U-locks are used? That's the $64,000 question! As I said before, I have not traced the U-lock case and so I do not have any hard evidence. However I believe that lock avoidance does work for U-locks and that the CLSN and PUNC checking will be done as I described. [...]
4633 22 41_IBM DB2 Object Restore - User Experiences10_Tom Glaser14_tg5444@ATT.COM31_Thu, 12 Apr 2007 09:44:06 -0500306_- Hi,
We are taking a second look at IBM's DB2 Object Restore Tool and wanting to know if anyone can share their experiences with this tool. Specifically, we are looking to see if the tool has successfully restored objects from outages and if you're seeing a lot of dasd consumed by this tool. [...]
4656 120 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 12 Apr 2007 17:56:03 +0300426_iso-8859-1 Hi,
The 3 conditions for lock avoidance:
ISO(CS) CURRENTDATA NO FOR FETCH / READ
If the data is rejected there's no need to lock it anyway. If it is not rejected (eg: satisfies the predicates) then it will not be locked upon the 3 rules above (all of them) So- cursor FOR UPDATE that actual passes rows to the program (means they satisfy the criteria) actually locks (U-Lock) the row/page [...]
4777 58 45_Re: IBM DB2 Object Restore - User Experiences10_Tobin, Jim17_Jim.Tobin@AIG.COM31_Thu, 12 Apr 2007 11:22:56 -0400325_us-ascii Tom,
I am in the process of upgrading OR from V1.3 to the latest release V2.1. It has successfully restored objects from outages after they were removed from the catalog. I have an open PMR regarding what additional DASD consumption to expect with V2.1. I'll send an update when I have more information. [...]
4836 51 31_Re: [FLUFF] IDUG is almost here7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 12 Apr 2007 12:52:48 -0700388_iso-8859-1 Can I put Leonardo Dicaprio's face with my email address? I'm tired of being confused with Matt Damon!
Willie Favero wrote: With IDUG just a few weeks away, I want to remind everyone to pick up their Brown DB2-L ribbon when they register. And make sure you wear it. It's the first step at letting everyone know you participate on this list. [...]
4888 156 31_Re: [FLUFF] IDUG is almost here13_Ohling, Tim R22_Ohling.Tim@CON-WAY.COM31_Thu, 12 Apr 2007 12:55:03 -0700421_us-ascii Matt Daemon is an obsure Unix process.
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: Thursday, April 12, 2007 12:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [FLUFF] IDUG is almost here
Can I put Leonardo Dicaprio's face with my email address? I'm tired of being confused with Matt Damon! [...]
5045 13 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 13 Apr 2007 02:48:29 -0500623_- Isaac
So, what you are saying, is, that for a U-lock, lock avoidance will not be done, which as well means, CLSN und PUNC-bit won't be checked.
--------------------------------------------------------------------------------- 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
5059 193 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Fri, 13 Apr 2007 10:12:10 +0100589_iso-8859-1 Hi Isaac
As a certified (certifiable?) pedant, I must disagree with much that you say.
1. I think my main disagreement is with your definition of Lock Avoidance. You seem to use the term to mean only avoidance of locks on qualifying rows (or the pages containing qualifying rows). I define it as the use of page latches, CLSN testing and PUNC testing to avoid locks on rows (or pages) regardless of whether the rows are qualifying or non-qualifying. This I believe is how the term was defined when first introduced in Version 3. See for example the Version 4 [...]
5253 62 46_DB2 for Z/OS test plan checklist for migration17_Gugel, Rich - DOA24_rich.gugel@WISCONSIN.GOV31_Fri, 13 Apr 2007 07:57:54 -0500299_us-ascii Hello folks, I was hoping that one of you had come across a presentation or article that you could share that gives a general outline or a checklist of how an applications group might go about testing a new release. We are going from V7 to V8 in this case, but I'd welcome any version [...]
5316 28 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 13 Apr 2007 08:40:57 -0500365_- Mike
In the meantime, I am sure, that U-locks cannot take advantage of lock avoidance. Due to your manual hints, I can proof it. First of all the redbook "Locking in DB2 for MVS/ESA Environment" states (page 22):
Users have no direct control over the use of lock avoidance. To take advantage of lock avoidance machanisms, application must use: [...]
5345 77 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Fri, 13 Apr 2007 16:12:40 +0100496_iso-8859-1 Hi Walter
Remember that the Redbook was written for Version 4. When Lock Avoidance was first introduced it certainly did not work for updateable cursors, only for read-only and ambiguous. However, I think things have changed since then, which is why the latest references refer to updateable cursors. I have found references to updateable cursors as far back as the Version 5 Admin Guide, which has the same table as the Version 8. Maybe the change came in with Version 5? [...]
5423 177 50_Re: DB2 for Z/OS test plan checklist for migration12_Steve Grimes22_steve_grimes@WUSTL.EDU31_Fri, 13 Apr 2007 10:38:58 -0500480_US-ASCII Hello,
We have "static test plans" for all major applications that we exercise for software/hardware upgrades and/or environment and configuration changes.
Sometimes these are tweaked based on the nature of the change, but basically, they touch every major system function. I have a dated version of this that we used for V6 patches and the V6-V7 upgrade. We have not re-visited this yet for the V8 upgrade; I'm sure the plans will need to be updated. [...]
5601 179 73_DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Fri, 13 Apr 2007 17:27:21 +0100459_us-ascii Is there a way to accomplish the above. We need to be able to do a SELECT statement from CICS and batch to an Oracle database which resides on a Unix box.
Jim McAlpine
Jim McAlpine Email: jim.mcalpine@cedar.com Direct Dial: +44 (0)191 4827856
CedarOpenAccounts PrismTech House 5th Avenue Business Park Team Valley Gateshead Tyne and Wear NE11 ONG United Kingdom Tel 0191 482 7900 Fax 0191 482 7901 www.cedaropenaccounts.com [...]
5781 93 77_Re: DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch12_tim malamphy20_timalamphy@YAHOO.COM31_Fri, 13 Apr 2007 09:50:01 -0700580_iso-8859-1 I believe you would need a product like Oracle Transparent Gateway or Webshpere Information Integrator to accomplish this. --- Jim McAlpine wrote:
> Is there a way to accomplish the above. We need to > be able to do a > SELECT statement from CICS and batch to an Oracle > database which resides > on a Unix box. > > Jim McAlpine > > Jim McAlpine > Email: jim.mcalpine@cedar.com > Direct Dial: +44 (0)191 4827856 > > CedarOpenAccounts > PrismTech House > 5th Avenue Business Park > Team Valley > Gateshead > Tyne and Wear > NE11 ONG [...]
5875 209 77_Re: DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM31_Fri, 13 Apr 2007 12:18:52 -0500499_us-ascii If Oracle supports DRDA server functionality, you can set it up as a remote location and access it via your local DB2. I don't know if Oracle does that out of the box or needs an add on product to act as DRDA application server. HTH Khalid
"Jim McAlpine" Sent by: "DB2 Data Base Discussion List" 04/13/2007 11:27 AM Please respond to "DB2 Database Discussion list at IDUG" [...]
6085 69 66_Re: [DB2 z/OS V8] Justifying separation of Data Warehouse and OLTP10_Dave Nance16_dav1mo@YAHOO.COM31_Fri, 13 Apr 2007 10:27:43 -0700411_iso-8859-1 Hey Lock, My question would be what would be the additional costs of transferring the data and the creation of those processes. Shouldn't be too big of a deal since you already have some transformation process set up, so it would just be altering those processed. If everything is running well in the single subsystem what is the push to separate? Additional growth, current performance issues? [...]
6155 112 66_Re: [DB2 z/OS V8] Justifying separation of Data Warehouse and OLTP14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Fri, 13 Apr 2007 14:12:53 -0400613_us-ascii Dave,
I agree, there is a cost of data transfer from the current environment to the new DB2 subsystem housing the new data warehouse. There are some challenges in that there is a Lot of data, and some of it is time-sensitive.
As for what brought up the push? Well, we're in V8 NFM and we were really counting on taking advantage of parallelism, DPSIs, MQTs, and StarJoin for data warehouse tables. Additionally, since the DW tables have the usual access profile (all query during the day, all Load/ETL at night), we were looking at different ZParm values, bufferpool sizes/thresholds, [...]
6268 169 21_db2 v8 crash on liunx10_Paul Chang18_pyfchang@YAHOO.COM31_Fri, 13 Apr 2007 11:06:36 -0700583_iso-8859-1
Hi, I am new to the db2. Our db2 v8 on linux crash with the following error in db2diag.log file. Anyone has same issue before? look like MESSAGE : ADM0503C thanks, Paul.
2007-04-13-09.10.16.655879-420 I195843524G484 LEVEL: Error PID : 4301 TID : 3086325440 PROC : db2agent (DEV100) 0 INSTANCE: qa1 NODE : 000 DB : DEV100 APPHDL : 0-432 APPID: GA0101EB.L942.0111EB5CA935 FUNCTION: DB2 UDB, base sys utilities, sqleagnt_sigsegvh, probe:1 MESSAGE : Error in agent servicing application with coor_node: DATA #1 : Hexdump, 2 bytes 0xBFC4AA0E : 0000 [...]
6438 250 77_Re: DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Fri, 13 Apr 2007 14:50:49 -0400536_us-ascii We use Oracle's Access Manager for both.
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine Sent: Friday, April 13, 2007 12:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch
Is there a way to accomplish the above. We need to be able to do a SELECT statement from CICS and batch to an Oracle database which resides on a Unix box. [...]
6689 108 77_Re: DB2 for z/OS version 7 - accessing an Oracle database from CICS and batch23_Walter Trovijo Jr (UOL)19_wtrovijo@UOL.COM.BR31_Fri, 13 Apr 2007 22:11:26 -0300494_iso-8859-1 DB2 for z/OS version 7 - accessing an Oracle database from CICS and batchHi Jim,
I don't know how it works for large volumes - or if you're going to use it for large volumes at all -, but I've done that for small volumes going thru DB2 Connect Enterprise OLE/DB UDF table functions. All you need is Oracle Client installed on DB2 CONEE box. I've coded a rexx stored procedure to both to access and create table UDFs in CONEE to map Oracle tables based on Oracle catalog. [...]
6798 257 43_Re: DB2 V7 z/OS 1.7, locks on FOR UPDATE OF12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 14 Apr 2007 10:24:51 +0300548_iso-8859-1 Hi Mike,
When I say LOCK - I mean LOCK , not a LATCH. The distinction for me is easy - LOCK is registered with the IRLM, LATCH is not. The idea with lock avoidance is about latches instead of locks (about 1/10 the price).
LOCK AVOIDANCE deals with the reading process itself. When you use select for update you need to take locks and register them with IRLM before evaluation. If you use lock avoidance you don't have to do it (you get a latch). If the needed page/row is held by someone else (reading it) and you did [...]