1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2007, week 2 2 117 60_[AD] 32nd Conference of SQLAdria, Ljubljana, 30th March 200713_Maja Markotic21_Maja.Markotic@ZABA.HR30_Thu, 8 Mar 2007 07:16:22 +0100676_UTF-8 Thank you, Robert, I wasn't aware of the naming standard. I'll make sure to apply it next time.

Regards, Maja









"Galambos, Robert" "Maja Markotic" 07.03.2007 16:19 cc Subject FW: [DB2-L] 32nd Conference of SQLAdria, Ljubljana, 30th March 2007



















Maja

You may not be aware that we are trying to enforce a naming standard for the type of message that you have posted here, within DB2-L listserv. That is a prefix of '[AD]' on the subject header. [...] 120 97 49_Re: [Z/os V7] IBM Load utility and Varchar fields28_Chandrasekaran, Balachandran35_balachandran.chandrasekaran@EDS.COM30_Thu, 8 Mar 2007 12:01:00 +0530343_us-ascii

James & Walter ,

Thanks for your answers. The data is unload from the table itself and so, after value of c1, two bytes should have had length of field c2. We too always had VARCHAR as the last column except this one table.

My doubt is : Is this how DB2 will work or something DB2 missed to do it correct ? [...] 218 44 46_Re: z/OS DB2 V8 - Internal handling of UNICODE14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Thu, 8 Mar 2007 07:59:23 +0100487_iso-8859-1 > The SQLs get parsed in UNICODE and DBRM gets built in UNICODE.

That's right. The precompiler will convert your SQL statement itself to Unicode. The only important thing here is that the precompiler's CCSID setting must match your 3270 (screen) setting. Otherwise a con- dition like "... WHERE c LIKE '!%'" might be seen by the precompiler as "... WHERE c LIKE '[%'" (when e.g. mixing CCSID 500 with CCSID 37) and hence by BIND (which will solely parse Unicode). [...] 263 61 26_[MVS] DISTSERV accounting$10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Thu, 8 Mar 2007 09:45:01 +0100436_US-ASCII Esteemed Listers

We have an increasing number of connections to DB2 (v 7) via DDF. These connections are somewhat all included in PLAN DISTSERV but now due to a new accounting policy we have the need to identify

users of each connection via DDF to charge CPU usage to each department/user. I've some options but I'd like to know how other shops do it, if they start new DB2 traces or what DB2 component they [...] 325 107 49_Re: [Z/os V7] IBM Load utility and Varchar fields14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 8 Mar 2007 22:31:56 +1100549_US-ASCII If the data was unloaded from the table itself (using UNLOAD or DSNTIAUL) a set of load control statements should have been generated by the unload process. Why didn't you use this?

James Campbell

On 8 Mar 2007 at 12:01, Chandrasekaran, Balachandran wrote:

> > James & Walter , > > Thanks for your answers. The data is unload from the table itself and > so, after value of c1, two bytes should have had length of field c2. We > too always had VARCHAR as the last column except this one table. > > My doubt is : Is [...] 433 23 26_Datasharing presentation ?13_Hanne Lyssand10_han@VPS.NO30_Thu, 8 Mar 2007 13:18:33 +0100316_UTF-8 Hello dear db2 community

I have just been asked to do a presentation on DB2 datasharing for my technical and operational staff tomorrow morning. It’s just an overview of course, but I have zero to show. I can do it drawing on the white board, but some power point drawings would be of good help. [...] 457 19 34_Problems accessing IDUG code place36_Anne Stout-Delaware Valley UG Leader23_anne.stout@DOWJONES.COM30_Thu, 8 Mar 2007 07:11:24 -0600293_- The IDUG web team would like to thank Hugh Lapham for working with us to debug a registration problem he discovered. To allow Hugh access to the site we worked around his problem by having a web team administrator register him, as we continue to research the root cause of the problem. [...] 477 101 28_DB2 z/OS V71 DCLGEN question15_Chris Hoelscher21_choelscher@HUMANA.COM30_Thu, 8 Mar 2007 09:28:12 -0500375_US-ASCII good morning everyone

i have a table with a column defined as CLOB(51200)

our DCLGEN (V7) utility does not seem to handling this correctly - it generates:

CLOB (4) in the SQL declare portion, and NO PICTURE in the cobol declaration section

i attempted to RTFM and STFW (search the web) and possible came up with a valid translation: [...] 579 290 76_Re: DB2 Z/OS V8 & AIX V9 - Remote stored procedures - Commit / Rollbacklogic15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US30_Thu, 8 Mar 2007 09:47:10 -0500561_US-ASCII Brian,

Thanks for the pointer. We tested this. By default SP is not committing the changes. So client can control the UOW.

Thanks again.

- Pradeep







"Bear, Brian" Sent by: DB2 Data Base Discussion List 03/07/2007 03:59 PM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] DB2 Z/OS V8 & AIX V9 - Remote stored procedures - Commit / Rollback logic [...] 870 151 30_Re: Datasharing presentation ?16_Robert Catterall24_RCatterall@CHECKFREE.COM30_Thu, 8 Mar 2007 09:59:02 -05006_UTF-8 1022 47 30_Re: Datasharing presentation ?14_Larry Kintisch19_lkint@IX.NETCOM.COM30_Thu, 8 Mar 2007 10:03:09 -0500665_iso-8859-1 Hi Hanne, You could download the 10/2006 Redbook "DB2 for z/OS: Data Sharing in a Nutshell" :

http://www.redbooks.ibm.com/abstracts/sg247322.html?Open

You should find some useful stuff in there.

Larry Kintisch ABLE Information Services 845-353-0885



At 07:18 AM 3/8/2007, you wrote: >Hello dear db2 community > >I have just been asked to do a presentation on DB2 datasharing for my >technical and operational staff tomorrow morning. It’s just an overview of >course, but I have zero to show. I can do it drawing on the white board, >but some power point drawings would be of good help. > >Are any of you willing [...] 1070 56 49_z/OS V7 Performance Reporting: Starting from Zero15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Thu, 8 Mar 2007 10:06:51 -0500605_ISO-8859-1 I'm DBA for a small company in NYC which is doing essentially no DB2 subsystem or application performance reporting. We have DB2PM, and are in process of either acquiring OmegamonXE, or switching the whole kit-and-caboodle to BMC Mainview for DB2.

Does anyone have a way for me to put up some basic, generic SMF-based performance reports to just turn the lights on here? I've got no reporting and don't know how to _do_ reporting, but it's important that I start. Is there a book, a website, something in the Code Place, a PDF/PPT/presentation from a conference, which will give me [...] 1127 193 32_Re: DB2 z/OS V71 DCLGEN question13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Thu, 8 Mar 2007 10:23:23 -0500481_us-ascii Hi Chris.

I suggest you open a PMR with IBM.

I have reported a number of features of DCLGEN which didn't seem like the way it should work. Some have been fixed; some not.

Dave





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Chris Hoelscher Sent: Thursday, March 08, 2007 9:28 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/OS V71 DCLGEN question [...] 1321 220 32_Re: DB2 z/OS V71 DCLGEN question16_Michael Liberman18_mliberman@DTCC.COM30_Thu, 8 Mar 2007 15:18:16 +0000697_US-ASCII Hi Chris,

Check the following link, It has information about LOB's and DCLGEN

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc/db2prodhome.htm

hope this will help.



------------------------------------------------------ Michael Liberman Database Application Administration The depository Trust and Clearing corp. Mliberman@dtcc.com ------------------------------------------------------





Chris Hoelscher Sent by: DB2 Data Base Discussion List 08/03/2007 14:28 Please respond to DB2 Database Discussion list at IDUG [...] 1542 131 43_Betr.: [DB2-L] DB2 z/OS V71 DCLGEN question16_laurens Zwanepol31_lbn.zwanepol@BELASTINGDIENST.NL30_Thu, 8 Mar 2007 16:37:07 +0100955_us-ascii Chris,

Our DCLGEN utility generates the following:

***************************** Top of Data ****************************** ****************************************************************** * DCLGEN TABLE(RESUME) * * LIBRARY(A21G098.ZWANL02.A01.INCLLIB(RESUME)) * * ACTION(REPLACE) * * LANGUAGE(COBOL) * * STRUCTURE(DCLRESUME) * * APOST * * ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS * ****************************************************************** EXEC SQL DECLARE RESUME TABLE ( EMPNO CHAR(6) NOT NULL, FORMAT CHAR(10) NOT NULL, RESUME_ROWID ROWID NOT NULL, INHOUD BLOB(15728640) ) END-EXEC. ****************************************************************** * COBOL DECLARATION FOR TABLE RESUME * ****************************************************************** 01 DCLRESUME. 10 EMPNO PIC X(6). 10 FORMAT PIC X(10). 10 RESUME-ROWID USAGE SQL TYPE IS ROWID. 10 INHOUD USAGE SQL TYPE IS BLOB-LOCATOR. [...] 1674 93 81_Re: Webcast: 27th Feb - The Big DB2 Tuning Lever - Buffer Pools in a 64-Bit World15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Thu, 8 Mar 2007 10:44:10 -0500621_ISO-8859-1 Phil,

Is this webcast stored for replay or likely to be rebroadcast at some point?



On 2/22/07, Grainger, Phil wrote: > > Apologies for two webcast plugs in the same day - but at least I got > mine in first :) > > Now that we have gigabytes of memory available, many have the incorrect > idea that simply making pools much larger will always improve > performance. Join CA and Joel Goldstein of Responsive Systems to learn > about industry-proven methodologies for maximizing performance while > optimizing memory utilization. A Hit Ratio is a useless metric - [...] 1768 27 52_[DB2 Z/OS] Question on DB2 PM - Performance Database14_Nguyen Ductuan17_ndt.db2@GMAIL.COM30_Thu, 8 Mar 2007 09:54:37 -0600495_- Dear all ,

We just installed the package " Omegamon XE for DB2 Performance Monitor on Z/OS" , and i am trying to feed the Performance Database , in order to be able to retrieve historical accounting , stats ... information from DB2 tables.

We used BMC Data Performance Report , and with DPR we can have a summarized information (by hour, by day ...). I've read the documentation of DB2 PM , but it seems that it is not possible to have the data aggregated (by the product). [...] 1796 31 51_[JOB POSTING] DB2 DBA needed in Olympia, Washington18_Longnecker, Dennis31_Dennis.Longnecker@COURTS.WA.GOV30_Thu, 8 Mar 2007 08:11:07 -0800363_us-ascii The Administrative Office of the Courts, in Olympia, Washington is currently recruiting for a Senior Data Base Analyst. We are small government agency in Olympia, WA are a seeking a data base administrator for DB2 on z/OS.

For application information visit our web site at www.courts.wa.gov/employ. CLOSING DATE: 5:00 p.m. on March 14, 2007. [...] 1828 32 37_Question about "DISPLAY PROCEDURE(*)"13_Miguel Villar23_mvillarg@BANCOPASTOR.ES30_Thu, 8 Mar 2007 10:15:14 -0600353_- After "DISPLAY PROCEDURE(*)" we get some counters, one of them is MAXQ.

Supose PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV APS0002 STARTED 0 0 8 0 0 DSNPWLM The book DB2 UDB for z/OS Version 8, says about MAXQ "The maximum number of threads that have waited concurrently for the procedure to be scheduled since DB2 was started" [...] 1861 153 37_Question about "DISPLAY PROCEDURE(*)"13_Miguel Villar23_mvillarg@BANCOPASTOR.ES30_Thu, 8 Mar 2007 17:39:03 +0100362_ISO-8859-1 After "DISPLAY PROCEDURE(*)" we get some counters, one of them is MAXQ.

Supose PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV APS0002 STARTED 0 0 8 0 0 DSNPWLM The book DB2 UDB for z/OS Version 8, says about MAXQ "The maximum number of threads that have waited concurrently for the procedure to be scheduled since DB2 was started" [...] 2015 263 40_Re: V8 CM - DISTSERV select from catalog11_John Miller26_John.L.Miller@ATCOITEK.COM30_Thu, 8 Mar 2007 09:55:29 -0700428_us-ascii Thanks Guys! But since I'm only in CM I don't think I can use any fancy CCSID CASTs. Am I stuck with my program translation until we move to NFM?

Thanks, John

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell Sent: Wednesday, March 07, 2007 4:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] V8 CM - DISTSERV select from catalog [...] 2279 14 17_DB2 DSNJSLR MACRO7_D Ghosh21_the_ghosh@HOTMAIL.COM30_Thu, 8 Mar 2007 12:04:00 -0600738_- As we all know DSNJSLR macro is a very powerful macro to understand db2 log. As IBM does provide a sample application in admin guide. We will see a BCTR R9,R0 instruction used in that sample code.

Can anybody tell me what was the purpose of that instruction.

--------------------------------------------------------------------------------- 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 2294 50 21_Re: DB2 DSNJSLR MACRO12_McKown, John29_John.Mckown@HEALTHMARKETS.COM30_Thu, 8 Mar 2007 12:34:38 -0600515_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of D Ghosh > Sent: Thursday, March 08, 2007 12:04 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] DB2 DSNJSLR MACRO > > > As we all know DSNJSLR macro is a very powerful macro to understand > db2 log. As IBM does provide a sample application in admin guide. > We will see a BCTR R9,R0 instruction used in that sample code. > > Can anybody tell me what was the purpose of that instruction. [...] 2345 158 37_Question about "DISPLAY PROCEDURE(*)"13_Miguel Villar23_mvillarg@BANCOPASTOR.ES30_Thu, 8 Mar 2007 17:39:03 +0100362_ISO-8859-1 After "DISPLAY PROCEDURE(*)" we get some counters, one of them is MAXQ.

