1 IDUGDB2-L.ORG /home/listserv/home/db2-l March 2010, week 3 2 110 37_Re: Values used in triggers DB29 z/OS10_Dave Churn19_davechurn@GMAIL.COM31_Sun, 14 Mar 2010 22:28:39 -0500641_ISO-8859-1 On 3/12/10, Mark.Doyle@wpsic.com wrote:
> I'm assuming you are inserting into the audit table from the base table.
> The basic template of the trigger will look something like this:
>
> CREATE TRIGGER ZPLQ020D AFTER DELETE ON XXXX.PLQ_XXXX_XXX_XXX
> REFERENCING OLD AS OROW FOR EACH ROW
> MODE DB2SQL INSERT INTO XXXXA.XXX_XXXX_XXX_XXX VALUES (
> OROW.U_ADDL_INT_TYP, NULL
> ,CURRENT SQLID, CURRENT TIMESTAMP );
>
> Notice the CURRENT SQLID on the last line. You don't need to SET the
> value, you just need to use it in the insert part of the trigger.
>
> I had [...]58_a31ad4711003142028s7ae3159dt24826f83c66058f@mail.gmail.com 113 63 14_Re: -805 erorr14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 15 Mar 2010 21:42:04 +1100368_US-ASCII What plan name?

Many CICS systems are setup to use the name of the first program that
invokes DB2 as the plan-name. This seems be the situation here.

When you use the syncpoint or dummy sql, the plan-name is "main"; when
you don't the plan name is "sub" - and plan "sub" doesn't include a package
for program "main" in its pklist. [...]49_4B9EA9AC.22721.4EA91CC4@jacampbell.acslink.net.au 177 75 28_Re: Stored procedure STEPLIB14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 15 Mar 2010 22:10:37 +1100723_ISO-8859-1 like "DB2 code that is executed in the WLM-SPAS must match the DB2
release of the DBM1 address space, or an error will result."
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.i
bm.db29.doc.dshare/db2z_wlmmanagedstoredprocs.htm

Very hard to avoid if you are getting the code from LINKLIST - which can
have only one release.

James Campbell

On 10 Mar 2010 at 12:29, Sevetson, Phil wrote:

>
> Do you ever need to have multiple versions of the DB2 product code running in the same LPAR
> (like: code fixes migrated to one SSID but not another)? That´s a lot easier if your STEPLIB points
> to subsystem-specific executables.
>
[...]49_4B9EB05D.16252.4EC3402C@jacampbell.acslink.net.au 253 28 37_Re: Values used in triggers DB29 z/OS17_Darren Kilpatrick31_darren.kilpatrick@WELLPOINT.COM31_Mon, 15 Mar 2010 13:28:09 +0000321_UTF-8 Mark,
Thanks for the help. I have put the Current Sqlid and Current timestamp in the trigger. How about some other values, like the program and/or transaction that is doing the delete? Do you know of a way to get that? Let's also assume a delete can be run from different programs, so I can't hard code it. [...]63_7304329475102818.WA.darren.kilpatrickwellpoint.com@www.idug.org 282 165 37_Re: Values used in triggers DB29 z/OS0_20_Mark.Doyle@WPSIC.COM31_Mon, 15 Mar 2010 09:35:19 -0500609_US-ASCII Darren,

What is true for those two special registers is true for ANY special
register. You just need to find the correct one(s) for your environment
to find the info you need. Without knowing the details, I can't give
definitive advice, but I would expect that one or more of the CURRENT_*
special registers (or maybe SESSION_USER) will give you need. I would
expect to need different special registers for static programs that for
dynamic / distributed processes. One way you might find out what
registers you need is to take a shell of the type of program you want [...]67_OF171F0C20.59C26268-ON862576E7.004F098B-862576E7.0050236D@wpsic.com 448 105 28_Re: Stored procedure STEPLIB14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 15 Mar 2010 12:45:58 -0400361_iso-8859-1 James,

I wasn't specifically aware of that wrinkle, but I'd never assign a WLM SPAS to more than one subsystem anyway. So I suppose using a linklist is only feasible for _one_ subsystem in an LPAR. Any others must all use explicit allocations, or stored procedures will fail when multiple releases are present, if I'm reading this right. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC466EC@MAIL02.fisalan.nycnet 554 98 65_Abend=0c4 reason=00000000 for DB2 V9 NFM distributed applications9_Ed Vetock25_Ed_Vetock@NAVYFEDERAL.ORG31_Mon, 15 Mar 2010 13:33:24 -0400630_US-ASCII Hi

I was wondering if anyone else has seen the following error:

We are experiencing ABEND=0c4; reason=00000000 in our UNIT and INTG
distributed address spaces since we upgraded to New Function Mode.

History. We upgraded to New Function Mode on Jan 7 2010 and Feb 21 for
UNIT and INTG respectively. We then rebound on z/OS the SYSLN***
packages using REOPT(AUTO) vs. REOPT(ONCE) on Feb 25 and March4 for UNIT
and INTG. With one exception; that is when we started receiving the
ABEND=0C4 messages. We then rebound back to REOPT(ONCE) but are still
seeing the ABEND=0C4 messages. [...]73_OF57CDF4D5.EA8C7597-ON852576E7.00601E0C-852576E7.00604AC7@navyfederal.org 653 209 61_Re: {MVS} unexplained cpu increase in DB2 mstr and Top Secret16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 15 Mar 2010 14:32:45 -0400556_windows-1252 Deborah,

Do you have a CICS-DB2 workload? If so, is it possible that something
happened around 12/17/09 such that more CICS-DB2 threads are terminating as
opposed to being reused, thereby driving MSTR thread creation (and
termination) activity, and associated sign-on activity? You could see thread
create and terminate activity in a DB2 monitor accounting detail report (and
you could group the data in this report by connection type, so that you'd
have all the data related to CICS-DB2 activity in one report). [...]59_de7260f31003151132s1a478aacvdd8c1ed0fa3e565f@mail.gmail.com 863 56 61_Re: 2010 NA RUG Coupon Code and $1600 Mentor Program Discount35_Galluzzo, Lori (HC Product Support)20_lori.galluzzo@HP.COM31_Mon, 15 Mar 2010 18:33:08 +0000632_us-ascii Dear Rug Members -
Below is the website for the $50 RUG DISCOUNT COUPON CODE - RUG discount code RUG10 so you can save $50 off of their 2010 North America Conference registration . The code will be active throughout the early-bird registration period, which ends on March 26, 2010.

Have you heard about the IDUG Mentor Program? IDUG is looking for long-time conference attendees to publicly recognize at the 2010 event. If you or any of your RUG members have attended five IDUG conferences within the past ten years, please ask them to apply at . IDUG members who [...]74_D6F0B7F3CE566B42B24474A7E7B8CBC43472F0960F@GVW0547EXC.americas.hpqcorp.net 920 28 37_Re: Values used in triggers DB29 z/OS17_Darren Kilpatrick31_darren.kilpatrick@WELLPOINT.COM31_Mon, 15 Mar 2010 19:31:19 +0000371_UTF-8 Mark,
Apparently there are only a few special registers on z/OS that DB2 will allow you to use the SET command (according to SQL reference). I could only fine a stored procedure called WLM_SET_CLIENT_INFO that can be called that will allow you to change those. Other than that, I have not found another way to get the values of program name, etc. Thoughts? [...]63_8390233807775005.WA.darren.kilpatrickwellpoint.com@www.idug.org 949 72 17_Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 15 Mar 2010 20:02:25 +0000420_UTF-8 These are numbers for the same query, same table, just different members of the same data sharing group.
One member is a bit busier causing a noticable decrease in BP hit ratio and increase in sync_read_io's and a large
increase in response time. I thought it was interesting just to see the numbers. This is the only object in
that bufferpool because otherwise it's i/o overwhelms all in it's path. [...]53_9731366992749520.WA.jorghluekebcbsmn.com@www.idug.org 1022 372 37_Re: Values used in triggers DB29 z/OS0_20_Mark.Doyle@WPSIC.COM31_Mon, 15 Mar 2010 15:51:40 -0500655_US-ASCII Darren,

I don't believe you need to set anything.

