1 IDUGDB2-L.ORG /home/listserv/home/db2-l September 2010, week 1 2 31 13_DB2 questions4_Anil21_alisha_kale@YAHOO.COM30_Wed, 1 Sep 2010 01:12:32 -0400733_UTF-8 Hi All !

I seem to recall that IBM used to have a site wherein you could ask "how to" questions about using db2 features. Any idea if that ssrvice still exists ? if yes, would you share more details about to go about asking these "how to " questions.

Thanks.

Anil

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* 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 [...]52_3103196562178841.WA.alishakaleyahoo.com@www.idug.org 34 45 9_RO Status4_Anil21_alisha_kale@YAHOO.COM30_Wed, 1 Sep 2010 01:28:56 -0400653_UTF-8 Env: DB2 V9 for z/os

Hi All !

How would you track when did a tablespace go into RO status ?

Case:
Lets say,
1. A batch job fails. DBA sees the message "unavailable resource"
Reason code: "00C90080", type of resource: "00000200", and resource name: "dbname1.tsname1". SQLSTATE=57011
2. 00C90080
Explanation: An attempt was made to allocate a resource for update operations. However, the resource was already started for read-only access.
3. you check tablespace "dbname1.tsname1" status
4. dbname1.tsname1 is in RW
5. You want to track down what had put dbname1.tsname1 in RO, and then back to RW. [...]52_8013682788620606.WA.alishakaleyahoo.com@www.idug.org 80 55 13_Re: RO Status10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 1 Sep 2010 05:56:33 -0400402_UTF-8 If you don't know absolutely nothing about your environment and you've no idea about what and when someone put that TS in RO (A command, a job) you can execute a batch or a REXX executing a -DIS command USE at given intervals (via a scheduler of course) just to narrow the window where the event happened. After you have a vague idea about that interval you can analyze the problem better . [...]48_8253110902123336.WA.mscarpacesve.it@www.idug.org 136 144 13_Re: RO Status9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK30_Wed, 1 Sep 2010 11:22:43 +0000638_iso-8859-1 If you have DB2 Query Monitor, you can look at DB2 Command Activity (option 4)

Regards

Chris

> Date: Wed, 1 Sep 2010 01:28:56 -0400
> From: alisha_kale@YAHOO.COM
> Subject: [DB2-L] RO Status
> To: DB2-L@IDUGDB2-L.ORG
>
> Env: DB2 V9 for z/os
>
> Hi All !
>
> How would you track when did a tablespace go into RO status ?
>
> Case:
> Lets say,
> 1. A batch job fails. DBA sees the message "unavailable resource"
> Reason code: "00C90080", type of resource: "00000200", and resource name: "dbname1.tsname1". SQLSTATE=57011
> 2. 00C90080
> Explanation: An [...]43_COL121-W34DC55E28E816BFB2733EFA68B0@phx.gbl 281 89 13_Re: RO Status13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Wed, 1 Sep 2010 08:35:52 -0500787_utf-8 Version 9 of DB2 added a TRACE RECORD in the log which contains the actual command syntax and context information about who/what executed it. You can print off the TYPE(0010) - the SYSTEM EVENT - log records and then look for your specific START/STOP. Here's an example:

00B78254091F LRSN(C683FDFEC1CF) TYPE(SYSTEM EVENT)
SUBTYPE(TRACE RECORD)
*LRH* 01720069 00100041 10800000 00000000 00000000 00000726 00000000 0000C683
FDFEC1CF 0000
0000 014C0000 0000004C 01000001 00000014 00380001 00304060 E2E3D6D7 40C4C1E3
0020 C1C2C1E2 C54DE6E4 D9D9C7C8 E5C25D40 E2D7C1C3 C5D5C1D4 4DD3C1D9 E7F0F0F1
0040 C15D4040 1F310410 00000000 00560117 005A02A1 16E2D1C0 C4C5C6C6 C683FDFE
0060 C1C86A00 0000600C 0000600C 00000144 C4C5C6C6 40404040 40404040 40404040
[...]69_476996CBBE9AF14285E09E63C370072A148D749623@PHXCCRPRD01.adprod.bmc.com 371 42 19_Virginia DOT outage14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Wed, 1 Sep 2010 14:03:15 -0400413_UTF-8 As many of you may know the State of Virginia has been dealing with a major computer system outage impacting 24 departments.
The outage has extended over 7 business days with no estimated time for repair.
Most of the press that I have seen discusses driver license renewals being down to the extent that police are not sighting people who are stopped and found to have recently expired licenses. [...]56_0374264937774405.WA.ibmsysproggeeksites.com@www.idug.org 414 47 17_Re: DB2 questions13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Wed, 1 Sep 2010 12:07:51 -0700417_utf-8 We use the "ask question" feature of IBMLink, it is just like opening an ETR except you can ask non-defect related questions. However, you have to subscribe to that service and it costs us beaucoup $.

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil
Sent: Tuesday, August 31, 2010 10:13 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DB2 questions [...]64_927FE8790B320742927BCF9ADF7AE48A19A5AD7250@PDXMAIL.pacificorp.us 462 66 23_Re: Virginia DOT outage13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 1 Sep 2010 15:49:13 -0400491_utf-8 If prior experiences of similar long-running outages are anything to go by, we may probably never know

Snag is, there will be two things going on

a) Fixing the problem
b) Determining whose responsibility it was and what level (if any) of compensation is payable

However, so far as the z/OS angle goes, the article does say "State officials said the outage affected only 485 of the state's 4,800 data servers" which, to me, doesn't sound mainframe related. [...]60_4440F5DA00E3F3459BBCB97431B91B6612B909DFB6@MAILR004.mail.lan 529 33 28_XML for Db2 v8.1 on z/Os 1.714_Sherry Simmerl20_ssimmerl@CSIHOME.COM30_Wed, 1 Sep 2010 16:52:36 -0400403_UTF-8 I am tasked with setting up XML for Db2 v8.1 and came across in Admin manual that "All the MQ XML stored procedures have been deprecated". Quite confusing since deprecated as far as I could find means superseded and should be avoided.
Reading several manuals to try to understand the sample jobs. OSXML Extender Administration and Programming and Red book on Stored Procedures and beyond. [...]52_9067693679048029.WA.ssimmerlcsihome.com@www.idug.org 563 62 32_Re: XML for Db2 v8.1 on z/Os 1.713_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 1 Sep 2010 17:16:00 -0400609_us-ascii Sherry

I know this doesn't really answer your questions, but how far in your future is DB2 9?

That's really the way forward for DB2 and XML......

Phil G