Supose PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV APS0002 STARTED 0 0 8 0 0 DSNPWLM The book DB2 UDB for z/OS Version 8, says about MAXQ "The maximum number of threads that have waited concurrently for the procedure to be scheduled since DB2 was started" [...] 2504 82 55_Re: [DB2 NFM z/OS] My special friend: the DB2 optimizer13_Terry Purcell18_tpurcel@US.IBM.COM30_Thu, 8 Mar 2007 13:12:31 -0600561_- Hi Walter,

Optimizer does choose the lowest cost path, so obviously optimizer believes that your original plan is the lowest total cost.

The problem with your comparison technique of using DSN_STATEMNT_TABLE is that this only reports the estimated CPU cost. This information is there for use by predictive govenor.

Optimizer takes into consideration the CPU and I/O cost when determining which is the lowest cost. Thus using the DSN_STATEMNT_TABLE output to compare which is lowest total cost is incorrect usage of this information. [...] 2587 61 40_DB2 V8.1 for z/OS, CICS TS 2.3, and JDBC12_Allen Jaques27_allen_jaques@FREDDIEMAC.COM30_Thu, 8 Mar 2007 14:31:55 -0500513_US-ASCII Is anyone using JDBC under CICS TS 2.3 to access DB2 V8.1 data? We are trying to configure the CICS JVM sample profile, DFHJVMPR, to use the JDBC DB2 Universal Driver to interface with one of our test DB2 V8.1 subsystems and are not having much luck at all.

The Universal Driver works just fine with the DB2 IVP we have tested with (both Type 2 and Type 4 connectivity); we are seeing all the expected results from the IVP. The Universal Driver is just not working for us with CICS, however. [...] 2649 80 26_CA-Insight vs Tmon for DB218_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Thu, 8 Mar 2007 14:47:21 -0500373_us-ascii Now this may be a loaded-question but is anyone who is using CA-Insight willing to share some info with me as to how they like the tool or dislike it? And if they are familiar with TMON for DB2, how they think it compares to that tool.

We currently use TMON for DB2 and I'm wondering how CA-Insight stacks up against it for features and functionality. [...] 2730 241 41_Re: Question about "DISPLAY PROCEDURE(*)"11_Bear, Brian23_Brian.Bear@CHARMING.COM30_Thu, 8 Mar 2007 15:26:16 -0500526_iso-8859-1 I think it's the High Water Mark (HWM) of how many threads were waiting at the same time for a procedure to start. So your output probably says that at a given moment in time, there were 8 theads all waiting for procedure APS0002 to start.



________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Miguel Villar Sent: Thursday, March 08, 2007 11:39 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Question about "DISPLAY PROCEDURE(*)" [...] 2972 424 30_Re: CA-Insight vs Tmon for DB214_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Thu, 8 Mar 2007 14:42:48 -0600373_us-ascii I am very familiar with both.





I can state that every monitor has it's perks.

I can also state that every monitor has a skeleton or two in the closet where they do something REALLY BADLY

Some do a function that the others do not do.

Some are more accurate in one regard than the other (relative to a specific stat). [...] 3397 85 45_DB2 Z/os V8: Trigger with select, then update11_Mike Jessen22_jessenmike@HOTMAIL.COM30_Thu, 8 Mar 2007 14:58:24 -0600375_iso-8859-1 Does anyone have an example of an After update trigger that selects a value from Table A, then uses that value in a subsequent update statement.

Here's what we are trying to do:

After Update Trigger defined on TableB. Select field from TableC where conditions = ...; Update TableA Set field = value from Tablec, field2 = value from tableb, etc. [...] 3483 165 49_Re: DB2 Z/os V8: Trigger with select, then update13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 8 Mar 2007 16:07:33 -0500360_iso-8859-1 Rather than a select followed by an update, this sounds like an update with a subselect in the SET clause...

Update TableA Set field = (SELECT value from Tablec WHERE ...) ,field2 = value from tableb, etc..

Use with caution... if the subselect doesn't return anything you get nulls. If it returns more than 1 row, you get a -811. [...] 3649 212 49_Re: DB2 Z/os V8: Trigger with select, then update13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 8 Mar 2007 16:22:48 -0500445_iso-8859-1 P.S. This is V8 syntax, so you need to be in new function mode V8 to do this.





-----Original Message----- From: DB2 Data Base Discussion List on behalf of David Simpson Sent: Thu 3/8/2007 4:07 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Z/os V8: Trigger with select, then update

Rather than a select followed by an update, this sounds like an update with a subselect in the SET clause... [...] 3862 26 31_converting UDB DDL to OS390 DDL8_Nick Nur17_nick_nur@YAHOO.CA30_Thu, 8 Mar 2007 15:15:50 -0600361_- Hi colleagues

I wonder if anybody can give me advice as how to handle the following situation. I am given a large file (about 1400 tables)containing DB2 UDB DDL mostly default values that I must implement on DB2 OS390.

What is the best and fastest way of converting these DB2 UDB DDL to DB2 OS390 DDL and customizing it to performing well. [...] 3889 90 30_Re: Datasharing presentation ?15_nguyen duc tuan17_ndt.db2@GMAIL.COM30_Thu, 8 Mar 2007 22:46:54 +0100616_ISO-8859-1 there is also 2 webcast available for download from the IBM/DB2 site. If you cannot find them, just drop me a word. Regards



On 3/8/07, Larry Kintisch wrote: > > Hi Hanne, > You could download the 10/2006 Redbook "DB2 > for z/OS: Data Sharing in a Nutshell" : > > http://www.redbooks.ibm.com/abstracts/sg247322.html?Open > > You should find some useful stuff in there. > > Larry Kintisch ABLE Information Services 845-353-0885 > > > At 07:18 AM 3/8/2007, you wrote: > >Hello dear db2 community > > > >I have just been asked to do a presentation on DB2 datasharing [...] 3980 45 49_Re: DB2 Z/os V8: Trigger with select, then update11_Denis - DB219_denis.db2@GMAIL.COM30_Thu, 8 Mar 2007 20:55:47 -0300571_ISO-8859-1 You may be looking for something like this:

CREATE TRIGGER ACME AFTER UPDATE ON TABLEB REFERECING NEW as N OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE TABLEA SET FIELD1 = (Select field from TableC where conditions = ...), FIELD2 = N.COLUMN ETC..

Mike Jessen escreveu: > Does anyone have an example of an After update trigger that selects a > value from Table A, then uses that value in a subsequent update statement. > > Here's what we are trying to do: > > After Update Trigger defined on TableB. > Select field from TableC where [...] 4026 50 30_Re: [MVS] DISTSERV accounting$14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Mar 2007 12:08:08 +1100327_US-ASCII The correct way is to upgrade to DB2 V8 (well you will be doing this in the next 387 days, won't you). You can then use the CLIENT_USERID special register on both the client and zOS host.

I believe it is also recorded in the correlation header - where you should be able to use it for accounting purposes. [...] 4077 63 32_Re: DB2 z/OS V71 DCLGEN question14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Mar 2007 12:08:08 +1100373_US-ASCII The correct translation is USAGE SQL TYPE IS CLOB(51200). However it is possible that IBM deliberately use lob locators in the generated copy code.

If you used a CLOB-LOCATOR what you get (from a SELECT or FETCH) is a pointer to the data. You have to then use more SQL to extract the actual data. Necessary if the CLOB is 2GB in size; not if its 51KB. [...] 4141 42 35_Re: converting UDB DDL to OS390 DDL14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Mar 2007 12:08:09 +1100563_US-ASCII Most of the database ER design tools have a facility to import DDL and export DDL in/from a variety of database dialects. Search the archives for tools, links etc.

James Campbell



On 8 Mar 2007 at 15:15, Nick Nur wrote:

> Hi colleagues > > I wonder if anybody can give me advice as how to handle the following > situation. I am given a large file (about 1400 tables)containing DB2 UDB > DDL mostly default values that I must implement on DB2 OS390. > > What is the best and fastest way of converting these DB2 UDB DDL to [...] 4184 446 49_Re: DB2 Z/os V8: Trigger with select, then update14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Mar 2007 12:08:10 +1100571_US-ASCII Update TableA Set field = (SELECT VALUE(MAX(value),0) from Tablec WHERE ...) ... gets around both the problems in the last paragraph.

James Campbell

On 8 Mar 2007 at 16:07, David Simpson wrote:

> > Rather than a select followed by an update, this sounds like an update with a subselect in the > SET clause... > > Update TableA Set field = (SELECT value from Tablec WHERE ...) > ,field2 = value from tableb, etc.. > > Use with caution... if the subselect doesn't return anything you get nulls. If it returns more than 1 > row, you get a [...] 4631 41 48_after CCSID conversion compile-link-bind problem15_Yadigar Dirican18_ydirican@YAHOO.COM30_Thu, 8 Mar 2007 23:59:57 -0800441_iso-8859-1 Hi,

We converted SCCSID from 500 to 1026. Generakky everything was okay. But some programs needed compile-link and bind. When I looked up them they includes dynamic SQL statements? I could not understand why was it need.

Environment: z/os V1.7 DB2 V7.1

Thank you in advance.



--------------------------------- It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. [...] 4673 67 44_(Fwd) Re: [DB2-L] [MVS] DISTSERV accounting$14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Mar 2007 20:20:03 +1100506_US-ASCII Ooops, just checked a V7 IFCID format - QWHCEUID DS CL16 /*THE END USER'S USERID AT THE */ /* USER'S WORKSTATION. */ It is there, just not easily usable by an application program. But if you use the IFCIDs for charging ...

James Campbell

------- Forwarded message follows ------- From: Self To: DB2 Database Discussion list at IDUG Subject: Re: [DB2-L] [MVS] DISTSERV accounting$ Date sent: Fri, 09 Mar 2007 12:02:00 +1100 [...] 4741 42 48_Re: (Fwd) Re: [DB2-L] [MVS] DISTSERV accounting$10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Fri, 9 Mar 2007 10:44:44 +0100382_US-ASCII Hi James

Thank you very much for replies. We're planning V7>V8 migration for 2nd half of 2007. Maybe with MXG we can use this information even in V7. I'll check it but first have to understand what our admin wants......

Thank you again and best regards

Max Scarpa

--------------------------------------------------------------------------------- 4784 50 12_Index-Checks35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Fri, 9 Mar 2007 10:51:19 +0100559_iso-8859-1 Hi everyone out there,

I'm posting this on behalf of a colleague :

We have a given set of tables that all are the same; same number of columns, same solumn-names, only the tablenames and creators differ. All tables should ideally have the same set of indexes; now I want to be on the safe side and check via SQL if all the tables do have the same indexes, i.d. the indexes have the same columns with the right position and the right ordering, if not, I want a report of those indexes that are either missing or have differences. [...] 4835 59 56_Re: [DB2 Z/OS] Question on DB2 PM - Performance Database3_Les21_db2dba@BTINTERNET.COM30_Fri, 9 Mar 2007 09:55:04 -0000326_us-ascii Hi

Sorry for my usual quick reply but this can be done as I do it with our data.

I'll look into how I set it up and come back to you but it will not be until next week as I am going away (right now) for four days. I will come back to you if you do not have the answer before then (Wednesday next). [...] 4895 117 56_Re: [DB2 Z/OS] Question on DB2 PM - Performance Database15_nguyen duc tuan17_ndt.db2@GMAIL.COM30_Fri, 9 Mar 2007 11:49:21 +0100486_ISO-8859-1 thank you Leslie ,

i am looking on "REDUCE" and "SAVE-FILE" ... the doc. is really short on this part .





On 3/9/07, Les wrote: > > Hi > > Sorry for my usual quick reply but this can be done as I do it with our > data. > > I'll look into how I set it up and come back to you but it will not be > until > next week as I am going away (right now) for four days. I will come back > to > you if you do not have the answer [...] 5013 118 27_Betr.: [DB2-L] Index-Checks16_laurens Zwanepol31_lbn.zwanepol@BELASTINGDIENST.NL30_Fri, 9 Mar 2007 11:19:19 +0100511_iso-8859-1 Rüdiger,

You could use "DB2 Object Comparison Tool" off IBM to discover differences between two database. As always, there are also other tools that can do the trick for you.



Mvgr,

Laurens Zwanepol B/CICT Quintax BPA A.017 Tel: 055-528 3250 B.g.g. 06-1377 9631





"Kurtz, Rüdiger" Aan Verzonden door: DB2-L@WWW.IDUGDB2-L.ORG "DB2 Data Base Cc Discussion List" [DB2-L] Index-Checks [...] 5132 196 31_Re: Betr.: [DB2-L] Index-Checks13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 9 Mar 2007 11:02:54 -0000588_iso-8859-1 Oh, OK, here goes. Ahem...

BMC has a product called Change Manager that can compare object structures and tell you what's different, and build DDL to get you from one t'other. Apparently.