When I run this SQL in SPUFI:
SELECT
CURRENT APPLICATION ENCODING SCHEME "1" , CURRENT CLIENT_ACCTNG "2" ,
CURRENT CLIENT_APPLNAME "3" , CURRENT CLIENT_USERID "4" , CURRENT
CLIENT_WRKSTNNAME "5" , CURRENT DATE "6" , CURRENT DEBUG MODE "7" ,
CURRENT DECFLOAT ROUNDING MODE "8" , CURRENT DEGREE "9" , CURRENT
LOCALE LC_CTYPE "10" , CURRENT MEMBER "11" , CURRENT OPTIMIZATION HINT
"12" , CURRENT PACKAGE PATH "13" , CURRENT PACKAGESET "14" , CURRENT
PATH "15" , CURRENT PRECISION "16" , CURRENT REFRESH AGE "17" , CURRENT
ROUTINE VERSION "18" , CURRENT [...]67_OFDEEEF85E.5AA0F942-ON862576E7.0070103E-862576E7.00729862@wpsic.com 1395 26 34_Re: GetPages and SELECT statements5_anand19_mahadea@LABCORP.COM31_Mon, 15 Mar 2010 20:58:18 +0000749_UTF-8 Troy,
Would this kind of exercise be meaningful when the amount of data is vastly different between my test environment and production environment?
Thanks

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]51_3310201229269192.WA.mahadealabcorp.com@www.idug.org 1422 28 34_Re: GetPages and SELECT statements5_anand19_mahadea@LABCORP.COM31_Mon, 15 Mar 2010 20:54:27 +0000305_UTF-8 Adam,
Thanks for your response. I'm not sure of the correlation between index size and number of getpages. When the index length increases due to the addition of 1 or more columns, why would more getpages need to be issued? Is it because a larger index could result in more nodes?
Thanks [...]51_5080661907801028.WA.mahadealabcorp.com@www.idug.org 1451 308 21_Re: Interesting Stats35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 15 Mar 2010 21:18:37 -0400468_utf-8 If there's only one object in the pool, one system being busier should not make any difference in pool hits.
You may want to check your GBP size, and dir/data ratio.

Regards,
Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com [...]46_D519495E0E2642AAA853F8237B419BC0@DellNotebook3 1760 42 36_*bad* IBM_SERVICE_DATA in PLAN_TABLE12_SteveMallett21_malutjuta@HOTMAIL.COM31_Tue, 16 Mar 2010 05:36:32 +0000494_UTF-8 Dear Listers,

After migrating to DB2 for z/OS V9 CM some of our processes started complaining about being unable to perform Character Conversions involving Unicode data contained in column IBM_SERVICE_DATA in the PLAN_TABLE.

The problem only seems to occur in Tablespaces created as Unicode (perhaps unsurprisingly) and only in a sparse number of rows. Probably not caused by the DB2 V9 migration but by the coincidental migration of PLAN_TABLE data to new TS instead. [...]53_2744471881018269.WA.malutjutahotmail.com@www.idug.org 1803 193 21_Re: Interesting Stats0_24_hhuang@DCCSH.ICBC.COM.CN31_Tue, 16 Mar 2010 13:47:04 +0800816_GB2312 Jorg,

I just want to confirm, same local BP size, BP parameters(VPSEQT, etc.)?
And also same checkpoint settins?

Huang Hao
Senior Manager, Dept. System
ICBC Data Center(Shanghai)
Tel :(+86)021-28989825
Mail:hhuang@dccsh.icbc.com.cn

Jorg Lueke
·¢¼þÈË: IDUG DB2-L
2010-03-16 04:02
Çë´ð¸´ ¸ø
IDUG DB2-L

ÊÕ¼þÈË
DB2-L@IDUGDB2-L.ORG
³­ËÍ

Ö÷Ìâ
[DB2-L] Interesting Stats

These are numbers for the same query, same table, just different members
of the same data sharing group.
One member is a bit busier causing a noticable decrease in BP hit ratio
and increase in sync_read_io's and a large
increase in response time. I thought it was interesting just [...]69_OF2AD96EF4.B08171D1-ON482576E8.001F9036-482576E8.001FC668@icbc.com.cn 1997 118 29_AW: [DB2-L] Interesting Stats35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 16 Mar 2010 08:56:47 +0100403_iso-8859-1 Jörg

Do you have any ideas about the number of rereads, i.e. a page, which was already read into the bufferpool and must be read from disk a second time? Because this is a hint, that your bufferpool is too small?

Do you mean with 'bit busier', that there is more traffic on that tablespace? And therefore the pages, your test-query needs, are always not in the bufferpool? [...]43_DB2-L%201003160756565943.06A2@IDUGDB2-L.ORG 2116 33 44_AUTO: Angelo Corridori is out of the office.16_Angelo Corridori27_Angelo.Corridori@MARIST.EDU31_Tue, 16 Mar 2010 04:00:36 -0400398_US-ASCII I am out of the office until 03/17/2010.

I will respond to your message when I return. Primarily working from home;
limited access to e-mail/phonemail. Please send an e-mail if you need to
get touch with me.

Note: This is an automated response to your message "[SUSPECTED SPAM]
DB2-L Digest - 15 Mar 2010 to 16 Mar 2010 (#2010-74)" sent on 3/15/10
21:00:00. [...]68_OF2687497C.54C549CF-ON852576E8.002C001B-852576E8.002C001B@marist.edu 2150 31 34_Re: GetPages and SELECT statements12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Tue, 16 Mar 2010 08:35:20 +0000393_UTF-8 The addition of columns to an index will increase the size of the index - more pages and possibly more levels. As already mentioned, you have to be aware of the possible impact on updaters / inserters. Have you studied how your existing index is used?

Do you actually have a problem? In your original posting you don't say if you're trying to fix a problem with performance. [...]54_2615670058838698.WA.adambaldwines.ibm.com@www.idug.org 2182 87 39_Reminder - 2010 IDUG EU Call for papers10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 16 Mar 2010 10:59:17 +0100654_US-ASCII IDUG is proud to announce IDUG 2010 Europe, taking place 8-12 November
2010 at the Hilton Vienna Hotel in Vienna, Austria.

As you already know, the quality of the conference is highly dependent on
the quality of the content.
As you are DB2 professionals who would like to share your experience and
knowledge with DB2 peers, we invite you to participate as speakers at
IDUG's
European conference 2010. When professionals, like you, share best
practices and user experiences with the DB2 user community, IDUG Europe
will continue its track record of success. If you are interested in
submitting an abstract, [...]66_OF7A8FE5B5.75C7C5F6-ONC12576E8.0036491B-C12576E8.0036834C@cesve.it 2270 116 115_IDUG Webcast on =?ISO-8859-1?Q?=B3DB2_For_z/OS_Stored_Procedures=3A_Trends_And_Technol?= =?ISO-8859-1?Q?ogy=22=A0?=10_Mark Labby21_mlabby.idug@GMAIL.COM31_Tue, 16 Mar 2010 06:19:44 -0400502_ISO-8859-1 IDUG Webcast on ³DB2 For z/OS Stored Procedures: Trends And Technology" 
will be available at http://www.idug.org/catterall.mp4  on March 22,
2010.  

Suggested start time to watch the webcast is at 1:00pm, EST with the live
chat to immediately follow.   

Since we have members in all time zones, we are making the webcast
available starting earlier (7:00am EST), but please note that Robert
Catterall will answer your questions from 2:10 pm - 2:30 pm  EST. [...]36_C7C4D380.1ADFC%mlabby.idug@gmail.com 2387 98 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Tue, 16 Mar 2010 12:56:19 -0000669_us-ascii Steve,

I have a customer who have this problem, I know they opened a PMR with
similar issues last week, so they are at early days yet.

Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com

http://www.ca.com/

P please don't print this e-mail unless you really need to!

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SteveMallett
Sent: 16 March 2010 05:37
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] *bad* IBM_SERVICE_DATA in PLAN_TABLE [...]56_3D02E8610514C04F991CF832BA154C6610566F44@UKSLMS11.ca.com 2486 255 21_Re: Interesting Stats13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 16 Mar 2010 08:01:34 -0700701_utf-8 Are the virtual pool sizes the same between the two members?

_____________________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jorg Lueke
Sent: Monday, March 15, 2010 1:02 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Interesting Stats