Sent from my iPod, so typing is even worse than usual

On 1 Sep 2010, at 21:53, Sherry Simmerl wrote:

> I am tasked with setting up XML for Db2 v8.1 and came across in Admin manual that "All the MQ XML stored procedures have been deprecated". Quite confusing since deprecated as far as I could find means superseded and should be avoided.
> Reading several manuals to try to [...]49_EFC82920-50A7-45EE-9D09-BD0C3EAA4B15@cogito.co.uk 626 163 68_Re: V7 DSNLIENO SOCKET=CONNECT RETURN CODE=1128 REASON CODE=7663029116_Robert Catterall21_rfcatterall@GMAIL.COM30_Wed, 1 Sep 2010 21:26:35 -0400728_ISO-8859-1 For what it's worth, in the z/OS 1.9 UNIX System Services Codes manual the
description of return code 1128 is "The attempt to connect was rejected"
(see
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/BPXZA880/3.0?SHELF=EZ2ZO10K&DT=20070606153306).
UNIX System Services is involved with TCP/IP communication on the z/OS
platform.

Robert

On Tue, Aug 17, 2010 at 3:23 AM, Steve wrote:

> Listers,
>
> May take you back a couple of versions but I have a DB2 V7 for z/OS 1.9 DDF
> task that aborts occasionally, last time seemed to begin with following
> combination (& especially the message with RC=1128 which I can't get a hit
[...]60_AANLkTi=v8bsRahswJeVjYxO639xjYJddqsiSAHSyWqOu@mail.gmail.com 790 85 23_Re: Virginia DOT outage35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Wed, 1 Sep 2010 22:11:43 -0400869_UTF-8 the below article was 10 months ago.

http://spectrum.ieee.org/riskfactor/computing/it/virginia-information-technologies-agency-believes-in-the-perfect-network-fairy

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

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Avram Friedman"
Newsgroups: bit.listserv.db2-l
To:
Sent: Wednesday, September 01, 2010 2:03 PM
Subject: [DB2-L] Virginia DOT outage [...]46_74AF5908CF374FD8BC034DC6DDE4263F@DellNotebook3 876 198 23_Re: Virginia DOT outage0_17_jtonchick@AOL.COM30_Thu, 2 Sep 2010 00:41:58 -0400587_us-ascii "State officials said the outage affected only 485 of the state's 4,800 data servers but that fixes have taken longer than they expected." Doesn't sound like a DB2 for z/OS shop (aka. mainframe) to me.

The other thing to take note of is that the VA DMV has outsourced its IT. What does the contract say about disaster recovery? They obviously did not stipulate a threshold of outage that would trigger a disaster recovery event, or a specified time for the restore to be complete. This is an election year, so I think some Virginia elected officials might be filing [...]52_8CD18704F3B30D5-DB8-66CD@Webmail-m123.sysops.aol.com 1075 81 32_Re: XML for Db2 v8.1 on z/Os 1.713_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 2 Sep 2010 07:57:48 -0400472_utf-8 Sherry

A bit more information on my somewhat terse statement last night about waiting (if you can) for DB2 9

Putting it very simply, there are really three options to storing XML data in DB2 on z/OS

1. Just stuff the documents (technical phrase) in CLOB columns
2. Deconstruct the documents and store the deconstructed results in relational tables and columns
3. Store the documents AS documents in a XML column in DB2 9 (and beyond) [...]60_4440F5DA00E3F3459BBCB97431B91B6612B909DFEB@MAILR004.mail.lan 1157 153 32_Re: XML for Db2 v8.1 on z/Os 1.716_Daniel Luksetich18_danl@DB2EXPERT.COM30_Thu, 2 Sep 2010 08:09:25 -0500550_UTF-8 I'd like to comment on (b) in Phil's remarks below. In DB2 for z/OS V8 it is much easier to make an XML document from relational data then to make relational from XML. There are several built-in functions that allow this to happen.

Most people who store XML in DB2 end of building a dictionary to document and parse the documents. Then they don't think of the fact that their metadata has to be cataloged and backed up someplace. That stuff is already built into DB2. Also, there are performance trade-offs when storing XML in DB2. [...]35_008201cb4aa0$12a38440$37ea8cc0$@com 1311 168 45_SQL Question on replacing alphanumeric values18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM30_Thu, 2 Sep 2010 10:05:44 -0700604_us-ascii Dear Esteemed Listers:

Environment:
z/OS - V 1.10
DB2 - V 9.1 CM

Question:

In our database, we do have a field that has alphanumeric values and numeric values mixed in it. The requirement is to eliminate all the alphanumeric values from these fields. We tried to look for different functions in available in DB2 like REPLACE, TRANSLATE and OVERLAY. All these talk about strings to be used. But, in our requirement we only know that the values that we need to eliminate are alphanumeric values, we do not know the strings to be used in these functions.
Can you [...]55_D42E68651088914B9B327A4EEA8433ACB0AEF8D1@VM251.apsc.com 1480 207 49_Re: SQL Question on replacing alphanumeric values14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Thu, 2 Sep 2010 13:23:10 -0400592_us-ascii Satish,

I'm not sure you have completely stated your requirement - what should the alphas become?
It sounds like you need to translate the numbers back to themselves and the alphas to something
- for blanks, this would be
Update Your_ table set Your_column =
TRANSLATE (Your_column, '1234567890 ' _26 spaces_ , '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ')
For zeros:
TRANSLATE (Your_column, '1234567890000000000000000000000000000' _27 zeros at the end_ , '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Where your_column like '%A%' or your_column like '%B%' etc. [...]75_0BF6D36E311EAB4BA3F891660E254C363CAEEA533A@EMARC124VS01.exchad.jpmchase.net 1688 667 49_Re: SQL Question on replacing alphanumeric values18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM30_Thu, 2 Sep 2010 10:38:42 -0700498_us-ascii Mark,

The field in question is declared as 'X(05)'. Yes, you are correct. The numbers should be as they are and alphanumeric values need to be replaced with nulls. The field that we have got has values like 'X288', 'BOB', ']' etc.

In the first example, we need to get rid of just 'X'. The output should be '288'.
In the second example, we need to get rid of 'BOB'. The output should be ''.
In the third example, we need to get rid of ']'. The output should be ''. [...]55_D42E68651088914B9B327A4EEA8433ACB0AEF937@VM251.apsc.com 2356 303 49_Re: SQL Question on replacing alphanumeric values14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Thu, 2 Sep 2010 13:57:19 -0400698_us-ascii Your update statement will be of the form:
Update Your_table set your_column =
Case when substr(your_column,1,1) = '1' then '1'
when substr(your_column,1,1) = '2' then '2'
when substr(your_column,1,1) = '3' then '3'
when substr(your_column,1,1) = '4' then '4'
etc. to '0'
else '' _empty string_
end concat
Case when substr(your_column,2,1) = '1' then '1'
when substr(your_column,2,1) = '2' then '2'
etc. to '0'
else ''
end concat
Case when substr(your_column,3,1) = '1' then '1'
when substr(your_column,3,1) = '2' then '2'
etc. to '0'
else ''
end concat
up to Case when substr(your_column,5,1) = '1' then '1' etc .
No where [...]75_0BF6D36E311EAB4BA3F891660E254C363CAEEA5489@EMARC124VS01.exchad.jpmchase.net 2660 724 49_Re: SQL Question on replacing alphanumeric values18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM30_Thu, 2 Sep 2010 11:43:30 -0700733_us-ascii Mark,

I tried this logic, and this worked. Thank you very much providing this solution as I didn't how to proceed with this.

Thanks & Regards,
Satish Srikakulapu

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Doyle, Mark M2
Sent: Thursday, September 02, 2010 10:57 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] SQL Question on replacing alphanumeric values

Your update statement will be of the form:
Update Your_table set your_column =
Case when substr(your_column,1,1) = '1' then '1'
when substr(your_column,1,1) = '2' then '2'
when substr(your_column,1,1) = '3' then '3'
when substr(your_column,1,1) = '4' then '4'
etc. to '0'
else '' _empty [...]55_D42E68651088914B9B327A4EEA8433ACB0AEF9E1@VM251.apsc.com 3385 43 38_Empty result set from stored procedure11_Gary Snider29_gsnider@CENTRAL-INSURANCE.COM30_Thu, 2 Sep 2010 14:54:32 -0400734_UTF-8 Our developers have an application (stored procedure) which appears to have started working differently after migrating to V9. The application process is as follows:
----------------------------------------
Declare cursor a with return
Open cursor a
Fetch
Close cursor a
If data was returned from Fetch above
Open cursor a again
Return without closing
else
do other processing before return
----------------------------------------
After we migrated to V9, the .NET application calling the SP (via DB2 Connect Server) would accasionally receive an empty result set (SQL0100W) even though the fetch from the first open cursor returned data. The problem has been circumvented by using two [...]60_1890733884106909.WA.gsnidercentralinsurance.com@www.idug.org 3429 41 41_DRDA PERFORMANCE IMPROVEMENT USING TCP/IP7_Ed Long19_rdhm99a@PRODIGY.NET30_Thu, 2 Sep 2010 12:48:42 -0700798_us-ascii Just found these two PTF's that resolve APAR PM12256.
They up the percentage of TCP/IP DRDA traffic eligible for ZIIP processing to 60%.
The 60% figure is in the APAR cover letter.
The PTF's are UK56724 and UK56725.

Edward Long

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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 80% off when you both come to the conference!
_____________________________________________________________________42_121781.7881.qm@web80206.mail.mud.yahoo.com 3471 81 45_Re: DRDA PERFORMANCE IMPROVEMENT USING TCP/IP15_Cristian Molaro18_cristian@MOLARO.BE30_Thu, 2 Sep 2010 16:34:09 -0400530_- Hello,

We have these PTFs applied in several production DB2s (V8NFM) including one running in DS since more than a month.
Indeed, PM12256 says:

This APAR changes the method used by DB2 for z/OS to control
the portion of SQL request workloads via DRDA over TCP/IP
connections ("DRDA workloads") that are authorized to be
diverted to available zIIP specialty engine(s). With this
APAR, IBM has also increased the portion of such DRDA
workloads that is authorized to run on a zIIP to up to 60% [...]50_1241319101059210.WA.cristianmolaro.be@www.idug.org 3553 33 68_Re: V7 DSNLIENO SOCKET=CONNECT RETURN CODE=1128 REASON CODE=766302915_Steve21_malutjuta@HOTMAIL.COM30_Thu, 2 Sep 2010 20:52:37 -0400783_UTF-8 Thanks Rob,

That might prove very helpful .

Now that you point me towards USS it seems so obvious a place to have been looking in the first place.

Let you know if we find anything

regards,
Steve

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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 80% off when you both come to the conference!
_____________________________________________________________________ [...]53_6740155517328561.WA.malutjutahotmail.com@www.idug.org 3587 39 36_TBRUG reminder notice September 17th14_Nelia Gonzalez28_nelia.gonzalez@SYNIVERSE.COM30_Thu, 2 Sep 2010 23:00:57 -0400499_UTF-8 Greetings,

This is a friendly reminder of our upcoming Vendor Tools Fair presentations on Friday, September 17.

This event is free for members (includes breakfast/lunch). We strongly encourage you to register by Monday, Sept 13th, to ensure adequate food and beverages for all attendees. You can send email to tbrugbod@gmail.com to confirm your registration. Please visit our website at www.tbrug.com for meeting announcements, presentation notes, downloads, and much more. [...]60_3834195052173087.WA.nelia.gonzalezsyniverse.com@www.idug.org 3627 318 64_Re: z/OS db2 v9 - now select from insert from a recursive select37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Fri, 3 Sep 2010 08:06:21 +0200646_iso-8859-2 Hi all
I am now able (with your help) to do insert from a recursive select, but what about Select from insert from a recursive select?
I have tried
SELECT NM FROM FINAL TABLE (
INSERT INTO SPTOW -- this insert works OK
WITH P (NM, ID, STRYNG, LEV) AS
(SELECT 0,USERID,USERID,0 FROM DBZ0.CS07.SPTOQ WHERE AUTHORITY=0
UNION ALL
SELECT S.AUTHORITY, S.USERID, P.STRYNG || S.USERID, P.LEV+1
FROM P, DBZ0.CS07.SPTOQ S
WHERE NM+1=AUTHORITY AND P.LEV<10
)
SELECT *
FROM P
WHERE NM < 5
);
DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM
CLAUSE IS NOT ALLOWED [...]58_0675446363E09A4EBE4CCF8D3AD7814F0454816A@MAIL1.cen.csin.cz 3946 89 59_AUTO: Jim Rohal is out of the office (returning 09/07/2010)0_24_Jim.Rohal@HUNTINGTON.COM30_Fri, 3 Sep 2010 04:00:56 -0400334_us-ascii I am out of the office until 09/07/2010.

I will reply to your message when I return. I will have not have any
access to email during this time.

Note: This is an automated response to your message "Re: [DB2-L] z/OS db2
v9 - now select from insert from a recursive select" sent on 9/3/2010
2:06:21. [...]69_OF43D2F5BA.6E98730A-ON85257793.002C080C-85257793.002C080C@LocalDomain 4036 527 64_Re: z/OS db2 v9 - now select from insert from a recursive select13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Fri, 3 Sep 2010 04:10:15 -0400701_iso-8859-2 Hi Dusan

Unfortunately, as you've discovered, currently there is a limit to how "deep" you can nest these types of SQL

Which is a pity
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org/emea

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Pospí¹il Du¹an
Sent: 03 September 2010 07:06
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS db2 v9 - now select from insert from a recursive select [...]60_4440F5DA00E3F3459BBCB97431B91B6612B909E0B3@MAILR004.mail.lan 4564 24 37_Alison Pelletier is out of the office16_Alison Pelletier28_alisonpelletier@DISCOVER.COM30_Fri, 3 Sep 2010 04:00:03 -0500579_US-ASCII I will be out of the office starting 09/02/2010 and will not return until
09/13/2010.

Please consider the environment before printing this email.

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* 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
_____________________________________________________________________ [...]70_OF4D5AA552.0601E605-ON86257793.00317162-86257793.00317165@discover.com 4589 607 64_Re: z/OS db2 v9 - now select from insert from a recursive select37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Fri, 3 Sep 2010 11:04:24 +0200473_iso-8859-2 Thank you for your answer
Regards,
dusan

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Friday, September 03, 2010 10:10 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS db2 v9 - now select from insert from a recursive select

Hi Dusan

Unfortunately, as you've discovered, currently there is a limit to how "deep" you can nest these types of SQL [...]58_0675446363E09A4EBE4CCF8D3AD7814F0454816D@MAIL1.cen.csin.cz 5197 424 53_Re: AW: [DB2-L] * Please Read * IDUG Survey and DB2-L17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM30_Fri, 3 Sep 2010 12:11:20 +0100616_US-ASCII Max,

to your comment "managing even a simple list needs people,time and
resource$ (there's no free lunch) to avoid, for example, site hacking.
We can have these resources now, but this doesn't mean we'll have them
forever and moving all DB2 informations on different places on the Net
could be a 'cheap' if not mandatory choice in the future" all I can
reply is exactly what Cathy pointed out: IDUG are only hosting DB2-L,
which is independent of IDUG, so if IDUG intend to stop paying all these
no-doubt megadollars for hosting the LISTSERV, then we simply need to
take [...]56_3AA3218BB9554144A37CE24F8EE985350326D850@UKSLMS11.ca.com 5622 434 47_[AD] Moderating opportunities at IDUG EMEA 201013_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Fri, 3 Sep 2010 12:56:40 +0100482_US-ASCII I can't believe Friday has come around again so soon, but that means it's
time for one of Phils IDUG EMEA "what to do?" messages - this week I'm going
to focus on technical session moderators

IDUG moderators are possibly the most important people at IDUG after our
speakers

Moderator duties include :

. Reminding the speaker to schedule time at the end of the
presentation for a question and answer period and to repeat any questions. [...]43_005e01cb4b5f$12599c30$370cd490$@demon.co.uk 6057 94 23_Re: Virginia DOT outage14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Fri, 3 Sep 2010 10:18:09 -0400288_UTF-8 Actually it seems to be that this is very much a mainframe related issue that can be taken in a posistive or negative way

Most DOT systems are mainframe IMS and or DB2 (IMS is a bit more common I think as these systems were computer based long before DB2 was invented). [...]56_5671971226439452.WA.ibmsysproggeeksites.com@www.idug.org 6152 1449 71_Re: New England DB2 Users Group - Tools Fair - September Meeting Notice13_George Toolan19_gtoolan@COMCAST.NET30_Fri, 3 Sep 2010 10:18:50 -0400415_us-ascii September 16, 2010 - Meeting Notice

Annual Tools Fair

The next meeting of the New England DB2 User's group will occur on Thursday,
September 16, 2010 in the Page and Crafts rooms at the Publick House in
Sturbridge Mass. We will start at 0900 and wrap up by 4:30 pm.

Please pre-register by going to the web site NEDB2UG.ORG
and click on the rotating [...]35_008c01cb4b72$eec82480$cc586d80$@net 7602 69 23_Re: Virginia DOT outage9_Ken Hynes21_ken.hynes@ACS-INC.COM30_Fri, 3 Sep 2010 10:35:28 -0400611_UTF-8 Hi,

I live in Virginia and have followed the VITA outage with some interest. I have worked for an outsourcer myself for a number of years so I have more than a passing interest in this kind of thing. Basically little real information has been produced other than the usual CYA. If your up for a "laugh" watch VITA Director Sam Nixon "not" answer anything about the outage -- http://www.youtube.com/watch?v=eqSf5RNebuE. You would be as well advised to watch Charles Durning do his "Dance a Little Sidestep" -- at least it's entertaining -- see it at http://www.youtube.com/watch?v=rCDoBvG1HoI. [...]52_8147197220574271.WA.ken.hynesacsinc.com@www.idug.org 7672 82 23_Re: Virginia DOT outage13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Fri, 3 Sep 2010 10:00:25 -0500562_UTF-8 Has there been any mention of platforms or any software in any of the
press releases so far? The news stories I have been able to find make
no mention.

If there are, would someone please share links.

Willie

On 9/3/2010 9:18 AM, Avram Friedman wrote:
> Actually it seems to be that this is very much a mainframe related issue that can be taken in a posistive or negative way
>
> Most DOT systems are mainframe IMS and or DB2 (IMS is a bit more common I think as these systems were computer based long before DB2 was [...]30_4C810D89.7070004@attglobal.net 7755 25 21_Tmon for DB2 Question4_Bill21_db2_dba@BELLSOUTH.NET30_Fri, 3 Sep 2010 11:16:28 -0400431_UTF-8 I recently started working on a new client and their monitor of choice in the DB2 environment is Tmon. I am trying to set the exception monitor to capture server SQL that exceeds 1 minute. Does anyone have any experience linking the exception monitor with the SQL Analyzer portion of the tool and capturing the offending SQL? The documentation refers to the process, but not real helpful when it comes to the specifics. [...]52_4269137555360238.WA.db2dbabellsouth.net@www.idug.org 7781 108 23_Re: Virginia DOT outage12_Phil Gunning19_pkgunning@GMAIL.COM30_Fri, 3 Sep 2010 11:23:08 -0400510_UTF-8 Thanks Ken that’s interesting. It would be really good to know if there was a database failure and if it was Oracle....either way, SAN or no SAN they should have been able to "failover" to the DR site , you know using their "tested" plan... Uh, we all know nowadays that recovery and DR usually get the short end of the stick or lip service at the leaderless IT management level. Now there are some exceptions but I see this all the time. Now, kablooey is funny though! [...]35_003001cb4b7b$ea6a3280$bf3e9780$@com 7890 153 23_Re: Virginia DOT outage14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Fri, 3 Sep 2010 11:31:03 -04000_56_1774692814389451.WA.ibmsysproggeeksites.com@www.idug.org 8044 150 23_Re: Virginia DOT outage9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 3 Sep 2010 11:02:13 -0500524_US-ASCII Lots of discussion over on IBM-main - including links to local papers. For
which we can thank Avram since he posted the first links.

Net conclusion so far - never had a DR plan - never had a DR exercise -
wasn't in the contract so ignore.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Willie Favero
Sent: Friday, September 03, 2010 10:00 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Virginia DOT outage [...]43_A6200EBED8F44229B37FED464948DBE0@mikelaptop 8195 328 23_Re: Virginia DOT outage13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Fri, 3 Sep 2010 12:06:31 -05001052_UTF-8 Thanks Avram

The article at
http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2005-Enterprise-Edition/Virginia-Department-of-Motor-Vehicles/Virginia-DMV-Enhances-Decisions-Boosts-Safety-Through-Integration-with-Other-Agencies/4000004307

is a good read... thanks for sharing...

Willie

On 9/3/2010 10:31 AM, Avram Friedman wrote:
> saThe failed application is Microsoft SQL server. The failed component
> is an EMC disk array supporting the Microsoft environment. The
> Microsoft SQL environment was built by cloning . warehousing several
> mainframe DB2 and IMS systems. Here is a Microsoft article from a year
> ago that discusses what a good job and big need they satisified by
> getting off those nasty mainframe data bases
> http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2005-Enterprise-Edition/Virginia-Department-of-Motor-Vehicles/Virginia-DMV-Enhances-Decisions-Boosts-Safety-Through-Integration-with-Other-Agencies/4000004307
> Av On Fri, 3 Sep 2010 10:00:25 [...]30_4C812B17.9050701@attglobal.net 8524 43 34_Re: Best IBM url for APAR searches17_Katherine A Wheat17_kwheat@US.IBM.COM30_Fri, 3 Sep 2010 17:17:31 -0400471_UTF-8 I'm a little late here in my response, but I just wanted to add a few other options for searching APARs...

You can search from the DB2 for z/OS support page (http://www.ibm.com/support/entry/portal/Overview/Software/Information_Management/DB2_for_z~OS):
1. In the list of "Choose your task" options, click Troubleshooting.
2. Under "Featured troubleshooting links," click View all APARs.
From there, you can search or browse through all APARs. [...]49_8724378130571046.WA.kwheatus.ibm.com@www.idug.org 8568 106 46_Utility IDs for Dynamic Runstats in Peoplesoft0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 3 Sep 2010 14:52:32 -0700934_US-ASCII Can someone tell me how Peoplesoft generates the utility id for dynamic
runstats in DB2 z/OS? I thought I heard that it is time-stamp based.

Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com
Email. barbara.j.nigh@us.hsbc.com
Internet. http://www.hsbc.com
______________________________________________________

-----------------------------------------
******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it [...]74_OF44539407.C1DACCD8-ON88257793.0078045D-88257793.00782AEA@smtpgate.us.hsbc 8675 27 61_AUTO: Truls Pedersen is out of office. (returning 13.09.2010)14_Truls Pedersen16_tpeders6@CSC.COM30_Sat, 4 Sep 2010 10:04:58 +0100703_US-ASCII I am out of the office until 13.09.2010.

Note: This is an automated response to your message "DB2-L Digest - 3 Sep
2010 to 4 Sep 2010 (#2010-240)" sent on 4/9/10 6:00:00.

This is the only notification you will receive while this person is away.

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]65_OFD872A095.DB7558DD-ON80257794.0031E502-80257794.0031E502@csc.com 8703 455 64_Re: z/OS db2 v9 - now select from insert from a recursive select14_Peter Backlund21_BacklundDB2@TELIA.COM30_Sat, 4 Sep 2010 16:17:16 +02000_26_4C8254EC.5060600@telia.com 9159 256 66_Re: New for IDUG EMEA 2010 - IBM Hands-on Labs - And They're Free!13_Laila hosaini19_lshosaini@YAHOO.COM30_Sun, 5 Sep 2010 00:12:01 -0700724_iso-8859-1 Hi Dear Phil
 
must we register there ,to attend in Hands-on Labs?
Thanks I n advance
 
best regards
Leila

--- On Tue, 8/31/10, Phil Grainger wrote:

From: Phil Grainger
Subject: [DB2-L] New for IDUG EMEA 2010 - IBM Hands-on Labs - And They're Free!
To: DB2-L@IDUGDB2-L.ORG
Date: Tuesday, August 31, 2010, 7:42 PM

Expand your technical proficiency by working alongside the experts at the IDUG EMEA 2010 Hands-on Labs. These instructor-led mini-courses are designed to get you up to speed quickly with hands-on interaction directly with IBM products.
 
Work through your questions with experts and [...]43_356418.95397.qm@web50307.mail.re2.yahoo.com 9416 500 66_Re: New for IDUG EMEA 2010 - IBM Hands-on Labs - And They're Free!20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Sun, 5 Sep 2010 12:39:48 +0300639_us-ascii Hello Leila,

Registration link will be available soon. Watch the EMEA Space and see
you in the conference.

Regards - Cuneyt Goksu
IDUG EMEA 2010, Conference Chairperson

Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Laila hosaini
Sent: Sunday, September 05, 2010 10:12 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] New for IDUG EMEA 2010 - IBM Hands-on Labs - And
They're Free! [...]42_010001cb4cde$49646cc0$0201a8c0@cuneytgoksu 9917 800 64_Re: z/OS db2 v9 - now select from insert from a recursive select37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Mon, 6 Sep 2010 08:33:21 +0200564_iso-8859-2 I have tried
INSERT INTO SPTOW2
SELECT *
FROM FINAL TABLE (
INSERT INTO SPTOW VALUES(1,'A','A A',1) -- independent insert is OK
);
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM -- to my surprise the same sqlcode; I hope at least you know why
CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED
DSNT418I SQLSTATE = 428FL SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOP0 SQL PROCEDURE DETECTING ERROR [...]58_0675446363E09A4EBE4CCF8D3AD7814F04548172@MAIL1.cen.csin.cz 10718 113 53_Re: AW: [DB2-L] * Please Read * IDUG Survey and DB2-L10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 6 Sep 2010 08:44:38 +0200430_US-ASCII Aurora

I'm sure DB2-L could be hosted even in my grampa's Playstation 3 but you
still need a grampa AND a PS3. Moving (if the case, I was only theorizing)

DB2 informations to Linkedin or some other social forum on the web has the
advantage you don't need any hoster and administrators for you list and
you
don't need (in theory) IDUG,BMC or CA banners. Or even my stupid
footonotes :-). [...]66_OF1C4A1342.4B955957-ONC1257796.002301F4-C1257796.0025017C@cesve.it 10832 839 64_Re: z/OS db2 v9 - now select from insert from a recursive select13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Mon, 6 Sep 2010 04:53:44 -0400506_iso-8859-2 The best one would be

insert into tablea
select *
from old table
(delete from tableb
where .......)

This (if it was allowed) would be a great single-statement archive - MOVING rows from one table to another in a single SQL statement

As with all these others, this also gives sqlcode -20165 :(
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B6612B909E175@MAILR004.mail.lan 11672 876 72_AW: [DB2-L] z/OS db2 v9 - now select from insert from a recursive select35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Mon, 6 Sep 2010 11:40:33 +0200847_iso-8859-2 Hi Phil

So you could raise a new requirement for that.

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
walter.janissen@itergo.com

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996

________________________________
Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Phil Grainger
Gesendet: Montag, 6. September 2010 10:54
An: DB2-L@IDUGDB2-L.ORG
Betreff: Re: [DB2-L] z/OS db2 v9 - now select from insert from a recursive select [...]43_DB2-L%201009060540452338.3A04@IDUGDB2-L.ORG 12549 70 37_IDUG EMEA 2010 - Registration support10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 6 Sep 2010 12:01:56 +0200496_US-ASCII Esteemed listers

We're seeing that, after enjoying your vacations, many of you are
registering to IDUG EMEA Conference :-)))))). Thanks !