Now I get to sit back and wait for the howls of protest. Seriously, Rudiger, along with IBM and BMC, I'm sure CA also have a product to compare structures and I'm also sure there will be others. Doing it yourself by querying the Catalog is a lot harder, but also cheaper. You gets what you pays for, I'm afraid. The Code [...] 5329 246 39_AW: [DB2-L] Betr.: [DB2-L] Index-Checks35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Fri, 9 Mar 2007 12:40:22 +0100849_iso-8859-1 Raymond,

as it so happens we do have BMC's Change Manager, but not for the 'little' flavours of DB2, only for the 'real thing'. I should've mentioned what sort of os I was talking about.

Thanks all the same and all the best

Rüdiger Kurtz Abteilung Informatik Betrieb

HUK-COBURG Bahnhofsplatz 96444 Coburg Telefon 09561 96-3914 Telefax 09561 96-3678 E-Mail ruediger.kurtz@huk-coburg.de ============================================================= HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg Reg.-Gericht Coburg HRB 100 Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg Vorsitzender des Aufsichtsrats: Werner Strohmayr. Vorstand: Rolf-Peter Hoenen (Sprecher), Dieter Beck, Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Christian Hofer, Dr. [...] 5576 177 39_AW: [DB2-L] Betr.: [DB2-L] Index-Checks35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Fri, 9 Mar 2007 12:41:22 +0100763_iso-8859-1 Laurens,

thanks a lot but see my answer to Raymond.

Cheers

Rüdiger Kurtz Abteilung Informatik Betrieb

HUK-COBURG Bahnhofsplatz 96444 Coburg Telefon 09561 96-3914 Telefax 09561 96-3678 E-Mail ruediger.kurtz@huk-coburg.de ============================================================= HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg Reg.-Gericht Coburg HRB 100 Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg Vorsitzender des Aufsichtsrats: Werner Strohmayr. Vorstand: Rolf-Peter Hoenen (Sprecher), Dieter Beck, Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Christian Hofer, Dr. Wolfgang Weiler. ============================================================= [...] 5754 174 43_Re: Long DB2 Prepares for PSAESRV Processes14_Clayton, Colin23_Colin.Clayton@RBS.CO.UK30_Fri, 9 Mar 2007 14:12:17 -0000302_- Hi Terry,

We are now running with TABLES_JOINED_THRESHOLD = 10 and MXQBCE = 1023 in Production and I'm happy to report that our PREPARE times have come down from an average of 60 secs to an average of 3 secs !

We did this in a series of steps over several days and results were... [...] 5929 82 16_Re: Index-Checks14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET30_Fri, 9 Mar 2007 06:16:56 -0800660_iso-8859-1 If you simply looking for sql, you can query the SYSIBM tables to get the information. Maybe something as follow might help, but this only list the attributes for a given table for a specific owner. This should get you started.

SELECT CHAR(IX.TBNAME,18) AS TBNAME , CHAR(IX.NAME,8) AS NAME , CHAR(IK.COLNAME,18) AS COLNAME , C.COLTYPE , C.LENGTH , C.SCALE , C.NULLS FROM SYSIBM.SYSINDEXES IX , SYSIBM.SYSKEYS IK , SYSIBM.SYSCOLUMNS C WHERE IX.NAME = IK.IXNAME AND IX.CREATOR = IK.IXCREATOR AND IX.TBNAME = C.TBNAME AND IX.TBCREATOR = C.TBCREATOR AND IK.COLNAME = C.NAME AND IX.TBNAME = AND IX.CREATOR = ORDER BY [...] 6012 74 16_Re: Index-Checks10_Hays, John15_JHays@SFBLI.COM30_Fri, 9 Mar 2007 08:18:51 -0600445_iso-8859-1 We use a PC based product called Advance Query Tool. This is very inexpensive product that works great. We currently have 100 licenses.

http://www.querytool.com/



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kurtz, Rüdiger Sent: Friday, March 09, 2007 3:51 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Index-Checks

Hi everyone out there, [...] 6087 76 30_Re: Datasharing presentation ?14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET30_Fri, 9 Mar 2007 06:28:58 -0800500_iso-8859-1 You might also want to check..

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.dshare/bjnzspsh.htm

nguyen duc tuan wrote: there is also 2 webcast available for download from the IBM/DB2 site. If you cannot find them, just drop me a word. Regards



On 3/8/07, Larry Kintisch wrote: Hi Hanne, You could download the 10/2006 Redbook "DB2 for z/OS: Data Sharing in a Nutshell" : [...] 6164 39 52_Re: after CCSID conversion compile-link-bind problem12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Mar 2007 08:18:10 -0600398_- The CCSID information is put into many places in the catalog and directory, including in your plans and packages. You don't tag the text with a CCSID, so the tagging is on the database objects, plans & packages. The precompiler has a specification in V8, but you're not there yet. I do hope you're migrating soon, as V7 has been GA for six years now and will be out of service in 15 months. [...] 6204 120 24_AW: [DB2-L] Index-Checks35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Fri, 9 Mar 2007 15:42:52 +0100751_iso-8859-1 Thank you, I'll pass the information on.

Best regards

Rüdiger Kurtz Abteilung Informatik Betrieb

HUK-COBURG Bahnhofsplatz 96444 Coburg Telefon 09561 96-3914 Telefax 09561 96-3678 E-Mail ruediger.kurtz@huk-coburg.de ============================================================= HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg Reg.-Gericht Coburg HRB 100 Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg Vorsitzender des Aufsichtsrats: Werner Strohmayr. Vorstand: Rolf-Peter Hoenen (Sprecher), Dieter Beck, Wolfgang Flaßhoff, Stefan Gronbach, Klaus-Jürgen Heitmann, Christian Hofer, Dr. Wolfgang Weiler. ============================================================= [...] 6325 100 49_Re: [MVS] DISTSERV accounting$-Follow-Up Question11_Bear, Brian23_Brian.Bear@CHARMING.COM30_Fri, 9 Mar 2007 09:46:36 -0500400_US-ASCII Just curious about James' rhetorical about upgrading within the next 387 days.

How many of you can use the fact that DB2 will be out of support as the reason to upgrade?

My shop does not see that as reason enough, unfortunately. I have to "sell" the reasons. There needs to be a substantial benefit to upgrading that will benefit the company's productivity or capability. [...] 6426 20 33_Lock escalation with data sharing13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Fri, 9 Mar 2007 08:54:56 -0600771_- Hello,

We're currently running DB2 v7 on z/OS in a data sharing environment. A question has come up regarding row level locking (LOCKSIZE ROW) and lock escalation.

Does lock escalation occur when using LOCKSIZE ROW or does it only occur with LOCKSIZE ANY?

Thanks, Donna Domovic

--------------------------------------------------------------------------------- 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 6447 294 49_Re: [MVS] DISTSERV accounting$-Follow-Up Question12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Fri, 9 Mar 2007 10:07:50 -0500402_US-ASCII We feel that being out of support is a big reason for upgrading. We are still V7 but plans to go to V8 early in 2008. A peopletools upgrade is holding me up. Jeff





"Bear, Brian" Sent by: DB2 Data Base Discussion List 03/09/2007 09:46 AM Please respond to DB2 Database Discussion list at IDUG [...] 6742 105 37_Re: Lock escalation with data sharing15_Moschelli Mauro34_mauro.moschelli@INTESASANPAOLO.COM30_Fri, 9 Mar 2007 16:12:54 +0100411_iso-8859-1 It depends on the LOCKMAX clause on the CREATE TABLESPACE statement. If it is omitted with locksize row there is no escalation by default, but you can set LOCKMAX to enable escalation.

See CREATE TABLESPACE on SQL Reference (LOCKSIZE and LOCKMAX)

HTH





Mauro Moschelli Intesa Sanpaolo S.p.A.

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS [...] 6848 52 35_Re: converting UDB DDL to OS390 DDL12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Mar 2007 09:11:13 -0600463_- What version of DB2 are you using? I guess V7 since you did not say z/OS, but I hope you are moving soon if that is so. The largest effort to make DDL intended for another platform is in DB2 9, with a new table space structure called partition by growth that is the default table space type, and automatic creation of indexes, databases and table spaces. There are some improvements in various defaults in V8 and V9 to make the defaults perform and scale. [...] 6901 325 49_Re: [MVS] DISTSERV accounting$-Follow-Up Question10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Fri, 9 Mar 2007 16:34:03 +0100415_US-ASCII In my previous company we had DB2 V6 and CICS 4.1 (!) by far out of maintenace as the raise in price for migration didn't justify the effort as we didn't need most of the new features so there was no

advantage in migrating. They decided to run the risk in a widely Condolizated and stable environment where the chance for problems were low. And they were right. And mainframe was switched off. [...] 7227 56 53_Re: z/OS V7 Performance Reporting: Starting from Zero12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Mar 2007 09:27:45 -0600318_- Everyone gets to start from zero. DB2 PM has excellent batch reporting and it produces the reports you see in the performance section of the Administration Guide. That is a decent place to start with a couple of chapters, if you like to read, chapters 24 and 25, about 20 pages in the latest book, dsnagh16.pdf [...] 7284 347 81_Re: Webcast: 27th Feb - The Big DB2 Tuning Lever - Buffer Pools in a 64-Bit World14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 9 Mar 2007 15:41:19 -0000623_us-ascii Yes, it was recorded. It's under the webcast replays section of ca.com. Here is the link: http://www3.ca.com/events/eventdetail.aspx?cevid=97753&eis=1



Phil Grainger CA Product Manager Phone: +44 (0)161 929 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com

________________________________



From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Philip Sevetson Sent: 08 March 2007 15:44 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Webcast: 27th Feb - The Big DB2 Tuning Lever - Buffer Pools in a 64-Bit World [...] 7632 29 30_Re: Datasharing presentation ?13_Hanne Lyssand10_han@VPS.NO30_Fri, 9 Mar 2007 17:10:04 +0100889_us-ascii Thanks for a quick reply. The presentation went fine and I gave you and yur company credit for the help!

Have a good weekend :-)

best regards Hanne

(I now start my weekend in the snow in Oslo)





********************************************************************** For Your service, this message has been checked for viruses http://www.vps.no **********************************************************************

--------------------------------------------------------------------------------- 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 [...] 7662 29 35_Re: converting UDB DDL to OS390 DDL14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Fri, 9 Mar 2007 17:48:59 +0100415_iso-8859-1 Differences should be minimal and systematic. Using a decent editor, you should be able to perform any "global" syntax changes with a single operation on the script file. I would try out the resulting DDL on a test database, judge the settings (mainly tablespace and bufferpool choices), and possibly change a few more global things in the script before running the DDL in the destination database. [...] 7692 170 67_DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM30_Fri, 9 Mar 2007 16:42:00 -0000345_iso-8859-1 I'm getting the above when trying to select colums from a table that is granted public. The same user can read this table in spufi. The user does make a connection to the database but gets -551 on the select.

Any ideas.

Jim McAlpine

Jim McAlpine Email: jim.mcalpine@cedar.com Direct Dial: +44 (0)191 4827856 [...] 7863 88 35_Re: converting UDB DDL to OS390 DDL10_Nicola Nur17_nick_nur@YAHOO.CA30_Fri, 9 Mar 2007 11:51:51 -0500571_iso-8859-1 Thanks James and Roger.

We are at DB2 v8 z/OS NFM.

Also an extra concern is some objects like LOBs can be built implicitly by DB2. The concern is about performance. Thanks for your help. Nick Nur

Roger Miller wrote: What version of DB2 are you using? I guess V7 since you did not say z/OS, but I hope you are moving soon if that is so. The largest effort to make DDL intended for another platform is in DB2 9, with a new table space structure called partition by growth that is the default table space type, and [...] 7952 200 71_Re: DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway13_David Simpson22_dsimpson@THEMISINC.COM30_Fri, 9 Mar 2007 11:56:21 -0500631_iso-8859-1 Try granting to PUBLIC AT ALL LOCATIONS

David Simpson Senior Technical Advisor Themis Inc. dsimpson@themisinc.com http://www.themisinc.com





-----Original Message----- From: DB2 Data Base Discussion List on behalf of Jim McAlpine Sent: Fri 3/9/2007 11:42 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway

I'm getting the above when trying to select colums from a table that is granted public. The same user can read this table in spufi. The user does make a connection to the database but gets -551 on the select. [...] 8153 266 71_Re: DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway11_Bear, Brian23_Brian.Bear@CHARMING.COM30_Fri, 9 Mar 2007 12:01:54 -0500611_US-ASCII I know the Oracle gateway does some additional SELECTs on "overhead" tables before the actual user requested select is processed. I one case, I see in my traces that the gateway is issuing

SELECT COUNT(*) FROM "OTGDB2"."DUAL" A1

So the -551 maybe on these overhead tables.

brian



________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine Sent: Friday, March 09, 2007 11:42 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway [...] 8420 306 71_Re: DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM30_Fri, 9 Mar 2007 17:23:36 -0000507_iso-8859-1 I should have added that I can do this to another DB2 subsystem with another userid and that I can do it to the failing DB2 subsystem with a privileged user. Neither system has the GRANT below in place.

Jim McAlpine

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of David Simpson Sent: 09 March 2007 16:56 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway [...] 8727 75 49_Re: [MVS] DISTSERV accounting$-Follow-Up Question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Fri, 9 Mar 2007 10:53:28 -0800569_us-ascii I have effectively used a product's EOS date as justification for upgrade, but it was tricky with DB2 v8 because EOS was not announced until recently, and I wanted to upgrade last year. I used the EOM date in my justification and just mentioned that EOS was soon to follow. They didn't seem real thrilled about it, but they still let me do the upgrade. Several times, and sometimes for long periods of time, we have run software versions beyond their EOS dates, and management was perfectly happy with that. For example, the only thing that got us off of [...] 8803 199 31_Re: Betr.: [DB2-L] Index-Checks13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 9 Mar 2007 11:02:54 -0000588_iso-8859-1 Oh, OK, here goes. Ahem...