These are numbers for the same query, same table, just different members of the same data sharing group.
One member is a bit busier causing a noticable decrease in BP hit ratio and increase in sync_read_io's and a large
increase in response time. I thought it was interesting just to see the numbers. This is the only object in
that bufferpool because otherwise it's i/o overwhelms [...]64_927FE8790B320742927BCF9ADF7AE48A18EE6388C7@PDXMAIL.pacificorp.us 2742 168 99_Re: Many New Index Options in DB2 =?windows-1252?Q?=96_Version_9_Performance_Features_=96_?=Part 1916_Robert Catterall21_rfcatterall@GMAIL.COM31_Tue, 16 Mar 2010 11:29:42 -0400587_ISO-8859-1 Darren,

In choosing an index page size, one consideration is your intention
regarding the use of the index compression feature of DB2 Version 9. If
you're going to compress an index, you will have to go with a page size
larger than 4K. Your choice of 8K versus 16K versus 32K for the index page
size will probably be based on the output you get from a DSN1COMP utility
job run for the index in question. DSN1COMP will provide, for the various
page size choices, an estimate for the amount of disk space that could be
saved via compression and [...]59_de7260f31003160829g38a0cc20p3e4952699541f8dc@mail.gmail.com 2911 110 53_Re: Moving yourself from DB2 to SQL Server (Maybe OT)10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 16 Mar 2010 17:02:14 +0100477_US-ASCII Esteemed listers

Sorry if it sounds a little OT.

In the past I read (or I think I read) some posts dealing with the 'move'
of DBAs/sysprogs from DB2 (z/OS) to SQL Server. They said it was a good
advantage to know something of DB2.

Can anyone confirm it ? If someone is working with DB2 and SQL Server
(or started to work with SQL Server) , can she/he provide any
hits/tips/reads...etc to start to learn something about SQL Server [...]66_OFAB338728.EEB924D3-ONC12576E8.0056C7B4-C12576E8.0057BE22@cesve.it 3022 75 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 16 Mar 2010 11:44:17 -0500401_US-ASCII Why would you convert the data in the column IBM_SERVICE_DATA?
When I look at SDSNSAMP(DSNTESC) the column is defined as
"IBM_SERVICE_DATA" VARCHAR(254) FOR BIT DATA
NOT NULL WITH DEFAULT,

Since it is defined as 'for bit data', you should never convert it.
Especially since it contains a bunch of address like offsets and ebcdic
characters even in a unicode table. [...]43_94389B0993C14FA3B307AAB422E6D71B@mikelaptop 3098 51 39_DB2 for z/OS equivalent for Oracle HINT12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Tue, 16 Mar 2010 17:32:14 +0000819_ISO-8859-1 Is there any DB2 equivalent of the Oracle HINT facility. We have a table
whereby 90% of the queries need to use a particular index but one particular
query needs to use a different index. Is there any way to achieve this.

Jim McAlpine

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]59_21d1f8c21003161032t48486264ve48bf18d819e2f63@mail.gmail.com 3150 81 43_Re: DB2 for z/OS equivalent for Oracle HINT10_Dave Nance16_dav1mo@YAHOO.COM31_Tue, 16 Mar 2010 11:10:04 -0700591_iso-8859-1 Jim,     You can make use of opthints in your plan table. You don't mention directly, is the query not using the index it should? You may want to take a look at the statistics for the table and this index to see why the query is not making use of what you deem a more suitable access path.   David Nance   ________________________________ From: Jim McAlpine To: DB2-L@IDUGDB2-L.ORG Sent: Tue, March 16, 2010 1:32:14 PM Subject: [DB2-L] DB2 for z/OS equivalent for Oracle HINT Is there any DB2 equivalent of the Oracle HINT facility.  We have a table [...]43_341853.85114.qm@web57802.mail.re3.yahoo.com 3232 506 43_Re: DB2 for z/OS equivalent for Oracle HINT16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 16 Mar 2010 14:18:09 -0400400_US-ASCII Like Dave mentioned - use OPTHINT in the PLAN_TABLE as the second
choice. For dynamic SQL you can use the SET CURRENT OPTIMIZATION-HINT to
try to force a specific index usage.

It sounds like you have multiple columns in the index, so try RUNSTATS
with COLGROUP to gather better frequency statistics.

Steen Rasmussen
CA

Sr Engineering Services Architect [...]56_0A18F096E689AC43BB2F52DAE5674D6007EF7C30@USILMS14.ca.com 3739 98 53_Re: Moving yourself from DB2 to SQL Server (Maybe OT)10_Joe Geller21_joerg6666@HOTMAIL.COM31_Tue, 16 Mar 2010 18:25:56 +0000565_UTF-8 My strongest advice is to keep an open mind and prepare to learn new techniques. I’ve worked a bit with SQLServer (as well as Oracle), and for the most part I find that DB2 does things much better. But not in all cases – occasionally I find a feature or implementation in another DBMS that DB2 doesn’t do as well. We sometimes forget that there is more than one way to do something. Luckily, IBM is not afraid to change things where appropriate. A good example is reordered row format, which is a big help for variable length columns.
SQLServer [...]53_4791297150339239.WA.joerg6666hotmail.com@www.idug.org 3838 135 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Tue, 16 Mar 2010 18:41:16 -0000486_us-ascii Mike,

The problem is that the data is probably converted on the path between
the terminal (or whatever means of insert) and DB2 - at least this is
what my customer found.

Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com

http://www.ca.com/

P please don't print this e-mail unless you really need to! [...]56_3D02E8610514C04F991CF832BA154C6610566F69@UKSLMS11.ca.com 3974 23 21_Re: Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 16 Mar 2010 20:29:41 +0000622_UTF-8 Yes, the BP are defined exactly the same on each member

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]53_5798565335265931.WA.jorghluekebcbsmn.com@www.idug.org 3998 32 33_Re: AW: [DB2-L] Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 16 Mar 2010 20:51:56 +0000474_UTF-8 Walter,

The BP is 60,000 buffers and the object has 200,000 pages, so maybe to big but not too little.
The application connects via WebSphere JDBC, since there's no sysplex parm defined the workload can go to any member.
Usually on any given day the bulk of the connections stay on one member and so that member gets bombarded with the i/o
for this tablespace. For instance, yesterday one member had 15000000 getpages the next highest was 600000. [...]53_5741803406748393.WA.jorghluekebcbsmn.com@www.idug.org 4031 26 21_Re: Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 16 Mar 2010 20:31:18 +0000699_UTF-8 Joel,

The GBP read ratio is 0%. This table is hit and scanned constantly so pages are unlikely to stay in the pool very long.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]53_9168393366094059.WA.jorghluekebcbsmn.com@www.idug.org 4058 35 85_Plan Stability =?UTF-8?Q?=E2=80=93_Version_9_Performance_Features_=E2=80=93_?=Part 2011_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 16 Mar 2010 21:55:04 +00000_51_8318624793535413.WA.davedavebeulke.com@www.idug.org 4094 192 15_RTS db2 v9 z/os12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Tue, 16 Mar 2010 15:18:43 -0700523_us-ascii Greetings list members. I have a question for the shops that are using
RTS. We recently converted to V9 and started to use the new RTS tables
IBM has generated. We have generally directly queried the tables when
researching data changes. I'm wondering how other shops that use RTS
query the tables. Do you directly query the tables or use the DSNACCOX
stored procedure for queries? I'm sure I'll have additional questions
depending on how others are using the RTS tables. Thanks in advance! [...]55_4B518F9CD247DA458840B9A5B9435D1602F5BC92@EXCV25.srp.gov 4287 373 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE13_Steve Mallett21_malutjuta@HOTMAIL.COM31_Wed, 17 Mar 2010 08:05:20 +0930712_iso-8859-1 Hi Mike,

We don't particularly want to convert that data but find that processes such as the following do have conversion problems :

SELECT * FROM PLAN_TABLE, third party Unload products & probably others, finding errors similar to

DSNT408I SQLCODE = -331, ERROR: CHARACTER CONVERSION CANNOT BE PERFORMED
BECAUSE A STRING, POSITION 5, CANNOT BE CONVERTED FROM 367 TO 37,
REASON 12
DSNT418I SQLSTATE = 22021 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXROUA SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -260 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFEFC' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC [...]43_COL108-W273E13C4C10C0913075C79A42D0@phx.gbl 4661 498 21_Re: Interesting Stats35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 16 Mar 2010 22:21:01 -0400422_utf-8 Jorg,