This year, to provide a direct and fast reply to attendees registration
questions, we strengthened our support for registration to IDUG EMEA
Conference. If you aren't able to register or you cannot access to IDUG
EMEA registration pages please contact Conference Chairperson,
Cuneyt Goksu thru cuneytgoksu@usa.net. [...]66_OF51B97F3D.508838A2-ONC1257796.0036E307-C1257796.003711BA@cesve.it 12620 245 53_Re: AW: [DB2-L] * Please Read * IDUG Survey and DB2-L12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Mon, 6 Sep 2010 12:45:26 +0200554_iso-8859-1 Ok Max - you got me...

Yes, I would definitely miss this particular list if it disappeared (and I don't seem to be the only one).
Can't be that much of work and cost to run listserv. So I just applied for volunteering on the IDUG web team.

Peter

_______________________
Peter Suhner
peter_suhner@hotmail.com

IDUG Europe is the premier training and networking event dedicated exclusively to DB2
Register for IDUG 2010 Vienna and attend my session on "DB2 and Oracle Infrastructure Standardisation"! [...]43_SNT107-W56B0755C7243D9850FCD94F1700@phx.gbl 12866 30 12_Index Levels21_Devyani Sahasrabuddhe30_devyanisahasrabuddhe@GMAIL.COM30_Mon, 6 Sep 2010 07:03:24 -0400846_UTF-8 List,

What can be scenarios in which number of levels in an index increase? When index levels increase, will it take more time to execute a query using that particular index? Can this be seen as an opportunity to do reorg index?

Thanks,
Devyani

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
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!
_____________________________________________________________________ [...]62_6568310431753316.WA.devyanisahasrabuddhegmail.com@www.idug.org 12897 82 24_AW: [DB2-L] Index Levels35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Mon, 6 Sep 2010 13:19:25 +0200346_iso-8859-1 Yes, the more levels an index has, the greater is the likelihood, that more sync-Ios are necessary. But a reorg does not necessarily reduce the number of levels. It could be, that the number increases because of freespace you specified.

To reduce the number of levels, there are several options, which may or may not help: [...]43_DB2-L%201009060719350782.3A3F@IDUGDB2-L.ORG 12980 140 53_Re: AW: [DB2-L] * Please Read * IDUG Survey and DB2-L10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 6 Sep 2010 13:28:19 +0200366_US-ASCII Hi Peter

Thank you for volunteering ! Don't worry, I'll stop to post in DB2-L soon
while the list will live for another century :-)))).