BMC has a product called Change Manager that can compare object structures and tell you what's different, and build DDL to get you from one t'other. Apparently.

Now I get to sit back and wait for the howls of protest. Seriously, Rudiger, along with IBM and BMC, I'm sure CA also have a product to compare structures and I'm also sure there will be others. Doing it yourself by querying the Catalog is a lot harder, but also cheaper. You gets what you pays for, I'm afraid. The Code [...] 9003 352 81_Re: Webcast: 27th Feb - The Big DB2 Tuning Lever - Buffer Pools in a 64-Bit World14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 9 Mar 2007 15:41:19 -0000623_us-ascii Yes, it was recorded. It's under the webcast replays section of ca.com. Here is the link: http://www3.ca.com/events/eventdetail.aspx?cevid=97753&eis=1



Phil Grainger CA Product Manager Phone: +44 (0)161 929 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com

________________________________



From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Philip Sevetson Sent: 08 March 2007 15:44 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Webcast: 27th Feb - The Big DB2 Tuning Lever - Buffer Pools in a 64-Bit World [...] 9356 194 31_Re: Betr.: [DB2-L] Index-Checks10_Dave Nance16_dav1mo@YAHOO.COM30_Fri, 9 Mar 2007 13:39:00 -0800627_iso-8859-1 That's it!!! We're gonna have to do something about all these plugs!!!:)!!!



"Bell, Raymond" wrote: Oh, OK, here goes. Ahem...

BMC has a product called Change Manager that can compare object structures and tell you what's different, and build DDL to get you from one t'other. Apparently.

Now I get to sit back and wait for the howls of protest. Seriously, Rudiger, along with IBM and BMC, I'm sure CA also have a product to compare structures and I'm also sure there will be others. Doing it yourself by querying the Catalog is a lot harder, but also cheaper. [...] 9551 100 35_Re: converting UDB DDL to OS390 DDL12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Mar 2007 16:53:34 -0600278_- It's nice when we did something that you need. It would be a lot better if we can get it installed, so would actually help you. In short, the DDL you have would come pretty close in DB2 9 for z/OS if you use the automatic creation of objects and the managed extent size. [...] 9652 206 34_Re: changing archive logs to DASD?11_Mike Kalena16_mkalena@BEAR.COM31_Sat, 10 Mar 2007 16:52:20 -0600467_- Sorry for the delayed reply, I only read DB2-L once a week but feel free to contact me offline at mkalena@bear.com.

We have SVOLARC=NO but our logs are sized for 1 full pack, 3,328 cylinders. We stripe the active logs across 4 packs but the total size is 3,328. We originally had to do that to get SRDF working to our DR site in syncronous mode but now it's no longer necessary as we reconfigured our DR methods, and are using SRDFA to the new DR site. [...] 9859 57 45_Re: DB2 zOS v7.1 First time Use of WLM SP RRS11_Mike Kalena16_mkalena@BEAR.COM31_Sat, 10 Mar 2007 16:59:33 -0600756_- Brian,

I found this on IBM's redbook site:

Systems Programmer's Guide to Resource Recovery Services (RRS)

http://www.redbooks.ibm.com/abstracts/sg246980.html?Open

Should be what you want:

This IBM Redbook gives you a broad understanding of the Resource Recovery Services (RRS) environment. RRS provides a global syncpoint manager that any resource manager on z/OS can exploit. It enables transactions to update protected resources managed by many resource managers. RRS is increasingly becoming a prerequisite for new resource managers, and for new capabilities in existing resource managers. Rather than having to implement their own two-phase commit protocol, these products can use the support provided by RRS. [...] 9917 76 62_v7.1 z/OS: enhancements/changes in V81 available/forced in CM?15_Chris Hoelscher21_choelscher@HUMANA.COM31_Sun, 11 Mar 2007 17:15:32 -0400313_US-ASCII hello all, it is my understanding that many enhancements are made avaialble (in fact go in wanted or not) in Compatibility Mode - is there a list of those features? or in the absence of such a documented list, can the list members suggest what enhancements will be in effect when V8 CM in in place? [...] 9994 202 66_Re: v7.1 z/OS: enhancements/changes in V81 available/forced in CM?13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Sun, 11 Mar 2007 17:31:56 -0400398_us-ascii Hi Chris.

I don't think there is an official list.

What I have been told is None of the enhancements requiring any SQL changes Many of the optimizer enhancements (obviously those not requiring any of the new SQL clauses) All of the Utility improvements not requiring syntax changes.

There may be more.

You can count on hearing from others where I'm wrong. [...] 10197 64 66_Re: v7.1 z/OS: enhancements/changes in V81 available/forced in CM?13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Sun, 11 Mar 2007 16:32:14 -0500403_ISO-8859-1 Because that list could be a moving target, IBM does not formally publish one. With that said, I did take a shot at making such a list of some of the more popular features in my blog back in 2005. Check out http://blogs.ittoolbox.com/database/db2zos/archives/lets-begin-our-compatibility-mode-discussions-6116 as a starting point. The list may have even more features to add to my list. [...] 10262 54 12_TERM UTILITY37_Mohamed Arif (HCL Financial Services)18_mohamedarif@HCL.IN31_Mon, 12 Mar 2007 12:47:31 +0530378_us-ascii Hi,

We are using DB2 v 7.1 in Z/OS environment.

I have issued TERM UTILITY command to terminate a utility. But it is not getting terminated for last 30 minutes.

DIS utility command shows



DSNU106I -DB2P DSNUGDIS - REORG UTILITY, MEMBER = UTILID = IKH2REOR TERMINATING DSN9022I -DB2P DSNUGCCC '-DIS UTILITY' NORMAL COMPLETION *** [...] 10317 124 40_IRLM - Out of space following Drop Table12_Cianci, Nick19_nick.cianci@EDS.COM31_Mon, 12 Mar 2007 18:26:42 +1100516_us-ascii Dear List ,

just wondering if anyone has come across this before.

We dropped a 700K record obsolete table & associated table space. We used UniCenter (R11) in recovery mode to control the drop in case we needed to revert. Table is defined with ERASE NO.

Yet when we drop the table the locking activity is so great that the IRLM has a coronary and we get a -904 with a 00c90092 (IRLM exhausted Virtual Storage). Now I was thinking how could a TableSpace drop repeatedly cause this. [...] 10442 160 16_Re: TERM UTILITY13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 12 Mar 2007 08:32:17 +0100322_US-ASCII My memory of my DB2 days is getting dim, but I seem to remember that this was normal behaviour for the Online REORG utility (I very rarely used non-OLR in V7). I always cancelled the job and then did the -TERM UTIL to clean up. Also check whether one of the involved table- or indexspaces is in STOPP state. [...] 10603 179 44_Re: IRLM - Out of space following Drop Table37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Mon, 12 Mar 2007 08:33:04 +0100443_iso-8859-2 i suppose you didn't run MODIFY RECOVERY for that TS for a long time maybe that to do it even now with appropriate date could hepl but only a gest

regards, dp

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cianci, Nick Sent: Monday, March 12, 2007 8:27 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] IRLM - Out of space following Drop Table [...] 10783 181 44_Re: IRLM - Out of space following Drop Table13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 12 Mar 2007 08:36:37 +0100428_US-ASCII When you drop a table, you also have to drop all dependend objects, like views and the entries in the history tables. But even so, it shouldn't be a problem unless you've defined the history tablespaces with row-level locking and there are millions of rows involved. You _have_ set your IRMLPROC with REGION=0M, I hope (and that there is no installation exit that overrides this spec with REGION=4M or something)? [...] 10965 31 44_Re: IRLM - Out of space following Drop Table14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Mon, 12 Mar 2007 08:49:21 +0100407_iso-8859-1 > The Delete activity was concentrated in SYSCOLDIST_HIST (4K rows) > & SYSCOLUMNS_HIST (6.7K rows).

In this case it's probably an option to delete those rows manually before dropping the object. The "commit" between the catalog deletes and the subsequent drop operations could solve the problem...

Indeed a nice example of the "hidden" cost & overhead of a simple table drop! [...] 10997 340 71_Re: DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Mon, 12 Mar 2007 09:07:30 -0000352_iso-8859-1 How do I find out what grants have been given against a particular object so I can compare my 2 systems.

Jim McAlpine

-----Original Message----- From: Jim McAlpine Sent: 09 March 2007 17:24 To: 'DB2 Database Discussion list at IDUG' Subject: RE: [DB2-L] DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway [...] 11338 30 71_Re: DB2 for z/OS version 7 - getting -551 on SELECT from Oracle gateway14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Mon, 12 Mar 2007 10:30:01 +0100607_iso-8859-1 Object grants are in different places in the Catalog, depending on the object and the type of autorization. From SELECT autorizations on tables and views, run the following query (e.g. in SPUFI):

SELECT grantee, location, rtrim(tcreator)||'.'||ttname AS table_or_view FROM sysibm.systabauth WHERE selectauth IN ('Y','G') ORDER BY 2









_________________________________________________________ Flyger tiden iväg? Fånga dagen med Yahoo! Mails inbyggda kalender. Dessutom 250 MB gratis, virusscanning och antispam. Få den på: http://se.mail.yahoo.com [...] 11369 66 97_[AD] Webcast March 13th: DB2 9 for z/OS - An Independent Overview - POSSIBLE TIME CHANGE FOR YOU!14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 12 Mar 2007 11:01:40 -0000383_us-ascii Just a polite note to all the NON-US folks who may have registered for my Db2 9 webcast tomorrow

Most of the US put their clocks on an hour over the weekend, so although the webcast is still scheduled for 12.00pm (noon) Eastern Daylight Time, it will now happen an hour EARLIER in most of the rest of the world (e.g. 4pm in the UK and 5pm in the rest of Europe) [...] 11436 84 54_Re: Long DB2 Prepares for PeopleSoft PSAESRV Processes14_Clayton, Colin23_Colin.Clayton@RBS.CO.UK31_Mon, 12 Mar 2007 11:07:19 -0000515_- Hi Terry,

We are now running with TABLES_JOINED_THRESHOLD = 10 and MXQBCE = 1023 in Production and I'm happy to report that our PREPARE times have come down from an average of 60 secs to an average of 3 secs !

We did this in a series of steps over several days and results were... 12 / 4095 ; ~ 12 cpu seconds, about 70K service units 11 / 2047 ; ~ 6 cpu seconds, about 31K service units 10 / 1023 ; ~ 3 cpu seconds, about 17K service units I'd like to thank you, everyone else who contributed [...] 11521 82 36_Update problems DB2 V7 and Z/OS V1.70_26_wdavies@CO.EL-DORADO.CA.US31_Mon, 12 Mar 2007 07:36:55 -0700509_US-ASCII Hi all:

We are converting our system to a new box and operating system. We are going to a Z9 with Z/OS 1.7 from an older box with Z/OS 1.4. When we bring up DB2 on the new operating system we are able to select from tables but we are not able update, delete or insert into any table. In trying from CA's RC/Update I get a DSNT408I SQLCODE= -924 SQLSTATE = 58006, system error from ? then next screen I get DSNT408I SQLCODE= -923, Error: Connection not established DB2, reason, type, name [...] 11604 188 40_Re: Update problems DB2 V7 and Z/OS V1.714_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 12 Mar 2007 14:43:30 -0000557_us-ascii Walter

To remove the confusion about where the problem lies, what do you get when you try a SELECT from SPUFI??

Phil Grainger CA Product Manager Phone: +44 (0)161 929 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of wdavies@CO.EL-DORADO.CA.US Sent: 12 March 2007 14:37 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Update problems DB2 V7 and Z/OS V1.7 [...] 11793 233 40_Re: Update problems DB2 V7 and Z/OS V1.70_26_wdavies@CO.EL-DORADO.CA.US31_Mon, 12 Mar 2007 07:48:21 -0700424_US-ASCII I get the same error message on the log regardless of where I try to update from so it is not strictly a CA problem.

Thank You Walter Davies DBA El Dorado County









"Grainger, Phil" Sent by: DB2 Data Base Discussion List 03/12/2007 07:43 AM Please respond to DB2 Database Discussion list at IDUG [...] 12027 162 40_Re: Update problems DB2 V7 and Z/OS V1.70_17_sjvagnier@AEP.COM31_Mon, 12 Mar 2007 11:00:48 -0400435_US-ASCII Walter, if your security software is RACF, check to see if there is a new RACF security default profile that is restricting the DB2 userid running the started address space xxxxDBM1 from making updates.





**************************************** Steve Vagnier American Electric Power One Riverside Plaza - 7th Floor Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677 [...] 12190 43 28_Help on a long running query12_Vik Krishnan22_vkrishn@TRANSUNION.COM31_Mon, 12 Mar 2007 08:58:09 -0600417_- We have a query that is really running long which i think should be pretty quick.