Perhaps you need to show the rest of the acctg information.
Your data below implies that your huge elapsed time difference is Synch IO, and you probably have poor dasd performance
as one of your underlying causes, as well as poor pool performance.
You can easily get your avg elapsed per synch IO from the actg info.
We can't see if there is any SP activity from the data you provided. [...]46_B25D12B949234BF9A4B924C3988068CC@DellNotebook3 5160 176 19_Re: RTS db2 v9 z/os13_Steve Mallett21_malutjuta@HOTMAIL.COM31_Wed, 17 Mar 2010 13:38:04 +0930479_Windows-1252 Hi Shery,

For one of out V8 clients we use the queries from the manual to generate listdefs for thier weekly Reorg jobs,

same thresholds as DSNACCOR

There's also a very interesting presentation around on use of RTS History tables which is outside the scope of the manuals

regards,

Steve

Date: Tue, 16 Mar 2010 15:18:43 -0700
From: Shery.Hepp@SRPNET.COM
Subject: [DB2-L] RTS db2 v9 z/os
To: DB2-L@IDUGDB2-L.ORG [...]43_COL108-W37581892A81D889238FAD1A42C0@phx.gbl 5337 26 41_Garth Oldham/Australia/IBM is unavailable12_Garth Oldham19_vcgarth@AU1.IBM.COM31_Wed, 17 Mar 2010 16:46:10 +1100393_US-ASCII I will be out of the office starting 17/03/2010 and will not return until
19/03/2010.

I am unavailable to attend to your email on Thursday 18th March 2010. For
urgent requests please email the DBDCSUP@AU1.IBM.COM mailbox. For any
Requested product maintenance please send the request to
DBDCSUP@AU1.IBM.COM it will be sent to a senior DB2 person for approval. [...]69_OFD0C9AD9E.01C9296F-ONCA2576E9.001FB19E-CA2576E9.001FB19E@au1.ibm.com 5364 54 19_db2 iSeries problem6_John S15_dev01ibm@YYW.SE31_Wed, 17 Mar 2010 06:55:37 +0000361_UTF-8 Hi,
We have a problem on DB2 iSeries with a user account that can't seem to use packages.

The user say USER1 can use the iseries access odbc to connect to the database and run SQL successfully.
The same USER1 can use DB2 Connect from another windows machine to connect to the database.
Using query tools, we can use USER1 to run SQL. [...]47_9863150725184768.WA.dev01ibmyyw.se@www.idug.org 5419 139 42_Re: Moving yourself from DB2 to SQL Server10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 17 Mar 2010 09:46:33 +0100553_US-ASCII Hi Joe

Thank you for your reply.

I'm still not sure I'll work with SQL Server (after 15+ years of DB2 in
general I'd like to continue to work with it) but it's better to be ready,
and I'm (in general) happy to know something new.

I talked with some people working with SQL Server and the first
surprising thing is they think on a small scale even if they have a lot of
data to manage. They think on a small scale even when they have many
tx/sec and they (almost all) consider servers like a PC (even if [...]66_OF4F569093.5C86DCFE-ONC12576E9.0029F196-C12576E9.002FDAC0@cesve.it 5559 395 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 17 Mar 2010 08:43:53 -0000689_us-ascii My customers found that they had the same problems if they used SPUFI
for their SQL by the way, not only ISV tools.

Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com

http://www.ca.com/

P please don't print this e-mail unless you really need to!

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steve Mallett
Sent: 16 March 2010 22:35
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] *bad* IBM_SERVICE_DATA in PLAN_TABLE [...]56_3D02E8610514C04F991CF832BA154C6610566F6E@UKSLMS11.ca.com 5955 114 43_Re: DB2 for z/OS equivalent for Oracle HINT12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Wed, 17 Mar 2010 09:18:03 +0000640_windows-1252 On Tue, Mar 16, 2010 at 6:18 PM, Rasmussen, Steen wrote:

> Like Dave mentioned – use OPTHINT in the PLAN_TABLE as the second choice.
> For dynamic SQL you can use the SET CURRENT OPTIMIZATION-HINT to try to
> force a specific index usage.
>
> It sounds like you have multiple columns in the index, so try RUNSTATS with
> COLGROUP to gather better frequency statistics.
>
>
>
> *Steen Rasmussen*
> CA
>
> Sr Engineering Services Architect
>
> IBM Certified Database Associate - DB2 9 Fundamentals
>
> IBM Certified Database Administrator - DB2 [...]59_21d1f8c21003170218r6f6ae1ael46c5da9a4e9fe019@mail.gmail.com 6070 108 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 17 Mar 2010 10:49:04 +0100330_US-ASCII I had in the past a similar (not equal and in other tables) problem in a
sandbox, and it was due to wrong UNICODE service(s) in z/OS.

Do you have CCSID 37 in your z/OS UNICODE conv services ? If you're in V8
it's mandatory to have CCSID from/to CODEPAGE 37 (as 500 and 1047) so
probably you have it. [...]66_OF08FE0EA6.50EB9608-ONC12576E9.00343FB4-C12576E9.003593C5@cesve.it 6179 106 19_Re: RTS db2 v9 z/os10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 17 Mar 2010 11:13:58 +0100422_US-ASCII Hi Shery

There are many papers providing queries for RST tables so it seems this is
the preferable method. I used DSNACCOX only for some tests when I
implemented RTS to decide if it was useful or not.

Then DSNACCOX had some 'bugs' and I found more useful (and flexible) for
my shop to use dedicated query embedded in REXXs to generate reorg/IC/etc
jobs and scheduling via CONTROL_M [...]66_OFFA22AED0.6090EA82-ONC12576E9.0035FE69-C12576E9.0037DB73@cesve.it 6286 81 43_Re: DB2 for z/OS equivalent for Oracle HINT12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Wed, 17 Mar 2010 10:33:17 +0000485_ISO-8859-1 >
> There are 2 indexes on the table. For most of the query types we need
>> to use the main index, but for one particular query we need to use the
>> second index. I'll check out OPTHINT.
>>
>
> Thanks
>
> Jim McAlpine
>

I haven't explained that very well. The appropriate index is being chosen
for the query but because of the nature of the data that index performs
badly. Using an alternative index performs much much better. [...]59_21d1f8c21003170333q59da116cqec246c18a41d6cdd@mail.gmail.com 6368 298 19_Re: RTS db2 v9 z/os10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 17 Mar 2010 09:13:20 -0400394_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA6143B967F@NSTMC101PEX1.ubsw.net 6667 126 49_z/OS Session variables - Super duper weirdness...11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 17 Mar 2010 14:59:07 +0100509_ISO-8859-1 I have a real nasty problem in DB2 V9 NF...now it could be that I am
simply nuts but O did the following

1) Set the ZPARMS in the DECP to look like this

DELIM=DEFAULT,
SQLDELI=QUOTE,
DSQLDELI=APOST

2) Bounce DB2 and Compile, link, bind and execute a COBOL program that
runs with APOST in the precomp and comp step that does three GETVARIABLES
for
SYSIBM.COBOL_STRING_DELIMITER, SYSIBM.SQL_STRING_DELIMITER and
SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER. [...]64_OFECF6EF27.877255E7-ONC12576E9.004BC018-C12576E9.004CD352@seg.de 6794 187 45_Stored Procedure nest level exceeded response13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 17 Mar 2010 11:17:41 -0400405_utf-8 Good morning friends,

DB2 z/OS v8 or v9

Has anyone encountered exceeding the max number of nest levels (16)
for a stored procedure?
It's documented that 16 is the limit, but I can't find out what happens
when the limit is exceeded.
In my testing, the process just seems to pretend the 17th call didn't
occur. No SQLcode or warning; no msgs that I can find in logs. [...]70_E814208E0983274198870632E8697D5B0E3B519B@nasa-dtw-ex001.nasa.cpwr.corp 6982 34 28_Re: Stored procedure STEPLIB11_Dave Harvey17_db2dave@GMAIL.COM31_Wed, 17 Mar 2010 15:23:42 +0000563_UTF-8 I have only recently been exposed to v9 but was fascinated by the WLM-SP issue, particularly the excellent v9 dissertation Phil pointed to (http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.dshare/db2z_wlmmanagedstoredprocs.htm) because I faced the same issue in v8.