Anyway as pointed out, it was only a survey to understand OUR (we, users,
I mean) favorite place(s) for finding DB2 informations and networking with
other experts. That's all. I only made some [...]66_OF1713F46F.BF05EBF2-ONC1257796.003B7C65-C1257796.003EFA18@cesve.it 13121 116 49_Peter Vanroose's presentations about DB2 for z/OS10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 6 Sep 2010 09:04:44 -0400472_iso-8859-1 Hi DB2 user,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes):
http://db2usa.blogspot.com

Last update on Monday, September 6th 2010

Here are several Peter Vanroose's presentations about DB2 for z/OS available on Abis website:
- DB2 9 New Datatypes and SQL Functions: Blessing or Curse?
by Peter Vanroose

- Experiences with stored procedures, triggers and XML on DB2 version 8 for z/OS
by Peter Vanroose [...]43_SNT118-W45CE38CC985F22D0B38F0BAF700@phx.gbl 13238 619 33_Dallas DB2 Forum meeting tomorrow9_Bob Brock28_brock@KRYPTON-CONSULTING.COM30_Mon, 6 Sep 2010 09:51:24 -0500535_iso-8859-1 What better way to start a short work week than with:

DB2 FORUM User Group

Quarterly Meeting

Tuesday, September 7, 2010