select A, B, C, count(*) from prod.acct_acty acv inner join prod.account acc on acv.party_id = acc.party_id and acv.acct_id = acc.acct_id where acv.rsub_base_cde = ? and acv.rsub_sfx_cde = ? and acv.curr_bal_amt > 0 and acv.eff_dte between '1997-06-01' and '2006-12-01' and acc.clsd_dte is null group by A, B, C with ur; [...] 12234 31 40_Re: Update problems DB2 V7 and Z/OS V1.710_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Mon, 12 Mar 2007 16:19:36 +0100147_US-ASCII Is ERLY code loaded correctly ?

Max Scarpa

--------------------------------------------------------------------------------- 12266 229 40_Re: Update problems DB2 V7 and Z/OS V1.712_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Mon, 12 Mar 2007 15:28:35 -0000467_iso-8859-1 The only reference I can find to this sort of error is a very old information apar which related to RACF profiles required to connect to DB2. Have you copied across your RACF database.

Jim McAlpine

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of wdavies@CO.EL-DORADO.CA.US Sent: 12 March 2007 14:37 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Update problems DB2 V7 and Z/OS V1.7 [...] 12496 315 40_Re: Update problems DB2 V7 and Z/OS V1.715_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK31_Mon, 12 Mar 2007 16:01:51 +0000602_us-ascii Hi Walter,

z/OS v1.7 introduces certain requirements for the RACF DSNR class, which can adversely affect (i.e. stop!) certain DB2 functionality.

As Jim suggests, this could be the cause of your problem.

Your z/OS or RACF sysprogs should (hopefully) be aware of this. Sorry I can't be more specific.

Cheers,

Gordon Fishwick. SSE plc.





Jim McAlpine Sent by: DB2 Data Base Discussion List 12/03/2007 15:28 Please respond to DB2 Database Discussion list at IDUG [...] 12812 88 66_Re: v7.1 z/OS: enhancements/changes in V81 available/forced in CM?0_22_sally.mir@WACHOVIA.COM31_Mon, 12 Mar 2007 12:22:03 -0400400_US-ASCII IDUG 2007 NA plug:

Session B04: Stuck in Compatibility Mode? How to Make the Best of It

May 7, 2007 4;20 PM

This presentation contains information on some of the important features that are available in CM.



Sally A. Mir, AVP Wachovia Enterprise Data Management IBM Certified Database Administrator DB2 Universal Database V8.1 for Z/OS (336) 773-4011 [...] 12901 23 32_Re: Help on a long running query14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Mon, 12 Mar 2007 17:32:32 +0100814_iso-8859-1 What's the access path reported by EXPLAIN? And the cost calculation of EXPLAIN?









_________________________________________________________ Flyger tiden iväg? Fånga dagen med Yahoo! Mails inbyggda kalender. Dessutom 250 MB gratis, virusscanning och antispam. Få den på: http://se.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 12925 190 40_Re: Update problems DB2 V7 and Z/OS V1.713_Carol Broyles21_clbroyles55@YAHOO.COM31_Mon, 12 Mar 2007 10:20:08 -0700630_iso-8859-1 Hi Walter, Do you use RACF? You may need to define DSNR class profiles for your DB2 systems. I think this was a loophole that was closed up in z/OS 1.6. Carol ----- Original Message ---- From: "wdavies@CO.EL-DORADO.CA.US" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, March 12, 2007 10:48:21 AM Subject: Re: [DB2-L] Update problems DB2 V7 and Z/OS V1.7 I get the same error message on the log regardless of where I try to update from so it is not strictly a CA problem. Thank You Walter Davies DBA El Dorado County "Grainger, Phil" Sent by: DB2 Data Base Discussion [...] 13116 395 40_Re: Update problems DB2 V7 and Z/OS V1.70_26_wdavies@CO.EL-DORADO.CA.US31_Mon, 12 Mar 2007 10:55:36 -0700340_US-ASCII We have RACF installed on the system for security but I do not use it for DB2 security. I use native DB2 security. From what I understand the DSNR class profiles are for using RACF instead of DB2 for table security. They are not turned on in our subsystem currently. Do I need to turn them on even though I am not using them? [...] 13512 221 40_Re: Update problems DB2 V7 and Z/OS V1.714_Bruce Lightsey30_Bruce.Lightsey@ITS.STATE.MS.US31_Mon, 12 Mar 2007 13:21:13 -0500370_US-ASCII This same thing happened to us last September when we upgraded from z/OS 1.4 to 1.7 - only our sub-systems wouldn't stay up at all. This was cured by reading the notes dealing with changes from z/OS 1.6 to 1.7 ( I didn't originally since we we starting at 1.4 ) and getting my RACF guy to perform the appropriate magic. Once RACF was happy, DB2 was happy. [...] 13734 280 40_Re: Update problems DB2 V7 and Z/OS V1.713_Carol Broyles21_clbroyles55@YAHOO.COM31_Mon, 12 Mar 2007 11:22:55 -0700618_iso-8859-1 You need to have them turned on even if you are using DB2 security. This bit us when we upgraded to z/OS 1.6. Carol ----- Original Message ---- From: "wdavies@CO.EL-DORADO.CA.US" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, March 12, 2007 1:55:36 PM Subject: Re: [DB2-L] Update problems DB2 V7 and Z/OS V1.7 We have RACF installed on the system for security but I do not use it for DB2 security. I use native DB2 security. >From what I understand the DSNR class profiles are for using RACF instead of DB2 for table security. They are not turned on in our subsystem currently. [...] 14015 315 40_Re: Update problems DB2 V7 and Z/OS V1.711_Bear, Brian23_Brian.Bear@CHARMING.COM31_Mon, 12 Mar 2007 14:32:18 -0400291_US-ASCII Wow. We are currently running zOS 1.6 DB2 v7 using native DB2 security and will be upgrading to zOS 1.8 DB2 v7 still using native DB2 security.

Am I understanding this thread correctly in that I may need to address DB2 security issues within RACF when we go to zOS 1.8? [...] 14331 357 40_Re: Update problems DB2 V7 and Z/OS V1.713_Carol Broyles21_clbroyles55@YAHOO.COM31_Mon, 12 Mar 2007 11:48:49 -0700620_iso-8859-1 Brian, We were missing some DSNR profiles for some subsystems when we upgraded from z/OS 1.4 to 1.6. DB2 began enforcing these with 1.6, so you are probably okay. Carol ----- Original Message ---- From: "Bear, Brian" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, March 12, 2007 2:32:18 PM Subject: Re: [DB2-L] Update problems DB2 V7 and Z/OS V1.7 Wow. We are currently running zOS 1.6 DB2 v7 using native DB2 security and will be upgrading to zOS 1.8 DB2 v7 still using native DB2 security. Am I understanding this thread correctly in that I may need to address DB2 security issues [...] 14689 158 40_Re: Update problems DB2 V7 and Z/OS V1.711_Bear, Brian23_Brian.Bear@CHARMING.COM31_Mon, 12 Mar 2007 14:53:11 -0400504_US-ASCII Thanks Carol ! I feel much better.



________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Carol Broyles Sent: Monday, March 12, 2007 1:49 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Update problems DB2 V7 and Z/OS V1.7



Brian,

We were missing some DSNR profiles for some subsystems when we upgraded from z/OS 1.4 to 1.6. DB2 began enforcing these with 1.6, so you are probably okay. [...] 14848 115 27_Another "deep end" question19_Mike Kerford-Byrnes17_mkb@HILL-LEYS.COM31_Mon, 12 Mar 2007 19:30:46 -0000363_us-ascii Following on from my previous question involving exits, I am now delving a bit deeper.

Given the following:

A) a DB2 table with say, six columns (A,B,C,D,E,F) and an Edit Procedure B) A View defined on that table specifying 4 of those columns (A,B,E,F)

If a SELECT/INSERT is made via that View, will the Edit procedure kick in? [...] 14964 62 31_Re: Another "deep end" question9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 12 Mar 2007 14:54:50 -0600326_us-ascii The documentation is fairly simple - EDITPROC's work at the row level right after the DBM1 has found the row. A view is the same as a select with limited columns. The insert can only work if there are default values or null's for the ommitted columns. The editproc will see the result with all values filled in. [...] 15027 35 24_DB2 QM and GUI Interface10_Tom Glaser14_tg5444@ATT.COM31_Mon, 12 Mar 2007 13:52:38 -0600419_- Hi,

I'd like to know if anyone is using the CAE (Consolidation and Analysis Engine) interface, which is the GUI interface to IBM's DB2 Query Monitor? If so, may I ask what you are monitoring? For example, we are monitoring the following:

Agent Failures DASD Read Delays Queries consuming high CPU Queries with large getpage activity Large Sync I/O Low BP Hit Ratios Resource Unavailable RLF Errors [...] 15063 154 39_SAP using DB2 and Daylight Savings Time13_Petersen, Jim26_Jim_Petersen@HOMEDEPOT.COM31_Mon, 12 Mar 2007 16:21:05 -0400554_us-ascii This has been an ongoing discussion in the company even before the change in dates for DST. From what I am understanding, we are having problems getting the Unix/AIX Servers to change time automatically and be in synch with the z/OS and DB2 hosts.

I am looking for SAP users who also use DB2. How do you accomplish your time change? We are currently running LOCAL = UTC + Offset. I know on the mainframe side we are going to change the time at the Sysplex Timers. Done it for may years now at different accounts. None that have used [...] 15218 82 43_Re: SAP using DB2 and Daylight Savings Time0_22_MMaziarczyk@SYMCOR.COM31_Mon, 12 Mar 2007 16:25:20 -0400579_UTF-8

For our AIX servers, our unix admins had to patch the servers with what IBM released as a fix for the DST issue.

http://www14.software.ibm.com/webapp/set2/sas/f/genunix3/aixfixes.html

Mark









"Petersen, Jim" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] SAP using DB2 and Daylight Savings Time

03/12/2007 04:21 PM



Please respond to DB2 Database Discussion list at IDUG [...] 15301 28 25_z/OS v7 - CASE and CONCAT16_Natalie Faulkner21_nfaulkner@WVADMIN.GOV31_Mon, 12 Mar 2007 15:09:28 -0600369_ISO-8859-1 I have a developer trying to utilize a CASE, CONCAT, and SUBSTR into a singleton select. He receives a SQLCODE = -104, ERROR: ILLEGAL SYMBOL “||” when he runs the following query SELECT (CASE WHEN COL1 = '999' THEN '3' WHEN COL2 = '250' THEN '7' ELSE '4' END) AS TYPE || (SUBSTR(COL3,2,4)) AS PROJ FROM owner.table1 WHERE COL4 IN ('1234','ABCD','WXYZ') [...] 15330 80 29_Re: z/OS v7 - CASE and CONCAT14_Glen Sanderson26_Glen.Sanderson@SAFEWAY.COM31_Mon, 12 Mar 2007 15:25:40 -0600546_us-ascii It should be written as: SELECT CASE WHEN COL1 = '999' THEN '3' WHEN COL2 = '250' THEN '7' ELSE '4' END || (SUBSTR(COL3,2,4) AS col-name

You cannot put a re-name on a column between functions.

Glen Sanderson Information Technology Database Administration - Support Canada Safeway Ltd./Safeway Inc. -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Natalie Faulkner Sent: March 12, 2007 16:09 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] z/OS v7 - CASE and CONCAT [...] 15411 64 32_Re: Help on a long running query14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 13 Mar 2007 08:28:15 +1100442_US-ASCII How many account rows are there for a party_id/acct_id? If it averages one, then it seems to be taking about 0.075 seconds for each random access to account. I would be taking a hard look at - is account and its index on constrained disk - is the index disorganised - would adding clsd_dte to the clustering index be usefull (What %'age of accounts are closed) - buffer pool conflicts - are parts of the the index being flushed [...] 15476 12 29_Re: z/OS v7 - CASE and CONCAT16_Natalie Faulkner21_nfaulkner@WVADMIN.GOV31_Mon, 12 Mar 2007 15:27:24 -0600552_- Thanks a bunch for the help.....

I should have seen that one coming !!!!

--------------------------------------------------------------------------------- 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 15489 219 40_Re: Update problems DB2 V7 and Z/OS V1.710_Paul Fegan33_Paul.Z.Fegan@TRANSPORT.QLD.GOV.AU31_Tue, 13 Mar 2007 07:42:31 +1000552_us-ascii The DSNR resource class profiles control who can connect to DB2 using the various methods (TSO, BATCH, CICS .....) They have no bearing on what you can do once you are connected so if you use internal DB2 grants then they are unaffected by the RACF Class stuff. As a general rule in all the shops I've worked in the DSNR.ssid.SASS, DSNR.ssid.MASS etc profile are just given a UAC or read in RACF so anyone can connect to DB2. This doesn't give them the ability to do anything in DB2 but they can connect. It's probably easier to create a [...] 15709 64 28_Re: DB2 QM and GUI Interface25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Mon, 12 Mar 2007 15:50:15 -0700376_iso-8859-1 We utilize the Query Monitor DB2 data store as a work area for extracting our most resource intensive SQL, and store it in a separate set of tables. These tables are then externalized through a reporting tool to the end-users. This allows us to track the Top 10 SQL for a given functional ID, and also the CPU utilization of a given SQL over a period of time. [...] 15774 233 31_Re: Another "deep end" question13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US31_Mon, 12 Mar 2007 16:40:14 -0700600_us-ascii Mike,

From the SQL Reference

EDITPROC program-name

Designates program-name as the edit routine for the table. The edit routine, which must be provided by the current server's site, is invoked during the execution of LOAD, INSERT, UPDATE, and all row retrieval operations on the table. An edit routine receives an entire table row, and can transform that row in any way. Also, it receives a transformed row and must change the row back to its original form. For information on writing an EDITPROC exit routine, see Appendix B (Volume 2) of DB2 Administration Guide. [...] 16008 36 15_dead lock issue12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN31_Tue, 13 Mar 2007 15:00:27 +0800385_US-ASCII Dear list,

I encounter a dead lock issue in our shop. According the DB2PM LOCKING TRACE - DEADLOCK report, I can figure out the following situation happened:

HOLDER WAITERS PAGEA TRANA(X) TRANB(U), TRANC(U), TRAND(U) PAGEB TRAND(U) TRANA(S), TRANE(U)

It's the state of the lock in the parentheses. And the victim was TRANC.

The questions are: [...] 16045 158 50_DB2 for z/OS - migration to version 8.1 using ADCD12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Tue, 13 Mar 2007 09:01:53 -0000524_iso-8859-1 -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of sally.mir@WACHOVIA.COM Sent: 12 March 2007 16:22 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] v7.1 z/OS: enhancements/changes in V81 available/forced in CM?