You could say that the same dissertation was missing from v8 but it happens to be the neatest work-around which we used, mostly because it mimicked our other STC's and even DSNUPROC. Not sure why IBM's example includes SDSNEXT/RUNLIB etc. [...]49_4081612145009193.WA.db2davegmail.com@www.idug.org 7017 104 43_Re: DB2 for z/OS equivalent for Oracle HINT12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Wed, 17 Mar 2010 15:35:58 +0000590_ISO-8859-1 On Wed, Mar 17, 2010 at 10:33 AM, Jim McAlpine wrote:

> There are 2 indexes on the table. For most of the query types we
>>> need to use the main index, but for one particular query we need to use the
>>> second index. I'll check out OPTHINT.
>>>
>>
>> Thanks
>>
>> Jim McAlpine
>>
>
> I haven't explained that very well. The appropriate index is being chosen
> for the query but because of the nature of the data that index performs
> badly. Using an alternative index performs much much better.
>
> [...]59_21d1f8c21003170835i6ba643cdrf051b9183f67291e@mail.gmail.com 7122 556 56_Re: [SPAM] Stored Procedure nest level exceeded response16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Wed, 17 Mar 2010 10:39:06 -0500510_UTF-8 Hi Dave,

Yes, the calling procedure should receive a negative SQLCODE when the 17 level call is attempted, and I have tested this but with UDFs.

Now, remember that the error is returned to the caller, which is the 16th called procedure. What happens next depends upon the language and parameter style of the stored procedures involved all through the series of calls. Proper error handling is the responsibility of the procedure, and it has to echo that back to the caller, and so on. [...]35_020901cac5e7$fc332d10$f4998730$@com 7679 38 34_Re: GetPages and SELECT statements5_anand19_mahadea@LABCORP.COM31_Wed, 17 Mar 2010 15:53:22 +0000393_UTF-8 The addition of columns to an index will increase the size of the index - more pages and possibly more levels. As already mentioned, you have to be aware of the possible impact on updaters / inserters. Have you studied how your existing index is used?

Do you actually have a problem? In your original posting you don't say if you're trying to fix a problem with performance. [...]51_5687134470938165.WA.mahadealabcorp.com@www.idug.org 7718 124 50_Fw: [DB2-L] Moving yourself from DB2 to SQL Server13_Mick P Graley16_mgraley2@CSC.COM31_Wed, 17 Mar 2010 18:02:29 +0100600_US-ASCII I'm probably gonna set off a holy war here, but here goes anyway (please
don't be too harsh - read my last sentence before crucifying me)...

I agree with Joe. Because we're so comfortable with the performance and
reliability of mainframe DB2 sometimes we forget that the other database
systems also have plus points. I've been working with DB2 for over 20 years
but I've also had a few years experience of Ingres and Oracle on HP-UX and
dabbled at Transact-SQL (SQL Server) programming on a project for my old
boss. Because I used to be a programmer he thought [...]65_OF70D1FCEA.D939650F-ON802576E9.005D4D3F-802576E9.005D9CD7@csc.com 7843 388 49_Re: Stored Procedure nest level exceeded response13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 17 Mar 2010 13:14:55 -0400570_utf-8 Thanks Dan.

The language is Rexx and parameter style is General.

I’ll try parmstyle SQL.

The program queries SQlcode and it’s zero.

Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel Luksetich
Sent: Wednesday, March 17, 2010 11:39 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [SPAM] Stored Procedure nest level exceeded response

Hi Dave,

Yes, the calling procedure should receive a negative SQLCODE when the 17 level call is attempted, and I have tested this but with UDFs. [...]70_E814208E0983274198870632E8697D5B0E3B519D@nasa-dtw-ex001.nasa.cpwr.corp 8232 807 49_Re: Stored Procedure nest level exceeded response16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Wed, 17 Mar 2010 12:26:12 -0500528_UTF-8 Parm style SQL not allowed for REXX, only general or general with nulls.

I’m afraid you’ll have to interrogate you test program more completely.

I have a recursive SQL procedure you can try to make sure DB2 reacts properly to the limit. You can borrow it if you like.

Dan

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave
Sent: Wednesday, March 17, 2010 12:15 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Re: Stored Procedure nest level exceeded response [...]35_022b01cac5f6$f3024780$d906d680$@com 9040 236 51_AW: [DB2-L] DB2 for z/OS equivalent for Oracle HINT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Wed, 17 Mar 2010 18:27:03 +0100343_iso-8859-1 Hi

The value in this column must match the value, you use when you execute the SET CURRENT OPTIMIZATION HINT.

For example: The value in OPTHINT is 'HUGO, the you execute SET CURRENT OPTIMIZATION HINT = 'HUIGO'

I believe, if DB2 accepts your hint, you will get a +394, when you execute your original query. [...]43_DB2-L%201003171727128708.072A@IDUGDB2-L.ORG 9277 510 49_Re: Stored Procedure nest level exceeded response13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 17 Mar 2010 13:30:28 -0400477_utf-8 Thanks again Dan. I was just coming back to say the reference tells me Rexx & Parmstyle SQL are not compatible.

I’ll do some more diagnosing.

Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel Luksetich
Sent: Wednesday, March 17, 2010 1:26 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Stored Procedure nest level exceeded response

Parm style SQL not allowed for REXX, only general or general with nulls. [...]70_E814208E0983274198870632E8697D5B0E3B519E@nasa-dtw-ex001.nasa.cpwr.corp 9788 50 20_peoplesoft on z/inux0_21_BudGreenman@ONGOV.NET31_Wed, 17 Mar 2010 13:43:52 -0400686_US-ASCII Is anyone running peoplesoft on zlinux with db2?
We are looking into implementing peoplesoft and would like to run it using
db2 on zlinux, however oracle does not certify that set up, but they do
certify oracle on zlinux so we may have to go with an oracle database.

Bud Greenman
Applications Programming Manager

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
[...]67_OF6A3B0097.396552F7-ON852576E9.00608FA3-852576E9.006165E6@ongov.net 9839 523 43_Re: DB2 for z/OS equivalent for Oracle HINT16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 17 Mar 2010 14:22:42 -0400393_US-ASCII To add to Walter's description, invent a name to specify in the
PLAN_TABLE for OPTHINT. The specify the INDEX as well as matchcols etc.
you want for this specific statement listed in the PLAN_TABLE. For
dynamic SQL specify the SET CURRENT OPTIMIZATION HINT ahead of the SQL
statement. For static SQL you can also specify the OPTHINT(name) in the
REBIND statement. [...]56_0A18F096E689AC43BB2F52DAE5674D6007EF80DD@USILMS14.ca.com 10363 680 43_Re: DB2 for z/OS equivalent for Oracle HINT12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Wed, 17 Mar 2010 14:52:44 -0500441_US-ASCII On our website, www.hlstechnologies.com, there is our presentation on "Using
SPUFI to Explore DB2 Hints" It is in the download area.

The presentation explains how to validate a DB2 Hint and thus avoid the
problem with Hint rejection. The common error messages from a rejected Hint
are explained.

If you have any questions or want to do a free web meeting to explore more
of these issues. Let me know. [...]35_014401cac60b$69abb100$3d031300$@com 11044 289 30_[meta] Filtering out DB2-L web14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 17 Mar 2010 15:52:18 -0400576_us-ascii All,

It's come to my attention that there are commercial web filters out there that are blocking the web interface of our listserv (and a lot of other listservs). I don't know the reason they're doing this, mind, unless the conceit is that all listservs are non-work-related.

I made contact with one DB2 DBA at such a company yesterday, at New York's instance of the z/OS Technical Summit, and today gave her the instructions necessary to subscribe to DB2-L without using the Web interface, so hopefully for that one company the problem is solved. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46704@MAIL02.fisalan.nycnet 11334 29 30_Capture dynamic SQL statements4_Anil21_alisha_kale@YAHOO.COM31_Wed, 17 Mar 2010 20:31:55 +0000632_UTF-8 Hi All !

Can someone share with me the various techniques that could be used to capture dynamic SQL statements from a db2 subsystem in the z/os environment (DB2 V9 CM for z/os).

Thanks.

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]52_4771211274493791.WA.alishakaleyahoo.com@www.idug.org 11364 94 34_Re: Capture dynamic SQL statements10_Mark Stone17_mastone@GMAIL.COM31_Wed, 17 Mar 2010 13:37:03 -0700634_ISO-8859-1 I think OSC can query the dynamic statement cache for at least some recent
stuff.