8:00 AM - 12:30 PM

Location: IBM

1503 LBJ Freeway (LBJ at Luna)

Building 1503 Room 3000

Dallas, TX 75234

Breakfast Sponsor: DB2 Forum

08:00 - 08:55 Registration and Breakfast

08:55 - 09:00 Business and Announcements

09:00 - 10:00 "Ask not what the Optimizer can do for you - Ask what you can do for the Optimizer!" [...]38_5BA59A3AEF62427E91844DE3612A908B@RHINO 13858 271 53_Re: AW: [DB2-L] * Please Read * IDUG Survey and DB2-L12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Mon, 6 Sep 2010 17:50:45 +0200530_iso-8859-1 Max,

agreed, it was less my immediate fear of a disappearing LISTSERV than just this one additional kick it took to trigger some more personal involvement.

Cheers,
Peter

Date: Mon, 6 Sep 2010 13:28:19 +0200
From: mscarpa@CESVE.IT
Subject: Re: [DB2-L] AW: [DB2-L] * Please Read * IDUG Survey and DB2-L
To: DB2-L@IDUGDB2-L.ORG

Hi Peter

Thank you for volunteering ! Don't
worry, I'll stop to post in DB2-L soon while the list will live for another
century :-)))). [...]43_SNT107-W6114B32C96D4B92D52CE7AF1700@phx.gbl 14130 240 61_[AD] You killed the ELEPHANTS but MOSQUITOES are killing YOU!13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Mon, 6 Sep 2010 13:03:21 -0400599_us-ascii Please take a moment to look at this new webinar from Cogito North America - scheduled for THIS Friday, September 10th, at noon Eastern Daylight Time