IDUG 2007 NA plug:

Session B04: Stuck in Compatibility Mode? How to Make the Best of It

May 7, 2007 4;20 PM

This presentation contains information on some of the important features that are available in CM. [...] 16204 47 54_Re: DB2 for z/OS - migration to version 8.1 using ADCD12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Tue, 13 Mar 2007 09:09:17 -0000543_iso-8859-1 Sorry about the last post - fat finger problems. Now on to the question.

We have several DB2 subsystems running DB2 version 7.1 and have 8.1 installed via an ADCD install. Let me say at the outset I am not a DB2 DBA or System Admin but an MVS sysprog but I have the job of upgrading to DB2 8.1. First question, where do I start. Is there a redbook that would help in the migration process or is it a case of ingesting the whole of the installation manual. Obviously with the ADCD, the actual install process (smp/e etc) [...] 16252 37 4_-10415_Yadigar Dirican18_ydirican@YAHOO.COM31_Tue, 13 Mar 2007 02:09:49 -0700319_iso-8859-1 Hi,

We are receiving sqlcode -104 at run time after IPL, then if the program is compiled-linked and binded then it is okay. As you accept it is a difficult and long solution for all programs. Is there any opinion wht it happened and what is solution other than that.

Thank you in advance. [...] 16290 145 21_Antwort: [DB2-L] -10411_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 13 Mar 2007 10:35:18 +0100646_iso-8859-1 uhhh which DB2 version on which Hardware with what type of SQL are we talking about here?

Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert









Yadigar Dirican Gesendet von: DB2 Data Base Discussion List 13.03.2007 10:09 Bitte antworten an DB2 Database Discussion list at IDUG [...] 16436 36 25_Re: Antwort: [DB2-L] -10414_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Tue, 13 Mar 2007 10:48:46 +0100577_iso-8859-1 This must IMO be due to an automatic rebind which occurs after IPL, don't know why. And a syntax error in one of the SQL statements in the package subjected to automatic rebind.

Kind regards

Bernd





Am Dienstag, 13. März 2007 10:35 schrieben Sie: > Hi, > > We are receiving sqlcode -104 at run time after IPL, then if the program > is compiled-linked and binded then it is okay. As you accept it is a > difficult and long solution for all programs. > Is there any opinion wht it happened and what is solution other than that. [...] 16473 79 25_Re: Antwort: [DB2-L] -10415_Yadigar Dirican18_ydirican@YAHOO.COM31_Tue, 13 Mar 2007 03:09:51 -0700517_iso-8859-1 DB2 V7, z/os V1.7, zseries. There is no any specific SQL, different SQLs from different programs. It happened immediately at various programs after IPL.

Roy Boxwell wrote:

uhhh which DB2 version on which Hardware with what type of SQL are we talking about here?

Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...] 16553 303 42_Antwort: Re: [DB2-L] Antwort: [DB2-L] -10411_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 13 Mar 2007 11:37:58 +0100484_iso-8859-1 I saw in a previous post (I searched after I sent the last mail) that you have recently changed your CCSID...could it be that you used one of the following characters in you SQL text

The logical not (sideways L) Vertical bar

These two (amongst others) can map to different codepoints and so a recompile, relink,rebind would re-parse the SQL and all would/could/might be OK. Perhaps a few LIKE "%^%" sql against SYSPACK.... and SYSPLAN... are called for? [...] 16857 125 46_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] -10415_Yadigar Dirican18_ydirican@YAHOO.COM31_Tue, 13 Mar 2007 04:38:37 -0700339_iso-8859-1 Thank you for your reply, they might be. But I could not understand why it happened after IPL.

Roy Boxwell wrote: I saw in a previous post (I searched after I sent the last mail) that you have recently changed your CCSID...could it be that you used one of the following characters in you SQL text [...] 16983 110 5_64BIT16_Bahman Saeedinia21_saeedinia@HOTMAIL.COM31_Tue, 13 Mar 2007 11:44:52 +0000344_iso-8859-1 Hi.

Recently we moved to z800, z/OS 1.6 , and DB2 ver 8.1. I wanted to make a DB2 SQL stored procedure using DSNHSQL and I wanted to use 64 BIT addressing. For this purpose I used the LP64 and XPLINK options for C language and also the AMODE=64 and RMODE=ANY options for linkage step. But received the following message: [...] 17094 46 46_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] -10414_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Tue, 13 Mar 2007 06:58:36 -0500383_utf-8 Are you sure the IPL is the true catalyst?

Check assembly of zparms Check concatinations of SDSNLOAD/EXIT are they the right ones? Was new maint put on? Does the DBD look healthy?

Have you looked at the code? Specifically surrounding the SQLCODE tests of other SQL...perhaps the SQLCODE104 is the result of an illogical condition of another section of code. [...] 17141 73 54_Re: DB2 for z/OS - migration to version 8.1 using ADCD14_Wayne Driscoll25_wdriscoll@JMESOFTWARE.COM31_Tue, 13 Mar 2007 08:26:51 -0400337_us-ascii Jim, If you look at the DB2 Installation Guide, it has a chapter on the migration process. To perfrorm the migration you will need the V8 libraries allocated to your ISPF session, and use the DSNTINST clist, with the MIGRATE option. Wayne Driscoll Product Developer JME Software LLC NOTE: All opinions are strictly my own. [...] 17215 114 54_Re: DB2 for z/OS - migration to version 8.1 using ADCD12_Jim McAlpine22_Jim.McAlpine@CEDAR.COM31_Tue, 13 Mar 2007 12:49:18 -0000674_iso-8859-1 Thanks Wayne, I'll have a look at that chapter.

Jim McAlpine

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Wayne Driscoll Sent: 13 March 2007 12:27 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS - migration to version 8.1 using ADCD



Jim, If you look at the DB2 Installation Guide, it has a chapter on the migration process. To perfrorm the migration you will need the V8 libraries allocated to your ISPF session, and use the DSNTINST clist, with the MIGRATE option. Wayne Driscoll Product Developer JME Software LLC NOTE: All opinions are strictly my own. [...] 17330 48 46_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] -10415_Yadigar Dirican18_ydirican@YAHOO.COM31_Tue, 13 Mar 2007 05:58:40 -0700398_iso-8859-1 Yes, DBD looks healthy. Only sdsnexit was wrong order in the lnklst, but Procedures aboout DB2 have steplib and that is right EXIT library.

"Fazio, Richard" wrote: Are you sure the IPL is the true catalyst?

Check assembly of zparms Check concatinations of SDSNLOAD/EXIT are they the right ones? Was new maint put on? Does the DBD look healthy? [...] 17379 66 19_Re: dead lock issue12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM31_Tue, 13 Mar 2007 08:18:25 -0500265_us-ascii Are you sure the failure of TRANC was a DEADLOCK, or just a lock TIMEOUT ? Are you sure TRANC was not a Holder of any lock, only a Waiter ? AFAIK, there have to be two lock Holders who are also Waiters on each other's lock for there to be a DEADLOCK. [...] 17446 334 46_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] -10414_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Tue, 13 Mar 2007 08:23:15 -0500454_US-ASCII What was the failing statement/sqlca output?





Was the plan/package invalid when the SQLCODE104 occurred?





Are their many programs having this problem? Your initial email suggests this is more pervasive than just one program.





Is there any consistency on the style of sql failing (select, lock, etc)?





What about the other questions? Maint, Zparms, etc [...] 17781 12 32_Re: Help on a long running query33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 13 Mar 2007 07:58:52 -0600636_- Which indexes are available? Do you have all the necessary statistics available, especially COLDIST- values for the compound columns used in your where-predicates?

--------------------------------------------------------------------------------- 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 17794 27 41_DB2 V8 Z/OS - explain stmtcache stmttoken13_Dawn Kastelic26_dawn.kastelic@HIGHMARK.COM31_Tue, 13 Mar 2007 08:18:15 -0600614_- We have been on V8 for about 1 year now and are starting to put together procedures to utilize the explain feature for the dynamic sql cache. Has anybody had any experience using EXPLAIN STMTCACHE STMTTOKEN? From what I understand, the STMTTOKEN can be set by the application program using the SET_ID function. For example: CALL 'DSNRLI' USING SETIDFN PROGID RETCODE REASCODE Does this function set the STMTTOKEN with the value of PROGID from this call? Also, the documentation states that the PROGNAME on the DSN_STATEMENT_CACHE_TABLE will contain the value of DSNDCACHE if it is for a cached statement. I [...] 17822 17 28_Re: DB2 QM and GUI Interface33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 13 Mar 2007 08:38:19 -0600450_- Tom

We are using QM, but neither CAE nor (at least mostly) the ISPF interface. At our site QM run 2 hours a day in production. We are using the offload feature. There are daily jobs, which accumulate these data on day, on plan and and on package level spanning a time frame of 30 days. We use this data, to see, how a special tuning activity has changed the CPU-time, or which are the most executed or costly statements and and and ... [...] 17840 124 18_IDUG/IBM tech conf12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Tue, 13 Mar 2007 10:01:02 -0700467_us-ascii I was hoping to get some assistance from the list. I have attended many IDUGs in the past, but not the IBM tech conference. This year I find that I have a choice over which one to attend. However, I'm not sure if I should go to IDUG or try the IBM tech conf. Are any of you able to assist with giving me some information regarding my dilemma? I would prefer you respond to me directly on your opinion as to which conference you would recommend and why. [...] 17965 148 103_Fwd: FW: Final meeting announcement - NEDB2UG meeting on TUESDAY, MARCH 27, 2007, Sturbridge Host Hotel7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 13 Mar 2007 10:46:36 -0700327_iso-8859-1



Please come to Boston, or at least Sturbridge, for the spring time New England Db2 User's Group session featuring Jeff Josten, Ernie Mancill, and more. Sell your paintings on the side walk; (Name that tune and win a hearty handshake or at least a virtual trip to Denver or L.A). Be there Aloha. [...] 18114 148 54_Re: DB2 for z/OS - migration to version 8.1 using ADCD7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 13 Mar 2007 11:05:49 -0700395_iso-8859-1 There is another, more subversive, alternative.

Don't convert your V7 system at all.

Export a copy of the databases and data that you need and import it onto the V8 system.

ADCD comes with the two dbms's designed to run in parallel; why not exploit it?

ADCD comes with the IBM DB adm tool that does a pretty good job of dumping and restoring DDL etc. [...] 18263 63 22_Re: IDUG/IBM tech conf14_Wayne Driscoll25_wdriscoll@JMESOFTWARE.COM31_Tue, 13 Mar 2007 14:08:25 -0400574_us-ascii Shery, It has been a few years since I have been to either, but my experience has been that IDUG has a larger number of user experience type presentations than the Tech Conference, while the Tech Conf usually had a larger number of presentations by IBM'ers, including a lot of presentations given by people from SVL. This isn't to say that IDUG doesn't have any IBM presentations, just that it often had more user type presentations than the tech conf. My personal preference was for the Tech Conf, but I see the value of both. Hope this helps, Wayne Driscoll [...] 18327 359 22_Re: IDUG/IBM tech conf11_Tom Moulder21_tom_moulder@1SCOM.NET31_Tue, 13 Mar 2007 13:23:01 -0500596_windows-1250 Shery





IDUG will be held this year in San Jose. There is an additional track of IMS presentations that might also be of benefit. However, just having this event in San Jose will give much greater access to IBM development personnel than has ever been possible.





There will still be a very large number of user presentations for those that want to hear an alternative view of the DB2 experience. But there will also be a bigger presence than in years past for IBM to discuss both V8 and V9. I believe that there will be a need to hear [...] 18687 256 55_Re: Rebind of a package with lower case collection name19_VIDYASAGAR ATTULURI19_vattuluri@GMAIL.COM31_Tue, 13 Mar 2007 15:02:19 -0400364_ISO-8859-1 John,

If i try with quotes it gives me the error message as