On Wed, Mar 17, 2010 at 1:31 PM, Anil wrote:

> Hi All !
>
> Can someone share with me the various techniques that could be used to
> capture dynamic SQL statements from a db2 subsystem in the z/os environment
> (DB2 V9 CM for z/os).
>
> Thanks.
>
> Anil
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> * If you are going to attend only one [...]59_b73ec4021003171337w6fd64972v5372d98ab01135a0@mail.gmail.com 11459 41 34_Re: Capture dynamic SQL statements16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 17 Mar 2010 16:40:34 -0400522_UTF-8 I will refrain from any vendor tools for now, but you could do an EXPLAIN of the dynamic statement cache

Steen Rasmussen
CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 DBA for z/OS

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil
Sent: Wednesday, March 17, 2010 3:32 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Capture dynamic SQL statements [...]56_0A18F096E689AC43BB2F52DAE5674D6007EF8222@USILMS14.ca.com 11501 51 34_Re: Capture dynamic SQL statements14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 17 Mar 2010 17:00:32 -0400485_us-ascii We're trying this right now. Two words: Vendor Products.

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil
Sent: Wednesday, March 17, 2010 4:32 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Capture dynamic SQL statements

Hi All !

Can someone share with me the various techniques that could be used to capture dynamic SQL statements from a db2 subsystem in the z/os environment (DB2 V9 CM for z/os). [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46705@MAIL02.fisalan.nycnet 11553 29 34_Re: Capture dynamic SQL statements14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 17 Mar 2010 21:58:25 +0000652_UTF-8 Have a look at the following presentation at last year's IDUG Conference in Rome:
Session: F01
Snapping the DB2 for z/OS Dynamic Statement Cache
(Daniel L Luksetich)

-- Peter Vanroose,
ABIS Training & Consulting.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]49_3764087736356217.WA.pvanrooseabis.be@www.idug.org 11583 181 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE13_Steve Mallett21_malutjuta@HOTMAIL.COM31_Thu, 18 Mar 2010 09:26:16 +0930351_iso-8859-1 Max,

we're CCSID 37, the CODEPAGE looks OK

We've since discovered that the former PLAN_TABLE was in a EBCDIC TSpace and had column IBM_SERVICE_DATA defined FOR BIT DATA but then was migrated to new PLAN_TABLE in UNICODE TSpace with column accidently NOT defined FOR BIT DATA. That would seem to explain problem detected. [...]43_COL108-W55EE04FAA0E223B908AA93A42C0@phx.gbl 11765 62 34_Re: Capture dynamic SQL statements13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 17 Mar 2010 19:35:27 -0500570_UTF-8 First, enable the dynamic statement cache. When you're ready to capture
your dynamic SQL statements, fire up your IFCIDs 316, 317, and 318 to
make sure you grab all the details, then use the no-charge optimization
Service Center to look at the information. There are also presentations
by Pat Bossman and Terry Purcel that describe the process in more
detail. The books (and some presentations) have lots of information on
what to with the information once you EXPLAIN it to the statement cache
table via the EXPLAIN STATEMENT CACHE ALL. [...]30_4BA1754F.9040004@attglobal.net 11828 42 34_Re: Capture dynamic SQL statements10_Todd Burch17_toddburch@MAC.COM31_Wed, 17 Mar 2010 23:28:28 -0500370_US-ASCII If you want to use the READS interface with IFI, you can grab IFCID
0124, but that is a "point in time" extraction and won't get everything.

A better IFCID (via the READA interface) is IFCID 0350. That will get
the complete SQL statement (perhaps across several records) during
prepare for dynamic SQL and also for static SQL at BIND time. [...]44_17855BB8-A34A-4101-B7FE-E686F911F787@mac.com 11871 244 19_Re: RTS db2 v9 z/os17_Tunen, Marcel van31_Marcel.van-Tunen@CORUSGROUP.COM31_Thu, 18 Mar 2010 08:17:59 +0100461_us-ascii We do both:
Query the tables for current values, find indexen that have not been
used for a while.
and use DSNACCOX to generate (part of) our maintanance.

btw: We found that last used on indexes is not updated when an index is
used in a RI relation, example:
parent child relation with cascade rule. When deleting the parent
probebly the child index is used to delete the child, but last used
stats do not get updated. [...]77_95955CB1A0786744B601CEA40207647F017FF36E@IJMEXCMAIL02.ce.altis.corusgroup.com 12116 163 51_AW: [DB2-L] DB2 for z/OS equivalent for Oracle HINT35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 18 Mar 2010 08:50:28 +0100368_iso-8859-1 Jim

To add one more comment: The best and most convenient option to use hints are the so called CTE-hints. But unfortunately this option is IBM confidential. Maybe you can ask your IBM representative, if you can get a copy of the description. I must admit, I like these CTE-hints and they are far more easier to use, than the official opthints. [...]43_DB2-L%201003180750379211.07A6@IDUGDB2-L.ORG 12280 136 54_Re: Fw: [DB2-L] Moving yourself from DB2 to SQL Server10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 18 Mar 2010 10:00:54 +0100559_US-ASCII Hi Mick

No holy war, I was only asking if a DB2 for z/OS skill could be useful for
using SQL Server. That's all :-)

I was not discussing pros and cons of other RDBMS. What surprised me
talking with people working with SQL Server is they see (not always, this
must to be clear) hardware change as one of the most important tuning
effort. OK a server isn't a mainframe and the costs are by far lower, but
it's something strange for me as I squeezed (maybe) the most from our old
9672 before closing it (for 6 years [...]66_OFA7D406A6.919AAF4C-ONC12576EA.002A7F89-C12576EA.00312AC6@cesve.it 12417 287 40_Re: *bad* IBM_SERVICE_DATA in PLAN_TABLE10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 18 Mar 2010 10:04:12 +0100625_US-ASCII Hi Steve

It sounds reasonable. Thank you for sharing

Max Scarpa

"From this day to the ending of the world,But we in it shall be
remember'd;
We few, we happy few, we band of mainframers;
For he to-day that will sheds his knowledge with me Shall be my fellow
DB2er;"

*********************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
********************************************************************************* [...]66_OFD0996818.9276E113-ONC12576EA.0031BF5D-C12576EA.00317847@cesve.it 12705 113 54_Re: Fw: [DB2-L] Moving yourself from DB2 to SQL Server13_Mick P Graley16_mgraley2@CSC.COM31_Thu, 18 Mar 2010 12:21:18 +0100570_US-ASCII Hi Max,

Yeah I guess I never really answered your original question, but it was
sort of implied.
I think any mainframe DB2 DBA/SysAdm/SysProg would pick up SQL Server very
easily. Or Oracle. And they are the big boys from a job point of view.
I'm currently trying to get into zLinux myself as I don't see my DB2 for
z/OS job existing in 3 years time, that way I can easily jump into the
Wintel or UNIX world if necessary (I'd rather do something completely
different in the outdoors but IT pays too well ;-)
I guess what I'm [...]65_OF977C7F89.0DE9CA7E-ON802576EA.003BF9E0-802576EA.003E604C@csc.com 12819 272 49_Re: Stored Procedure nest level exceeded response13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 18 Mar 2010 09:53:37 -0400584_utf-8 In case anyone else is interested, I finally found the SQLCode:

-724

THE ACTIVATION OF THE object-type OBJECT object-name WOULD EXCEED THE MAXIMUM LEVEL OF INDIRECT SQL CASCADING

Explanation: Cascading of indirect SQL occurs when a trigger, user-defined function or stored procedure invokes another trigger, user-defined function or stored procedure which in turn invokes another. The activation of some of the triggers in this chain might be due to the enforcement of referential constraint delete rules. The depth of this cascading is limited to 16. [...]70_E814208E0983274198870632E8697D5B0E3B51B2@nasa-dtw-ex001.nasa.cpwr.corp 13092 95 34_Re: Capture dynamic SQL statements16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 18 Mar 2010 09:15:32 -0500560_UTF-8 There's also a Feb/March 2010 zJournal article on the subject that may be easier to read. Here is a summary of some ways to capture the dynamic SQL

1) EXPLAIN the statement cache. Very good and easy. I use it constantly, and even do coordinated performance benchmarks using this. You can issue a RUNSTATS REPORT NO UPDATE NONE to flush the cache prior to a test.
2) Use one of you vendor tools to view the dynamic statements. If you have an online monitor there is a very good chance it will allow you to view the cached statements.
3) [...]35_02c701cac6a5$841829c0$8c487d40$@com 13188 333 53_Re: z/OS Session variables - Super duper weirdness...13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 18 Mar 2010 10:26:03 -0400416_utf-8 Hello Roy,