Most DB2 tuning efforts focus on single "problem" SQL statements (the elephants - they're really easy to find). You may well be able to find a way to tune your elephant, but the overall workload performance improvement may be limited. In this live demo of the EZ-Tracer and EZ-Index Analyzer components of EZ-DB2, Gary Bronziet will demonstrate why it is the mosquitoes and not the elephants which are the root of your problem. [...]60_4440F5DA00E3F3459BBCB97431B91B6612B909E1AA@MAILR004.mail.lan 14371 88 16_Re: Index Levels14_Larry Kintisch17_LKint@VERIZON.NET30_Mon, 6 Sep 2010 20:05:03 -0400508_us-ascii Hi Devyani,
You may want to download and read sections of the IBM Redbook
SG24-7134 "DB2 UDB for z/OS: Application Design for High Performance
and Availability" [even if you are using LUW]. It has some nice
descriptions of the way in which DB2 uses indexes. Search the index
for "level" and you find a number of references to "index level".
Go to:
http://www.redbooks.ibm.com/ and search for SG24-7134. Download a
free copy or order a paper copy. It's a great resource. [...]40_0L8C00M60O8ZIOX4@vms173005.mailsrvcs.net 14460 713 113_The DB2Night Show Episode #28 - "DB2 Table Partitioning", with Mike Winer, IBM - Friday 10 September 10 am US CDT13_David Chapman24_David.Chapman@IAG.COM.AU30_Tue, 7 Sep 2010 16:21:15 +1000556_us-ascii _______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________

Hi DB2 List,

The DB2Night Show Episode #28 - "DB2 Table Partitioning", with Mike Winer, IBM

The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. [...]43_DB2-L%201009070221286419.3A8E@IDUGDB2-L.ORG 15174 24 16_Re: Index Levels21_Devyani Sahasrabuddhe30_devyanisahasrabuddhe@GMAIL.COM30_Tue, 7 Sep 2010 05:41:12 -0400631_UTF-8 Thanks a lot Larry for very good piece of information!

Devyani

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]62_7715203945303760.WA.devyanisahasrabuddhegmail.com@www.idug.org 15199 78 13_Change of UDF16_Christof Meister23_Christof.meister@KQV.DE30_Tue, 7 Sep 2010 08:08:32 -0400610_UTF-8 Hi all
On some Tables we have Audit Triggers. Every Trigger contains an UDF where we check up the Subsystem and the User. Primary we want one Point of Change for all Audittrigger.