DSNE931E DSNECP55 PACKAGE ("QAT01_abcde.OMOFFED") CONTAINS AN INVALID SYMBOL (")



Once again, has anybody on this list ever bound a package with lower case name on the host, meaning binding it on the host not from a remote location.

Please let me know. [...] 18944 211 55_Re: Rebind of a package with lower case collection name12_Stone, Lynda29_Lynda.Stone@LIBERTYMUTUAL.COM31_Tue, 13 Mar 2007 15:26:32 -0400474_us-ascii Vidya,

Be sure to have the "caps off" on the profile of your JCL, so the text isn't converted to all caps - here's an example with a mixed case version -

DSN SYSTEM(DB2T) DSN REBIND PACKAGE (SSDEVDVK.VKPROPLA.(CA_2.0.cplusplus.BIND.V25)) DSNT254I - DSNTBRB2 REBIND OPTIONS FOR PACKAGE = DB2T.SSDEVDVK.VKPROPLA.(CA_2.0.cplusplus.BIND.V25) ..... DSNT232I - SUCCESSFUL REBIND FOR PACKAGE = DB2T.SSDEVDVK.VKPROPLA.(CA_2.0.cplusplus.BIND.V25) DSN END [...] 19156 158 55_Re: Rebind of a package with lower case collection name19_VIDYASAGAR ATTULURI19_vattuluri@GMAIL.COM31_Tue, 13 Mar 2007 15:33:45 -0400567_ISO-8859-1 Lynda,

Actually my profile says CAPS OFF only, i am pasting the screen shot of PROF command on my jcl.

....ISFEDIT (VARIABLE - 255)....RECOVERY OFF WARN....NUMBER OFF......... ....CAPS OFF....HEX OFF....NULLS ON STD....TABS OFF..................... ....AUTOSAVE ON....AUTONUM OFF....AUTOLIST OFF....STATS OFF............. ....PROFILE UNLOCK....IMACRO NONE....PACK OFF....NOTE ON................ ....HILITE JCL CURSOR FIND..............................................

Still, it is trying to find the package with upper case name. [...] 19315 42 50_LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Tue, 13 Mar 2007 14:15:54 -0600466_iso-8859-1 Hello List, An application seems to be changing it's commit behavior ( should be AUTO_COMMIT_ON and get's changed to AUTO_COMMIT_OFF ), in the middle of a large transaction.

Debugging this problem is a challenge, and I was wondering if there was a way to see the commit status of a connection using something like the "GET SNAPSHOT FOR APPLICATION" command? I've read the help on that command, and it doesn't seem to provide this information. [...] 19358 240 54_Re: LUW UDB How to get commit setting of a connection?0_18_renusharma@AOL.COM31_Tue, 13 Mar 2007 16:24:36 -0400526_us-ascii Are you reconnecting in between or giving commit. that might be effecting the autocommit behaviour



Thanks

Renu

-----Original Message----- From: ewodarz@LOGISYS911.COM To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tue, 13 Mar 2007 4:15 PM Subject: [DB2-L] LUW UDB How to get commit setting of a connection?



Hello List, An application seems to be changing it's commit behavior ( should be AUTO_COMMIT_ON and get's changed to AUTO_COMMIT_OFF ), in the middle of a large transaction. [...] 19599 139 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Tue, 13 Mar 2007 14:28:39 -0600408_iso-8859-1 Single connection, at least 2 commits, the second sequence of statements is not a single UOW as it should be.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of renusharma@AOL.COM Sent: Tuesday, March 13, 2007 13:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LUW UDB How to get commit setting of a connection? [...] 19739 138 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Tue, 13 Mar 2007 14:31:44 -0600411_iso-8859-1 Also, this only happens if db2cli.ini AUTOCOMMIT=1 . If it's not present, or set to 0, the behavior is as expected.

Erick

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of renusharma@AOL.COM Sent: Tuesday, March 13, 2007 13:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LUW UDB How to get commit setting of a connection? [...] 19878 221 22_Re: IDUG/IBM tech conf10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Tue, 13 Mar 2007 16:46:04 -0400571_iso-8859-1 Hi Shery,

OK, I will state up front that I am giving a somewhat biased opinion because I am on the IDUG webteam, but my experience is that both had similar technical presentations. IDUG has more user experience presentations and a somewhat more balanced view of 3rd party software. The Tech Conference has evolved into the "IBM Information On Demand" conference, and taken on much more than DB2 as a focus, so I can't say what affect that has had. From a DBA attendee's perspective, did the change in focus dilute the overall affect or broaden it? [...] 20100 47 20_Where should I look?13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 13 Mar 2007 17:58:54 -0300363_US-ASCII Hi list,

anybody knows in which MVS manual should I look for this error code?

It's not in System Codes.

DSNU002I DSNUTILB - COULD NOT LOAD RELEASE DEPENDENT MODULE DSNUT710 CODE=X'00000106' REASON=X'0000000C'

Cheers,

Horacio Villa

--------------------------------------------------------------------------------- 20148 85 24_Re: Where should I look?7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 13 Mar 2007 14:06:11 -0700375_iso-8859-1 z/OS Messages and Codes. The fastest route is the LOOKAT website IBM has made available.

I think you will find that the underlying error is a 106-c which is a load failure ostentibly due to insufficient Virtual Storage. I have seen 106's caused by compresses of sdsnload, or load libraries taking extents. Retry the job with a much larger region size. [...] 20234 39 24_Re: Where should I look?11_Denis - DB219_denis.db2@GMAIL.COM31_Tue, 13 Mar 2007 18:06:54 -0300890_ISO-8859-1 You may find your answer here..

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.mc/msgs/dsnu002i.html http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.dshare/vcoexis.htm

Horacio Villa escreveu: > > Hi list, > > anybody knows in which MVS manual should I look for this error code? > > It's not in System Codes. > > DSNU002I DSNUTILB - COULD NOT LOAD RELEASE DEPENDENT MODULE DSNUT710 > CODE=X'00000106' REASON=X'0000000C' > > Cheers, > > Horacio Villa > --------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and > home page at http://www.idugdb2-l.org/archives/db2-l.html. From that > page select "Join or Leave the list". The IDUG DB2-L FAQ is at > http://www.idugdb2-l.org. The IDUG [...] 20274 147 24_Re: Where should I look?12_McKown, John29_John.Mckown@HEALTHMARKETS.COM31_Tue, 13 Mar 2007 16:07:25 -0500442_us-ascii That is an S106-0C abend, which means: "Not enough storage was available for FETCH to get storage for the module or control blocks." In other words, your region value is too small.

There really isn't an equivalent MVS message, per se. An "normal" batch job would have likely issued the message IEF450I. Unless it was running LE, upon which it would likely have gotten an U4093 type abend. I hate "helpful" things such as that. 20422 95 24_Re: Where should I look?13_FRITZ LEBLANC27_FRITZ.LEBLANC@SALLIEMAE.COM31_Tue, 13 Mar 2007 17:05:42 -0400440_US-ASCII I googled the message and it showed up in the DB2V8 Messages manual.

Fritz Leblanc Manager, Mainframe Database Sallie Mae, Inc 11100 USA Parkway Fishers, IN 46037 Tel: 317-806-0375 Cell: 317-607-2657 Fax: 317-595-1494 email: fritz.leblanc@salliemae.com

>>> Horacio Villa 03/13/07 4:58 PM >>>



Hi list,

anybody knows in which MVS manual should I look for this error code? [...] 20518 215 24_Re: Where should I look?13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 13 Mar 2007 18:32:58 -0300534_US-ASCII Hi John,

thanks for your answer. As you say, it's a problem with not enough storage. The failing step was a HPUnload execution, but it had REGION=0M while the JOB card was with REGION=4M. We changed the 0M to the JOB card & it worked.

Thanks again to all,

Horacio Villa





"McKown, John" Sent by: DB2 Data Base Discussion List 13/03/2007 18:07 Please respond to DB2 Database Discussion list at IDUG [...] 20734 203 54_Re: LUW UDB How to get commit setting of a connection?11_John Miller26_John.L.Miller@ATCOITEK.COM31_Tue, 13 Mar 2007 15:54:28 -0600385_us-ascii Erick,

You could try turning on a CLI trace. Then look for (I Believe) the command SQLSetConnectAttr to set SQL_ATTR_AUTOCOMMIT. You will see it either set it to null (which is equivalent to 0) or 1.

You can turn on CLI trace by opening db2cli.ini: Under the appropriate database heading (or under [COMMON]) Add Trace=1 TraceFlush=1 TracePathName=C:\trace\ [...] 20938 220 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Tue, 13 Mar 2007 15:56:33 -0600535_iso-8859-1 I'll give that a try.

Thanks!!

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of John Miller Sent: Tuesday, March 13, 2007 14:54 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LUW UDB How to get commit setting of a connection?



Erick,

You could try turning on a CLI trace. Then look for (I Believe) the command SQLSetConnectAttr to set SQL_ATTR_AUTOCOMMIT. You will see it either set it to null (which is equivalent to 0) or 1. [...] 21159 57 22_Re: IDUG/IBM tech conf12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 13 Mar 2007 15:40:11 -0700384_iso-8859-1 Shery, It really depends upon what you want. I've found the IBM Tech Conference to be more technical in nature and stronger technically than IDUG. As others have mentioned, IDUG has more user presentations so you can get more user experiences there. Much weaker though in quality and technical strength but broader in general experience and better for basic learning. [...] 21217 60 22_Re: IDUG/IBM tech conf11_Tom Moulder21_tom_moulder@1SCOM.NET31_Tue, 13 Mar 2007 18:07:11 -0500420_windows-1250 Quote ---> My personal preference is the Tech Conference because i want more technical information at a deeper level than is usually found at IDUG. I've found the Tech conference much more useful than IDUG generally in the past. And I don't think adding IMS presentations to IDUG helps it at all. If I wanted IMS information, I'd go to the IMS Tech Conference, not the International "DB2" Users group. [...] 21278 67 22_Re: IDUG/IBM tech conf12_Jim Campbell32_james.campbell@CREDIT-SUISSE.COM31_Tue, 13 Mar 2007 17:14:31 -0600581_- As a member of the IDUG Board of Directors, my opinion may seem biased. That being said, I will agree with Wayne Driscoll's initial assessment that there are more IBM presentations at the Tech Conference. It is that way for two reasons. 1st) Tech/IOD is a for profit IBM conference, run by IBM. 2nd) IBM does not offer the same level of variety of User, non IBM Vendor and Consultant Presentations. You will see Select Partner, IBM Gold Consultant and Featured User Presentations. IDUG is a not for Profit User Run organization. This mix of IBM, Vendor, User and Consultant [...] 21346 44 9_Re: 64BIT14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 14 Mar 2007 10:54:57 +1100439_US-ASCII Well, off the top of my head (after reading the explanation of IEW2469E and its reason code 3), I would guess that, while *you* made your C code compile into AMODE64, *IBM* did not assemble (or whatever they used) DSNRLI into AMODE64. So the Binder decided that it would not be nice to pass control from your code - running in 64 bit mode - to another module which would think that the PSW and registers were in 31 bit mode. [...] 21391 85 9_Re: 64BIT9_Tony Saul30_generalemaillists@YAHOO.COM.AU31_Wed, 14 Mar 2007 11:16:38 +1100557_iso-8859-1 And DB2 V9 still has DSNRLI as 31/ANY and I can't see any module as AMODE 64 (to see if there was an alternative).



--- James Campbell wrote:

> Well, off the top of my head (after reading the > explanation of IEW2469E and its reason code > 3), I would guess that, while *you* made your C code > compile into AMODE64, *IBM* did not > assemble (or whatever they used) DSNRLI into > AMODE64. So the Binder decided that it > would not be nice to pass control from your code - > running in 64 bit [...] 21477 186 55_Re: Rebind of a package with lower case collection name19_VIDYASAGAR ATTULURI19_vattuluri@GMAIL.COM31_Tue, 13 Mar 2007 21:55:28 -0400751_ISO-8859-1 Lynda,

One more thing i observed, in your example the lower case is in version-id. which it is taking, but the lower case in collection or package name is not taking, that is what my problem is.



Regards Vidya



On 3/13/07, VIDYASAGAR ATTULURI wrote: > > Lynda, > > Actually my profile says CAPS OFF only, i am pasting the screen shot of > PROF command on my jcl. > > ....ISFEDIT (VARIABLE - 255)....RECOVERY OFF WARN....NUMBER OFF......... > ....CAPS OFF....HEX OFF....NULLS ON STD....TABS OFF..................... > ....AUTOSAVE ON....AUTONUM OFF....AUTOLIST OFF....STATS OFF............. > ....PROFILE UNLOCK....IMACRO NONE....PACK OFF....NOTE ON................ > ....HILITE [...] 21664 242 55_Re: Rebind of a package with lower case collection name14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 14 Mar 2007 14:42:21 +1100389_US-ASCII A carefull read of the DB2 Command Reference:

" collection-id: An SQL identifier of 1 to 128 letters, digits, or the underscore that identifies ... ... version-id: An SQL identifier of 1 to 64 lowercase alphabetic letters, uppercase alphabetic letters, digits, underscores, at signs (@), number signs (#), dollar signs ($), dashes, and periods that is assigned .... " [...] 21907 95 54_=?GB2312?Q?=B4=F0=B8=B4:?= Re: [DB2-L] dead lock issue12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN31_Wed, 14 Mar 2007 14:17:18 +0800539_GB2312 Hi Kirk,

Thank you for your response. I am sure TRANC is DEADLOCK since it's specified in the PM LOCKING TRACE report. And I can't find TRANC was a holder after searching the whole PM report. So these are the reasons that confused me.