What's IBM's response to the PMR you opened?

Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Wednesday, March 17, 2010 9:59 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] z/OS Session variables - Super duper weirdness...

I have a real nasty problem in DB2 V9 NF...now it could be that I am simply nuts but O did the following [...]70_E814208E0983274198870632E8697D5B0E3B51B3@nasa-dtw-ex001.nasa.cpwr.corp 13522 45 55_Re: AW: [DB2-L] DB2 for z/OS equivalent for Oracle HINT12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 18 Mar 2010 15:15:49 +0000538_ISO-8859-1 Thanks to all who responded. I think I've got the hang of how hints work
now.

Jim McAlpine

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]59_21d1f8c21003180815p33dc3a07m42f854942a89ce2d@mail.gmail.com 13568 127 33_Re: AW: [DB2-L] Interesting Stats16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 18 Mar 2010 11:41:05 -0400508_ISO-8859-1 Jorg,

It would appear that an unbalanced workload may be contributing to the
problem. You mention that "there's no sysplex parm defined." By that, do you
mean that sysplex workload balancing has NOT been enabled at the DB2 Connect
level (or the DB2 JDBC Universal Driver level, if that's what you're using)?
If so, any chance of getting this enabled so that connections pertaining to
the application would be more evenly spread across the data sharing group
members? [...]59_de7260f31003180841w60721962m84e9ca95086adac7@mail.gmail.com 13696 31 33_Re: AW: [DB2-L] Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 18 Mar 2010 16:15:07 +0000566_UTF-8 Robert,

The default setting for WebSphere JDBC to DB2 z/os is to use sysplex routing. We found this out because capacity planning doesn't want adhoc workload on some of the Lpars used for the most critical work. But, even though this workload is sysplex enabled by default it's not every query that is routed. It looks to me that each connection is routed to an available member by WLM. But once that
connection is on, it keeps using that LPAR. So if one LPAR is free when the web connection start most of the connections go there and then with [...]53_5318988613845468.WA.jorghluekebcbsmn.com@www.idug.org 13728 32 21_Re: Interesting Stats10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 18 Mar 2010 16:22:23 +0000619_UTF-8 Joel,

Yes the average i/o time is quite different 2 miliseconds versus 4 miliseconds per i/o. This illustrates the difference between the ideal reporting and monitoring environment and one we can practically maintain. I'd like to see the averge i/o response times, and then any significant processes that deviate greatly from the average. But we have trouble finding the time to just deal with Top 10 tuning iterations. On top of that, this is a BCA application and tablespace and efficient design is not the thought that springs to mind. This table just gets pounded with queries that generally have [...]53_8696129987927414.WA.jorghluekebcbsmn.com@www.idug.org 13761 151 41_AW: [DB2-L] AW: [DB2-L] Interesting Stats12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 18 Mar 2010 18:12:11 +0100522_us-ascii Robert, as far as I can see it the DB2 JDBC Universal Driver is a
monster. And all the configuration features are more than bad. I had a
discussion with IBM about this theme. And all what they told me "it
works as designed". So far my management decided to throw our DB2 for
z/OS appz and all (really all) out and we (me?) of our applications will
move to Oracle::::::: And yes, all these systems have their own goodies,
and they have the all bad things - from a view of an old mainframer. [...]58_99E58D5AC1BE6D4CBF492AF72B942C3D061ADB87@dzstus010.dzbw.de 13913 44 45_Re: AW: [DB2-L] AW: [DB2-L] Interesting Stats14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Thu, 18 Mar 2010 20:01:13 +0100488_ISO-8859-1 I am writing from Stuttgart, Germany, too, just another site,
some kilometers from Georg's apart.

Georg's statement about the situation in Germany is not valid
in general. The situation at our site is different. We stay with DB2,
but we migrate some applications off the mainframe, due to cost reasons.
We go to DB2 on Linux, AIX or Windows with such applications that
are not bound to the mainframe for other reasons (for example,
IMS dialogue). [...]28_4BA27879.6030806@t-online.de 13958 121 24_Re: peoplesoft on z/inux15_Leon Katsnelson15_leon@CA.IBM.COM31_Thu, 18 Mar 2010 13:39:26 -0700464_US-ASCII As you probably know, we do offer DB2 for zLinux. It is fully supported by
IBM and it is the same DB2 that is used by PeopleSoft on other Linux, UNIX
and Windows platforms. SO there is full support for this on the DB2
database side.

I think what you are asking/comparing:
PeopleSoft v?.? is certified with DB2 for zLinux v?.? and you are saying
that PeopelSoft v?.? is certified with Oracle for zLinux. Did I get this
right? [...]68_OFEA20723B.A8FC6128-ON882576EA.00680C03-882576EA.0071794D@ca.ibm.com 14080 310 19_Re: RTS db2 v9 z/os18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Thu, 18 Mar 2010 14:26:18 -0700671_us-ascii Shery,

We use RTS tables to determine which tables need to be re-orged but we don't use DSNACCOX stored procedure. We are on V8 NFM.

Satish

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hepp Shery C
Sent: Tuesday, March 16, 2010 3:19 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] RTS db2 v9 z/os

Greetings list members. I have a question for the shops that are using RTS. We recently converted to V9 and started to use the new RTS tables IBM has generated. We have generally directly queried the tables when researching data changes. I'm wondering how other shops that use [...]55_D42E68651088914B9B327A4EEA8433AC907E48E2@VM251.apsc.com 14391 164 33_Re: AW: [DB2-L] Interesting Stats16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 18 Mar 2010 23:25:44 -0400422_ISO-8859-1 Jorg,

You mention that "even though this workload is sysplex enabled by default
it's not every query that is routed. It looks to me that each connection is
routed to an available member by WLM. But once that connection is on, it
keeps using that LPAR. So if one LPAR is free when the web connection start
most of the connections go there and then with reuse they stay there all
day." [...]58_de7260f31003182025g10bb5824ibd3c0c7df80b4f1@mail.gmail.com 14556 104 76_Java error with the SQL containing UNION after moving to DB2 9.1 for z/OS CM14_Vidya Attuluri27_vidya.attuluri@MARRIOTT.COM31_Fri, 19 Mar 2010 03:47:01 +0000526_UTF-8 Hi,

The below SQL that was executing fine from Java code suddenly gives error in java as follows:

[3/18/10 13:28:00:522 EDT] 0000002f SystemErr R com.marriott.ir.oneyield.dao.security.OYSecurityDAOSysException: Unable to execute sql statement:com.ibm.db2.jcc.b.SqlException: [jcc][10150][10300][3.53.70] Invalid parameter: Unknown column name URA_PROP_CODE. ERRORCODE=-4460, SQLSTATE=null

This is happening after one of our subsystem is upgraded to DB2 9.1 CM. we are using type 2 JDBC driver. [...]59_1978452368520805.WA.vidya.attulurimarriott.com@www.idug.org 14661 72 53_Re: z/OS Session variables - Super duper weirdness...11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 19 Mar 2010 10:53:02 +0100606_UTF-8 You posted

>>Hello Roy,
>>
>> What’s IBM’s response to the PMR you opened?
>>
>>Dave

I cannot open PMRs.....I have to rely on other people or friendly co-DB2
users to do that for me....

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 [...]64_OF2CC78CE8.EDCFBD2F-ONC12576EB.00363906-C12576EB.00364BB4@seg.de 14734 141 80_Re: Java error with the SQL containing UNION after moving to DB2 9.1 for z/OS CM11_Mike Turner19_mike.turner@GMX.COM31_Fri, 19 Mar 2010 09:57:50 -0000617_utf-8 Hi Vidya

That changed in Version 8. To quote from the V8 Install Guide:

"Prior to Version 8, the result column name in a SQLNAME field of the SQLDA
for
a statement involving a UNION reflected the column name or label of the
first
sub-query in the statement. In Version 8, DB2 returns the name or the label
of the
column only if the name or label is the same for that column across all
sub-queries
in the statement. Otherwise, the result column name will be blank.You can
temporarily override this behavior by setting subsystem parameter
UNION_COLNAME_7 to YES." [...]44_C18A4433495040749066655F2CCF7EEB@ToshibaA300 14876 290 53_Re: z/OS Session variables - Super duper weirdness...13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 19 Mar 2010 07:39:08 -0400553_utf-8 Bummer, Roy. Can’t or won’t your service provider open PMRs for you?

Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Friday, March 19, 2010 5:53 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS Session variables - Super duper weirdness...

You posted

>>Hello Roy,
>>
>> What’s IBM’s response to the PMR you opened?
>>
>>Dave

I cannot open PMRs.....I have to rely on other people or friendly co-DB2 users to do that for me.... [...]70_E814208E0983274198870632E8697D5B0E3B51D4@nasa-dtw-ex001.nasa.cpwr.corp 15167 67 53_Re: z/OS Session variables - Super duper weirdness...11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 19 Mar 2010 13:17:43 +0100572_UTF-8 <
<
Nope, its worse...we're an ISV partner developer and so are not able to
open PMRs on GA software....

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 [...]64_OFA18BEFD7.2B9A7D36-ONC12576EB.00437A89-C12576EB.00438A98@seg.de 15235 149 80_Re: Java error with the SQL containing UNION after moving to DB2 9.1 for z/OS CM10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 19 Mar 2010 08:40:10 -0400392_US-ASCII Seems like it should work, but since it does not . . .

Are the definitions of the 2 columns still identical or might one have
been changed, deliberately or inadvertently? Also, have you tried using
'AS ***' to give the columns the same name in the 3 select statements
comprising the union?

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492 [...]62_1A700EEF49343148A08879B1E3CA5BA6143B9C17@NSTMC101PEX1.ubsw.net 15385 151 80_Re: Java error with the SQL containing UNION after moving to DB2 9.1 for z/OS CM18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Fri, 19 Mar 2010 09:44:36 -0700444_us-ascii We came across an issue that was a similar one when the JDBC drivers were changed. Was there any change in the drivers that were used? - Just a thought.

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Vidya Attuluri
Sent: Thursday, March 18, 2010 8:47 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Java error with the SQL containing UNION after moving to DB2 9.1 for z/OS CM [...]55_D42E68651088914B9B327A4EEA8433AC907E48F1@VM251.apsc.com 15537 247 53_[JOB] DB2 for z/OS with ITIL Documentation Experience7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Fri, 19 Mar 2010 13:17:38 -0400440_us-ascii I am looking for someone to staff a one-year contract position in the
Columbia, MD area with DB2 for z/OS skills AND process documentation and
project management skills. The customer needs a consultant who will
research, interview and gather information on internal processes and deliver
detailed documentation using an ITIL process that follows industry best
practices, as well as provide ongoing DB2 support. [...]41_4ba3b1bc.9553f10a.5e46.40be@mx.google.com 15785 171 37_UNLOAD - Increase of temporary space?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Fri, 19 Mar 2010 15:17:54 -0400694_ISO-8859-1 Hello:

I ran a simple unload job and it failed with a -904 reason.
When I dug a little deep into it, I figured it's got to do with lack of
temporary space.

********************************** TOP OF DATA
**********************************
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT505I DSNTIAUL OPTIONS USED: SQL
DSNT506I INPUT STATEMENT WAS NOT A FULL SELECT ON A SINGLE TABLE. LOAD
STATEMENT
DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 482
DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 23136
DSNT493I SQL ERROR DURING SQL STATEMENT OPEN , TABLE TBLNAME
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED [...]59_85726c9d1003191217h6603f887o66c6a8663bdfb1ce@mail.gmail.com 15957 183 41_Re: UNLOAD - Increase of temporary space?0_24_carol.sutfin@REGIONS.COM31_Fri, 19 Mar 2010 15:44:07 -0500516_ISO-8859-1 It says so right in the message.

You have run out of 4K work space.

Why don't you try running a UNLOAD using DSNUTILB instead of DSNTIAUL.

It will run faster and may not require all those resources.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
carol.sutfin@regions.com

From: DB2 DBA

To: DB2-L@IDUGDB2-L.ORG

Date: 03/19/2010 02:31 PM

Subject: [DB2-L] UNLOAD - Increase of temporary space? [...]71_OFF2F4917B.C232F9DC-ON862576EB.0071B180-862576EB.0071E718@corp.rgbk.com 16141 155 41_Re: UNLOAD - Increase of temporary space?10_Dave Nance16_dav1mo@YAHOO.COM31_Fri, 19 Mar 2010 14:37:10 -0700609_iso-8859-1    It would seem that you have a fairly small sort work area. Is there an order by or due to something in the query a sort is being performed. Also, is one of the columns you are selecting very long?   David Nance   ________________________________ From: DB2 DBA To: DB2-L@IDUGDB2-L.ORG Sent: Fri, March 19, 2010 3:17:54 PM Subject: [DB2-L] UNLOAD - Increase of temporary space? Hello: I ran a simple unload job and it failed with a -904 reason. When I dug a little deep into it, I figured it's got to do with lack of temporary space. ********************************* [...]43_868815.89950.qm@web57802.mail.re3.yahoo.com 16297 54 41_Re: UNLOAD - Increase of temporary space?13_John Saunders27_john_j_saunders@YAHOO.CO.UK31_Sat, 20 Mar 2010 09:54:21 +0000493_UTF-8 I suspect the SQL statement you quoted isn't the actual one being used ;-)
The TIAUL output gives us some clues -
1) there is more than one table involved
2) the output record length is 482 bytes (so we don't have to worry about huge columns)

The need for temporary work space implies a SORT - either an access path choice or because of an SQL construct (ORDER BY, GROUP BY, DISTINCT, etc). So maybe the unload is from a view containing extra SQL you aren't seeing? [...]57_0708882703309722.WA.johnjsaundersyahoo.co.uk@www.idug.org 16352 520 41_Re: UNLOAD - Increase of temporary space?15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Sat, 20 Mar 2010 15:49:42 -0400511_us-ascii If you look at the install guide step 15 JCL member DSNTIJTM is used to
define temp 4K space.

You can use the JCL in this member to DELETE/DEFINE or add new 4K work
files.

Troy Coleman
CA
Principal Product Manager

troy.coleman@ca.com
Tel: +1-630-505-6000 X46025

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2 DBA
Sent: Friday, March 19, 2010 2:18 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] UNLOAD - Increase of temporary space? [...]56_F9576E62032243419E097FED5F0E75F308437B26@USILMS12.ca.com 16873 47 28_STOGROUP Reached Max Volumes4_Anil21_alisha_kale@YAHOO.COM31_Sun, 21 Mar 2010 14:51:11 +0000602_UTF-8 Hi All,

In DB2 for z/os land, you could have a maximum number of 133 volumes in a storage group.

I was curious to find out what are the various ways me fellow DBAs are addressing this condition:

Lets say we have a SG called DBASG001
Some options that come to mind are:
1. Reclasify the objects by some criteria (application/database name/functionality) and assign them a seperate storage group, say DBASG002.
2. Add a new storage group DBASG002 for new objects and spin off of some large objectes from DBASG001 to DBASG002.
3. Don't wait till you get to 133 [...]52_1715092587900826.WA.alishakaleyahoo.com@www.idug.org 16921 143 32_Re: STOGROUP Reached Max Volumes11_Nick Cianci19_ncianci@AU1.IBM.COM31_Mon, 22 Mar 2010 09:29:19 +1100588_ISO-8859-1 Anil
we use SMS, and let the storage group manage it, defining the
STOGROUP as VOLUMES(*). We currently have a mix of volume types but number
around 460 volumes in our pool (managed by SMS) that is used by our
stogroup.

If you want to manage the volumes independently of SMS, then yes you'll
probably need to defined multiple STOGROUPS and decide which you want to
allocate you DB2 files. In a prior Pre-SMS DB2 v2.3 lifetime that's what
we did, in fact we had one volume (+ an overflow if I remember correctly)
per stogroup. But DASD was a [...]69_OFDCF1BABE.A7368DCF-ONCA2576ED.007A79CB-CA2576ED.007B88D5@au1.ibm.com