CREATE FUNCTION
xxx.FN_AUDIT
(
PERSNR CHAR(8) FOR SBCS DATA CCSID EBCDIC
)
RETURNS
INTEGER
SPECIFIC FN_AUDIT
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
NO EXTERNAL ACTION
RETURN CASE WHEN CURRENT SERVER = 'XXXX' AND (PERSNR BE
TWEEN 'XXXXXXX' AND 'ZZZZZZZ' OR USER BETWEEN 'xxxxxx' AND 'vvvvvv' OR
USER LIKE 'AAAA%') THEN 1 ELSE 0 END
; [...]55_0694019882192215.WA.Christof.meisterkqv.de@www.idug.org 15278 669 117_Re: The DB2Night Show Episode #28 - "DB2 Table Partitioning", with Mike Winer, IBM - Friday 10 September 10 am US CDT14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Sep 2010 11:04:29 -0400554_us-ascii David,
Scott's (DBI) presentations are usually LUW platform-specific, and not applicable to z/OS. Is this partitioning presentation also for the non-z/OS platforms? Or does it cover z/OS as well?
--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Chapman
Sent: Tuesday, September 07, 2010 2:21 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] The DB2Night Show Episode #28 - "DB2 Table Partitioning", with Mike Winer, IBM - Friday 10 September 10 am US CDT [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF9BB@MAIL02.fisalan.nycnet 15948 81 51_[AD] Training: Relational Design; Appl. Programming14_Larry Kintisch17_LKint@VERIZON.NET30_Tue, 7 Sep 2010 11:24:15 -0400322_us-ascii Hi Listers,

I'll be teaching two DB2 classes as a contract instructor for IBM,
as I have for 19 years, and hope these two IBM classes are just what
some of your "newbies" or managers are looking for. Maybe some of you
were recently moved into the DB2 world or are moving from legacy systems. [...]40_0L8D00BVPUT2GMG5@vms173013.mailsrvcs.net 16030 107 16_Re: Index Levels14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Sep 2010 11:30:02 -0400557_iso-8859-1 Walter,
You didn't mention index compression!!!
--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Walter Janißen
Sent: Monday, September 06, 2010 7:19 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] AW: [DB2-L] Index Levels