Kirk Hampton ·¢¼þÈË£º DB2 Data Base Discussion ÊÕ¼þÈË£º List DB2-L@WWW.IDUGDB2-L.ORG Ö÷Ì⣺ Re: [DB2-L] dead lock issue 2007-03-13 21:18 Çë´ð¸´ ¸ø DB2 Database Discussion list at IDUG [...] 22003 16 0_13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 14 Mar 2007 06:53:30 -0400334_us-ascii The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 22020 103 55_Re: [DB2 NFM z/OS] My special friend: the DB2 optimizer33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 14 Mar 2007 06:39:08 -0600532_- Hi Terry

Thank you for your response, but I feel your answer fairly disappointing. When I tune a query, in most cases, I looked at the service units before and after and if I saw a decrease, I was content. Well, I say, most of the time, because I don't always trust these numbers. But now, you said, that's not correct at all. These numbers are only for the governor, but I ask myself, what does the governor do with these numbers, If I tune a query and the service units are less than before? I think, if the service [...] 22124 28 26_DSNTIJNE (how to restart?)2_JJ16_tzs61x@YAHOO.COM31_Wed, 14 Mar 2007 06:56:17 -0700468_iso-8859-1 During the CM to ENFM, how to restart DSNTIJNE if it should abend for any reason, Do we simply restart the job from top ? or do we need to CATENFM COMPLETE before we restart it?

Thanks JJ







____________________________________________________________________________________ We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list. http://tv.yahoo.com/collections/265 [...] 22153 84 30_Re: DSNTIJNE (how to restart?)0_17_sjvagnier@AEP.COM31_Wed, 14 Mar 2007 10:01:18 -0400529_US-ASCII Hello JJ,

just restart job DSNTIJNE from the top.

Regards,





**************************************** Steve Vagnier American Electric Power One Riverside Plaza - 7th Floor Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677







JJ To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] DSNTIJNE (how to restart?)



03/14/2007 09:56 AM [...] 22238 63 30_Re: DSNTIJNE (how to restart?)11_Gene Renico33_generenico@NORTHWESTERNMUTUAL.COM31_Wed, 14 Mar 2007 09:02:25 -0500395_us-ascii I have had it abend abend on me a few times. Just resubmit DSNTIJNE - it will do the cleanup and start where it left off.

Gene Renico 414-661-8580

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of JJ Sent: Wednesday, March 14, 2007 8:56 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DSNTIJNE (how to restart?) [...] 22302 49 34_-TERM UTIL(utilname) authorization12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 14 Mar 2007 15:13:09 +0100687_us-ascii Environment: DB2 for z/OS V7

Collegues,

How can I find out all userids which are granted to use the -TERM UTIL command ?

Thanks in advance for any advice you may be able to provide.

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) 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 ---------------------------------------------------------------------- [...] 22352 66 22_DB2 for ZOS V8 CM mode16_Wallace, Stephen25_stephen.wallace@LANDG.COM31_Wed, 14 Mar 2007 14:13:56 -0000362_us-ascii Listers, We have some poorly performing SQL that we are trying to explain. Nothing is being written to the plan table even though EXPLAIN(YES) is specified and the bind returns a zero. The statements concerned all contain a reference to a DECLARE GLOBAL TEMPORARY TABLE. Queries without DECLARE GLOBAL TEMPORARY TABLE references explain as normal. [...] 22419 100 22_Re: IDUG/IBM tech conf7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 14 Mar 2007 08:17:02 -0700312_iso-8859-1 My .02 is that IDUG offers very good value, particularly this year due to Location, location, location. I went for many years in a row, but stopped due to a sense of deja vu. It seemed like many of the presentations had been done before, or in honor of David Crosby, we had all been here before. [...] 22520 317 66_DB2 Performance Class March 27-Early Reg Discount ends this Friday11_Dave Beulke17_DaveBeulke@CS.COM29_Wed, 14 Mar 2007 11:25:37 EDT451_US-ASCII Dear DB2-L Members,

Sponsored By The Central PA DB2 Users Group the class "How to do a DB2 zOS Performance Review" Taught By Dave Beulke, Pragmatic Solutions, Inc

Registration: $399 until March 16th. After March 16th, $499. Register through the Event Schedule at the website: www.datasharing.com or through the links below

Directions: Available via the Central PA DB2 User Group Website at: http://www.db2parug.org. [...] 22838 82 38_Re: -TERM UTIL(utilname) authorization14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Wed, 14 Mar 2007 17:33:48 +0200459_us-ascii In the system table sysibm.sysdbauth are the authorities for data base . Check the Grantees where in the column DBMAINTAUTH has value yes . All these Grantees has the authority to terminate a utility

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: Wednesday, March 14, 2007 4:13 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] -TERM UTIL(utilname) authorization [...] 22921 236 54_Re: LUW UDB How to get commit setting of a connection?11_Scott Sloss19_scott_sloss@ADP.COM31_Wed, 14 Mar 2007 11:34:54 -0400442_utf-8 Hi,

Your AutoCommit is being overridden by the CLI setting of AUTOCOMMIT=1 (NO Autocommit). Any connection to the database section this is set for (or all connections if it's the common section) will be set to autocommit off. If you need to turn autocommit off for only certain jobs, etc. it's best to turn them off inside the app using set SQL_ATTR_AUTOCOMMIT as John stated below. Then autocommit is off only in that UOW. [...] 23158 23 38_Re: -TERM UTIL(utilname) authorization33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 14 Mar 2007 09:36:44 -0600370_- Peter

Either I can say, the answer is easy or, I think, as the question is intended, it's hard even not impossible.

I can answer: Everybody, who can run a utility can use this command, because everybody can terminate his utility.

But if you mean, who can use TERM UTIL for a utility, he didn't submit, than the answer depends on many things: [...] 23182 13 26_Re: DB2 for ZOS V8 CM mode33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 14 Mar 2007 09:42:04 -0600626_- Steve

I think, this is what is expected, because the use of a DTT makes the statement dynamic, so the access path is determined at execution time.

--------------------------------------------------------------------------------- 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 23196 51 53_[DB design, all platforms] Denormalization Categories15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Wed, 14 Mar 2007 13:15:11 -0400483_ISO-8859-1 I'm just having a flashback to way back in the early '90s.

I attended a presentation at the New England DB2 Users' Group where a Candle representative made a presentation which broke down the possible denormalizations of a data model into about seven categories and gave justifications and disadvantages for each. Has anyone seen this presentation, or something similar, floating around? (Is the original presenter on this list? She was a Candle database engineer.) 23248 287 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Wed, 14 Mar 2007 11:58:29 -0600375_utf-8 Hi Scott,

I belive this is an IBM bug that only happens if AUTOCOMMIT is set to 1 in the db2cli.ini.

The code set's SQL_ATTR_AUTOCOMMIT after the connection is allocated, and *before* the connection is made. (If it's made after SQLConnect there is no problem).

According to the following DB2 UDB 8 documentation, this should work properly ... [...] 23536 345 54_Re: LUW UDB How to get commit setting of a connection?11_John Miller26_John.L.Miller@ATCOITEK.COM31_Wed, 14 Mar 2007 12:24:44 -0600388_us-ascii Erick,

Are you using connection pooling? I had a similar problem with the Accounting string and Client userid connection attribute which would set themselves fine on the first connect but any subsequent connect would flush those attributes settings. We didn't open a PMR for the same reason as you stated below "Calling SQLSetConnectAttr after SQLConnect works fine" [...] 23882 135 26_Re: DB2 for ZOS V8 CM mode11_John Miller26_John.L.Miller@ATCOITEK.COM31_Wed, 14 Mar 2007 12:41:56 -0600551_us-ascii Stephen,

We found the same problem. We opened a PMR and were told that this is as designed. The statement is recorded in sysstmt and syspackstmt (which implies to me it is in the DBRM) but it is not bound. It has STATUS M -> "Parsed - statement references a table that is qualified with SESSION and was not bound because the table reference could be for a declared temporary table". If you need to see an explain from this then in the same unit of work you need to declare the temporary table and issue a manual EXPLAIN statement [...] 24018 198 26_Re: DB2 for ZOS V8 CM mode19_Suresh Chimalakonda29_suresh.chimalakonda@GMAIL.COM31_Wed, 14 Mar 2007 14:51:17 -0400554_ISO-8859-1 Allow me ask you guys a small question ( at the expense of sounding like an idiot ), how does an explain help on a "create gtt" statement ? Does the explain result help you in anyway ?

Thanks Suresh





On 3/14/07, John Miller wrote: > > Stephen, > > We found the same problem. We opened a PMR and were told that this is > as designed. The statement is recorded in sysstmt and syspackstmt > (which implies to me it is in the DBRM) but it is not bound. It has > STATUS M -> "Parsed - [...] 24217 364 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Wed, 14 Mar 2007 13:02:16 -0600452_iso-8859-1 No, It must happen independent of that. I'm a little curious how this behaves in V7 and earlier. I'd hate to think I missed this in testing before.

Thanks

Erick

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of John Miller Sent: Wednesday, March 14, 2007 12:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] LUW UDB How to get commit setting of a connection? [...] 24582 192 26_Re: DB2 for ZOS V8 CM mode12_Felton, John25_Felton.John@PRINCIPAL.COM31_Wed, 14 Mar 2007 14:08:50 -0500328_us-ascii Stephen,

If the SQL is more complex, you should also consider populating your declared temp table prior to the Explain in order to get more accurate statistics. Also remember that you can create indexes on declared temp tables. This is often the solution to poorly performing SQL with declared temp tables. [...] 24775 372 26_Re: DB2 for ZOS V8 CM mode11_John Miller26_John.L.Miller@ATCOITEK.COM31_Wed, 14 Mar 2007 13:30:06 -0600661_us-ascii







________________________________

From: Miller, John (ATCO CIS) Sent: Wednesday, March 14, 2007 1:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: RE: [DB2-L] DB2 for ZOS V8 CM mode





The explain is on the SELECT statement. Which may join to other non temporary tables. My example below simplified the select statement.





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Suresh Chimalakonda Sent: Wednesday, March 14, 2007 12:51 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for ZOS V8 CM mode [...] 25148 301 26_Re: DB2 for ZOS V8 CM mode19_Suresh Chimalakonda29_suresh.chimalakonda@GMAIL.COM31_Wed, 14 Mar 2007 16:02:42 -0400324_ISO-8859-1 John

Apologies, my bad.

My experience has been that, if you run an explain on a query which has a combination of reglar table and GTTs you do get an explain output. At least that is what I used to see for the "created global temporary" tables. I am not sure about " declared temporary tables". [...] 25450 282 54_Re: LUW UDB How to get commit setting of a connection?12_Erick Wodarz22_ewodarz@LOGISYS911.COM31_Wed, 14 Mar 2007 14:23:06 -0600599_utf-8 Hi Scott, Do you have a reference db2 documentationg regarding 'autocommit is off only in that UOW'? I've tested this in DB2 7 GA, and it works according to the following link



http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm. db2.udb.doc/ad/r0000646.htm

Connection Attributes

At any time between allocating and freeing a connection, an application can call SQLSetConnectAttr(). All connection and statement attributes successfully set by the application for the connection persist until SQLFreeHandle() is called on the connection. [...] 25733 282 26_Re: DB2 for ZOS V8 CM mode11_John Miller26_John.L.Miller@ATCOITEK.COM31_Wed, 14 Mar 2007 14:33:37 -0600497_us-ascii That is correct. You will see an explain for a created temporary table because it exists when the package is bound. A declared temporary table exists only for the duration of the transaction it was declared in. Therefore it does not exist at bind time. You might be able to get creative and declare the temp table before binding your package (i.e. in the same unit of work) but I'm not that creative :-). If anyone wants to try that out I'd certainly be interested in the results... [...] 26016 45 57_Re: [DB design, all platforms] Denormalization Categories14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 15 Mar 2007 08:14:20 +1100676_US-ASCII http://www.datamodel.org/NormalizationRules.html might cover this.

James Campbell





On 14 Mar 2007 at 13:15, Philip Sevetson wrote:

> > I'm just having a flashback to way back in the early'90s. > > I attended a presentation at the New England DB2 Users' Group where a Candle representative > made a presentation which broke down the possible denormalizations of a data model into about > seven categories and gave justifications and disadvantages for each. Has anyone seen this > presentation, or something similar, floating around? (Is the original presenter on this list? She > was a Candle database engineer.) > -- > --Phil [...] 26062 218 22_Re: IDUG/IBM tech conf12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 14 Mar 2007 14:44:00 -0700482_us-ascii



I wanted to send out a thank you to all of those that replied to my question. I really appreciate the honesty of some of the responses.









Regards, Shery Hepp





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hepp Shery C Sent: Tuesday, March 13, 2007 12:01 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] IDUG/IBM tech conf [...] 26281 152 55_Re: [DB2 NFM z/OS] My special friend: the DB2 optimizer13_Terry Purcell18_tpurcel@US.IBM.COM31_Wed, 14 Mar 2007 21:26:55 -0600559_- Hello Walter,

One reason for your disappointment (in my opinion) is that you are still focusing on CPU cost only.

When we talk about "lowest cost" from optimizer's perspective, we are referring to the lowest elapsed time, which is made up of CPU and I/O time. You can do a huge amount of CPU processing in the same amount of time it can take to perform 1 physical I/O. Optimizer tries to predict when a page will be in the BP, or when the I/Os will be sequential, as either can be much lower elapsed than performing physical random I/Os. [...]