Yes, the more levels an index has, the greater is the likelihood, that more sync-Ios are necessary. But a reorg does not necessarily reduce the number of levels. It could be, that the number increases because of freespace you specified. [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF9BC@MAIL02.fisalan.nycnet 16138 137 16_Re: Index Levels13_David Simpson22_dsimpson@THEMISINC.COM30_Tue, 7 Sep 2010 12:21:27 -0400325_iso-8859-1 Index compression is NOT likely to reduce the number of levels in an index. This is because the index is compressed on disk ONLY and not in the bufferpool. The larger page sizes associated with index compression could reduce the number of levels, but this is likely due to the page size, not the compression. [...]67_7EC87535E7581C4C890F5BF1CF9A937402A41815@THEMISSRV1.themisinc.local 16276 161 16_Re: Index Levels14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Sep 2010 12:27:30 -0400493_iso-8859-1 David,

Sorry, I thought the discussion had drifted a bit to the total number of SyncIOs, which should be reduced (when index is compressed) for certain types of index access. You're right about the number of levels, of course.

--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson
Sent: Tuesday, September 07, 2010 12:21 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Index Levels [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF9BE@MAIL02.fisalan.nycnet 16438 47 16_Re: Index Levels15_Cristian Molaro18_cristian@MOLARO.BE30_Tue, 7 Sep 2010 12:42:51 -0400575_UTF-8 Hi,
Bigger than 4K pages for indexes is a feature that was introduced in order to support index compression, but we should also consider to exploit bigger index pages independently of index compression in order to potentially reduce the index number of levels.
John Campbell has documented some examples of how the number of levels can decrease when using index pages > 4K.
Have a look at slides #36 & #37 here: http://www.gse-nordic.org/Working%20Groups/GNRC/Conferences/2009Workfolder/DB2/DB2%20for%20zOS%20Design%20for%20High%20Insert%20Performance(1) [...]50_5471246470304616.WA.cristianmolaro.be@www.idug.org 16486 39 24_Oracle Conversion to DB211_Dave Beulke19_dave@DAVEBEULKE.COM30_Tue, 7 Sep 2010 12:21:53 -04000_51_0378580652811353.WA.davedavebeulke.com@www.idug.org 16526 173 117_Re: The DB2Night Show Episode #28 - "DB2 Table Partitioning", with Mike Winer, IBM - Friday 10 September 10 am US CDT12_Martin Hubel17_Martin@MHUBEL.COM30_Tue, 7 Sep 2010 14:44:23 -04000_43_DB2-L%201009071444326166.3AE0@IDUGDB2-L.ORG 16700 144 81_Elimination of the Build2 Phase made the NPI worse - DB2 V9 (NFM) - z/OS 01.11.0015_Moss, William R15_MossW@AETNA.COM30_Tue, 7 Sep 2010 14:35:52 -0400582_us-ascii Just wanted to share some information we received from IBM about partition level reorgs and NPI's.

We have a 200 partition table with one NPI. The NPI was performing poorly and real time statistics showed that it needed to be reorged. One of the partitions also needed to be reorged so rather than running a standalone index reorg or a standalone index rebuild, we ran a reorg of the one partition knowing that the NPI would get rebuilt in the process. The partition reorg was run as shrlevel change, but there was no activity against the table during the time [...]70_F393F47ECBE9FC4AA71B2A242C0B6895128BB5BC1F@HFDPMSGCMS01.aeth.aetna.com 16845 195 16_Re: Index Levels13_David Simpson22_dsimpson@THEMISINC.COM30_Tue, 7 Sep 2010 15:28:08 -0400345_iso-8859-1 Indeed... sorry about that... I neglected to read the entire thread before I responded.

I'm also told (though have not benchmarked this) that compressing indexes will not reduce I/O... presumably the same reasons. The larger page sizes without compression would result in the same number of pages needing to be retrieved. [...]67_7EC87535E7581C4C890F5BF1CF9A937402A41854@THEMISSRV1.themisinc.local 17041 449 85_Re: Elimination of the Build2 Phase made the NPI worse - DB2 V9 (NFM) - z/OS 01.11.0014_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Sep 2010 16:06:11 -0400471_us-ascii Bill,

Thanks for the tip. We've been relying on our partition-level REORGs to clean up NPIs, so we'll take a closer look at that here.

--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Moss, William R
Sent: Tuesday, September 07, 2010 2:36 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Elimination of the Build2 Phase made the NPI worse - DB2 V9 (NFM) - z/OS 01.11.00 [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF9C0@MAIL02.fisalan.nycnet 17491 218 16_Re: Index Levels14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Sep 2010 16:09:47 -0400497_iso-8859-1 If you're not carrying large enough Index Bufferpools to have your L1 and L2 pages cached, you're right. Key question: are index pages cached in compressed form? This would give you reduced SyncIO for constant BP size, due to better BP hit ratios.

--Phil

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson
Sent: Tuesday, September 07, 2010 3:28 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Index Levels [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF9C1@MAIL02.fisalan.nycnet 17710 177 16_Re: Index Levels13_David Simpson22_dsimpson@THEMISINC.COM30_Tue, 7 Sep 2010 15:52:38 -0500663_utf-8 Index pages are not compressed in the bufferpool. Compression is on disk only.

On Sep 7, 2010, at 3:49 PM, "Sevetson, Phil" wrote:

> If you're not carrying large enough Index Bufferpools to have your L1 and L2 pages cached, you're right. Key question: are index pages cached in compressed form? This would give you reduced SyncIO for constant BP size, due to better BP hit ratios.
>
> --Phil
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson
> Sent: Tuesday, September 07, 2010 3:28 PM
> To: DB2-L@IDUGDB2-L.ORG
> Subject: [...]50_A5D5D01A-2973-444D-BAB1-E073DE26B29B@themisinc.com 17888 210 16_Re: Index Levels13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Tue, 7 Sep 2010 16:35:23 -0500305_ISO-8859-1 Index pages are not compressed in the buffer pool. In fact, if using
index compression, the index pages are stored on disk as 4K pages and
must use (moved to) a buffer pool defined as 8K, 16, or 32K buffer pool.

DSN1COMP can be used to determine the best pool size to choose. [...]30_4C86B01B.8090603@attglobal.net 18099 111 72_Re: DB2 version 10 for Z - what release of DB2 connect will be supported12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 7 Sep 2010 18:54:41 -0400356_UTF-8 I missed this one, but some of my colleagues found it. I can provide a little later answer.
I have found from frequent trips to Las Vegas for IOD and IDUG that I have more fun if
I keep my money in my pocket. It seems that when I gamble, I put money or chips on
the table, and then someone else picks them up. Where is the fun in that? [...]51_1864211862881016.WA.millerrlus.ibm.com@www.idug.org 18211 72 25_index column not selected9_Subbu Rao24_subrahmanyarao@YAHOO.COM30_Tue, 7 Sep 2010 19:21:10 -0400528_UTF-8 Hi,
Please assist me in getting the index columns being selected (MATCHCOLS) for the following query. Currently on Z/0s v8.1, the query gives TS scan when it comes to the table tab2.
SELECT C.CLIENT,
B.AGE,
B.MEMBERSEQ,
A.MEMINCDTE
FROM Tab1 A,
Tab2 B,
tab3 C
WHERE A.COL1 = :HV1
AND A.COL1 = B.COL1
AND A.COL1 = C.COL1
AND A.COL2 = :HV2
AND A.COL2 = B.COL2
AND A.COL3 = B.COL3
AND B.CLIENT = C.CLIENT
AND B.OFFSETNO = :HV3
AND B.STATUS = :HV4
AND C.YRJOINED = 0 [...]56_7468411798883495.WA.subrahmanyaraoyahoo.com@www.idug.org